sqlf

package module
v1.1.0 Latest Latest
Warning

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

 Go to latest Published: Mar 22, 2024 License: MIT Imports: 7 Imported by: 0

README

Package sqlf focuses only on building SQL queries by combining fragments. Low reusability and scalability are the main challenges we face when writing SQL, the package is designed to solve these problems.

Fragment

Unlike any other sql builder or ORMs, Fragment is the only concept you need to learn.

Fragment is usually a part of a SQL query, for example, combining main fragment and any number of condition fragments, we can get a complete query.

query, args, _ := (&sqlf.Fragment{
	Raw: `SELECT * FROM foo WHERE #join('#fragment', ' AND ')`,
	Fragments: []*sqlf.Fragment{
		sqlf.Fa(`bar IN (#join('#argDollar', ', '))`, 1, 2, 3),
		sqlf.Fa(`baz = $1`, true),
	},
}).Build()
fmt.Println(query)
fmt.Println(args)
// Output:
// SELECT * FROM foo WHERE bar IN ($1, $2, $3) AND baz = $4
// [1 2 3 true]

Explanation:

  • We pay attention only to the references inside a fragment, e.g., use $1 to refer Fragment.Args[0], or ? to refer Fragment.Args in order.
  • #join, #fragment, etc., are preprocessing functions, which will be explained later.

See example_test.go for more examples.

Preprocessing Functions

name description example
c, column Fragment.Columns at index #c1
t, table Fragment.Tables at index #t1
fragment Fragment.Fragments at index #fragment1
builder Fragment.Builders at index #builder1
argDollar Fragment.Args at index with style $ #join('#argDollar', ', ')
argQuestion Fragment.Args at index with style ? #join('#argQuestion', ', ')
join Join the template with separator #join('#fragment', ' AND ')
Join from index 3 to end #join('#argDollar', ',', 3)
Join from index 3 to 6 #join('#argDollar', ',', 3, 6)

Note:

  • #c1 is equivalent to #c(1), which is a special syntax to call preprocessing functions when an integer (usually an index) is the only argument.
  • Expressions in the #join template are functions, not function calls.

You can register custom preprocessing functions to the build context.

ctx := sqlf.NewContext()
ids := sqlf.NewArgsProperty(1, 2, 3)
_ = ctx.Funcs(sqlf.FuncMap{
	"_id": func(i int) (string, error) {
		return ids.Build(ctx, i, syntax.Dollar)
	},
})
b := &sqlf.Fragment{
	Raw: "#join('#fragment', '\nUNION\n')",
	Fragments: []*sqlf.Fragment{
		{Raw: "SELECT id, 'foo' typ, count FROM foo WHERE id IN (#join('#_id', ', '))"},
		{Raw: "SELECT id, 'bar' typ, count FROM bar WHERE id IN (#join('#_id', ', '))"},
	},
}
query, _ := b.BuildContext(ctx)
fmt.Println(query)
fmt.Println(ctx.Args())
// SELECT id, 'foo' typ, count FROM foo WHERE id IN ($1, $2, $3)
// UNION
// SELECT id, 'bar' typ, count FROM bar WHERE id IN ($1, $2, $3)
// [1 2 3]

QueryBuilder

*sqlb.QueryBuilder is a high-level abstraction of SQL queries for building complex queries, with *sqlf.Fragment as its underlying foundation.

See sqlb/example_test.go for examples.

Documentation

Overview

Package sqlf focuses only on building SQL queries by combining fragments. Low reusability and scalability are the main challenges we face when writing SQL, the package is designed to solve these problems.

Fragment

Unlike any other sql builder or ORMs, Fragment is the only concept you need to learn.

Fragment is usually a part of a SQL query, for example, combining main fragment and any number of condition fragments, we can get a complete query. 

query, args, _ := (&sqlf.Fragment{
    Raw: `SELECT * FROM foo WHERE #join('#fragment', ' AND ')`,
    Fragments: []*sqlf.Fragment{
        sqlf.Fa(`bar IN (#join('#argDollar', ', '))`, 1, 2, 3),
        sqlf.Fa(`baz = $1`, true),
    },
}).Build()
fmt.Println(query)
fmt.Println(args)
// Output:
// SELECT * FROM foo WHERE bar IN ($1, $2, $3) AND baz = $4
// [1 2 3 true]

Explanation:

  • We pay attention only to the references inside the fragment, for example, use $1 to refer Fragment.Args[0], or ? to refer Fragment.Args in order.
  • #join, #fragment, etc., are preprocessing functions, which will be explained later.

Preprocessing Functions

  • c, column: Fragment.Columns at index, e.g. #c1
  • t, table: Fragment.Tables at index, e.g. #t1
  • fragment: Fragment.Fragments at index, e.g. #fragment1
  • builder: Fragment.Builders at index, e.g. #builder1
  • argDollar: Fragment.Args at index with style $, usually used in #join().
  • argQuestion: Fragment.Args at index with style ?, usually used in #join().
  • join: Join the template with separator, e.g. #join('#column', ', '), #join('#argDollar', ',', 3), #join('#argDollar', ',', 3, 6)

Note:

  • #c1 is equivalent to #c(1), which is a special syntax to call preprocessing functions when an integer (usually an index) is the only argument.
  • Expressions in the #join template are functions, not function calls.
  • You can register custom functions to the build context, see Context.Funcs.
Example (Basic) 
query, args, _ := (&sqlf.Fragment{
	Raw: `SELECT * FROM foo WHERE #join('#fragment', ' AND ')`,
	Fragments: []*sqlf.Fragment{
		sqlf.Fa(`bar IN (#join('#argDollar', ', '))`, 1, 2, 3),
		sqlf.Fa(`baz = $1`, true),
	},
}).Build()
fmt.Println(query)
fmt.Println(args)

Output:

SELECT * FROM foo WHERE bar IN ($1, $2, $3) AND baz = $4
[1 2 3 true]
Example (Select) 
selects := &sqlf.Fragment{
	Raw: "SELECT #join('#column', ', ')",
}
from := &sqlf.Fragment{
	Raw: "FROM #t1",
}
where := &sqlf.Fragment{
	Prefix: "WHERE",
	Raw:    "#join('#fragment', ' AND ')",
}
builder := sqlf.Ff(
	"#join('#fragment', ' ')",
	selects, from, where,
)

var users sqlf.Table = "users"
selects.WithColumns(users.Expressions("id", "name", "email")...)
from.WithTables(users)
where.AppendFragments(&sqlf.Fragment{
	Raw:     "#c1 IN (#join('#argDollar', ', '))",
	Columns: users.Expressions("id"),
	Args:    []any{1, 2, 3},
})
where.AppendFragments(&sqlf.Fragment{
	Raw:     "#c1 = $1",
	Columns: users.Expressions("active"),
	Args:    []any{true},
})

query, args, err := builder.Build()
if err != nil {
	panic(err)
}
fmt.Println(query)
fmt.Println(args)

Output:

SELECT id, name, email FROM users WHERE id IN ($1, $2, $3) AND active = $4
[1 2 3 true]
Example (ShortcutFuncs) 
query, args, _ := sqlf.Ff(
	`#join('#fragment', ' UNION ')`,
	sqlf.Fa(`SELECT 1`),
	sqlf.Fa(`SELECT 2`),
).Build()
fmt.Println(query)
fmt.Println(args)

Output:

SELECT 1 UNION SELECT 2
[]
Example (UnalignedJoin) 
// this example demonstrates how #join() works between unaligned properties.
// it leaves the extra property items (.Args[2:] here) unused, which leads to an error.
// to make it work, we use #noUnusedError() to suppress the error.
ctx := sqlf.NewContext()
ctx.Funcs(sqlf.FuncMap{
	"noUnusedError": func(ctx *sqlf.FragmentContext) {
		for i := 3; i <= ctx.Args.Count(); i++ {
			ctx.Args.ReportUsed(i)
		}
	},
})
foo := sqlf.Table("foo")
b := &sqlf.Fragment{
	Raw:     "#noUnusedError() UPDATE #t1 SET #join('#c=#argDollar', ', ')",
	Tables:  []sqlf.Table{foo},
	Columns: foo.Expressions("bar", "baz"),
	Args:    []any{1, 2, 3, true, false},
}
query, err := b.BuildContext(ctx)
if err != nil {
	panic(err)
}
fmt.Println(query)
fmt.Println(ctx.Args())

Output:

UPDATE foo SET bar=$1, baz=$2
[1 2]
Example (Update) 
update := &sqlf.Fragment{
	Raw: "UPDATE #t1 SET #join('#c=#argDollar', ', ')",
}
where := &sqlf.Fragment{
	Prefix: "WHERE",
	Raw:    "#join('#fragment', ' AND ')",
}
// consider wrapping it with your own builder
// to provide a more friendly APIs
builder := &sqlf.Fragment{
	Raw: "#join('#fragment', ' ')",
	Fragments: []*sqlf.Fragment{
		update,
		where,
	},
}

var users sqlf.Table = "users"
update.WithTables(users)
update.WithColumns(users.Expressions("name", "email")...)
update.WithArgs("alice", "alice@example.org")
where.AppendFragments(&sqlf.Fragment{
	Raw:     "#c1=$1",
	Columns: users.Expressions("id"),
	Args:    []any{1},
})

query, args, err := builder.Build()
if err != nil {
	panic(err)
}
fmt.Println(query)
fmt.Println(args)

Output:

UPDATE users SET name=$1, email=$2 WHERE id=$3
[alice alice@example.org 1]

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// ErrInvalidIndex is returned when the reference index is invalid.
	ErrInvalidIndex = errors.New("invalid index")
)

Functions

This section is empty.

Types

type ArgsProperty 

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

ArgsProperty is the args builder

func NewArgsProperty 

func NewArgsProperty(args ...any) *ArgsProperty

NewArgsProperty returns a new ArgsBuilder.

func (*ArgsProperty) Build 

func (b *ArgsProperty) Build(ctx *Context, index int, defaultStyle syntax.BindVarStyle) (string, error)

Build builds the arg at index.

func (ArgsProperty) CheckUsage 

func (b ArgsProperty) CheckUsage() error

CheckUsage checks if all items are used.

func (ArgsProperty) Count 

func (b ArgsProperty) Count() int

Count returns the count of items.

func (ArgsProperty) Get 

func (b ArgsProperty) Get(index int) (T, error)

Get returns the item at index, starting from 1.

func (ArgsProperty) ReportUsed 

func (b ArgsProperty) ReportUsed(index int)

ReportUsed reports the item at index is used, starting from 1.

type Builder 

type Builder interface {
	// Build builds and returns the query and args.
	Build() (query string, args []any, err error)
	// BuildContext builds the query with the context.
	// The built args should be committed to the context, which can be
	// retrieved after building.
	BuildContext(ctx *Context) (query string, err error)
}

Builder is the interface for sql builders.

type BuildersProperty 

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

BuildersProperty is the Builders property

func NewBuildersProperty 

func NewBuildersProperty(builders ...Builder) *BuildersProperty

NewBuildersProperty returns a new BuildersProperty.

func (*BuildersProperty) Build 

func (b *BuildersProperty) Build(ctx *Context, index int) (string, error)

Build builds the builder at index.

func (BuildersProperty) CheckUsage 

func (b BuildersProperty) CheckUsage() error

CheckUsage checks if all items are used.

func (BuildersProperty) Count 

func (b BuildersProperty) Count() int

Count returns the count of items.

func (BuildersProperty) Get 

func (b BuildersProperty) Get(index int) (T, error)

Get returns the item at index, starting from 1.

func (BuildersProperty) ReportUsed 

func (b BuildersProperty) ReportUsed(index int)

ReportUsed reports the item at index is used, starting from 1.

type Column 

type Column struct {
	Table Table
	Raw   string
	Args  []any
}

Column is a column of a table.

type ColumnsProperty 

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

ColumnsProperty is the columns property

func NewColumnsProperty 

func NewColumnsProperty(columns ...*Column) *ColumnsProperty

NewColumnsProperty returns a new ColumnsProperty.

func (*ColumnsProperty) Build 

func (b *ColumnsProperty) Build(ctx *Context, index int) (string, error)

Build builds the column at index.

func (ColumnsProperty) CheckUsage 

func (b ColumnsProperty) CheckUsage() error

CheckUsage checks if all items are used.

func (ColumnsProperty) Count 

func (b ColumnsProperty) Count() int

Count returns the count of items.

func (ColumnsProperty) Get 

func (b ColumnsProperty) Get(index int) (T, error)

Get returns the item at index, starting from 1.

func (ColumnsProperty) ReportUsed 

func (b ColumnsProperty) ReportUsed(index int)

ReportUsed reports the item at index is used, starting from 1.

type Context 

type Context struct {
	// BindVarStyle overrides bindvar styles of all fragments.
	// if not set, the first bindvar style encountered when
	// building is applied.
	BindVarStyle syntax.BindVarStyle
	// contains filtered or unexported fields
}

Context is the global context shared between all fragments building.

func NewContext 

func NewContext() *Context

NewContext returns a new context.

func (*Context) Args 

func (c *Context) Args() []any

Args returns the built args of the context.

func (*Context) CommitArg 

func (c *Context) CommitArg(arg any, defaultStyle syntax.BindVarStyle) string

CommitArg commits an built arg to the context and returns the built bindvar. defaultStyle is used only when no style is set in the context and no style is seen before.

func (*Context) Funcs 

func (c *Context) Funcs(funcs FuncMap) error

Funcs adds the preprocessing functions to the context.

The function name is case sensitive, only letters and underscore are allowed.

Allowed function signatures: 

func(/* args... */) (string, error)
func(/* args... */) string
func(/* args... */)

Allowed argument types:

  • number types: int, int8, int16, int32, int64, uint, uint8, uint16, uint32, uint64,float32, float64
  • string
  • bool
  • *sqlf.FragmentContext: allowed only as the first argument

Here are examples of legal names and function signatures: 

funcs := sqlf.FuncMap{
	// #number1, #join('#number', ', ')
	"number": func(i int) (string, error) {/* ... */},
	// #myBuilder1, #join('#myBuilder', ', ')
	"myBuilder": func(ctx *sqlf.FragmentContext, i int) (string, error)  {/* ... */},
	// #string('string')
	"string": func(str string) (string, error)  {/* ... */},
	// #numbers(1,2)
	"numbers": func(ctx *sqlf.FragmentContext, a, b int) string  {/* ... */},
}
Example 
// this example shows how to use Global Args by using
// *sqlf.ArgsProperty and custom function, so that we
// don't have to put Args into every fragment, which leads
// to a list of redundant args.
ctx := sqlf.NewContext()
ids := sqlf.NewArgsProperty(1, 2, 3)
err := ctx.Funcs(sqlf.FuncMap{
	"_id": func(i int) (string, error) {
		return ids.Build(ctx, i, syntax.Dollar)
	},
})
if err != nil {
	panic(err)
}
fragment := &sqlf.Fragment{
	Raw: "#join('#fragment', '\nUNION\n')",
	Fragments: []*sqlf.Fragment{
		{Raw: "SELECT id, 'foo' typ, count FROM foo WHERE id IN (#join('#_id', ', '))"},
		{Raw: "SELECT id, 'bar' typ, count FROM bar WHERE id IN (#join('#_id', ', '))"},
	},
}
query, err := fragment.BuildContext(ctx)
if err != nil {
	panic(err)
}
args := ctx.Args()
fmt.Println(query)
fmt.Println(args)

Output:

SELECT id, 'foo' typ, count FROM foo WHERE id IN ($1, $2, $3)
UNION
SELECT id, 'bar' typ, count FROM bar WHERE id IN ($1, $2, $3)
[1 2 3]

type Fragment 

type Fragment struct {
	Raw       string      // Raw string support bind vars and preprocessing functions.
	Args      []any       // Args to be referred by the Raw, e.g.: ?, $1
	Columns   []*Column   // Columns to be referred by the Raw, e.g.: #c1, #column2
	Tables    []Table     // Table names / alias to be referred by the Raw, e.g.: #t1, #table2
	Fragments []*Fragment // Fragments to be referred by the Raw, e.g.: #fragment1, #fragment2
	Builders  []Builder   // Builders to be referred by the Raw, e.g.: #builder1, #builder2

	Prefix string // Prefix is added before the built fragment only if which is not empty.
	Suffix string // Suffix is added after the built fragment only if which is not empty.
}

Fragment is the builder for a part of or even the full query, it allows you to write and combine fragments with freedom.

func Fa 

func Fa(raw string, args ...any) *Fragment

Fa creates a new Fragment with Args property.

func Fb 

func Fb(raw string, builders ...Builder) *Fragment

Fb creates a new Fragment with Builders property.

func Fc added in v1.0.1 

func Fc(raw string, columns ...*Column) *Fragment

Fc creates a new Fragment with Columns property.

func Ff 

func Ff(raw string, fragments ...*Fragment) *Fragment

Ff creates a new Fragment with Fragments property.

func Ft added in v1.0.1 

func Ft(raw string, tables ...Table) *Fragment

Ft creates a new Fragment with Tables property.

func (*Fragment) AppendArgs 

func (f *Fragment) AppendArgs(args ...any)

AppendArgs appends args to the fragment. Args are used to be referred by the Raw, e.g.: ?, $1

func (*Fragment) AppendBuilders 

func (f *Fragment) AppendBuilders(builders ...Builder)

AppendBuilders appends builders to the fragment. Builders are used to be referred by the Raw, e.g.: #builder1, #builder2

func (*Fragment) AppendColumns 

func (f *Fragment) AppendColumns(columns ...*Column)

AppendColumns appends columns to the fragment. Columns are used to be referred by the Raw, e.g.: #c1, #column2

func (*Fragment) AppendFragments 

func (f *Fragment) AppendFragments(fragments ...*Fragment)

AppendFragments appends fragments to the fragment. Fragments are used to be referred by the Raw, e.g.: #fragment1, #fragment2

func (*Fragment) AppendTables 

func (f *Fragment) AppendTables(tables ...Table)

AppendTables appends tables to the fragment. Tables are used to be referred by the Raw, e.g.: #t1, #table2

func (*Fragment) Build 

func (f *Fragment) Build() (query string, args []any, err error)

Build builds the fragment.

func (*Fragment) BuildContext 

func (f *Fragment) BuildContext(ctx *Context) (string, error)

BuildContext builds the fragment with context.

func (*Fragment) WithArgs 

func (f *Fragment) WithArgs(args ...any)

WithArgs replace f.Args with the args Args are used to be referred by the Raw, e.g.: ?, $1

func (*Fragment) WithBuilders 

func (f *Fragment) WithBuilders(builders ...Builder)

WithBuilders replace f.Builders with the builders Builders are used to be referred by the Raw, e.g.: #builder1, #builder2

func (*Fragment) WithColumns 

func (f *Fragment) WithColumns(columns ...*Column)

WithColumns replace f.Columns with the columns Columns are used to be referred by the Raw, e.g.: #c1, #column2

func (*Fragment) WithFragments 

func (f *Fragment) WithFragments(fragments ...*Fragment)

WithFragments replace f.Fragments with the fragments Fragments are used to be referred by the Raw, e.g.: #fragment1, #fragment2

func (*Fragment) WithTables 

func (f *Fragment) WithTables(tables ...Table)

WithTables replace f.Tables with the tables Tables are used to be referred by the Raw, e.g.: #t1, #table2

type FragmentContext 

type FragmentContext struct {
	Global *Context // global context

	Raw       string
	Args      *ArgsProperty
	Columns   *ColumnsProperty
	Tables    *TablesProperty
	Fragments *FragmentsProperty
	Builders  *BuildersProperty
}

FragmentContext is the FragmentContext for current fragment building.

func (*FragmentContext) CheckUsage 

func (c *FragmentContext) CheckUsage() error

CheckUsage checks if all properties are used.

type FragmentsProperty 

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

FragmentsProperty is the fragments property

func NewFragmentsProperty 

func NewFragmentsProperty(fragments ...*Fragment) *FragmentsProperty

NewFragmentsProperty returns a new FragmentsProperty.

func (*FragmentsProperty) Build 

func (b *FragmentsProperty) Build(ctx *Context, index int) (string, error)

Build builds the fragment at index.

func (FragmentsProperty) CheckUsage 

func (b FragmentsProperty) CheckUsage() error

CheckUsage checks if all items are used.

func (FragmentsProperty) Count 

func (b FragmentsProperty) Count() int

Count returns the count of items.

func (FragmentsProperty) Get 

func (b FragmentsProperty) Get(index int) (T, error)

Get returns the item at index, starting from 1.

func (FragmentsProperty) ReportUsed 

func (b FragmentsProperty) ReportUsed(index int)

ReportUsed reports the item at index is used, starting from 1.

type FuncMap 

type FuncMap map[string]any

FuncMap is the type of the map defining the mapping from names to functions.

type Table 

type Table string

Table is a table identifier, it can be a table name or an alias.

func (Table) Column 

func (t Table) Column(name string) *Column

Column returns a column of the table. It adds table prefix to the column name, e.g.: "id" -> "t.id".

For example: 

t := Table("t")
// these two are equivalent
t.Column("id")         // "t.id"
t.Expression("#t1.id") // "t.id"

If you want to use the column name directly, try: 

t.Expressions("id") // "id"

func (Table) Columns 

func (t Table) Columns(names ...string) []*Column

Columns returns columns of the table from names. It adds table prefix to the column name, e.g.: "id" -> "t.id".

For example: 

t := Table("t")
// these two are equivalent
t.Columns("id", "name")              // "t.id", "t.name"
t.Expressions("#t1.id", "#t1.name")  // "t.id", "t.name"

If you want to use the column name directly, try: 

t.Expressions("id", "name") // "id", "name"

func (Table) Expression 

func (t Table) Expression(expression string, args ...any) *Column

Expression returns a column of the table from the expression it accepts bindvars and the preprocessor #t1 which is implicit in t.

For example: 

t := Table("t")
t.Expression("id")                       // "id"
t.Expression("#t1.id")                   // "table.id"
t.Expression("#t1.id")                  // "t.id"
t.Expression("COALESCE(#t1.id,0)")      // "COALESCE(t.id,0)"
t.Expression("#t1.deteled_at > $1", 1)  // "t.deteled_at > $1"

func (Table) Expressions 

func (t Table) Expressions(expressions ...string) []*Column

Expressions returns columns of the table from the expression, it accepts bindvars and the preprocessor #t1 which is implicit in t.

For example: 

t := Table("t")
t.Expressions("id", "deteled_at")         // "id", "deteled_at"
t.Expressions("#t1.id", "#t1.deteled_at") // "table.id", "table.deteled_at"
t.Expressions("#t1.id", "#t1.deteled_at") // "t.id", "t.deteled_at"
t.Expressions("COALESCE(#t1.id,0)")       // "COALESCE(t.id,0)"

type TablesProperty 

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

TablesProperty is the tables property

func NewTablesProperty 

func NewTablesProperty(tables ...Table) *TablesProperty

NewTablesProperty returns a new TablesProperty.

func (*TablesProperty) Build 

func (b *TablesProperty) Build(ctx *Context, index int) (string, error)

Build builds the table at index.

func (TablesProperty) CheckUsage 

func (b TablesProperty) CheckUsage() error

CheckUsage checks if all items are used.

func (TablesProperty) Count 

func (b TablesProperty) Count() int

Count returns the count of items.

func (TablesProperty) Get 

func (b TablesProperty) Get(index int) (T, error)

Get returns the item at index, starting from 1.

func (TablesProperty) ReportUsed 

func (b TablesProperty) ReportUsed(index int)

ReportUsed reports the item at index is used, starting from 1.

Source Files

View all Source files

Directories

Path Synopsis
Package sqlb is a SQL query builder based on `sqlf.Fragment`.
 Package sqlb is a SQL query builder based on `sqlf.Fragment`.
Package util provides utility functions for SQL.
 Package util provides utility functions for SQL.

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.