Pinnace — forward-only migrations for SQLite

Pinnace is a single-binary migration runner for SQLite. It reads numbered .sql files from a directory, applies the ones the database has not seen, and writes down what it did — inside the same transaction as the change itself.

What it does

A migration is a file. A run is a loop over the files that are new. There is no DSL to learn, no ORM to configure, and nothing to install into the database beyond one bookkeeping table.

Every migration runs inside BEGIN IMMEDIATE together with the insert that records it, so a run either completes or leaves the database exactly as it was. A crash mid-file cannot produce a half-applied schema, and a second process cannot start a run while the first holds the write lock.

If the process dies at any point, running it again is safe. That is the whole design.

Getting started

Requires SQLite 3.35 or newer for ALTER TABLE … DROP COLUMN; older builds work for everything else.

# one static binary, no runtime dependencies
curl -sSL https://get.pinnace.dev/install.sh | sh

# or from source
go install pinnace.dev/cmd/pinnace@latest

Point it at a database and a directory. It prints what it is about to do before it does it.

$ pinnace up --db app.db --dir migrations/

  0003_add_sessions.sql      applied   14ms
  0004_index_sessions.sql    applied    2ms
  0005_backfill_slugs.sql    applied   611ms

3 applied, 2 already current, 0 failed

Writing migrations

Files are matched by a leading number and sorted by it. The rest of the name is for humans and is never parsed.

migrations/
  0001_initial_schema.sql
  0002_add_users.sql
  0003_add_sessions.sql

Statements

The file is handed to SQLite as-is. Multiple statements are fine; so are comments, triggers and PRAGMA directives that are legal inside a transaction. Pinnace does not split, rewrite or reformat your SQL, which means an error message points at your file and not at a parser.

No down migrations

There are none, deliberately. A rollback that has never been run against production data is a guess. Write a new forward migration that undoes the change; it gets the same review, the same test run and the same transaction as everything else.

The ledger

State lives in the database, in a table called _pinnace. Nothing is cached on disk and nothing is inferred from filenames at runtime.

Columns of `_pinnace`
Column	Type	Meaning
seq	INTEGER	Leading number parsed from the filename.
name	TEXT	Filename as applied, for reporting.
digest	TEXT	SHA-256 of the file contents at apply time.
applied_at	TEXT	UTC timestamp, ISO 8601.
ms	INTEGER	Wall-clock duration of the statement batch.

Because the digest is stored, editing a migration that has already run is caught rather than silently ignored: pinnace verify exits non-zero and names the file. Keep it in CI and applied history stops drifting from the repository.

Flags

--db PATH: Database file. Created if missing, along with any parent directories.
--dir PATH: Migration directory. Defaults to ./migrations.
--to SEQ: Stop after the given sequence number instead of applying everything pending.
--dry-run: Print the plan and exit without opening a write transaction.
--timeout DURATION: How long to wait for the write lock before giving up. Defaults to 30s.
--json: Emit one JSON object per migration on stdout, for log collectors.

Limits

Pinnace speaks to SQLite and nothing else. There is no Postgres driver planned; the transactional guarantees it advertises come from having exactly one writer, which is not a property worth pretending to have elsewhere.

Statements that SQLite refuses to run inside a transaction — VACUUM, changing journal_mode — must be run yourself outside a migration. The runner reports them as errors rather than quietly committing early to accommodate them.

Reports and patches: pinnace.dev/issues. The changelog is in the repository, not on this page.

Plain SQL files, applied once, in order.