recipes

A grouped query no longer returns rows of the model, so Collect scans it into the type that does match. This is the whole of "a dashboard query": one statement, one destination struct, no per-row loop.

type statusTotal struct {
	Status string `db:"status"`
	Posts  int64  `db:"posts"`
	Views  int64  `db:"views"`
}

q := sqlb.Query[recipes.Post]().
	Select(sqlb.F("status"), sqlb.Count().As("posts"), sqlb.Sum(sqlb.F("view_count")).As("views")).
	GroupBy(sqlb.F("status"))

db := recordingDBWith(
	[]string{"status", "posts", "views"},
	[]any{"published", int64(2), int64(31)},
	[]any{"draft", int64(1), int64(0)},
)

totals, err := sqlb.Collect[statusTotal](context.Background(), db, q)
if err != nil {
	panic(err)
}
for _, t := range totals {
	fmt.Printf("%-9s %d posts, %d views\n", t.Status, t.Posts, t.Views)
}

Output:
published 2 posts, 31 views
draft     1 posts, 0 views

The rest of the aggregate vocabulary. CountOf counts non-null values of one column, which is a different question from Count's "how many rows"; Coalesce is how a sum over no rows becomes 0 instead of NULL.

show(sqlb.Query[recipes.Post]().
	Select(
		sqlb.CountOf(sqlb.F("published_at")).As("published"),
		sqlb.CountDistinct(sqlb.F("author_id")).As("authors"),
		sqlb.Min(sqlb.F("view_count")).As("least"),
		sqlb.Max(sqlb.F("view_count")).As("most"),
		sqlb.Avg(sqlb.F("view_count")).As("mean"),
	))

Output:
SELECT count("published_at") AS "published", count(DISTINCT "author_id") AS "authors", min("view_count") AS "least", max("view_count") AS "most", avg("view_count") AS "mean" FROM "posts"

Select replaces the default projection, and an aggregate carries an alias. The alias is not decoration: it is what a destination struct's `db` tag matches, so the name here and the name there have to agree.

show(sqlb.Query[recipes.Post]().
	Select(
		sqlb.F("status"),
		sqlb.Count().As("posts"),
		sqlb.Sum(sqlb.F("view_count")).As("views"),
	).
	Where(sqlb.F("org_id").Eq("acme")).
	GroupBy(sqlb.F("status")).
	OrderBy(sqlb.F("status").Asc()))

Output:
SELECT "status", count(*) AS "posts", sum("view_count") AS "views" FROM "posts" WHERE "org_id" = $1 GROUP BY "status" ORDER BY "status" ASC
args: [acme]

Having filters the groups, where Where filters the rows. Both take the same predicates, which is the payoff of the predicate being a value rather than a clause: nothing has to be written twice to be usable in a second position.

show(sqlb.Query[recipes.Post]().
	Select(sqlb.F("author_id"), sqlb.Count().As("posts")).
	Where(sqlb.F("status").Eq("published")).
	GroupBy(sqlb.F("author_id")).
	Having(sqlb.F("count(*)").Gt(5)))

Output:
SELECT "author_id", count(*) AS "posts" FROM "posts" WHERE "status" = $1 GROUP BY "author_id" HAVING "count(*)" > $2
args: [published 5]

Array is the variadic spelling of that slice, for a comparand assembled from loose values rather than held in one. It is nothing more than that: passing a []string directly binds the same way.

showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("tags").Eq(sqlb.Array("go", "sql"))))

Output:
WHERE "tags" = $1
args: [[go sql]]

The three array predicates, and the difference between them is the whole point:

Has     the array contains this one element
HasAny  the array overlaps these — at least one in common
HasAll  the array contains every one of these

Has takes a single value rather than a list, because `$1 = ANY(tags)` is the form an index over the column serves.

showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("tags").Has("go")))
showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("tags").HasAny("go", "rust")))
showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("tags").HasAll("go", "postgres")))

Output:
WHERE $1 = ANY("tags")
args: [go]
WHERE "tags" && $1
args: [[go rust]]
WHERE "tags" @> $1
args: [[go postgres]]

The empty cases follow from what the operators mean rather than from a convention, and they differ: an overlap with nothing is nothing, and every array contains the empty array. Knowing which is which is the reason to think about the empty case at all.

var none []any

showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("tags").HasAny(none...)))
showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("tags").HasAll(none...)))

Output:
WHERE false
WHERE true

An array column is a plain Go slice. `Tags []string` maps to `text[]`, and pgx decodes it into the slice — there is no wrapper type to adopt and no `pq.StringArray` in the model, which matters because the model is also what a generated client is shaped from.

post, err := sqlb.Query[recipes.Post]().First(context.Background(), recordingDB())
if err != nil {
	panic(err)
}
fmt.Printf("%q\n", post.Tags)

Output:
["go" "sql"]

Writing an array is the same slice going the other way, and it reaches the database as a slice: pgx encodes the array, so nothing here builds a `{a,b}` literal and nothing has to escape a value containing a comma or a quote.

That is one of the things taking pgx bought (ADR-0040). sqlb used to carry its own array codec because the standard library has none.

show(sqlb.UpdateRows[recipes.Post]().
	Set("tags", []string{"go", "a,b", `quote"d`}).
	Where(sqlb.F("id").Eq("p1")))

Output:
UPDATE "posts" SET "tags" = $1 WHERE "id" = $2 RETURNING "id", "org_id", "author_id", "title", "body", "status", "view_count", "tags", "metadata", "published_at", "deleted_at", "created_at"
args: [[go a,b quote"d] p1]

Delete returns how many rows went, rather than the rows themselves.

n, err := sqlb.DeleteRows[recipes.Post]().
	Where(sqlb.F("status").Eq("draft"), sqlb.F("org_id").Eq("acme")).
	Exec(context.Background(), recordingDB())
if err != nil {
	panic(err)
}
fmt.Println("deleted:", n)
fmt.Println(statements()[0])

Output:
deleted: 1
DELETE FROM "posts" WHERE ("status" = $1) AND ("org_id" = $2)

Describe attaches the same metadata at runtime that the tags would carry. It is the answer to "can I use this without code generation", and the more common case: layering sqlb over structs that already exist.

Call it during initialisation, before any query runs — from init, as here in spirit. It mutates the cached model in place and does not lock, because a mutex there would put a cost on the read path of every query to pay for something that happens once at startup. Calling it after the first statement panics rather than racing, and naming a column that does not exist panics too, listing the ones that do.

sqlb.Describe[Invoice]().
	Table("invoices").
	PrimaryKey("id").
	Column("InternalMemo", "internal_memo").
	Defaulted("id", "created_at").
	Filterable("customer_id", "paid", "amount_due").
	Sortable("created_at", "amount_due").
	Hidden("internal_memo")

show(sqlb.Query[Invoice]().
	Where(sqlb.F("paid").Eq(false)).
	OrderBy(sqlb.F("amount_due").Desc()).
	Limit(5))

Output:
SELECT "invoices"."id", "invoices"."customer_id", "invoices"."amount_due", "invoices"."paid", "invoices"."internal_memo", "invoices"."created_at" FROM "invoices" WHERE "paid" = $1 ORDER BY "amount_due" DESC LIMIT 5
args: [false]

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
)

// Without tags or a description the builder still works — column names are
// derived from field names — but no column is filterable, sortable or
// searchable, so the REST layer rejects every request against it.
//
// That is the intended default rather than an oversight: capabilities are
// opt-in, and an undescribed model exposes nothing. The alternative default
// exposes a table the moment someone writes a struct.
type Ledger struct {
	ID     string
	Secret string
}

func main() {
	model := sqlb.ModelOf[Ledger]()

	fmt.Println("table:", model.Table)
	for _, c := range model.Columns {
		fmt.Printf("  %-7s filterable=%v sortable=%v\n", c.Name, c.Filterable, c.Sortable)
	}
}

Output:
table: ledgers
  id      filterable=false sortable=false
  secret  filterable=false sortable=false

A description merges onto whatever the tags already said, so a partly tagged model is completed rather than restated. ModelOf reads back what the two together produced — which is also the value filter.Options is handed, so this is how to check what a REST resource will actually accept.

package main

import (
	"fmt"
	"time"

	"github.com/jryannel/sqlb"
)

// Receipt is partly tagged, which is the usual state of a model being adopted.
type Receipt struct {
	ID        string    `db:"id" sqlb:"pk,default"`
	OrderID   string    `db:"order_id"`
	Total     int64     `db:"total"`
	CreatedAt time.Time `db:"created_at" sqlb:"default"`
}

func main() {
	sqlb.Describe[Receipt]().
		Table("receipts").
		Filterable("order_id").
		Sortable("created_at", "total")

	model := sqlb.ModelOf[Receipt]()
	fmt.Println("table:", model.Table, "pk:", model.PK.Name)
	for _, c := range model.Columns {
		fmt.Printf("  %-10s filterable=%-5v sortable=%-5v defaulted=%v\n",
			c.Name, c.Filterable, c.Sortable, c.HasDefault)
	}
}

Output:
table: receipts pk: id
  id         filterable=true  sortable=false defaulted=true
  order_id   filterable=true  sortable=false defaulted=false
  total      filterable=false sortable=true  defaulted=false
  created_at filterable=false sortable=true  defaulted=true

After-commit callbacks run once the transaction has committed, so a failure in one cannot roll anything back. A failing callback does not stop the others either — these are independent side effects, and abandoning the rest leaves more inconsistency rather than less. The failures come back joined under ErrAfterCommit.

db := recordingDB()

err := db.WithTx(context.Background(), func(ctx context.Context, _ *sqlb.DB) error {
	if err := sqlb.AfterCommit(ctx, func(context.Context) error {
		return errors.New("the event bus was down")
	}); err != nil {
		return err
	}
	return sqlb.AfterCommit(ctx, func(context.Context) error {
		fmt.Println("the second callback still ran")
		return nil
	})
})

fmt.Println("committed:", count(statements(), "COMMIT") == 1)
fmt.Println("is ErrAfterCommit:", errors.Is(err, sqlb.ErrAfterCommit))

Output:
the second callback still ran
committed: true
is ErrAfterCommit: true

The five kinds are SQLSTATE class 23, named as a schema names them rather than as Postgres numbers them, so a switch reads the way the declaration does.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
)

func main() {
	for _, code := range []string{"23505", "23503", "23514", "23502", "23P01", "42P01"} {
		kind, ok := sqlb.ConstraintKindOf(code)
		fmt.Printf("%-6s %-12s %v\n", code, kind, ok)
	}
}

Output:
23505  unique       true
23503  foreign_key  true
23514  check        true
23502  not_null     true
23P01  exclusion    true
42P01               false

A constraint violation is the caller's mistake far more often than it is an outage: a second signup on a taken email, an order naming a product that was deleted, a balance a CHECK will not let go negative. Without this they arrive as an opaque driver error, and the only way to tell them apart is to match on the text of a message — which no rename survives.

errors.Is is the cheap test for the class; errors.As gets the detail. The constraint *name* is the field that carries the value, because it is what lets a handler say "that email is taken" rather than "something was already there" — and it is filled in with no registration, because ADR-0040 settled which driver sqlb reads.

db := failingDB(&pgconn.PgError{
	Code:           "23505",
	ConstraintName: "authors_email_key",
	TableName:      "authors",
	Message:        `duplicate key value violates unique constraint "authors_email_key"`,
})

author := recipes.Author{Email: "ada@example.com"}
_, err := sqlb.InsertRows(&author).One(context.Background(), db)

fmt.Println("is ErrConstraint:", errors.Is(err, sqlb.ErrConstraint))

var ce *sqlb.ConstraintError
if errors.As(err, &ce) {
	fmt.Printf("%s on %s.%s\n", ce.Kind, ce.Table, ce.Constraint)

	// Which is what a handler branches on — the name the schema declares,
	// not prose from the database.
	if ce.Constraint == "authors_email_key" {
		fmt.Println("response: that email address is already registered")
	}
}

Output:
is ErrConstraint: true
unique on authors.authors_email_key
response: that email address is already registered

SetErrorClassifier is what remains for that case, and it is rarely needed now. It used to be the only way to reach the constraint name at all — sqlb depended on the standard library alone and would not name a driver — and anyone who registered one for that reason can delete it.

A registered classifier that declines is not a veto: it may know one driver and be handed an error from another, so the built-in check still runs after it. Call it once at startup, before serving.

sqlb.SetErrorClassifier(func(err error) (sqlb.ConstraintError, bool) {
	var opaque opaqueError
	if !errors.As(err, &opaque) {
		return sqlb.ConstraintError{}, false
	}
	_, rest, ok := strings.Cut(opaque.text, "SQLSTATE ")
	if !ok {
		return sqlb.ConstraintError{}, false
	}
	kind, ok := sqlb.ConstraintKindOf(strings.TrimRight(rest, ")"))
	if !ok {
		return sqlb.ConstraintError{}, false
	}
	return sqlb.ConstraintError{Kind: kind}, true
})
defer sqlb.SetErrorClassifier(nil) // an application never does this

db := failingDB(opaqueError{text: "ERROR: duplicate key (SQLSTATE 23505)"})

author := recipes.Author{Email: "ada@example.com"}
_, err := sqlb.InsertRows(&author).One(context.Background(), db)

var ce *sqlb.ConstraintError
fmt.Println("classified:", errors.As(err, &ce), ce.Kind)

Output:
classified: true unique

An error that is not a constraint violation stays what it was. A syntax error or a dead connection must never arrive dressed as the caller's fault, which is why the classification is a whitelist of class 23 rather than "a write failed, so blame the input".

db := failingDB(&pgconn.PgError{Code: "08006", Message: "connection failure"})

author := recipes.Author{Email: "ada@example.com"}
_, err := sqlb.InsertRows(&author).One(context.Background(), db)

fmt.Println("is ErrConstraint:", errors.Is(err, sqlb.ErrConstraint))

// The driver's own error is wrapped rather than replaced, so a caller that
// does depend on pgx loses nothing.
var pg *pgconn.PgError
fmt.Println("PgError reachable:", errors.As(err, &pg), pg.Code)

Output:
is ErrConstraint: false
PgError reachable: true 08006

The sentinel errors, and what each one means:

ErrNotFound     One or First matched nothing
ErrConstraint   the database refused the write
ErrUnscoped     an update or delete with no Where
ErrBadCursor    ?cursor= did not decode against this ordering
ErrAfterCommit  the transaction committed, but a callback failed

Every one is testable with errors.Is, so a handler branches on the class rather than on the text of a message.

_, err := sqlb.Query[recipes.Post]().
	Where(sqlb.F("id").Eq("nope")).
	One(context.Background(), recordingDBWith(postColumns))

fmt.Println("is ErrNotFound:", errors.Is(err, sqlb.ErrNotFound))
fmt.Println("message:       ", err)

Output:
is ErrNotFound: true
message:        sqlb: no rows matched

Every terminal method takes an Executor, so a pool, a connection, a transaction and a wrapper like the one above are all the same argument.

sqlb.New wraps one in a handle, which is what WithTx and the hook registry hang off. It is additive: passing a *sqlb.DB where a *pgxpool.Pool used to go changes nothing else, because the handle is itself an Executor.

var exec sqlb.Executor = recordingDB()
db := sqlb.New(exec)

fmt.Println("handle is an Executor:", func() bool { var _ sqlb.Executor = db; return true }())
fmt.Println("can begin a transaction:", db.CanBeginTx())
fmt.Println("inside one:", db.InTx())

Output:
handle is an Executor: true
can begin a transaction: false
inside one: false

CanBeginTx exists so a caller who *requires* transactions can say so at startup rather than on the first write. rest.Resource uses it for exactly that: a resource wrapping its generated writes refuses to mount over an executor that cannot begin one, because discovering it at request time means the first POST is the error report.

It asks whether the executor also satisfies Beginner. Keeping that a separate assertion rather than a third method on Executor is what lets a wrapper be written against two methods and still work.

pool := recordingDB() // a handle over an executor that begins
fmt.Println("pool:", pool.CanBeginTx())

err := pool.WithTx(context.Background(), func(_ context.Context, tx *sqlb.DB) error {
	// Inside a transaction it still reports true, where WithTx joins
	// rather than begins.
	fmt.Println("tx:  ", tx.CanBeginTx(), "in a transaction:", tx.InTx())
	return nil
})
if err != nil {
	panic(err)
}

Output:
pool: true
tx:   true in a transaction: true

Every statement passes through the wrapper, whoever built it — a hand-written query, a REST filter, or a generated handler.

traced := tracer{
	inner: recordingDB(),
	log: func(op, q string, args []any, _ time.Duration, err error) {
		fmt.Printf("%s args=%d err=%v %s\n", op, len(args), err, firstWords(q, 3))
	},
}

ctx := context.Background()
if _, err := sqlb.Query[recipes.Post]().Where(sqlb.F("org_id").Eq("acme")).All(ctx, traced); err != nil {
	panic(err)
}
if _, err := sqlb.DeleteRows[recipes.Post]().Where(sqlb.F("id").Eq("p1")).Exec(ctx, traced); err != nil {
	panic(err)
}

Output:
query args=1 err=<nil> SELECT "posts"."id", "posts"."org_id",
exec args=1 err=<nil> DELETE FROM "posts"

Expand resolves a declared relation inline, one LEFT JOIN each, and the joined row arrives as its own value on the model rather than as extra columns spliced into it. That is the difference from Join: a join changes which rows match, an expansion changes what each row carries.

Names are relation names, not column names — `Expand("author")`, not `Expand("author_id")`. An unknown name fails the query rather than being ignored, because a silently dropped expansion answers the request with a 200 and a missing field.

The joined row arrives as one JSON value in a column of its own, so the row stays exactly as wide as the model however many relations were asked for. That is what lets ?select and ?expand coexist: a projection names columns of Post, and an expansion is not one of them.

show(sqlb.Query[recipes.Post]().
	Expand("author").
	Where(sqlb.F("status").Eq("published")).
	Limit(2))

Output:
SELECT "posts"."id", "posts"."org_id", "posts"."author_id", "posts"."title", "posts"."body", "posts"."status", "posts"."view_count", "posts"."tags", "posts"."metadata", "posts"."published_at", "posts"."deleted_at", "posts"."created_at", CASE WHEN "__ex_author"."id" IS NULL THEN NULL ELSE json_build_object('id', "__ex_author"."id", 'org_id', "__ex_author"."org_id", 'name', "__ex_author"."name", 'email', "__ex_author"."email") END AS "__expand_author" FROM "posts" LEFT JOIN "authors" AS "__ex_author" ON "__ex_author"."id" = "posts"."author_id" WHERE "posts"."status" = $1 LIMIT 2
args: [published]

Expanding is additive and idempotent, so naming the same relation twice joins it once — which matters because the caller and a BeforeQuery hook can both ask for it without coordinating.

q := sqlb.Query[recipes.Post]().Expand("author").Expand("author")
showExpanded(q.Expanded())

Output:
expanded: [author]

A hidden column has no spelling anywhere, and that holds through an expansion too: Author.PasswordHash is absent from the object above. This is the property that makes expansion safe to expose over HTTP — the joined model's own capabilities travel with it rather than being re-decided at the join.

sql, _, err := sqlb.Query[recipes.Post]().Expand("author").SQL()
if err != nil {
	panic(err)
}
showContains(sql, "password_hash")
showContains(sql, "'email'")

Output:
mentions password_hash: false
mentions 'email': true

A relation the model never declared is an error on the builder, and it names what would have been accepted. Terminal methods return it, so it cannot be missed by a caller who did not check Err.

_, _, err := sqlb.Query[recipes.Post]().Expand("publisher").SQL()
showError(err)

Output:
cannot expand "publisher": Post has no such relation (expandable: author)

Explain plans a query against the live schema without running it, which answers two questions SQL() cannot.

First, whether the statement is valid against the database as it *is*: a column that a migration was written for and never applied fails here, which a compile-time column check cannot catch. Second, whether the plan is still the one you expected — an index scan that silently became a sequential scan is invisible in the SQL text and obvious in the plan.

It does not execute the statement, so it is safe on a mutation. ExplainAnalyze does execute; run that inside a transaction you roll back.

db := recordingDBWith([]string{"QUERY PLAN"}, []any{[]byte(seqScanPlan)})

plan, err := sqlb.Explain(context.Background(), db, sqlb.Query[recipes.Post]().
	Where(sqlb.F("status").Eq("published")).
	OrderBy(sqlb.F("view_count").Desc()))
if err != nil {
	panic(err)
}

fmt.Println("analyzed:", plan.Analyzed)
fmt.Println("estimated rows:", plan.PlanRows)
fmt.Println("scans posts sequentially:", plan.UsesSeqScan("posts"))
fmt.Println("uses posts_status_idx:", plan.UsesIndex("posts_status_idx"))

Output:
analyzed: false
estimated rows: 4200
scans posts sequentially: true
uses posts_status_idx: false

Diagnostics report plan shapes that usually mean a missing index or a query that will not scale. They are advisory — a sequential scan over a lookup table is correct, and so is a sort of twenty rows — which is why the threshold is a variable you can move rather than a constant.

This is what makes a plan usable as a test assertion: a query whose plan regresses fails a build instead of a pager.

db := recordingDBWith([]string{"QUERY PLAN"}, []any{[]byte(seqScanPlan)})

plan, err := sqlb.Explain(context.Background(), db, sqlb.Query[recipes.Post]().
	Where(sqlb.F("status").Eq("published")).
	OrderBy(sqlb.F("view_count").Desc()))
if err != nil {
	panic(err)
}

fmt.Print(sqlb.Diagnostics(plan.Diagnostics()))

Output:
[seq-scan] Seq Scan on posts: sequential scan over ~4200 rows filtering on (status = 'published'::text)
    fix: add an index covering the filtered columns on "posts"

The tree, in the shape a reader — or an agent comparing two runs — can scan quickly.

db := recordingDBWith([]string{"QUERY PLAN"}, []any{[]byte(seqScanPlan)})

plan, err := sqlb.Explain(context.Background(), db, sqlb.Query[recipes.Post]().
	Where(sqlb.F("status").Eq("published")))
if err != nil {
	panic(err)
}
fmt.Print(plan)

Output:
cost=1892.40 rows=4200
  -> Sort (cost=1892.40 rows=4200)
    -> Seq Scan on posts (cost=1421.00 rows=4200) filter=(status = 'published'::text)

The whole HTTP-to-SQL layer for a dynamic list endpoint, in one handler: parse, apply, run. Everything the request may ask for is decided by the column capabilities and the Options; everything the *caller* may see is decided by the hooks. Neither is written here.

This is the payoff the rest of the design is aimed at. It is also what rest.Resource generates, for callers who would rather not write even this.

opts := filter.Options{
	Model:           sqlb.ModelOf[recipes.Post](),
	DefaultPageSize: 20,
	MaxPageSize:     100,
}
db := recordingDB()

list := func(w http.ResponseWriter, r *http.Request) {
	q, err := filter.Parse(r.URL.Query(), opts)
	if err != nil {
		// WriteError renders every rejected parameter, with the allowed
		// alternatives where there are any.
		filter.WriteError(w, err)
		return
	}

	posts, err := filter.Apply(sqlb.Query[recipes.Post](), q).All(r.Context(), db)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	w.Header().Set("content-type", "application/json")
	_ = json.NewEncoder(w).Encode(map[string]any{"items": posts})
}

rec := httptest.NewRecorder()
list(rec, httptest.NewRequest(http.MethodGet, "/posts?status=eq.published&sort=-view_count", nil))
fmt.Println(rec.Code, firstWords(statements()[0], 4))

rec = httptest.NewRecorder()
list(rec, httptest.NewRequest(http.MethodGet, "/posts?sort=body", nil))
fmt.Println(rec.Code, rec.Body.String())

Output:
200 SELECT "id", "org_id", "author_id",
400 {"details":[{"param":"sort","value":"body","reason":"column is not sortable","allowed":["title","status","view_count","published_at","created_at"]}],"error":"invalid_query","message":"one or more query parameters were rejected"}

?expand is validated against the Expandable list at parse time and performed by Apply, so a parsed expansion is never silently dropped: a name that is not there is a 400 that lists the ones that are.

opts := filter.Options{
	Model:      sqlb.ModelOf[recipes.Post](),
	Expandable: []string{"author"},
}

values, err := url.ParseQuery("expand=author&select=id,title")
if err != nil {
	panic(err)
}
q, err := filter.Parse(values, opts)
if err != nil {
	panic(err)
}
fmt.Println("expand:", q.Expand)

values, err = url.ParseQuery("expand=publisher")
if err != nil {
	panic(err)
}
_, err = filter.Parse(values, opts)
showError(err)

Output:
expand: [author]
filter: expand=publisher: relation is not expandable (allowed: author)

The REST filter grammar compiles a query string into the same predicate AST that hand-written Go produces. One compiler, one bind-parameter discipline, one set of hooks — two producers.

?status=eq.published         operator form
?status=published            shorthand for eq
?view_count=gte.100          repeated params conjoin
?status=in.draft,review      value lists
?published_at=isnull         null tests
?tags=has.go                 array containment
?metadata=hasdoc.{"a":1}     jsonb containment
?or=(status.eq.draft,view_count.lt.10)
?sort=-created_at,title      "-" for descending
?select=id,title             projection
?search=ada                  fan-out over searchable columns
?page=2&per_page=50          pagination
?cursor=…                    keyset pagination, instead of page

opts := filter.Options{Model: sqlb.ModelOf[recipes.Post]()}

values, err := url.ParseQuery("status=in.published,review&view_count=gte.100&sort=-view_count&per_page=10")
if err != nil {
	panic(err)
}
q, err := filter.Parse(values, opts)
if err != nil {
	panic(err)
}

show(filter.Apply(sqlb.Query[recipes.Post](), q))

Output:
SELECT "id", "org_id", "author_id", "title", "body", "status", "view_count", "tags", "metadata", "published_at", "deleted_at", "created_at" FROM "posts" WHERE ("status" IN ($1, $2)) AND ("view_count" >= $3) ORDER BY "view_count" DESC, "id" DESC LIMIT 10 OFFSET 0
args: [published review 100]

Options is where a resource's limits live. They are not advisory: an unbounded list endpoint is a denial of service waiting for a client that forgets to paginate, so the defaults are conservative and a request over budget is refused rather than silently trimmed.

MaxFilters counts leaf conditions, including the ones inside or= groups — counting top-level parameters instead would leave the budget open to one group holding as many conditions as the client cared to write.

opts := filter.Options{
	Model:           sqlb.ModelOf[recipes.Post](),
	DefaultPageSize: 20,
	MaxPageSize:     100,
	MaxFilters:      2,
	MaxSortTerms:    2,
	Expandable:      []string{"author"},
}

values, err := url.ParseQuery("or=(status.eq.draft,status.eq.review,view_count.lt.10)")
if err != nil {
	panic(err)
}
_, err = filter.Parse(values, opts)
// The prefix is the package; the word after it is the parameter at fault,
// which for a budget overrun is `filter` itself.
showError(err)

Output:
filter: filter: 3 filter conditions requested, the limit is 2

Apply owns the projection, and the default is every *non-hidden* column — not the builder's "every mapped column". The difference is the reason: a handler that forgot to project would otherwise put a hidden column into a response, and a default that is safe only when remembered is not a default.

Author.PasswordHash is what that buys. It is absent from both statements below, and there is no ?select that names it back in.

opts := filter.Options{Model: sqlb.ModelOf[recipes.Author]()}

values, err := url.ParseQuery("select=id,name")
if err != nil {
	panic(err)
}
q, err := filter.Parse(values, opts)
if err != nil {
	panic(err)
}
show(filter.Apply(sqlb.Query[recipes.Author](), q))

all, err := filter.Parse(url.Values{}, opts)
if err != nil {
	panic(err)
}
show(filter.Apply(sqlb.Query[recipes.Author](), all))

Output:
SELECT "id", "name" FROM "authors" ORDER BY "id" ASC LIMIT 25 OFFSET 0
SELECT "id", "org_id", "name", "email" FROM "authors" ORDER BY "id" ASC LIMIT 25 OFFSET 0

?search fans out over every column that declared the capability, joined with OR. A column is searchable only if it said so, which is what keeps the fan-out from turning into a sequential scan of the whole table.

opts := filter.Options{Model: sqlb.ModelOf[recipes.Post]()}

values, err := url.ParseQuery("search=postgres")
if err != nil {
	panic(err)
}
q, err := filter.Parse(values, opts)
if err != nil {
	panic(err)
}
showWhere(filter.Apply(sqlb.Query[recipes.Post](), q))

Output:
WHERE ("title" ILIKE $1) OR ("body" ILIKE $2) ORDER BY "id" ASC LIMIT 25 OFFSET 0
args: [%postgres% %postgres%]

A JSON filter tree is the second frontend over the same compiler. Use it when the nesting a client needs outgrows what a query string can spell — and when the client is a program rather than a person, which is most of the time an agent is involved.

It is gated identically: the same column capabilities, the same coercion, the same bind discipline, the same budget. "Arbitrary nesting" is not a way in.

body := []byte(`{
	  "op": "and",
	  "children": [
	    {"op": "eq",  "field": "org_id", "value": "acme"},
	    {"op": "or",  "children": [
	      {"op": "eq",  "field": "status",     "value": "published"},
	      {"op": "gte", "field": "view_count", "value": 1000}
	    ]}
	  ]
	}`)

pred, err := filter.ParseFilterTree(body, filter.Options{Model: sqlb.ModelOf[recipes.Post]()})
if err != nil {
	panic(err)
}

showWhere(sqlb.Query[recipes.Post]().Where(pred))

Output:
WHERE ("org_id" = $1) AND (("status" = $2) OR ("view_count" >= $3))
args: [acme published 1000]

The refusals are the same too, and every problem is reported at once rather than one per round trip — which is the difference between a client that can fix its request and one that plays twenty questions.

body := []byte(`{
	  "op": "and",
	  "children": [
	    {"op": "eq",       "field": "password_hash", "value": "x"},
	    {"op": "contains", "field": "view_count",    "value": "12"}
	  ]
	}`)

_, err := filter.ParseFilterTree(body, filter.Options{Model: sqlb.ModelOf[recipes.Author]()})
showFilterErrors(err)

Output:
filter: password_hash: unknown parameter (allowed: id, org_id, name, email)
filter: view_count: unknown parameter (allowed: id, org_id, name, email)

A tree may also arrive inside a query string, in the reserved `filter` parameter, alongside the URL grammar. Parse charges both to one MaxFilters budget — the point of the budget being what a request costs, not which spelling it used.

showConst("filter.TreeParam", filter.TreeParam)

Output:
filter.TreeParam = filter

BeforeUpdate and BeforeDelete receive the statement rather than the rows, so they can force a column or narrow what is affected. Forcing updated_at here means no call site can forget it — including one written next year.

reg := sqlb.NewRegistry()
hooks := sqlb.On[recipes.Post](reg)

hooks.BeforeUpdate(func(ctx context.Context, u *sqlb.Update[recipes.Post]) error {
	org, ok := orgFrom(ctx)
	if !ok {
		return errors.New("no tenant on the context")
	}
	u.Where(sqlb.F("org_id").Eq(org))
	return nil
})

ctx := context.WithValue(context.Background(), orgKey{}, "acme")
_, err := sqlb.UpdateRows[recipes.Post]().
	Set("status", "published").
	Where(sqlb.F("id").Eq("p1")).
	Exec(ctx, recordingDB().WithHooks(reg))
if err != nil {
	panic(err)
}
fmt.Println(lastWhere())

Output:
("id" = $2) AND ("org_id" = $3)

The hook amends a clone, so running the same builder twice does not accumulate its predicates. That is what makes a base query safe to keep around.

reg := sqlb.NewRegistry()
hooks := sqlb.On[recipes.Post](reg)
hooks.BeforeQuery(func(_ context.Context, q *sqlb.Builder[recipes.Post]) error {
	q.Where(sqlb.F("org_id").Eq("acme"))
	return nil
})

db := recordingDB().WithHooks(reg)
ctx := context.Background()
q := sqlb.Query[recipes.Post]().Where(sqlb.F("status").Eq("published"))

for range 2 {
	if _, err := q.All(ctx, db); err != nil {
		panic(err)
	}
	fmt.Println(lastWhere())
}

Output:
("status" = $1) AND ("org_id" = $2)
("status" = $1) AND ("org_id" = $2)

Two registries, and therefore two sets of domain rules, coexisting in one process. A handle carries exactly the rules it was given, so the strict one and the unrestricted one are the same pool seen through different rules.

strict := sqlb.NewRegistry()
sqlb.On[recipes.Post](strict).BeforeQuery(func(_ context.Context, q *sqlb.Builder[recipes.Post]) error {
	q.Where(sqlb.F("status").Eq("published"))
	return nil
})

ctx := context.Background()
db := recordingDB()

if _, err := sqlb.Query[recipes.Post]().All(ctx, db.WithHooks(strict)); err != nil {
	panic(err)
}
fmt.Println("with registry:", lastWhere())

if _, err := sqlb.Query[recipes.Post]().All(ctx, db); err != nil {
	panic(err)
}
fmt.Println("without:      ", lastWhere())

Output:
with registry: "status" = $1
without:       (no WHERE clause)

BeforeCreate runs on each row before insert and may modify it: normalising an email, deriving a slug, stamping the owner from the context. Doing it here rather than in a handler means it also holds for the rows a generated REST handler creates.

reg := sqlb.NewRegistry()
hooks := sqlb.On[recipes.Post](reg)

hooks.BeforeCreate(func(ctx context.Context, p *recipes.Post) error {
	p.Title = strings.TrimSpace(p.Title)
	if p.Title == "" {
		return errors.New("a post needs a title")
	}
	if org, ok := orgFrom(ctx); ok {
		p.OrgID = org
	}
	return nil
})

ctx := context.WithValue(context.Background(), orgKey{}, "acme")
post := recipes.Post{Title: "  Hello  "}
if _, err := sqlb.InsertRows(&post).One(ctx, recordingDB().WithHooks(reg)); err != nil {
	panic(err)
}
fmt.Printf("%q in %q\n", post.Title, post.OrgID)

empty := recipes.Post{Title: "   "}
_, err := sqlb.InsertRows(&empty).One(ctx, recordingDB().WithHooks(reg))
fmt.Println("refused:", err)

Output:
"Hello" in "acme"
refused: a post needs a title

A hook that must read rows written earlier in the same transaction has to read through the transaction handle. Reading through the pool would miss them, because they are not committed yet — and TxFrom is how the hook gets hold of it.

reg := sqlb.NewRegistry()
hooks := sqlb.On[recipes.Post](reg)

hooks.BeforeCreate(func(ctx context.Context, p *recipes.Post) error {
	tx, ok := sqlb.TxFrom(ctx)
	if !ok {
		return errors.New("posts must be created inside a transaction")
	}
	n, err := sqlb.Query[recipes.Post]().Where(sqlb.F("title").Eq(p.Title)).Count(ctx, tx)
	if err != nil {
		return err
	}
	fmt.Println("existing posts with that title, as this transaction sees it:", n)
	return nil
})

db := recordingDB().WithHooks(reg)
err := db.WithTx(context.Background(), func(ctx context.Context, tx *sqlb.DB) error {
	post := recipes.Post{Title: "Hello"}
	_, err := sqlb.InsertRows(&post).One(ctx, tx)
	return err
})
if err != nil {
	panic(err)
}

// Outside one, the hook refuses rather than reading the wrong thing.
post := recipes.Post{Title: "Hello"}
_, err = sqlb.InsertRows(&post).One(context.Background(), db)
fmt.Println("outside a transaction:", err)

Output:
existing posts with that title, as this transaction sees it: 1
outside a transaction: posts must be created inside a transaction

BeforeQuery is the load-bearing hook: it receives the query itself, so one registration constrains every read of the model — including the reads that generated REST handlers issue. Tenant scoping stops being something each call site has to remember.

Registration happens once at startup, into a registry the handle then carries. There is no process-wide registry to fall back on (ADR-0047), which is what makes the rules in force a property of how the handle was built.

reg := sqlb.NewRegistry()
hooks := sqlb.On[recipes.Post](reg)

hooks.BeforeQuery(func(ctx context.Context, q *sqlb.Builder[recipes.Post]) error {
	org, ok := orgFrom(ctx)
	if !ok {
		// Not "no restriction". A read with no tenant is a bug, and the
		// shape most tenancy failures take is the fallback that lets it
		// through.
		return errors.New("no tenant on the context")
	}
	q.Where(sqlb.F("org_id").Eq(org), sqlb.F("deleted_at").IsNull())
	return nil
})

db := recordingDB().WithHooks(reg)
ctx := context.WithValue(context.Background(), orgKey{}, "acme")

// The caller filters on status and knows nothing about tenants.
if _, err := sqlb.Query[recipes.Post]().Where(sqlb.F("status").Eq("published")).All(ctx, db); err != nil {
	panic(err)
}
fmt.Println("list: ", lastWhere())

// A different entry point, scoped the same.
if _, err := sqlb.Query[recipes.Post]().Count(ctx, db); err != nil {
	panic(err)
}
fmt.Println("count:", lastWhere())

// And a request that never established a tenant does not run at all.
_, err := sqlb.Query[recipes.Post]().All(context.Background(), db)
fmt.Println("no tenant:", err)

Output:
list:  (("status" = $1) AND ("org_id" = $2)) AND ("deleted_at" IS NULL)
count: ("org_id" = $1) AND ("deleted_at" IS NULL)
no tenant: no tenant on the context

One statement, several rows. Only and Omit narrow which columns are written, for the cases where "every non-zero column" is not what was meant.

a := recipes.Post{OrgID: "acme", Title: "First"}
b := recipes.Post{OrgID: "acme", Title: "Second"}

show(sqlb.InsertRows(&a, &b).Only("org_id", "title"))

Output:
INSERT INTO "posts" ("org_id", "title") VALUES ($1, $2), ($3, $4) RETURNING "id", "org_id", "author_id", "title", "body", "status", "view_count", "tags", "metadata", "published_at", "deleted_at", "created_at"
args: [acme First acme Second]

Upsert. OnConflictUpdate names the conflicting columns and the ones to overwrite from the proposed row; OnConflictDoNothing skips instead.

A skipped row cannot be told apart from its neighbours in what comes back, so a do-nothing statement that skipped anything leaves *every* caller struct untouched and the returned slice is the only account of what was written.

post := recipes.Post{OrgID: "acme", Title: "Hello"}

show(sqlb.InsertRows(&post).
	Only("org_id", "title", "status").
	OnConflictUpdate([]string{"org_id", "title"}, "status"))

Output:
INSERT INTO "posts" ("org_id", "title", "status") VALUES ($1, $2, $3) ON CONFLICT ("org_id", "title") DO UPDATE SET "status" = EXCLUDED."status" RETURNING "id", "org_id", "author_id", "title", "body", "status", "view_count", "tags", "metadata", "published_at", "deleted_at", "created_at"
args: [acme Hello ]

InsertRows takes pointers, and the statement always returns the stored rows, so database-generated values land back in the caller's structs. A column carrying a default is omitted when its Go value is the zero value — which is why the empty ID below does not overwrite the key the database generates.

post := recipes.Post{OrgID: "acme", AuthorID: "a1", Title: "Hello", Status: "draft"}

stored, err := sqlb.InsertRows(&post).One(context.Background(), recordingDB())
if err != nil {
	panic(err)
}
fmt.Println("returned:", stored.ID, stored.CreatedAt.Format("2006-01-02"))
fmt.Println("caller's struct:", post.ID, post.CreatedAt.Format("2006-01-02"))

Output:
returned: p1 2026-06-01
caller's struct: p1 2026-06-01

LeftJoin keeps rows with no match, which is what "and how many comments does each have, including none" needs. The aggregate then counts a column of the joined table rather than rows, so a post with no comments counts 0 instead of 1.

show(sqlb.Query[recipes.Post]().
	LeftJoin("comments", "c", sqlb.F("c.post_id").EqField(sqlb.F("posts.id"))).
	Select(sqlb.F("posts.id"), sqlb.CountOf(sqlb.F("c.id")).As("comments")).
	GroupBy(sqlb.F("posts.id")))

Output:
SELECT "posts"."id", count("c"."id") AS "comments" FROM "posts" LEFT JOIN "comments" AS "c" ON "c"."post_id" = "posts"."id" GROUP BY "posts"."id"

Qualify attaches a table to a column reference after the fact, for building predicates in a helper that does not know which alias it will be used under.

published := func(table string) sqlb.Pred {
	return sqlb.F("status").Qualify(table).Eq("published")
}

showWhere(sqlb.Query[recipes.Post]().As("p").Where(published("p")))

Output:
WHERE "p"."status" = $1
args: [published]

As aliases the model's own table, which is what a self-join needs: both sides are the same table, so at least one of them must be called something else.

EqField is the only column-to-column comparison there is. Anything else — `earlier.published_at < p.published_at` — needs RawPred, for the reason raw_test.go gives.

show(sqlb.Query[recipes.Post]().
	As("p").
	Join("posts", "sibling", sqlb.F("sibling.author_id").EqField(sqlb.F("p.author_id"))).
	Where(sqlb.Not(sqlb.F("sibling.id").EqField(sqlb.F("p.id")))).
	Select(sqlb.F("p.id"), sqlb.Sel(sqlb.F("sibling.id").Column()).As("sibling_id")))

Output:
SELECT "p"."id", "sibling"."id" AS "sibling_id" FROM "posts" AS "p" JOIN "posts" AS "sibling" ON "sibling"."author_id" = "p"."author_id" WHERE NOT ("sibling"."id" = "p"."id")

Join takes the table, an alias and the ON predicate — built from EqField, since both sides are columns. The alias may be empty, in which case the table name is the alias.

A join changes which rows come back, not which columns: the projection is still the model's. Use Expand instead when the point is to *carry* the other row in the response; see expand_test.go.

show(sqlb.Query[recipes.Post]().
	Join("authors", "a", sqlb.F("a.id").EqField(sqlb.F("posts.author_id"))).
	Where(sqlb.F("a.name").Contains("ada")).
	Select(sqlb.F("posts.id"), sqlb.F("posts.title")))

Output:
SELECT "posts"."id", "posts"."title" FROM "posts" JOIN "authors" AS "a" ON "a"."id" = "posts"."author_id" WHERE "a"."name" ILIKE $1
args: [%ada%]

ContainsJSON is Postgres's `@>`: every key and value in the document must appear in the column. It is the operator a GIN index over the column serves, and it is why a document column can be narrowed without the schema declaring in advance which keys it holds.

The argument is JSON text rather than a Go value because a predicate has no error to return and marshalling has one. A caller holding a value marshals it first, and handles the failure where it happens.

filter, err := json.Marshal(map[string]any{"lang": "de"})
if err != nil {
	panic(err)
}

showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("metadata").ContainsJSON(string(filter))))

Output:
WHERE "metadata" @> $1::jsonb
args: [{"lang":"de"}]

A jsonb column is json.RawMessage, not []byte. That distinction is load bearing: a bytea column is also a slice of bytes, and sqlb decides which operators a column offers by the Go type, so getting it backwards would offer document containment over a blob.

post, err := sqlb.Query[recipes.Post]().First(context.Background(), recordingDB())
if err != nil {
	panic(err)
}

var meta struct {
	Lang string `json:"lang"`
}
if err := json.Unmarshal(post.Metadata, &meta); err != nil {
	panic(err)
}
fmt.Println(string(post.Metadata), "->", meta.Lang)

Output:
{"lang":"en"} -> en

Writing a document is the value going the other way, and the column takes it as a bind parameter like any other.

doc, err := json.Marshal(map[string]any{"lang": "de", "reviewed": true})
if err != nil {
	panic(err)
}

show(sqlb.UpdateRows[recipes.Post]().
	Set("metadata", json.RawMessage(doc)).
	Where(sqlb.F("id").Eq("p1")))

Output:
UPDATE "posts" SET "metadata" = $1 WHERE "id" = $2 RETURNING "id", "org_id", "author_id", "title", "body", "status", "view_count", "tags", "metadata", "published_at", "deleted_at", "created_at"
args: [{"lang":"de","reviewed":true} p1]

Every later migration is the same call with a different left-hand side: what the database has now, against what the schema declares. `sqlb migrate` gets "now" by replaying the checked-in history into a shadow database; introspect gets it from a live one.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/migrate"
	"github.com/jryannel/sqlb/schema"
)

func main() {
	current := schema.NewRegistry()
	current.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
	)

	target := schema.NewRegistry()
	target.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
		schema.Text("slug").Unique().Filterable(),
	).Index("slug")

	changes, err := migrate.Diff(current, target)
	if err != nil {
		panic(err)
	}
	for _, c := range changes {
		fmt.Println(c.Up)
	}
}

Output:
ALTER TABLE "posts" ADD COLUMN "slug" text NOT NULL;
ALTER TABLE "posts" ADD CONSTRAINT "posts_slug_key" UNIQUE ("slug");
CREATE INDEX CONCURRENTLY "posts_slug_idx" ON "posts" ("slug");

A change that can lose data is marked, carries the reason, and renders commented out unless the caller passes AllowDestructive. Dropping a column should be a deliberate act with a flag attached, not something that happens because a generator decided it.

package main

import (
	"fmt"
	"strings"

	"github.com/jryannel/sqlb/migrate"
	"github.com/jryannel/sqlb/schema"
)

func main() {
	current := schema.NewRegistry()
	current.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
		schema.Text("subtitle"),
	)

	target := schema.NewRegistry()
	target.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
	)

	changes, err := migrate.Diff(current, target)
	if err != nil {
		panic(err)
	}
	for _, c := range changes {
		fmt.Printf("destructive=%v %s\n  reason: %s\n", c.Destructive, c.Up, c.Reason)
	}

	files, err := migrate.Render(
		migrate.Migration{Version: "00002", Name: "drops_subtitle", Changes: changes},
		migrate.Options{Format: migrate.Goose},
	)
	if err != nil {
		panic(err)
	}
	for name, body := range files {
		fmt.Println("--", name)
		fmt.Println(strings.TrimSpace(body))
	}
}

Output:
destructive=true ALTER TABLE "posts" DROP COLUMN "subtitle";
  reason: dropping posts.subtitle deletes its contents. The Down restores the column but not the values
-- 00002_drops_subtitle.sql
-- Generated by sqlb. Review before applying.

-- +goose Up
-- drop column posts.subtitle
-- DESTRUCTIVE: dropping posts.subtitle deletes its contents. The Down
--   restores the column but not the values
-- Review, then uncomment to apply. Generated commented out on purpose.
-- ALTER TABLE "posts" DROP COLUMN "subtitle";

-- +goose Down
-- DESTRUCTIVE: dropping posts.subtitle deletes its contents. The Down
--   restores the column but not the values
-- Review, then uncomment to apply. Generated commented out on purpose.
-- ALTER TABLE "posts" ADD COLUMN "subtitle" text NOT NULL;

Diff returns migration changes as values. Nothing runs: your runner applies them, or a person reads them first, which is the difference between a migration tool and a generator of migrations.

The first migration is a diff against nothing.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/migrate"
	"github.com/jryannel/sqlb/schema"
)

func main() {
	target := schema.NewRegistry()
	target.Table("orgs",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("name").Sortable(),
		schema.Timestamps(),
	)

	changes, err := migrate.Diff(schema.NewRegistry(), target, migrate.MinPostgres(18))
	if err != nil {
		panic(err)
	}
	for _, c := range changes {
		fmt.Println(c.Up)
	}
}

Output:
CREATE TABLE "orgs" (
    "id" uuid NOT NULL DEFAULT uuidv7(),
    "name" text NOT NULL,
    "created_at" timestamptz NOT NULL DEFAULT now(),
    "updated_at" timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT "orgs_pkey" PRIMARY KEY ("id")
);

A CREATE INDEX comes out CONCURRENTLY, and migrate.Split gives it a file of its own — transaction control in goose and golang-migrate is per file, so a statement that cannot run inside a transaction cannot share one with statements that should.

Blocking is the hook for a policy this package cannot have: whether a full table scan is acceptable depends on how many rows the table holds, which is not in the schema. A project that knows its big tables can refuse a migration touching one, or route it to whoever sequences an expand/contract rollout.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/migrate"
	"github.com/jryannel/sqlb/schema"
)

func main() {
	current := schema.NewRegistry()
	current.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
	)

	target := schema.NewRegistry()
	target.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title"),
		schema.BigInt("view_count").Default(schema.Value(0)),
	).Index("view_count")

	changes, err := migrate.Diff(current, target)
	if err != nil {
		panic(err)
	}
	m := migrate.Migration{Version: "00002", Name: "adds_view_count", Changes: changes}

	fmt.Println("destructive:", m.Destructive())
	for _, c := range m.Blocking() {
		fmt.Println("blocking:", c.Up)
	}
}

Output:
destructive: false

An update or delete with no Where is refused rather than run, because rewriting every row is almost never what was meant. Everything is how a caller who did mean it says so — one word, at the call site, in the diff.

_, _, err := sqlb.UpdateRows[recipes.Post]().Set("status", "archived").SQL()
showError(err)
fmt.Println("is ErrUnscoped:", errors.Is(err, sqlb.ErrUnscoped))

showWhere(sqlb.UpdateRows[recipes.Post]().Set("status", "archived").Everything())

Output:
sqlb: statement would affect every row; add a Where clause or call Everything to confirm
is ErrUnscoped: true
(no WHERE clause)

Between is a closed interval — both ends included — and NotBetween excludes one. It is two bind parameters rather than a range type, so it works for timestamps, numbers and anything else the column's type compares.

from := time.Date(2026, 1, 1, 0, 0, 0, 0, time.UTC)
to := time.Date(2026, 12, 31, 23, 59, 59, 0, time.UTC)

showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("published_at").Between(from, to)))

Output:
WHERE "published_at" BETWEEN $1 AND $2
args: [2026-01-01 00:00:00 +0000 UTC 2026-12-31 23:59:59 +0000 UTC]

EqField compares two columns instead of a column and a value. It is what a join condition is made of, and what a self-referential comparison needs — neither of which can be spelled with Eq, since a value there would be bound as a parameter rather than read as a column.

showWhere(sqlb.Query[recipes.Post]().
	Where(sqlb.F("posts.author_id").EqField(sqlb.F("comments.author_id"))))

Output:
WHERE "posts"."author_id" = "comments"."author_id"

The comparison operators, which are the ones with no surprises: Eq, Neq, Gt, Gte, Lt, Lte. Every operand becomes a bind parameter.

showWhere(sqlb.Query[recipes.Post]().Where(
	sqlb.F("view_count").Gte(100),
	sqlb.F("view_count").Lt(10_000),
	sqlb.F("status").Neq("draft"),
))

Output:
WHERE (("view_count" >= $1) AND ("view_count" < $2)) AND ("status" <> $3)
args: [100 10000 draft]

OneOf is an IN list, and its empty case is the one worth knowing: it matches nothing, because that is what `in ()` means. The alternative — quietly dropping the predicate — turns an empty permission set into "may see everything", which is the shape a lot of authorisation bugs take.

NotOneOf is its negation, and an empty NotOneOf excludes nothing.

showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("status").OneOf("published", "review")))

var allowed []any // the caller may see no statuses at all
showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("status").OneOf(allowed...)))

Output:
WHERE "status" IN ($1, $2)
args: [published review]
WHERE false

Null tests are their own operators, because `= NULL` is not one. IsNull and NotNull are available on any column; whether a *REST request* may ask for them depends on the Go field being a pointer, which is how sqlb knows the column is nullable.

showWhere(sqlb.Query[recipes.Post]().Where(
	sqlb.F("published_at").NotNull(),
	sqlb.F("deleted_at").IsNull(),
))

Output:
WHERE ("published_at" IS NOT NULL) AND ("deleted_at" IS NULL)

Contains escapes LIKE wildcards, so a user typing "100%" searches for that literal string instead of matching every row. StartsWith and EndsWith escape them too; all three are case-insensitive.

Like does not escape, because a pattern is the point of it. Use Like only for patterns your own code wrote.

showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("title").Contains("100%")))
showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("title").StartsWith("Getting")))
showWhere(sqlb.Query[recipes.Post]().Where(sqlb.F("title").Like("Chapter _:%")))

Output:
WHERE "title" ILIKE $1
args: [%100\%%]
WHERE "title" ILIKE $1
args: [Getting%]
WHERE "title" LIKE $1
args: [Chapter _:%]

A cursor names the position of the last row rather than counting to it, so page 500 costs what page 1 costs and a concurrent insert cannot make a client read a row twice.

The loop is: run the query, take CursorFor on the last row, hand it back as After. A zero cursor is a no-op, so the first request and every one after it run the same code.

ctx := context.Background()
db := recordingDB()

page := sqlb.Query[recipes.Post]().
	OrderBy(sqlb.F("created_at").Desc()).
	Limit(20)

rows, err := page.All(ctx, db)
if err != nil {
	panic(err)
}

next, err := page.CursorFor(rows[len(rows)-1])
if err != nil {
	panic(err)
}

// The next request. Same query, plus the cursor the client sent back.
showWhere(page.Clone().After(next).Select(sqlb.F("id")))
fmt.Println("zero cursor is the first page:", sqlb.Cursor("").IsZero())

Output:
WHERE ("created_at", "id") < ($1, $2) ORDER BY "created_at" DESC, "id" DESC LIMIT 20
args: [2026-06-01 09:00:00 +0000 UTC p1]
zero cursor is the first page: true

Page is 1-based offset pagination. The limit and offset render as literals rather than bind parameters so the planner can see them; both are validated ints, so there is no injection surface in doing that.

It is the right tool for a page number in a URL and the wrong one for a feed: page 500 makes Postgres count past 9,980 rows to discard them.

show(sqlb.Query[recipes.Post]().
	OrderBy(sqlb.F("created_at").Desc()).
	Page(3, 20).
	Select(sqlb.F("id")))

Output:
SELECT "id" FROM "posts" ORDER BY "created_at" DESC LIMIT 20 OFFSET 40

After keeps its predicate apart from Where rather than folding it in, so Count still answers "how many rows match" rather than "how many are left". A total that changed as a client paged would be a worse answer than no total.

ctx := context.Background()
db := recordingDB()

page := sqlb.Query[recipes.Post]().
	Where(sqlb.F("status").Eq("published")).
	OrderBy(sqlb.F("created_at").Desc())

rows, err := page.All(ctx, db)
if err != nil {
	panic(err)
}
next, err := page.CursorFor(rows[0])
if err != nil {
	panic(err)
}

if _, err := page.Clone().After(next).Count(ctx, db); err != nil {
	panic(err)
}
fmt.Println("count WHERE:", lastWhere())

Output:
count WHERE: "status" = $1

A cursor is opaque by intent rather than by encryption. It decodes to the ordering columns and the values of the row it was taken from — nothing a client could not read off the response it came in — and After checks those columns against the ordering the request actually asked for, so an edited cursor can only move the boundary along a column the caller was already permitted to sort by.

ctx := context.Background()

page := sqlb.Query[recipes.Post]().OrderBy(sqlb.F("created_at").Desc()).Limit(20)
rows, err := page.All(ctx, recordingDB())
if err != nil {
	panic(err)
}
cursor, err := page.CursorFor(rows[0])
if err != nil {
	panic(err)
}

showDecodedCursor(cursor)

Output:
{"k":[{"c":"created_at","d":true,"v":"2026-06-01T09:00:00Z"},{"c":"id","d":true,"v":"p1"}]}

Stable makes an ordering deterministic by appending the primary key, and without it no cursor can exist. `ORDER BY status` leaves rows with equal status in whatever order the plan produced, so page 2 may repeat a row from page 1 or skip one — and nothing in the result can tell the two apart.

The appended term takes the direction of the last existing one, so "newest first" stays newest-first rather than reversing halfway through.

show(sqlb.Query[recipes.Post]().
	OrderBy(sqlb.F("created_at").Desc()).
	Stable().
	Select(sqlb.F("id")))

Output:
SELECT "id" FROM "posts" ORDER BY "created_at" DESC, "id" DESC

Where conjoins predicates, and zero predicates are skipped. That is why an optional filter needs no surrounding statement: the branch that decides whether the filter applies is the branch that already exists.

This is the case static query generators cannot express, and the reason sqlb exists.

search := "postgres" // in a handler, this came from the request

q := sqlb.Query[recipes.Post]().Where(sqlb.F("status").Eq("published"))
if search != "" {
	q = q.Where(sqlb.F("title").Contains(search))
}

showWhere(q)

Output:
WHERE ("status" = $1) AND ("title" ILIKE $2)
args: [published %postgres%]

Predicates are values, so a rule used in several places can be a function. Nothing in the type says "predicate about posts", which is deliberate: the column names are checked when the statement compiles against a model.

// visible is the rule "published, and not soft-deleted", written once.
visible := func() sqlb.Pred {
	return sqlb.And(
		sqlb.F("status").Eq("published"),
		sqlb.F("deleted_at").IsNull(),
	)
}

showWhere(sqlb.Query[recipes.Post]().Where(visible(), sqlb.F("org_id").Eq("acme")))

Output:
WHERE (("status" = $1) AND ("deleted_at" IS NULL)) AND ("org_id" = $2)
args: [published acme]

If drops the predicate when its condition does not hold, which keeps the chain unbroken. It returns a zero Pred, and Where skips those — so the same call site reads the same way whether or not the filter was supplied.

minViews := int64(0) // this request did not ask for a minimum

showWhere(sqlb.Query[recipes.Post]().Where(
	sqlb.F("status").Eq("published"),
	sqlb.If(minViews > 0, sqlb.F("view_count").Gte(minViews)),
))

Output:
WHERE "status" = $1
args: [published]

Not negates a predicate rather than requiring a negated operator for every comparison. The parenthesisation is explicit, so a negated disjunction means what it reads as.

showWhere(sqlb.Query[recipes.Post]().Where(
	sqlb.Not(sqlb.Or(
		sqlb.F("status").Eq("draft"),
		sqlb.F("status").Eq("review"),
	)),
))

Output:
WHERE NOT (("status" = $1) OR ("status" = $2))
args: [draft review]

Or groups alternatives into one predicate, which Where then conjoins with the rest. And nests the other way. Values never reach the SQL text: every one becomes a bind parameter, which is the whole bind discipline in one sentence.

showWhere(sqlb.Query[recipes.Post]().Where(
	sqlb.F("org_id").Eq("acme"),
	sqlb.Or(
		sqlb.F("status").Eq("published"),
		sqlb.And(
			sqlb.F("status").Eq("review"),
			sqlb.F("view_count").Gt(100),
		),
	),
))

Output:
WHERE ("org_id" = $1) AND (("status" = $2) OR (("status" = $3) AND ("view_count" > $4)))
args: [acme published review 100]

Building a slice of predicates is the other shape, for when the conditions come from a loop rather than from named branches. Where is variadic, so the slice goes in whole.

requested := map[string]string{"status": "published"} // sorted below for a stable output

var preds []sqlb.Pred
for _, name := range []string{"author_id", "org_id", "status"} {
	if v, ok := requested[name]; ok {
		preds = append(preds, sqlb.F(name).Eq(v))
	}
}

showWhere(sqlb.Query[recipes.Post]().Where(preds...))

Output:
WHERE "status" = $1
args: [published]

Building the query and running it are separate steps, so a base query can be built once and reused. Clone is what makes that safe: the builder's methods mutate in place, which is what lets a hook amend a query it was handed, and means two callers must not share one.

base := sqlb.Query[recipes.Post]().Where(sqlb.F("org_id").Eq("acme"))

drafts := base.Clone().Where(sqlb.F("status").Eq("draft"))
published := base.Clone().Where(sqlb.F("status").Eq("published"))

showWhere(drafts)
showWhere(published)
showWhere(base) // untouched

Output:
WHERE ("org_id" = $1) AND ("status" = $2)
args: [acme draft]
WHERE ("org_id" = $1) AND ("status" = $2)
args: [acme published]
WHERE "org_id" = $1
args: [acme]

A query is a value, and SQL renders it without running it.

This is the inspection point the rest of these recipes are written against: log it, diff it in a test, or paste it into EXPLAIN. Nothing here has touched a database.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
	"github.com/jryannel/sqlb/example/recipes"
)

func main() {
	q := sqlb.Query[recipes.Post]().
		Where(sqlb.F("status").Eq("published")).
		OrderBy(sqlb.F("view_count").Desc()).
		Limit(10)

	sql, args, err := q.SQL()
	if err != nil {
		panic(err)
	}
	fmt.Println(sql)
	fmt.Println("args:", args)
}

Output:
SELECT "posts"."id", "posts"."org_id", "posts"."author_id", "posts"."title", "posts"."body", "posts"."status", "posts"."view_count", "posts"."tags", "posts"."metadata", "posts"."published_at", "posts"."deleted_at", "posts"."created_at" FROM "posts" WHERE "status" = $1 ORDER BY "view_count" DESC LIMIT 10
args: [published]

Select replaces the default projection of every mapped column. Collect scans the result into a type other than the model, which is how a query that no longer returns rows of T stays typed.

type titleOnly struct {
	ID    string `db:"id"`
	Title string `db:"title"`
}

q := sqlb.Query[recipes.Post]().
	Select(sqlb.F("id"), sqlb.F("title")).
	Where(sqlb.F("status").Eq("published"))

rows, err := sqlb.Collect[titleOnly](
	context.Background(),
	recordingDBWith([]string{"id", "title"}, []any{"p1", "Hello"}),
	q,
)
if err != nil {
	panic(err)
}
show(q)
fmt.Println(rows)

Output:
SELECT "id", "title" FROM "posts" WHERE "status" = $1
args: [published]
[{p1 Hello}]

Terminal methods run the query, and each says what it expects of the result. Choosing the right one is how "exactly one row" becomes an error rather than a silently discarded second row.

All    every matching row
First  the first, or ErrNotFound — pair it with OrderBy
One    the only one; more than one match is an error
Count  how many match, ignoring pagination
Exists whether any match, without fetching one

db := recordingDB()
ctx := context.Background()

posts, err := sqlb.Query[recipes.Post]().Where(sqlb.F("org_id").Eq("acme")).All(ctx, db)
if err != nil {
	panic(err)
}
fmt.Println("all:", len(posts), posts[0].Title)

n, err := sqlb.Query[recipes.Post]().Count(ctx, db)
if err != nil {
	panic(err)
}
fmt.Println("count:", n)

_, err = sqlb.Query[recipes.Post]().Where(sqlb.F("id").Eq("nope")).One(ctx, recordingDBWith(postColumns))
fmt.Println("one, no match:", errors.Is(err, sqlb.ErrNotFound))

Output:
all: 1 Hello
count: 1
one, no match: true

Cast emits its type name verbatim, so it must never come from user input. The value beside it is still bound.

show(sqlb.Query[recipes.Post]().
	Select(sqlb.Sel(sqlb.F("metadata").Cast("text")).As("metadata_text")).
	Where(sqlb.F("id").Eq("p1")))

Output:
SELECT "metadata"::text AS "metadata_text" FROM "posts" WHERE "id" = $1
args: [p1]

The comparison EqField does not cover. Only equality has a column-to-column form, so an inequality between two columns is raw — which is a small enough gap that closing it with a partial operator set would be worse than naming it.

showWhere(sqlb.Query[recipes.Post]().
	As("p").
	Join("posts", "sibling", sqlb.F("sibling.author_id").EqField(sqlb.F("p.author_id"))).
	Where(sqlb.RawPred(`"sibling"."published_at" < "p"."published_at"`)))

Output:
WHERE "sibling"."published_at" < "p"."published_at"

RawPred is verbatim SQL with its own bind parameters, written as `?` placeholders which the compiler renumbers into $1, $2 alongside everything else. Use it for expressions the builder cannot model — and only for those: its contents are not validated, so a value that reached it by concatenation is an injection.

The values still go through `?`. That is the discipline the escape hatch keeps: raw *structure*, never raw values.

showWhere(sqlb.Query[recipes.Post]().Where(
	sqlb.F("org_id").Eq("acme"),
	sqlb.RawPred(`to_tsvector('english', "body") @@ plainto_tsquery('english', ?)`, "index scan"),
))

Output:
WHERE ("org_id" = $1) AND (to_tsvector('english', "body") @@ plainto_tsquery('english', $2))
args: [acme index scan]

RawSel is the same escape hatch in the projection, and Raw is the expression form that SetExpr and GroupByExpr take.

show(sqlb.Query[recipes.Post]().
	Select(
		sqlb.F("status"),
		sqlb.RawSel(`percentile_cont(?) WITHIN GROUP (ORDER BY "view_count")`, 0.95).As("p95"),
	).
	GroupBy(sqlb.F("status")))

Output:
SELECT "status", percentile_cont($1) WITHIN GROUP (ORDER BY "view_count") AS "p95" FROM "posts" GROUP BY "status"
args: [0.95]

The mistake this replaces. Eq and its siblings bind their operand as a value, so passing a Field to one sends the *field* to the driver as a parameter rather than comparing columns. It compiles; it is wrong at runtime.

sql, args, err := sqlb.Query[recipes.Post]().
	Where(sqlb.F("published_at").Lt(sqlb.F("created_at"))).
	SQL()
if err != nil {
	panic(err)
}
showContains(sql, `"published_at" < $1`)
showContains(sql, `"published_at" < "created_at"`)
showArgCount(args) // the Field went to the driver as one

Output:
mentions "published_at" < $1: true
mentions "published_at" < "created_at": false
1 bind parameter

The declaration the models in models.go are generated from. A schema file writes `schema.Table(…)`, which registers in the default registry; these recipes each use a registry of their own so that one recipe cannot affect the next.

Everything a column can do is opt-in and said here once. That single statement is what becomes the `sqlb` struct tag, the REST capability, the OpenAPI parameter, the TypeScript type and the CLI flag — which is the reason the declaration is worth reading closely and the generated files are not.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/schema"
)

func main() {
	app := schema.NewRegistry()

	org := app.Table("orgs",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("name").Searchable().Sortable(),
		schema.Text("slug").Unique().Filterable(),
		schema.Timestamps(),
	)

	post := app.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		// A reference declares the foreign key, what a delete of the parent
		// does, and — because it says so — that ?expand=org may follow it.
		schema.Ref("org", org).OnDelete(schema.Cascade).Filterable().Expandable(),

		schema.Text("title").Searchable().Sortable(),
		schema.Enum("status", "draft", "review", "published").
			Default(schema.Value("draft")).
			Filterable().Sortable(),
		schema.BigInt("view_count").Default(schema.Value(0)).Filterable().Sortable().ReadOnly(),
		schema.Timestamp("published_at").Nullable().Filterable().Sortable(),
		schema.Text("tags").Array().Filterable(),

		schema.Timestamps(),
		schema.SoftDelete(),
	).
		Index("org_id", "status").
		Check("published_posts_have_a_date", "status <> 'published' OR published_at IS NOT NULL").
		Describe("A blog post.")

	fmt.Println("table:", post.Name())
	for _, f := range post.Fields() {
		fmt.Println(" ", f.Name())
	}
}

Output:
table: posts
  id
  org_id
  title
  status
  view_count
  published_at
  tags
  created_at
  updated_at
  deleted_at

Expose is what publishes a table over HTTP, and a table without it is reachable from Go and has no REST surface at all. Leaving an operation out of Ops is how a table gets a read API and no delete — which is what a table declaring SoftDelete wants, since the generated delete is a real DELETE.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/schema"
)

func main() {
	app := schema.NewRegistry()

	app.Table("drafts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title").Sortable(),
	)
	posts := app.Table("posts",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Text("title").Searchable().Sortable(),
	).Expose(schema.REST{
		Path:            "/posts",
		Ops:             schema.OpCreate | schema.OpRead | schema.OpUpdate | schema.OpList,
		DefaultPageSize: 20,
		MaxPageSize:     100,
		MaxFilters:      12,
	})

	fmt.Println("exposed:", len(app.Exposed()))
	fmt.Println("path:   ", posts.Rest().Path)
}

Output:
exposed: 1
path:    /posts

Validate answers "is this schema well-formed?" and returns errors. Lint answers "will it behave badly in production?" and returns advice.

The distinction is worth the two functions: a table can validate completely and still expose an unindexed filter that sequential-scans on every request — the kind of mistake that is invisible in review and obvious at three in the morning. Diagnostics are advisory; a filterable column on a table of twenty rows does not need an index.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/schema"
)

func main() {
	app := schema.NewRegistry()

	app.Table("events",
		schema.UUIDv7("id").PrimaryKey(),
		// Filterable, and no index. That is the finding.
		schema.Text("source").Filterable(),
	)

	for _, d := range app.Lint() {
		fmt.Printf("%s (%s.%s)\n  %s\n  fix: %s\n", d.Rule, d.Table, d.Column, d.Message, d.Fix)
	}
}

Output:
unindexed-filter (events.source)
  column is filterable but is not the leading column of any index, so filtering on it scans the table
  fix: add .Index("source") to the table, or drop .Filterable() from the column

A module is a registry whose tables carry its name, so ownership is visible in the database and cannot be forgotten. The prefix is applied by the registry rather than written into each declaration — a convention repeated at every call site is a convention that drifts.

package main

import (
	"fmt"

	"github.com/jryannel/sqlb/schema"
)

func main() {
	billing := schema.NewModule("billing")
	invoice := billing.Table("invoices",
		schema.UUIDv7("id").PrimaryKey(),
		schema.Numeric("amount_due").Filterable(),
	)

	fmt.Println("declared as:", invoice.LocalName())
	fmt.Println("stored as:  ", invoice.Name())
}

Output:
declared as: invoices
stored as:   billing_invoices

AfterCommit is where anything the outside world can observe belongs — publishing an event, enqueuing a job, invalidating a cache.

The AfterCreate family runs *inside* the transaction, which is correct for validation (an error there rolls the write back) and wrong for a side effect, because the transaction may still abort after the hook has already announced a write that then never happened.

db := recordingDB()

err := db.WithTx(context.Background(), func(ctx context.Context, tx *sqlb.DB) error {
	post := recipes.Post{OrgID: "acme", Title: "Hello"}
	if _, err := sqlb.InsertRows(&post).One(ctx, tx); err != nil {
		return err
	}
	return sqlb.AfterCommit(ctx, func(context.Context) error {
		fmt.Println("published post.created")
		return nil
	})
})
if err != nil {
	panic(err)
}
fmt.Println("last statement:", statements()[len(statements())-1])

Output:
published post.created
last statement: COMMIT

A callback registered on a transaction that rolls back never runs. That is the whole guarantee, and it is the reason to reach for AfterCommit rather than doing the side effect after WithTx returns — the registration sits next to the write it belongs to.

db := recordingDB()

err := db.WithTx(context.Background(), func(ctx context.Context, _ *sqlb.DB) error {
	if err := sqlb.AfterCommit(ctx, func(context.Context) error {
		fmt.Println("this never prints")
		return nil
	}); err != nil {
		return err
	}
	return errors.New("aborted")
})
fmt.Println("err:", err)
fmt.Println("last statement:", statements()[len(statements())-1])

Output:
err: aborted
last statement: ROLLBACK

WithTx runs a unit of work on one connection, committing if the function returns nil and rolling back otherwise. The handle passed in executes on the transaction, so every statement inside lands there.

Pass the *inner* ctx onward, not the enclosing one: it is what carries the transaction, and it is what makes TxFrom work inside a hook.

db := recordingDB()

err := db.WithTx(context.Background(), func(ctx context.Context, tx *sqlb.DB) error {
	post := recipes.Post{OrgID: "acme", Title: "Hello"}
	if _, err := sqlb.InsertRows(&post).One(ctx, tx); err != nil {
		return err
	}
	_, err := sqlb.UpdateRows[recipes.Comment]().
		Set("post_id", post.ID).
		Where(sqlb.F("id").Eq("c1")).
		Exec(ctx, tx)
	return err
})
if err != nil {
	panic(err)
}
for _, s := range statements() {
	fmt.Println(firstWords(s, 3))
}

Output:
BEGIN
INSERT INTO "posts"
UPDATE "comments" SET
COMMIT

Nesting joins rather than nests: WithTx on a handle already in a transaction runs on that same transaction and leaves the commit to the outermost call. That keeps a function which opens a transaction callable from inside one, which is what a service method needs to be.

db := recordingDB()

err := db.WithTx(context.Background(), func(ctx context.Context, tx *sqlb.DB) error {
	return tx.WithTx(ctx, func(ctx context.Context, inner *sqlb.DB) error {
		fmt.Println("inner is in a transaction:", inner.InTx())
		post := recipes.Post{Title: "Hello"}
		_, err := sqlb.InsertRows(&post).One(ctx, inner)
		return err
	})
})
if err != nil {
	panic(err)
}
fmt.Println("begins:", count(statements(), "BEGIN"), "commits:", count(statements(), "COMMIT"))

Output:
inner is in a transaction: true
begins: 1 commits: 1

Returning an error rolls back. So does a panic — which is re-raised afterwards, so a transaction is never left open by one.

db := recordingDB()

errRejected := errors.New("the domain said no")
err := db.WithTx(context.Background(), func(ctx context.Context, tx *sqlb.DB) error {
	post := recipes.Post{OrgID: "acme", Title: "Hello"}
	if _, err := sqlb.InsertRows(&post).One(ctx, tx); err != nil {
		return err
	}
	return errRejected
})

fmt.Println("returned:", errors.Is(err, errRejected))
for _, s := range statements() {
	fmt.Println(firstWords(s, 3))
}

Output:
returned: true
BEGIN
INSERT INTO "posts"
ROLLBACK

The same query as the untyped one, with the comparands checked. Eq(42) on a Status column, Contains on ViewCount and Has on Title all fail to compile — which is the whole return on generating the facade.

showWhere(sqlb.Query[recipes.Post]().Where(
	PostCols.Status.OneOf(StatusPublished, StatusReview),
	PostCols.Title.Contains("postgres"),
	PostCols.ViewCount.Gte(100),
	PostCols.Tags.Has("go"),
))

Output:
WHERE ((("status" IN ($1, $2)) AND ("title" ILIKE $3)) AND ("view_count" >= $4)) AND ($5 = ANY("tags"))
args: [published review %postgres% 100 go]

Field is the way back out, for the operators a typed column deliberately does not carry. It is an escape hatch rather than a workaround: the typed surface covers what is checkable, and this covers the rest.

showWhere(sqlb.Query[recipes.Post]().Where(
	PostCols.PublishedAt.Field().Between(
		time.Date(2026, 1, 1, 0, 0, 0, 0, time.UTC),
		time.Date(2026, 6, 30, 0, 0, 0, 0, time.UTC),
	),
))

Output:
WHERE "published_at" BETWEEN $1 AND $2
args: [2026-01-01 00:00:00 +0000 UTC 2026-06-30 00:00:00 +0000 UTC]

A hidden column has no entry in the generated facade at all, so a predicate against one does not compile rather than being refused at runtime. That is the same guarantee the REST layer makes, moved to build time for the caller who has a compiler.

// AuthorCols would have no PasswordHash field. The untyped escape hatch
// still exists, and still runs — Go code was never the thing being
// restricted:
showWhere(sqlb.Query[recipes.Author]().Where(sqlb.F("password_hash").Eq("...")))

Output:
WHERE "password_hash" = $1
args: [...]

Ordering and qualification work the same way, so a typed column is usable everywhere an untyped one is.

show(sqlb.Query[recipes.Post]().
	As("p").
	Select(PostCols.ID.Qualify("p").Field()).
	OrderBy(PostCols.ViewCount.Desc(), PostCols.ID.Asc()))

Output:
SELECT "p"."id" FROM "posts" AS "p" ORDER BY "view_count" DESC, "id" ASC

SetExpr assigns an expression rather than a value, which is what an increment needs: read-modify-write in Go would lose a concurrent update between the read and the write.

show(sqlb.UpdateRows[recipes.Post]().
	SetExpr("view_count", sqlb.Raw{SQL: `"view_count" + 1`}).
	Where(sqlb.F("id").Eq("p1")))

Output:
UPDATE "posts" SET "view_count" = "view_count" + 1 WHERE "id" = $1 RETURNING "id", "org_id", "author_id", "title", "body", "status", "view_count", "tags", "metadata", "published_at", "deleted_at", "created_at"
args: [p1]

One asserts that exactly one row was affected. The check is on the *result*, so an update that matched three rows has already changed all three when the error returns — under autocommit that is durable. Inside WithTx the error rolls it back, which is what turns "expected one" from a report into a refusal.

_, err := sqlb.UpdateRows[recipes.Post]().
	Set("status", "published").
	Where(sqlb.F("id").Eq("p1")).
	One(context.Background(), recordingDB())
showError(err)

Output:
(no error)