README
¶
query
Note: This library is still very much a work in progress, expect breaking changes.
query is a simple PostgreSQL query builder for Go. It works by using first class functions to allow for queries to be built up. This does not support the entire dialect for PostgreSQL, only a subset that would be necessary for CRUD operations.
For full examples as to how queries of different types can be built up, see the
query_test.go file.
Let's look at how a simple SELECT query can be built up using this library,
q := query.Select(
query.Columns("*"),
query.From("posts"),
query.Where("user_id", "=", query.Arg(10)),
query.OrderDesc("created_at"),
)
we can then use the above to pass a query to our database driver, along with that arguments passed to the query,
rows, err := db.Query(q.Build(), q.Args()...)
Calling Build on the query will build up the query string and correctly set the parameter arguments in the query. Args will return an interface slice containing the arguments given to the query via query.Arg.
We can do even more complex SELECT queries too,
q := query.Select(
query.Columns("*"),
query.From("posts"),
query.Where("id", "IN",
query.Select(
query.Columns("id"),
query.From("post_tags"),
query.Where("name", "LIKE", query.Arg("%sql%")),
),
),
query.OrderDesc("created_at"),
)
This library makes use of the type Option which is a first class function, which takes the current Query an Option is being applied to and returns it.
type Option func(Query) Query
With this we can define our own Option functions to clean up some of the queries we want to build.
func Search(col, pattern string) query.Option {
return func(q query.Query) query.Query {
if pattern == "" {
return q
}
return query.Where(col, "LIKE", query.Arg("%" + pattern + "%"))(q)
}
}
q := query.Select(
query.Columns("*"),
query.From("posts"),
Search("title", "query builder"),
query.OrderDesc("created_at"),
)
Documentation
¶
Overview ¶
Package query provides an extensible SQL query builder for PostgreSQL. It works by using first class functions to allow for queries to be built up. This does not support the entire dialect for PostgreSQL, only a subset that would be necessary for CRUD operations.
Let's look at how a simple SELECT query can be built up using this library,
q := query.Select(
query.Columns("*"),
query.From("posts"),
query.Where("user_id", "=", query.Arg(10)),
query.OrderDesc("created_at"),
)
we can then use the above to pass a query to our database driver, along with the arguments passed to the query,
rows, err := db.Query(q.Build(), q.Args()...)
Calling Build on the query will build up the query string and correctly set the parameter arguments in the query. Args will return an interface slice containing the arguments given to the query via query.Arg.
We can do even more complex SELECT queries too,
q := query.Select(
query.Columns("*"),
query.From("posts"),
query.Where("id", "IN",
query.Select(
query.Columns("id"),
query.From("post_tags"),
query.Where("name", "LIKE", query.Arg("%sql%")),
),
),
query.OrderDesc("created_at"),
)
This library makes use of the type Option which is a first class function, which takes the current Query an Option is being applied to and returns it.
type Option func(Query) Query
With this we can define our own Option functions to clean up some of the queries we want to build.
func Search(col, pattern string) query.Option {
return func(q query.Query) query.Query {
if pattern == "" {
return q
}
return query.Where(col, "LIKE", query.Arg("%" + pattern + "%"))(q)
}
}
q := query.Select(
query.Columns("*"),
query.From("posts"),
Search("title", "query builder"),
query.OrderDesc("created_at"),
)
Index ¶
- func Arg(val interface{}) argExpr
- func Columns(cols ...string) listExpr
- func Count(cols ...string) callExpr
- func Ident(s string) identExpr
- func List(vals ...interface{}) listExpr
- func Lit(val interface{}) litExpr
- func Sum(col string) callExpr
- type Expr
- type Option
- func From(table string) Option
- func Limit(n int64) Option
- func Offset(n int64) Option
- func Options(opts ...Option) Option
- func OrWhere(col, op string, expr Expr) Option
- func OrderAsc(cols ...string) Option
- func OrderDesc(cols ...string) Option
- func Returning(cols ...string) Option
- func Set(col string, expr Expr) Option
- func Values(vals ...interface{}) Option
- func Where(col, op string, expr Expr) Option
- type Query
- func Delete(table string, opts ...Option) Query
- func Insert(table string, expr Expr, opts ...Option) Query
- func Select(expr Expr, opts ...Option) Query
- func SelectDistinct(expr Expr, opts ...Option) Query
- func SelectDistinctOn(cols []string, expr Expr, opts ...Option) Query
- func Union(queries ...Query) Query
- func Update(table string, opts ...Option) Query
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func Arg ¶
func Arg(val interface{}) argExpr
Arg returns an argument expression for the given value. When built this will use ? as the placeholder for the argument value.
func Columns ¶
func Columns(cols ...string) listExpr
Columns returns a list expression of the given column names. This will not be wrapped in parentheses when built.
func Count ¶
func Count(cols ...string) callExpr
Count returns a call expression for the COUNT function on the given columns.
func Ident ¶
func Ident(s string) identExpr
Ident returns an identifier expression for the given string. When built this will simply use the initial string that was given.
func List ¶
func List(vals ...interface{}) listExpr
List returns a list expression of the given values. Each item in the given list will use the ? placeholder. This will be wrapped in parentheses when built.
func Lit ¶
func Lit(val interface{}) litExpr
Lit returns a literal expression for the given value. This will place the literal value into the built up expression string itself, and not use the ? placeholder. For example using Lit like so,
Where("deleted_at", "IS NOT", Lit("NULL"))
would result in a WHERE clause being built up like this,
WHERE (deleted_at IS NOT NULL)
Types ¶
type Expr ¶
type Expr interface {
// Args returns the arguments that were given to the Query expression.
Args() []interface{}
// Build returns the built up Query expression.
Build() string
}
Expr is an expression that exists within the Query being built. This would typically be an identifier, literal, argument, function call, or list values in queries.
type Option ¶
Option is the type for the first class functions that should be used for modifying a Query as it is being built. This will be passed the latest state of the Query, and should return that same Query once any modifications have been made.
func OrWhere ¶
OrWhere appends a WHERE clause to the Query. This will append the arguments of the given expression to the Query too. This will use OR for conjoining with a preceding WHERE clause.
func OrderAsc ¶
OrderAsc appends an ORDER BY [column,...] ASC clause for the given columns to the Query.
func OrderDesc ¶
OrderDesc appends an ORDER BY [column,...] DESC clause for the given columns to the Query.
func Returning ¶
Returning appends a RETURNING [column,...] clause for the given columns to the Query.
type Query ¶
type Query struct {
// contains filtered or unexported fields
}
Query contains the state of a Query that is being built. The only way this should be modified is via the use of the Option first class function.
func Insert ¶
Insert builds up an INSERT query on the given table using the given leading expression, and applying the given options.
func Select ¶
Select will build up a SELECT query using the given leading expression, and applying the given options.
func SelectDistinct ¶
func Union ¶
Union returns a new Query that applies the UNION clause to all fo the given queries. This allows for multiple queries to be used within a single query.
Source Files