superbasic

README

The superbasic SQL-Builder

This package provides utilities to build complex and dynamic but secure SQL queries.

go get github.com/wroge/superbasic

Base Building-Blocks

The base building-blocks are superbasic.SQL(sql, expr...), superbasic.Append(expr...), superbasic.Join(sep, expr...), superbasic.If(condition, then) and superbasic.IfElse(condition, then, else) All further expressions are based on these functions.

search := "biden"

sql, args, err := superbasic.Append(
	superbasic.SQL("SELECT id, first, last FROM presidents"),
	superbasic.If(search != "", superbasic.SQL(" WHERE last = ?", search)),
).ToSQL()
// SELECT id, first, last FROM presidents WHERE last = ? [Biden] <nil>

Types and Interfaces

Expressions within the arguments, like superbasic.Expression or superbasic.Sqlizer, are recognized and handled accordingly. Unknown arguments are built into the expression using placeholders. Slices of expressions, like []superbasic.Expression, are joined by the separator in the superbasic.Join expression or by ", " in all other cases.

sql, args, err = superbasic.SQL("SELECT ? FROM presidents", 
	[]superbasic.Expression{superbasic.SQL("first"), superbasic.SQL("last")}).ToSQL()
// SELECT first, last FROM presidents

Build-in Queries

In addition, there are expressions, such as superbasic.Query or superbasic.Insert, that allow you to build the queries even more easily. Sometimes it is more readable to use the helper functions like superbasic.Andand superbasic.Equals, therefore this library also offers an increasing amount of utility-functions.

This example shows a query where the resulting sql expression is translated into Postgres format.

query := superbasic.Query{
	Select: superbasic.SQL("p.id, p.first, p.last"),
	From:   superbasic.SQL("presidents AS p"),
	Where: superbasic.And(
		superbasic.Equals("p.last", "Bush"),
		superbasic.NotEquals("p.first", "George W."),
	),
	// Could also be written as:
	// Where: superbasic.Join(" AND ",
	// 		superbasic.SQL("p.last = ?", "Bush"),
	// 		superbasic.SQL("p.first <> ?", "George W."),
	// ),
	OrderBy: superbasic.SQL("p.last"),
	Limit:   3,
}

sql, args, err = superbasic.ToPostgres(query)
// SELECT p.id, p.first, p.last FROM presidents AS p WHERE p.last = $1 AND p.first <> $2 ORDER BY p.last LIMIT 3
// [Bush George W.]

All expressions can, of course, be nested within each other to produce new expressions. The superbasic.Insert example shows how to append the Postgres-specific expression RETURNING id.

insert := superbasic.Append(
	superbasic.Insert{
		Into:    "presidents",
		Columns: []string{"first", "last"},
		Values: [][]any{
			{"Joe", "Bden"},
			{"Donald", "Trump"},
			{"Barack", "Obama"},
			{"George W.", "Bush"},
			{"Bill", "Clinton"},
			{"George H. W.", "Bush"},
		},
	},
	superbasic.SQL(" RETURNING id"),
)

sql, args, err := superbasic.ToPostgres(insert)
// INSERT INTO presidents (first, last) VALUES ($1, $2), ($3, $4), ($5, $6), ($7, $8), ($9, $10), ($11, $12) RETURNING id 
// [Joe Bden Donald Trump Barack Obama George W. Bush Bill Clinton George H. W. Bush]

If you prefer to write the SQL queries yourself, you are of course welcome to do so.

sql, args, err := superbasic.SQL("INSERT INTO presidents (first, last) VALUES ? RETURNING id", superbasic.Values(
	[]any{"Joe", "Bden"},
	[]any{"Donald", "Trump"},
	[]any{"Barack", "Obama"},
	[]any{"George W.", "Bush"},
	[]any{"Bill", "Clinton"},
	[]any{"George H. W.", "Bush"},
)).ToSQL()
// INSERT INTO presidents (first, last) VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?) RETURNING id 
// [Joe Bden Donald Trump Barack Obama George W. Bush Bill Clinton George H. W. Bush]

Customized Expressions

The next section shows the superbasic.Query expression. Here you can see how easy it is to create your own expressions.

type Query struct {
	Select  any
	From    any
	Where   any
	GroupBy any
	Having  any
	OrderBy any
	Limit   uint64
	Offset  uint64
}

func (q Query) ToSQL() (string, []any, error) {
	return Append(
		SQL("SELECT ?", IfElse(q.Select != nil, q.Select, SQL("*"))),
		If(q.From != nil, SQL(" FROM ?", q.From)),
		If(q.Where != nil, SQL(" WHERE ?", q.Where)),
		If(q.GroupBy != nil, SQL(" GROUP BY ?", q.GroupBy)),
		If(q.Having != nil, SQL(" HAVING ?", q.Having)),
		If(q.OrderBy != nil, SQL(" ORDER BY ?", q.OrderBy)),
		If(q.Limit > 0, SQL(fmt.Sprintf(" LIMIT %d", q.Limit))),
		If(q.Offset > 0, SQL(fmt.Sprintf(" OFFSET %d", q.Offset))),
	).ToSQL()
}

DDL expressions

It is also possible to create DDL queries, for example using the predefined expression superbasic.Table.

table := superbasic.Table{
	IfNotExists: true,
	Name:        "presidents",
	Columns: []superbasic.Sqlizer{
		superbasic.Column{
			Name: "id",
			Type: "SERIAL",
			Constraints: []superbasic.Sqlizer{
				superbasic.SQL("PRIMARY KEY"),
			},
		},
		superbasic.SQL("first TEXT NOT NULL"),
		superbasic.SQL("last TEXT NOT NULL"),
	},
	Constraints: superbasic.SQL("UNIQUE (first, last)"),
}

sql, err := superbasic.ToDDL(table)
// CREATE TABLE IF NOT EXISTS presidents (id SERIAL PRIMARY KEY, first TEXT NOT NULL, last TEXT NOT NULL, UNIQUE (first, last))

SQL Builder

Of course, there is also a builder that can be used to create the SQL query. Here is an example:

b := superbasic.NewBuilder()

b.WriteSQL("SELECT ").WriteSQL("first, last")
b.WriteSQL(" FROM presidents")
b.WriteSQL(" WHERE ")
b.Write(superbasic.Join(" OR ", superbasic.SQL("last = ?", "Bush"), superbasic.SQL("first = ?", "Joe")))

sql, args, err := b.ToSQL()
// SELECT first, last FROM presidents WHERE last = ? OR first = ? 
// [Bush Joe]

Documentation

Index

• Variables
• func ToDDL(expr any) (string, error)
• func ToPostgres(expr any) (string, []any, error)
• type Builder
  • func NewBuilder() *Builder
  • func (b *Builder) ToSQL() (string, []any, error)
  • func (b *Builder) Write(expr any) *Builder
  • func (b *Builder) WriteSQL(sql string, args ...any) *Builder
• type Column
  • func (cs Column) ToSQL() (string, []any, error)
• type Delete
  • func (d Delete) ToSQL() (string, []any, error)
• type Expression
  • func And(expr ...Sqlizer) Expression
  • func Append(expr ...any) Expression
  • func Equals(ident string, value any) Expression
  • func Error(err error) Expression
  • func Greater(ident string, value any) Expression
  • func GreaterOrEquals(ident string, value any) Expression
  • func If(cond bool, then any) Expression
  • func IfElse(cond bool, then any, els any) Expression
  • func Join(sep string, expr ...any) Expression
  • func Less(ident string, value any) Expression
  • func LessOrEquals(ident string, value any) Expression
  • func Not(expr Sqlizer) Expression
  • func NotEquals(ident string, value any) Expression
  • func Or(left, right Sqlizer) Expression
  • func SQL(sql string, args ...any) Expression
  • func Values(data ...[]any) Expression
  • func (e Expression) ToSQL() (string, []any, error)
• type Insert
  • func (i Insert) ToSQL() (string, []any, error)
• type Query
  • func (q Query) ToSQL() (string, []any, error)
• type Sqlizer
• type Table
  • func (ct Table) ToSQL() (string, []any, error)
• type Update
  • func (u Update) ToSQL() (string, []any, error)

Variables

var ErrInvalidNumberOfArguments = errors.New("invalid number of arguments")

Functions

func ToDDL

func ToDDL(expr any) (string, error)

func ToPostgres

func ToPostgres(expr any) (string, []any, error)

Types

type Builder

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

func NewBuilder

func NewBuilder() *Builder

func (*Builder) ToSQL

func (b *Builder) ToSQL() (string, []any, error)

func (*Builder) Write

func (b *Builder) Write(expr any) *Builder

func (*Builder) WriteSQL

func (b *Builder) WriteSQL(sql string, args ...any) *Builder

type Column

type Column struct {
	Name        string
	Type        string
	Constraints any
}

func (Column) ToSQL

func (cs Column) ToSQL() (string, []any, error)

type Delete

type Delete struct {
	From  string
	Where any
}

func (Delete) ToSQL

func (d Delete) ToSQL() (string, []any, error)

type Expression

type Expression struct {
	SQL  string
	Args []any
	Err  error
}

func And

func And(expr ...Sqlizer) Expression

func Append

func Append(expr ...any) Expression

func Equals

func Equals(ident string, value any) Expression

func Error

func Error(err error) Expression

func Greater

func Greater(ident string, value any) Expression

func GreaterOrEquals

func GreaterOrEquals(ident string, value any) Expression

func If

func If(cond bool, then any) Expression

func IfElse

func IfElse(cond bool, then any, els any) Expression

func Join

func Join(sep string, expr ...any) Expression

func Less

func Less(ident string, value any) Expression

func LessOrEquals

func LessOrEquals(ident string, value any) Expression

func Not

func Not(expr Sqlizer) Expression

func NotEquals

func NotEquals(ident string, value any) Expression

func Or

func Or(left, right Sqlizer) Expression

func SQL

func SQL(sql string, args ...any) Expression

func Values

func Values(data ...[]any) Expression

func (Expression) ToSQL

func (e Expression) ToSQL() (string, []any, error)

type Insert

type Insert struct {
	Into    string
	Columns []string
	Values  [][]any
}

func (Insert) ToSQL

func (i Insert) ToSQL() (string, []any, error)

type Query

type Query struct {
	Select  any
	From    any
	Where   any
	GroupBy any
	Having  any
	OrderBy any
	Limit   uint64
	Offset  uint64
}

func (Query) ToSQL

func (q Query) ToSQL() (string, []any, error)

type Sqlizer

type Sqlizer interface {
	ToSQL() (string, []any, error)
}

type Table

type Table struct {
	IfNotExists bool
	Name        string
	Columns     any
	Constraints any
}

func (Table) ToSQL

func (ct Table) ToSQL() (string, []any, error)

type Update

type Update struct {
	Table string
	Set   any
	Where any
}

func (Update) ToSQL

func (u Update) ToSQL() (string, []any, error)