How to write and run database migrations

Piko's migration service applies SQL files in version order, records a checksum per applied file, and refuses to run if a previously applied file has changed on disk. This guide covers writing migrations, running them at startup, and recovering when a checksum goes wrong. See the querier reference for the API surface.

Lay out migration files

Migrations live under db/migrations/ as paired .up.sql and .down.sql files, numbered for ordering:

db/
  schema.go
  migrations/
    001_initial.up.sql
    001_initial.down.sql
    002_add_comments.up.sql
    002_add_comments.down.sql

Every file must follow the {version}_{name}.{up|down}.sql convention: a numeric version prefix, then a mandatory _ separator and a non-empty descriptive name, then .up.sql or .down.sql. The runner parses the version as an int64 from the leading digits and uses it for ordering. The descriptive name does not participate in version comparison. Use zero-padded integers (001_initial) or timestamps (20260115_initial), and pick one scheme and stick with it.

Files that do not match are silently skipped. A bare-numeric name with no _name segment (20260115143000.up.sql) and a name with a T separator before the _ (20260115T143000_init.up.sql) both fail to match, so the runner skips them.

A minimal pair:

001_initial.up.sql:

CREATE TABLE tasks (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT NOT NULL,
    completed  INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL
);

CREATE INDEX tasks_created_at_idx ON tasks (created_at DESC);

001_initial.down.sql:

DROP INDEX tasks_created_at_idx;
DROP TABLE tasks;

Writing the down file is optional but recommended. Without one, rolling that version back returns a db.NoDownMigrationError.

Embed migrations in the binary

Expose the migration folder from a package in db/. This lets the binary ship with its own schema.

db/schema.go:

// Package db provides embedded database migrations.
package db

import "embed"

//go:embed migrations/*.sql
var Migrations embed.FS

Build-time errors surface if a migration file contains malformed SQL or the embed directive is wrong.

Run migrations at startup

Apply Up before the server starts accepting traffic. A typical main.go:

package main

import (
    "context"
    "database/sql"
    "os"

    _ "modernc.org/sqlite"

    "piko.sh/piko"
    "piko.sh/piko/wdk/db"
    "piko.sh/piko/wdk/db/db_engine_sqlite"

    mydb "myapp/db"
)

func main() {
    database, err := sql.Open("sqlite", "file:./data/app.db")
    if err != nil {
        panic(err)
    }

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

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

    ssr := piko.New(
        piko.WithDatabase("primary", &db.DatabaseRegistration{
            DB:           database,
            EngineConfig: db_engine_sqlite.SQLite(),
        }),
    )

    if err := ssr.Run(piko.RunModeDev); err != nil {
        panic(err)
    }
}

migrator.Up returns the count of migrations applied during the call. Log the count, or call migrator.Status afterwards for a per-file audit trail.

Choose the right dialect

Swap db.SQLiteDialect() for your target database:

Roll forward and back

The migration service exposes four directional methods plus Status:

migrator.Down(ctx, 1)         // Undo the most recent migration.
migrator.DownTo(ctx, int64(1)) // Roll back everything applied after version 1.
migrator.UpTo(ctx, int64(2))   // Apply pending migrations up to version 2.

The integer argument to DownTo and UpTo is the numeric prefix parsed from the migration filename (001_initial.up.sql -> 1). Both rollback methods require a .down.sql for every version they touch.

Inspect status

status, err := migrator.Status(ctx)
for _, m := range status {
    fmt.Printf("%d %s applied=%v\n", m.Version, m.Filename, m.Applied)
}

MigrationStatus exposes Name, Filename, Version (int64), Applied, AppliedAt, ChecksumMatch, HasDownMigration, Dirty, and LastStatement. LastStatement is a *int. It is nil for legacy applied rows that the runner recorded before per-statement tracking landed. Otherwise it points at the index of the statement that was running when a previous attempt failed. Use this in a health probe or a CLI helper to surface migration drift without querying the database directly.

For a checksum-only audit that does not touch any pending migrations, call migrator.Validate(ctx). It walks the applied rows, recomputes each file's checksum, and returns an error if any have drifted - useful as a CI gate.

Skip the transaction wrapper

By default the executor wraps each migration in BEGIN/COMMIT. Some statements (CREATE INDEX CONCURRENTLY on PostgreSQL, VACUUM on SQLite, certain DDL on MySQL) refuse to run inside a transaction. Add -- piko.migration(no_transaction: true) above a statement to opt the migration out of transaction wrapping (CREATE INDEX CONCURRENTLY and VACUUM are auto-detected):

-- piko.migration(no_transaction: true)
CREATE INDEX CONCURRENTLY tasks_completed_idx ON tasks (completed);

The directive applies per file. Other migrations stay transactional.

Lock acquisition mode

db.NewMigrationService blocks waiting for the dialect lock by default. Pass db.WithNonBlockingLock() to fail fast when another process is mid-migration:

migrator := db.NewMigrationService(executor, reader, "migrations",
    db.WithNonBlockingLock(),
)

if _, err := migrator.Up(ctx); errors.Is(err, db.ErrLockNotAcquired) {
    // another process holds the lock - retry, exit, or surface the state
}

This is the right default for short-lived CLI invocations or sidecar containers, where waiting is worse than retrying later.

Recover from interrupted runs (dirty migrations)

If a process dies mid-migration, the row in piko_migrations keeps dirty = 1 and last_statement pointing at the index of the statement that was running when the run died. On the next call to Up, the service spots the dirty row, lines it up with the matching pending file, and resumes from the next statement. Previously committed work is not redone. No manual cleanup applies for the common case of "retry the same migration after a transient failure".

If the dirty record does not line up with the next pending file, the run fails fast with a domain-level dirty-migration error. This happens when the file is missing, when someone renumbered it, or when it sits behind other unrelated pending versions. The run refuses to apply anything else until the operator resolves the inconsistency.

Recover from a checksum mismatch

If you edit an applied migration file after it ran, Up returns db.ChecksumMismatchError. The service protects against silently losing track of schema evolution. Options to recover:

1. Revert the edit. Preferred. Keep history faithful and add a new migration for the change.
2. Add a new migration that describes the edit. Preserves auditability.
3. Update the recorded checksum manually. Only when you know the edit is byte-compatible (whitespace, comments) and you have reviewed the implications.

Never edit a migration file that has run in production without a conscious decision about which of the three paths applies.

Use errors.As to extract structured fields from typed migration errors and errors.Is(err, db.ErrLockNotAcquired) for the sentinel. See Migration errors for the full catalogue.

Lifecycle hooks

The migration service accepts MigrationServiceOption values for lifecycle hooks:

• BeforeMigrationHook: runs before each migration's SQL.
• AfterMigrationHook: runs after each migration.
• BeforeRunHook: runs before any migration.
• AfterRunHook: runs after the full run.

Use hooks to log, emit metrics, or gate on environment checks.

Apply seed data

For reference data and fixtures - country lists, default roles, demo content - use the seed service instead of smuggling INSERT statements into schema migrations. Seed files live under their own directory (typically db/seeds/) and follow the same versioned naming as migrations (001_countries.sql, 002_default_roles.sql). The runner records applied versions in a piko_seeds table so it runs each file at most once.

seedExecutor := db.NewSeedExecutor(database, db.SQLiteDialect())
seedReader := db.NewFSFileReader(mydb.Seeds)
seeds := db.NewSeedService(seedExecutor, seedReader, "seeds")

if err := seeds.Apply(ctx); err != nil {
    panic(err)
}

SeedService exposes Apply, Status, and the underlying AppliedSeed records via the same shape as MigrationStatus. When you register seeds through WithDatabase (with a SeedFS on the registration), the framework wires the service automatically and you can resolve it with db.GetSeedService(name).

Seeds should be idempotent at the SQL level. Use INSERT ... ON CONFLICT DO NOTHING or equivalent so that re-running a seed file in a recovery scenario stays safe even after the history table loses its rows.

Combine with a Lifecycle component

For services that need migrations and graceful shutdown wired together, use a lifecycle component:

type DatabaseComponent struct {
    db       *sql.DB
    migrator db.MigrationService
}

func (c *DatabaseComponent) OnStart(ctx context.Context) error {
    _, err := c.migrator.Up(ctx)
    return err
}

This pattern groups database startup with its health probe (see the health checks how-to) and cleans up on shutdown.

See also

• Querier reference.
• How to write queries.
• How to swap database engines.
• Lifecycle how-to for running migrations as a managed lifecycle component.
• Scenarios: 022 SQLite, 023 MySQL, 024 PostgreSQL.