Migration tracking contract
JauntyQ reads your migrations but never runs them. You apply them with your own runner (a script, Flyway, DbUp, anything). This page is what that runner has to do so that it and JauntyQ agree about which migrations exist, in what order, and which have been applied.
Why JauntyQ does not apply migrations
Every other JauntyQ command that touches a database only reads it (schema pull, schema verify). Applying a migration would be the first thing JauntyQ
does that changes a database, and a mistake there cannot be undone by running
it again. Plenty of tools already apply migrations well. What none of them do is
what JauntyQ does with a migration before it runs: check every query in your
project against the schema it produces, and report what it would break. JauntyQ
keeps to that half. Applying is left to you, and this contract covers the part
hand-written runners most often get wrong.
What JauntyQ does with db/migrations/
- Only pending migrations live there. At build time the generator applies
every
.sqlfile under amigrationsfolder, in memory, on top of the committed schema snapshot, and checks your queries against the result. It does not know which files your database has already run. Once a migration is deployed and you re-pull the snapshot, the snapshot already contains its changes, so move the file out ofdb/migrations/(see troubleshooting). - Order is by file name. Files are sorted by their file name alone (the
folder they sit in does not count). Runs of digits compare as numbers, so
V2__add_col.sqlcomes beforeV10__rename.sql. Everything else compares character by character, case-sensitively.0002_x.sql,002_x.sqland2_x.sqlare the same number; the one with more leading zeros sorts later.
What your runner must do
- Apply in JauntyQ's order. Sort the same way, or name files so every sort
agrees: zero-padded numbers (
0001_,0002_) or a fixed-width timestamp (20260930_1200_). - Record each migration in a
schema_migrationstable, one row per applied file, keyed by its file name. - Write that row in the same transaction as the migration. Begin a
transaction, run the migration, insert its
schema_migrationsrow, commit. If the row is written after the commit, a crash between the two leaves a migration applied but unrecorded, and the next run applies it twice. If it is written before, a failed migration is recorded as applied. - Use a plain
INSERT, not an upsert, so two runners applying the same file at once make the second one fail and roll back instead of silently succeeding.
The table
Two columns. version is the file name exactly as it appears in
db/migrations/, including .sql.
dbmate and golang-migrate also name their table schema_migrations, but they
store a version number rather than the file name, so they do not meet this
contract and jauntyq migrate status refuses to read their table.
-- PostgreSQL
CREATE TABLE schema_migrations (
version text PRIMARY KEY,
applied_at timestamptz NOT NULL DEFAULT now()
);
-- SQL Server
CREATE TABLE schema_migrations (
version nvarchar(255) NOT NULL PRIMARY KEY,
applied_at datetime2 NOT NULL DEFAULT sysutcdatetime()
);
-- MySQL
CREATE TABLE schema_migrations (
version varchar(255) NOT NULL PRIMARY KEY,
applied_at datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);
-- SQLite
CREATE TABLE schema_migrations (
version TEXT NOT NULL PRIMARY KEY,
applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
Extra columns (who applied it, a checksum, run duration) are fine. Keep
version and applied_at as named.
Where a transaction cannot help
- MySQL commits on every DDL statement, so a migration with several
ALTERs that fails part-way leaves the earlier ones applied, and no runner can roll them back. Keep MySQL migrations to one schema change per file, and write the tracking row right after it. - Some statements refuse to run inside a transaction, for example
PostgreSQL's
CREATE INDEX CONCURRENTLY. Put each such statement in a file of its own, run it outside a transaction, and record it only after it succeeds.
Checking a database against this contract
jauntyq migrate status reads the schema_migrations table and compares it
with db/migrations/, using the table shape and file order above. It lists the
pending files in apply order and exits 2 on drift: a file that is recorded as
applied but still in the folder, or a pending file that sorts before the latest
applied one. --fail-on pending also fails on any pending file, for a deploy
gate. It only reads; see the CLI reference.