quill

README

A fluent SQL query builder for Go, with an API inspired by Laravel's query builder. It builds SQL with bind arguments and can optionally run it through database/sql. It is not an ORM: there are no models, relations, model lifecycle hooks or migrations.

• Dialects: PostgreSQL, MySQL, MariaDB, SQLite
• Standard library only at runtime (testify and go-sqlmock are used by tests only)

Features

Building queries

• One builder per statement (Select, Insert, Update, Delete), each exposing only the methods valid for it
• SELECT with aliases, DISTINCT, aggregates, GROUP BY / HAVING, ordering, limit and offset
• Rich WHERE: comparisons, IN, BETWEEN, LIKE, NULL, column and date-part comparisons, nested groups, EXISTS, row values, and WhereAll / WhereAny / WhereNone
• Joins (including subquery and lateral joins), unions, subqueries and common table expressions (WITH, WITH RECURSIVE)
• JSON predicates and JSON path updates, locks and index hints, raw SQL escape hatches
• Conditional building with When and Tap; Clone and snapshotting keep reused builders safe
• Inspect SQL with ToSQL, or with arguments interpolated through ToRawSQL (debugging only)

Writing data

• Insert (with Ignore, Using for insert-from-select and Upsert), Update (with Set, Increment / Decrement and FromJoins), Delete, truncate and RETURNING
• Struct writes with Rows / SetRow and db:"name,omitempty" tags

Running queries (optional: build-only use needs no connection)

• Scan into structs, maps and scalars; First, Find, Sole, Value, Pluck, Exists
• Offset, simple and cursor (keyset) pagination; Chunk and Each for large result sets
• Transactions, a query hook for logging, metrics and tracing, and a table prefix option
• Typed, select-only API with From[T], so you get []T back without a destination variable

Why quill

• You keep control of the SQL. No models or relations to learn; every query reads like the SQL it produces.
• One API, four dialects. Quoting, placeholders and syntax differences are handled for you, and a feature a dialect cannot express returns ErrUnsupported instead of emitting SQL that behaves differently.
• Safe by default. Values are always bound, identifiers are always quoted, and an UPDATE or DELETE without a WHERE is refused unless you call AllRows().
• Errors, not panics. A bad argument is recorded and returned from the terminal call. Only the Must* variants panic.
• Fast to build. Query construction is about 2 to 7 times faster than squirrel and goqu in the benchmark scenarios, with far fewer allocations; see the benchmarks.

Installation

go get github.com/akfaiz/quill

import "github.com/akfaiz/quill/db"

Quick start

Build SQL without a database (set the dialect first, then start a statement with Select, Insert, Update or Delete):

sql, args, err := db.Use(db.Postgres).
    Select("id", "name").
    From("users").
    Where("votes", ">", 100).
    OrderByDesc("created_at").
    Limit(10).
    ToSQL()
// SELECT "id", "name" FROM "users" WHERE "votes" > $1 ORDER BY "created_at" DESC LIMIT 10
// args: [100]

sql, args, err = db.Use(db.Postgres).Update("users").Set("name", "Anna").Where("id", 7).ToSQL()
// UPDATE "users" SET "name" = $1 WHERE "id" = $2
// args: [Anna 7]

Run queries (wrap any *sql.DB, *sql.Tx or *sql.Conn). db.Use only builds SQL: running a query on it returns ErrNotBound. db.New also executes:

conn := db.New(sqlDB, db.Postgres)

var users []User
err := conn.Select().From("users").Where("active", true).OrderBy("id").Get(ctx, &users)

id, err := conn.Insert("users").Values(map[string]any{"name": "Ann"}).ExecGetID(ctx)
n, err  := conn.Update("users").Set("name", "Anna").Where("id", id).Exec(ctx)

Name the variable conn (or anything but db): a local variable called db hides the package inside its scope.

Let a struct name its own table, and skip the destination variable (db.From[T] is select-only; write structs with Rows and SetRow):

type User struct {
    ID    int64   `db:"id,omitempty"`
    Name  string  `db:"name"`
    Email *string `db:"email"`
}

func (User) TableName() string { return "users" }

users, err := db.From[User](conn).Where("active", true).OrderBy("id").Get(ctx) // []User
id, err    := conn.Insert("users").Rows(User{Name: "Ann"}).ExecGetID(ctx)
_, err      = conn.Update("users").SetRow(User{Name: "Anna"}).Where("id", id).Exec(ctx)

Run several statements atomically:

err := conn.Transaction(ctx, func(tx *db.DB) error {
    if _, err := tx.Update("accounts").Decrement("balance", 50).Where("id", 1).Exec(ctx); err != nil {
        return err
    }
    _, err := tx.Update("accounts").Increment("balance", 50).Where("id", 2).Exec(ctx)
    return err
})

Documentation

Guide	What it covers
Getting started	Install, connecting, options, building versus running, errors
Building queries	Select, where, joins, grouping, ordering, unions, subqueries, JSON, raw SQL
Writing data	Insert, upsert, update, increment, delete, truncate, RETURNING, the safety guard
Running queries	Reading and scanning, aggregates, pagination, chunking, streaming, transactions, hooks
Typed API	TableName(), From[T], As[T], struct writes with Rows / SetRow, scalar helpers
Dialects	What differs between PostgreSQL, MySQL, MariaDB and SQLite, with versions
Security	Injection, raw SQL, logging and personal data, untrusted cursors
Reference	Errors, types, options and an index of every method
Benchmarks	Build-time comparison with go-sqlbuilder, goqu and squirrel
Contributing	Layout, tests, fuzzing, integration suites, adding a dialect

API documentation is also available through go doc github.com/akfaiz/quill/db.

License

MIT License. See LICENSE for details.