where

README

where

• Provides a fluent API for dynamically constructing SQL WHERE & HAVING clauses.
• Also supports dynamic LIMIT, OFFSET and ORDER BY clauses.
• Allows the identifiers to be quoted to suit different SQL dialects, or not at all.
• dialect package supports different placeholder styles.
• quote package supports quoting SQL identifiers in back-ticks, double quotes, square barckets, or nothing.

Install

Install with this command:

go get github.com/rickb777/where/v2

where

Package where provides composable expressions for WHERE and HAVING clauses in SQL. These can range from the very simplest no-op to complex nested trees of AND and OR conditions.

In the naive approach, strings can be concatenated to construct lists of expression that are AND-ed together. However, mixing AND with OR makes things much more difficult. So this package does the work for you.

Also in this package are query constraints to provide ORDER BY, LIMIT and OFFSET clauses, along with 'TOP' for MS-SQL. These are similar to WHERE clauses except literal values are used instead of parameter placeholders.

Further support for SQL dialects and formatting options is provided in the dialect sub-package.

Queries should be written using '?' query placeholders throughout, and then these can be translated to the form needed by the chosen dialect: one of dialect.Query, dialect.Dollar, dialect.AtP or dialect.Inline.

Also, support for quoted identifiers is provided in the quote sub-package.

• quote.Quoter is the interface for a quoter.
• implementations include quote.ANSI, quote.Backticks, quote.SquareBrackets, and quote.None.

Documentation

Overview

Package where provides composable expressions for WHERE and HAVING clauses in SQL. These can range from the very simplest no-op to complex nested trees of 'AND' and 'OR' conditions. To get started, first look at `Expression` and its functions. The examples show how these can be used.

Index

• func Having(wh Expression, option ...dialect.FormatOption) (string, []interface{})
• func InlinePlaceholders(query string, args []any) (string, []any)
• func ReplacePlaceholders(sql string, opt dialect.FormatOption, from ...int) string
• func Where(wh Expression, option ...dialect.FormatOption) (string, []interface{})
• type Clause
  • func (exp Clause) And(other Expression) Expression
  • func (exp Clause) Format(option ...dialect.FormatOption) (string, []any)
  • func (exp Clause) Or(other Expression) Expression
  • func (exp Clause) String() string
• type Condition
  • func (exp Condition) And(other Expression) Expression
  • func (exp Condition) Format(option ...dialect.FormatOption) (string, []any)
  • func (exp Condition) Or(other Expression) Expression
  • func (exp Condition) String() string
• type Expression
  • func And(exp ...Expression) Expression
  • func Between(column string, a, b any) Expression
  • func Eq(column string, value any) Expression
  • func Gt(column string, value any) Expression
  • func GtEq(column string, value any) Expression
  • func In(column string, values ...any) Expression
  • func InSlice(column string, arg any) Expression
  • func Like(column string, pattern string) Expression
  • func Literal(column, predicate string, value ...any) Expression
  • func Lt(column string, value any) Expression
  • func LtEq(column string, value any) Expression
  • func NoOp() Expression
  • func Not(exp Expression) Expression
  • func NotEq(column string, value any) Expression
  • func NotNull(column string) Expression
  • func Null(column string) Expression
  • func Or(exp ...Expression) Expression
  • func Predicate(predicate string, value ...any) Expression
• type QueryConstraint
  • func Limit(n int) *QueryConstraint
  • func Offset(n int) *QueryConstraint
  • func OrderBy(column ...string) *QueryConstraint
  • func (qc *QueryConstraint) Asc() *QueryConstraint
  • func (qc *QueryConstraint) Desc() *QueryConstraint
  • func (qc *QueryConstraint) Format(d dialect.Dialect, option ...dialect.FormatOption) string
  • func (qc *QueryConstraint) FormatTOP(d dialect.Dialect) string
  • func (qc *QueryConstraint) Limit(n int) *QueryConstraint
  • func (qc *QueryConstraint) NullsFirst() *QueryConstraint
  • func (qc *QueryConstraint) NullsLast() *QueryConstraint
  • func (qc *QueryConstraint) Offset(n int) *QueryConstraint
  • func (qc *QueryConstraint) OrderBy(column ...string) *QueryConstraint
  • func (qc *QueryConstraint) String() string

Examples

• Limit
• Offset
• OrderBy
• QueryConstraint.NullsFirst
• QueryConstraint.NullsLast
• Where
• Where (Mysql_using_parameters)
• Where (Postgres_using_parameters)
• Where (Sqlserver_using_parameters)

Functions

func Having

func Having(wh Expression, option ...dialect.FormatOption) (string, []interface{})

Having constructs the SQL clause beginning "HAVING ...". If the expression is empty or nil, the returned string will be blank. Optional parameters may be supplied. Otherwise, by default, quote.DefaultQuoter is used and the result will contain '?' style placeholders.

func InlinePlaceholders

func InlinePlaceholders(query string, args []any) (string, []any)

InlinePlaceholders replaces every '?' placeholder with the corresponding argument value. Number and boolean arguments are inserted verbatim. Everything else is inserted in string syntax, i.e. enclosed in single quote marks.

The modified string is returned, along with any remaining arguments.

func ReplacePlaceholders

func ReplacePlaceholders(sql string, opt dialect.FormatOption, from ...int) string

ReplacePlaceholders replaces all "?" placeholders with numbered placeholders, using the given dialect option.

• For PostgreSQL these will be "$1" and upward placeholders so the dalect.Dollar option should be supplied.
• For SQL-Server there will be "@p1" and upward placeholders so the dialect.AtP should be supplied.

The count will start with 'from', or from 1.

func Where

func Where(wh Expression, option ...dialect.FormatOption) (string, []interface{})

Where constructs the SQL clause beginning "WHERE ...". If the expression is empty or nil, the returned string will be blank. Optional parameters may be supplied. Otherwise, by default, quote.DefaultQuoter is used and the result will contain '?' style placeholders.

Example

// in this example, identifiers will be unquoted
quote.DefaultQuoter = quote.None // (this is the default)

// some simple expressions
nameEqJohn := where.Eq("name", "John")
nameEqPeter := where.Eq("name", "Peter")
ageGt10 := where.Gt("age", 10)
likes := where.In("likes", "cats", "dogs")

// Build a compound expression - this is a static expression
// but it could be built up in stages depending on if-conditions.
wh := where.And(where.Or(nameEqJohn, nameEqPeter), ageGt10, likes)

// For Postgres, the placeholders have to be altered. It's necessary to do
// this on the whole query if there might be other placeholders in it too.
expr, args := where.Where(wh, dialect.Dollar)

fmt.Println(expr)
fmt.Println(args)

Output:

WHERE (name=$1 OR name=$2) AND age>$3 AND likes IN ($4,$5)
[John Peter 10 cats dogs]

Example (Mysql_using_parameters)

// some simple expressions
nameEqJohn := where.Eq("name", "John")
nameEqPeter := where.Eq("name", "Peter")
ageGt10 := where.Gt("age", 10)
likes := where.In("likes", "cats", "dogs")

// Build a compound expression - this is a static expression
// but it could be built up in stages depending on if-conditions.
wh := where.And(where.Or(nameEqJohn, nameEqPeter), ageGt10, likes)

// Format the 'where' clause, quoting all the identifiers for MySql.
clause, args := where.Where(wh, dialect.Backticks)

fmt.Println(clause)
fmt.Println(args)

Output:

WHERE (`name`=? OR `name`=?) AND `age`>? AND `likes` IN (?,?)
[John Peter 10 cats dogs]

Example (Postgres_using_parameters)

// some simple expressions
nameEqJohn := where.Eq("name", "John")
nameEqPeter := where.Eq("name", "Peter")
ageGt10 := where.Gt("age", 10)
likes := where.In("likes", "cats", "dogs")

// Build a compound expression - this is a static expression
// but it could be built up in stages depending on if-conditions.
wh := where.And(where.Or(nameEqJohn, nameEqPeter), ageGt10, likes)

// Format the 'where' clause, quoting all the identifiers for Postgres
// and replacing all the '?' parameters with "$1" numbered parameters,
// counting from 1.
clause, args := where.Where(wh, dialect.ANSIQuotes, dialect.Dollar)

fmt.Println(clause)
fmt.Println(args)

Output:

WHERE ("name"=$1 OR "name"=$2) AND "age">$3 AND "likes" IN ($4,$5)
[John Peter 10 cats dogs]

Example (Sqlserver_using_parameters)

// some simple expressions
nameEqJohn := where.Eq("name", "John")
nameEqPeter := where.Eq("name", "Peter")
ageGt10 := where.Gt("age", 10)
likes := where.In("likes", "cats", "dogs")

// Build a compound expression - this is a static expression
// but it could be built up in stages depending on if-conditions.
wh := where.And(where.Or(nameEqJohn, nameEqPeter), ageGt10, likes)

// Format the 'where' clause, quoting all the identifiers for Postgres
// and replacing all the '?' parameters with "$1" numbered parameters,
// counting from 1.
clause, args := where.Where(wh, dialect.SquareBrackets, dialect.AtP)

fmt.Println(clause)
fmt.Println(args)

Output:

WHERE ([name]=@p1 OR [name]=@p2) AND [age]>@p3 AND [likes] IN (@p4,@p5)
[John Peter 10 cats dogs]

Types

type Clause

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

Clause is a compound expression. It contains a list of zero or more expressions and notes whether to conjoin them using 'AND' or 'OR'.

func (Clause) And

func (exp Clause) And(other Expression) Expression

And combines two clauses into a clause that requires they are both true. Parentheses will be inserted to preserve the calling order. SQL implementation note: AND has higher precedence than OR.

func (Clause) Format

func (exp Clause) Format(option ...dialect.FormatOption) (string, []any)

Format formats an expression, returning the formatted string and the list of arguments.

func (Clause) Or

func (exp Clause) Or(other Expression) Expression

Or combines two clauses into a clause that requires either is true. Parentheses will be inserted to preserve the calling order. SQL implementation note: AND has higher precedence than OR.

func (Clause) String

func (exp Clause) String() string

type Condition

type Condition struct {
	Column, Predicate string
	Args              []interface{}
}

Condition is a simple condition such as an equality test. For convenience, use the factory functions 'Eq', 'GtEq', 'Null', 'In' etc.

This can also be constructed directly, which will be useful for non-portable cases, such as Postgresql 'SIMILAR TO'

expr := where.Condition{Column: "name", Predicate: " SIMILAR TO ?", Args: []any{pattern}}

Also for literal values (taking care to protect against injection attacks), e.g.

expr := where.Condition{Column: "age", Predicate: " = 47"}

Column can be left blank; this allows the predicate to be a sub-query such as EXISTS(...), e.g.

expr := where.Condition{Predicate: "EXISTS (SELECT 1 FROM offers WHERE expiry_date = CURRENT_DATE)"}

The functions Literal and Predicate provide for these cases.

func (Condition) And

func (exp Condition) And(other Expression) Expression

And combines two conditions into a clause that requires they are both true.

func (Condition) Format

func (exp Condition) Format(option ...dialect.FormatOption) (string, []any)

Format formats an expression, returning the formatted string and the list of arguments.

func (Condition) Or

func (exp Condition) Or(other Expression) Expression

Or combines two conditions into a clause that requires either is true.

func (Condition) String

func (exp Condition) String() string

type Expression

type Expression interface {
	// String prints the expression with inlined values inserted instead of placeholders.
	// Column names are not quoted.
	String() string

	// Format formats the (nested) expression as a string containing placeholders etc.
	// It doesn't include the WHERE or HAVING conjunction word.
	Format(option ...dialect.FormatOption) (string, []interface{})

	// And concatenates this expression with another such that both must evaluate true.
	And(Expression) Expression
	// Or concatenates this expression with another such that either must evaluate true.
	Or(Expression) Expression
	// contains filtered or unexported methods
}

Expression is an element in a WHERE clause. Expressions consist of simple conditions or more complex clauses of multiple conditions.

func And

func And(exp ...Expression) Expression

And combines some expressions into a clause that requires they are all true. Any nil items are silently dropped.

func Between

func Between(column string, a, b any) Expression

Between returns a between condition on a column.

func Eq

func Eq(column string, value any) Expression

Eq returns an equality condition on a column.

func Gt

func Gt(column string, value any) Expression

Gt returns a greater than condition on a column.

func GtEq

func GtEq(column string, value any) Expression

GtEq returns a greater than or equal condition on a column.

func In

func In(column string, values ...any) Expression

In returns an 'IN' condition on a column.

• If there are no values, this becomes a no-op.
• If any value is nil, an 'IS NULL' expression is OR-ed with the 'IN' expression.

Note that this does not use reflection, unlike InSlice.

func InSlice

func InSlice(column string, arg any) Expression

InSlice returns an 'IN' condition on a column.

• If arg is nil, this becomes a no-op.
• arg is reflectively expanded as an array or slice to use all the contained values.
• If any value is nil, an 'IS NULL' expression is OR-ed with the 'IN' expression.

Some '?' placeholders are used so it is necessary to replace placeholders in the resulting query according to SQL dialect, e.g using 'dialect.ReplacePlaceholdersWithNumbers(query)'.

Note that this uses reflection, unlike In.

func Like

func Like(column string, pattern string) Expression

Like returns a pattern-matching condition on a column. Be careful: this can hurt performance.

func Literal

func Literal(column, predicate string, value ...any) Expression

Literal returns a literal condition on a column. For example

• where.Literal("age", " > 45")

The column "age" will be quoted appropriately if a formatting option specifies this.

Be careful not to allow injection attacks: do not include a string from an external source in the column or predicate.

This function is the basis for most other predicates.

func Lt

func Lt(column string, value any) Expression

Lt returns a less than condition on a column.

func LtEq

func LtEq(column string, value any) Expression

LtEq returns a less than or equal than condition on a column.

func NoOp

func NoOp() Expression

NoOp creates an empty expression. This is useful for conditionally chaining expression-based contextual decisions. It can also be passed to any method that need an expression but for which none is required in that case.

func Not

func Not(exp Expression) Expression

Not negates an expression.

func NotEq

func NotEq(column string, value any) Expression

NotEq returns a not equal condition on a column.

func NotNull

func NotNull(column string) Expression

NotNull returns an 'IS NOT NULL' condition on a column. It's also possible to use Not(Null(...)).

func Null

func Null(column string) Expression

Null returns an 'IS NULL' condition on a column.

func Or

func Or(exp ...Expression) Expression

Or combines some expressions into a clause that requires that any is true. Any nil items are silently dropped.

func Predicate

func Predicate(predicate string, value ...any) Expression

Predicate returns a literal predicate. For example

• where.Predicate(`EXISTS (SELECT 1 FROM offers WHERE expiry_date = CURRENT_DATE)`)

Column quoting won't apply.

Be careful not to allow injection attacks: do not include a string from an external source in the predicate.

type QueryConstraint

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

func Limit

func Limit(n int) *QueryConstraint

Limit sets the upper limit on the number of records to be returned. The default value, 0, suppresses any limit.

As a special case, for SQL-Server, this produces the 'TOP' expression (see FormatTOP).

Example

// In this example, we can see how SqlServer needs different syntax
// to Sqlite, Postgres, Mysql etc.
qc := where.Limit(10).Offset(20)

s1 := qc.Format(dialect.Sqlite)
fmt.Println("SQlite:    ", s1)

s2 := qc.FormatTOP(dialect.SqlServer)
fmt.Println("SQL-Server:", s2)

s3 := qc.Format(dialect.SqlServer)
fmt.Println("SQL-Server:", s3)

Output:

SQlite:      LIMIT 10 OFFSET 20
SQL-Server:  TOP (10)
SQL-Server:  OFFSET 20

func Offset

func Offset(n int) *QueryConstraint

Offset sets the offset into the result set; previous items will be discarded.

Example

// In this example, we start a query constraint using Offset	.
qc := where.Offset(20)

s := qc.Format(dialect.Postgres)
fmt.Println(s)

Output:

OFFSET 20

func OrderBy

func OrderBy(column ...string) *QueryConstraint

OrderBy lists the column(s) by which the database will be asked to sort its results. The columns passed in here will be quoted according to the quoter in use when built. Be careful not to allow injection attacks: do not include a string from an external source in the columns.

Example

// OrderBy understands that Asc and Desc apply to the preceding columns
qc := where.OrderBy("foo", "bar").Desc().
	OrderBy("baz").Asc().
	Limit(10).
	Offset(20)

// here we chose Sqlite, but Mysql nnd Postgres would give the same result
s := qc.Format(dialect.Sqlite, dialect.NoQuotes)
fmt.Println(s)

// Sqlite doesn't use 'TOP' so it will be blank
s = qc.FormatTOP(dialect.Sqlite)
fmt.Println(s)

Output:

ORDER BY foo DESC, bar DESC, baz ASC LIMIT 10 OFFSET 20

func (*QueryConstraint) Asc

func (qc *QueryConstraint) Asc() *QueryConstraint

Asc sets the sort order to be ascending for the columns specified previously, not including those already set.

func (*QueryConstraint) Desc

func (qc *QueryConstraint) Desc() *QueryConstraint

Desc sets the sort order to be descending for the columns specified previously, not including those already set.

func (*QueryConstraint) Format

func (qc *QueryConstraint) Format(d dialect.Dialect, option ...dialect.FormatOption) string

Format formats the SQL expressions.

func (*QueryConstraint) FormatTOP

func (qc *QueryConstraint) FormatTOP(d dialect.Dialect) string

FormatTOP formats the SQL 'TOP' expression using the given dialect. Only SQL-Server uses this; for other dialects, it returns an empty string. Insert the returned string into your query after "SELECT [DISTINCT] " and before the list of column names.

func (*QueryConstraint) Limit

func (qc *QueryConstraint) Limit(n int) *QueryConstraint

Limit sets the upper limit on the number of records to be returned.

func (*QueryConstraint) NullsFirst

func (qc *QueryConstraint) NullsFirst() *QueryConstraint

NullsFirst can be used to control whether nulls appear before non-null values in the sort ordering. By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.

Example

// OrderBy also includes a "NULLS LAST" phrase.
qc := where.OrderBy("foo").NullsFirst()

// For Postgres, we're using double-quotes.
s := qc.Format(dialect.Postgres, dialect.ANSIQuotes)
fmt.Println(s)

Output:

ORDER BY "foo" NULLS FIRST

func (*QueryConstraint) NullsLast

func (qc *QueryConstraint) NullsLast() *QueryConstraint

NullsLast can be used to control whether nulls appear after non-null values in the sort ordering. By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise.

Example

// OrderBy also includes a "NULLS LAST" phrase.
qc := where.OrderBy("foo").NullsLast()

// For Postgres, we're using double-quotes.
s := qc.Format(dialect.Postgres, dialect.ANSIQuotes)
fmt.Println(s)

Output:

ORDER BY "foo" NULLS LAST

func (*QueryConstraint) Offset

func (qc *QueryConstraint) Offset(n int) *QueryConstraint

Offset sets the offset into the result set. The database will skip earlier records. It is usually important to set the order of results explicitly (see OrderBy).

func (*QueryConstraint) OrderBy

func (qc *QueryConstraint) OrderBy(column ...string) *QueryConstraint

OrderBy lists the column(s) by which the database will be asked to sort its results. The columns passed in here will be quoted according to the needs of the selected dialect. Be careful not to allow injection attacks: do not include a string from an external source in the columns.

func (*QueryConstraint) String

func (qc *QueryConstraint) String() string