Skip to content
PtahDocs
latest

Graphic preview

100%
Page type: how-to

Roll back migrations

Roll a database back to an explicit target version, verify the plan on a shadow database first, and understand the down-file contract.

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.

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:

Terminal window
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

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

Terminal window
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

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:

Terminal window
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 changes the target:

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:

With --plan, the run derives the rollback before it asks and prints it above the prompt. Deriving it resets the dev database, replays the directory into it up to the target version and empties it again, so a declined prompt still costs that replay, and a docker:// dev URL starts its container before the prompt too. A plan the run refuses is refused before the prompt.

The run holds no lock while it waits for your answer, so another runner can apply or roll back a migration in that time. Once you answer, the run takes the migration lock and reads the applied migrations again. When the ones above the target are still the ones the plan was derived for, it runs the pre-flight hooks and then the statements it printed; the plan is not derived again. When they changed, it refuses before any statement runs and asks you to run the command again; see Failure modes. A change made outside the migrations, such as a table another tool creates, leaves the applied migrations as they were, so the printed statements run. On a dialect that takes no migration lock, such as SQLite, the applied migrations are still read again before the statements run, and nothing stops another runner from changing them while they run.

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

Terminal window
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.

Terminal window
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:

Terminal window
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

Section titled “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.

Terminal window
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
  • --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. With --plan, the derived statements bring the database to the directory as it stands up to the target, so a pending migration at or below the target is created and recorded as applied.
  • A version no migration file names is accepted. The run rolls back every applied migration above it and records the newest migration below it as the current version, with --plan or without, when no pending migration sits below it. A pending one is handled as the item above says.
  • --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.

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 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. The derived rollback leaves out the objects the exclude list of ptah.yaml names, so a table another tool created is not dropped as one the target version lacks. A selector in that list that names no object protects nothing, so the rollback refuses it as schema apply does; see Failure modes. 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.

golang-migrate opens no transaction around a file. --runner golang-migrate, or migration.runner: golang-migrate in ptah.yaml, rolls a down file back the way ptah migrations up applies an up file under the same runner. The flag wins over PTAH_RUNNER, and either wins over ptah.yaml, as for migrations up and migrations lint. A value that names no runner is refused before the migration directory is read.

  • On PostgreSQL, a down file that opens and closes its own transactions runs on one session, outside any transaction of the migrator’s, in the transactions PostgreSQL runs it in when it is sent as one query, as golang-migrate sends it. The revision is removed after the file’s last statement.
  • On PostgreSQL, a down file of one statement runs without a transaction, so a file whose only statement is DROP INDEX CONCURRENTLY rolls back.
  • A form the migrator does not run is refused before any migration is rolled back: ROLLBACK, a block the file leaves open, and a block in a file whose -- atlas:txmode file asks for a transaction around it. The schema and every revision stay as they were.
  • On every other engine a down file keeps the transaction around it, so its transaction control is refused as under the default runner.
  • --shadow-db verifies the rollback by running each down file the same way, and --plan builds the target version there by running each up file the way migrations up runs it. --dry-run prints the rollback and leaves the schema and the revisions as they were.

Transactions in a golang-migrate directory describes the transactions a file runs in. ptah migrations lint --runner golang-migrate judges a down file by the same reading (TX201).

A failed rollback rolls back the transaction it interrupted and records, on a dirty revision, the statements that stayed committed. Here a down file differs from an up file of the same directory. A retry of a failed up file with --allow-dirty resumes after its committed statements; a rollback never resumes, so the dirty revision stops the next one, and the way out depends on what committed. ptah migrations status names it. ptah migrations repair takes the runner the same way, --runner, PTAH_RUNNER or migration.runner, and reads the file with its transactions under golang-migrate. Measured on PostgreSQL 17:

down file after the failure the way out
BEGIN; DROP TABLE a; INSERT INTO missing ...; COMMIT; a is still there, applied=0 ptah migrations repair --version <version> records the migration applied, and the next rollback runs the whole file
DROP TABLE a; INSERT INTO missing ...; BEGIN; DROP TABLE b; COMMIT; a and b are still there, applied=0: the statements before BEGIN run in its transaction the same
BEGIN; DROP TABLE a; COMMIT; INSERT INTO missing ...; a is gone, applied=3 finish the rollback by hand, then ptah migrations set --version <previous>; or restore a, then ptah migrations repair --version <version> --force --runner golang-migrate
BEGIN; DROP TABLE a; COMMIT; DROP TABLE b;, and the revision write after the last statement fails a and b are gone, applied=4, every statement ptah migrations repair --version <version> --runner golang-migrate removes the revision

After a transaction of the file committed, a plain repair refuses to record the migration applied, and --resume-from is refused, because a resume would run the rest of the file without the transactions it opens. Both refusals name the two endings in the table. --force records the migration applied, for the operator who restored what the rollback reverted. A failure after the file’s last statement committed, such as a revision write that did not reach the database, records every statement applied and keeps the revision. A repair without --resume-from removes it, as a rollback that recorded its end does.

Without the golang-migrate runner, a repair that has to read such a file reads it one statement at a time, refuses its BEGIN, and says to repair with --runner golang-migrate. On every other engine the runner refuses the BEGIN too, so neither the refusal nor the hint names it there.

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.

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.
  • A later ptah migrations down refuses it too, whatever its target, and names the command that ends the row. The dirty row is not applied, so the version already reads as the target of the failed rollback, and a rollback that read only the version would report itself done.

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" }
}

--server-details adds what the server said about a down statement or a commit that failed, as it does for up: after the error line, and as server_details in the document. What the server said describes it, and what it may disclose.

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 names in planned the versions above the target it was derived for, newest first. The run reads them again under the migration lock before any statement runs. A plan the run refuses or cannot derive stops it before it starts, as a failed shadow verification does: the document carries the error, and no planned or status. A run that finds the applied migrations changed once it holds the lock has started, so its document carries planned, the error and the status it found.

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.

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:

Terminal window
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.

  • 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.

  • A previous rollback left a dirty revision. The command exits 2 before it plans or prompts, whatever the target, and names the repair that ends the row:

    error: error running down migrations: migration 2 is dirty: state=failed applied=0/1 error="..." error_stmt="DROP TABLE not_there"
    No down statement of this rollback committed, so the migration is still applied. Run 'ptah migrations repair --version 2' to record it applied, then roll it back again.
  • Shadow verification fails. The target is untouched; the error names the migration whose down could not be replayed.

  • An exclude selector names no object. With --plan, a selector of the project’s exclude list that matches nothing in the target or in the schema of the target version would let the rollback drop the object it was written to keep. The command exits 2 before it asks for confirmation, on a dry run too, and names the file the selector came from. Nothing has run on the target. The dev database was replayed to derive the plan, and the run empties it again; when that fails, the error says so on a line of its own, and the next run refuses the dev database until it is empty:

    error: the exclude selection from ptah.yaml matched no objects: "audit_event"; migrations down --plan refuses a selection that protects nothing, set PTAH_ATLAS_ALLOW_UNMATCHED_EXCLUDE=1 to keep the permissive behavior

    Correct the selector. PTAH_ATLAS_ALLOW_UNMATCHED_EXCLUDE=1 turns the refusal into a warning and runs the rollback as derived.

  • The target changed while the prompt waited. With --plan, another runner applied or rolled back a migration after the plan was derived. The statements describe a database that is no longer there, so the command exits 2 once it holds the migration lock, before any statement runs, and names both sets of versions. Every table and every revision row are as the other runner left them:

    error: error running down migrations: the rollback to version 1 was derived for the applied migrations [2], and the migrations applied above version 1 are now [3 2]; nothing was rolled back. Run migrations down --plan again to derive the rollback from the database as it is now

    Run the command again: it derives the rollback from the database as it now stands and shows it before it asks.

  • 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.