Roll back migrations

Something shipped that has to come back out, or a deploy is being unwound. Roll back to an explicit target version, prove the plan on a shadow database before touching the target, and use the down-file contract to understand what Ptah can reverse.

Prerequisites

A migration directory and a database with applied migrations. The examples use a local SQLite file with two applied migrations, 1785255952_init and 1785255953_add_posts.

If you do not have that state, build it here. The two migrations are written by hand so the versions match the ones this page rolls back:

Bash

mkdir ptah-rollback
cd ptah-rollback
mkdir migrations
cat > migrations/1785255952_init.up.sql <<'SQL'
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
SQL
cat > migrations/1785255952_init.down.sql <<'SQL'
DROP TABLE users;
SQL
cat > migrations/1785255953_add_posts.up.sql <<'SQL'
CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);
SQL
cat > migrations/1785255953_add_posts.down.sql <<'SQL'
DROP TABLE posts;
SQL

PowerShell

New-Item -ItemType Directory ptah-rollback | Out-Null
Set-Location ptah-rollback
New-Item -ItemType Directory migrations | Out-Null
@'
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);
'@ | Set-Content migrations/1785255952_init.up.sql
'DROP TABLE users;' | Set-Content migrations/1785255952_init.down.sql
@'
CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);
'@ | Set-Content migrations/1785255953_add_posts.up.sql
'DROP TABLE posts;' | Set-Content migrations/1785255953_add_posts.down.sql

Seal the directory and apply both, so there is something to roll back:

ptah migrations hash --dir ./migrations
ptah migrations up --db-url "sqlite://app.db" --migrations-dir ./migrations

Expected output on standard output:

✅ Migrations completed successfully!
Database is now at version: 1785255953

Roll back to a target version

Rollback always takes an explicit --target: the version the database should end up at. Everything applied above it is rolled back, newest first.

Check where the database is first:

ptah migrations status --db-url "sqlite://app.db" --migrations-dir ./migrations

Expected output on standard output:

Current Version: 1785255953
Total Migrations: 2
Applied Migrations: 2
Pending Migrations: 0

An interactive run states the consequences and asks for confirmation before it does anything:

Current version: 1785255953
Migrations to roll back: 1

⚠️  WARNING: Rolling back migrations can result in data loss!
This will roll back the database from version 1785255953 to version 1785255952.
The following 1 migration(s) will be rolled back: [1785255953]
Are you sure you want to continue? Type 'YES' to confirm:

Type YES to proceed. Pass --confirm where there is no terminal to answer, which is what the run below does:

Bash

ptah migrations down \
  --db-url "sqlite://app.db" \
  --migrations-dir ./migrations \
  --target 1785255952 \
  --confirm

PowerShell

ptah migrations down `
  --db-url "sqlite://app.db" `
  --migrations-dir ./migrations `
  --target 1785255952 `
  --confirm

Expected output on standard output:

✅ Migration rollback completed successfully!
Database is now at version: 1785255952

In automation there is no terminal to answer the prompt; a non-interactive run without confirmation aborts with error: read rollback confirmation: EOF. Pass --confirm to skip the prompt — only in scripts where the target and blast radius are already reviewed.

This confirmation belongs only to the native command. The Atlas-compatible ptah-compat migrate down matches Atlas by starting a real rollback without reading stdin; it does not accept --confirm.

Verify with status: the rolled-back version moves from applied back to pending.

ptah migrations status --db-url "sqlite://app.db" --migrations-dir ./migrations

Expected output on standard output:

Current Version: 1785255952
Applied Migrations: 1
Pending Migrations: 1

The version is pending again, not gone, so the same up puts it back:

ptah migrations up --db-url "sqlite://app.db" --migrations-dir ./migrations

Expected output on standard output:

✅ Migrations completed successfully!
Database is now at version: 1785255953

Before rollback starts, Ptah verifies that every selected migration has a down body that says what it does. A missing down body, or a down file with no statements that does not state it has none, aborts before Ptah changes either the schema or the revision table.

Dry run follows the same dirty-state, checksum, checkpoint, and down-body validation path as execution. It reports no migration as reverted when a preflight check fails before rollback starts.

Verify the plan on a shadow database first

Add --shadow-db to replay and verify the rollback plan on a disposable shadow database before the target is touched: the shadow database is dropped clean, migrated up to the target’s current version, and migrated down to the requested target. A failing or missing down migration aborts with the target untouched.

ptah migrations down \
  --db-url "$DATABASE_URL" \
  --migrations-dir ./migrations \
  --target 1785255952 \
  --confirm \
  --shadow-db "sqlite://shadow.db"

Expected output includes the verification pass before the real rollback:

✅ Rollback plan verified on shadow database

✅ Migration rollback completed successfully!
Database is now at version: 1785255952

The shadow database must match the target dialect and select a different live database or catalog from --db-url — use an empty scratch database of the same engine, never a real environment. Ptah first rejects equivalent URL aliases, then connects to the shadow and checks both live dialects and selected database/catalog names before resetting it. Equal live names fail closed even when the URLs use different hosts, driver overrides, or scheme spellings:

rollback verification failed: shadow database must be distinct from target database

Choosing a target

• --target <version> rolls back every applied migration above that version. Only versions recorded as applied are rolled back; a pending migration between applied ones is left alone.
• --target 0 rolls back everything, returning the database to an empty application schema.
• Rolling back “one step” means targeting the version below the current one — read it from ptah migrations status.

The down-file contract

Every version must have both an up and a down file. Ptah enforces the pair at registration, so a directory with a missing half fails every command that reads it (exit 2):

error: error registering migrations: incomplete migrations found (missing up or down files): [1]

Two consequences:

• Write real rollbacks. A generated down file reverses the generated up file, but hand-written migrations need the same care in both directions — the down half is what this page executes.
• Imported history may carry placeholder downs. When a source tool had no rollback for a migration, Import from another tool writes a placeholder down file, and a rollback through it is refused until someone decides what it should do.

A down file with no statements

A rollback runs nothing for a file holding only comments, so the objects the up file made stay. Moving the version back anyway would make the next apply run that up file again, and it would fail on those objects. So the rollback is refused, on exit 2, before any file of the plan runs, in a dry run and on --shadow-db too:

error: error running down migrations: migration 4 has a down file with no statements (0000000004_team_4.down.sql), so a rollback would change nothing and still move the version; write the rollback, or state that it has none with `-- +ptah empty_rollback` at the top of the down file

There are two ways out. Each edits a file, so run ptah migrations hash afterwards.

• Write the rollback. This is the answer when the up file changed the schema.

• State that it has none, when undoing the migration takes nothing:

  -- +ptah empty_rollback

  The rollback then runs nothing and moves the version back, and the next apply runs the up file again. So the up file has to be safe to run twice, as CREATE INDEX IF NOT EXISTS is. This is what Liquibase’s --rollback empty says, and importing a changeset that says it writes this line for you. A down file that holds the line and statements is refused too, because each contradicts the other.

The refusal is about the file. --plan derives a rollback from the dev database instead of running the down files, so it does not read this one. A migration registered with a Go function decides for itself. A generated migration whose reverse runs nothing carries the directive: one with nothing to undo, and one whose rollback leaves what the up file changed, such as a secret value the server never returns, which keeps a comment saying what stays.

A checkpoint adds one more boundary: history squashed below a checkpoint no longer has individually applied migrations to reverse, so a rollback target between the checkpoint and the squashed history fails with a clear error. Roll back to the checkpoint boundary or to 0, or restore from a backup.

What a failed rollback leaves behind

Both ptah migrations down and ptah-compat migrate down record the failure and record that it was a rollback. The revision row for the failing version is marked failed with error=<message>, applied becomes the number of down statements that completed — 0 when the first one fails, or when the dialect rolled the whole body back — and the row carries direction=down. So:

• ptah migrations status reports Status: ❌ Dirty migration state detected, names the version, and prints direction=down on the Dirty Migration: line.
• ptah migrations repair has a row to act on, and follows its direction.
• A later ptah migrations up refuses to run over the unfinished rollback instead of stacking new work on unknown state.

The document a rollback leaves

ptah migrations down --json prints the document ptah migrations up --json prints, with "command": "migrations down" and "direction": "down". The document is standard output, and everything written for a person goes to standard error, the output of a pre-flight hook included. --json needs --confirm or --dry-run: a program reading the document cannot answer the confirmation prompt, so a run with neither is refused before it connects, and its document says failed. A rollback whose second down file failed:

{
  "contract_version": 2,
  "command": "migrations down",
  "direction": "down",
  "outcome": "failed",
  "planned": [2, 1],
  "rolled_back": [2],
  "error": { "message": "error running down migrations: failed to revert migration 1: ..." },
  "failure": { "version": 1, "version_key": "1", "statement": 1, "statements": 1 },
  "migration_log": { "written": 4 },
  "status": { "…": "the history after the run, with the dirty row of version 1" }
}

planned is what the migrator selected while holding the migration lock: the applied migrations above the target, newest first. rolled_back names the selected migrations the history no longer records as applied, and applied stays absent. A migration whose rollback stopped keeps its dirty row, so it is not in rolled_back. The outcomes mean what they mean for up, read for a rollback: up-to-date says nothing above the target was applied, and applied says every selected migration was rolled back. A rollback derived with --plan does not go through the migrator, so its planned is the versions above the target the run read before it started.

Finishing an interrupted rollback

repair --resume-from <n> runs the remaining statements of the body that failed, and finishes the way that body’s success finishes:

Row	--resume-from runs	On success
direction=up	the up SQL	the revision is marked applied
direction=down	the down SQL	the revision is removed

A finished rollback means the migration is no longer applied, and Ptah records that by deleting its row — the same thing a rollback that never failed does. The statement numbers are the ones the row reports, so --resume-from accepts any statement of the down body, and a resume that fails again leaves the row pointing at the new failure so you can fix it and resume once more.

A no_transaction down runs on one pinned physical database session. When a resume skips a verified committed prefix, Ptah restores recognized session-control statements such as SET search_path on the new session before running the remaining down SQL. It refuses to guess after a prefix that created temporary objects or has unclassified session-local effects.

The complete down body is validated before the applied revision is changed to pending down. Top-level transaction-control statements are invalid in a no_transaction down because they can leave the body uncommitted while Ptah’s per-statement revision checkpoints commit independently. A validation or pinned session acquisition failure preserves the applied revision and does not run down SQL.

On PostgreSQL, Ptah also verifies every CREATE INDEX ... IF NOT EXISTS in the down body before it deletes the revision. The relation must be a usable index on the intended target table. Each create is bound to the schema selected by search_path when that statement ran, so equal raw names in different schemas remain separate checks. A failed transactional check rolls the down body back; a failed non-transactional check keeps the revision dirty with all completed down statements recorded, so repair can finalize it after the database state is corrected.

Without --resume-from, a rollback that already committed a statement is refused rather than recorded applied: recording it applied would sign off the migration over a schema whose objects the rollback already dropped. The error names every way out.

• --resume-from <n> — finish the rollback and remove the revision.
• --force — record it applied, for when you restored by hand the schema the rollback had started to undo.
• ptah migrations set --version <previous> — move the revision boundary when you finished the rollback by hand. Use --version 0 where the rolled-back migration is the oldest one in the directory and no previous version exists.

A rollback that committed nothing needs none of this: a transactional down whose body was rolled back, or a non-transactional one whose very first statement failed, still has the migration fully applied, and plain ptah migrations repair --version <version> clears it as it always has.

Note

Rows written by a Ptah that did not yet record the direction read as up rows. When the up and down bodies differ in statement count, --resume-from still refuses them rather than replay the wrong body, naming both counts; when the two bodies happen to be the same length such a row cannot be told apart, and --force is the override in either case.

ptah-compat migrate down keeps the Atlas revision-table schema and still records a failed rollback. There is no community Atlas rollback behavior to match here: on the pinned community build, atlas migrate down --help prints 'atlas migrate down' is not supported by the community version. So what a failed ptah-compat migrate down leaves behind is Ptah’s own decision, and the decision is to leave no clean applied row over a partially reverted schema.

For Atlas-format rows Ptah stores the rollback marker in operator_version, keeps applied and total as down-statement progress, and writes the error fields already present in the Atlas schema. A -- atlas:txmode none down whose third of four statements fails therefore leaves operator_version='Ptah/down', applied=2, total=4, and both error fields set. The failure is visible in ptah-compat migrate status, a later migrate apply refuses to cross the unfinished rollback — --allow-dirty included — and ptah migrations repair --revision-format atlas resumes the down body. A successful rollback still deletes the row.

ptah-compat intentionally has no repair verb. Resume its failed rollback through the native command, using the same database, migration directory, and revision schema as the compat command:

ptah migrations repair \
  --db-url "$DATABASE_URL" \
  --migrations-dir ./migrations \
  --dir-format atlas \
  --revision-format atlas \
  --version 2 \
  --resume-from 2

If the compat command used --revisions-schema, pass the same value as --migrations-schema to repair. A repair that has already run every down statement but stopped on a database-state safety check needs no --resume-from; after the database is reconciled, rerun repair to finalize the rollback without replaying SQL.

This is an intentional divergence in the direction of safety: the table stays readable by other tools, but a reader that expects a revision row to describe forward progress only may report a Ptah-recorded failed rollback differently. The same rule follows the revision table format, not the binary, so native ptah migrations down --revision-format atlas gets the same recoverable bookkeeping.

Failure modes

• The down SQL itself fails. The command exits 2 and the failing version’s revision row is marked dirty in both Ptah and Atlas table formats:

  error: error running down migrations: failed to revert migration 2: failed to execute migration SQL: sqlite: SQL execution failed: SQL logic error: no such table: not_there (1)

  ptah migrations status then reports Status: ❌ Dirty migration state detected. Fix the down file and repair the revision state — see Maintain migration history.

• Shadow verification fails. The target is untouched; the error names the migration whose down could not be replayed.

• Data does not come back. Rolling back a DROP-ing down file deletes rows by design. --pg-dump-to / --mysqldump-to on the down command write a backup first, and the confirmation prompt exists precisely because rollback is not a data-recovery tool.

Next steps

• Re-applying after a fix? Apply migrations.
• Rewriting the migration that had to come back out? Maintain migration history.
• Making rollback safety part of CI? Integrity and safety.