Cookbook

Task-based recipes. Each one is self-contained. Assume conn := db.New(sqlDB, db.Postgres) unless noted.

1. Search endpoint with optional filters
2. Test your SQL without a database
3. Reusable query scopes
4. Process a big table safely
5. Move money between accounts
6. Atomic counters
7. Insert or update by a unique key
8. Per-request logging and slow-query alerts
9. Per-tenant tables
10. Load a tree with a recursive CTE

1. Search endpoint with optional filters

Combine When, an allow-listed sort and pagination.

type Filter struct {
    Query  string
    Role   string
    Active *bool
    Sort   string // from the request, untrusted
    Desc   bool
    Page   int
}

var sortable = map[string]string{"created": "created_at", "name": "name"}

func SearchUsers(ctx context.Context, conn *db.DB, f Filter) (db.Paged[User], error) {
    col, ok := sortable[f.Sort]
    if !ok {
        col = "created_at" // fall back, never pass raw input
    }
    dir := "asc"
    if f.Desc {
        dir = "desc"
    }

    return db.From[User](conn).
        When(f.Query != "", func(q *db.Query[User]) {
            q.WhereAny([]string{"name", "email"}, "like", "%"+f.Query+"%")
        }).
        When(f.Role != "", func(q *db.Query[User]) { q.Where("role", f.Role) }).
        When(f.Active != nil, func(q *db.Query[User]) { q.Where("active", *f.Active) }).
        OrderBy(col, dir).
        Paginate(ctx, f.Page, 20)
}

Why it is safe: values are bound, and the sort column comes from your map. See Security.

2. Test your SQL without a database

db.Use builds SQL with no connection. Assert on the output:

func TestActiveUsersQuery(t *testing.T) {
    sql, args, err := db.Use(db.Postgres).
        Select("id").From("users").
        Where("active", true).
        OrderBy("id").
        ToSQL()

    if err != nil {
        t.Fatal(err)
    }
    want := `SELECT "id" FROM "users" WHERE "active" = $1 ORDER BY "id" ASC`
    if sql != want {
        t.Fatalf("got %s", sql)
    }
    if len(args) != 1 || args[0] != true {
        t.Fatalf("args: %v", args)
    }
}

To check the same query on several dialects, loop over db.Postgres, db.MySQL, db.MariaDB, db.SQLite.

3. Reusable query scopes

A scope is a plain function over a builder:

func NotDeleted(q *db.SelectBuilder) *db.SelectBuilder {
    return q.WhereNull("deleted_at")
}

func RecentFirst(q *db.SelectBuilder) *db.SelectBuilder {
    return q.Latest()
}

q := RecentFirst(NotDeleted(conn.Select().From("posts")))

Or use Tap to keep the chain fluent:

conn.Select().From("posts").
    Tap(func(q *db.SelectBuilder) { NotDeleted(q) }).
    Get(ctx, &posts)

4. Process a big table safely

Use ChunkByID when the callback changes the rows that match the filter:

var batch []Job
err := conn.Select().From("jobs").Where("state", "new").
    ChunkByID(ctx, 100, &batch, func() (bool, error) {
        for _, j := range batch {
            if err := run(ctx, j); err != nil {
                return false, err // stop and return the error
            }
            _, err := conn.Update("jobs").Set("state", "done").Where("id", j.ID).Exec(ctx)
            if err != nil {
                return false, err
            }
        }
        return true, nil // keep going
    })

For read-only work on huge tables, Each or db.Stream keeps one row in memory. See Pagination and Chunking.

5. Move money between accounts

func Transfer(ctx context.Context, conn *db.DB, from, to int64, amount int) error {
    return conn.Transaction(ctx, func(tx *db.DB) error {
        n, err := tx.Update("accounts").
            Decrement("balance", amount).
            Where("id", from).
            Where("balance", ">=", amount). // refuse to overdraw
            Exec(ctx)
        if err != nil {
            return err
        }
        if n == 0 {
            return errors.New("insufficient funds or unknown account")
        }
        _, err = tx.Update("accounts").Increment("balance", amount).Where("id", to).Exec(ctx)
        return err
    })
}

6. Atomic counters

Let the database do the arithmetic. No read-modify-write race:

_, err := conn.Update("posts").Increment("views", 1).Where("id", postID).Exec(ctx)
_, err = conn.Update("posts").IncrementEach(map[string]any{"views": 1, "likes": 1}).Where("id", postID).Exec(ctx)

7. Insert or update by a unique key

Prefer Upsert over UpdateOrInsert. It is one statement and race-safe with a unique index.

_, err := conn.Insert("settings").
    Values(map[string]any{"key": "theme", "value": "dark"}).
    Upsert([]string{"key"}, []string{"value"}).
    Exec(ctx)

Needs a unique index on key. PostgreSQL and SQLite require the conflict target (first argument). MySQL ignores it.

8. Per-request logging and slow-query alerts

conn := db.New(sqlDB, db.Postgres, db.WithHook(func(ctx context.Context, e db.QueryEvent) {
    attrs := []any{"query", e.SQL, "args", e.NumArgs, "took", e.Duration}
    switch {
    case e.Err != nil:
        slog.ErrorContext(ctx, "sql failed", append(attrs, "err", e.Err)...)
    case e.Duration > 500*time.Millisecond:
        slog.WarnContext(ctx, "slow query", attrs...)
    default:
        slog.DebugContext(ctx, "sql", attrs...)
    }
}))

Argument values are not logged unless you add db.WithLogArgs(). Do that only where personal data may be logged. See Hooks and Options.

9. Per-tenant tables

func tenantUsers(conn *db.DB, tenantID string) *db.Query[User] {
    // validate tenantID against your own tenant list first: it becomes part of an identifier
    return db.As[User](conn.Select().From("tenant_" + tenantID + "_users"))
}

users, err := tenantUsers(conn, "acme").Where("active", true).Get(ctx)

Quoting prevents injection, but still allow-list tenantID. See Security.

10. Load a tree with a recursive CTE

anchor := conn.Select().From("categories").Where("id", rootID)
step := conn.Select().From("categories as c").Select("c.*").Join("tree", "tree.id", "c.parent_id")

var rows []Category
err := conn.Select().From("tree").WithRecursive("tree", anchor, step).Get(ctx, &rows)

The step joins tree back to categories so each round walks one level further. See Recursive CTEs.