qrb

README

QRB

A PostgreSQL query builder in Go.

Why?

• Pure focus on PostgreSQL dialect
• Include support for selecting JSON from queries (e.g. json_build_object) to fetch nested data
• With other query builders, there is often a need to write raw SQL to achieve the desired result
• Supports database/sql and pgx drivers via qrbsqland qrbpgx packages

Design goals

• All builder data structures are immutable by design
• Implement the full PostgreSQL feature set, including lesser used features
• Use explicit types instead of any where possible and do not use reflection
• First-hand support for JSON selection (i.e. use json_build_object and json_agg to select hierarchical data via JSON)
• Write SQL as Go code following the natural order of the query parts
• Guide the developer by providing builder types with methods appropriate for the current context

Install

go get github.com/networkteam/qrb

Examples

Select JSON with common table expression

myCategory := "SQL Hacks"

q := qrb.With("author_json").As(
        qrb.Select(
                qrb.N("authors.author_id"),
        ).
        Select(
            fn.JsonBuildObject().
                Prop("id", qrb.N("authors.author_id")).
                Prop("name", qrb.N("authors.name")),
        ).As("json").
        From(qrb.N("authors")),
    ).
    Select(
        qrb.N("posts.post_id"),
        fn.JsonBuildObject().
            Prop("title", qrb.N("posts.title")).
            Prop("author", qrb.N("author_json.json")),
    ).
    From(qrb.N("posts")).
    LeftJoin(qrb.N("author_json")).On(qrb.N("posts.author_id").Eq(qrb.N("author_json.author_id"))).
    Where(qrb.N("posts.category").Eq(qrb.Arg(myCategory))).
    OrderBy(qrb.N("posts.created_at")).Desc().NullsLast()
}

Generated SQL

WITH author_json AS (
    SELECT authors.author_id, json_build_object('id', authors.author_id, 'name', authors.name) AS json
    FROM authors
)
SELECT posts.post_id, json_build_object('title', posts.title, 'author', author_json.json)
FROM posts
    LEFT JOIN author_json ON posts.author_id = author_json.author_id
WHERE posts.category = $1
ORDER BY posts.created_at DESC NULLS LAST

Placeholders

qrb.Bind supports named arguments that can be supplied after the query value has been built.

qrb.Arg will generate positional arguments with the specified value.

Both can be combined.

q := qrb.
    Select(qrb.N("*")).
    From(qrb.N("employees")).
    Where(qrb.And(
        qrb.Or(
            qrb.N("firstname").ILike(qrb.Bind("search")),
            qrb.N("lastname").ILike(qrb.Bind("search")),
        ),
        qrb.N("active").Eq(qrb.Arg(true)),
    ))

sql, args, err := qrb.
    Build(q).
    WithNamedArgs(map[string]any{"search": "Jo%"}).
    ToSQL()

// args: []any{"Jo%", true}

Generated SQL

SELECT *
FROM employees
WHERE ((firstname ILIKE $1) OR (lastname ILIKE $1))
  AND (active = $2)

Select WITH RECURSIVE

q := qrb.WithRecursive("employee_recursive").ColumnNames("distance", "employee_name", "manager_name").As(
        qrb.Select(qrb.Int(1), qrb.N("employee_name"), qrb.N("manager_name")).
            From(qrb.N("employee")).
            Where(qrb.N("manager_name").Eq(qrb.String("Mary"))).
            Union().All().
            Select(qrb.N("er.distance").Op("+", qrb.Int(1)), qrb.N("e.employee_name"), qrb.N("e.manager_name")).
            From(qrb.N("employee_recursive")).As("er").
            From(qrb.N("employee")).As("e").
            Where(qrb.N("er.employee_name").Eq(qrb.N("e.manager_name"))),
    ).
    Select(qrb.N("distance"), qrb.N("employee_name")).From(qrb.N("employee_recursive"))

Generated SQL

WITH RECURSIVE employee_recursive (distance, employee_name, manager_name) AS (
    SELECT 1, employee_name, manager_name
    FROM employee
    WHERE manager_name = 'Mary'
    UNION ALL
    SELECT er.distance + 1, e.employee_name, e.manager_name
    FROM employee_recursive AS er, employee AS e
    WHERE er.employee_name = e.manager_name
)
SELECT distance, employee_name
FROM employee_recursive

Functions including ROWS FROM

q := qrb.Select(qrb.N("*")).
    From(qrb.RowsFrom(
        qrb.Func("json_to_recordset", qrb.String(`[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]`)).ColumnDefinition("a", "INTEGER").ColumnDefinition("b", "TEXT"),
        qrb.Func("generate_series", qrb.Int(1), qrb.Int(3)),
    ).WithOrdinality()).As("x").ColumnAliases("p", "q", "s").
    OrderBy(qrb.N("p"))

Generated SQL

SELECT *
FROM ROWS FROM (
         json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]') AS (a INTEGER, b TEXT),
         generate_series(1, 3)
         ) WITH ORDINALITY AS x (p, q, s)
ORDER BY
    p

Group by with grouping sets, rollup and cube

q := qrb.
    Select(qrb.N("a"), qrb.N("b"), qrb.N("c")).
    From(qrb.N("test1")).
    GroupBy().Distinct().
    Rollup(
        qrb.Exps(qrb.N("a"), qrb.N("b")),
    ).
    Rollup(
        qrb.Exps(qrb.N("a"), qrb.N("c")),
    )

Generated SQL

SELECT a, b, c
FROM test1
GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)

Executing queries with pgx

conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatalf("Unable to connect to database: %v", err)
}

q := qrb.
	With("longest_track").As(
        qrb.Select(qrb.N(`"AlbumId"`), qrb.N(`"Milliseconds"`)).
            From(qrb.N(`"Track"`)).
            OrderBy(qrb.N(`"Milliseconds"`)).Desc().
            Limit(qrb.Int(1)),
    ).
    Select(qrb.N(`"Title"`)).As(`"AlbumTitle"`).
    Select(qrb.N(`"Name"`)).As(`"ArtistName"`).
    Select(qrb.N(`"Milliseconds"`)).As(`"Length"`).
    From(qrb.N(`"Album"`)).
    Join(qrb.N(`"longest_track"`)).Using(`"AlbumId"`).
    Join(qrb.N(`"Artist"`)).Using(`"ArtistId"`)

row, err := qrbpgx.
    Build(q).
    WithExecutor(conn).
    QueryRow(ctx)

The executor can either be a *pgx.Conn, *pgxpool.Pool or pgx.Tx (or any other type implementing qrbpgx.Executor). It can be specified after building the query with WithExecutor or in advance via qrbpgx.NewExecutorBuilder.

Executing queries with database/sql

(e.g. github.com/lib/pq or pgx with adapter)

db, err := sql.Open("postgres", os.Getenv("DATABASE_URL"))
if err != nil {
    panic(err)
}

q := qrb.
	With("longest_track").As(
        qrb.Select(qrb.N(`"AlbumId"`), qrb.N(`"Milliseconds"`)).
            From(qrb.N(`"Track"`)).
            OrderBy(qrb.N(`"Milliseconds"`)).Desc().
            Limit(qrb.Int(1)),
    ).
    Select(qrb.N(`"Title"`)).As(`"AlbumTitle"`).
    Select(qrb.N(`"Name"`)).As(`"ArtistName"`).
    Select(qrb.N(`"Milliseconds"`)).As(`"Length"`).
    From(qrb.N(`"Album"`)).
    Join(qrb.N(`"longest_track"`)).Using(`"AlbumId"`).
    Join(qrb.N(`"Artist"`)).Using(`"ArtistId"`)

row, err := qrbsql.
    Build(q).
    WithExecutor(db).
    QueryRow(ctx)

The executor can either be a *sql.DB or *sql.Tx (or any other type implementing qrbsql.Executor). It can be specified after building the query with WithExecutor or in advance via qrbpgx.NewExecutorBuilder.

License

MIT

Documentation

Index

• func Agg(name string, exps []builder.Exp) builder.AggExpBuilder
• func And(exps ...builder.Exp) builder.Exp
• func Arg(argument any) builder.Exp
• func Args(argument any, rest ...any) []builder.Exp
• func Array(exps ...builder.Exp) builder.Exp
• func Bind(argName string) builder.Exp
• func Bool(b bool) builder.Exp
• func Build(w builder.SQLWriter) *builder.QueryBuilder
• func Case(exp ...builder.Exp) builder.CaseBuilder
• func Coalesce(exp builder.Exp, rest ...builder.Exp) builder.FuncExp
• func Default() builder.Exp
• func DeleteFrom(tableName string) builder.DeleteBuilder
• func Exists(subquery builder.SelectExp) builder.Exp
• func Exps(exps ...builder.Exp) []builder.Exp
• func Float(f float64) builder.Exp
• func Func(name string, args ...builder.Exp) builder.FuncBuilder
• func Greatest(exp builder.Exp, rest ...builder.Exp) builder.FuncExp
• func InsertInto(tableName string) builder.InsertBuilder
• func Int(s int) builder.Exp
• func Interval(s string) builder.Exp
• func Least(exp builder.Exp, rest ...builder.Exp) builder.FuncExp
• func N(s string) builder.IdentExp
• func Null() builder.Exp
• func NullIf(value1, value2 builder.Exp) builder.FuncExp
• func Or(exps ...builder.Exp) builder.Exp
• func RowsFrom(fn builder.FuncBuilder, fns ...builder.FuncBuilder) builder.RowsFromBuilder
• func Select(exps ...builder.Exp) builder.SelectSelectBuilder
• func SelectJson(obj builder.JsonBuildObjectBuilder) builder.SelectJsonSelectBuilder
• func String(s string) builder.Exp
• func Update(tableName string) builder.UpdateBuilder
• func With(queryName string) builder.WithWithBuilder
• func WithRecursive(queryName string) builder.WithWithBuilder

Functions

func Agg

func Agg(name string, exps []builder.Exp) builder.AggExpBuilder

Agg builds an aggregate function expression.

func And

func And(exps ...builder.Exp) builder.Exp

func Arg

func Arg(argument any) builder.Exp

Arg creates an expression that represents an argument that will be bound to a placeholder with the given value. Each call to Arg will create a new placeholder and emit the argument when writing the query.

func Args

func Args(argument any, rest ...any) []builder.Exp

Args creates argument expressions for the given arguments.

func Array

func Array(exps ...builder.Exp) builder.Exp

func Bind

func Bind(argName string) builder.Exp

Bind creates an expression that represents an argument that will be bound to a placeholder with the given value. If Bind is called again with the same name, the same placeholder will be used.

func Bool

func Bool(b bool) builder.Exp

func Build

func Build(w builder.SQLWriter) *builder.QueryBuilder

Build starts a new query builder based on the given SQLWriter. For executing the query, use qrbpgx.Build or qrbsql.Build which can set an executor specific to a driver.

func Case

func Case(exp ...builder.Exp) builder.CaseBuilder

func Coalesce

func Coalesce(exp builder.Exp, rest ...builder.Exp) builder.FuncExp

func Default

func Default() builder.Exp

func DeleteFrom

func DeleteFrom(tableName string) builder.DeleteBuilder

func Exists

func Exists(subquery builder.SelectExp) builder.Exp

func Exps

func Exps(exps ...builder.Exp) []builder.Exp

Exps returns a slice of expressions, just for syntactic sugar. TODO We could use this as a way to express a scalar list of expressions e.g. for IN by using a custom slice type

func Float

func Float(f float64) builder.Exp

func Func

func Func(name string, args ...builder.Exp) builder.FuncBuilder

Func is a function call expression.

func Greatest

func Greatest(exp builder.Exp, rest ...builder.Exp) builder.FuncExp

func InsertInto

func InsertInto(tableName string) builder.InsertBuilder

func Int

func Int(s int) builder.Exp

func Interval

func Interval(s string) builder.Exp

func Least

func Least(exp builder.Exp, rest ...builder.Exp) builder.FuncExp

func N

func N(s string) builder.IdentExp

N writes the given name / identifier.

It will validate the identifier when writing the query, but it will not detect all invalid identifiers that are invalid in PostgreSQL (especially considering reserved keywords).

func Null

func Null() builder.Exp

func NullIf

func NullIf(value1, value2 builder.Exp) builder.FuncExp

func Or

func Or(exps ...builder.Exp) builder.Exp

func RowsFrom

func RowsFrom(fn builder.FuncBuilder, fns ...builder.FuncBuilder) builder.RowsFromBuilder

func Select

func Select(exps ...builder.Exp) builder.SelectSelectBuilder

Select the given output expressions for the select list and start a new SelectBuilder.

func SelectJson

func SelectJson(obj builder.JsonBuildObjectBuilder) builder.SelectJsonSelectBuilder

SelectJson sets the JSON selection for this select builder.

It will always be the first element in the select list. It can be modified later by SelectBuilder.ApplySelectJson.

func String

func String(s string) builder.Exp

func Update

func Update(tableName string) builder.UpdateBuilder

func With

func With(queryName string) builder.WithWithBuilder

With starts a new builder with the given WITH query. Call WithBuilder.As to define the query.

func WithRecursive

func WithRecursive(queryName string) builder.WithWithBuilder

WithRecursive starts a new builder with the given WITH RECURSIVE query.