sqlf

package module
v2.0.0-beta.5 Latest Latest
Warning

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

 Go to latest Published: Oct 20, 2024 License: MIT Imports: 7 Imported by: 0

README

Package sqlf focuses on building SQL queries by free combination of fragments.

The package exports only a few functions and methods, but improves a lot on the reusability and extensibility of SQL, which are the main challenges we encounter when writing SQL.

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, which uses exactly the same syntax as database/sql, but provides the ability to combine them in any way.

import (
	"fmt"
	"github.com/qjebbs/go-sqlf/v2"
)
func Example_basic() {
	query, args, _ := sqlf.Ff(
		"SELECT * FROM foo WHERE #join('#fragment', ' AND ')", // join fragments
		sqlf.Fa("baz = $1", true),                             // `database/sql` style
		sqlf.Fa("bar BETWEEN ? AND ?", 1, 100),                // `database/sql` style
	).BuildQuery(syntax.Dollar)
	fmt.Println(query)
	fmt.Println(args)
	// Output:
	// SELECT * FROM foo WHERE baz = $1 AND bar BETWEEN $2 AND $3
	// [true 1 100]
}

Explanation:

  • We pay attention only to the references inside a fragment, not between fragments.
  • #join, #arg, #f, etc., are preprocessing functions, which will be explained later.
  • See Example_deeperLook of example_test.go for what happend inside the *sqlf.Fragment.

Preprocessing Functions

name description example
f, fragment fragments at index #f1, #fragment1
join Join the template with separator #join('#f', ' AND ')
Join from index 3 to end #join('#f', ',', 3)
Join from index 3 to 6 #join('#f', ',', 3, 6)
arg arguments at index #join('#arg', ',')

Note:

  • #f1 is equivalent to #f(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.

See Example ContextWithFuncs of example_test.go for how to register custom preprocessing functions, and implementing global arguments/fragments.

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.

The package exports only a few functions and methods, but improves a lot on the reusability and extensibility of SQL, which are the main challenges we encounter when writing SQL.

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, which uses exactly the same syntax as `database/sql`, but provides the ability to combine them in any way. 

query, args, _ := sqlf.Ff(
	"SELECT * FROM foo WHERE #join('#fragment', ' AND ')", // join fragments
	sqlf.Fa("baz = $1", true),                             // `database/sql` style
	sqlf.Fa("bar BETWEEN ? AND ?", 1, 100),                // `database/sql` style
).BuildQuery(syntax.Dollar)
fmt.Println(query)
fmt.Println(args)
// Output:
// SELECT * FROM foo WHERE baz = $1 AND bar BETWEEN $2 AND $3
// [true 1 100]

Explanation:

  • We pay attention only to the references inside a fragment, not between fragments.
  • #join, #f, etc., are preprocessing functions, which will be explained later.
  • See Example (DeeperLook) for what happend inside the *sqlf.Fragment.

Preprocessing Functions

  • f, fragment: fragments at index, e.g. #f1
  • join: Join the template with separator, e.g. #join('#f', ', '), #join('#arg', ',', 3), #join('#arg', ',', 3, 6)
  • arg: arguments at index, usually used in #join().

Note:

  • #f1 is equivalent to #f(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 ContextWithFuncs.
Example (Basic) 
query, args, _ := sqlf.Ff(
	"SELECT * FROM foo WHERE #join('#fragment', ' AND ')", // join fragments
	sqlf.Fa("baz = $1", true),                             // `database/sql` style
	sqlf.Fa("bar BETWEEN ? AND ?", 1, 100),                // `database/sql` style
).BuildQuery(syntax.Dollar)
fmt.Println(query)
fmt.Println(args)

Output:

SELECT * FROM foo WHERE baz = $1 AND bar BETWEEN $2 AND $3
[true 1 100]
Example (DeeperLook) 
// This example is equivalent to Exmaple Basic (which is more concise), but
// it reveales what happend inside a *sqlf.Fragment.

// *sqlf.Fragment has two types of properties storage, .Args and .Fragments.
// Raw query can reference the contents of .Args, just like `database/sql`.
a := &sqlf.Fragment{
	Raw:  "baz = $1",
	Args: []any{true},
}
b := &sqlf.Fragment{
	Raw:  "bar BETWEEN ? AND ?",
	Args: []any{1, 100},
}
query, args, _ := (&sqlf.Fragment{
	// Similarly, referencing .Fragments results fragments combinations.
	Raw:       "SELECT * FROM foo WHERE #join('#fragment', ' AND ')",
	Fragments: []sqlf.FragmentBuilder{a, b},
}).BuildQuery(syntax.Dollar)
fmt.Println(query)
fmt.Println(args)

Output:

SELECT * FROM foo WHERE baz = $1 AND bar BETWEEN $2 AND $3
[true 1 100]
Example (Select) 
selects := sqlf.Ff("SELECT #join('#fragment', ', ')")
from := sqlf.Ff("FROM #f1")
where := sqlf.Ff("#join('#fragment', ' AND ')").WithPrefix("WHERE")
builder := sqlf.Ff("#join('#fragment', ' ')", selects, from, where)

var users sqlb.Table = "users"
selects.WithFragments(
	users.AnonymousColumn("id"),
	users.AnonymousColumn("name"),
	users.AnonymousColumn("email"),
)
from.WithFragments(users)
where.WithFragments(
	sqlf.F("#f1 IN (#join('#arg', ', '))").
		WithFragments(users.AnonymousColumn("id")).
		WithArgs(1, 2, 3),
)
where.AppendFragments(
	sqlf.F("#f1 = $1").
		WithFragments(users.AnonymousColumn("active")).
		WithArgs(true),
)

query, args, err := builder.BuildQuery(syntax.Dollar)
if err != nil {
	fmt.Println(err)
	return
}
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 (Update) 
// consider wrapping it with your own builder to provide a more friendly APIs
update := sqlf.Fa("UPDATE #f1")
fieldValues := sqlf.Fa("SET #join('#fragment=#arg', ', ')")
where := sqlf.Fa("#join('#fragment', ' AND ')").WithPrefix("WHERE")
builder := sqlf.Ff("#join('#fragment', ' ')", update, fieldValues, where)

var users sqlb.Table = "users"
update.WithFragments(users)
fieldValues.WithFragments(
	users.AnonymousColumn("name"),
	users.AnonymousColumn("email"),
)
fieldValues.WithArgs("alice", "alice@example.org")
where.AppendFragments(
	sqlf.F("#f1=$1").
		WithFragments(users.AnonymousColumn("id")).
		WithArgs(1),
)

query, args, err := builder.BuildQuery(syntax.Dollar)
if err != nil {
	fmt.Println(err)
	return
}
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.
	// It's a required behaviour for a custom #func to be compatible with #join.
	ErrInvalidIndex = errors.New("invalid index")
)

Functions

This section is empty.

Types

type Context 

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

Context is the global context shared between all fragments building.

func ContextWithFuncs 

func ContextWithFuncs(c *Context, funcs FuncMap) (*Context, error)

ContextWithFuncs returns a new context with the preprocessing functions added.

Example 
// this example shows how to use Global Args by using
// sqlf.NewArgsProperties and custom function, so that we
// don't have to put Args into every fragment, which leads
// to a list of redundant args.
ids := sqlf.NewArgsProperties(1, 2, 3)
ctx, err := sqlf.ContextWithFuncs(sqlf.NewContext(syntax.Dollar), sqlf.FuncMap{
	"_id": func(ctx *sqlf.Context, i int) (string, error) {
		return ids.Build(ctx, i)
	},
})
if err != nil {
	fmt.Println(err)
	return
}
fragment := sqlf.Ff(
	"#join('#fragment', '\nUNION\n')",
	sqlf.Fa("SELECT id, 'foo' typ, count FROM foo WHERE id IN (#join('#_id', ', '))"),
	sqlf.Fa("SELECT id, 'bar' typ, count FROM bar WHERE id IN (#join('#_id', ', '))"),
)
query, err := fragment.BuildFragment(ctx)
if err != nil {
	fmt.Println(err)
	return
}
fmt.Println(query)
fmt.Println(ctx.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]

func NewContext 

func NewContext(bindVarStyle syntax.BindVarStyle) *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) string

CommitArg commits an built arg to the context and returns the built bindvar.

It's used usually in the implementation of a FragmentBuilder, most users don't need to care about it.

type Fragment 

type Fragment struct {
	Raw       string            // Raw string support bind vars (?, $1) and preprocessing functions (#join).
	Args      []any             // Args can be referenced by the Raw, for example: ?, $1
	Fragments []FragmentBuilder // Fragments can be referenced by the Raw, for example: #f1, #fragment1
	Prefix    string            // Prefix is added before the fragment only when the fragment is built not empty.
	Suffix    string            // Suffix is added after the fragment only when the fragment is built not empty.
}

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

func F 

func F(raw string) *Fragment

F creates a new Fragment.

func Fa 

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

Fa creates a new Fragment with args.

func Ff 

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

Ff creates a new Fragment with fragments.

func (*Fragment) AppendArgs 

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

AppendArgs appends args to f.

func (*Fragment) AppendFragments 

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

AppendFragments appends fragments to f.

func (*Fragment) BuildFragment 

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

BuildFragment builds the fragment with context.

func (*Fragment) BuildQuery 

func (f *Fragment) BuildQuery(bindVarStyle syntax.BindVarStyle) (query string, args []any, err error)

BuildQuery builds the fragment as full query.

func (*Fragment) WithArgs 

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

WithArgs sets the args of f.

func (*Fragment) WithFragments 

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

WithFragments sets the fragments of f.

func (*Fragment) WithPrefix 

func (f *Fragment) WithPrefix(prefix string) *Fragment

WithPrefix sets the prefix which is added before the fragment only when the f is built not empty.

func (*Fragment) WithSuffix 

func (f *Fragment) WithSuffix(suffix string) *Fragment

WithSuffix sets the suffix which is added before the fragment only when the f is built not empty.

type FragmentBuilder 

type FragmentBuilder interface {
	// BuildFragment builds as a fragment with the context.
	// The args should be committed to the ctx if any.
	BuildFragment(ctx *Context) (query string, err error)
}

FragmentBuilder is a builder that builds a fragment.

type FragmentContext 

type FragmentContext struct {
	Fragment  *Fragment
	Args      Properties
	Fragments Properties
}

FragmentContext is the context for current fragment building.

type FuncMap 

type FuncMap map[string]any

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

The function names are 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.Context: 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.Context, i int) (string, error)  {/* ... */},
	// #string('string')
	"string": func(str string) (string, error)  {/* ... */},
	// #numbers(1,2)
	"numbers": func(ctx *sqlf.Context, a, b int) string  {/* ... */},
}

type Properties 

type Properties []Property

Properties is a list of properties.

func NewArgsProperties 

func NewArgsProperties(args ...any) Properties

NewArgsProperties creates new properties from args. It's useful for creating global arg properties shared between fragments, see examples for ContextWithFuncs() for how to use it.

func NewFragmentProperties 

func NewFragmentProperties(fragments ...FragmentBuilder) Properties

NewFragmentProperties creates new properties from FragmentBuilder. It's useful for creating global fragment properties shared between fragments, see examples for ContextWithFuncs() for how to use it.

func (Properties) Build 

func (p Properties) Build(ctx *Context, i int) (string, error)

Build builds the propery at i. it returns ErrInvalidIndex when the i is out of range, which is the required behaviour for a custom #func to be compatible with #join.

See examples for ContextWithFuncs() for how to use it.

type Property 

type Property interface {
	FragmentBuilder
	// Used reports if the property is used.
	Used() bool
	// ReportUsed marks current property as used
	ReportUsed()
}

Property is the interface for properties.

type QueryBuilder 

type QueryBuilder interface {
	// BuildQuery builds and returns the query and args.
	BuildQuery(bindVarStyle syntax.BindVarStyle) (query string, args []any, err error)
}

QueryBuilder is the interface for sql builders.

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.