sqlb

package module
v0.4.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 30, 2026 License: MIT Imports: 19 Imported by: 0

README

sqlb

Go Reference CI Go Version

A schema-first data layer for Go and Postgres: declare your tables once, get typed composable queries, a validated REST filter grammar, and domain hooks — without hand-writing the HTTP-to-SQL layer for every dynamic view.

Documentation · Quickstart · API reference · Decision records

Why

Static query generators cannot express "this WHERE clause exists only when the user typed something in the search box." The usual workaround is string concatenation, which is why the HTTP layer of a filter/sort/search page is mostly boilerplate.

PostgREST solves that by making the database the API, but there is then nowhere to put Go domain logic, and the whole schema sits one policy mistake away from being public.

sqlb takes the middle path. A query is a value, so predicates can be added conditionally:

q := sqlb.Query[Post]().Where(sqlb.F("status").Eq("published"))
if search != "" {
    q = q.Where(sqlb.F("title").Contains(search))
}
posts, err := q.OrderBy(sqlb.F("created_at").Desc()).Limit(50).All(ctx, db)

and the REST filter grammar compiles into that same predicate AST. One compiler, one bind-parameter discipline, one set of hooks — two producers.

What that buys

  • Capabilities are opt-in per column. Filterable, Sortable, Searchable, Hidden. A column that does not declare a capability cannot be reached through it — ever, and the failure is a 400 naming what would have been accepted, not a leak. This is the difference between this and exposing the database.
  • Hooks are the domain seam. BeforeQuery receives the query itself, so one registration constrains every read of a model — including the reads that generated REST handlers issue. Tenant scoping stops being something each call site has to remember.
  • Paging that survives a write. ?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. Every list response carries the cursor for the next page, so adopting it needs no flag.
  • Nothing runs unasked. SQL() renders text and args without executing. Explain plans against the live schema without running it, so it also fails on the migration that was written and never applied — which a compile-time column check cannot. Diff returns migration changes as values; your runner applies them.
  • The clients are generated from the schema too. A TypeScript client, emitted into the repository that consumes it, where where admits only filterable columns with the operators their type accepts, select narrows the response type, and a hidden column has no spelling at all. The OpenAPI document cannot say any of that — ?status=eq.published documents as array<string> — so it is generated from the model instead (guide). The same vocabulary reaches a Flutter app as Dart — plus the cursor pager an infinite-scrolling list needs, which is the piece a mobile client otherwise rebuilds out of has_more and an offset counter (guide) — and a shell as a cobra command tree: one flag per filterable column, its operators in the usage string, so --help states what a resource accepts without a request — which is the form the guarantee has to take for a caller with no compile step, such as an agent (guide).
  • One dependency, and it is the one you already have. The engine is written on pgx and takes nothing else; a CI gate fails on anything that is not pgx or something pgx itself pulls in. That is a deliberate reversal — sqlb used to depend on the standard library alone, and ADR-0040 says what it bought: sqlb writes join a pgx.Tx your own code opened, arrays need no codec, and pgvector's binary format is reachable. Only the REST adapter pulls in Huma, and only if you use it. The generated TypeScript, the generated Dart and the generated CLI are separate toolchains and separate opt-ins; the emitters produce text, so codegen itself takes nothing.

Install

go get github.com/jryannel/sqlb

Go 1.25 or newer, and Postgres. Quickstart goes from here to a running server.

The generator is a command, and the loop is one line each way:

go install github.com/jryannel/sqlb/cmd/sqlb@latest

sqlb generate ./schema                # models, typed columns, REST bodies, manifest, clients
sqlb check ./schema                   # the CI drift gate: writes nothing, fails if stale
sqlb migrate -name adds_slug ./schema # the migration that closes the gap

The argument is the package that declares your schema, and the package says what to emit and where by exporting one function. Because the schema is Go, sqlb compiles a driver against your module to read it — see ADR-0032 for why that is forced and what it costs.

generate and check need no database. migrate works out the current schema by replaying your committed migrations into a scratch Postgres, because reading a live one tells you what the database looks like rather than whether the migrations produce it — so it needs an empty database, except for the very first migration, which diffs against nothing.

The schema DSL and code generation are both optional: sqlb.Describe[T]() layers the same capabilities over structs you already have, including stock sqlc output, without editing them.

Status

Pre-1.0, one author, no observed consumers. That is the honest starting position, and no amount of feature work substitutes for elapsed time under real traffic. Compatibility says what v0.1.0 freezes and which surfaces are expected to move.

What is proven, and re-checked on every run rather than asserted: CI applies the generated DDL to a real Postgres 18, reads it back with introspect, and requires the round trip to be a fixpoint; the query path runs through a real PgBouncer in transaction pooling, because that is the deployed topology; and the blog example is generated from its schema, so every behaviour test in it is also a test of the generator's output.

Postgres only. LISTEN/NOTIFY, jsonb aggregation and RETURNING are all load-bearing; multi-dialect support would cost the best features.

Not built yet, in the order they matter: a durable change feed, and an MCP server over the manifest. Vision has the detail.

Documentation
Start here Overview, quickstart, a worked first app, structs-first adoption
Concepts The five ideas the rest of it rests on
Schema · Queries · REST · TypeScript · Dart · CLI · Migrations One section per surface
Examples Six worked applications, and what each one proves
Reference Filter operators, column types, capabilities, codegen options, CLI, rejections
Architecture How the pieces fit, the request path, where safety lives
Decision records What was decided, why, and what would change our mind
example/recipes Eighty-odd small examples, one file per aspect — the place to look when you know what you are building and need to know how one piece is spelled
example/blog A worked schema and everything codegen emits from it
example/tasks A multi-tenant task manager: auth, migrations, a runnable server, and a generated TypeScript client, Dart client and CLI
example/fxapp The same pieces assembled by uber-go/fx: hooks arriving through a value group, and a resource that refuses to mount without them
example/computed Four ways to get a derived value out of Postgres — generated columns, trigger counters, projected expressions, views — and where sqlb's ceiling is today (ADR-0041)

Development

mise run test    # the inner loop; no Docker or Postgres needed
mise run ci      # the full gate, same as .github/workflows/ci.yml
mise tasks       # everything else

Tool versions are pinned in mise.toml, so a green run locally and a green run in CI use the same Go and the same linter. The engine's tests run against an in-memory executor rather than a database, which keeps the inner loop fast; test-pg answers what that cannot — whether the generated SQL is valid rather than merely expected — and is part of ci.

CONTRIBUTING.md has what a change is expected to carry, and where to argue with a decision record rather than around it.

License

MIT — see LICENSE.

Documentation

Overview

Package sqlb is a composable, type-parameterised SQL builder for Postgres.

A query is a plain value, not a statement executed at the point of construction. That is the whole point: predicates can be added conditionally, which is what static query generators cannot express. 

q := sqlb.Query[User]().Where(sqlb.F("age").Gte(18))
if search != "" {
    q = q.Where(sqlb.F("name").Contains(search))
}
users, err := q.OrderBy(sqlb.F("created_at").Desc()).Limit(50).All(ctx, db)

Because the query is a value, hooks and the REST layer can both mutate it before it is compiled, and the same predicate AST is produced by hand-written Go and by parsed URL filter expressions.

Values never reach the SQL text. Every user-supplied value becomes a bind parameter; only identifiers validated against the model are interpolated.

Index

Examples

Constants

This section is empty.

Variables

View Source 
var ErrAfterCommit = errors.New("sqlb: transaction committed, but an after-commit callback failed")

ErrAfterCommit reports that the transaction committed but an after-commit callback failed. The distinction matters: the write is durable and must not be retried, while the side effect did not happen and may need to be. 

if err := db.WithTx(ctx, placeOrder); err != nil {
    if errors.Is(err, sqlb.ErrAfterCommit) {
        // The order exists. Something downstream of it did not fire.
        log.Error("order placed, notification failed", "err", err)
    } else {
        return err // The order does not exist.
    }
}
View Source 
var ErrBadCursor = errors.New("sqlb: invalid cursor")

ErrBadCursor is the class of every cursor a request cannot be answered with: malformed, or issued for a different ordering. It is a sentinel so the REST layer can map the whole class to 400 without inspecting text; the wrapped error says which case it was and what to do.

View Source 
var ErrConstraint = errors.New("sqlb: constraint violated")

ErrConstraint is the class of every write a database constraint refused.

It exists so that a caller can tell its own mistake from the library's without depending on a driver: 

if errors.Is(err, sqlb.ErrConstraint) { … }

A caller needing to know *which* constraint matches a *ConstraintError with errors.As instead.

View Source 
var ErrNotFound = errors.New("sqlb: no rows matched")

ErrNotFound is returned by One when the query matches no rows. It is a sentinel so that HTTP handlers can map it to 404 without inspecting text.

View Source 
var ErrUnscoped = errors.New("sqlb: statement would affect every row; add a Where clause or call Everything to confirm")

ErrUnscoped is returned by Update and Delete when no WHERE clause was given. Rewriting or removing every row is almost never intended, so it must be requested explicitly with Everything.

View Source 
var SeqScanRowThreshold int64 = 1000

SeqScanRowThreshold is the estimated row count above which a sequential scan is reported. Small tables are legitimately scanned, so flagging every one would be noise that trains readers to ignore the output.

Functions

func AfterCommit 

func AfterCommit(ctx context.Context, fn func(context.Context) error) error

AfterCommit registers fn on the transaction carried by ctx. It is the form to use from a hook, which receives a context rather than a handle: 

sqlb.On[Order]().AfterCreate(func(ctx context.Context, o *Order) error {
    id := o.ID
    return sqlb.AfterCommit(ctx, func(ctx context.Context) error {
        return events.Publish(ctx, OrderPlaced{ID: id})
    })
})

Outside a transaction this is an error rather than an immediate call. "After commit" only means something when sqlb owns the commit; under autocommit the driver has already committed each statement and sqlb cannot say when, so a callback registered from BeforeCreate would fire before the insert and one registered from AfterCreate would fire after it. Running fn at a moment that depends on which hook happened to call it is the kind of quietly-wrong behaviour this codebase refuses elsewhere; the fix is one call, WithTx.

Example

AfterCommit runs work that must not happen if the write does not. AfterCreate and its siblings run inside the transaction, which is right for validation and wrong for anything the outside world can observe: the transaction may still abort after the hook has announced a write that then never happened. 

hooks := sqlb.On[Article]()
defer hooks.Reset()

hooks.AfterCreate(func(ctx context.Context, a *Article) error {
	// Runs inside the transaction. Returning an error here rolls the insert
	// back, so the event is registered rather than published.
	id := a.ID
	return sqlb.AfterCommit(ctx, func(context.Context) error {
		fmt.Println("published event for", id)
		return nil
	})
})

db := exampleDB()
err := db.WithTx(context.Background(), func(ctx context.Context, tx *sqlb.DB) error {
	a := Article{Title: "Hello", Status: "draft", OrgID: "acme"}
	_, err := sqlb.InsertRows(&a).One(ctx, tx)
	fmt.Println("insert returned, still inside the transaction")
	return err
})
if err != nil {
	panic(err)
}

Output:
insert returned, still inside the transaction
published event for a1
Example (Rollback)

A rollback discards the callbacks by never reaching them, which is the whole point: no event is published for a write that did not land. 

hooks := sqlb.On[Article]()
defer hooks.Reset()

hooks.AfterCreate(func(ctx context.Context, a *Article) error {
	return sqlb.AfterCommit(ctx, func(context.Context) error {
		fmt.Println("this must not print")
		return nil
	})
})

db := exampleDB()
errPaymentDeclined := errors.New("payment declined")
err := db.WithTx(context.Background(), func(ctx context.Context, tx *sqlb.DB) error {
	a := Article{Title: "Hello", Status: "draft", OrgID: "acme"}
	if _, err := sqlb.InsertRows(&a).One(ctx, tx); err != nil {
		return err
	}
	return errPaymentDeclined // something later in the unit of work fails
})

fmt.Println("WithTx:", err)
fmt.Println("last statement:", exampleLog[len(exampleLog)-1])

Output:
WithTx: payment declined
last statement: ROLLBACK

func Array 

func Array(values ...any) any

Array gathers a value list into one Postgres array parameter rather than a list of them. It is what an array-valued comparand needs: 

q.Where(sqlb.F("tags").Eq(sqlb.Array("go", "sql")))

The elements are encoded by their Go types against the column's element type, so a []string, a []int64 or a mixed list of already-coerced values all work. It is a variadic spelling of the slice and nothing more — passing a []string directly binds the same way (ADR-0040).

func Collect 

func Collect[R, T any](ctx context.Context, db Executor, b *Builder[T]) ([]R, error)

Collect runs a query and scans its rows into R rather than the model type. It is how grouped and aggregated queries are read, where the result shape is not the table shape: 

type Revenue struct {
    Status string  `db:"status"`
    Total  float64 `db:"revenue"`
}
rows, err := sqlb.Collect[Revenue](ctx, db,
    sqlb.Query[Order]().
        GroupBy(sqlb.F("status")).
        Select(sqlb.F("status"), sqlb.Sum(sqlb.F("total")).As("revenue")))

Query hooks still run, so tenant scoping applies to aggregates too.

func Diagnostics 

func Diagnostics(ds []PlanDiagnostic) string

Diagnostics renders a slice of plan diagnostics as text.

func RegisterVectorType added in v0.4.0 

func RegisterVectorType(ctx context.Context, conn *pgx.Conn) error

RegisterVectorType teaches a connection pgvector's binary format. It is the shape pgxpool.Config.AfterConnect wants, so it goes straight on: 

cfg.AfterConnect = sqlb.RegisterVectorType

The registration is per connection because the OID is per database: `vector` is an extension type, so it has no fixed number and has to be looked up wherever the connection landed.

A database without the extension installed is not an error. There is no vector type to register, so nothing is registered and the connection is returned as it is — which is what lets one AfterConnect serve a pool that reaches databases with and without it, and keeps this from being a startup failure for an application that declares no vector column.

func SetErrorClassifier 

func SetErrorClassifier(fn ErrorClassifier)

SetErrorClassifier installs a classifier that supersedes the built-in one.

It is rarely needed now. This used to be the only way to reach the constraint *name* — the field that lets an application branch on which rule was broken — because that name is a struct field on the driver's error type rather than a method, and sqlb depended on the standard library alone and would not name a driver. ADR-0040 settled which driver, so the built-in classifier reads *pgconn.PgError directly and fills every field. Users who registered a classifier for exactly that reason can delete it.

What remains is a seam for errors that reach sqlb wrapped in something errors.As cannot see through, or for an application that wants its own mapping. Call it once at startup, before serving; passing nil restores the built-in classification.

func VectorPoolConfig added in v0.4.0 

func VectorPoolConfig(dsn string) (*pgxpool.Config, error)

VectorPoolConfig is RegisterVectorType applied to a pool config, for the common case where that is the only AfterConnect a caller wants: 

cfg, err := sqlb.VectorPoolConfig(dsn)
if err != nil {
    return err
}
pool, err := pgxpool.NewWithConfig(ctx, cfg)

It replaces any AfterConnect already set rather than chaining onto it, which is why it takes a DSN rather than a config: a function that silently dropped a hook somebody else installed would be worse than one that cannot.

Types

type ArrayCol 

type ArrayCol[E any] struct {
	// contains filtered or unexported fields
}

ArrayCol is a typed reference to an array column, carrying the containment operators and none of the ordering ones. Generated model packages declare one per array column: 

var PostTags = sqlb.ArrayColumn[string]("tags")
q.Where(gen.PostTags.Has("urgent"))   // Has(42) does not compile

Like Col it does not embed Field, so the pattern and ordering operators an array cannot serve are absent rather than present and failing in Postgres.

func ArrayColumn 

func ArrayColumn[E any](name string) ArrayCol[E]

ArrayColumn declares a typed array column reference.

func (ArrayCol[E]) Column 

func (c ArrayCol[E]) Column() Column

Column returns the reference as an expression node.

func (ArrayCol[E]) Eq 

func (c ArrayCol[E]) Eq(v []E) Pred

Eq compares whole arrays, which Postgres does element by element.

func (ArrayCol[E]) Field 

func (c ArrayCol[E]) Field() Field

Field returns the untyped reference, for the operators the typed surface does not cover.

func (ArrayCol[E]) Has 

func (c ArrayCol[E]) Has(v E) Pred

Has matches rows whose array contains the element.

func (ArrayCol[E]) HasAll 

func (c ArrayCol[E]) HasAll(values ...E) Pred

HasAll matches rows whose array contains every value.

func (ArrayCol[E]) HasAny 

func (c ArrayCol[E]) HasAny(values ...E) Pred

HasAny matches rows whose array overlaps the values.

func (ArrayCol[E]) IsNull 

func (c ArrayCol[E]) IsNull() Pred

IsNull distinguishes a NULL column from an empty array, which are different values and compare differently.

func (ArrayCol[E]) Name 

func (c ArrayCol[E]) Name() string

Name returns the column name without its table qualifier.

func (ArrayCol[E]) Neq 

func (c ArrayCol[E]) Neq(v []E) Pred

func (ArrayCol[E]) NotNull 

func (c ArrayCol[E]) NotNull() Pred

func (ArrayCol[E]) Qualify 

func (c ArrayCol[E]) Qualify(table string) ArrayCol[E]

Qualify attaches a table name to the reference.

type Beginner 

type Beginner interface {
	BeginTx(ctx context.Context, opts pgx.TxOptions) (pgx.Tx, error)
}

Beginner is the subset of a pgx pool or connection that opens a transaction. It is asserted for rather than required, so Executor stays two methods and every wrapper written against it keeps working.

*pgxpool.Pool and *pgx.Conn satisfy it. A wrapper that wants WithTx to work through it — a tracer, a pool adapter — implements this alongside Executor and returns the underlying pgx.Tx.

type BetweenExpr 

type BetweenExpr struct {
	Operand Expr
	Lo, Hi  Expr
	Not     bool
}

BetweenExpr is a range test. It is its own node rather than a Binary because its right-hand side spans two operands and must not be parenthesised.

type Binary 

type Binary struct {
	Op          string
	Left, Right Expr
}

Binary is an infix operation such as `a = b` or `a AND b`.

type Builder 

type Builder[T any] struct {
	// contains filtered or unexported fields
}

Builder is a SELECT statement under construction against model T.

Its methods mutate the builder in place and return it, so a query can be assembled across branches without reassignment gymnastics and hooks can amend a query they are handed. Use Clone before sharing a partially built query between goroutines or request scopes.

func Query 

func Query[T any]() *Builder[T]

Query starts a SELECT against the table mapped by T.

Example

A query is a value. SQL renders the statement and its bind parameters without running anything, which is the inspection point: log it, diff it in a test, or paste it into EXPLAIN. 

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
)

// Article is the model these examples query. The `db` tags name the columns and
// the `sqlb` tags declare what the REST layer may do with them; both are what
// codegen writes from a schema declaration.
type Article struct {
	ID        string `db:"id" sqlb:"pk,default"`
	Title     string `db:"title" sqlb:"filter,search,sort"`
	Status    string `db:"status" sqlb:"filter,sort"`
	ViewCount int64  `db:"view_count" sqlb:"filter,sort"`
	OrgID     string `db:"org_id" sqlb:"filter"`
}

func (Article) TableName() string { return "articles" }

func main() {
	q := sqlb.Query[Article]().
		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...)
}

Output:
SELECT "articles"."id", "articles"."title", "articles"."status", "articles"."view_count", "articles"."org_id" FROM "articles" WHERE "status" = $1 ORDER BY "view_count" DESC LIMIT 10
published

func (*Builder[T]) After 

func (b *Builder[T]) After(c Cursor) *Builder[T]

After restricts the query to the rows following the cursor's position in the query's own ordering. A zero cursor is a no-op, so the first page and every page after it are the same call.

It calls Stable first, because the cursor it was handed was issued against a total order and has to be interpreted against the same one.

The predicate is kept apart from Where rather than folded into it, so that 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.

func (*Builder[T]) All 

func (b *Builder[T]) All(ctx context.Context, db Executor) ([]T, error)

All runs the query and returns every matching row.

The builder is cloned first, so query hooks amend a copy and running the same builder twice does not accumulate their predicates.

func (*Builder[T]) As 

func (b *Builder[T]) As(alias string) *Builder[T]

As aliases the table, which is required for self-joins.

func (*Builder[T]) ClearSelect 

func (b *Builder[T]) ClearSelect() *Builder[T]

ClearSelect discards the projection built so far, so the next Select starts from nothing rather than adding to it.

func (*Builder[T]) Clone 

func (b *Builder[T]) Clone() *Builder[T]

Clone returns an independent copy, so a base query can be reused as the starting point for several derived ones.

func (*Builder[T]) Count 

func (b *Builder[T]) Count(ctx context.Context, db Executor) (int64, error)

Count returns the number of matching rows, ignoring pagination. For a grouped query it counts groups.

func (*Builder[T]) CursorFor 

func (b *Builder[T]) CursorFor(row T) (Cursor, error)

CursorFor returns the cursor naming row's position in this query's ordering, for a caller handing a client the start of the next page.

The row must have been produced by this query, or by one ordered identically: the cursor is built by reading the ordering columns off it, so a row from a different projection that left one of them zero would encode a position that was never reached.

func (*Builder[T]) Distinct 

func (b *Builder[T]) Distinct() *Builder[T]

Distinct adds DISTINCT to the projection.

func (*Builder[T]) Err 

func (b *Builder[T]) Err() error

Err returns the first error recorded while building, if any. Terminal methods return it too, so checking it explicitly is optional.

func (*Builder[T]) Exists 

func (b *Builder[T]) Exists(ctx context.Context, db Executor) (bool, error)

Exists reports whether the query matches at least one row.

func (*Builder[T]) Expand 

func (b *Builder[T]) Expand(names ...string) *Builder[T]

Expand resolves the named relations inline, one LEFT JOIN each.

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

Expanding is additive and idempotent: naming the same relation twice joins it once.

func (*Builder[T]) Expanded 

func (b *Builder[T]) Expanded() []string

Expanded reports the relations this query will resolve.

func (*Builder[T]) Fail 

func (b *Builder[T]) Fail(err error) *Builder[T]

Fail records err and returns the builder, so a package outside sqlb can put a query into the same error state the builder uses internally rather than having to break the fluent chain with its own error return. Only the first error is kept, matching fail.

filter.Apply is the motivating caller: it assembles a builder from a parsed request and needs somewhere to put "this request is valid but I cannot express it".

func (*Builder[T]) First 

func (b *Builder[T]) First(ctx context.Context, db Executor) (T, error)

First returns the first matching row, or ErrNotFound. Unlike One it accepts multiple matches, so it should be paired with OrderBy to be deterministic.

func (*Builder[T]) ForShare 

func (b *Builder[T]) ForShare() *Builder[T]

ForShare takes shared row locks.

func (*Builder[T]) ForUpdate 

func (b *Builder[T]) ForUpdate() *Builder[T]

ForUpdate takes row locks for the duration of the transaction.

func (*Builder[T]) GroupBy 

func (b *Builder[T]) GroupBy(fields ...Field) *Builder[T]

GroupBy groups by the given columns.

func (*Builder[T]) GroupByExpr 

func (b *Builder[T]) GroupByExpr(exprs ...Expr) *Builder[T]

GroupByExpr groups by arbitrary expressions.

func (*Builder[T]) Having 

func (b *Builder[T]) Having(preds ...Pred) *Builder[T]

Having filters grouped rows.

func (*Builder[T]) Join 

func (b *Builder[T]) Join(table, alias string, on Pred) *Builder[T]

Join adds an inner join. Pass an empty alias to use the table name.

func (*Builder[T]) LeftJoin 

func (b *Builder[T]) LeftJoin(table, alias string, on Pred) *Builder[T]

LeftJoin adds a left outer join.

func (*Builder[T]) Limit 

func (b *Builder[T]) Limit(n int) *Builder[T]

Limit caps the number of rows returned. A negative limit is an error rather than a silent no-op, since it usually means an unchecked computed value.

func (*Builder[T]) Model 

func (b *Builder[T]) Model() *Model

Model returns the reflected model the query runs against.

func (*Builder[T]) Offset 

func (b *Builder[T]) Offset(n int) *Builder[T]

Offset skips rows.

func (*Builder[T]) One 

func (b *Builder[T]) One(ctx context.Context, db Executor) (T, error)

One runs the query and returns the single matching row. It returns ErrNotFound if nothing matched, and an error if more than one row did, since a caller asking for one row is asserting that only one exists.

func (*Builder[T]) OrderBy 

func (b *Builder[T]) OrderBy(orders ...Order) *Builder[T]

OrderBy appends ordering terms.

func (*Builder[T]) OrderColumns 

func (b *Builder[T]) OrderColumns() []string

OrderColumns names the columns the query orders by, in order, skipping any term that orders by an expression rather than a column.

filter.Apply is the motivating caller: it owns the projection and has to cover whatever the ordering ended up being, including the tiebreaker Stable appended, so that a cursor can be read off the last row.

func (*Builder[T]) Page 

func (b *Builder[T]) Page(number, size int) *Builder[T]

Page applies offset pagination. Pages are 1-based.

Example

Page is 1-based offset pagination. 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. 

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
)

// Article is the model these examples query. The `db` tags name the columns and
// the `sqlb` tags declare what the REST layer may do with them; both are what
// codegen writes from a schema declaration.
type Article struct {
	ID        string `db:"id" sqlb:"pk,default"`
	Title     string `db:"title" sqlb:"filter,search,sort"`
	Status    string `db:"status" sqlb:"filter,sort"`
	ViewCount int64  `db:"view_count" sqlb:"filter,sort"`
	OrgID     string `db:"org_id" sqlb:"filter"`
}

func (Article) TableName() string { return "articles" }

func main() {
	sql, _, _ := sqlb.Query[Article]().
		OrderBy(sqlb.F("id").Asc()).
		Page(3, 20).
		SQL()

	fmt.Println(sql)
}

Output:
SELECT "articles"."id", "articles"."title", "articles"."status", "articles"."view_count", "articles"."org_id" FROM "articles" ORDER BY "id" ASC LIMIT 20 OFFSET 40

func (*Builder[T]) SQL 

func (b *Builder[T]) SQL() (string, []any, error)

SQL compiles the query to SQL text and its bind parameters. It is the inspection point: log it, diff it in tests, or paste it into EXPLAIN.

func (*Builder[T]) Select 

func (b *Builder[T]) Select(items ...Selectable) *Builder[T]

Select appends to the projection. Without any call the query selects every mapped column of T. Use ClearSelect to start the projection over.

Example (Aggregate)

Select replaces the default projection of every mapped column, and aggregates carry an alias that the destination type's `db` tag matches. Collect scans such a result into a type other than the model. 

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
)

// Article is the model these examples query. The `db` tags name the columns and
// the `sqlb` tags declare what the REST layer may do with them; both are what
// codegen writes from a schema declaration.
type Article struct {
	ID        string `db:"id" sqlb:"pk,default"`
	Title     string `db:"title" sqlb:"filter,search,sort"`
	Status    string `db:"status" sqlb:"filter,sort"`
	ViewCount int64  `db:"view_count" sqlb:"filter,sort"`
	OrgID     string `db:"org_id" sqlb:"filter"`
}

func (Article) TableName() string { return "articles" }

func main() {
	sql, _, _ := sqlb.Query[Article]().
		Select(sqlb.F("status"), sqlb.Sum(sqlb.F("view_count")).As("views")).
		GroupBy(sqlb.F("status")).
		OrderBy(sqlb.F("status").Asc()).
		SQL()

	fmt.Println(sql)
}

Output:
SELECT "status", sum("view_count") AS "views" FROM "articles" GROUP BY "status" ORDER BY "status" ASC

func (*Builder[T]) SkipLocked 

func (b *Builder[T]) SkipLocked() *Builder[T]

SkipLocked skips rows already locked, for queue-style consumers. It has no effect without ForUpdate or ForShare.

func (*Builder[T]) Stable 

func (b *Builder[T]) Stable() *Builder[T]

Stable makes the ordering deterministic by appending the primary key, which is what lets a page boundary be named at all.

Without it `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 no cursor can distinguish the two. This is the same defect schema.Lint reports as list-without-sort; Stable is the fix rather than the warning.

It is idempotent, and a no-op when the ordering already contains the primary key — including when the caller sorted by it explicitly. The appended term takes the direction of the last existing term, so `?sort=-created_at` reads as "newest first, and newest id first among equal timestamps" rather than changing direction halfway through the ORDER BY.

A model with no primary key is left alone rather than failed: such a model can still be listed and paged by offset, and only the cursor calls — After and CursorFor — genuinely cannot work without a key, so they are where the error belongs.

func (*Builder[T]) UseDialect 

func (b *Builder[T]) UseDialect(d Dialect) *Builder[T]

UseDialect overrides the dialect for this query.

func (*Builder[T]) Where 

func (b *Builder[T]) Where(preds ...Pred) *Builder[T]

Where conjoins predicates. Zero predicates are skipped, so conditional filters need no surrounding if statement.

Example (Conditional)

Because the query is a value rather than a statement, a predicate can be added on a branch. This is the case static query generators cannot express, and the reason sqlb exists. 

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
)

// Article is the model these examples query. The `db` tags name the columns and
// the `sqlb` tags declare what the REST layer may do with them; both are what
// codegen writes from a schema declaration.
type Article struct {
	ID        string `db:"id" sqlb:"pk,default"`
	Title     string `db:"title" sqlb:"filter,search,sort"`
	Status    string `db:"status" sqlb:"filter,sort"`
	ViewCount int64  `db:"view_count" sqlb:"filter,sort"`
	OrgID     string `db:"org_id" sqlb:"filter"`
}

func (Article) TableName() string { return "articles" }

func main() {
	search := "postgres" // in a handler this came from the request

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

	sql, args, _ := q.SQL()
	fmt.Println(sql)
	fmt.Println(args...)
}

Output:
SELECT "articles"."id", "articles"."title", "articles"."status", "articles"."view_count", "articles"."org_id" FROM "articles" WHERE ("status" = $1) AND ("title" ILIKE $2)
published %postgres%

type Call 

type Call struct {
	Name     string
	Args     []Expr
	Star     bool
	Distinct bool
}

Call is a function call. Star renders `f(*)`; Distinct renders `f(DISTINCT x)`.

Name is written verbatim and is not validated, like Raw and Field.Cast. It must not come from user input. The helpers in this package — Count, Sum, Lower and the rest — supply their own names, so reaching for the struct literal is what puts a caller on this path.

type Cast 

type Cast struct {
	Inner Expr
	Type  string
}

Cast is a type cast, rendered as `expr::type`.

type Col 

type Col[T any] struct {
	// contains filtered or unexported fields
}

Col is a column reference carrying the column's Go type, so that comparands are checked at compile time. Generated model packages declare one per column: 

var PostStatus = sqlb.Typed[Status]("status")
q.Where(gen.PostStatus.Eq(StatusDraft))   // Eq(42) does not compile

It deliberately does not embed Field. Embedding would promote every operator onto every column, so Contains would be callable on an integer — which compiles, reaches the database, and fails there. The operators are re-declared here instead, and the text-only ones live on TextCol.

func Typed 

func Typed[T any](name string) Col[T]

Typed declares a typed column reference.

func (Col[T]) Asc 

func (c Col[T]) Asc() Order

Asc orders by the column ascending.

func (Col[T]) Between 

func (c Col[T]) Between(lo, hi T) Pred

func (Col[T]) Column 

func (c Col[T]) Column() Column

Column returns the reference as an expression node.

func (Col[T]) Desc 

func (c Col[T]) Desc() Order

Desc orders by the column descending.

func (Col[T]) Eq 

func (c Col[T]) Eq(v T) Pred

func (Col[T]) EqCol 

func (c Col[T]) EqCol(other Col[T]) Pred

EqCol compares two columns of the same type.

func (Col[T]) Field 

func (c Col[T]) Field() Field

Field returns the untyped reference, for the operators the typed surface does not cover.

func (Col[T]) Gt 

func (c Col[T]) Gt(v T) Pred

func (Col[T]) Gte 

func (c Col[T]) Gte(v T) Pred

func (Col[T]) IsNull 

func (c Col[T]) IsNull() Pred

IsNull matches rows where the column is NULL. It is available on every typed column, including those whose Go type is not a pointer, because nullability is a property of the column rather than of the comparand.

func (Col[T]) Lt 

func (c Col[T]) Lt(v T) Pred

func (Col[T]) Lte 

func (c Col[T]) Lte(v T) Pred

func (Col[T]) Name 

func (c Col[T]) Name() string

Name returns the column name without its table qualifier.

func (Col[T]) Neq 

func (c Col[T]) Neq(v T) Pred

func (Col[T]) NotNull 

func (c Col[T]) NotNull() Pred

func (Col[T]) NotOneOf 

func (c Col[T]) NotOneOf(values ...T) Pred

NotOneOf excludes all of the values.

func (Col[T]) OneOf 

func (c Col[T]) OneOf(values ...T) Pred

OneOf matches any of the values.

func (Col[T]) Qualify 

func (c Col[T]) Qualify(table string) Col[T]

Qualify attaches a table name to the reference.

type Collection 

type Collection[T any] struct {
	Items   []T  `json:"items"`
	HasMore bool `json:"has_more"`
}

Collection is what an expanded reverse relation arrives as: the children that fit under the relation's cap, and whether there were more.

A bare slice would be the obvious choice and it is deliberately not used. A reverse expansion is capped — an uncapped one makes a single response's size a function of data nobody bounded — and a slice cannot say it was truncated, so a caller reading fifty of an author's two hundred posts would have no way to tell. `HasMore` is the difference between a preview and a wrong answer.

The envelope is the one `rest` already returns for a collection, minus the fields a per-row subquery should not pay for: there is no total, because counting is a second aggregate on every base row and `?count=exact` on the child's own endpoint is where a caller asks for one deliberately.

ADR-0022 records the reasoning, and the trigger that would replace this with an error rather than a bare slice if the envelope proves annoying.

func (Collection[T]) Len 

func (c Collection[T]) Len() int

Len reports how many children were returned, which is at most the cap.

type Column 

type Column struct {
	Table string
	Name  string
}

Column references a table column. Table may be empty for an unqualified reference.

type ColumnInfo 

type ColumnInfo struct {
	// Name is the SQL column name.
	Name string
	// Field is the Go struct field name.
	Field string
	// Index is the reflect field index path, which may traverse embedded structs.
	Index []int
	// Type is the Go type of the struct field.
	Type reflect.Type
	// Nullable reports whether the Go field is a pointer, and so may hold NULL.
	Nullable bool
	// HasDefault reports that the column has a database default. Inserts omit
	// such a column when its Go value is the zero value, so the database fills
	// it rather than being handed a zero.
	HasDefault bool

	// Capabilities, read back from the `sqlb` struct tag that codegen writes
	// from the schema declaration.
	PrimaryKey bool
	Filterable bool
	Sortable   bool
	Searchable bool
	Expandable bool
	ReadOnly   bool
	Immutable  bool
	Hidden     bool

	// Obligations, from the same tag. Nothing on the request path reads
	// either: they are the schema's statement that this model's rows are
	// confined by something, and they are checked once, where a resource is
	// mounted.
	Scoped     bool
	SoftDelete bool
}

ColumnInfo describes one mapped column of a model.

type Compilable 

type Compilable interface {
	SQL() (string, []any, error)
}

Compilable is anything that renders to SQL: every builder and every mutation statement in this package.

type ConstraintError 

type ConstraintError struct {
	// Kind is the integrity rule that was broken.
	Kind ConstraintKind
	// Constraint is the name of the index or constraint that refused the
	// write, where the driver reports one. It is the name the schema declares,
	// so a caller can match on it rather than on prose.
	Constraint string
	// Table is the relation the constraint belongs to, where reported.
	Table string
	// Column is the column at fault, where the constraint names exactly one —
	// which for a NOT NULL violation it does, and for a composite unique index
	// it does not.
	Column string
	// Detail is the driver's own elaboration, where it offers one. It can name
	// the conflicting values, so it is a developer-facing string rather than
	// something to put in a response.
	Detail string
	// contains filtered or unexported fields
}

ConstraintError reports a write the database refused because it would have broken a constraint.

This 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 it those 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, and which every application with a unique index otherwise ends up writing.

Every field is filled from what Postgres reported. That is a change: the built-in classification used to recover the kind alone, because reading a constraint name meant naming a driver and sqlb named none. It names pgx now (ADR-0040), so a caller can branch on Constraint without registering anything.

func (*ConstraintError) Error 

func (e *ConstraintError) Error() string

Error implements error. The wrapped driver error is included, so a log line carries what the database actually said.

func (*ConstraintError) Is 

func (e *ConstraintError) Is(target error) bool

Is reports ErrConstraint, making errors.Is the cheap test for the class.

func (*ConstraintError) Unwrap 

func (e *ConstraintError) Unwrap() error

Unwrap returns the driver's error, so a caller that does depend on its driver can still reach the original.

type ConstraintKind 

type ConstraintKind string

ConstraintKind names the integrity rule a write broke. It is SQLSTATE class 23, in the terms a schema declares rather than in the terms Postgres numbers them, so that a caller switching on it reads as the schema does. 

const (
	// ConstraintUnique is a duplicate value in a unique index (23505).
	ConstraintUnique ConstraintKind = "unique"
	// ConstraintForeignKey is a reference to a row that is not there, or a
	// delete of a row still referenced (23503).
	ConstraintForeignKey ConstraintKind = "foreign_key"
	// ConstraintCheck is a CHECK expression that evaluated false (23514).
	ConstraintCheck ConstraintKind = "check"
	// ConstraintNotNull is a NULL in a column declared NOT NULL (23502).
	ConstraintNotNull ConstraintKind = "not_null"
	// ConstraintExclusion is an EXCLUDE constraint (23P01).
	ConstraintExclusion ConstraintKind = "exclusion"
)

func ConstraintKindOf 

func ConstraintKindOf(sqlstate string) (ConstraintKind, bool)

ConstraintKindOf maps a SQLSTATE code onto a ConstraintKind, reporting false for codes outside class 23. It is exported for classifiers, which need the same mapping and should not have to restate it.

type Cursor 

type Cursor string

Cursor is an opaque position in an ordered result set.

It is opaque by intent rather than by encryption: it decodes to the ordering columns and the values of the row it was taken from, and a client that decodes it learns nothing it could not read off the response. Tampering is equally uninteresting — After checks the columns and directions 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.

func (Cursor) IsZero 

func (c Cursor) IsZero() bool

IsZero reports whether the cursor is empty, which means "start at the beginning". After ignores a zero cursor, so a first request and a subsequent one can run the same code.

type DB 

type DB struct {
	// contains filtered or unexported fields
}

DB is a handle carrying an Executor and the hook registry that its queries resolve against.

It is itself an Executor, which is what makes it additive: every terminal call already takes one, so passing a *DB where a *sql.DB used to go changes nothing except which registry the hooks come from. 

db := sqlb.New(pool)
posts, err := sqlb.Query[Post]().All(ctx, db)

The reason to want one is WithTx. A unit of work needs every statement in it to run on the same connection, and hooks need to be able to tell that they are inside one — neither is expressible when the executor is threaded through call sites individually and the registry is a process global.

Go 1.27 makes the call syntax nicer without changing this object graph: the package-level generic functions gain method forms on *DB, as the README's table describes. The handle is what those methods will hang off, which is why it is worth building now rather than with the toolchain.

func New 

func New(exec Executor) *DB

New returns a handle over exec, using the process-default hook registry — so hooks registered with On[T]() apply to it, and an existing program can adopt the handle without moving its registrations.

A pgx.Tx is an Executor like any other, and passing one is how sqlb joins a transaction the application opened itself: 

tx, err := pool.Begin(ctx)
defer tx.Rollback(ctx)
if err := legacy.DebitAccount(ctx, tx, id); err != nil {
    return err
}
_, err = sqlb.InsertRows(&entry).Exec(ctx, sqlb.New(tx))
...
return tx.Commit(ctx)

The handle knows it is inside one, so InTx reports true and a WithTx on it joins rather than opening a second transaction against the same pool. What it deliberately does not do is take over the boundary: the caller opened the transaction and the caller commits it. So AfterCommit refuses here rather than accumulating callbacks nothing will ever drain — WithTx is what owns a commit, and therefore the only thing that can promise anything after one.

func TxFrom 

func TxFrom(ctx context.Context) (*DB, bool)

TxFrom returns the transaction handle a hook is running under, if any.

This is what lets a hook take part in the unit of work it was triggered by. A BeforeQuery that needs to see rows written earlier in the same transaction must read through this handle — reading through the process-wide pool would miss them, because they are not committed yet: 

sqlb.On[Post]().BeforeCreate(func(ctx context.Context, p *Post) error {
    tx, ok := sqlb.TxFrom(ctx)
    if !ok {
        return errors.New("posts must be created inside a transaction")
    }
    n, err := sqlb.Query[Post]().Where(sqlb.F("slug").Eq(p.Slug)).Count(ctx, tx)
    ...
})

func (*DB) AfterCommit 

func (d *DB) AfterCommit(fn func(context.Context) error) error

AfterCommit registers fn to run once this transaction commits, and not at all if it rolls back.

This is where publishing an event, enqueuing a job or invalidating a cache belongs. AfterCreate and its siblings run inside the transaction, which is correct for validation — an error there rolls the write back — and wrong for anything the outside world can observe, because the transaction may still abort after the hook has already told the world it succeeded.

Callbacks run in registration order after Commit returns nil, each receiving the context WithTx was called with. That context carries no transaction: there is nothing left to join, and handing back a committed one would be a trap.

A failing callback does not stop the others — these are independent side effects, and abandoning the rest leaves more inconsistency rather than less. The failures are joined under ErrAfterCommit.

func (*DB) CanBeginTx 

func (d *DB) CanBeginTx() bool

CanBeginTx reports whether WithTx would be able to open a transaction on this handle.

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

It reports true inside a transaction as well, where WithTx joins rather than begins.

func (*DB) Exec added in v0.4.0 

func (d *DB) Exec(ctx context.Context, query string, args ...any) (pgconn.CommandTag, error)

Exec satisfies Executor.

func (*DB) Hooks 

func (d *DB) Hooks() *Registry

Hooks returns the registry this handle resolves against.

func (*DB) InTx 

func (d *DB) InTx() bool

InTx reports whether this handle is inside a transaction. A BeforeQuery hook that must not run its own statements outside the caller's unit of work can check it.

func (*DB) Query added in v0.4.0 

func (d *DB) Query(ctx context.Context, query string, args ...any) (pgx.Rows, error)

Query satisfies Executor.

func (*DB) Tx 

func (d *DB) Tx() (pgx.Tx, bool)

Tx returns the underlying pgx.Tx, if this handle runs on one.

It exists so that a unit of work can be shared with code that wants more than Executor's two methods — CopyFrom, SendBatch, or a generated query set — so both sides land on one transaction without giving up WithTx's rollback and panic handling: 

err := db.WithTx(ctx, func(ctx context.Context, tx *sqlb.DB) error {
    post, err := sqlb.InsertRows(&p).One(ctx, tx)
    if err != nil {
        return err
    }
    pgTx, ok := tx.Tx()
    if !ok {
        return errors.New("expected a transaction")
    }
    return queries.New(pgTx).RecordPublication(ctx, post.ID)
})

It reports false when the executor is a pool, or a wrapper that does not expose the transaction it holds. Committing or rolling back the returned pgx.Tx directly is a mistake: WithTx owns that boundary, and doing it here leaves the after-commit callbacks unrun.

func (*DB) WithHooks 

func (d *DB) WithHooks(r *Registry) *DB

WithHooks returns a copy of the handle resolving hooks against r instead of the process default. It is how a test gets isolation without Reset, and how two tenants-worth of differing domain rules can coexist in one process.

func (*DB) WithTx 

func (d *DB) WithTx(ctx context.Context, fn func(ctx context.Context, tx *DB) error) error

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

err := db.WithTx(ctx, func(ctx context.Context, tx *sqlb.DB) error {
    order, err := sqlb.InsertRows(&o).One(ctx, tx)
    if err != nil {
        return err
    }
    _, err = sqlb.UpdateRows[Stock]().
        Set("reserved", true).
        Where(sqlb.F("sku").Eq(order.SKU)).
        Exec(ctx, tx)
    return err
})

fn receives a context carrying the transaction, which is what makes TxFrom work inside hooks — so pass that ctx onward rather than the enclosing one.

A panic in fn rolls back and is re-raised, so a transaction is never left open by one.

Nesting joins rather than nests: calling WithTx on a handle that is already in a transaction runs fn on that same transaction and leaves the commit to the outermost call. Savepoints would be the alternative and are a larger promise — partial rollback changes what "the unit of work succeeded" means, and nothing needs it yet. Joining keeps a function that opens a transaction callable from inside one.

func (*DB) WithTxOptions 

func (d *DB) WithTxOptions(ctx context.Context, opts pgx.TxOptions, fn func(ctx context.Context, tx *DB) error) error

WithTxOptions is WithTx with an explicit isolation level or read-only flag.

The options are ignored when joining an outer transaction, since isolation is a property of the transaction and the outer one has already begun. Asking for stricter isolation than the enclosing transaction provides is therefore an error rather than a silent downgrade.

type Delete 

type Delete[T any] struct {
	// contains filtered or unexported fields
}

Delete is a DELETE statement over model T.

func DeleteRows 

func DeleteRows[T any]() *Delete[T]

DeleteRows starts a DELETE.

func (*Delete[T]) Clone 

func (d *Delete[T]) Clone() *Delete[T]

Clone returns an independent copy, so a statement can be reused as the starting point for several derived ones.

func (*Delete[T]) Everything 

func (d *Delete[T]) Everything() *Delete[T]

Everything confirms an intentionally unscoped delete.

func (*Delete[T]) Exec 

func (d *Delete[T]) Exec(ctx context.Context, db Executor) (int64, error)

Exec runs the delete and returns the number of rows removed.

The statement is cloned first, for the reason Update.Exec clones: a BeforeDelete hook narrowing the statement must narrow one execution, not every later one.

func (*Delete[T]) SQL 

func (d *Delete[T]) SQL() (string, []any, error)

SQL compiles the statement without running it.

func (*Delete[T]) UseDialect 

func (d *Delete[T]) UseDialect(dl Dialect) *Delete[T]

UseDialect overrides the dialect for this statement.

func (*Delete[T]) Where 

func (d *Delete[T]) Where(preds ...Pred) *Delete[T]

Where narrows the affected rows.

type Description 

type Description[T any] struct {
	// contains filtered or unexported fields
}

Description is a set of pending metadata changes to a model.

func Describe 

func Describe[T any]() *Description[T]

Describe attaches column metadata to a model at runtime, as an alternative to the `sqlb` struct tags that codegen writes.

It exists for two cases. The first is using sqlb without any code generation at all. The second, and the more common one, is layering sqlb over structs that already exist and that you would rather not edit — the output of another generator, or a package you do not own: 

func init() {
    sqlb.Describe[Invoice]().
        Table("invoices").
        PrimaryKey("id").
        Defaulted("id", "created_at").
        Filterable("customer_id", "paid", "amount_due").
        Sortable("created_at", "amount_due").
        Hidden("internal_memo")
}

Without either tags or a description, the query 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: capabilities are opt-in, and an undescribed model exposes nothing.

Descriptions merge onto whatever the tags already said, so a partly tagged model can be completed here.

Call it during initialisation, before any query runs. It mutates the cached model in place and does not lock, because doing so would put a mutex on the read path of every query to pay for something that happens once at startup. Calling it after the first statement has been built against the model panics rather than racing. Naming a column that does not exist panics too, listing the ones that do.

func (*Description[T]) Column 

func (d *Description[T]) Column(field, column string) *Description[T]

Column overrides the column a Go field maps to, for when the derived snake_case name is not the real one and the struct cannot be given a tag.

func (*Description[T]) Defaulted 

func (d *Description[T]) Defaulted(columns ...string) *Description[T]

Defaulted marks columns that carry a database default. Inserts omit such a column when its Go value is the zero value, so the database fills it instead of being handed an empty string or a zero timestamp.

func (*Description[T]) Filterable 

func (d *Description[T]) Filterable(columns ...string) *Description[T]

Filterable allows the columns to be used in REST filter expressions.

func (*Description[T]) Hidden 

func (d *Description[T]) Hidden(columns ...string) *Description[T]

Hidden omits the columns from every REST response, and makes them unreachable from a filter, a sort or a projection.

func (*Description[T]) Immutable 

func (d *Description[T]) Immutable(columns ...string) *Description[T]

Immutable allows the columns to be set at create time only.

func (*Description[T]) Model 

func (d *Description[T]) Model() *Model

Model returns the model being described, for inspection.

func (*Description[T]) PrimaryKey 

func (d *Description[T]) PrimaryKey(column string) *Description[T]

PrimaryKey marks the key column. It implies ReadOnly and Filterable, and is what lets the REST layer address a single row.

func (*Description[T]) ReadOnly 

func (d *Description[T]) ReadOnly(columns ...string) *Description[T]

ReadOnly makes the columns unwritable through REST.

func (*Description[T]) Relation 

func (d *Description[T]) Relation(field, fkColumn string, opts ...RelationOption) *Description[T]

Relation declares an expandable reference: field is the Go field an expanded row lands in, and fkColumn is the local column joined on. 

sqlb.Describe[Task]().
    Table("tasks").
    PrimaryKey("id").
    Relation("List", "list_id")

It is the runtime form of the two-field declaration codegen writes, and it says in one call what the tags say in two: 

ListID string `db:"list_id" sqlb:"expand"`
List   *List  `db:"-"       sqlb:"expands=list_id"`

Which is the reason it needs no agreement check. Split across two tags the halves can disagree — a field expanding a column that never declared the capability — and the model build refuses that. Here there is one statement of one fact, so declaring the relation is what makes the column expandable.

The relation is named by field's json tag, falling back to the snake-cased field name, because `?expand` names the relation the way the response spells it. The field itself must not be a mapped column: an expanded row is not a value of the row it hangs off, and a field cannot be both.

The target's own model — its columns, and which of them are Hidden — comes from the Go type, and is resolved on first expansion rather than here, so two models expandable to each other do not recurse at startup.

The reverse direction

A field of type *sqlb.Collection[T] declares the other direction, and then fkColumn is a column of T rather than of this model: 

sqlb.Describe[List]().
    Table("lists").
    PrimaryKey("id").
    Relation("Tasks", "list_id", sqlb.ExpandOrder("-created_at"), sqlb.ExpandLimit(20))

The options apply to a collection only, because only a collection is capped and only a capped result has to decide which rows it keeps. Passing them to a forward relation is refused rather than ignored.

func (*Description[T]) Scoped 

func (d *Description[T]) Scoped(column string) *Description[T]

Scoped declares that the column confines the model's rows to one tenant, and so that every operation a resource exposes over it must be constrained by a hook. It is the runtime form of schema.Field.Scoped, for models sqlb did not generate, and it writes no predicate: [rest.Resource] refuses to mount a resource whose obligations no hook satisfies, and that is all it does.

func (*Description[T]) Searchable 

func (d *Description[T]) Searchable(columns ...string) *Description[T]

Searchable includes the columns in the ?search fan-out. It implies Filterable, matching the `search` tag.

func (*Description[T]) SoftDeleted 

func (d *Description[T]) SoftDeleted(column string) *Description[T]

SoftDeleted declares the column a soft-delete predicate is expected to filter — the runtime form of schema.SoftDelete's half that is not a column definition. Like Scoped it obliges a BeforeQuery hook and nothing more.

func (*Description[T]) Sortable 

func (d *Description[T]) Sortable(columns ...string) *Description[T]

Sortable allows the columns to appear in ?sort.

func (*Description[T]) Table 

func (d *Description[T]) Table(name string) *Description[T]

Table overrides the table name, which is otherwise derived from the type name or taken from a TableName method.

func (*Description[T]) Timestamps 

func (d *Description[T]) Timestamps(columns ...string) *Description[T]

Timestamps is shorthand for the common created_at / updated_at pair: database-defaulted, read-only and sortable.

type Dialect 

type Dialect interface {
	// Placeholder renders the nth bind parameter, 1-based.
	Placeholder(n int) string
	// QuoteIdent quotes an identifier.
	QuoteIdent(s string) string
	// Name identifies the dialect in diagnostics.
	Name() string
}

Dialect adapts the compiler to a specific database. Postgres is the only implementation today; the interface exists so that the AST does not have to change when a second one is added.

type ErrorClassifier 

type ErrorClassifier func(error) (ConstraintError, bool)

ErrorClassifier turns a driver's error into a ConstraintError. It reports false for anything that is not a constraint violation.

type Executor 

type Executor interface {
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
}

Executor is the subset of pgx that sqlb runs statements through. *pgxpool.Pool, *pgx.Conn and pgx.Tx all satisfy it as they stand, as does any instrumenting wrapper over them.

Taking pgx rather than database/sql is ADR-0040, and the thing it buys that an abstraction could not is that a caller's own pgx.Tx *is* an Executor: sqlb writes join a unit of work the application already opened, rather than opening a second transaction against the same pool.

type Expr 

type Expr interface {
	// contains filtered or unexported methods
}

Expr is a SQL expression node. The set of implementations is closed apart from Raw, which is the escape hatch for expressions the builder cannot model.

type Field 

type Field struct {
	// contains filtered or unexported fields
}

Field is a reference to a column, and the entry point for building predicates against it.

func F 

func F(name string) Field

F references a column. A dotted name is split into table and column, so both F("age") and F("users.age") are valid.

func (Field) Asc 

func (f Field) Asc() Order

Asc orders by the column ascending.

func (Field) Between 

func (f Field) Between(lo, hi any) Pred

Between matches a closed interval.

func (Field) Cast 

func (f Field) Cast(typ string) Expr

Cast returns the field cast to a SQL type. The type name is emitted verbatim, so it must not come from user input.

func (Field) Column 

func (f Field) Column() Column

Column returns the field as an expression node.

func (Field) Contains 

func (f Field) Contains(v string) Pred

Contains matches rows whose column contains v, case-insensitively. Wildcards in v are escaped, so it is safe for user input.

Example

Contains escapes LIKE wildcards, so a user typing "100%" searches for that literal string instead of matching every row. Use Like only for patterns your own code wrote. 

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
)

// Article is the model these examples query. The `db` tags name the columns and
// the `sqlb` tags declare what the REST layer may do with them; both are what
// codegen writes from a schema declaration.
type Article struct {
	ID        string `db:"id" sqlb:"pk,default"`
	Title     string `db:"title" sqlb:"filter,search,sort"`
	Status    string `db:"status" sqlb:"filter,sort"`
	ViewCount int64  `db:"view_count" sqlb:"filter,sort"`
	OrgID     string `db:"org_id" sqlb:"filter"`
}

func (Article) TableName() string { return "articles" }

func main() {
	sql, args, _ := sqlb.Query[Article]().
		Where(sqlb.F("title").Contains("100%")).
		SQL()

	fmt.Println(sql)
	fmt.Println(args...)
}

Output:
SELECT "articles"."id", "articles"."title", "articles"."status", "articles"."view_count", "articles"."org_id" FROM "articles" WHERE "title" ILIKE $1
%100\%%

func (Field) ContainsJSON added in v0.4.0 

func (f Field) ContainsJSON(doc string) Pred

ContainsJSON matches rows whose jsonb column contains doc — Postgres's `@>`, which asks that every key and value in doc appear in the column. It is the operator a GIN index over the column serves, and the reason a document column can be narrowed without declaring in advance which keys it holds. 

sqlb.F("metadata").ContainsJSON(`{"lang":"de"}`)
// "metadata" @> $1::jsonb

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

The parameter carries an explicit ::jsonb cast. It is not strictly needed — a driver that leaves the parameter type unspecified lets Postgres infer jsonb from the operator, which pgtest confirmed by removing the cast and watching the query still run — but it says what the statement means, and it holds for a driver that types the parameter as text rather than leaving it open.

func (Field) Desc 

func (f Field) Desc() Order

Desc orders by the column descending.

func (Field) EndsWith 

func (f Field) EndsWith(v string) Pred

EndsWith matches a case-insensitive suffix, with wildcards in v escaped.

func (Field) Eq 

func (f Field) Eq(v any) Pred

func (Field) EqField 

func (f Field) EqField(other Field) Pred

EqField compares two columns, for join and self-referential conditions.

func (Field) Gt 

func (f Field) Gt(v any) Pred

func (Field) Gte 

func (f Field) Gte(v any) Pred

func (Field) Has 

func (f Field) Has(v any) Pred

Has matches rows whose array column contains the element. The operand is a single value, not an array: `$1 = ANY(tags)`.

func (Field) HasAll 

func (f Field) HasAll(values ...any) Pred

HasAll matches rows whose array column contains every value — `tags @> $1`. An empty value set matches every row, since every array contains the empty one.

func (Field) HasAny 

func (f Field) HasAny(values ...any) Pred

HasAny matches rows whose array column overlaps the values — `tags && $1`. An empty value set matches nothing, which is what an overlap with nothing is.

func (Field) ILike 

func (f Field) ILike(pattern string) Pred

ILike is Like, case-insensitively.

func (Field) IsNull 

func (f Field) IsNull() Pred

IsNull matches rows where the column is NULL.

func (Field) Like 

func (f Field) Like(pattern string) Pred

Like matches a caller-supplied LIKE pattern. The pattern is a bind parameter, but its wildcards are not escaped: prefer Contains, StartsWith or EndsWith for values that came from a user.

func (Field) Lt 

func (f Field) Lt(v any) Pred

func (Field) Lte 

func (f Field) Lte(v any) Pred

func (Field) Name 

func (f Field) Name() string

Name returns the column name without its table qualifier.

func (Field) Neq 

func (f Field) Neq(v any) Pred

func (Field) NotBetween 

func (f Field) NotBetween(lo, hi any) Pred

NotBetween excludes a closed interval.

func (Field) NotNull 

func (f Field) NotNull() Pred

NotNull matches rows where the column is not NULL.

func (Field) NotOneOf 

func (f Field) NotOneOf(values ...any) Pred

NotOneOf is the negation of OneOf. An empty value set excludes nothing.

func (Field) OneOf 

func (f Field) OneOf(values ...any) Pred

OneOf matches rows whose column equals any of the values. An empty value set yields a predicate that matches nothing, which is what `in ()` means.

func (Field) Qualify 

func (f Field) Qualify(table string) Field

Qualify attaches a table name to the reference.

func (Field) StartsWith 

func (f Field) StartsWith(v string) Pred

StartsWith matches a case-insensitive prefix, with wildcards in v escaped.

func (Field) Table 

func (f Field) Table() string

Table returns the table qualifier, which may be empty.

type Hooks 

type Hooks[T any] struct {
	// contains filtered or unexported fields
}

Hooks are the domain-logic seams around a model's queries and mutations.

The most load-bearing one is BeforeQuery. It receives the query itself, so a single registration applies a constraint to every read of that model — including reads issued by the generated REST handlers, which is how tenant scoping stops being something each call site has to remember: 

sqlb.On[Post]().BeforeQuery(func(ctx context.Context, q *sqlb.Builder[Post]) error {
    org, ok := auth.OrgFrom(ctx)
    if !ok {
        return auth.ErrNoTenant
    }
    q.Where(sqlb.F("org_id").Eq(org))
    return nil
})

Hooks are registered once at startup, typically from an init function or main, and run in registration order. A hook returning an error aborts the operation and the error reaches the caller unwrapped.

func On 

func On[T any]() *Hooks[T]

On returns the hook set for model T in the process-default registry, creating it on first use.

func OnIn 

func OnIn[T any](r *Registry) *Hooks[T]

OnIn returns the hook set for model T in r, creating it on first use.

func (*Hooks[T]) AfterCreate 

func (h *Hooks[T]) AfterCreate(fn func(context.Context, *T) error) *Hooks[T]

AfterCreate runs on each inserted row, with database defaults populated. It runs inside the caller's transaction, so returning an error rolls the insert back.

That makes it right for validation and wrong for anything the outside world can observe — publishing an event, enqueuing a job, invalidating a cache — because the transaction may still abort after the hook has announced a write that then never happened. Register those with AfterCommit instead.

func (*Hooks[T]) AfterDelete 

func (h *Hooks[T]) AfterDelete(fn func(context.Context, int64) error) *Hooks[T]

AfterDelete receives the number of rows removed. Like AfterCreate it runs inside the transaction; side effects the outside world can see belong in AfterCommit.

func (*Hooks[T]) AfterUpdate 

func (h *Hooks[T]) AfterUpdate(fn func(context.Context, []T) error) *Hooks[T]

AfterUpdate receives the updated rows. Like AfterCreate it runs inside the transaction; side effects the outside world can see belong in AfterCommit.

func (*Hooks[T]) BeforeCreate 

func (h *Hooks[T]) BeforeCreate(fn func(context.Context, *T) error) *Hooks[T]

BeforeCreate runs on each row before insert, and may modify it: normalising an email, deriving a slug, stamping an owner.

func (*Hooks[T]) BeforeDelete 

func (h *Hooks[T]) BeforeDelete(fn func(context.Context, *Delete[T]) error) *Hooks[T]

BeforeDelete runs before a delete executes and receives the statement.

func (*Hooks[T]) BeforeQuery 

func (h *Hooks[T]) BeforeQuery(fn func(context.Context, *Builder[T]) error) *Hooks[T]

BeforeQuery runs before every SELECT against T, including those issued by generated REST handlers. The hook may add predicates, joins or ordering.

"Every SELECT against T" means every statement whose subject is T, and also every statement that reaches T as the target of another model's expansion: joining `lists` for `?expand=list` runs List's hooks, requalified onto the join alias, so a scope registered here constrains GET /lists *and* the `list` an expanded task carries.

Two things about the expansion case are worth knowing before relying on it. Only the predicates are read — the hook runs against a throwaway builder, so an ordering or a limit it sets does not follow. And a predicate that cannot be requalified onto the alias, which means RawPred or a column belonging to a table the expansion did not join, fails the query rather than being dropped. See the expansion notes in expand.go.

Example

BeforeQuery is the load-bearing hook: it receives the query itself, so one registration constrains every read of the model — including the reads the generated REST handlers issue. Multi-tenancy and soft deletes stop being something each call site has to remember. 

hooks := sqlb.On[Article]()
defer hooks.Reset()

hooks.BeforeQuery(func(_ context.Context, q *sqlb.Builder[Article]) error {
	// In a real application the tenant comes from the request context.
	q.Where(sqlb.F("org_id").Eq("acme"))
	return nil
})

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

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

// A different read, through a different entry point, is scoped too.
if _, err := sqlb.Query[Article]().Count(ctx, db); err != nil {
	panic(err)
}
fmt.Println("count:", whereClause())

Output:
list:  ("status" = $1) AND ("org_id" = $2)
count: "org_id" = $1
Example (Reject)

A hook returning an error aborts the operation, and the error reaches the caller unwrapped. This is how "no tenant in this context" becomes impossible to forget rather than merely documented. 

hooks := sqlb.On[Article]()
defer hooks.Reset()

errNoTenant := errors.New("no tenant in context")
hooks.BeforeQuery(func(ctx context.Context, q *sqlb.Builder[Article]) error {
	org, ok := ctx.Value(orgKey{}).(string)
	if !ok {
		return errNoTenant
	}
	q.Where(sqlb.F("org_id").Eq(org))
	return nil
})

db := exampleDB()

_, err := sqlb.Query[Article]().All(context.Background(), db)
fmt.Println("unscoped:", err)
fmt.Println("statements run:", len(exampleLog))

ctx := context.WithValue(context.Background(), orgKey{}, "acme")
if _, err := sqlb.Query[Article]().All(ctx, db); err != nil {
	panic(err)
}
fmt.Println("scoped:  ", whereClause())

Output:
unscoped: no tenant in context
statements run: 0
scoped:   "org_id" = $1

func (*Hooks[T]) BeforeUpdate 

func (h *Hooks[T]) BeforeUpdate(fn func(context.Context, *Update[T]) error) *Hooks[T]

BeforeUpdate runs before an update executes and receives the statement, so it can force columns (an updated_at stamp) or narrow the affected rows.

func (*Hooks[T]) Registered 

func (h *Hooks[T]) Registered() RegisteredHooks

Registered reports which kinds of hook are registered for T.

func (*Hooks[T]) Reset 

func (h *Hooks[T]) Reset()

Reset removes every registered hook for T. It exists for tests against the process-default registry, which otherwise leak registrations between cases. A test that can afford to name its own registry — NewRegistry, then DB.WithHooks — gets the same isolation without the teardown.

type Insert 

type Insert[T any] struct {
	// contains filtered or unexported fields
}

Insert is an INSERT statement over model T.

Columns carrying a database default are omitted when their Go value is the zero value, so generated identifiers and timestamps come from the database rather than being overwritten with zeroes. The statement always returns the inserted rows, so those values land back in the caller's structs.

func InsertRows 

func InsertRows[T any](rows ...*T) *Insert[T]

InsertRows starts an INSERT for one or more rows. The rows are pointers so that hooks and returned database values can be written back into them.

func (*Insert[T]) Exec 

func (i *Insert[T]) Exec(ctx context.Context, db Executor) ([]T, error)

Exec runs the insert, returning the stored rows with database defaults applied. The caller's structs are updated in place as well — except when ON CONFLICT DO NOTHING skipped a row, in which case none of them are; see writeBack for why.

func (*Insert[T]) Omit 

func (i *Insert[T]) Omit(columns ...string) *Insert[T]

Omit excludes the named columns, leaving them to their database defaults.

func (*Insert[T]) OnConflictDoNothing 

func (i *Insert[T]) OnConflictDoNothing(target ...string) *Insert[T]

OnConflictDoNothing makes a conflict on the given columns skip the row instead of failing. Skipped rows are simply absent from the result.

Because a skipped row cannot be told apart from its neighbours in what comes back, a statement that skips any row leaves every caller struct untouched — the returned slice is then the only account of what was written. See Exec.

func (*Insert[T]) OnConflictUpdate 

func (i *Insert[T]) OnConflictUpdate(target []string, update ...string) *Insert[T]

OnConflictUpdate upserts: a conflict on target updates the named columns from the proposed row. With no update columns it behaves as do-nothing.

func (*Insert[T]) One 

func (i *Insert[T]) One(ctx context.Context, db Executor) (T, error)

One inserts a single row and returns it.

func (*Insert[T]) Only 

func (i *Insert[T]) Only(columns ...string) *Insert[T]

Only restricts the insert to the named columns.

func (*Insert[T]) SQL 

func (i *Insert[T]) SQL() (string, []any, error)

SQL compiles the statement without running it.

func (*Insert[T]) UseDialect 

func (i *Insert[T]) UseDialect(d Dialect) *Insert[T]

UseDialect overrides the dialect for this statement.

type List 

type List struct {
	Items []Expr
}

List is a parenthesised expression list, used by IN and row constructors.

type Model 

type Model struct {
	Type    reflect.Type
	Table   string
	Columns []*ColumnInfo
	PK      *ColumnInfo

	// Relations are the expandable references this model declares — the
	// struct fields carrying an expanded row rather than a column of their
	// own. They are not columns: a relation field is `db:"-"`, so nothing
	// selects, inserts or updates it.
	Relations []*RelationInfo

	// Scope and Soft are the columns that declared an obligation, or nil. They
	// are resolved here so that the check at mount time is a field read rather
	// than a scan, and so that the error can name the column that asked.
	Scope *ColumnInfo
	Soft  *ColumnInfo
	// contains filtered or unexported fields
}

Model is the reflected mapping between a Go struct and a table.

func ModelOf 

func ModelOf[T any]() *Model

ModelOf returns the model for T, reflecting over it once and caching the result. It panics if T is not a struct, which is a programming error rather than a runtime condition.

func (*Model) Column 

func (m *Model) Column(name string) *ColumnInfo

Column returns the named column, or nil.

func (*Model) ColumnNames 

func (m *Model) ColumnNames() []string

ColumnNames returns every mapped column name in declaration order.

func (*Model) InUse 

func (m *Model) InUse() bool

InUse reports whether a statement has been built against this model.

func (*Model) Relation 

func (m *Model) Relation(name string) *RelationInfo

Relation returns the named relation, or nil.

func (*Model) RelationNames 

func (m *Model) RelationNames() []string

RelationNames returns every expandable relation name, in declaration order.

func (*Model) Selectable 

func (m *Model) Selectable() []*ColumnInfo

Selectable returns the columns a REST response may contain: everything not marked hidden.

type Nearness added in v0.4.0 

type Nearness struct {
	// contains filtered or unexported fields
}

Nearness is a similarity comparison between a vector column and a query vector, from which the projection, the threshold and the ordering are all derived. Build one with Near.

func Near added in v0.4.0 

func Near(f Field, v Vector) Nearness

Near compares a vector column against a query vector.

The vector binds as one parameter, cast to `vector` — which is what makes the comparison work against a column whose type Postgres knows and whose parameter type it would otherwise have to guess.

One parameter, not three. The handle names the vector in the projection, the threshold and the ordering, and all three render the same placeholder: an embedding is about twenty kilobytes and sending it once per mention would treble every search's payload for nothing.

func (Nearness) AtLeast added in v0.4.0 

func (n Nearness) AtLeast(score float64) Pred

AtLeast keeps rows whose similarity is at or above score.

Note what this does to a shortfall: with a threshold applied, a query returning fewer rows than its limit is the *normal* case, so counting rows cannot tell "nothing was similar enough" from "the search did not look hard enough". That distinction does not matter under an exact scan, where the second cannot happen. It is why ADR-0026 says an under-recall signal must count before the threshold cut rather than after — a thing to remember when the index half is built, and harmless until then.

func (Nearness) Distance added in v0.4.0 

func (n Nearness) Distance() Selection

Distance selects the raw distance, aliased `distance`, for a caller that wants the number Postgres computed rather than the one people read.

Offered because re-ranking against another system's scores needs the comparable quantity, and computing `1 - similarity` back is a rounding error nobody should have to think about.

func (Nearness) Nearest added in v0.4.0 

func (n Nearness) Nearest() Order

Nearest orders by distance ascending, which is closest first.

It is the distance that is ordered by and not the score, though the two are equivalent orderings: `ORDER BY col <=> $1` is the shape an ANN index can serve, and writing it this way means adding an index later changes the plan rather than the statement.

func (Nearness) Similarity added in v0.4.0 

func (n Nearness) Similarity() Selection

Similarity selects the score, aliased `similarity`. Larger is closer, and it is in [0, 2] for cosine — 1 for an identical direction, 0 for an orthogonal one, and above 1 only for vectors pointing away from each other.

Rename it with As if the destination struct calls it something else.

type Order 

type Order struct {
	// contains filtered or unexported fields
}

Order is one ORDER BY term.

func OrderBy 

func OrderBy(e Expr) Order

OrderBy orders by an arbitrary expression, ascending.

func OrderByDesc 

func OrderByDesc(e Expr) Order

OrderByDesc orders by an arbitrary expression, descending.

func (Order) NullsFirst 

func (o Order) NullsFirst() Order

NullsFirst places NULLs before other values.

func (Order) NullsLast 

func (o Order) NullsLast() Order

NullsLast places NULLs after other values.

type Param 

type Param struct {
	Value any
}

Param is a bind parameter. Its value is never interpolated into SQL text.

type Plan 

type Plan struct {
	SQL      string
	Args     []any
	Analyzed bool
	Raw      json.RawMessage

	// TotalCost is the planner's estimate for the whole statement, in its
	// arbitrary cost units. Useful for comparison against itself over time,
	// not as an absolute.
	TotalCost float64
	// PlanRows is the estimated row count at the root.
	PlanRows int64
	// ActualRows and ActualMS are populated only by ExplainAnalyze.
	ActualRows int64
	ActualMS   float64

	// Nodes is the plan tree flattened depth-first, which is the convenient
	// shape for scanning rather than rendering.
	Nodes []PlanNode
}

Plan is a parsed Postgres query plan.

func Explain 

func Explain(ctx context.Context, db Executor, q Compilable) (*Plan, error)

Explain asks Postgres to plan a query without running it.

It answers two questions that `SQL()` alone cannot. First, whether the statement is actually valid against the live database — a column that no longer exists, or a type that no longer matches, fails here rather than in production. Second, whether the plan is still the one you expect: an index scan that silently became a sequential scan is invisible in the SQL text and obvious in the plan.

Both make it usable as a test assertion, which is the point. A query whose plan regresses can fail a build: 

plan, err := sqlb.Explain(ctx, db, q)
if err != nil {
    t.Fatal(err)
}
if d := plan.Diagnostics(); len(d) > 0 {
    t.Errorf("query plan regressed:\n%s", sqlb.Diagnostics(d))
}

Explain does not execute the statement, so it is safe on mutations. Use ExplainAnalyze only when you mean to run it.

func ExplainAnalyze 

func ExplainAnalyze(ctx context.Context, db Executor, q Compilable) (*Plan, error)

ExplainAnalyze plans and *executes* the statement, returning real timings and row counts rather than estimates.

On an INSERT, UPDATE or DELETE this writes to the database. Run it inside a transaction you roll back, or not at all.

func (*Plan) Diagnostics 

func (p *Plan) Diagnostics() []PlanDiagnostic

Diagnostics reports 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.

func (*Plan) String 

func (p *Plan) String() string

String renders the plan as an indented tree, in the shape a reader — or an agent comparing two runs — can scan quickly.

func (*Plan) UsesIndex 

func (p *Plan) UsesIndex(name string) bool

UsesIndex reports whether the named index appears anywhere in the plan.

func (*Plan) UsesSeqScan 

func (p *Plan) UsesSeqScan(relation string) bool

UsesSeqScan reports whether any node sequentially scans the named relation. Pass an empty string to ask about any relation.

type PlanDiagnostic 

type PlanDiagnostic struct {
	Rule    string
	Node    string
	Message string
	Fix     string
}

PlanDiagnostic is an observation about a plan that is worth acting on.

func (PlanDiagnostic) String 

func (d PlanDiagnostic) String() string

type PlanNode 

type PlanNode struct {
	Depth      int
	Type       string
	Relation   string
	Index      string
	Filter     string
	TotalCost  float64
	PlanRows   int64
	ActualRows int64
	ActualMS   float64
	SortMethod string
	SortSpace  string
}

PlanNode is one step of the plan.

type Postgres 

type Postgres struct{}

Postgres is the Postgres dialect: $N placeholders and double-quoted identifiers.

func (Postgres) Name 

func (Postgres) Name() string

func (Postgres) Placeholder 

func (Postgres) Placeholder(n int) string

func (Postgres) QuoteIdent 

func (Postgres) QuoteIdent(s string) string

type Pred 

type Pred struct {
	// contains filtered or unexported fields
}

Pred is a boolean expression.

The zero Pred is a no-op: Where, And and Or all skip it. That makes conditional construction read without branches: 

q.Where(sqlb.If(minAge > 0, sqlb.F("age").Gte(minAge)))

func And 

func And(preds ...Pred) Pred

And conjoins the non-zero predicates. It returns the zero Pred if none are non-zero, and the single predicate unwrapped if exactly one is.

func If 

func If(cond bool, p Pred) Pred

If returns p when cond holds and the zero Pred otherwise.

Example

If drops the predicate when its condition does not hold, so an optional filter needs no surrounding statement. The zero Pred it returns is skipped by Where. 

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
)

// Article is the model these examples query. The `db` tags name the columns and
// the `sqlb` tags declare what the REST layer may do with them; both are what
// codegen writes from a schema declaration.
type Article struct {
	ID        string `db:"id" sqlb:"pk,default"`
	Title     string `db:"title" sqlb:"filter,search,sort"`
	Status    string `db:"status" sqlb:"filter,sort"`
	ViewCount int64  `db:"view_count" sqlb:"filter,sort"`
	OrgID     string `db:"org_id" sqlb:"filter"`
}

func (Article) TableName() string { return "articles" }

func main() {
	minViews := int64(0) // not supplied by this request

	sql, args, _ := sqlb.Query[Article]().
		Where(
			sqlb.F("status").Eq("published"),
			sqlb.If(minViews > 0, sqlb.F("view_count").Gte(minViews)),
		).
		SQL()

	fmt.Println(sql)
	fmt.Println(args...)
}

Output:
SELECT "articles"."id", "articles"."title", "articles"."status", "articles"."view_count", "articles"."org_id" FROM "articles" WHERE "status" = $1
published

func Not 

func Not(p Pred) Pred

Not negates a predicate. Negating the zero Pred yields the zero Pred rather than the always-false predicate, so an absent filter stays absent.

func Or 

func Or(preds ...Pred) Pred

Or disjoins the non-zero predicates.

Example

Or groups alternatives into a single predicate, which Where then conjoins with the rest. Values never reach the SQL text: every one becomes a bind parameter. 

package main

import (
	"fmt"

	"github.com/jryannel/sqlb"
)

// Article is the model these examples query. The `db` tags name the columns and
// the `sqlb` tags declare what the REST layer may do with them; both are what
// codegen writes from a schema declaration.
type Article struct {
	ID        string `db:"id" sqlb:"pk,default"`
	Title     string `db:"title" sqlb:"filter,search,sort"`
	Status    string `db:"status" sqlb:"filter,sort"`
	ViewCount int64  `db:"view_count" sqlb:"filter,sort"`
	OrgID     string `db:"org_id" sqlb:"filter"`
}

func (Article) TableName() string { return "articles" }

func main() {
	sql, args, _ := sqlb.Query[Article]().
		Where(
			sqlb.F("org_id").Eq("acme"),
			sqlb.Or(
				sqlb.F("status").Eq("published"),
				sqlb.F("status").Eq("review"),
			),
		).
		SQL()

	fmt.Println(sql)
	fmt.Println(args...)
}

Output:
SELECT "articles"."id", "articles"."title", "articles"."status", "articles"."view_count", "articles"."org_id" FROM "articles" WHERE ("org_id" = $1) AND (("status" = $2) OR ("status" = $3))
acme published review

func RawPred 

func RawPred(sql string, args ...any) Pred

RawPred is a predicate written as verbatim SQL with `?` placeholders.

func (Pred) Expr 

func (p Pred) Expr() Expr

Expr returns the underlying expression, or nil for the zero Pred.

func (Pred) IsZero 

func (p Pred) IsZero() bool

IsZero reports whether the predicate is empty and will be skipped.

type Raw 

type Raw struct {
	SQL  string
	Args []any
}

Raw is verbatim SQL with its own bind parameters, written as `?` placeholders which the compiler renumbers. Use it only for expressions the builder cannot model: its contents are not validated.

type RegisteredHooks 

type RegisteredHooks struct {
	BeforeQuery  bool
	BeforeCreate bool
	BeforeUpdate bool
	BeforeDelete bool
}

RegisteredHooks reports which kinds of hook a model has, one bool per kind.

It answers "did anyone write this" and deliberately not "does it do the right thing": a hook's body is a closure, and nothing here can tell a tenant predicate from a logging statement. That makes it useful for exactly one thing — refusing to serve a model whose schema declared an obligation that no registration could possibly be meeting, because there is no registration (ADR-0030).

func RegisteredFor 

func RegisteredFor[T any](exec Executor) RegisteredHooks

RegisteredFor reports which hooks are registered for T against whichever registry exec resolves to — the same resolution a query would get, so a handle carrying a scoped registry is asked about that registry rather than about the process default.

It reads the registry at the moment it is called, which is why the check it exists for belongs where a resource is mounted: hooks registered afterwards are not visible to it, and a program that mounts before it registers is a program whose first request would have run unscoped anyway.

type Registry 

type Registry struct {
	// contains filtered or unexported fields
}

Registry holds the hook sets for a set of models, keyed by type.

Most programs never name one: On[T]() reaches a process default, and registering at startup is the intended use. A registry becomes worth holding when two of them need to differ — a test that wants isolation without Reset, or a handle whose domain rules are not the process-wide ones. Attach it with DB.WithHooks.

func NewRegistry 

func NewRegistry() *Registry

NewRegistry returns an empty registry.

type RelationInfo 

type RelationInfo struct {
	// Name is what `?expand` names it, taken from the field's json tag and
	// falling back to the snake-cased field name. It is deliberately the JSON
	// name: the parameter is part of the wire format, and a client should not
	// have to know the Go field is called `List` when the payload says `list`.
	Name string
	// Field is the Go struct field the expanded row is written to.
	Field string
	// Index is the reflect path to that field.
	Index []int
	// Elem is the struct type behind the field, with any pointer removed. For
	// a Collection it is the child type, not the Collection itself.
	Elem reflect.Type
	// FK is the column joined on: a column of this model for a forward
	// relation, and a column of the target for a collection. It is resolved
	// with the target for a collection, so it is nil until Target has run.
	FK *ColumnInfo

	// Collection reports that this relation is the reverse direction — many
	// rows of the target pointing back at one row of this model.
	Collection bool
	// Order is the child column a collection is ordered by, with the target's
	// primary key appended as a tiebreaker. Empty means the primary key alone.
	// Under a cap, a non-total order does not reshuffle the result, it decides
	// which children the caller never sees — see ADR-0027 and ADR-0022.
	Order     string
	OrderDesc bool
	// Limit caps a collection. Zero means defaultExpandLimit.
	Limit int
	// contains filtered or unexported fields
}

RelationInfo describes one expandable reference.

func (*RelationInfo) Cap 

func (r *RelationInfo) Cap() int

Cap reports how many children this relation returns at most.

func (*RelationInfo) Target 

func (r *RelationInfo) Target() (*Model, error)

Target returns the model of the expanded type.

Resolved lazily and once. A cycle — two models expandable to each other — is fine as long as nothing expands both at the same moment, which the SQL could not express anyway.

type RelationOption 

type RelationOption func(*relationTag)

RelationOption adjusts a collection expansion declared through Describe. The generated form spells the same two things in the struct tag: `sqlb:"expands=list_id,order=-created_at,limit=20"`.

func ExpandLimit 

func ExpandLimit(n int) RelationOption

ExpandLimit caps how many children an expansion returns. The default is 50. Past the cap the collection reports HasMore, and the caller follows the child's own endpoint filtered by the foreign key.

func ExpandOrder 

func ExpandOrder(column string) RelationOption

ExpandOrder orders a collection's children, most significant first, with a leading "-" for descending — the spelling ?sort already uses. The target's primary key is appended as a tiebreaker either way, because under a cap a non-total order decides which children the caller never sees.

type Selectable 

type Selectable interface {
	// contains filtered or unexported methods
}

Selectable is anything that can appear in a SELECT list.

type Selection 

type Selection struct {
	// contains filtered or unexported fields
}

Selection is one item in a SELECT list: an expression and an optional alias.

func Avg 

func Avg(f Field) Selection

func Coalesce 

func Coalesce(exprs ...Expr) Selection

Coalesce returns the first non-NULL argument.

func Count 

func Count() Selection

Count is COUNT(*).

func CountDistinct 

func CountDistinct(f Field) Selection

CountDistinct is COUNT(DISTINCT col).

func CountOf 

func CountOf(f Field) Selection

CountOf is COUNT(col), which skips NULLs.

func Max 

func Max(f Field) Selection

func Min 

func Min(f Field) Selection

func RawSel 

func RawSel(sql string, args ...any) Selection

RawSel selects verbatim SQL with `?` placeholders.

func Sel 

func Sel(e Expr) Selection

Sel selects an arbitrary expression.

func Sum 

func Sum(f Field) Selection

func (Selection) Alias 

func (s Selection) Alias() string

Alias returns the selection's alias, which may be empty.

func (Selection) As 

func (s Selection) As(alias string) Selection

As names the selection. The alias must be a plain identifier; it is the name the result is scanned into.

func (Selection) Expr 

func (s Selection) Expr() Expr

Expr returns the selected expression.

type Tabler 

type Tabler interface {
	TableName() string
}

Tabler lets a model name its own table. Generated models implement it; hand-written ones can too. Without it the table name is derived from the type name, so `type User struct{}` maps to "users".

type TextCol 

type TextCol[T ~string] struct {
	Col[T]
}

TextCol is a Col over a string-like column, carrying the pattern operators that only make sense there. Generators emit it for text and varchar columns, including those with a named string type.

func TextColumn 

func TextColumn[T ~string](name string) TextCol[T]

TextColumn declares a typed text column reference.

func (TextCol[T]) Contains 

func (c TextCol[T]) Contains(v string) Pred

Contains matches rows whose column contains v, case-insensitively, with wildcards in v escaped.

func (TextCol[T]) EndsWith 

func (c TextCol[T]) EndsWith(v string) Pred

EndsWith matches a case-insensitive suffix, with wildcards in v escaped.

func (TextCol[T]) ILike 

func (c TextCol[T]) ILike(pattern string) Pred

ILike is Like, case-insensitively.

func (TextCol[T]) Like 

func (c TextCol[T]) Like(pattern string) Pred

Like matches a caller-supplied pattern, whose wildcards are not escaped.

func (TextCol[T]) StartsWith 

func (c TextCol[T]) StartsWith(v string) Pred

StartsWith matches a case-insensitive prefix, with wildcards in v escaped.

type Unary 

type Unary struct {
	Op      string
	Operand Expr
	Postfix bool
}

Unary is a prefix or postfix operation such as `NOT a` or `a IS NULL`.

type Update 

type Update[T any] struct {
	// contains filtered or unexported fields
}

Update is an UPDATE statement over model T.

func UpdateRows 

func UpdateRows[T any]() *Update[T]

UpdateRows starts an UPDATE.

func (*Update[T]) Clone 

func (u *Update[T]) Clone() *Update[T]

Clone returns an independent copy, so a statement can be reused as the starting point for several derived ones.

func (*Update[T]) Everything 

func (u *Update[T]) Everything() *Update[T]

Everything confirms an intentionally unscoped update.

func (*Update[T]) Exec 

func (u *Update[T]) Exec(ctx context.Context, db Executor) ([]T, error)

Exec runs the update and returns the updated rows.

The statement is cloned first, for the reason Builder.All clones: a BeforeUpdate hook amends what it is given, and the doc comment's own example is one that calls Set. Amending the caller's statement would make a second Exec assign updated_at twice and narrow a scoping predicate twice.

func (*Update[T]) One 

func (u *Update[T]) One(ctx context.Context, db Executor) (T, error)

One runs an update expected to touch exactly one row.

The check is on the result, so an update matching several rows has already changed all of them when the error returns. Under autocommit that is durable; inside WithTx the error rolls it back, which is the way to make "expected one" a refusal rather than a report.

func (*Update[T]) SQL 

func (u *Update[T]) SQL() (string, []any, error)

SQL compiles the statement without running it.

func (*Update[T]) Set 

func (u *Update[T]) Set(column string, value any) *Update[T]

Set assigns a value to a column.

func (*Update[T]) SetExpr 

func (u *Update[T]) SetExpr(column string, value Expr) *Update[T]

SetExpr assigns an expression, for updates computed from the current row such as a counter increment.

func (*Update[T]) UseDialect 

func (u *Update[T]) UseDialect(d Dialect) *Update[T]

UseDialect overrides the dialect for this statement.

func (*Update[T]) Where 

func (u *Update[T]) Where(preds ...Pred) *Update[T]

Where narrows the affected rows.

type Vector added in v0.4.0 

type Vector []float32

Vector is a pgvector embedding.

It is a plain []float32, so a caller's embedder output goes in without a conversion and comes back out the same way: 

chunk.Embedding = sqlb.Vector(embedder.Embed(ctx, text))

Declare the column with schema.Vector(name, dim). The dimension belongs to the column rather than to this type: a vector(1536) column refuses a 768-component value, and the check is Postgres's.

Register the codec

Values move in pgvector's binary format, which is worth about 2.7× the time and 21× the memory of the text form on a page of 1,536-component embeddings — the measurement ADR-0040 was decided on. Binary needs the type's OID, which an extension type only has once it is installed, so it is registered per connection: 

cfg, err := pgxpool.ParseConfig(dsn)
cfg.AfterConnect = sqlb.RegisterVectorType
pool, err := pgxpool.NewWithConfig(ctx, cfg)

Without that registration a Vector still works and moves as text, which is correct and slower. RegisterVectorType says what to do about a database that does not have the extension at all.

func (Vector) String added in v0.4.0 

func (v Vector) String() string

String renders the pgvector text form, `[1,2,3]`. It is what the type sends when no codec is registered, and what a %v in a log will show.

Source Files

View all Source files

Directories

Path Synopsis
cmd
sqlb command
Command sqlb keeps a project's generated code and migration history in step with its schema declaration, and reports when either has drifted from it.
 Command sqlb keeps a project's generated code and migration history in step with its schema declaration, and reports when either has drifted from it.
Package codegen renders a schema declaration into Go source.
 Package codegen renders a schema declaration into Go source.
example
blog
blog/blogschema
Package blogschema is the schema definition for the blog example: the single source of truth that an author, or an agent, edits.
 Package blogschema is the schema definition for the blog example: the single source of truth that an author, or an agent, edits.
computed
Package computed shows how to get a derived value — one the row does not store — out of Postgres through sqlb.
 Package computed shows how to get a derived value — one the row does not store — out of Postgres through sqlb.
recipes
Package recipes is a collection of small, single-topic examples: one file per aspect of sqlb, each answering a question someone actually has.
 Package recipes is a collection of small, single-topic examples: one file per aspect of sqlb, each answering a question someone actually has.
withsqlc
Package withsqlc demonstrates sqlb and sqlc over one schema.
 Package withsqlc demonstrates sqlb and sqlc over one schema.
withsqlc/gen command
Command gen renders the blog schema as the plain `schema.sql` that sqlc reads, which is the mechanical half of the sqlb/sqlc pairing story: one schema declaration, two consumers.
 Command gen renders the blog schema as the plain `schema.sql` that sqlc reads, which is the mechanical half of the sqlb/sqlc pairing story: one schema declaration, two consumers.
withsqlc/sqlcgen
Package filter compiles URL query parameters into sqlb predicates.
 Package filter compiles URL query parameters into sqlb predicates.
internal
internalschema
Package internalschema is a schema package that lives under internal/, and exists so that the command is tested against one.
 Package internalschema is a schema package that lives under internal/, and exists so that the command is tested against one.
pgfake
Package pgfake provides the pgx shapes sqlb's own tests run against, so the engine's suite stays database-free.
 Package pgfake provides the pgx shapes sqlb's own tests run against, so the engine's suite stays database-free.
Package introspect reads a Postgres schema out of pg_catalog and returns the same *schema.Registry the DSL produces.
 Package introspect reads a Postgres schema out of pg_catalog and returns the same *schema.Registry the DSL produces.
Package migrate turns a schema change into migration files for an existing migration runner.
 Package migrate turns a schema change into migration files for an existing migration runner.
Package rest mounts a schema-declared resource on a Huma API.
 Package rest mounts a schema-declared resource on a Huma API.
Package restcompat diffs the REST contract two schemas generate, and classifies each delta as breaking, additive, or neutral for a deployed client.
 Package restcompat diffs the REST contract two schemas generate, and classifies each delta as breaking, additive, or neutral for a deployed client.
Package schema is the declarative schema DSL for sqlb.
 Package schema is the declarative schema DSL for sqlb.
Package shadow builds a schema by replaying a migration history into an empty database, and reading back what the migrations actually produced.
 Package shadow builds a schema by replaying a migration history into an empty database, and reading back what the migrations actually produced.
sqlbfx module

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.