sqlexpr

package module
v0.1.2 Latest Latest
Warning

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

 Go to latest Published: Feb 12, 2020 License: MIT Imports: 5 Imported by: 0

README

Idiomatic, simple and powerful SQL Builder for Go

GoDoc

Three goals:

  1. Make constructing SQL queries less tedious (e.g. avoid matching up INSERT field names to values, construct IN expressions easily).

  2. Allow to reuse SQL-related code (e.g. share code that sets fields across INSERTs and UPDATEs, share conditionals across SELECTs, UPDATEs and DELETEs).

  3. Allow to use branches and loops when constructing SQL (e.g. add a WHERE condition inside an if statement)

Features:

  1. Uses plain Go types (e.g. Or is a slice of expressions, Select is a struct with a bunch of fields) that are easy to manipulate without any layers of abstraction.

  2. Allows to insert arbitrary SQL whenever needed (just pass Raw, which is just a type Raw string).

  3. Allows to pick SQL dialect (this is a global setting, because working with multiple different database types in a single app is extremely rate and is not worth polluting the entire code with dialect selection).

Usage

See GoDoc for more details.

Import:

"github.com/andreyvit/sqlexpr"

Define tables and columns:

const (
    accounts   = sqlexpr.Table("accounts")
    id         = sqlexpr.Column("id")
    email      = sqlexpr.Column("email")
    name       = sqlexpr.Column("name")
    notes      = sqlexpr.Column("notes")
    updated_at = sqlexpr.Column("updated_at")
    deleted    = sqlexpr.Column("deleted")
)
Select
includeNotes := true
query := "%abc%"

s := sqlexpr.Select{From: accounts}
s.AddField(id, email, name)
if includeNotes {
    s.AddField(notes)
}
s.AddWhere(sqlexpr.Or{sqlexpr.Like(name, query), sqlexpr.Like(notes, query)})
s.AddWhere(sqlexpr.Not(deleted))

sql, args := sqlexpr.Build(s)

Result:

SELECT id, email, name, notes FROM accounts WHERE (name LIKE $1 OR notes LIKE $2) AND NOT deleted
Insert
s := sqlexpr.Insert{Table: accounts}
s.Set(email, "john@example.com")
s.Set(name, "John Doe")
s.Set(notes, "Little Johny")
s.Set(updated_at, sqlexpr.NOW)
s.AddReturning(id)

sql, args := sqlexpr.Build(s)

Result:

INSERT INTO accounts (email, name, notes, updated_at) VALUES ($1, $2, $3, NOW()) RETURNING id
Update
s := sqlexpr.Update{Table: accounts}
s.Set(email, "john@example.com")
s.Set(name, "John Doe")
s.Set(notes, "Little Johny")
s.Set(updated_at, sqlexpr.NOW)
s.AddWhere(sqlexpr.Eq(id, 42))
s.AddReturning(updated_at)

sql, args := sqlexpr.Build(s)

Result:

UPDATE accounts SET email = $1, name = $2, notes = $3, updated_at = NOW() WHERE id = $4 RETURNING updated_at
Delete
s := sqlexpr.Delete{Table: accounts}
s.AddWhere(sqlexpr.Eq(id, 42))

sql, args := sqlexpr.Build(s)

Result:

DELETE FROM accounts WHERE id = $1

Principles

  1. Everything that this package produces is an sqlexpr.Expr. You can turn an Expr into SQL (plus arguments slice) using sqlexpr.Build(expr).

  2. All names are passed as sqlexpr.Table and sqlexpr.Column, all SQL keywords, punctuation and raw SQL code as sqlexpr.Raw. These are all just strings, defined as new types.

  3. Any value that is not Expr becomes an argument (i.e. adds a placeholders like $1 or ? into the SQL statement).

  4. There are four top-level types of Exprs: sqlexpr.Select, sqlexpr.Insert, sqlexpr.Update and sqlexpr.Delete. These are structs with very simple fields that you can fill.

  5. These four structs define a few simple helper methods, e.g. AddWhere appends the given condition to .Where slice. The helpers both simplify your code and allow code reuse via interfaces:

    // Settable represents INSERTs and UPDATEs
type Settable interface {
    Set(field Expr, value interface{})
}
// Whereable represents SELECTs, UPDATEs and DELETEs
type Whereable interface {
    AddWhere(conds ...Expr)
}
// Fieldable represents SELECTs (SELECT field list) and INSERTs/UPDATEs/DELETEs (RETURNING clause)
type Fieldable interface {
    AddField(fields ...Expr)
}

  6. All other expressions can be produced via types and functions in this package. See the tests for examples.

Development

Use modd (go get github.com/cortesi/modd/cmd/modd) to re-run tests automatically during development by running modd (recommended).

License

MIT.

Documentation

Index

Examples

Constants

View Source 
const (
	Empty     = Raw("")
	Star      = Raw("*")
	TRUE      = Raw("TRUE")
	FALSE     = Raw("FALSE")
	NOW       = Raw("NOW()")
	ForUpdate = Raw("FOR UPDATE")
)

Variables

View Source 
var MySQLDialect = &Dialect{
	Name:     "MySQL",
	ArgStyle: QuestionMarkArgs,
}
View Source 
var PostgresDialect = &Dialect{
	Name:     "PostgreSQL",
	ArgStyle: DollarNumberArgs,
}
View Source 
var SQLiteDialect = &Dialect{
	Name:     "SQLite",
	ArgStyle: QuestionMarkArgs,
}

Functions

func Build 

func Build(e Expr) (sql string, args []interface{})

func Exec 

func Exec(ctx context.Context, ex Executor, expr Expr) (sql.Result, error)

func FormatSQLArgs 

func FormatSQLArgs(sql string, args []interface{}) string

func Query 

func Query(ctx context.Context, ex Executor, expr Expr) (*sql.Rows, error)

func QueryRow 

func QueryRow(ctx context.Context, ex Executor, expr Expr) *sql.Row

func SetDialect 

func SetDialect(d *Dialect)

Types

type And 

type And []interface{}

func (And) AppendToSQLBuilder 

func (v And) AppendToSQLBuilder(b *Builder)

type ArgStyle 

type ArgStyle int
const (
	QuestionMarkArgs ArgStyle = iota
	DollarNumberArgs
)

type Array 

type Array []interface{}

func ArrayOfInt64s 

func ArrayOfInt64s(items []int64) Array

func ArrayOfInts 

func ArrayOfInts(items []int) Array

func ArrayOfStrings 

func ArrayOfStrings(items []string) Array

func (Array) AppendToSQLBuilder 

func (v Array) AppendToSQLBuilder(b *Builder)

func (Array) At added in v0.1.2 

func (v Array) At(i int) interface{}

func (Array) Count added in v0.1.2 

func (v Array) Count() int

type Builder 

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

func (*Builder) Append 

func (b *Builder) Append(item interface{})

func (*Builder) AppendAll 

func (b *Builder) AppendAll(items ...interface{})

func (*Builder) AppendExpr 

func (b *Builder) AppendExpr(expr Expr)

func (*Builder) AppendName 

func (b *Builder) AppendName(s string)

func (*Builder) AppendRaw 

func (b *Builder) AppendRaw(s string)

func (*Builder) Args 

func (b *Builder) Args() []interface{}

func (*Builder) SQL 

func (b *Builder) SQL() string

func (*Builder) SQLArgs 

func (b *Builder) SQLArgs() (sql string, args []interface{})

func (*Builder) String 

func (b *Builder) String() string

type Clause 

type Clause struct {
	Start     string
	Separator string
	Items     []Expr
}

func (Clause) AppendToSQLBuilder 

func (v Clause) AppendToSQLBuilder(b *Builder)

type Column 

type Column string

func (Column) AppendToSQLBuilder 

func (v Column) AppendToSQLBuilder(b *Builder)

type Delete 

type Delete struct {
	Table     Expr
	Leading   Expr
	Where     Where
	Trailing  Expr
	Returning Returning
}
Example 
package main

import (
	"fmt"
	"github.com/andreyvit/sqlexpr"
)

func main() {
	const (
		accounts = sqlexpr.Table("accounts")
		id       = sqlexpr.Column("id")
		email    = sqlexpr.Column("email")
		name     = sqlexpr.Column("name")
		notes    = sqlexpr.Column("notes")
		deleted  = sqlexpr.Column("deleted")
	)

	s := sqlexpr.Delete{Table: accounts}
	s.AddWhere(sqlexpr.Eq(id, 42))

	sql, args := sqlexpr.Build(s)
	fmt.Println(sql)
	fmt.Printf("%#v", args)

}

Output:

DELETE FROM accounts WHERE id = $1
[]interface {}{42}

func (*Delete) AddField 

func (s *Delete) AddField(fields ...Expr)

func (*Delete) AddWhere 

func (s *Delete) AddWhere(conds ...Expr)

func (Delete) AppendToSQLBuilder 

func (s Delete) AppendToSQLBuilder(b *Builder)

func (*Delete) Exec 

func (s *Delete) Exec(ctx context.Context, ex Executor) (sql.Result, error)

func (*Delete) Query 

func (s *Delete) Query(ctx context.Context, ex Executor) (*sql.Rows, error)

func (*Delete) QueryRow 

func (s *Delete) QueryRow(ctx context.Context, ex Executor) *sql.Row

type Dialect 

type Dialect struct {
	Name     string
	ArgStyle ArgStyle
}

func (*Dialect) FormatPlaceholder 

func (d *Dialect) FormatPlaceholder(index int) string

type Executor 

type Executor interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Executor is compatible with *sql.DB, *sql.Tx (or an arbitrary middleman)

type Expr 

type Expr interface {
	AppendToSQLBuilder(b *Builder)
}

func As 

func As(v Expr, name Column) Expr

func Asc 

func Asc(v Expr) Expr

func Count 

func Count(v Expr) Expr

func Desc 

func Desc(v Expr) Expr

func Dot 

func Dot(a, b Expr) Expr

func Eq 

func Eq(lhs interface{}, rhs interface{}) Expr

func Func 

func Func(name string, args ...interface{}) Expr

func In 

func In(lhs interface{}, items Expr) Expr

func InnerJoin 

func InnerJoin(a Expr, aCol Column, b Expr, bCol Column) Expr

func IsNotNull 

func IsNotNull(v interface{}) Expr

func IsNull 

func IsNull(v interface{}) Expr

func Like 

func Like(lhs interface{}, rhs interface{}) Expr

func LikeCaseInsensitive 

func LikeCaseInsensitive(lhs interface{}, rhs interface{}) Expr

func Max 

func Max(v Expr) Expr

func MaybeForUpdate 

func MaybeForUpdate(forUpdate bool) Expr

func Min 

func Min(v Expr) Expr

func Not 

func Not(v interface{}) Expr

func NotLike 

func NotLike(lhs interface{}, rhs interface{}) Expr

func NotLikeCaseInsensitive 

func NotLikeCaseInsensitive(lhs interface{}, rhs interface{}) Expr

func Op 

func Op(lhs interface{}, op string, rhs interface{}) Expr

func Parens added in v0.1.1 

func Parens(v Expr) Expr

func Qualified added in v0.1.1 

func Qualified(exprs ...Expr) Expr

func Value 

func Value(v interface{}) Expr

type Fieldable 

type Fieldable interface {
	AddField(fields ...Expr)
}

Fieldable represents SELECTs (SELECT field list) and INSERTs/UPDATEs/DELETEs (RETURNING clause)

type Fragment 

type Fragment []interface{}

func (Fragment) AppendToSQLBuilder 

func (v Fragment) AppendToSQLBuilder(b *Builder)

type Insert 

type Insert struct {
	Leading   Expr
	Table     Expr
	Setters   []Setter
	Trailing  Expr
	Returning Returning
}
Example 
package main

import (
	"fmt"
	"github.com/andreyvit/sqlexpr"
)

func main() {
	const (
		accounts   = sqlexpr.Table("accounts")
		id         = sqlexpr.Column("id")
		email      = sqlexpr.Column("email")
		name       = sqlexpr.Column("name")
		notes      = sqlexpr.Column("notes")
		updated_at = sqlexpr.Column("updated_at")
		deleted    = sqlexpr.Column("deleted")
	)

	s := sqlexpr.Insert{Table: accounts}
	s.Set(email, "john@example.com")
	s.Set(name, "John Doe")
	s.Set(notes, "Little Johny")
	s.Set(updated_at, sqlexpr.NOW)
	s.AddField(id)

	sql, args := sqlexpr.Build(s)
	fmt.Println(sql)
	fmt.Printf("%#v", args)

}

Output:

INSERT INTO accounts (email, name, notes, updated_at) VALUES ($1, $2, $3, NOW()) RETURNING id
[]interface {}{"john@example.com", "John Doe", "Little Johny"}

func (*Insert) AddField 

func (s *Insert) AddField(fields ...Expr)

func (Insert) AppendToSQLBuilder 

func (s Insert) AppendToSQLBuilder(b *Builder)

func (*Insert) Exec 

func (s *Insert) Exec(ctx context.Context, ex Executor) (sql.Result, error)

func (*Insert) Query 

func (s *Insert) Query(ctx context.Context, ex Executor) (*sql.Rows, error)

func (*Insert) QueryRow 

func (s *Insert) QueryRow(ctx context.Context, ex Executor) *sql.Row

func (*Insert) Set 

func (s *Insert) Set(field Expr, value interface{})

type LimitExpr 

type LimitExpr int

func (LimitExpr) AppendToSQLBuilder 

func (v LimitExpr) AppendToSQLBuilder(b *Builder)

type List 

type List []Expr

func (List) AppendToSQLBuilder 

func (v List) AppendToSQLBuilder(b *Builder)

type Or 

type Or []interface{}

func (Or) AppendToSQLBuilder 

func (v Or) AppendToSQLBuilder(b *Builder)

type OrderBy 

type OrderBy []Expr

func (OrderBy) AppendToSQLBuilder 

func (v OrderBy) AppendToSQLBuilder(b *Builder)

type Raw 

type Raw string

func (Raw) AppendToSQLBuilder 

func (v Raw) AppendToSQLBuilder(b *Builder)

type Returning 

type Returning []Expr

func (Returning) AppendToSQLBuilder 

func (v Returning) AppendToSQLBuilder(b *Builder)

type Select 

type Select struct {
	Leading  Expr
	From     Expr
	Fields   List
	Where    Where
	Grouping Expr
	OrderBy  OrderBy
	Limit    int
	Trailing Expr
}
Example 
package main

import (
	"fmt"
	"github.com/andreyvit/sqlexpr"
)

func main() {
	const (
		accounts = sqlexpr.Table("accounts")
		id       = sqlexpr.Column("id")
		email    = sqlexpr.Column("email")
		name     = sqlexpr.Column("name")
		notes    = sqlexpr.Column("notes")
		deleted  = sqlexpr.Column("deleted")
	)

	includeNotes := true
	query := "%abc%"

	s := sqlexpr.Select{From: accounts}
	s.AddField(id, email, name)
	if includeNotes {
		s.AddField(notes)
	}
	s.AddWhere(sqlexpr.Or{sqlexpr.Like(name, query), sqlexpr.Like(notes, query)})
	s.AddWhere(sqlexpr.Not(deleted))

	sql, args := sqlexpr.Build(s)
	fmt.Println(sql)
	fmt.Printf("%#v", args)

}

Output:

SELECT id, email, name, notes FROM accounts WHERE (name LIKE $1 OR notes LIKE $2) AND NOT deleted
[]interface {}{"%abc%", "%abc%"}

func (*Select) AddField 

func (s *Select) AddField(fields ...Expr)

func (*Select) AddWhere 

func (s *Select) AddWhere(conds ...Expr)

func (Select) AppendToSQLBuilder 

func (s Select) AppendToSQLBuilder(b *Builder)

func (*Select) Query 

func (s *Select) Query(ctx context.Context, ex Executor) (*sql.Rows, error)

func (*Select) QueryRow 

func (s *Select) QueryRow(ctx context.Context, ex Executor) *sql.Row

type Settable 

type Settable interface {
	Set(field Expr, value interface{})
}

Settable represents INSERTs and UPDATEs

type Setter 

type Setter struct {
	Field Expr
	Value interface{}
}

type Table 

type Table string

func (Table) AppendToSQLBuilder 

func (v Table) AppendToSQLBuilder(b *Builder)

type Update 

type Update struct {
	Table     Expr
	Leading   Expr
	Setters   []Setter
	Where     Where
	Trailing  Expr
	Returning Returning
}
Example 
package main

import (
	"fmt"
	"github.com/andreyvit/sqlexpr"
)

func main() {
	const (
		accounts   = sqlexpr.Table("accounts")
		id         = sqlexpr.Column("id")
		email      = sqlexpr.Column("email")
		name       = sqlexpr.Column("name")
		notes      = sqlexpr.Column("notes")
		updated_at = sqlexpr.Column("updated_at")
		deleted    = sqlexpr.Column("deleted")
	)

	s := sqlexpr.Update{Table: accounts}
	s.Set(email, "john@example.com")
	s.Set(name, "John Doe")
	s.Set(notes, "Little Johny")
	s.Set(updated_at, sqlexpr.NOW)
	s.AddWhere(sqlexpr.Eq(id, 42))
	s.AddField(updated_at)

	sql, args := sqlexpr.Build(s)
	fmt.Println(sql)
	fmt.Printf("%#v", args)

}

Output:

UPDATE accounts SET email = $1, name = $2, notes = $3, updated_at = NOW() WHERE id = $4 RETURNING updated_at
[]interface {}{"john@example.com", "John Doe", "Little Johny", 42}

func (*Update) AddField 

func (s *Update) AddField(fields ...Expr)

func (*Update) AddWhere 

func (s *Update) AddWhere(conds ...Expr)

func (Update) AppendToSQLBuilder 

func (s Update) AppendToSQLBuilder(b *Builder)

func (*Update) Exec 

func (s *Update) Exec(ctx context.Context, ex Executor) (sql.Result, error)

func (*Update) Query 

func (s *Update) Query(ctx context.Context, ex Executor) (*sql.Rows, error)

func (*Update) QueryRow 

func (s *Update) QueryRow(ctx context.Context, ex Executor) *sql.Row

func (*Update) Set 

func (s *Update) Set(field Expr, value interface{})

type Where 

type Where []Expr

func (Where) AppendToSQLBuilder 

func (v Where) AppendToSQLBuilder(b *Builder)

type Whereable 

type Whereable interface {
	AddWhere(conds ...Expr)
}

Whereable represents SELECTs, UPDATEs and DELETEs

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.