squirrel

package module
Version: v1.5.2 Latest Latest
Warning

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

Go to latest
Published: Nov 12, 2021 License: MIT Imports: 12 Imported by: 863

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,
})
Output:

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

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 Contect 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")
Output:

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
Output:

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) 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
}
Output:

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
}
Output:

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
}
Output:

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...
}
Output:

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)
Output:

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,
	},
})
Output:

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),
	})
Output:

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

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

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) 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.

Directories

Path Synopsis
integration module

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL