sqlb

package module
v0.1.6 Latest Latest
Warning

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

 Go to latest Published: Dec 21, 2020 License: Unlicense Imports: 11 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://godoc.org/github.com/mitranim/sqlb.

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

Changelog

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 = false

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 a nil result. 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`)}
	ErrTooManyArguments    Err = Err{Code: ErrCodeTooManyArguments, Cause: errors.New(`too many arguments`)}
	ErrOrdinalOutOfBounds  Err = Err{Code: ErrCodeOrdinalOutOfBounds, Cause: errors.New(`ordinal parameter exceeds arguments`)}
)

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"
	ErrCodeTooManyArguments    ErrCode = "TooManyArguments"
	ErrCodeOrdinalOutOfBounds  ErrCode = "OrdinalOutOfBounds"
)

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

func (NamedArg) IsNil 

func (self NamedArg) IsNil() bool

func (NamedArg) IsValid 

func (self NamedArg) IsValid() bool

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}

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"`
}{
	One: 10,
	Two: 20,
}

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

Is equivalent to: 

text := `"one" is not distinct from $1 and "two" is not distinct from $2`
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 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 errors. Contains both query content (as parsed AST) and arguments.

Automatically renumerates ordinal placeholders 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 of the form `$N`. 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 (*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 SqlQuery
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 _`

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.