sqlb

package module
v0.1.12 Latest Latest
Warning

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

 Go to latest Published: Apr 1, 2021 License: Unlicense Imports: 12 Imported by: 1

README

Overview

SQL Builder: simple SQL query builder. Oriented towards text and writing plain SQL, simplifying parameters, arguments, query interpolation, query composition, and so on. Also provides tools for converting structs into SQL expressions and arguments.

See the full documentation at https://pkg.go.dev/github.com/mitranim/sqlb.

See the sibling library https://github.com/mitranim/gos for scanning SQL rows into structs.

Changelog

0.1.13

  • StrQuery now interpolates directly, without invoking (*Query).Append on the provided query. This allows to interpolate StrQuery strings that contain named parameter placeholders. Use at your own risk.

  • (*Query).Append no longer has an argument length limit.

0.1.12

An Ord with an empty path now panics on serialization, instead of generating invalid SQL. We never de-serialize with an empty path.

0.1.11

Added Ords.Lax: a boolean that causes Ords to skip unknown fields during parsing.

0.1.10

Breaking changes in the name of efficiency:

  • NamedArgs.Conditions now uses = and is null, as appropriate, instead of previous is not distinct from. At the time of writing, Postgres (version <= 12) is unable to use indexes for is not distinct from, which may result in much slower queries. The new approach avoids this gotcha.

  • In Ord, nulls last is now opt-in rather than default. In addition, asc/desc in input strings is now optional. This more precisely reflects SQL semantics and allows finer-grained control. More importantly, it avoids a potential performance gotcha. At the time of writing, Postgres (version <= 12) is unable to use normal indexes for nulls last ordering. Instead it requires specialized indexes where nulls last is set explicitly. Making it opt-in reduces the chance of accidental slowness.

    • Added OrdAscNl and OrdDescNl for convenient construction.

    • Minor breaking change: Ord.IsDesc is now Ord.Desc.

  • Minor breaking change: removed Ord.IsValid.

Non-breaking additions:

  • Ords.RowNumber(): generates a Postgres window function expression row_number() over (order by ...), falling back on a constant value when the ordering is empty.

  • QueryOrd(): shortcut for making a Query with a single .Append() invocation.

  • QueryNamed(): shortcut for making a Query with a single .AppendNamed() invocation.

0.1.9

Added Ords and Ord: structured representation of order by, able to decode from external input such as JSON, but also flexible enough to store arbitrary sub-queries. Ported from github.com/mitranim/jel, while also adding the ability to store sub-queries rather than only identifiers.

0.1.8

Added StrQuery.

0.1.7

Corrected CheckUnused to be true by default, which was always intended.

0.1.6

Added CheckUnused which allows to opt out of unused parameter checks in Query.Append and Query.AppendNamed. Can be convenient for development.

0.1.5

Minor bugfix: Query.String is now implemented on the non-pointer type, as intended. Also updated the sqlp dependency.

0.1.4

Breaking changes in Query: simpler interface, better performance.

Instead of storing and operating on a parsed AST, Query now stores the query text as []byte. We use sqlp.Tokenizer to parse inputs without generating an AST, transcoding parameters on the fly. IQuery now simply appends to an externally-passed Query, instead of having to return a parsed AST representation. All together, this significantly simplifies the implementation of Query and any external IQuery types.

0.1.3

Added Query.Clear().

0.1.2

Breaking: methods of NamedArgs now return queries, suitable for inclusion into other queries. Separate methods for strings and arg slices have been removed.

0.1.1

Dependency update.

0.1.0

First tagged release.

License

https://unlicense.org

Misc

I'm receptive to suggestions. If this library almost satisfies you but needs changes, open an issue or chat me up. Contacts: https://mitranim.com/#contacts

Documentation

Overview

SQL Builder: simple SQL query builder. Oriented towards text and writing PLAIN SQL, simplifying parameters, arguments, query interpolation, query composition, and so on. Also provides tools for converting structs into SQL expressions and arguments.

See the sibling library https://github.com/mitranim/gos for scanning SQL rows into structs.

Key Features

• You write plain SQL. There's no DSL in Go.

• Automatically renumerates ordinal parameters such as $1, $2, and so on. In the code, the count always starts at 1.

• Supports named parameters such as :ident, automatically converting them into ordinals.

• Avoids parameter collisions.

• Composable: query objects used as arguments are automatically inserted, combining the arguments and automatically renumerating the parameters.

• Supports converting structs to SQL clauses such as `select a, b, c`, `names (...) values (...)`, etc.

• Supports converting structs to named argument maps.

Examples

See `Query`, `Query.Append`, `Query.AppendNamed` for examples.

Index

Examples

Constants

This section is empty.

Variables

View Source 
var CheckUnused = true

If true (default), unused query parameters cause panics in functions like `Query.Append`. If false, unused parameters are ok. Turning this off can be convenient in development, when changing queries rapidly.

Functions

func Cols 

func Cols(dest interface{}) string

Takes a struct and generates a string of column names suitable for inclusion into `select`. Also accepts the following inputs and automatically dereferences them into a struct type:

  • Struct pointer.
  • Struct slice.
  • Struct slice pointer.

Nil slices and pointers are fine, as long as they carry a struct type. Any other input causes a panic.

Should be used in conjunction with `Query`. Also see `Query.WrapSelectCols()`.

Example 
package main

import (
	"fmt"

	"github.com/mitranim/sqlb"
)

func main() {
	type Internal struct {
		Id   string `db:"id"`
		Name string `db:"name"`
	}

	type External struct {
		Id       string   `db:"id"`
		Name     string   `db:"name"`
		Internal Internal `db:"internal"`
	}

	fmt.Println(sqlb.Cols(External{}))

	/**
	Formatted here for readability:

	"id",
	"name",
	("internal")."id"   as "internal.id",
	("internal")."name" as "internal.name"
	*/
}

func StructMap 

func StructMap(input interface{}) map[string]interface{}

Scans a struct, accumulating fields tagged with `db` into a map suitable for `Query.AppendNamed`. The input must be a struct or a struct pointer. A nil pointer is fine and produces an empty non-nil map. Panics on other inputs. Treats embedded structs as part of enclosing structs.

Types

type Err 

type Err struct {
	Code  ErrCode
	While string
	Cause error
}

Type of errors returned by this package. 

var (
	ErrInvalidInput        Err = Err{Code: ErrCodeInvalidInput, Cause: errors.New(`invalid input`)}
	ErrMissingArgument     Err = Err{Code: ErrCodeMissingArgument, Cause: errors.New(`missing argument`)}
	ErrUnexpectedParameter Err = Err{Code: ErrCodeUnexpectedParameter, Cause: errors.New(`unexpected parameter`)}
	ErrUnusedArgument      Err = Err{Code: ErrCodeUnusedArgument, Cause: errors.New(`unused argument`)}
	ErrOrdinalOutOfBounds  Err = Err{Code: ErrCodeOrdinalOutOfBounds, Cause: errors.New(`ordinal parameter exceeds arguments`)}
	ErrUnknownField        Err = Err{Code: ErrCodeUnknownField, Cause: errors.New(`unknown field`)}
	ErrInternal            Err = Err{Code: ErrCodeInternal, Cause: errors.New(`internal error`)}
)

Use blank error variables to detect error types: 

if errors.Is(err, sqlb.ErrIndexMismatch) {
	// Handle specific error.
}

Note that errors returned by this package can't be compared via `==` because they may include additional details about the circumstances. When compared by `errors.Is`, they compare `.Cause` and fall back on `.Code`.

func (Err) Error 

func (self Err) Error() string

Implement `error`.

func (Err) Is 

func (self Err) Is(other error) bool

Implement a hidden interface in "errors".

func (Err) Unwrap 

func (self Err) Unwrap() error

Implement a hidden interface in "errors".

type ErrCode 

type ErrCode string

Error codes. You probably shouldn't use this directly; instead, use the `Err` variables with `errors.Is`. 

const (
	ErrCodeUnknown             ErrCode = ""
	ErrCodeInvalidInput        ErrCode = "InvalidInput"
	ErrCodeMissingArgument     ErrCode = "MissingArgument"
	ErrCodeUnexpectedParameter ErrCode = "UnexpectedParameter"
	ErrCodeUnusedArgument      ErrCode = "UnusedArgument"
	ErrCodeOrdinalOutOfBounds  ErrCode = "OrdinalOutOfBounds"
	ErrCodeUnknownField        ErrCode = "UnknownField"
	ErrCodeInternal            ErrCode = "Internal"
)

type IQuery 

type IQuery interface{ QueryAppend(*Query) }

Interface that allows compatibility between different query variants. Subquery insertion / flattening, supported by `Query.Append` and `Query.AppendNamed`, detects instances of this interface, rather than the concrete type `Query`, allowing external code to implement its own variants, wrap `Query`, etc.

WTB better name.

type NamedArg 

type NamedArg struct {
	Name  string
	Value interface{}
}

Same as `sql.NamedArg`, with additional methods. See `NamedArgs`.

func Named 

func Named(name string, value interface{}) NamedArg

Convenience function for creating a named arg without struct field labels.

func (NamedArg) IsNil 

func (self NamedArg) IsNil() bool

Returns true if the value would be equivalent to `null` in SQL. Caution: this is NOT the same as comparing the value to `nil`: 

NamedArg{}.Value == nil                      // true
NamedArg{}.IsNil()                           // true
NamedArg{Value: (*string)(nil)}.Value == nil // false
NamedArg{Value: (*string)(nil)}.IsNil()      // true

This currently does NOT support fake nullable types like `sql.NullString`, because the Go developers didn't see fit to generalize them over a standard interface, and I'm unwilling to hardcode their full list here. I don't recommend their use. Just use actual nilable types.

type NamedArgs 

type NamedArgs []NamedArg

Sequence of named SQL arguments with utility methods for query building. Usually obtained by calling `StructNamedArgs()`.

func StructNamedArgs 

func StructNamedArgs(input interface{}) NamedArgs

Scans a struct, converting fields tagged with `db` into a sequence of named `NamedArgs`. The input must be a struct or a struct pointer. A nil pointer is fine and produces a nil result. Panics on other inputs. Treats embedded structs as part of enclosing structs.

func (NamedArgs) Assignments 

func (self NamedArgs) Assignments() Query

Returns a query whose string representation is suitable for an SQL `update set` clause, with arguments. Should be included into other queries via `Query.Append` or `Query.AppendNamed`.

For example, this: 

val := struct {
	One int64 `db:"one"`
	Two int64 `db:"two"`
}{
	One: 10,
	Two: 20,
}

query := StructNamedArgs(val).Assignments()
text := query.String()
args := query.Args

Is equivalent to: 

text := `"one" = $1, "two" = $2`
args := []interface{}{10, 20}

Known issue: when empty, this generates an empty query which is invalid SQL. Don't use this when `NamedArgs` is empty.

func (NamedArgs) Conditions 

func (self NamedArgs) Conditions() Query

Returns a query whose string representation is suitable for an SQL `where` or `on` clause, with arguments. Should be included into other queries via `Query.Append` or `Query.AppendNamed`.

For example, this: 

val := struct {
	One   int64  `db:"one"`
	Two   int64  `db:"two"`
	Three *int64 `db:"three"`
}{
	One: 10,
	Two: 20,
}

query := StructNamedArgs(val).Conditions()
text := query.String()
args := query.Args

Is equivalent to: 

text := `"one" = $1 and "two" = $2 and "three" is null`
args := []interface{}{10, 20}

func (NamedArgs) Every 

func (self NamedArgs) Every(fun func(NamedArg) bool) bool

Returns true if every argument satisfies the predicate function. Example: 

ok := args.Every(NamedArg.IsNil)

func (NamedArgs) Names 

func (self NamedArgs) Names() Query

Returns a query whose string representation is suitable for an SQL `select` clause. Should be included into other queries via `Query.Append` or `Query.AppendNamed`.

For example, this: 

val := struct {
	One int64 `db:"one"`
	Two int64 `db:"two"`
}{
	One: 10,
	Two: 20,
}

text := StructNamedArgs(val).Names().String()

Is equivalent to: 

text := `"one", "two"`

func (NamedArgs) NamesAndValues 

func (self NamedArgs) NamesAndValues() Query

Returns a query whose string representation is suitable for an SQL `insert` clause, with arguments. Should be included into other queries via `Query.Append` or `Query.AppendNamed`.

For example, this: 

val := struct {
	One int64 `db:"one"`
	Two int64 `db:"two"`
}{
	One: 10,
	Two: 20,
}

query := StructNamedArgs(val).NamesAndValues()
text := query.String()
args := query.Args

Is equivalent to: 

text := `("one", "two") values ($1, $2)`
args := []interface{}{10, 20}

func (NamedArgs) Some 

func (self NamedArgs) Some(fun func(NamedArg) bool) bool

Returns true if at least one argument satisfies the predicate function. Example: 

ok := args.Some(NamedArg.IsNil)

func (NamedArgs) Values 

func (self NamedArgs) Values() Query

Returns a query whose string representation is suitable for an SQL `values()` clause, with arguments. Should be included into other queries via `Query.Append` or `Query.AppendNamed`.

For example, this: 

val := struct {
	One int64 `db:"one"`
	Two int64 `db:"two"`
}{
	One: 10,
	Two: 20,
}

query := StructNamedArgs(val).Values()
text := query.String()
args := query.Args

Is equivalent to: 

text := `$1, $2`
args := []interface{}{10, 20}

type Ord added in v0.1.10 

type Ord struct {
	Path      []string
	Desc      bool
	NullsLast bool
}

Short for "ordering". Describes an SQL ordering like: 

`"some_col" asc`

`("nested")."other_col" desc`

but in a structured format. When encoding for SQL, identifiers are quoted for safety. Identifier case is preserved. Parsing of keyword such as "asc", "desc", "nulls last" is case-insensitive and non-case-preserving since they're converted to bools.

Note on `Desc`: the default value `false` corresponds to "ascending", which is the default in SQL.

Also see `Ords`.

func OrdAsc added in v0.1.10 

func OrdAsc(path ...string) Ord

Shortcut: 

OrdAsc(`one`, `two) ≡ Ord{Path: []string{`one`, `two`}}

func OrdAscNl added in v0.1.10 

func OrdAscNl(path ...string) Ord

Shortcut: 

OrdAscNl(`one`, `two) ≡ Ord{Path: []string{`one`, `two`}, NullsLast: true}

func OrdDesc added in v0.1.10 

func OrdDesc(path ...string) Ord

Shortcut: 

OrdDesc(`one`, `two) ≡ Ord{Path: []string{`one`, `two`}, Desc: true}

func OrdDescNl added in v0.1.10 

func OrdDescNl(path ...string) Ord

Shortcut: 

OrdDescNl(`one`, `two) ≡ Ord{Path: []string{`one`, `two`}, Desc: true, NullsLast: true}

func (Ord) AppendBytes added in v0.1.10 

func (self Ord) AppendBytes(buf *[]byte)

Appends an SQL string to the buffer. See `.String()`.

func (Ord) QueryAppend added in v0.1.10 

func (self Ord) QueryAppend(out *Query)

Implement `IQuery`, allowing this to be placed in `Ords`.

func (Ord) String added in v0.1.10 

func (self Ord) String() string

Returns an SQL string like: 

"some_col" asc

("some_col")."other_col" asc

type Ords added in v0.1.10 

type Ords struct {
	Items []IQuery
	Type  reflect.Type
	Lax   bool
}

Short for "orderings". Structured representation of an SQL ordering such as: 

`order by "some_col" asc`

`order by "some_col" asc, "nested"."other_col" desc`

For flexibility, the sequence of `Ords` may include arbitrary SQL expressions expressed as `IQuery` instances. But when decoding external input, every created element is an instance of `Ord`.

When encoding an `Ord` to a string, identifiers are quoted for safety. An ordering with empty `.Items` represents no ordering: "".

`.Type` is used for parsing external input. It must be a struct type. Every field name or path must be found in the struct type, possibly in nested structs. The decoding process will convert every JSON field name into the corresponding DB column name. Identifiers without the corresponding pair of `json` and `db` tags cause a parse error.

`.Lax` allows to ignore unknown fields. Normally, orderings not present in `.Type` generate errors; when `.Lax = true`, they're simply ignored.

Usage for parsing: 

input := []byte(`["one asc", "two.three desc"]`)

ords := OrdsFor(SomeStructType{})

err := ords.UnmarshalJSON(input)
panic(err)

The result is equivalent to: 

OrdsFrom(OrdAsc(`one`), OrdDesc(`two`, `three`))

Usage for SQL: 

text, args := ords.Query()

`Ords` implements `IQuery` and can be directly used as a sub-query: 

var query Query
query.Append(`select from where $1`, OrdsFrom(OrdAsc(`some_col`)))

func OrdsFor added in v0.1.10 

func OrdsFor(val interface{}) Ords

Shortcut for empty `Ords` intended for parsing. The input is used only as a type carrier. The parsing process will consult the provided type; see `Ords.UnmarshalJSON`.

func OrdsFrom added in v0.1.10 

func OrdsFrom(items ...IQuery) Ords

Shortcut for creating `Ords` without a type.

func (*Ords) Append added in v0.1.10 

func (self *Ords) Append(items ...IQuery)

Convenience method for appending.

func (Ords) IsEmpty added in v0.1.10 

func (self Ords) IsEmpty() bool

Returns true if there are no non-nil items.

func (Ords) Len added in v0.1.10 

func (self Ords) Len() (count int)

Returns the amount of non-nil items.

func (*Ords) Or added in v0.1.10 

func (self *Ords) Or(items ...IQuery)

If empty, replaces items with the provided fallback. Otherwise does nothing.

func (*Ords) ParseSlice added in v0.1.10 

func (self *Ords) ParseSlice(vals []string) error

Convenience method for parsing string slices, which may come from URL queries, form-encoded data, and so on.

func (Ords) QueryAppend added in v0.1.10 

func (self Ords) QueryAppend(out *Query)

Implement `IQuery`, allowing this to be used as a sub-query for `Query`. When used as an argument for `Query.Append` or `Query.AppendNamed`, this will be automatically interpolated.

func (Ords) RowNumber added in v0.1.10 

func (self Ords) RowNumber() Query

Returns a query for the Postgres window function `row_number`: 

OrdsFrom().RowNumber()
-> `0`

OrdsFrom(OrdAsc(`col`)).RowNumber()
-> `row_number() over (order by "col" asc nulls last)`

As shown above, an empty `Ords` generates a constant `0`. The Postgres query planner should optimize away any ordering by this constant column.

func (*Ords) UnmarshalJSON added in v0.1.10 

func (self *Ords) UnmarshalJSON(input []byte) error

Implement decoding from JSON. Consults `.Type` to determine known field paths, and converts them to DB column paths, rejecting unknown identifiers.

type Query 

type Query struct {
	Text []byte
	Args []interface{}
}

Tool for building SQL queries. Makes it easy to append or insert arbitrary SQL code while avoiding common mistakes. Contains both query content (as parsed AST) and arguments.

Automatically renumerates ordinal parameters such as "$1" when appending code, making it easy to avoid mis-numbering. See `.Append()`.

Supports named parameters. See `.AppendNamed()`.

Composable: both `.Append()` and `.AppendNamed()` automatically interpolate sub-queries found in the arguments, combining the arguments and renumerating the parameters as appropriate.

Currently biased towards Postgres-style ordinal parameters such as "$1". The code is always converted to this "canonical" form. This can be rectified if there is enough demand; you can open an issue at https://github.com/mitranim/sqlb/issues.

func QueryNamed added in v0.1.10 

func QueryNamed(src string, args map[string]interface{}) Query

Convenience function for making a `Query` with a single `Query.AppendNamed` invocation.

func QueryOrd added in v0.1.10 

func QueryOrd(src string, args ...interface{}) Query

Convenience function for making a `Query` with a single `Query.Append` invocation.

func (*Query) Append 

func (self *Query) Append(src string, args ...interface{})

Appends code and arguments. Renumerates ordinal parameters, offsetting them by the previous argument count. The count in the code always starts from `$1`.

Composable: automatically interpolates any instances of `IQuery` found in the arguments, combining the arguments and renumerating the parameters as appropriate.

For example, this: 

var query Query
query.Append(`where true`)
query.Append(`and one = $1`, 10)
query.Append(`and two = $1`, 20) // Note the $1.

text := query.String()
args := query.Args

Is equivalent to this: 

text := `where true and one = $1 and two = $2`
args := []interface{}{10, 20}

Panics when: the code is malformed; the code has named parameters; a parameter doesn't have a corresponding argument; an argument doesn't have a corresponding parameter.

func (*Query) AppendNamed 

func (self *Query) AppendNamed(src string, args map[string]interface{})

Appends code and named arguments. The code must have named parameters in the form ":identifier". The keys in the arguments map must have the form "identifier", without a leading ":".

Internally, converts named parameters to ordinal parameters of the form `$N`, such as the ones used by `.Append()`.

Composable: automatically interpolates any instances of `IQuery` found in the arguments, combining the arguments and renumerating the parameters as appropriate.

For example, this: 

var query Query
query.AppendNamed(
	`select col where col = :value`,
	map[string]interface{}{"value": 10},
)

text := query.String()
args := query.Args

Is equivalent to this: 

text := `select col where col = $1`
args := []interface{}{10}

Panics when: the code is malformed; the code has ordinal parameters; a parameter doesn't have a corresponding argument; an argument doesn't have a corresponding parameter.

func (*Query) AppendQuery 

func (self *Query) AppendQuery(query IQuery)

Convenience method, inverse of `IQuery.QueryAppend`. Appends the other query to this one, combining the arguments and renumerating the ordinal parameters as appropriate.

func (*Query) Clear added in v0.1.3 

func (self *Query) Clear()

"Zeroes" the query, keeping any already-allocated capacity. Similar to `query = sqlb.Query{}`, but slightly clearer and marginally more efficient for subsequent query building.

func (Query) QueryAppend added in v0.1.6 

func (self Query) QueryAppend(out *Query)

Implement `IQuery`, allowing compatibility between different implementations, wrappers, etc.

func (Query) String added in v0.1.6 

func (self Query) String() string

Implement `fmt.Stringer`.

func (*Query) WrapSelect 

func (self *Query) WrapSelect(exprs string)

Wraps the query to select only the specified expressions.

For example, this: 

var query Query
query.Append(`select * from some_table`)
query.WrapSelect(`one, two`)

text := query.String()

Is equivalent to this: 

text := `with _ as (select * from some_table) select one, two from _`

func (*Query) WrapSelectCols 

func (self *Query) WrapSelectCols(dest interface{})

Wraps the query to select the fields derived by calling `Cols(dest)`.

For example, this: 

var query Query
query.Append(`select * from some_table`)

var out struct{Id int64 `db:"id"`}
query.WrapSelectCols(out)

text := query.String()

Is equivalent to this: 

text := `with _ as (select * from some_table) select "id" from _`

type StrQuery added in v0.1.8 

type StrQuery string

Shortcut for interpolating strings into queries.

Because this implements `IQuery`, when used as an ordinal or named parameter ($1 or :named), this will be directly interpolated into the query string and removed from the argument list.

Usage: 

var query Query
query.AppendNamed(
	`select col where :col = :value`,
	map[string]interface{}{"col": StrQuery("rel_id"), "value": 10},
)

func (StrQuery) QueryAppend added in v0.1.8 

func (self StrQuery) QueryAppend(out *Query)

Implement `IQuery`.

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.