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(...) headerthat 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. Eachdirective accepts zero or more positional arguments followed by zeroor 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 keywordarguments. You can also name every positional, so allthree 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 positionalargument and its named keyword-as-positional equivalent for any requiredargument.

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

Multi-line layout works when the call gets long. The parsercontinues across comment-prefixed lines while the paren depth staysopen:

-- 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 thefilter, 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 theirallow-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 theminto 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 thestatement's structure.

Dynamic queries can only filter and order over the data they select. The column allow-listis the query's SELECT projection. A column becomes filterable and orderable only when itappears in the SELECT list, whether directly, via *, or through a CTE that projects it. Acolumn that exists on a FROM or JOIN table but stays absent from the projection falls outsidethe 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 filteror order by a column, select it. Computed expressions and aggregates, which have no underlyingsource column, are likewise not filterable or orderable as bare identifiers.

Parameter directives

Parameter placeholders in the SQL ($1/$2 for Postgres, ? forSQLite/MySQL, :name/@name for named-parameter engines) becometyped 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 aGo-friendly name and optionally override its inferred SQL type ornullability.

-- $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 separatedirectives.

optional: true makes the parameter nullable. When the caller passes nil, the generator emitsa runtime predicate that drops the matching WHERE clause (the $1 IS NULL OR column = $1 idiomcollapses 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 beoverridden 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 bracketedlist of allowed column names. Passing any value outside the list atcall time fails fast. piko.sortable is a standalone header. Itbinds no placeholder, so it carries no $N as prefix, and the SELECTcarries no ORDER BY clause. The generator appends the ORDER BYfrom 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 wherebreaking 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-negativeinteger 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 requiredpositional is the source table or view. The from: keyword namesthe 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 whatevername you set via as:).

Type override: piko.column

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

Per-query override, which pins a derived column's type when theanalyser 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-definedGo 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 defaultstring and the file gains import "github.com/google/uuid". Nullablecolumns become *uuid.UUID automatically.

Migration-level (schema-wide) override, declared once above theCREATE TABLE. Every passthrough query that selects the columninherits 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 passthroughprojections. Any cast (::type, CAST(...)), function call, orarithmetic operation drops the override and falls back to theanalyser's inferred type. Re-apply per-query with anotherpiko.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-onlydetection on the CREATE FUNCTION immediately following it. Useful when you have a functionthat is logically read-only but the SQL engine cannot tell (Postgres STABLE/IMMUTABLE vsVOLATILE, 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 atransaction. The generator ignores piko.migration in a query file and ignores piko.queryin 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 soLSP plugins, IDE integrations, and CI scripts can match againstthem. The generator reserves numbers and never re-uses them. It retainsa gap (Q021) for forward-compatibility.

LSP plugins can use the Suggestion field on diagnostics that carryone 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.

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.