Documentation ¶
Overview ¶
Package squirrelly provides a fluent SQL generator with an integration with database/sql.
Based on https://github.com/Masterminds/squirrel.
Index ¶
- Variables
- func Alias(expr Sqlizer, alias string) aliasExpr
- func ConcatExpr(parts ...interface{}) concatExpr
- func DbGet(db DbLike, query Sqlizer, data any) error
- func DbGetAll(db DbLike, query Sqlizer, container any) error
- func DebugSqlizer(s Sqlizer) string
- func Exec(db Querier, query Sqlizer) (sql.Result, error)
- func Placeholders(count int) string
- func Query(db Querier, query Sqlizer) (*sql.Rows, error)
- func QueryRow(db Querier, query Sqlizer) *sql.Row
- type And
- type CaseBuilder
- type Db
- func (db *Db) Begin() (*Tx, error)
- func (db *Db) Exec(query Sqlizer) (sql.Result, error)
- func (db *Db) Get(query Sqlizer, data interface{}) error
- func (db *Db) GetAll(query Sqlizer, container interface{}) error
- func (db *Db) Query(query Sqlizer) (*sql.Rows, error)
- func (db *Db) QueryRow(query Sqlizer) *sql.Row
- func (db *Db) WithTx(fn func(DbLike) error) error
- type DbLike
- type DeleteBuilder
- func (b DeleteBuilder) From(from string) DeleteBuilder
- func (b DeleteBuilder) Limit(limit uint64) DeleteBuilder
- func (b DeleteBuilder) MustSql() (string, []interface{})
- func (b DeleteBuilder) Offset(offset uint64) DeleteBuilder
- func (b DeleteBuilder) OrderBy(orderBys ...string) DeleteBuilder
- func (b DeleteBuilder) PlaceholderFormat(f PlaceholderFormat) DeleteBuilder
- func (b DeleteBuilder) Prefix(sql string, args ...interface{}) DeleteBuilder
- func (b DeleteBuilder) PrefixExpr(expr Sqlizer) DeleteBuilder
- func (b DeleteBuilder) Suffix(sql string, args ...interface{}) DeleteBuilder
- func (b DeleteBuilder) SuffixExpr(expr Sqlizer) DeleteBuilder
- func (b DeleteBuilder) ToSql() (string, []interface{}, error)
- func (b DeleteBuilder) Where(pred interface{}, args ...interface{}) DeleteBuilder
- type Eq
- type Gt
- type GtOrEq
- type ILike
- type InsertBuilder
- func (b InsertBuilder) Columns(columns ...string) InsertBuilder
- func (b InsertBuilder) Into(from string) InsertBuilder
- func (b InsertBuilder) MustSql() (string, []interface{})
- func (b InsertBuilder) OnConflict(conflictKeys ...string) InsertBuilder
- func (b InsertBuilder) Options(options ...string) InsertBuilder
- func (b InsertBuilder) PlaceholderFormat(f PlaceholderFormat) InsertBuilder
- func (b InsertBuilder) Prefix(sql string, args ...interface{}) InsertBuilder
- func (b InsertBuilder) PrefixExpr(expr Sqlizer) InsertBuilder
- func (b InsertBuilder) Returning(columns ...string) InsertBuilder
- func (b InsertBuilder) Select(sb SelectBuilder) InsertBuilder
- func (b InsertBuilder) SetMap(clauses map[string]interface{}) InsertBuilder
- func (b InsertBuilder) Struct(data interface{}) InsertBuilder
- func (b InsertBuilder) StructValues(data interface{}) InsertBuilder
- func (b InsertBuilder) Suffix(sql string, args ...interface{}) InsertBuilder
- func (b InsertBuilder) SuffixExpr(expr Sqlizer) InsertBuilder
- func (b InsertBuilder) ToSql() (string, []interface{}, error)
- func (b InsertBuilder) UpdateColumns(columns ...string) InsertBuilder
- func (b InsertBuilder) Values(values ...interface{}) InsertBuilder
- type Like
- type Lt
- type LtOrEq
- type NotEq
- type NotILike
- type NotLike
- type Or
- type PlaceholderFormat
- type Querier
- type SelectBuilder
- func (b SelectBuilder) Column(column interface{}, args ...interface{}) SelectBuilder
- func (b SelectBuilder) Columns(columns ...string) SelectBuilder
- func (b SelectBuilder) CrossJoin(join string, rest ...interface{}) SelectBuilder
- func (b SelectBuilder) Distinct() SelectBuilder
- func (b SelectBuilder) From(from string) SelectBuilder
- func (b SelectBuilder) FromSelect(from SelectBuilder, alias string) SelectBuilder
- func (b SelectBuilder) GroupBy(groupBys ...string) SelectBuilder
- func (b SelectBuilder) Having(pred interface{}, rest ...interface{}) SelectBuilder
- func (b SelectBuilder) InnerJoin(join string, rest ...interface{}) SelectBuilder
- func (b SelectBuilder) Join(join string, rest ...interface{}) SelectBuilder
- func (b SelectBuilder) JoinClause(pred interface{}, args ...interface{}) SelectBuilder
- func (b SelectBuilder) LeftJoin(join string, rest ...interface{}) SelectBuilder
- func (b SelectBuilder) Limit(limit uint64) SelectBuilder
- func (b SelectBuilder) MustSql() (string, []interface{})
- func (b SelectBuilder) Offset(offset uint64) SelectBuilder
- func (b SelectBuilder) Options(options ...string) SelectBuilder
- func (b SelectBuilder) OrderBy(orderBys ...string) SelectBuilder
- func (b SelectBuilder) OrderByClause(pred interface{}, args ...interface{}) SelectBuilder
- func (b SelectBuilder) PlaceholderFormat(f PlaceholderFormat) SelectBuilder
- func (b SelectBuilder) Prefix(sql string, args ...interface{}) SelectBuilder
- func (b SelectBuilder) PrefixExpr(expr Sqlizer) SelectBuilder
- func (b SelectBuilder) RemoveColumns() SelectBuilder
- func (b SelectBuilder) RemoveLimit() SelectBuilder
- func (b SelectBuilder) RemoveOffset() SelectBuilder
- func (b SelectBuilder) RightJoin(join string, rest ...interface{}) SelectBuilder
- func (b SelectBuilder) Suffix(sql string, args ...interface{}) SelectBuilder
- func (b SelectBuilder) SuffixExpr(expr Sqlizer) SelectBuilder
- func (b SelectBuilder) ToSql() (string, []interface{}, error)
- func (b SelectBuilder) Where(pred interface{}, args ...interface{}) SelectBuilder
- type Sqlizer
- type StatementBuilderType
- func (b StatementBuilderType) Delete(from string) DeleteBuilder
- func (b StatementBuilderType) Insert(into string) InsertBuilder
- func (b StatementBuilderType) PlaceholderFormat(f PlaceholderFormat) StatementBuilderType
- func (b StatementBuilderType) Replace(into string) InsertBuilder
- func (b StatementBuilderType) Select(columns ...string) SelectBuilder
- func (b StatementBuilderType) Update(table string) UpdateBuilder
- func (b StatementBuilderType) Where(pred interface{}, args ...interface{}) StatementBuilderType
- type Tx
- type UpdateBuilder
- func (b UpdateBuilder) From(from string) UpdateBuilder
- func (b UpdateBuilder) FromSelect(from SelectBuilder, alias string) UpdateBuilder
- func (b UpdateBuilder) Limit(limit uint64) UpdateBuilder
- func (b UpdateBuilder) MustSql() (string, []interface{})
- func (b UpdateBuilder) Offset(offset uint64) UpdateBuilder
- func (b UpdateBuilder) OrderBy(orderBys ...string) UpdateBuilder
- func (b UpdateBuilder) PlaceholderFormat(f PlaceholderFormat) UpdateBuilder
- func (b UpdateBuilder) Prefix(sql string, args ...interface{}) UpdateBuilder
- func (b UpdateBuilder) PrefixExpr(expr Sqlizer) UpdateBuilder
- func (b UpdateBuilder) Set(column string, value interface{}) UpdateBuilder
- func (b UpdateBuilder) SetMap(clauses map[string]interface{}) UpdateBuilder
- func (b UpdateBuilder) Suffix(sql string, args ...interface{}) UpdateBuilder
- func (b UpdateBuilder) SuffixExpr(expr Sqlizer) UpdateBuilder
- func (b UpdateBuilder) Table(table string) UpdateBuilder
- func (b UpdateBuilder) ToSql() (string, []interface{}, error)
- func (b UpdateBuilder) Where(pred interface{}, args ...interface{}) UpdateBuilder
Examples ¶
Constants ¶
This section is empty.
Variables ¶
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{} )
var StatementBuilder = StatementBuilderType(builder.EmptyBuilder).PlaceholderFormat(Question)
StatementBuilder is a parent builder for other builders, e.g. SelectBuilder.
Functions ¶
func Alias ¶
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 ¶
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 ¶
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 Exec ¶
Exec runs database/sql.DB.Exec, using a squirrelly builder.
func Placeholders ¶
Placeholders returns a string with count ? placeholders joined with commas.
func Query ¶
Query runs database/sql.DB.Query using a squirrelly builder.
Types ¶
type CaseBuilder ¶
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 ¶
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 Db ¶
Type Db is a wrapper around sql.Db
Example ¶
package main import ( "fmt" sq "github.com/sleepdeprecation/squirrelly" _ "modernc.org/sqlite" ) func main() { // open a sqlite database in memory db, _ := sq.Open("sqlite", "file::memory:") // initialize a basic table // you can use db.DB to access the *sqlx.DB methods directly // (these match the *sql.Db methods, with some additions). db.DB.Exec("CREATE TABLE example (pk INTEGER PRIMARY KEY AUTOINCREMENT, comment TEXT NOT NULL)") type Example struct { Pk int `sq:"pk"` Comment string `sq:"comment"` } // build some queries insert := sq.Insert("example").Columns("comment").Values("first").Values("second").Values("third") _, err := db.Exec(insert) if err != nil { panic(err) } values := []*Example{} query := sq.Select("*").From("example") err = db.GetAll(query, &values) fmt.Printf("%+v\n", values) }
Output:
func Open ¶
Open uses the same convention as database/sql.Open, a driver name and a source string, both dependant on your driver's package.
func (*Db) Exec ¶
Exec runs database/sql.DB.Exec, using a squirrelly builder.
func (*Db) Get ¶
Get runs a query using a squirrelly builder (that should return one and only one result), and marshals the result into the data interface.
The data argument must be a pointer, it supports any value that database/sql.Rows.Scan supports, or structs that are tagged using the `sq` tag, similar to how the encoding/json.Marshal function works using the `json` tag.
func (*Db) GetAll ¶
GetAll runs a query using a squirrelly builder, and marshals the resulting records into the data interface.
The container argument must be a pointer to a slice, the slice may be of any value that database/sql.Rows.Scan supports, or structs that are tagged using the `sq` tag, similar to how the encoding/json.Marshal function works using the `json` tag.
func (*Db) Query ¶
Query runs database/sql.DB.Query using a squirrelly builder.
func (*Db) QueryRow ¶
QueryRow runs database/sql.DB.QueryRow using a squirrelly builder.
type DbLike ¶
type DbLike interface { Exec(Sqlizer) (sql.Result, error) Query(Sqlizer) (*sql.Rows, error) QueryRow(Sqlizer) *sql.Row Get(Sqlizer, any) error GetAll(Sqlizer, any) error }
interface for squirrelly db like structs
type DeleteBuilder ¶
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) 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 ¶
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 ¶
func (b DeleteBuilder) PrefixExpr(expr Sqlizer) DeleteBuilder
PrefixExpr adds an expression to the very beginning of the query
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 ¶
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:
type Gt ¶
type Gt Lt
Gt is syntactic sugar for use with Where/Having/Set methods. Ex:
.Where(Gt{"id": 1}) == "id > 1"
type GtOrEq ¶
type GtOrEq Lt
GtOrEq is syntactic sugar for use with Where/Having/Set methods. Ex:
.Where(GtOrEq{"id": 1}) == "id >= 1"
type ILike ¶
type ILike Like
ILike is syntactic sugar for use with ILIKE conditions. Ex:
.Where(ILike{"name": "sq%"})
type InsertBuilder ¶
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 ¶
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) Into ¶
func (b InsertBuilder) Into(from string) InsertBuilder
Into sets the INTO clause of the query.
func (InsertBuilder) MustSql ¶
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) OnConflict ¶
func (b InsertBuilder) OnConflict(conflictKeys ...string) InsertBuilder
OnConflict is used to turn an insert into an upsert. This is used to add the ON CONFLICT (keys ...) clause. When used with InsertBuilder.UpdateColumns the insert builder adds ON CONFLICT (keys ...) DO UPDATE SET ....
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 ¶
func (b InsertBuilder) PrefixExpr(expr Sqlizer) InsertBuilder
PrefixExpr adds an expression to the very beginning of the query
func (InsertBuilder) Returning ¶
func (b InsertBuilder) Returning(columns ...string) InsertBuilder
Returning adds a RETURNING <columns> suffix (before the InsertBuilder.Suffix) to the insert builder.
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) Struct ¶
func (b InsertBuilder) Struct(data interface{}) InsertBuilder
Struct sets the columns and values for an insert builder based on a struct's tags
For example
type Foo struct { A string `sq:"a"` B string `sq:"column_b"` } record := Foo{A: "a value", B: "another value"} // these lines have the same result Insert("table").Struct(&record) Insert("table").Columns("a", "b").StructValues(&record)
func (InsertBuilder) StructValues ¶
func (b InsertBuilder) StructValues(data interface{}) InsertBuilder
StructValues sets values for an insert builder based on the columns already set and the "sq" tag on the struct's value. For example
s := struct{A `sq:"a"`}{A: "foo"} Insert().Columns("a").StructValues(&s) // is equivalent to Insert().Columns("a").Values(s.A)
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 ¶
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) UpdateColumns ¶
func (b InsertBuilder) UpdateColumns(columns ...string) InsertBuilder
UpdateColumns, when used with InsertBuilder.OnConflict, generates ON CONFLICT DO UPDATE clauses to the insert builder.
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"})
type Lt ¶
type Lt map[string]interface{}
Lt is syntactic sugar for use with Where/Having/Set methods. Ex:
.Where(Lt{"id": 1})
type LtOrEq ¶
type LtOrEq Lt
LtOrEq is syntactic sugar for use with Where/Having/Set methods. Ex:
.Where(LtOrEq{"id": 1}) == "id <= 1"
type NotEq ¶
type NotEq Eq
NotEq is syntactic sugar for use with Where/Having/Set methods. Ex:
.Where(NotEq{"id": 1}) == "id <> 1"
type NotILike ¶
type NotILike Like
NotILike is syntactic sugar for use with ILIKE conditions. Ex:
.Where(NotILike{"name": "sq%"})
type NotLike ¶
type NotLike Like
NotLike is syntactic sugar for use with LIKE conditions. Ex:
.Where(NotLike{"name": "%irrel"})
type PlaceholderFormat ¶
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 Querier ¶
type Querier interface { Exec(string, ...any) (sql.Result, error) Query(string, ...any) (*sql.Rows, error) QueryRow(string, ...any) *sql.Row }
interface for database/sql db like structs
type SelectBuilder ¶
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 ¶
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) 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 ¶
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 ¶
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 ¶
func (b SelectBuilder) PrefixExpr(expr Sqlizer) SelectBuilder
PrefixExpr adds an expression to the very beginning of the query
func (SelectBuilder) RemoveColumns ¶
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 ¶
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) Suffix ¶
func (b SelectBuilder) Suffix(sql string, args ...interface{}) SelectBuilder
Suffix adds an expression to the end of the query
func (SelectBuilder) SuffixExpr ¶
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 ¶
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.
type StatementBuilderType ¶
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 ¶
func (b StatementBuilderType) Replace(into string) InsertBuilder
Replace returns a InsertBuilder for this StatementBuilderType with the statement keyword set to "REPLACE".
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 ¶
func (b StatementBuilderType) Where(pred interface{}, args ...interface{}) StatementBuilderType
Where adds WHERE expressions to the query.
See SelectBuilder.Where for more information.
type UpdateBuilder ¶
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) From ¶
func (b UpdateBuilder) From(from string) UpdateBuilder
From adds FROM clause to the query FROM is valid construct in postgresql only.
func (UpdateBuilder) FromSelect ¶
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 ¶
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 ¶
func (b UpdateBuilder) PrefixExpr(expr Sqlizer) UpdateBuilder
PrefixExpr adds an expression to the very beginning of the query
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 ¶
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.