Querier and migrations API

Piko's querier system generates type-safe Go code from plain .sql files. A project writes SQL, annotates each query with a small comment header, and runs the generator. Piko emits a Queries struct whose methods match the annotated queries exactly. The migration service runs schema changes with advisory locking, checksum verification, and dialect-specific SQL.

This page documents the generator annotations, the generated types, and the piko.sh/piko/wdk/db facade. For task recipes see the how-to guides on migrations, writing queries, and swapping database engines. Source of truth: wdk/db/ and cmd/generate_dal/.

Project layout

Every database in a project lives in its own directory under db/:

db/
  schema.go              Go package that embeds the migration FS.
  migrations/
    001_initial.up.sql   First forward migration.
    001_initial.down.sql Optional rollback.
    002_add_index.up.sql
  queries/
    *.sql                Annotated query files.
  generated/             Generator output; do not edit by hand.
    querier.go
    models.go
    prepared.go
    <name>.sql.go

The project's scaffolded generator (cmd/generator/main.go) reads every .sql file under migrations/ and queries/ and writes type-safe Go under generated/. The output is stale until the generator runs again. For the command to run it, see the how-to guides on writing queries and migrations.

Query annotations

Every query in queries/*.sql starts with a single piko.query(...) header that carries the query name plus the command kind:

-- piko.query(ListTasks, many)
SELECT id, title, completed, created_at
FROM tasks
ORDER BY created_at DESC;

Grammar

The header is a function-call-with-keyword-arguments grammar. Each directive accepts zero or more positional arguments followed by zero or more key: value keyword arguments:

-- piko.query(<positionals>, <key>: <value>, ...)
-- $N as piko.<role>(<positionals>, <key>: <value>, ...)
-- piko.<role>(<positionals>, <key>: <value>, ...)

Required arguments are positional. Optional arguments are keyword arguments. You can also name every positional, so all three of these are equivalent:

-- piko.query(GetUser, one)                     -- positional
-- piko.query(name: GetUser, command: one)      -- keyword-as-positional
-- piko.query(GetUser, command: one)            -- mixed

The three forms are interchangeable. The generator accepts a positional argument and its named keyword-as-positional equivalent for any required argument.

Top-level header: piko.query(...)

Multi-line layout works when the call gets long. The parser continues across comment-prefixed lines while the paren depth stays open:

-- piko.query(
--   SearchOrders,
--   many,
--   dynamic: runtime,
--   group_by: orders.id,
-- )

Positionals (in order):

Keyword arguments:

Command kinds

Dynamic runtime builder

dynamic: runtime emits a fluent builder instead of a fixed method, so callers compose the filter, ordering, and pagination at runtime:

rows, err := queries.SearchPosts(ctx).
    Where("category", "=", "tech").
    Where("views", ">=", 100).
    OrderBy("published", "DESC").
    Limit(20).
    Offset(40).
    All(ctx) // also .One(ctx) and .Count(ctx)

When the terminal runs, Piko validates every dynamic input against a closed allow-list. The builder rejects column names, operators, and ORDER BY directions outside their allow-list with a sentinel error. The sentinel is piko: unknown column in runtime query filter plus its operator and direction equivalents. The builder never interpolates them into SQL, and an invalid input makes the terminal return that error instead of panicking. Piko always sends bound values as driver parameters, so a value can never alter the statement's structure.

Dynamic queries can only filter and order over the data they select. The column allow-list is the query's SELECT projection. A column becomes filterable and orderable only when it appears in the SELECT list, whether directly, via *, or through a CTE that projects it. A column that exists on a FROM or JOIN table but stays absent from the projection falls outside the allow-list. The builder rejects .Where("password_hash", "LIKE", "a%") or .OrderBy("password_hash", "ASC") on a query that does not select password_hash. To filter or order by a column, select it. Computed expressions and aggregates, which have no underlying source column, are likewise not filterable or orderable as bare identifiers.

Parameter directives

Parameter placeholders in the SQL ($1/$2 for Postgres, ? for SQLite/MySQL, :name/@name for named-parameter engines) become typed Go parameters via a per-placeholder directive line:

-- piko.query(SearchOrders, many)
-- $1 as piko.param(status, optional: true)
-- $2 as piko.param(ids, type: int, kind: slice)
-- piko.sortable(order_by, [name, total, placed_at])
-- $3 as piko.param(page_size, default: 10, max: 100)
-- $4 as piko.param(skip)
SELECT id, status, total FROM orders
WHERE ($1::text IS NULL OR status = $1) AND id = ANY($2)
LIMIT $3 OFFSET $4;

Each role:

piko.param(name, type:, nullable:, default:)

The default binding. Use it when you want to give the placeholder a Go-friendly name and optionally override its inferred SQL type or nullability.

-- $1 as piko.param(user_id, type: int8, nullable: false)

piko.param qualities: optional: and kind: slice

piko.param carries keyword qualities that shape the input instead of acting as separate directives.

optional: true makes the parameter nullable. When the caller passes nil, the generator emits a runtime predicate that drops the matching WHERE clause (the $1 IS NULL OR column = $1 idiom collapses to this). It is mutually exclusive with default:.

-- $1 as piko.param(status, optional: true)
SELECT id, status FROM orders WHERE status = $1;

kind: slice expands a Go slice into IN-list placeholders at call time. The element type can be overridden with type: when inference is too loose.

-- $1 as piko.param(ids, type: int, kind: slice)
SELECT * FROM users WHERE id IN ($1);

piko.sortable(name, [columns])

A validated dynamic ORDER BY. The second positional is a bracketed list of allowed column names. Passing any value outside the list at call time fails fast. piko.sortable is a standalone header. It binds no placeholder, so it carries no $N as prefix, and the SELECT carries no ORDER BY clause. The generator appends the ORDER BY from the validated column enum.

-- piko.sortable(order_by, [name, price, created_at])
SELECT id, name, price FROM products;

You can also supply the columns list as a keyword argument (columns: [name, price, created_at]) for long lists where breaking the call across lines reads better.

LIMIT / OFFSET parameters

The generator types a piko.param placed in a LIMIT or OFFSET clause as a non-negative integer from its clause position. No special directive applies. Add the default: and max: qualities to clamp the value at call time. A default fills a zero value, and max caps it.

-- $1 as piko.param(page_size, default: 10, max: 100)
-- $2 as piko.param(page_offset)
SELECT id, name FROM products LIMIT $1 OFFSET $2;

Header directive: piko.embed

Groups projection columns into a nested Go struct. The required positional is the source table or view. The from: keyword names the FROM-clause alias whose columns belong to this embed group:

-- piko.query(GetBookWithAuthor, one)
-- piko.embed(authors, from: a)
SELECT b.id, b.title, a.id, a.name
FROM books b
INNER JOIN authors a ON a.id = b.author_id
WHERE b.id = $1;

The generated row type carries Authors AuthorsRow (or whatever name you set via as:).

Type override: piko.column

Override the inferred SQL type, the custom Go destination type, or the nullability of one output column. Valid both in query headers (per-query override) and in migration files (catalogue-wide override that propagates through every passthrough query).

Per-query override, which pins a derived column's type when the analyser cannot infer it precisely:

-- piko.query(GetUserEmailLower, one)
-- piko.column(email_lower, type: text)
SELECT id, LOWER(email) AS email_lower FROM users WHERE id = $1;

Custom Go destination type, which maps a column to a user-defined Go type (the generated file imports the package automatically):

-- piko.query(GetUserUUID, one)
-- piko.column(id, go_type: "github.com/google/uuid.UUID")
SELECT id, email FROM users WHERE id = $1;

The generated row struct uses uuid.UUID instead of the default string and the file gains import "github.com/google/uuid". Nullable columns become *uuid.UUID automatically.

Migration-level (schema-wide) override, declared once above the CREATE TABLE. Every passthrough query that selects the column inherits the override:

-- piko.column(users.id, go_type: "github.com/google/uuid.UUID")
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email TEXT NOT NULL
);

-- piko.query(GetUser, one)
SELECT id, email FROM users WHERE id = $1;   -- ID field is uuid.UUID

-- piko.query(GetUserAsString, one)
SELECT id::text AS id_str FROM users;        -- ID_str field is string

The override propagates only through direct passthrough projections. Any cast (::type, CAST(...)), function call, or arithmetic operation drops the override and falls back to the analyser's inferred type. Re-apply per-query with another piko.column line when you need the override on a derived column.

Precedence:

1. Query-level piko.column directive wins over migration-level.
2. Migration-level piko.column wins over inferred type.
3. type: and go_type: are mutually exclusive on one directive (the generator raises a Q038 diagnostic when a directive declares both).
4. nullable: works alongside either type: or go_type:.

Migration directive: piko.migration

In a migration file (*.up.sql), piko.migration(readonly: ...) overrides the read-only detection on the CREATE FUNCTION immediately following it. Useful when you have a function that is logically read-only but the SQL engine cannot tell (Postgres STABLE/IMMUTABLE vs VOLATILE, MySQL MODIFIES SQL DATA, etc.).

-- piko.migration(readonly: true)
CREATE FUNCTION calculate_score(item_id INT) RETURNS INT ...

-- piko.migration(readonly: false)
CREATE FUNCTION reset_counter() RETURNS VOID ...

piko.migration also accepts no_transaction: true above a statement that cannot run inside a transaction. The generator ignores piko.migration in a query file and ignores piko.query in a migration file (each raises a Q044 warning).

For a query's read-only flag, use the keyword on the query header instead:

-- piko.query(BareReadOnly, many, readonly: true)
SELECT id, name FROM items;

Parameter types

Positional parameters in SQL (? for SQLite/MySQL, $1/$2 for Postgres) map to Go in one of two shapes.

Single parameter (one ? or $1):

-- piko.query(DeleteTask, exec)
DELETE FROM tasks WHERE id = ?;

Generator emits:

func (q *Queries) DeleteTask(ctx context.Context, p1 int32) error

Multiple parameters (two or more):

-- piko.query(CreateTask, one)
INSERT INTO tasks (title, created_at) VALUES (?, ?) RETURNING id, title, created_at;

Generator emits a params struct:

type CreateTaskParams struct {
    P1 string
    P2 int32
}

func (q *Queries) CreateTask(ctx context.Context, params CreateTaskParams) (CreateTaskRow, error)

The types on the params struct come from the engine's type inference over the SQL expression. Override them with type hints if the inference is too loose (see the writing queries how-to).

Generated types

The generator emits four files under generated/:

querier.go

Defines the DBTX interface (satisfied by *sql.DB, *sql.Tx, and *sql.Conn) and the Queries struct:

type Queries struct { ... }

func New(db DBTX) *Queries
func NewWithReplica(writer DBTX, reader DBTX) *Queries
func (q *Queries) WithTx(tx *sql.Tx) *Queries
func (q *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error

NewWithReplica routes read-only queries to the reader and writing queries to the writer, based on the analysed query's read/write nature. A query that only reads goes to the reader. Any statement that modifies data, including a writing CTE or a call to a data-modifying function, goes to the writer. The readonly: keyword on the query header overrides the automatic detection.

models.go

Go structs for every table defined by migrations. Field names come from column names using Go capitalisation.

prepared.go

Prepared-statement cache (used automatically).

<name>.sql.go

One file per queries/<name>.sql. Each contains the typed methods for that file's queries.

Diagnostics

The generator emits diagnostics with stable Q-prefixed codes so LSP plugins, IDE integrations, and CI scripts can match against them. The generator reserves numbers and never re-uses them. It retains a gap (Q021) for forward-compatibility.

LSP plugins can use the Suggestion field on diagnostics that carry one to power one-click quick-fixes.

Migration service

The migration service applies .up.sql files in order, records a checksum per applied file, and refuses to run when the on-disk file has changed since the migrator applied it.

db.NewMigrationService(executor, fileReader, directory, opts...) MigrationService

Creates a migration service. Typical wiring:

import "piko.sh/piko/wdk/db"

executor := db.NewMigrationExecutor(database, db.SQLiteDialect())
reader := db.NewFSFileReader(embeddedFS)
migrator := db.NewMigrationService(executor, reader, "migrations")

if _, err := migrator.Up(ctx); err != nil {
    panic(err)
}

MigrationService methods

Migration service options

Pass these as variadic opts... to NewMigrationService:

The package exports the hook function types as BeforeMigrationHook, AfterMigrationHook, BeforeRunHook, and AfterRunHook.

NewFSFileReader(filesystem fs.FS) FileReaderPort

Wraps any io/fs.FS (typically a go:embed filesystem, also accepts os.DirFS) so migrations ship inside the binary. Returns the FileReaderPort interface that NewMigrationService and NewSeedService accept.

DialectConfig

One per supported database dialect. Dialects set the SQL for locking, checksum storage, and version tracking.

Migration errors

Seed service

The seed service applies SQL seed files in order, recording a checksum per applied file. Construction mirrors the migration service.

NewSeedService(executor SeedExecutorPort, fileReader FileReaderPort, directory string) SeedService

Creates a seed service that reads seed files from directory via fileReader and applies them through executor.

NewSeedExecutor(database *sql.DB, dialect DialectConfig) SeedExecutorPort

Constructs a SQL-based seed executor for the given database connection and dialect.

Database and service helpers

These accessors look up registered databases and services by the name passed to piko.WithDatabase.

Engine configs

piko.WithDatabase(name, registration) accepts a *db.DatabaseRegistration:

piko.WithDatabase("primary", &db.DatabaseRegistration{
    DB:           sqlDB,
    EngineConfig: db_engine_postgres.Postgres(),
})

Available engine configs:

Each engine config gives the generator type inference and the migrator the correct dialect. Swap the import to change engine. No other application code changes.

Parser limits

Engine parsers limit recursion and token processing per statement. Exceeding a limit returns a parse error. The engine constructor accepts limit options, and EngineConfig.Engine selects that configured instance.

config := db_engine_postgres.Postgres()
config.Engine = db_engine_postgres.NewPostgresEngine(
    db_engine_postgres.WithMaxTokensPerStatement(250_000),
)

piko.WithDatabase("primary", &db.DatabaseRegistration{
    DB:           sqlDB,
    EngineConfig: config,
})

Values below one keep the default. Derived engines accept their base engine's options.

Reserved database names

Register a SQL database under either name to persist that subsystem across restarts.

Code-generation config

The generator accepts a DatabaseConfig that tunes type inference and adds custom functions:

type DatabaseConfig = querier_dto.DatabaseConfig
type TypeOverride = querier_dto.TypeOverride
type CustomFunction = querier_dto.CustomFunctionConfig

Declare TypeOverride entries to force a SQL type to a specific Go type, or CustomFunction entries to register stored functions the engine would not otherwise know about.

See also

• How to migrations.
• How to writing queries.
• How to swapping database engines.
• Scenarios: 022 SQLite, 023 MySQL, 024 PostgreSQL, 025 DuckDB.

Integration tests: tests/integration/querier_sqlite, querier_postgres, querier_mariadb, querier_cockroachdb, querier_duckdb, querier_clickhouse, querier_timescaledb.