squirrel

package module
v1.4.1 Latest Latest
Warning

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

 Go to latest Published: Apr 18, 2024 License: MIT Imports: 11 Imported by: 0

README

Go Reference Go Coverage CI Status Stability Go Report

Evolution of github.com/Masterminds/squirrel, which unfortunately has not been updated by the author for a long time

Contains breaking changes and new features (see below).

Squirrel - fluent SQL generator for Go

import "github.com/n-r-w/squirrel"

Squirrel is not an ORM.

Squirrel helps you build SQL queries from composable parts:

import sq "github.com/n-r-w/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 makes conditional query building a breeze:

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

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

    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)

Breaking changes in comparison to the original github.com/Masterminds/squirrel

Removed all database interaction methods. Only query building functions are left

Squirrel is now a pure SQL query builder. For database interaction, use:

Changes in the Case method
  • To pass an integer value to the When and Else methods, you need to pass it as an int, not as a string.
  • To pass a string value to the When and Else methods, you don't need to add quotes.

Before:

sq.Case("id").When(1, "2").When(2, "'text'").Else("4")

After:

sq.Case("id").When(1, 2).When(2, "text").Else(4)

New features

Subquery support for WHERE clause
Select("id", "name").From("users").Where(Eq{"id": Select("id").From("other_table")}).ToSql()
// SELECT id, name FROM users WHERE id IN (SELECT id1 FROM other_table)
Support for integer values in CASE THEN/ELSE clause
Select("id", "name").From("users").Where(Case("id").When(1, 2).When(2, 3).Else(4))
// SELECT id, name FROM users WHERE CASE id WHEN 1 THEN 2 WHEN 2 THEN 3 ELSE 4 END
Support for aggregate functions SUM, COUNT, AVG, MIN, MAX
sq.Sum(subQuery)
Support for using slice as argument for Column function
Column(sq.Expr("id = ANY(?)", []int{1,2,3}))
Support for IN, NOT and NOT IN clause
In("id", []int{1, 2, 3}) // id=ANY(ARRAY[1,2,3])
NotIn("id", subQuery) // id NOT IN (<subQuery>)

Not(Select("col").From("table")) // NOT (SELECT col FROM table)
// double NOT is removed
Not(Not(Select("col").From("table"))) // SELECT col FROM table
Equal, NotEqual, Greater, GreaterOrEqual, Less, LessOrEqual functions
Equal(Select("col").From("table"), 1) // (SELECT col FROM table) = 1
NotEqual(Select("col").From("table"), 1) // (SELECT col FROM table) != 1
Greater(Select("col").From("table"), 1) // (SELECT col FROM table) > 1
GreaterOrEqual(Select("col").From("table"), 1) // (SELECT col FROM table) >= 1
Less(Select("col").From("table"), 1) // (SELECT col FROM table) < 1
LessOrEqual(Select("col").From("table"), 1) // (SELECT col FROM table) <= 1
Range function
sq.Range("id", 1, 10) // id BETWEEN 1 AND 10
sq.Range("id", 1, nil) //id >= 1
sq.Range("id", nil, 10) // id <= 10
EqNotEmpty function: ignores empty and zero values in Eq map. Useful for filtering
EqNotEmpty{"id1": 1, "name": nil, id2: 0, "desc": ""} // id1 = 1
OrderByCond function: can be used to avoid hardcoding column names in the code
columns := map[int]string{1: "id", 2: "created"}
orderConds := []OrderCond{{1, Asc}, {2, Desc}, {1, Desc}} // duplicate should be ignored

Select("id").From("users").OrderByCond(columns, orderConds)
// SELECT id FROM users ORDER BY id ASC, created DESC
Search function

The search condition is a WHERE clause with LIKE expressions. All columns will be converted to text. Value can be a string or a number.

Select("id", "name").From("users").Search("John", "name", "email")
// SELECT id, name FROM users WHERE (name::text LIKE ? OR email::text LIKE ?)  
// args = ["%John%", "%John%"]
PaginateByID: adds a LIMIT and start from ID condition to the query. WARNING: The columnID must be included in the ORDER BY clause to avoid unexpected results
Select("id", "name").From("users").PaginateByID(10, 20, "id").OrderBy("id ASC")
// SELECT id, name FROM users WHERE id > ? ORDER BY id ASC LIMIT 10
// args = [20]
PaginateByPage: adds a LIMIT and OFFSET to the query. WARNING: The columnID must be included in the ORDER BY clause to avoid unexpected results
Select("id", "name").From("users").PaginateByPage(10, 3).OrderBy("id ASC")
// SELECT id, name FROM users ORDER BY id ASC LIMIT 10 OFFSET 20
Paginate: allows you to use separated Paginator object to paginate the query

It's useful when you want to use the same Paginator object in different application layers. In following example, SetIDColumn method is used to specify the column name that will be used to paginate the query. If not set, error will be returned. It's required for combination with Paginate and PaginatorByID methods.

Select("id", "name").From("users").Paginate(PaginatorByID(10, 20)).SetIDColumn("id").OrderBy("id ASC")
// SELECT id, name FROM users WHERE id > ? ORDER BY id ASC LIMIT 10

Select("id", "name").From("users").Paginate(PaginatorByPage(10, 3)).OrderBy("id ASC")
// SELECT id, name FROM users ORDER BY id ASC LIMIT 10 OFFSET 20
Alias for Select statement: allows to use table alias in the query for multiple columns and add prefix to the column names if needed
Select().
Alias("u").Columns("id", "name").
From("users u")
Alias("u").GroupBy("id", "name").
Alias("u").OrderBy("id").
// SELECT u.id, u.name FROM users u GROUP BY u.id, u.name ORDER BY u.id

Select().
Alias("u", "pref").Columns("id", "name").
From("users u")
Alias("u", "pref").GroupBy("id", "name").
Alias("u", "pref").OrderBy("id").
// SELECT SELECT u.id AS pref_id, u.name AS pref_name FROM users u GROUP BY u.id AS pref_id, u.name AS pref_name ORDER BY u.id AS pref_id
CTE support (taken from https://github.com/joshring/squirrel)
With("alias").As(
  Select("col1").From("table"),
).Select(
  Select("col2").From("alias"),
)
// WITH alias AS (SELECT col1 FROM table) SELECT col2 FROM alias

WithRecursive("alias").As(
  Select("col1").From("table"),
).Select(
  Select("col2").From("alias"),
)
// WITH RECURSIVE alias AS (SELECT col1 FROM table) SELECT col2 FROM alias

Miscellaneous

  • Added a linter and fixed all warnings.

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 StatementBuilder = StatementBuilderType(builder.EmptyBuilder).PlaceholderFormat(Question)

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

Functions

func Alias 

func Alias(e Sqlizer, a 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 Avg added in v1.0.4 

func Avg(e Sqlizer) avgExpr

Avg allows to use AVG function in SQL query Ex: SelectBuilder.Select("id", Avg("amount"))

func ConcatExpr 

func ConcatExpr(parts ...any) 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 Count added in v1.0.4 

func Count(e Sqlizer) countExpr

Count allows to use COUNT function in SQL query Ex: SelectBuilder.Select("id", Count("amount"))

func Cte added in v1.3.0 

func Cte(e Sqlizer, cte string) cteExpr

Cte allows to define CTE (Common Table Expressions) in SQL query

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 Equal added in v1.4.0 

func Equal(e Sqlizer, v any) equalExpr

Equal allows to use = in SQL query Ex: SelectBuilder.Where(Equal(sq.Select(...), 1))

func Exists added in v1.0.4 

func Exists(e Sqlizer) existsExpr

Exists allows to use EXISTS in SQL query Ex: SelectBuilder.Where(Exists(Select("id").From("accounts").Where(Eq{"id": 1})))

func Greater added in v1.4.0 

func Greater(e Sqlizer, v any) greaterExpr

Greater allows to use > in SQL query Ex: SelectBuilder.Where(Greater(sq.Select(...), 1))

func GreaterOrEqual added in v1.4.0 

func GreaterOrEqual(e Sqlizer, v any) greaterOrEqualExpr

GreaterOrEqual allows to use >= in SQL query Ex: SelectBuilder.Where(GreaterOrEqual(sq.Select(...), 1))

func In added in v1.0.4 

func In(column string, e any) inExpr

In allows to use IN in SQL query Ex: SelectBuilder.Where(In("id", 1, 2, 3))

func Less added in v1.4.0 

func Less(e Sqlizer, v any) lessExpr

Less allows to use < in SQL query Ex: SelectBuilder.Where(Less(sq.Select(...), 1))

func LessOrEqual added in v1.4.0 

func LessOrEqual(e Sqlizer, v any) lessOrEqualExpr

LessOrEqual allows to use <= in SQL query Ex: SelectBuilder.Where(LessOrEqual(sq.Select(...), 1))

func Max added in v1.0.4 

func Max(e Sqlizer) maxExpr

Max allows to use MAX function in SQL query Ex: SelectBuilder.Select("id", Max("amount"))

func Min added in v1.0.4 

func Min(e Sqlizer) minExpr

Min allows to use MIN function in SQL query Ex: SelectBuilder.Select("id", Min("amount"))

func NotEqual added in v1.4.0 

func NotEqual(e Sqlizer, v any) notEqualExpr

NotEqual allows to use <> in SQL query Ex: SelectBuilder.Where(NotEqual(sq.Select(...), 1))

func NotExists added in v1.0.4 

func NotExists(e Sqlizer) notExistsExpr

NotExists allows to use NOT EXISTS in SQL query Ex: SelectBuilder.Where(NotExists(Select("id").From("accounts").Where(Eq{"id": 1})))

func NotIn added in v1.0.4 

func NotIn(column string, e any) notInExpr

NotIn allows to use NOT IN in SQL query Ex: SelectBuilder.Where(NotIn("id", 1, 2, 3))

func Placeholders 

func Placeholders(count int) string

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

func Range added in v1.0.6 

func Range(column string, start, end any) rangeExpr

Range allows to use range in SQL query Ex: SelectBuilder.Where(Range("id", 1, 3)) -> "id BETWEEN 1 AND 3" If start or end is nil, it will be omitted from the query. Ex: SelectBuilder.Where(Range("id", 1, nil)) -> "id >= 1" Ex: SelectBuilder.Where(Range("id", nil, 3)) -> "id <= 3"

func Sum added in v1.0.4 

func Sum(e Sqlizer) sumExpr

Sum allows to use SUM function in SQL query Ex: SelectBuilder.Select("id", Sum("amount"))

Types

type And 

type And conj

And conjunction Sqlizers

func (And) ToSql 

func (a And) ToSql() (string, []any, error)

type CaseBuilder 

type CaseBuilder builder.Builder

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

func Case 

func Case(what ...any) CaseBuilder

Case returns a new CaseBuilder "what" represents case value

func (CaseBuilder) Else 

func (b CaseBuilder) Else(e any) CaseBuilder

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

func (CaseBuilder) MustSql 

func (b CaseBuilder) MustSql() (string, []any)

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, []any, error)

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

func (CaseBuilder) When 

func (b CaseBuilder) When(when any, then any) CaseBuilder

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

type CommonTableExpressionsBuilder added in v1.3.0 

type CommonTableExpressionsBuilder builder.Builder

CommonTableExpressionsBuilder builds CTE (Common Table Expressions) SQL statements.

func With added in v1.3.0 

func With(cte string) CommonTableExpressionsBuilder

With returns a new CommonTableExpressionsBuilder with the given first cte name

See CommonTableExpressionsBuilder.Cte

func WithRecursive added in v1.3.0 

func WithRecursive(cte string) CommonTableExpressionsBuilder

WithRecursive returns a new CommonTableExpressionsBuilder with the RECURSIVE option and the given first cte name

See CommonTableExpressionsBuilder.Cte, CommonTableExpressionsBuilder.Recursive

func (CommonTableExpressionsBuilder) As added in v1.3.0 

func (b CommonTableExpressionsBuilder) As(as SelectBuilder) CommonTableExpressionsBuilder

As sets the expression for the Cte

func (CommonTableExpressionsBuilder) Cte added in v1.3.0 

func (b CommonTableExpressionsBuilder) Cte(cte string) CommonTableExpressionsBuilder

Cte starts a new cte

func (CommonTableExpressionsBuilder) Delete added in v1.3.0 

func (b CommonTableExpressionsBuilder) Delete(statement DeleteBuilder) CommonTableExpressionsBuilder

Delete finalizes the CommonTableExpressionsBuilder with a DELETE

func (CommonTableExpressionsBuilder) Insert added in v1.3.0 

func (b CommonTableExpressionsBuilder) Insert(statement InsertBuilder) CommonTableExpressionsBuilder

Insert finalizes the CommonTableExpressionsBuilder with an INSERT

func (CommonTableExpressionsBuilder) MustSql added in v1.3.0 

func (b CommonTableExpressionsBuilder) MustSql() (string, []any)

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

func (CommonTableExpressionsBuilder) PlaceholderFormat added in v1.3.0 

func (b CommonTableExpressionsBuilder) PlaceholderFormat(f PlaceholderFormat) CommonTableExpressionsBuilder

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

func (CommonTableExpressionsBuilder) Recursive added in v1.3.0 

func (b CommonTableExpressionsBuilder) Recursive(recursive bool) CommonTableExpressionsBuilder

func (CommonTableExpressionsBuilder) Replace added in v1.3.0 

func (b CommonTableExpressionsBuilder) Replace(statement InsertBuilder) CommonTableExpressionsBuilder

Replace finalizes the CommonTableExpressionsBuilder with a REPLACE

func (CommonTableExpressionsBuilder) Select added in v1.3.0 

func (b CommonTableExpressionsBuilder) Select(statement SelectBuilder) CommonTableExpressionsBuilder

Select finalizes the CommonTableExpressionsBuilder with a SELECT

func (CommonTableExpressionsBuilder) ToSql added in v1.3.0 

func (b CommonTableExpressionsBuilder) ToSql() (string, []any, error)

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

func (CommonTableExpressionsBuilder) Update added in v1.3.0 

func (b CommonTableExpressionsBuilder) Update(statement UpdateBuilder) CommonTableExpressionsBuilder

Update finalizes the CommonTableExpressionsBuilder with an UPDATE

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) 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, []any)

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

Prefix adds an expression to the beginning of the query

func (DeleteBuilder) PrefixExpr 

func (b DeleteBuilder) PrefixExpr(e Sqlizer) DeleteBuilder

PrefixExpr adds an expression to the very beginning of the query

func (DeleteBuilder) Suffix 

func (b DeleteBuilder) Suffix(sql string, args ...any) DeleteBuilder

Suffix adds an expression to the end of the query

func (DeleteBuilder) SuffixExpr 

func (b DeleteBuilder) SuffixExpr(e Sqlizer) DeleteBuilder

SuffixExpr adds an expression to the end of the query

func (DeleteBuilder) ToSql 

func (b DeleteBuilder) ToSql() (string, []any, error)

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

func (DeleteBuilder) Where 

func (b DeleteBuilder) Where(pred any, args ...any) DeleteBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

type Direction added in v1.0.7 

type Direction int

Direction is used in OrderByDir to specify the direction of the ordering. 

const (
	Asc Direction = iota
	Desc
)

func (Direction) String added in v1.0.7 

func (d Direction) String() string

String returns the string representation of the direction.

type Eq 

type Eq map[string]any

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 []any, err error)

type EqNotEmpty added in v1.0.6 

type EqNotEmpty map[string]any

EqNotEmpty ignores empty and zero values in Eq map. Ex: EqNotEmpty{"id1": 1, "name": nil, id2: 0, "desc": ""} -> "id1 = 1".

func (EqNotEmpty) ToSql added in v1.0.6 

func (eq EqNotEmpty) ToSql() (sql string, args []any, err error)

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

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 []any, 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 []any, err error)

type ILike 

type ILike Like

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

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

func (ILike) ToSql 

func (ilk ILike) ToSql() (sql string, args []any, 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 

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, []any)

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

Prefix adds an expression to the beginning of the query

func (InsertBuilder) PrefixExpr 

func (b InsertBuilder) PrefixExpr(e Sqlizer) InsertBuilder

PrefixExpr adds an expression to the very beginning of the query

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]any) 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 ...any) InsertBuilder

Suffix adds an expression to the end of the query

func (InsertBuilder) SuffixExpr 

func (b InsertBuilder) SuffixExpr(e Sqlizer) InsertBuilder

SuffixExpr adds an expression to the end of the query

func (InsertBuilder) ToSql 

func (b InsertBuilder) ToSql() (string, []any, error)

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

func (InsertBuilder) Values 

func (b InsertBuilder) Values(values ...any) InsertBuilder

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

type Like 

type Like map[string]any

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

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

func (Like) ToSql 

func (lk Like) ToSql() (sql string, args []any, err error)

type Lt 

type Lt map[string]any

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 []any, 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 []any, 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 []any, err error)

type NotILike 

type NotILike Like

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

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

func (NotILike) ToSql 

func (nilk NotILike) ToSql() (sql string, args []any, 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 []any, err error)

type Or 

type Or conj

Or conjunction Sqlizers

func (Or) ToSql 

func (o Or) ToSql() (string, []any, error)

type OrderByCondOption added in v1.4.1 

type OrderByCondOption struct {
	ColumnID  int
	NullsType OrderNullsType
}

OrderByCondOption is used to specify additional options for OrderByCond.

type OrderCond added in v1.0.7 

type OrderCond struct {
	ColumnID  int
	Direction Direction
}

OrderCond is used in OrderByDir to specify the condition of the ordering.

type OrderNullsType added in v1.4.1 

type OrderNullsType int

OrderNullsType is used to specify the order of NULLs in ORDER BY clause. 

const (
	OrderNullsUndefined OrderNullsType = iota
	OrderNullsFirst                    // ORDER BY ... NULLS FIRST
	OrderNullsLast                     // ORDER BY ... NULLS LAST
)

func (OrderNullsType) String added in v1.4.1 

func (o OrderNullsType) String() string

String returns the string representation of the order of NULLs.

type Paginator added in v1.0.7 

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

Paginator is a helper object to paginate results.

func PaginatorByID added in v1.0.7 

func PaginatorByID(limit uint64, lastID int64) Paginator

PaginatorByID creates a new Paginator for pagination by ID.

func PaginatorByPage added in v1.0.7 

func PaginatorByPage(pageSize, pageNum uint64) Paginator

PaginatorByPage creates a new Paginator for pagination by page.

func (Paginator) LastID added in v1.1.0 

func (p Paginator) LastID() int64

LastID returns the last ID for PaginatorTypeByID

func (Paginator) Limit added in v1.1.0 

func (p Paginator) Limit() uint64

Limit returns the limit for PaginatorTypeByID

func (Paginator) PageNumber added in v1.1.0 

func (p Paginator) PageNumber() uint64

PageNumber returns the page number for PaginatorTypeByPage

func (Paginator) PageSize added in v1.1.0 

func (p Paginator) PageSize() uint64

PageSize returns the page size for PaginatorTypeByPage

func (Paginator) Type added in v1.1.0 

func (p Paginator) Type() PaginatorType

Type returns the type of the paginator.

type PaginatorType added in v1.1.0 

type PaginatorType int
const (
	PaginatorTypeUndefined PaginatorType = iota
	PaginatorTypeByPage
	PaginatorTypeByID
)

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 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) Alias added in v1.2.0 

func (b SelectBuilder) Alias(table string, prefix ...string) alias

Alias creates a new table alias for the select builder. Prefix is used to add a prefix to the beginning of the column names. If no prefix, the column name will be used. All prefixes except the first will be ignored.

func (SelectBuilder) Column 

func (b SelectBuilder) Column(column any, args ...any) 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 ...any) 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 any, rest ...any) SelectBuilder

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

See Where.

func (SelectBuilder) InnerJoin 

func (b SelectBuilder) InnerJoin(join string, rest ...any) SelectBuilder

InnerJoin adds a INNER JOIN clause to the query.

func (SelectBuilder) Join 

func (b SelectBuilder) Join(join string, rest ...any) SelectBuilder

Join adds a JOIN clause to the query.

func (SelectBuilder) JoinClause 

func (b SelectBuilder) JoinClause(pred any, args ...any) SelectBuilder

JoinClause adds a join clause to the query.

func (SelectBuilder) LeftJoin 

func (b SelectBuilder) LeftJoin(join string, rest ...any) 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, []any)

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 any, args ...any) SelectBuilder

OrderByClause adds ORDER BY clause to the query.

func (SelectBuilder) OrderByCond added in v1.0.7 

func (b SelectBuilder) OrderByCond(columns map[int]string, conds []OrderCond, opts ...OrderByCondOption) SelectBuilder

OrderByCond adds ORDER BY expressions with direction to the query. The columns map is used to map OrderCond.ColumnID to the column name. Can be used to avoid hardcoding column names in the code.

func (SelectBuilder) Paginate added in v1.0.7 

func (b SelectBuilder) Paginate(p Paginator) SelectBuilder

Paginate adds pagination conditions to the query.

func (SelectBuilder) PaginateByID added in v1.0.7 

func (b SelectBuilder) PaginateByID(limit uint64, startID int64, columnID string) SelectBuilder

PaginateByID adds a LIMIT and start from ID condition to the query. WARNING: The columnID must be included in the ORDER BY clause to avoid unexpected results!

func (SelectBuilder) PaginateByPage added in v1.0.7 

func (b SelectBuilder) PaginateByPage(limit uint64, page uint64) SelectBuilder

PaginateByPage adds a LIMIT and OFFSET condition to the query. WARNING: query must be ordered to avoid unexpected results!

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

Prefix adds an expression to the beginning of the query

func (SelectBuilder) PrefixExpr 

func (b SelectBuilder) PrefixExpr(e 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

RemoveLimit 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 ...any) SelectBuilder

RightJoin adds a RIGHT JOIN clause to the query.

func (SelectBuilder) Search added in v1.0.7 

func (b SelectBuilder) Search(value any, columns ...string) SelectBuilder

Search adds a search condition to the query. The search condition is a WHERE clause with LIKE expressions. All columns will be converted to text. value can be a string or a number.

func (SelectBuilder) SetIDColumn added in v1.0.7 

func (b SelectBuilder) SetIDColumn(column string) SelectBuilder

SetIDColumn sets the column name to be used for pagination by ID. Required in special cases when Paginate function combined with PaginatorByID.

func (SelectBuilder) Suffix 

func (b SelectBuilder) Suffix(sql string, args ...any) SelectBuilder

Suffix adds an expression to the end of the query

func (SelectBuilder) SuffixExpr 

func (b SelectBuilder) SuffixExpr(e Sqlizer) SelectBuilder

SuffixExpr adds an expression to the end of the query

func (SelectBuilder) ToSql 

func (b SelectBuilder) ToSql() (string, []any, error)

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

func (SelectBuilder) Where 

func (b SelectBuilder) Where(pred any, args ...any) 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]any 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:

func (SelectBuilder) With added in v1.3.2 

func (b SelectBuilder) With(cteName string, cte SelectBuilder) SelectBuilder

With adds a CTE (Common Table Expression) to the query.

type Sqlizer 

type Sqlizer interface {
	ToSql() (string, []any, 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 ...any) Sqlizer

Expr builds an expression from a SQL fragment and arguments.

Ex: 

Expr("FROM_UNIXTIME(?)", t)

func Not added in v1.3.0 

func Not(e Sqlizer) Sqlizer

Not is a helper function to negate a condition.

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 

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 any, args ...any) StatementBuilderType

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

func (StatementBuilderType) With added in v1.3.0 

func (b StatementBuilderType) With(cte string) CommonTableExpressionsBuilder

With returns a CommonTableExpressionsBuilder for this StatementBuilderType

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) 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, []any)

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

Prefix adds an expression to the beginning of the query

func (UpdateBuilder) PrefixExpr 

func (b UpdateBuilder) PrefixExpr(e Sqlizer) UpdateBuilder

PrefixExpr adds an expression to the very beginning of the query

func (UpdateBuilder) Set 

func (b UpdateBuilder) Set(column string, value any) UpdateBuilder

Set adds SET clauses to the query.

func (UpdateBuilder) SetMap 

func (b UpdateBuilder) SetMap(clauses map[string]any) 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 ...any) UpdateBuilder

Suffix adds an expression to the end of the query

func (UpdateBuilder) SuffixExpr 

func (b UpdateBuilder) SuffixExpr(e 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, []any, error)

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

func (UpdateBuilder) Where 

func (b UpdateBuilder) Where(pred any, args ...any) UpdateBuilder

Where adds WHERE expressions to the query.

See SelectBuilder.Where for more information.

Source Files

View all Source files

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.