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.Select or superbasic.Insert, that allow you to build the queries even more easily. This example shows a query where the resulting sql expression is translated into Postgres format.

query := superbasic.Select{
	Columns: []superbasic.Sqlizer{
		superbasic.SQL("id"),
		superbasic.SQL("first"),
		superbasic.SQL("last"),
	},
	From: []superbasic.Sqlizer{
		superbasic.SQL("presidents"),
	},
	Where: superbasic.SQL("? OR ?",
		superbasic.SQL("last = ?", "Bush"), superbasic.SQL("first = ?", "Joe")),
	OrderBy: []superbasic.Sqlizer{
		superbasic.SQL("last"),
	},
	Limit: 3,
}

sql, args, err = superbasic.ToPostgres(query)
// SELECT id, first, last FROM presidents WHERE last = $1 OR first = $2 ORDER BY last LIMIT 3
// [Bush Joe]

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.Select expression. Here you can see how easy it is to create your own expressions.

type Select struct {
	Distinct bool
	Columns  []Sqlizer
	From     []Sqlizer
	Joins    []Sqlizer
	Where    Sqlizer
	GroupBy  []Sqlizer
	Having   Sqlizer
	OrderBy  []Sqlizer
	Limit    uint64
	Offset   uint64
}

func (s Select) ToSQL() (string, []any, error) {
	return Append(
		SQL("SELECT "),
		If(s.Distinct, SQL("DISTINCT ")),
		IfElse(len(s.Columns) > 0, s.Columns, SQL("*")),
		If(len(s.From) > 0, SQL(" FROM ?", s.From)),
		If(len(s.Joins) > 0, SQL(" ?", s.Joins)),
		If(s.Where != nil, SQL(" WHERE ?", s.Where)),
		If(len(s.GroupBy) > 0, SQL(" GROUP BY ?", s.GroupBy)),
		If(s.Having != nil, SQL(" HAVING ?", s.Having)),
		If(len(s.OrderBy) > 0, SQL(" ORDER BY ?", s.OrderBy)),
		If(s.Limit > 0, SQL(fmt.Sprintf(" LIMIT %d", s.Limit))),
		If(s.Offset > 0, SQL(fmt.Sprintf(" OFFSET %d", s.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.Column{
			Name: "first",
			Type: "TEXT",
			Constraints: []superbasic.Sqlizer{
				superbasic.SQL("NOT NULL"),
			},
		},
		superbasic.Column{
			Name: "last",
			Type: "TEXT",
			Constraints: []superbasic.Sqlizer{
				superbasic.SQL("NOT NULL"),
			},
		},
	},
	Constraints: []superbasic.Sqlizer{
		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)
• func ToSQL(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 Append(expr ...any) Expression
  • func Error(err error) 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 SQL(sql string, args ...any) Expression
  • func ToExpression(expr any) (Expression, bool)
  • func Values(data ...[]any) Expression
  • func (e Expression) ToSQL() (string, []any, error)
• type Insert
  • func (i Insert) ToSQL() (string, []any, error)
• type Select
  • func (s Select) 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)

func ToSQL

func ToSQL(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 []Sqlizer
}

func (Column) ToSQL

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

type Delete

type Delete struct {
	From  string
	Where Sqlizer
}

func (Delete) ToSQL

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

type Expression

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

func Append

func Append(expr ...any) Expression

func Error

func Error(err error) 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 SQL

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

func ToExpression

func ToExpression(expr any) (Expression, bool)

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 Select

type Select struct {
	Distinct bool
	Columns  []Sqlizer
	From     []Sqlizer
	Joins    []Sqlizer
	Where    Sqlizer
	GroupBy  []Sqlizer
	Having   Sqlizer
	OrderBy  []Sqlizer
	Limit    uint64
	Offset   uint64
}

func (Select) ToSQL

func (s Select) ToSQL() (string, []any, error)

type Sqlizer

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

type Table

type Table struct {
	IfNotExists bool
	Name        string
	Columns     []Sqlizer
	Constraints []Sqlizer
}

func (Table) ToSQL

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

type Update

type Update struct {
	Table string
	Set   []Sqlizer
	Where Sqlizer
}

func (Update) ToSQL

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