bob

package module
v0.7.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 25, 2022 License: MIT Imports: 9 Imported by: 5

README

Bob (the builder): A spec compliant SQL query builder

Test Status GitHub go.mod Go version Go Reference Go Report Card GitHub tag (latest SemVer) Coverage Status

Bob helps build SQL queries. It does not try to abstract away SQL, or to hide implementation, instead Bob adds typed handrails to the query building for a better development experience.

Bob can be progressively adopted from raw SQL query strings, to fully typed queries and models generated for your database.

Features

  • Faster than comparable packages. See Benchmarks.
  • Build any query. Supports the specification as closely as possible.
  • Generate models from your database schema. See Documentation

Examples

Examples are in the examples folder:

Dialect Support

Dialect Select Insert Update Delete ORM
Postgres ✅ ✅ ✅ ✅ ✅
MySQL ✅ ✅ ✅ ✅
SQLite ✅ ✅ ✅ ✅
SQL Server

Principles

Custom Crafting

In bob, each dialect, and the applicable query mods are custom crafted to be as close to the specification as possible. This is unlike most other query builders that use a common structure and attempt to adapt it to every dialect.

Progressive enhancement

Most query mods will accept a literal string that will be printed as is.

However, many functions and methods are provided to express even the most complex queries in readable Go code.

// Assuming we're building the following query
/*
SELECT status,
    LEAD(created_date, 1, NOW())
    OVER(PARTITION BY presale_id ORDER BY created_date) -
    created_date AS "difference"
FROM presales_presalestatus
*/

// different ways to express "SELECT status"
psql.Select(qm.Columns("status")) // SELECT status
psql.Select(qm.Columns(qm.Quote("status"))) // SELECT "status"

// Ways to express LEAD(created_date, 1, NOW())
"LEAD(created_date, 1, NOW()"
psql.F("LEAD", "created_date", 1, "NOW()")
psql.F("LEAD", "created_date", 1, qm.F("NOW"))

// Ways to express PARTITION BY presale_id ORDER BY created_date
"PARTITION BY presale_id ORDER BY created_date"
qm.Window("").PartitionBy("presale_id").OrderBy("created_date")

// Expressing LEAD(...) OVER(...)
"LEAD(created_date, 1, NOW()) OVER(PARTITION BY presale_id ORDER BY created_date)"
psql.F("LEAD", "created_date", 1, psql.F("NOW")).
    Over("").
    PartitionBy("presale_id").
    OrderBy("created_date")

// The full query
psql.Select(
    qm.Columns(
        "status",
        psql.F("LEAD", "created_date", 1, psql.F("NOW")).
            Over("").
            PartitionBy("presale_id").
            OrderBy("created_date").
            Minus("created_date").
            As("difference")),
    qm.From("presales_presalestatus")),
)

Query Building

Query building is done with the use of QueryMods.

QueryMods are options applied to a query. Each query type of each dialect defines what mods can be applied to it.
This way, the possible options can be built to match the spec as closely as possible.

Despite this custom configuration, the mods are designed to match each other as closely so that switching dialects can be achieved by simply switching imports.
However, if using an unspported mod, the error will be displayed at compile time.

As an example, both SELECT and INSERT can use CTEs(Common Table Expressions), but while INSERT can take an INTO expression, SELECT instead needs a FROM

import "github.com/stephenafamo/bob/dialect/psql/select/qm"
cte := psql.Select(
    qm.From("users"),
    qm.Where(psql.X("age").GTE(21)),
)

var cte query.Query
psql.Select(
    qm.With("adults").As(cte), // works
    qm.From("projects"),
)

import "github.com/stephenafamo/bob/dialect/psql/insert/qm"
psql.Insert(
    qm.With("adults").As(cte), // works as well
    qm.From("projects"), // ERROR: Does not compile!!!
    qm.Into("projects"), // works
)

Using this query mod system, the mods closely match the allowed syntax for each specific query type.

For conditional queries, the query object have an Apply() method which can be used to add more query mods.

q := psql.Select(
    qm.From("projects"),
) // SELECT * FROM projects

if !user.IsAdmin {
    q.Apply(
        qm.Where(psql.X("user_id").EQ(psql.Arg(user.ID))),
    ) // SELECT * FROM projects WHERE user_id = $1
}

Since the mods modify the main query object any new mods added with Apply() will affect all instances of the query.

To reuse the base of a query and add new mods each time, first use the Clone() method.

Quotes

It is often required to quote identifiers in SQL queries. With bob use the qm.Quote() where necessary.
When building the query, the quotes are added correctly by the dialect.

It can take multiple strings that need to be quoted and joined with .

// Postgres: "schema_name"."table_name"
// SQLite: "schema_name"."table_name"
// MySQL: `schema_name`.`table_name`
// SQL Server: [schema_name].[table_name]
psql.Quote("schema_name", "table_name")

Expressions

Every dialect contain starter functions to fluently build complex expressions.
It starts with one of several functions which then return a chain that has methods for various operators.

For example:

// Query: ($1 >= 50) AND (name IS NOT NULL)
// Args: 'Stephen'
psql.Arg("Stephen").GTE(50).
    And(psql.X("name").IsNotNull())

// OR

psql.And(
    psql.Arg("Stephen").GTE(50),
    psql.X("name").IsNotNull(),
)
Starters

These functions are included in every dialect and can be used to create a chainable expression.

The most flexible starter is X()

  • Pass a single value to start a plain chain
  • Pass multiple values to join them all with spaces. This is better than using a plain string because it is easier to interpolate quoted values, args, e.t.c.
// SQL: "schema"."table"."name" = $1
// Args: 'Stephen'
psql.X(psql.Quote("schema", "table", "name"), "=", psql.Arg("Stephen"))

Other starters are listed below:

NOTE: These are the common starters. Each dialect can sometimes include their own starters.
For example, starters for common function calls can easily be added

  • X(any): Plain start to a chain.
  • Not(any): Creates a NOT expr expression that is then chainable.
  • F(name string, args ...any): A generic function call. Takes a name and the arguments.
  • OR(...any): Joins multiple expressions with "OR"
  • AND(...any): Joins multiple expressions with "AND"
  • CONCAT(...any): Joins multiple expressions with "||"
  • S(string): Create a plain string literal. Single quoted.
  • Arg(...any): One or more arguments. These are replaced with placeholders in the query and the args returned.
  • Placeholders(uint): Inserts a count of placeholders without any specific value yet. Useful for compiling reusable queries.
  • Statement(clause string, args ...any): For inserting a raw statement somewhere. To keep it dialect agnostic, placeholders should be inserted with ? and a literal question mark can be escaped with a backslash \?.
  • Group(...any): To easily group a number of expressions. Wraps them in parentheses and seperates them with commas.
  • Quote(...string): For quoting. See details
  • P(any): To manually wrap an expression with parentheses. This is often not necessary as the parentheses will be added as the expression is built.
Chaining

The type returned by the starter methods return have methods for common operators.
NOTE: These are the common operators. Each dialect can sometimes include their own starters

  • IsNull(): X IS NULL
  • IsNotNull(): X IS NOT NULL
  • Is(y any): X IS DISTINCT FROM Y
  • IsNot(y any): X IS NOT DISTINCT FROM Y
  • EQ(y any): X = Y
  • NE(y any): X <> Y
  • LT(y any): X < Y
  • LTE(y any): X <= Y
  • GT(y any): X > Y
  • GTE(y any): X >= Y
  • In(...any): X IN (y, z)
  • NotIn(...any): X NOT IN (y, z)
  • Or(y any): X OR Y
  • And(y any): X AND Y
  • Concat(y any): X || Y

The following expressions cannot be chained and are expected to be used at the end of a chain

  • As(alias string): X as "alias". Used for aliasing column names

Parameters

To prevent SQL injection, it is necessary to use parameters in our queries. With bob use qm.Arg() where necessary.
This will write the placeholder correctly in the generated sql, and return the value in the argument slice.

// args: 100, "Stephen"
// Postgres: SELECT * from users WHERE id = $1 AND name = $2
// MySQL: SELECT * from users WHERE id = ? AND name = ?
// SQL Server: SELECT * from users WHERE id = @p1 AND name = @p2
psql.Select(
    qm.From("users"),
    qm.Where(psql.X("id").EQ(psql.Arg(100))),
    qm.Where(psql.X("name".EQ(psql.Arg("Stephen"))),
)

Raw Queries

As any good query builder, you are allowed to use your own raw SQL queries. Either at the top level with psql.RawQuery() or inside any clause with psql.Raw().

These functions take a query and args. The placeholder in the clauses are question marks ?.

// SELECT * from users WHERE id = $1 AND name = $2
// args: 100, "Stephen"

psql.RawQuery(`SELECT * FROM USERS WHERE id = ? and name = ?`, 100, "Stephen")
// -----
// OR
// -----
psql.Select(
    qm.From("users"),
    qm.Where(psql.Raw("id = ? and name = ?", 100, "Stephen")),
)

Using the Query

The Query object is an interface that has a single method:

type Query interface {
    // start is the index of the args, usually 1.
    // it is present to allow re-indexing in cases of a subquery
    // The method returns the value of any args placed
    // An `io.Writer` is used for efficiency when building the query.
    WriteQuery(w io.Writer, start int) (args []any, err error)
}

The WriteQuery method is useful when we want to write to an exisiting io.Writer.
However we often just want the query string and arguments. So the Query objects have the following methods:

  • Build() (query string, args []any, err error)
  • BuildN(start int) (query string, args []any, err error)
  • MustBuild() (query string, args []any) // panics on error
  • MustBuildN(start int) (query string, args []any) // panics on error
queryString, args, err := psql.Select(...).Build()

Since the query is built from scratch every time the WriteQuery() method is called, it can be useful to initialize the query one time and reuse where necessary.

For that, the MustBuild() function can be used. This panics on error.

var myquery, myargs = psql.Insert(...).MustBuild()

Roadmap

  • Postgres
    • Raw
    • Select
    • Insert
    • Update
    • Delete
    • Postgres Specific Operators
      • Is [Not] True
      • Is [Not] False
      • Is [Not] Unknown
      • [Not] Between Symmetric
      • Is [Not] [NFC|NFD|NFKC|NFKD] Normalized
  • MySQL
    • Raw
    • Select
    • Insert
    • Update
    • Delete
  • SQLite
    • Raw
    • Select
    • Insert
    • Update
    • Delete
    • SQLite Specific Operators
      • GLOB
  • SQL Server
    • Raw
    • Select
    • Insert
    • Update
    • Delete
  • Common Operators
    • [Not] Equal
    • Not Equal
    • Less than
    • Less than or equal to
    • Greater than
    • Greater than or equal to
    • And
    • Or
    • [Not] In
    • [Not] Null
    • Is [not] distinct from
    • Concatenation: ||
    • Between

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var ErrNoNamedArgs = errors.New("Dialect does not support named arguments")

Functions

func All 

func All[T any](ctx context.Context, exec Executor, q Query, m scan.Mapper[T], opts ...ExecOption[T]) ([]T, error)

func Allx 

func Allx[T any, Ts ~[]T](ctx context.Context, exec Executor, q Query, m scan.Mapper[T], opts ...ExecOption[T]) (Ts, error)

Allx takes 2 type parameters. The second is a special return type of the returned slice this is especially useful for when the the Query is Loadable and the loader depends on the return value implementing an interface

func Build 

func Build(q Query) (string, []any, error)

Convinient function to build query from start

func BuildN 

func BuildN(q Query, start int) (string, []any, error)

Convinient function to build query from a point

func Cursor 

func Cursor[T any](ctx context.Context, exec Executor, q Query, m scan.Mapper[T], opts ...ExecOption[T]) (scan.ICursor[T], error)

Cursor returns a cursor that works similar to *sql.Rows

func Exec added in v0.3.1 

func Exec(ctx context.Context, exec Executor, q Query) (int64, error)

func Express 

func Express(w io.Writer, d Dialect, start int, e any) ([]any, error)

func ExpressIf 

func ExpressIf(w io.Writer, d Dialect, start int, e any, cond bool, prefix, suffix string) ([]any, error)

ExpressIf expands an express if the condition evaluates to true it can also add a prefix and suffix

func ExpressSlice 

func ExpressSlice[T any](w io.Writer, d Dialect, start int, expressions []T, prefix, sep, suffix string) ([]any, error)

ExpressSlice is used to express a slice of expressions along with a prefix and suffix

func MustBuild 

func MustBuild(q Query) (string, []any)

MustBuild builds a query and panics on error useful for initializing queries that need to be reused

func MustBuildN 

func MustBuildN(q Query, start int) (string, []any)

func New 

func New[T StdInterface](wrapped T) common[T]

New wraps an StdInterface to make it comply with Queryer It also includes a number of other methods that are often used with *sql.DB, *sql.Tx and *sql.Conn

func NewQueryer 

func NewQueryer[T StdQueryer](wrapped T) scan.Queryer

NewQueryer wraps an StdQueryer and makes it a Queryer

func One 

func One[T any](ctx context.Context, exec Executor, q Query, m scan.Mapper[T], opts ...ExecOption[T]) (T, error)

Types

type BaseQuery 

type BaseQuery[E Expression] struct {
	Expression E
	Dialect    Dialect
}

BaseQuery wraps common functionality such as cloning, applying new mods and the actual query interface implementation

func (BaseQuery[E]) Apply 

func (b BaseQuery[E]) Apply(mods ...Mod[E])

func (BaseQuery[E]) Build 

func (q BaseQuery[E]) Build() (string, []any, error)

Convinient function to build query from start

func (BaseQuery[E]) BuildN 

func (q BaseQuery[E]) BuildN(start int) (string, []any, error)

Convinient function to build query from a point

func (BaseQuery[E]) Clone 

func (b BaseQuery[E]) Clone() BaseQuery[E]

func (BaseQuery[E]) GetExtraLoaders 

func (b BaseQuery[E]) GetExtraLoaders() []ExtraLoader

func (BaseQuery[E]) GetLoaders 

func (b BaseQuery[E]) GetLoaders() []LoadFunc

func (BaseQuery[E]) GetMapperMods 

func (b BaseQuery[E]) GetMapperMods() []scan.MapperMod

func (BaseQuery[E]) MustBuild 

func (q BaseQuery[E]) MustBuild() (string, []any)

MustBuild builds the query and panics on error useful for initializing queries that need to be reused

func (BaseQuery[E]) MustBuildN 

func (q BaseQuery[E]) MustBuildN(start int) (string, []any)

MustBuildN builds the query and panics on error start numbers the arguments from a different point

func (BaseQuery[E]) WriteQuery 

func (b BaseQuery[E]) WriteQuery(w io.Writer, start int) ([]any, error)

func (BaseQuery[E]) WriteSQL 

func (b BaseQuery[E]) WriteSQL(w io.Writer, _ Dialect, start int) ([]any, error)

Satisfies the Expression interface, but uses its own dialect instead of the dialect passed to it

type Conn 

type Conn struct {
	// contains filtered or unexported fields
}

Conn is similar to *sql.Conn but implements [Queryer]

func NewConn 

func NewConn(conn *sql.Conn) Conn

NewConn wraps an *sql.Conn and returns a type that implements [Queryer] This is useful when an existing *sql.Conn is used in other places in the codebase

func (Conn) ExecContext 

func (q Conn) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (Conn) PrepareContext 

func (q Conn) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

PrepareContext creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

The provided context is used for the preparation of the statement, not for the execution of the statement.

func (Conn) QueryRowContext 

func (q Conn) QueryRowContext(ctx context.Context, query string, args ...any) scan.Row

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

type DB 

type DB struct {
	// contains filtered or unexported fields
}

DB is similar to *sql.DB but implement [Queryer]

func NewDB 

func NewDB(db *sql.DB) DB

NewDB wraps an *sql.DB and returns a type that implements [Queryer] but still retains the expected methods used by *sql.DB This is useful when an existing *sql.DB is used in other places in the codebase

func Open 

func Open(driverName string, dataSource string) (DB, error)

Open works just like sql.Open, but converts the returned *sql.DB to DB

func OpenDB 

func OpenDB(c driver.Connector) DB

OpenDB works just like sql.OpenDB, but converts the returned *sql.DB to DB

func (DB) BeginTx 

func (d DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (Tx, error)

BeginTx is similar to [sql.DB.BeginTx()], but return a transaction that implements [Queryer]

func (DB) Close 

func (d DB) Close() error

Close works the same as [sql.DB.Close()]

func (DB) ExecContext 

func (q DB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (DB) PrepareContext 

func (q DB) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

PrepareContext creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

The provided context is used for the preparation of the statement, not for the execution of the statement.

func (DB) QueryRowContext 

func (q DB) QueryRowContext(ctx context.Context, query string, args ...any) scan.Row

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

type Dialect 

type Dialect interface {
	// WriteArg should write an argument placeholder to the writer with the given index
	WriteArg(w io.Writer, position int)

	// WriteQuoted writes the given string to the writer surrounded by the appropriate
	// quotes for the dialect
	WriteQuoted(w io.Writer, s string)
}

type DialectWithNamed 

type DialectWithNamed interface {
	Dialect
	// WriteNamedArg should write an argument placeholder to the writer with the given name
	WriteNamedArg(w io.Writer, name string)
}

Can also write namded args

type ExecOption added in v0.2.3 

type ExecOption[T any] func(*ExecSettings[T])

type ExecSettings added in v0.2.3 

type ExecSettings[T any] struct {
	AfterSelect func(ctx context.Context, retrieved []T) error
}

type Executor added in v0.3.1 

type Executor interface {
	scan.Queryer
	ExecContext(context.Context, string, ...any) (sql.Result, error)
}

func DebugExecutor added in v0.3.1 

func DebugExecutor(q Executor) Executor

type Expression 

type Expression interface {
	// Writes the textual representation of the expression to the writer
	// using the given dialect.
	// start is the beginning index of the args if it needs to write any
	WriteSQL(w io.Writer, d Dialect, start int) (args []any, err error)
}

type ExpressionFunc 

type ExpressionFunc func(w io.Writer, d Dialect, start int) ([]any, error)

func (ExpressionFunc) WriteSQL 

func (e ExpressionFunc) WriteSQL(w io.Writer, d Dialect, start int) ([]any, error)

type ExtraLoader 

type ExtraLoader interface {
	LoadOne(context.Context, Executor) error
	LoadMany(context.Context, Executor) error
}

type LoadFunc 

type LoadFunc = func(ctx context.Context, exec Executor, retrieved any) error

type Loadable 

type Loadable interface {
	GetLoaders() []LoadFunc
	GetExtraLoaders() []ExtraLoader
}

type MapperModder 

type MapperModder interface {
	GetMapperMods() []scan.MapperMod
}

type Mod 

type Mod[T any] interface {
	Apply(T)
}

type Query 

type Query interface {
	// It should satisfy the Expression interface so that it can be used
	// in places such as a sub-select
	// However, it is allowed for a query to use its own dialect and not
	// the dialect given to it
	Expression
	// start is the index of the args, usually 1.
	// it is present to allow re-indexing in cases of a subquery
	// The method returns the value of any args placed
	WriteQuery(w io.Writer, start int) (args []any, err error)
}

type StdInterface 

type StdInterface interface {
	StdQueryer
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

An interface that *sql.DB, *sql.Tx and *sql.Conn satisfy

type StdQueryer 

type StdQueryer interface {
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
}

A Queryer that returns the concrete type *sql.Rows

type Tx 

type Tx struct {
	// contains filtered or unexported fields
}

Tx is similar to *sql.Tx but implements [Queryer]

func NewTx 

func NewTx(tx *sql.Tx) Tx

NewTx wraps an *sql.Tx and returns a type that implements [Queryer] but still retains the expected methods used by *sql.Tx This is useful when an existing *sql.Tx is used in other places in the codebase

func (Tx) Commit 

func (t Tx) Commit() error

Commit works the same as [*sql.Tx.Commit()]

func (Tx) ExecContext 

func (q Tx) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (Tx) PrepareContext 

func (q Tx) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

PrepareContext creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's Close method when the statement is no longer needed.

The provided context is used for the preparation of the statement, not for the execution of the statement.

func (Tx) QueryRowContext 

func (q Tx) QueryRowContext(ctx context.Context, query string, args ...any) scan.Row

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (Tx) Rollback 

func (t Tx) Rollback() error

Rollback works the same as [*sql.Tx.Rollback()]

Source Files

View all Source files

Directories

Path Synopsis
dialect
mssql
mysql
mysql/delete/qm
mysql/dialect
mysql/insert/qm
mysql/select/qm
mysql/update/qm
psql
psql/delete/qm
psql/dialect
psql/insert/qm
psql/model
psql/select/qm
psql/update/qm
sqlite
sqlite/delete/qm
sqlite/dialect
sqlite/insert/qm
sqlite/select/qm
sqlite/update/qm
orm
gen
gen/drivers
Package drivers talks to various database backends and retrieves table, column, type, and foreign key information
 Package drivers talks to various database backends and retrieves table, column, type, and foreign key information
gen/drivers/mocks
gen/importers
Package importers helps with dynamic imports for templating
 Package importers helps with dynamic imports for templating

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.