squirrel

package module
v1.5.4 Latest Latest
Warning

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

 Go to latest Published: Mar 17, 2023 License: MIT Imports: 12 Imported by: 3,069

README ¶

Squirrel is "complete".

Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork (or substantially similar project) actively improves on what Squirrel does, let me know and I may link to it here.

Squirrel - fluent SQL generator for Go

import "github.com/Masterminds/squirrel"

GoDoc Build Status

Squirrel is not an ORM. For an application of Squirrel, check out structable, a table-struct mapper

Squirrel helps you build SQL queries from composable parts:

import sq "github.com/Masterminds/squirrel"

users := sq.Select("*").From("users").Join("emails USING (email_id)")

active := users.Where(sq.Eq{"deleted_at": nil})

sql, args, err := active.ToSql()

sql == "SELECT * FROM users JOIN emails USING (email_id) WHERE deleted_at IS NULL"

sql, args, err := sq.
    Insert("users").Columns("name", "age").
    Values("moe", 13).Values("larry", sq.Expr("? + 5", 12)).
    ToSql()

sql == "INSERT INTO users (name,age) VALUES (?,?),(?,? + 5)"

Squirrel can also execute queries directly:

stooges := users.Where(sq.Eq{"username": []string{"moe", "larry", "curly", "shemp"}})
three_stooges := stooges.Limit(3)
rows, err := three_stooges.RunWith(db).Query()

// Behaves like:
rows, err := db.Query("SELECT * FROM users WHERE username IN (?,?,?,?) LIMIT 3",
                      "moe", "larry", "curly", "shemp")

Squirrel makes conditional query building a breeze:

if len(q) > 0 {
    users = users.Where("name LIKE ?", fmt.Sprint("%", q, "%"))
}

Squirrel wants to make your life easier:

// StmtCache caches Prepared Stmts for you
dbCache := sq.NewStmtCache(db)

// StatementBuilder keeps your syntax neat
mydb := sq.StatementBuilder.RunWith(dbCache)
select_users := mydb.Select("*").From("users")

Squirrel loves PostgreSQL:

psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

// You use question marks for placeholders...
sql, _, _ := psql.Select("*").From("elephants").Where("name IN (?,?)", "Dumbo", "Verna").ToSql()

/// ...squirrel replaces them using PlaceholderFormat.
sql == "SELECT * FROM elephants WHERE name IN ($1,$2)"


/// You can retrieve id ...
query := sq.Insert("nodes").
    Columns("uuid", "type", "data").
    Values(node.Uuid, node.Type, node.Data).
    Suffix("RETURNING \"id\"").
    RunWith(m.db).
    PlaceholderFormat(sq.Dollar)

query.QueryRow().Scan(&node.id)

You can escape question marks by inserting two question marks:

SELECT * FROM nodes WHERE meta->'format' ??| array[?,?]

will generate with the Dollar Placeholder:

SELECT * FROM nodes WHERE meta->'format' ?| array[$1,$2]

FAQ

  • How can I build an IN query on composite keys / tuples, e.g. WHERE (col1, col2) IN ((1,2),(3,4))? (#104)

    Squirrel does not explicitly support tuples, but you can get the same effect with e.g.:

    sq.Or{
  sq.Eq{"col1": 1, "col2": 2},
  sq.Eq{"col1": 3, "col2": 4}}

    WHERE (col1 = 1 AND col2 = 2) OR (col1 = 3 AND col2 = 4)

    (which should produce the same query plan as the tuple version)

  • Why doesn't Eq{"mynumber": []uint8{1,2,3}} turn into an IN query? (#114)

    Values of type []byte are handled specially by database/sql. In Go, byte is just an alias of uint8, so there is no way to distinguish []uint8 from []byte.

  • Some features are poorly documented!

    This isn't a frequent complaints section!

  • Some features are poorly documented?

    Yes. The tests should be considered a part of the documentation; take a look at those for ideas on how to express more complex queries.

License

Squirrel is released under the MIT License.

Documentation ¶

Overview ¶

Package squirrel provides a fluent SQL generator.

See https://github.com/Masterminds/squirrel for examples.

Index ¶

Examples ¶

Constants ¶

This section is empty.

Variables ¶

View Source 
var (
	// Question is a PlaceholderFormat instance that leaves placeholders as
	// question marks.
	Question = questionFormat{}

	// Dollar is a PlaceholderFormat instance that replaces placeholders with
	// dollar-prefixed positional placeholders (e.g. $1, $2, $3).
	Dollar = dollarFormat{}

	// Colon is a PlaceholderFormat instance that replaces placeholders with
	// colon-prefixed positional placeholders (e.g. :1, :2, :3).
	Colon = colonFormat{}

	// AtP is a PlaceholderFormat instance that replaces placeholders with
	// "@p"-prefixed positional placeholders (e.g. @p1, @p2, @p3).
	AtP = atpFormat{}
)
View Source 
var NoContextSupport = errors.New("DB does not support Context")

NoContextSupport is returned if a db doesn't support Context.

View Source 
var RunnerNotQueryRunner = fmt.Errorf("cannot QueryRow; Runner is not a QueryRower")

RunnerNotQueryRunner is returned by QueryRow if the RunWith value doesn't implement QueryRower.

View Source 
var RunnerNotSet = fmt.Errorf("cannot run; no Runner set (RunWith)")

RunnerNotSet is returned by methods that need a Runner if it isn't set.

View Source 
var StatementBuilder = StatementBuilderType(builder.EmptyBuilder).PlaceholderFormat(Question)

StatementBuilder is a parent builder for other builders, e.g. SelectBuilder.

Functions ¶

func Alias ¶ 

func Alias(expr Sqlizer, alias string) aliasExpr

Alias allows to define alias for column in SelectBuilder. Useful when column is defined as complex expression like IF or CASE Ex: 

.Column(Alias(caseStmt, "case_column"))

func ConcatExpr ¶ added in v1.2.0 

func ConcatExpr(parts ...interface{}) concatExpr

ConcatExpr builds an expression by concatenating strings and other expressions.

Ex: 

name_expr := Expr("CONCAT(?, ' ', ?)", firstName, lastName)
ConcatExpr("COALESCE(full_name,", name_expr, ")")

func DebugSqlizer ¶ 

func DebugSqlizer(s Sqlizer) string

DebugSqlizer calls ToSql on s and shows the approximate SQL to be executed

If ToSql returns an error, the result of this method will look like: "[ToSql error: %s]" or "[DebugSqlizer error: %s]"

IMPORTANT: As its name suggests, this function should only be used for debugging. While the string result *might* be valid SQL, this function does not try very hard to ensure it. Additionally, executing the output of this function with any untrusted user input is certainly insecure.

func ExecContextWith ¶ 

func ExecContextWith(ctx context.Context, db ExecerContext, s Sqlizer) (res sql.Result, err error)

ExecContextWith ExecContexts the SQL returned by s with db.

func ExecWith ¶ 

func ExecWith(db Execer, s Sqlizer) (res sql.Result, err error)

ExecWith Execs the SQL returned by s with db.

func Placeholders ¶ 

func Placeholders(count int) string

Placeholders returns a string with count ? placeholders joined with commas.

func QueryContextWith ¶ 

func QueryContextWith(ctx context.Context, db QueryerContext, s Sqlizer) (rows *sql.Rows, err error)

QueryContextWith QueryContexts the SQL returned by s with db.

func QueryWith ¶ 

func QueryWith(db Queryer, s Sqlizer) (rows *sql.Rows, err error)

QueryWith Querys the SQL returned by s with db.

Types ¶

type And ¶ 

type And conj

And conjunction Sqlizers

func (And) ToSql ¶ 

func (a And) ToSql() (string, []interface{}, error)

type BaseRunner ¶ 

type BaseRunner interface {
	Execer
	Queryer
}

BaseRunner groups the Execer and Queryer interfaces.

type CaseBuilder ¶ 

type CaseBuilder builder.Builder

CaseBuilder builds SQL CASE construct which could be used as parts of queries.

func Case ¶ 

func Case(what ...interface{}) CaseBuilder

Case returns a new CaseBuilder "what" represents case value

func (CaseBuilder) Else ¶ 

func (b CaseBuilder) Else(expr interface{}) CaseBuilder

What sets optional "ELSE ..." part for CASE construct

func (CaseBuilder) MustSql ¶ added in v1.5.1 

func (b CaseBuilder) MustSql() (string, []interface{})

MustSql builds the query into a SQL string and bound args. It panics if there are any errors.

func (CaseBuilder) ToSql ¶ 

func (b CaseBuilder) ToSql() (string, []interface{}, error)

ToSql builds the query into a SQL string and bound args.

func (CaseBuilder) When ¶ 

func (b CaseBuilder) When(when interface{}, then interface{}) CaseBuilder

When adds "WHEN ... THEN ..." part to CASE construct

type DBProxy ¶ 

type DBProxy interface {
	Execer
	Queryer
	QueryRower
	Preparer
}

DBProxy groups the Execer, Queryer, QueryRower, and Preparer interfaces.

type DBProxyBeginner ¶ 

type DBProxyBeginner interface {
	DBProxy
	Begin() (*sql.Tx, error)
}

func NewStmtCacheProxy ¶ 

func NewStmtCacheProxy(db *sql.DB) DBProxyBeginner

type DBProxyContext ¶ 

type DBProxyContext interface {
	Execer
	Queryer
	QueryRower
	PreparerContext
}

DBProxyContext groups the Execer, Queryer, QueryRower and PreparerContext interfaces.

func NewStmtCacher ¶ 

func NewStmtCacher(prep PreparerContext) DBProxyContext

NewStmtCacher is deprecated

Use NewStmtCache instead

type DeleteBuilder ¶ 

type DeleteBuilder builder.Builder

DeleteBuilder builds SQL DELETE statements.

func Delete ¶ 

func Delete(from string) DeleteBuilder

Delete returns a new DeleteBuilder with the given table name.

See DeleteBuilder.Table.

func (DeleteBuilder) Exec ¶ 

func (b DeleteBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (DeleteBuilder) ExecContext ¶ 

func (b DeleteBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (DeleteBuilder) From ¶ 

func (b DeleteBuilder) From(from string) DeleteBuilder

From sets the table to be deleted from.

func (DeleteBuilder) Limit ¶ 

func (b DeleteBuilder) Limit(limit uint64) DeleteBuilder

Limit sets a LIMIT clause on the query.

func (DeleteBuilder) MustSql ¶ added in v1.5.1 

func (b DeleteBuilder) MustSql() (string, []interface{})

MustSql builds the query into a SQL string and bound args. It panics if there are any errors.

func (DeleteBuilder) Offset ¶ 

func (b DeleteBuilder) Offset(offset uint64) DeleteBuilder

Offset sets a OFFSET clause on the query.

func (DeleteBuilder) OrderBy ¶ 

func (b DeleteBuilder) OrderBy(orderBys ...string) DeleteBuilder

OrderBy adds ORDER BY expressions to the query.

func (DeleteBuilder) PlaceholderFormat ¶ 

func (b DeleteBuilder) PlaceholderFormat(f PlaceholderFormat) DeleteBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

func (DeleteBuilder) Prefix ¶ 

func (b DeleteBuilder) Prefix(sql string, args ...interface{}) DeleteBuilder

Prefix adds an expression to the beginning of the query

func (DeleteBuilder) PrefixExpr ¶ added in v1.2.0 

func (b DeleteBuilder) PrefixExpr(expr Sqlizer) DeleteBuilder

PrefixExpr adds an expression to the very beginning of the query

func (DeleteBuilder) Query ¶ 

func (b DeleteBuilder) Query() (*sql.Rows, error)

func (DeleteBuilder) QueryContext ¶ added in v1.3.0 

func (b DeleteBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (DeleteBuilder) QueryRowContext ¶ added in v1.3.0 

func (b DeleteBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (DeleteBuilder) RunWith ¶ 

func (b DeleteBuilder) RunWith(runner BaseRunner) DeleteBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec.

func (DeleteBuilder) ScanContext ¶ added in v1.3.0 

func (b DeleteBuilder) ScanContext(ctx context.Context, dest ...interface{}) error

ScanContext is a shortcut for QueryRowContext().Scan.

func (DeleteBuilder) Suffix ¶ 

func (b DeleteBuilder) Suffix(sql string, args ...interface{}) DeleteBuilder

Suffix adds an expression to the end of the query

func (DeleteBuilder) SuffixExpr ¶ added in v1.2.0 

func (b DeleteBuilder) SuffixExpr(expr Sqlizer) DeleteBuilder

SuffixExpr adds an expression to the end of the query

func (DeleteBuilder) ToSql ¶ 

func (b DeleteBuilder) ToSql() (string, []interface{}, error)

ToSql builds the query into a SQL string and bound args.

func (DeleteBuilder) Where ¶ 

func (b DeleteBuilder) Where(pred interface{}, args ...interface{}) DeleteBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

type Eq ¶ 

type Eq map[string]interface{}

Eq is syntactic sugar for use with Where/Having/Set methods.

Example ¶ 
Select("id", "created", "first_name").From("users").Where(Eq{
	"company": 20,
})

func (Eq) ToSql ¶ 

func (eq Eq) ToSql() (sql string, args []interface{}, err error)

type Execer ¶ 

type Execer interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
}

Execer is the interface that wraps the Exec method.

Exec executes the given query as implemented by database/sql.Exec.

type ExecerContext ¶ 

type ExecerContext interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

ExecerContext is the interface that wraps the ExecContext method.

Exec executes the given query as implemented by database/sql.ExecContext.

type Gt ¶ 

type Gt Lt

Gt is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(Gt{"id": 1}) == "id > 1"

func (Gt) ToSql ¶ 

func (gt Gt) ToSql() (sql string, args []interface{}, err error)

type GtOrEq ¶ 

type GtOrEq Lt

GtOrEq is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(GtOrEq{"id": 1}) == "id >= 1"

func (GtOrEq) ToSql ¶ 

func (gtOrEq GtOrEq) ToSql() (sql string, args []interface{}, err error)

type ILike ¶ added in v1.2.0 

type ILike Like

ILike is syntactic sugar for use with ILIKE conditions. Ex: 

.Where(ILike{"name": "sq%"})

func (ILike) ToSql ¶ added in v1.2.0 

func (ilk ILike) ToSql() (sql string, args []interface{}, err error)

type InsertBuilder ¶ 

type InsertBuilder builder.Builder

InsertBuilder builds SQL INSERT statements.

func Insert ¶ 

func Insert(into string) InsertBuilder

Insert returns a new InsertBuilder with the given table name.

See InsertBuilder.Into.

func Replace ¶ added in v1.2.0 

func Replace(into string) InsertBuilder

Replace returns a new InsertBuilder with the statement keyword set to "REPLACE" and with the given table name.

See InsertBuilder.Into.

func (InsertBuilder) Columns ¶ 

func (b InsertBuilder) Columns(columns ...string) InsertBuilder

Columns adds insert columns to the query.

func (InsertBuilder) Exec ¶ 

func (b InsertBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (InsertBuilder) ExecContext ¶ 

func (b InsertBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (InsertBuilder) Into ¶ 

func (b InsertBuilder) Into(from string) InsertBuilder

Into sets the INTO clause of the query.

func (InsertBuilder) MustSql ¶ added in v1.5.1 

func (b InsertBuilder) MustSql() (string, []interface{})

MustSql builds the query into a SQL string and bound args. It panics if there are any errors.

func (InsertBuilder) Options ¶ 

func (b InsertBuilder) Options(options ...string) InsertBuilder

Options adds keyword options before the INTO clause of the query.

func (InsertBuilder) PlaceholderFormat ¶ 

func (b InsertBuilder) PlaceholderFormat(f PlaceholderFormat) InsertBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

func (InsertBuilder) Prefix ¶ 

func (b InsertBuilder) Prefix(sql string, args ...interface{}) InsertBuilder

Prefix adds an expression to the beginning of the query

func (InsertBuilder) PrefixExpr ¶ added in v1.2.0 

func (b InsertBuilder) PrefixExpr(expr Sqlizer) InsertBuilder

PrefixExpr adds an expression to the very beginning of the query

func (InsertBuilder) Query ¶ 

func (b InsertBuilder) Query() (*sql.Rows, error)

Query builds and Querys the query with the Runner set by RunWith.

func (InsertBuilder) QueryContext ¶ 

func (b InsertBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (InsertBuilder) QueryRow ¶ 

func (b InsertBuilder) QueryRow() RowScanner

QueryRow builds and QueryRows the query with the Runner set by RunWith.

func (InsertBuilder) QueryRowContext ¶ 

func (b InsertBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (InsertBuilder) RunWith ¶ 

func (b InsertBuilder) RunWith(runner BaseRunner) InsertBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec.

func (InsertBuilder) Scan ¶ 

func (b InsertBuilder) Scan(dest ...interface{}) error

Scan is a shortcut for QueryRow().Scan.

func (InsertBuilder) ScanContext ¶ 

func (b InsertBuilder) ScanContext(ctx context.Context, dest ...interface{}) error

ScanContext is a shortcut for QueryRowContext().Scan.

func (InsertBuilder) Select ¶ 

func (b InsertBuilder) Select(sb SelectBuilder) InsertBuilder

Select set Select clause for insert query If Values and Select are used, then Select has higher priority

func (InsertBuilder) SetMap ¶ 

func (b InsertBuilder) SetMap(clauses map[string]interface{}) InsertBuilder

SetMap set columns and values for insert builder from a map of column name and value note that it will reset all previous columns and values was set if any

func (InsertBuilder) Suffix ¶ 

func (b InsertBuilder) Suffix(sql string, args ...interface{}) InsertBuilder

Suffix adds an expression to the end of the query

func (InsertBuilder) SuffixExpr ¶ added in v1.2.0 

func (b InsertBuilder) SuffixExpr(expr Sqlizer) InsertBuilder

SuffixExpr adds an expression to the end of the query

func (InsertBuilder) ToSql ¶ 

func (b InsertBuilder) ToSql() (string, []interface{}, error)

ToSql builds the query into a SQL string and bound args.

func (InsertBuilder) Values ¶ 

func (b InsertBuilder) Values(values ...interface{}) InsertBuilder

Values adds a single row's values to the query.

type Like ¶ 

type Like map[string]interface{}

Like is syntactic sugar for use with LIKE conditions. Ex: 

.Where(Like{"name": "%irrel"})

func (Like) ToSql ¶ 

func (lk Like) ToSql() (sql string, args []interface{}, err error)

type Lt ¶ 

type Lt map[string]interface{}

Lt is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(Lt{"id": 1})

func (Lt) ToSql ¶ 

func (lt Lt) ToSql() (sql string, args []interface{}, err error)

type LtOrEq ¶ 

type LtOrEq Lt

LtOrEq is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(LtOrEq{"id": 1}) == "id <= 1"

func (LtOrEq) ToSql ¶ 

func (ltOrEq LtOrEq) ToSql() (sql string, args []interface{}, err error)

type NotEq ¶ 

type NotEq Eq

NotEq is syntactic sugar for use with Where/Having/Set methods. Ex: 

.Where(NotEq{"id": 1}) == "id <> 1"

func (NotEq) ToSql ¶ 

func (neq NotEq) ToSql() (sql string, args []interface{}, err error)

type NotILike ¶ added in v1.2.0 

type NotILike Like

NotILike is syntactic sugar for use with ILIKE conditions. Ex: 

.Where(NotILike{"name": "sq%"})

func (NotILike) ToSql ¶ added in v1.2.0 

func (nilk NotILike) ToSql() (sql string, args []interface{}, err error)

type NotLike ¶ 

type NotLike Like

NotLike is syntactic sugar for use with LIKE conditions. Ex: 

.Where(NotLike{"name": "%irrel"})

func (NotLike) ToSql ¶ 

func (nlk NotLike) ToSql() (sql string, args []interface{}, err error)

type Or ¶ 

type Or conj

Or conjunction Sqlizers

func (Or) ToSql ¶ 

func (o Or) ToSql() (string, []interface{}, error)

type PlaceholderFormat ¶ 

type PlaceholderFormat interface {
	ReplacePlaceholders(sql string) (string, error)
}

PlaceholderFormat is the interface that wraps the ReplacePlaceholders method.

ReplacePlaceholders takes a SQL statement and replaces each question mark placeholder with a (possibly different) SQL placeholder.

type Preparer ¶ 

type Preparer interface {
	Prepare(query string) (*sql.Stmt, error)
}

Prepareer is the interface that wraps the Prepare method.

Prepare executes the given query as implemented by database/sql.Prepare.

type PreparerContext ¶ 

type PreparerContext interface {
	Preparer
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

PrepareerContext is the interface that wraps the Prepare and PrepareContext methods.

Prepare executes the given query as implemented by database/sql.Prepare. PrepareContext executes the given query as implemented by database/sql.PrepareContext.

type QueryRower ¶ 

type QueryRower interface {
	QueryRow(query string, args ...interface{}) RowScanner
}

QueryRower is the interface that wraps the QueryRow method.

QueryRow executes the given query as implemented by database/sql.QueryRow.

type QueryRowerContext ¶ 

type QueryRowerContext interface {
	QueryRowContext(ctx context.Context, query string, args ...interface{}) RowScanner
}

QueryRowerContext is the interface that wraps the QueryRowContext method.

QueryRowContext executes the given query as implemented by database/sql.QueryRowContext.

type Queryer ¶ 

type Queryer interface {
	Query(query string, args ...interface{}) (*sql.Rows, error)
}

Queryer is the interface that wraps the Query method.

Query executes the given query as implemented by database/sql.Query.

type QueryerContext ¶ 

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

QueryerContext is the interface that wraps the QueryContext method.

QueryContext executes the given query as implemented by database/sql.QueryContext.

type Row ¶ 

type Row struct {
	RowScanner
	// contains filtered or unexported fields
}

Row wraps database/sql.Row to let squirrel return new errors on Scan.

func (*Row) Scan ¶ 

func (r *Row) Scan(dest ...interface{}) error

Scan returns Row.err or calls RowScanner.Scan.

type RowScanner ¶ 

type RowScanner interface {
	Scan(...interface{}) error
}

RowScanner is the interface that wraps the Scan method.

Scan behaves like database/sql.Row.Scan.

func QueryRowContextWith ¶ 

func QueryRowContextWith(ctx context.Context, db QueryRowerContext, s Sqlizer) RowScanner

QueryRowContextWith QueryRowContexts the SQL returned by s with db.

func QueryRowWith ¶ 

func QueryRowWith(db QueryRower, s Sqlizer) RowScanner

QueryRowWith QueryRows the SQL returned by s with db.

type Runner ¶ 

type Runner interface {
	Execer
	Queryer
	QueryRower
}

Runner groups the Execer, Queryer, and QueryRower interfaces.

func WrapStdSql ¶ added in v1.2.0 

func WrapStdSql(stdSql StdSql) Runner

WrapStdSql wraps a type implementing the standard SQL interface with methods that squirrel expects.

type RunnerContext ¶ added in v1.2.0 

type RunnerContext interface {
	Runner
	QueryerContext
	QueryRowerContext
	ExecerContext
}

RunnerContext groups the Runner interface, along with the Context versions of each of its methods

func WrapStdSqlCtx ¶ added in v1.2.0 

func WrapStdSqlCtx(stdSqlCtx StdSqlCtx) RunnerContext

WrapStdSqlCtx wraps a type implementing the standard SQL interface plus the context versions of the methods with methods that squirrel expects.

type SelectBuilder ¶ 

type SelectBuilder builder.Builder

SelectBuilder builds SQL SELECT statements.

func Select ¶ 

func Select(columns ...string) SelectBuilder

Select returns a new SelectBuilder, optionally setting some result columns.

See SelectBuilder.Columns.

Example ¶ 
Select("id", "created", "first_name").From("users") // ... continue building up your query

// sql methods in select columns are ok
Select("first_name", "count(*)").From("users")

// column aliases are ok too
Select("first_name", "count(*) as n_users").From("users")

func (SelectBuilder) Column ¶ 

func (b SelectBuilder) Column(column interface{}, args ...interface{}) SelectBuilder

Column adds a result column to the query. Unlike Columns, Column accepts args which will be bound to placeholders in the columns string, for example: 

Column("IF(col IN ("+squirrel.Placeholders(3)+"), 1, 0) as col", 1, 2, 3)

func (SelectBuilder) Columns ¶ 

func (b SelectBuilder) Columns(columns ...string) SelectBuilder

Columns adds result columns to the query.

Example ¶ 
query := Select("id").Columns("created", "first_name").From("users")

sql, _, _ := query.ToSql()
fmt.Println(sql)

Output:
SELECT id, created, first_name FROM users
Example (Order) ¶ 
// out of order is ok too
query := Select("id").Columns("created").From("users").Columns("first_name")

sql, _, _ := query.ToSql()
fmt.Println(sql)

Output:
SELECT id, created, first_name FROM users

func (SelectBuilder) CrossJoin ¶ added in v1.5.0 

func (b SelectBuilder) CrossJoin(join string, rest ...interface{}) SelectBuilder

CrossJoin adds a CROSS JOIN clause to the query.

func (SelectBuilder) Distinct ¶ 

func (b SelectBuilder) Distinct() SelectBuilder

Distinct adds a DISTINCT clause to the query.

func (SelectBuilder) Exec ¶ 

func (b SelectBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (SelectBuilder) ExecContext ¶ 

func (b SelectBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (SelectBuilder) From ¶ 

func (b SelectBuilder) From(from string) SelectBuilder

From sets the FROM clause of the query.

Example ¶ 
Select("id", "created", "first_name").From("users") // ... continue building up your query

func (SelectBuilder) FromSelect ¶ 

func (b SelectBuilder) FromSelect(from SelectBuilder, alias string) SelectBuilder

FromSelect sets a subquery into the FROM clause of the query.

Example ¶ 
usersByCompany := Select("company", "count(*) as n_users").From("users").GroupBy("company")
query := Select("company.id", "company.name", "users_by_company.n_users").
	FromSelect(usersByCompany, "users_by_company").
	Join("company on company.id = users_by_company.company")

sql, _, _ := query.ToSql()
fmt.Println(sql)

Output:
SELECT company.id, company.name, users_by_company.n_users FROM (SELECT company, count(*) as n_users FROM users GROUP BY company) AS users_by_company JOIN company on company.id = users_by_company.company

func (SelectBuilder) GroupBy ¶ 

func (b SelectBuilder) GroupBy(groupBys ...string) SelectBuilder

GroupBy adds GROUP BY expressions to the query.

func (SelectBuilder) Having ¶ 

func (b SelectBuilder) Having(pred interface{}, rest ...interface{}) SelectBuilder

Having adds an expression to the HAVING clause of the query.

See Where.

func (SelectBuilder) InnerJoin ¶ added in v1.5.0 

func (b SelectBuilder) InnerJoin(join string, rest ...interface{}) SelectBuilder

InnerJoin adds a INNER JOIN clause to the query.

func (SelectBuilder) Join ¶ 

func (b SelectBuilder) Join(join string, rest ...interface{}) SelectBuilder

Join adds a JOIN clause to the query.

func (SelectBuilder) JoinClause ¶ 

func (b SelectBuilder) JoinClause(pred interface{}, args ...interface{}) SelectBuilder

JoinClause adds a join clause to the query.

func (SelectBuilder) LeftJoin ¶ 

func (b SelectBuilder) LeftJoin(join string, rest ...interface{}) SelectBuilder

LeftJoin adds a LEFT JOIN clause to the query.

func (SelectBuilder) Limit ¶ 

func (b SelectBuilder) Limit(limit uint64) SelectBuilder

Limit sets a LIMIT clause on the query.

func (SelectBuilder) MustSql ¶ 

func (b SelectBuilder) MustSql() (string, []interface{})

MustSql builds the query into a SQL string and bound args. It panics if there are any errors.

func (SelectBuilder) Offset ¶ 

func (b SelectBuilder) Offset(offset uint64) SelectBuilder

Offset sets a OFFSET clause on the query.

func (SelectBuilder) Options ¶ 

func (b SelectBuilder) Options(options ...string) SelectBuilder

Options adds select option to the query

func (SelectBuilder) OrderBy ¶ 

func (b SelectBuilder) OrderBy(orderBys ...string) SelectBuilder

OrderBy adds ORDER BY expressions to the query.

func (SelectBuilder) OrderByClause ¶ added in v1.2.0 

func (b SelectBuilder) OrderByClause(pred interface{}, args ...interface{}) SelectBuilder

OrderByClause adds ORDER BY clause to the query.

func (SelectBuilder) PlaceholderFormat ¶ 

func (b SelectBuilder) PlaceholderFormat(f PlaceholderFormat) SelectBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

func (SelectBuilder) Prefix ¶ 

func (b SelectBuilder) Prefix(sql string, args ...interface{}) SelectBuilder

Prefix adds an expression to the beginning of the query

func (SelectBuilder) PrefixExpr ¶ added in v1.2.0 

func (b SelectBuilder) PrefixExpr(expr Sqlizer) SelectBuilder

PrefixExpr adds an expression to the very beginning of the query

func (SelectBuilder) Query ¶ 

func (b SelectBuilder) Query() (*sql.Rows, error)

Query builds and Querys the query with the Runner set by RunWith.

func (SelectBuilder) QueryContext ¶ 

func (b SelectBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (SelectBuilder) QueryRow ¶ 

func (b SelectBuilder) QueryRow() RowScanner

QueryRow builds and QueryRows the query with the Runner set by RunWith.

func (SelectBuilder) QueryRowContext ¶ 

func (b SelectBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (SelectBuilder) RemoveColumns ¶ added in v1.5.4 

func (b SelectBuilder) RemoveColumns() SelectBuilder

RemoveColumns remove all columns from query. Must add a new column with Column or Columns methods, otherwise return a error.

func (SelectBuilder) RemoveLimit ¶ 

func (b SelectBuilder) RemoveLimit() SelectBuilder

Limit ALL allows to access all records with limit

func (SelectBuilder) RemoveOffset ¶ added in v1.2.0 

func (b SelectBuilder) RemoveOffset() SelectBuilder

RemoveOffset removes OFFSET clause.

func (SelectBuilder) RightJoin ¶ 

func (b SelectBuilder) RightJoin(join string, rest ...interface{}) SelectBuilder

RightJoin adds a RIGHT JOIN clause to the query.

func (SelectBuilder) RunWith ¶ 

func (b SelectBuilder) RunWith(runner BaseRunner) SelectBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec. For most cases runner will be a database connection.

Internally we use this to mock out the database connection for testing.

Example ¶ 
var db *sql.DB

query := Select("id", "created", "first_name").From("users").RunWith(db)

var id int
var created time.Time
var firstName string

if err := query.Scan(&id, &created, &firstName); err != nil {
	log.Println(err)
	return
}

func (SelectBuilder) Scan ¶ 

func (b SelectBuilder) Scan(dest ...interface{}) error

Scan is a shortcut for QueryRow().Scan.

Example ¶ 
var db *sql.DB

query := Select("id", "created", "first_name").From("users")
query = query.RunWith(db)

var id int
var created time.Time
var firstName string

if err := query.Scan(&id, &created, &firstName); err != nil {
	log.Println(err)
	return
}

func (SelectBuilder) ScanContext ¶ 

func (b SelectBuilder) ScanContext(ctx context.Context, dest ...interface{}) error

ScanContext is a shortcut for QueryRowContext().Scan.

Example ¶ 
var db *sql.DB

query := Select("id", "created", "first_name").From("users")
query = query.RunWith(db)

var id int
var created time.Time
var firstName string

if err := query.ScanContext(ctx, &id, &created, &firstName); err != nil {
	log.Println(err)
	return
}

func (SelectBuilder) Suffix ¶ 

func (b SelectBuilder) Suffix(sql string, args ...interface{}) SelectBuilder

Suffix adds an expression to the end of the query

func (SelectBuilder) SuffixExpr ¶ added in v1.2.0 

func (b SelectBuilder) SuffixExpr(expr Sqlizer) SelectBuilder

SuffixExpr adds an expression to the end of the query

func (SelectBuilder) ToSql ¶ 

func (b SelectBuilder) ToSql() (string, []interface{}, error)

ToSql builds the query into a SQL string and bound args.

Example ¶ 
var db *sql.DB

query := Select("id", "created", "first_name").From("users")

sql, args, err := query.ToSql()
if err != nil {
	log.Println(err)
	return
}

rows, err := db.Query(sql, args...)
if err != nil {
	log.Println(err)
	return
}

defer rows.Close()

for rows.Next() {
	// scan...
}

func (SelectBuilder) Where ¶ 

func (b SelectBuilder) Where(pred interface{}, args ...interface{}) SelectBuilder

Where adds an expression to the WHERE clause of the query.

Expressions are ANDed together in the generated SQL.

Where accepts several types for its pred argument:

nil OR "" - ignored.

string - SQL expression. If the expression has SQL placeholders then a set of arguments must be passed as well, one for each placeholder.

map[string]interface{} OR Eq - map of SQL expressions to values. Each key is transformed into an expression like "<key> = ?", with the corresponding value bound to the placeholder. If the value is nil, the expression will be "<key> IS NULL". If the value is an array or slice, the expression will be "<key> IN (?,?,...)", with one placeholder for each item in the value. These expressions are ANDed together.

Where will panic if pred isn't any of the above types.

Example ¶ 
companyId := 20
Select("id", "created", "first_name").From("users").Where("company = ?", companyId)
Example (Helpers) ¶ 
companyId := 20

Select("id", "created", "first_name").From("users").Where(Eq{
	"company": companyId,
})

Select("id", "created", "first_name").From("users").Where(GtOrEq{
	"created": time.Now().AddDate(0, 0, -7),
})

Select("id", "created", "first_name").From("users").Where(And{
	GtOrEq{
		"created": time.Now().AddDate(0, 0, -7),
	},
	Eq{
		"company": companyId,
	},
})
Example (Multiple) ¶ 
companyId := 20

// multiple where's are ok

Select("id", "created", "first_name").
	From("users").
	Where("company = ?", companyId).
	Where(GtOrEq{
		"created": time.Now().AddDate(0, 0, -7),
	})

type Sqlizer ¶ 

type Sqlizer interface {
	ToSql() (string, []interface{}, error)
}

Sqlizer is the interface that wraps the ToSql method.

ToSql returns a SQL representation of the Sqlizer, along with a slice of args as passed to e.g. database/sql.Exec. It can also return an error.

func Expr ¶ 

func Expr(sql string, args ...interface{}) Sqlizer

Expr builds an expression from a SQL fragment and arguments.

Ex: 

Expr("FROM_UNIXTIME(?)", t)

type StatementBuilderType ¶ 

type StatementBuilderType builder.Builder

StatementBuilderType is the type of StatementBuilder.

func (StatementBuilderType) Delete ¶ 

func (b StatementBuilderType) Delete(from string) DeleteBuilder

Delete returns a DeleteBuilder for this StatementBuilderType.

func (StatementBuilderType) Insert ¶ 

func (b StatementBuilderType) Insert(into string) InsertBuilder

Insert returns a InsertBuilder for this StatementBuilderType.

func (StatementBuilderType) PlaceholderFormat ¶ 

func (b StatementBuilderType) PlaceholderFormat(f PlaceholderFormat) StatementBuilderType

PlaceholderFormat sets the PlaceholderFormat field for any child builders.

func (StatementBuilderType) Replace ¶ added in v1.2.0 

func (b StatementBuilderType) Replace(into string) InsertBuilder

Replace returns a InsertBuilder for this StatementBuilderType with the statement keyword set to "REPLACE".

func (StatementBuilderType) RunWith ¶ 

func (b StatementBuilderType) RunWith(runner BaseRunner) StatementBuilderType

RunWith sets the RunWith field for any child builders.

func (StatementBuilderType) Select ¶ 

func (b StatementBuilderType) Select(columns ...string) SelectBuilder

Select returns a SelectBuilder for this StatementBuilderType.

func (StatementBuilderType) Update ¶ 

func (b StatementBuilderType) Update(table string) UpdateBuilder

Update returns a UpdateBuilder for this StatementBuilderType.

func (StatementBuilderType) Where ¶ added in v1.4.0 

func (b StatementBuilderType) Where(pred interface{}, args ...interface{}) StatementBuilderType

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

type StdSql ¶ added in v1.2.0 

type StdSql interface {
	Query(string, ...interface{}) (*sql.Rows, error)
	QueryRow(string, ...interface{}) *sql.Row
	Exec(string, ...interface{}) (sql.Result, error)
}

StdSql encompasses the standard methods of the *sql.DB type, and other types that wrap these methods.

type StdSqlCtx ¶ added in v1.2.0 

type StdSqlCtx interface {
	StdSql
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
}

StdSqlCtx encompasses the standard methods of the *sql.DB type, along with the Context versions of those methods, and other types that wrap these methods.

type StmtCache ¶ added in v1.2.0 

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

StmtCache wraps and delegates down to a Preparer type

It also automatically prepares all statements sent to the underlying Preparer calls for Exec, Query and QueryRow and caches the returns *sql.Stmt using the provided query as the key. So that it can be automatically re-used.

func NewStmtCache ¶ added in v1.2.0 

func NewStmtCache(prep PreparerContext) *StmtCache

NewStmtCache returns a *StmtCache wrapping a PreparerContext that caches Prepared Stmts.

Stmts are cached based on the string value of their queries.

func (*StmtCache) Clear ¶ added in v1.2.0 

func (sc *StmtCache) Clear() (err error)

Clear removes and closes all the currently cached prepared statements

func (*StmtCache) Exec ¶ added in v1.2.0 

func (sc *StmtCache) Exec(query string, args ...interface{}) (res sql.Result, err error)

Exec delegates down to the underlying Preparer using a prepared statement

func (*StmtCache) ExecContext ¶ added in v1.2.0 

func (sc *StmtCache) ExecContext(ctx context.Context, query string, args ...interface{}) (res sql.Result, err error)

ExecContext delegates down to the underlying PreparerContext using a prepared statement

func (*StmtCache) Prepare ¶ added in v1.2.0 

func (sc *StmtCache) Prepare(query string) (*sql.Stmt, error)

Prepare delegates down to the underlying Preparer and caches the result using the provided query as a key

func (*StmtCache) PrepareContext ¶ added in v1.2.0 

func (sc *StmtCache) PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)

PrepareContext delegates down to the underlying PreparerContext and caches the result using the provided query as a key

func (*StmtCache) Query ¶ added in v1.2.0 

func (sc *StmtCache) Query(query string, args ...interface{}) (rows *sql.Rows, err error)

Query delegates down to the underlying Preparer using a prepared statement

func (*StmtCache) QueryContext ¶ added in v1.2.0 

func (sc *StmtCache) QueryContext(ctx context.Context, query string, args ...interface{}) (rows *sql.Rows, err error)

QueryContext delegates down to the underlying PreparerContext using a prepared statement

func (*StmtCache) QueryRow ¶ added in v1.2.0 

func (sc *StmtCache) QueryRow(query string, args ...interface{}) RowScanner

QueryRow delegates down to the underlying Preparer using a prepared statement

func (*StmtCache) QueryRowContext ¶ added in v1.2.0 

func (sc *StmtCache) QueryRowContext(ctx context.Context, query string, args ...interface{}) RowScanner

QueryRowContext delegates down to the underlying PreparerContext using a prepared statement

type UpdateBuilder ¶ 

type UpdateBuilder builder.Builder

UpdateBuilder builds SQL UPDATE statements.

func Update ¶ 

func Update(table string) UpdateBuilder

Update returns a new UpdateBuilder with the given table name.

See UpdateBuilder.Table.

func (UpdateBuilder) Exec ¶ 

func (b UpdateBuilder) Exec() (sql.Result, error)

Exec builds and Execs the query with the Runner set by RunWith.

func (UpdateBuilder) ExecContext ¶ 

func (b UpdateBuilder) ExecContext(ctx context.Context) (sql.Result, error)

ExecContext builds and ExecContexts the query with the Runner set by RunWith.

func (UpdateBuilder) From ¶ added in v1.5.4 

func (b UpdateBuilder) From(from string) UpdateBuilder

From adds FROM clause to the query FROM is valid construct in postgresql only.

func (UpdateBuilder) FromSelect ¶ added in v1.5.4 

func (b UpdateBuilder) FromSelect(from SelectBuilder, alias string) UpdateBuilder

FromSelect sets a subquery into the FROM clause of the query.

func (UpdateBuilder) Limit ¶ 

func (b UpdateBuilder) Limit(limit uint64) UpdateBuilder

Limit sets a LIMIT clause on the query.

func (UpdateBuilder) MustSql ¶ added in v1.5.1 

func (b UpdateBuilder) MustSql() (string, []interface{})

MustSql builds the query into a SQL string and bound args. It panics if there are any errors.

func (UpdateBuilder) Offset ¶ 

func (b UpdateBuilder) Offset(offset uint64) UpdateBuilder

Offset sets a OFFSET clause on the query.

func (UpdateBuilder) OrderBy ¶ 

func (b UpdateBuilder) OrderBy(orderBys ...string) UpdateBuilder

OrderBy adds ORDER BY expressions to the query.

func (UpdateBuilder) PlaceholderFormat ¶ 

func (b UpdateBuilder) PlaceholderFormat(f PlaceholderFormat) UpdateBuilder

PlaceholderFormat sets PlaceholderFormat (e.g. Question or Dollar) for the query.

func (UpdateBuilder) Prefix ¶ 

func (b UpdateBuilder) Prefix(sql string, args ...interface{}) UpdateBuilder

Prefix adds an expression to the beginning of the query

func (UpdateBuilder) PrefixExpr ¶ added in v1.2.0 

func (b UpdateBuilder) PrefixExpr(expr Sqlizer) UpdateBuilder

PrefixExpr adds an expression to the very beginning of the query

func (UpdateBuilder) Query ¶ 

func (b UpdateBuilder) Query() (*sql.Rows, error)

func (UpdateBuilder) QueryContext ¶ 

func (b UpdateBuilder) QueryContext(ctx context.Context) (*sql.Rows, error)

QueryContext builds and QueryContexts the query with the Runner set by RunWith.

func (UpdateBuilder) QueryRow ¶ 

func (b UpdateBuilder) QueryRow() RowScanner

func (UpdateBuilder) QueryRowContext ¶ 

func (b UpdateBuilder) QueryRowContext(ctx context.Context) RowScanner

QueryRowContext builds and QueryRowContexts the query with the Runner set by RunWith.

func (UpdateBuilder) RunWith ¶ 

func (b UpdateBuilder) RunWith(runner BaseRunner) UpdateBuilder

RunWith sets a Runner (like database/sql.DB) to be used with e.g. Exec.

func (UpdateBuilder) Scan ¶ 

func (b UpdateBuilder) Scan(dest ...interface{}) error

func (UpdateBuilder) ScanContext ¶ 

func (b UpdateBuilder) ScanContext(ctx context.Context, dest ...interface{}) error

ScanContext is a shortcut for QueryRowContext().Scan.

func (UpdateBuilder) Set ¶ 

func (b UpdateBuilder) Set(column string, value interface{}) UpdateBuilder

Set adds SET clauses to the query.

func (UpdateBuilder) SetMap ¶ 

func (b UpdateBuilder) SetMap(clauses map[string]interface{}) UpdateBuilder

SetMap is a convenience method which calls .Set for each key/value pair in clauses.

func (UpdateBuilder) Suffix ¶ 

func (b UpdateBuilder) Suffix(sql string, args ...interface{}) UpdateBuilder

Suffix adds an expression to the end of the query

func (UpdateBuilder) SuffixExpr ¶ added in v1.2.0 

func (b UpdateBuilder) SuffixExpr(expr Sqlizer) UpdateBuilder

SuffixExpr adds an expression to the end of the query

func (UpdateBuilder) Table ¶ 

func (b UpdateBuilder) Table(table string) UpdateBuilder

Table sets the table to be updated.

func (UpdateBuilder) ToSql ¶ 

func (b UpdateBuilder) ToSql() (string, []interface{}, error)

ToSql builds the query into a SQL string and bound args.

func (UpdateBuilder) Where ¶ 

func (b UpdateBuilder) Where(pred interface{}, args ...interface{}) UpdateBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

Source Files ¶

View all Source files

Directories ¶

Path Synopsis
integration module

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.