Core Concepts

Read this once. It explains the rules every builder follows, so the rest of the wiki makes sense.

On this page: The big picture · One builder per statement · Builders are mutable · Errors are collected · Must variants · Subqueries are snapshots · Concurrency · Safe by default · Types at a glance

The big picture

 db.Use(dialect)  ──┐                         ┌── ToSQL()  → (sql, args, err)
                    ├─► *db.DB ─► Builder ─►  │
 db.New(conn, d)  ──┘   Select / Insert /     └── Get / First / Exec / ...  (needs db.New)
                        Update / Delete

1. Create a *db.DB with a dialect (and optionally a connection).
2. Start a statement. You get a builder for that statement.
3. Chain methods to describe the query.
4. End with a terminal call: either build the SQL (ToSQL) or run it (Get, Exec, ...).

One builder per statement

Method	Builder	Notable methods
Select(cols...)	*db.SelectBuilder	From, Where*, Join*, GroupBy, Having*, Union, With*, locks, readers
Insert(table)	*db.InsertBuilder	Values, Rows, Ignore, Using, Upsert, Returning
Update(table)	*db.UpdateBuilder	Set, SetMap, SetRow, Increment, Where*, Join*, Limit, AllRows
Delete(table)	*db.DeleteBuilder	Where*, Join*, Limit, AllRows

The compiler enforces the split. You cannot call GroupBy on an update, or Where on an insert. The code will not compile.

Builders are mutable

Each call changes the builder and returns the same pointer. To branch a query, call Clone():

base := conn.Select().From("users").Where("active", true)

admins := base.Clone().Where("role", "admin")
guests := base.Clone().Where("role", "guest")

Without Clone, admins and guests would be the same builder with both conditions.

Errors are collected

quill does not panic on bad input. A bad operator, direction or argument is recorded on the builder. The chain keeps working, and the first error comes back from the terminal call.

q := conn.Select().From("users").
    Where("age", "banana", 5). // invalid operator, recorded
    OrderBy("name")            // chain continues

_, _, err := q.ToSQL()
errors.Is(err, db.ErrInvalidOperator) // true

q.Err() // look at the first error early, without a terminal call

All errors are sentinel values you test with errors.Is. See the Error Reference.

Must variants

For fixed queries (tests, package-level variables) where a failure is a programming mistake:

sql, args := db.Use(db.Postgres).Select().From("users").Where("id", 7).MustSQL()

MustSQL and MustRawSQL panic with the original error, wrapped, so errors.Is still works on the recovered value. Do not use them where the error can come from input, such as a user-chosen operator or column.

Subqueries are snapshots

A builder you pass as a subquery is copied when it is added. Changing the inner builder afterwards does not change the outer query.

inner := conn.Select("user_id").From("bans")
outer := conn.Select().From("users").WhereIn("id", inner)

inner.Where("reason", "spam") // does NOT affect outer

Bind arguments are numbered across the whole statement, so $1, $2, ... stay correct however deeply queries nest.

Concurrency

Builders are not safe for concurrent use. Build one per request. To share a base query across goroutines, share it and Clone() before each use. The *db.DB itself is a thin wrapper over your database/sql handle.

Safe by default

Risk	Default behaviour
Value injection	Values are always bound as arguments
Identifier injection	Identifiers are always quoted, embedded quotes are doubled
Unknown operators	Rejected with ErrInvalidOperator
Mass UPDATE/DELETE	Refused with ErrNoWhere unless WHERE, LIMIT or AllRows()
Dialect gaps	ErrUnsupported instead of silently different SQL
Logging personal data	Bind values are left out of hook events unless you opt in

Details in Security.

Types at a glance

Type	Purpose
db.Dialect	Postgres, MySQL, MariaDB, SQLite
db.DB	A dialect plus an optional connection. Starts statements
db.Conn	QueryContext + ExecContext. Satisfied by *sql.DB, *sql.Tx, *sql.Conn
db.SelectBuilder, InsertBuilder, UpdateBuilder, DeleteBuilder	One per statement
db.WhereClause, HavingClause, JoinClause	Passed to group and join callbacks
db.Expression	Raw SQL plus arguments, from db.Raw
db.Query[T]	Typed, select-only query

The full list is in the API Overview.