Test Database Migration Rollback Safely (2026)

TL;DR

To test database migration rollback safely, rebuild a disposable database, record pre-migration schema and data evidence, apply the forward migration, exercise it, run the reverse migration, and compare the restored state. Finish with a second forward migration in a clean-room CI job.

Key Takeaways

• Run rollback tests against a disposable database built from the same migration chain as production.
• Define the rollback contract before writing assertions because not every transformation is reversible.
• Verify schema objects through PostgreSQL catalogs instead of trusting a successful command exit code.
• Fingerprint business data before migration and compare it after rollback to detect silent damage.
• Exercise the full up, verify, down, verify, up sequence to expose one-way migration defects.
• Use ON_ERROR_STOP and clean volumes so SQL failures cannot be hidden by later commands.
• Treat destructive or lossy changes as restore-and-roll-forward operations when reverse SQL cannot preserve meaning.

To test database migration rollback safely, prove more than the fact that a down script exits with code 0. A credible test shows that the expected schema returns, protected data remains intact, old application assumptions work again, and the forward migration can still run after the reversal.

This tutorial builds that proof against an isolated PostgreSQL database. You will create a small customer and order schema, add an enum-backed order status, capture evidence, roll the change back, and automate the complete cycle. The same method works with Flyway, Liquibase, Prisma, Rails, Django, or hand-written SQL because the assertions target database behavior rather than a migration framework. For broader planning around risky transformations, read testing data migrations before adapting the lab to production-scale tables.

What You Will Build

You will build a runnable migration rollback gate with these outputs:

• A PostgreSQL 17.5 container that can be destroyed without touching a shared environment.
• A versioned baseline schema and deterministic fixture rows.
• A forward migration that adds order_status, an orders.status column, and an index.
• A reverse migration that removes only the objects introduced by that version.
• Catalog assertions for columns, types, indexes, constraints, and migration history.
• A data fingerprint captured before the change and compared after rollback.
• A Bash runner that performs baseline -> up -> verify -> down -> verify -> up -> verify from a clean volume.

The example intentionally uses a small additive change. It is complex enough to expose ordering and dependency errors, yet reversible without pretending that dropped customer data can be reconstructed.

Prerequisites

Use the following validated baseline:

Newer compatible patch releases are acceptable, but keep one exact image tag in CI. A floating postgres:latest tag makes failures difficult to reproduce. You need a terminal and roughly 300 MB of free local space. You do not need a host PostgreSQL installation because every SQL command runs inside the container.

Confirm Docker before creating files:

docker version --format '{{.Server.Version}}'
docker compose version --short
bash --version | head -n 1

Verification: Docker must report a reachable server, Compose must return a version, and Bash must print its version without an error. If docker version shows only client information, start Docker before continuing.

Step 1: Define How You Will Test Database Migration Rollback Safely

Start by writing a rollback contract. A rollback is not automatically equivalent to restoring every bit. The contract states which state must be identical, which new state may be discarded, and which application version must remain compatible.

For this lab, the pre-migration contract is precise:

Create the lab layout:

mkdir -p migration-rollback-lab/{migrations,fixtures,tests,scripts}
cd migration-rollback-lab
printf '%s\n' migrations fixtures tests scripts

A change that drops a column containing unique user input needs a different contract. Reverse SQL cannot recreate values that no longer exist. Such a release needs a backup, shadow column, dual-write period, or a roll-forward repair. The risk analysis in testing database constraints helps identify dependencies that a simple column checklist can miss.

Verification: The final command must print all four directory names. Before moving on, review the table and confirm that every object created by migration 002 has a stated rollback expectation.

Step 2: Start an Isolated PostgreSQL Database

Create compose.yaml in the lab root:

services:
  db:
    image: postgres:17.5-alpine
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: app_test
    ports:
      - "55432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d app_test"]
      interval: 2s
      timeout: 3s
      retries: 20
    tmpfs:
      - /var/lib/postgresql/data

The nonstandard host port reduces collisions with a developer database. The tmpfs mount makes the cluster ephemeral. Never point this tutorial at staging or production, even if the migration seems harmless. A wrong database URL is one of the most dangerous rollback test failures because the test itself becomes the incident.

Start the service and ask Compose to wait for health:

docker compose up -d --wait
docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 -c 'select current_database(), current_user, version();'

ON_ERROR_STOP=1 changes psql from a permissive script runner into a useful test command. Without it, an early SQL error can scroll past while a later statement succeeds and produces a misleading zero exit status.

Verification: The query must show database app_test, user app, and PostgreSQL 17.5. Also run docker compose ps; the db service must be healthy. Stop if the database name differs.

Step 3: Create the Known Baseline and Fixtures

Create migrations/001_baseline_up.sql:

BEGIN;

CREATE TABLE schema_migrations (
  version text PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT customers_email_key UNIQUE (email)
);

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  total_cents integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT orders_customer_fk
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT orders_total_nonnegative CHECK (total_cents >= 0)
);

INSERT INTO schema_migrations(version) VALUES ('001_baseline');
COMMIT;

Create fixtures/seed.sql with stable business values:

INSERT INTO customers(email)
VALUES ('alex@example.test'), ('sam@example.test');

INSERT INTO orders(customer_id, total_cents)
VALUES (1, 2599), (1, 4100), (2, 875);

Apply both files through the container:

docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 < migrations/001_baseline_up.sql
docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 < fixtures/seed.sql

Named constraints make catalog checks and production diagnostics readable. Deterministic fixture values make a changed row visible; random data would add noise without increasing coverage here. If you need more elaborate environment setup, SQL for test data setup and teardown covers fixture boundaries and cleanup.

Verification: Run the following exact query:

docker compose exec -T db psql -U app -d app_test -Atc \
  "SELECT (SELECT count(*) FROM customers) || ':' || (SELECT count(*) FROM orders) || ':' || (SELECT sum(total_cents) FROM orders);"

The expected output is 2:3:7574. Any other value means the starting state is untrustworthy, so do not test the migration yet.

Step 4: Capture Pre-Migration Evidence

Counts alone miss in-place corruption. Create tests/capture_before.sql to store both counts and a deterministic fingerprint in a test-only schema:

CREATE SCHEMA IF NOT EXISTS qa_test;
DROP TABLE IF EXISTS qa_test.rollback_evidence;

CREATE TABLE qa_test.rollback_evidence (
  customer_count bigint NOT NULL,
  order_count bigint NOT NULL,
  business_fingerprint text NOT NULL
);

INSERT INTO qa_test.rollback_evidence
SELECT
  (SELECT count(*) FROM customers),
  (SELECT count(*) FROM orders),
  md5(
    COALESCE((
      SELECT string_agg(format('%s|%s', id, email), ',' ORDER BY id)
      FROM customers
    ), '') || '#' ||
    COALESCE((
      SELECT string_agg(
        format('%s|%s|%s', id, customer_id, total_cents),
        ',' ORDER BY id
      )
      FROM orders
    ), '')
  );

TABLE qa_test.rollback_evidence;

Apply it:

docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 < tests/capture_before.sql

The fingerprint includes only business fields that must survive. It deliberately excludes created_at, whose textual formatting can vary with session settings, and identity sequence state, which PostgreSQL does not promise to rewind after every transaction. In a real system, select stable primary keys and protected columns, order rows explicitly, and hash batches if the table is too large for one aggregation. For deeper query patterns, use validating data integrity with SQL.

Verification: The TABLE output must contain one evidence row with customer count 2, order count 3, and a nonempty 32-character MD5 value. Confirm the length directly with SELECT length(business_fingerprint) FROM qa_test.rollback_evidence;; it must return 32.

Step 5: Apply the Forward Migration and Verify Behavior

Create migrations/002_add_order_status_up.sql:

BEGIN;

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');
ALTER TABLE orders
  ADD COLUMN status order_status NOT NULL DEFAULT 'pending';
CREATE INDEX orders_status_idx ON orders(status);
INSERT INTO schema_migrations(version) VALUES ('002_add_order_status');

COMMIT;

Now create tests/assert_forward.sql:

DO $
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'orders'
      AND column_name = 'status'
      AND udt_name = 'order_status'
      AND is_nullable = 'NO'
  ) THEN
    RAISE EXCEPTION 'orders.status is missing or has the wrong definition';
  END IF;

  IF to_regclass('public.orders_status_idx') IS NULL THEN
    RAISE EXCEPTION 'orders_status_idx is missing';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM schema_migrations
    WHERE version = '002_add_order_status'
  ) THEN
    RAISE EXCEPTION 'migration version 002 was not recorded';
  END IF;

  IF (SELECT count(*) FROM orders WHERE status = 'pending') <> 3 THEN
    RAISE EXCEPTION 'existing orders did not receive the pending status';
  END IF;
END $;

BEGIN;
INSERT INTO orders(customer_id, total_cents, status)
VALUES (2, 1200, 'paid');
DO $
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM orders
    WHERE customer_id = 2 AND total_cents = 1200 AND status = 'paid'
  ) THEN
    RAISE EXCEPTION 'new status values cannot be written and read';
  END IF;
END $;
ROLLBACK;

Apply and assert:

docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 < migrations/002_add_order_status_up.sql
docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 < tests/assert_forward.sql

The temporary paid order is rolled back so it cannot invalidate the original fingerprint. The test checks behavior as well as structure: existing rows get a valid value, and a new enum value can be stored. Catalog-only checks would miss a broken backfill or an unusable default.

Verification: Both commands must exit 0. Then query SELECT version FROM schema_migrations ORDER BY version;; the output must contain 001_baseline and 002_add_order_status. Querying SELECT status, count(*) FROM orders GROUP BY status; must return pending|3 in unaligned output.

Step 6: Run the Reverse Migration and Assert the Restored State

Create migrations/002_add_order_status_down.sql in dependency-safe order:

BEGIN;

DO $
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM schema_migrations
    WHERE version = '002_add_order_status'
  ) THEN
    RAISE EXCEPTION 'cannot roll back migration 002 because it is not applied';
  END IF;
END $;

DROP INDEX orders_status_idx;
ALTER TABLE orders DROP COLUMN status;
DROP TYPE order_status;
DELETE FROM schema_migrations WHERE version = '002_add_order_status';

COMMIT;

Create tests/assert_rollback.sql:

DO $
DECLARE
  actual_customer_count bigint;
  actual_order_count bigint;
  actual_fingerprint text;
  expected_record qa_test.rollback_evidence%ROWTYPE;
BEGIN
  SELECT * INTO STRICT expected_record
  FROM qa_test.rollback_evidence;

  SELECT count(*) INTO actual_customer_count FROM customers;
  SELECT count(*) INTO actual_order_count FROM orders;
  SELECT md5(
    COALESCE((
      SELECT string_agg(format('%s|%s', id, email), ',' ORDER BY id)
      FROM customers
    ), '') || '#' ||
    COALESCE((
      SELECT string_agg(
        format('%s|%s|%s', id, customer_id, total_cents),
        ',' ORDER BY id
      )
      FROM orders
    ), '')
  ) INTO actual_fingerprint;

  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'orders'
      AND column_name = 'status'
  ) THEN
    RAISE EXCEPTION 'orders.status still exists';
  END IF;

  IF to_regtype('public.order_status') IS NOT NULL THEN
    RAISE EXCEPTION 'order_status type still exists';
  END IF;

  IF to_regclass('public.orders_status_idx') IS NOT NULL THEN
    RAISE EXCEPTION 'orders_status_idx still exists';
  END IF;

  IF EXISTS (
    SELECT 1 FROM schema_migrations
    WHERE version = '002_add_order_status'
  ) THEN
    RAISE EXCEPTION 'migration version 002 is still recorded';
  END IF;

  IF actual_customer_count <> expected_record.customer_count
     OR actual_order_count <> expected_record.order_count
     OR actual_fingerprint <> expected_record.business_fingerprint THEN
    RAISE EXCEPTION 'business data differs from pre-migration evidence';
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'orders_customer_fk'
      AND conrelid = 'public.orders'::regclass
  ) OR NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'orders_total_nonnegative'
      AND conrelid = 'public.orders'::regclass
  ) THEN
    RAISE EXCEPTION 'a baseline order constraint was lost';
  END IF;
END $;

Run the rollback and assertion as separate commands. Keeping them separate proves that the reverse file can commit successfully before an independent session inspects it.

docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 < migrations/002_add_order_status_down.sql
docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 < tests/assert_rollback.sql

Verification: assert_rollback.sql must exit 0 and print DO. Run SELECT count(*) FROM orders; and expect 3. A zero exit from the down migration alone is insufficient because a broad DROP ... CASCADE could succeed while deleting unrelated dependencies.

Step 7: Reapply the Migration and Check Application Compatibility

A second forward pass catches migrations that only work once. Common causes include a down script that leaves an enum, trigger, sequence, partial index, or migration-history row behind. Reapply the same file and the same forward assertions:

docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 < migrations/002_add_order_status_up.sql
docker compose exec -T db psql -U app -d app_test \
  -v ON_ERROR_STOP=1 < tests/assert_forward.sql

Database checks are necessary, but rollback usually happens because an application release is unhealthy. Add a compatibility smoke test using the previous application build against the rolled-back schema in your real pipeline. At minimum, exercise its startup migration check, one representative read, and one write. Do not assume schema equality means ORM metadata, prepared statements, or cached query plans are healthy. Test backend contracts without production offers a safe way to exercise those application-facing boundaries.

For expand-and-contract releases, test both application versions at the intermediate schema. An old binary must tolerate additive nullable objects before the new binary depends on them. A new binary usually cannot keep running after its required column is removed, so rollback order matters: route traffic to the old build, stop new writers, then reverse the database change.

Verification: The repeated migration and assert_forward.sql must both exit 0. Check that SELECT count(*) FROM schema_migrations WHERE version = '002_add_order_status'; returns exactly 1, not a duplicate or zero. In an application pipeline, the previous build's smoke test must also pass before the rollback gate is considered green.

Step 8: Automate How You Test Database Migration Rollback Safely

Create scripts/run-rollback-test.sh:

#!/usr/bin/env bash
set -Eeuo pipefail

cleanup() {
  docker compose down -v --remove-orphans
}
trap cleanup EXIT

run_sql_file() {
  docker compose exec -T db psql -U app -d app_test \
    -v ON_ERROR_STOP=1 < "$1"
}

docker compose up -d --wait
run_sql_file migrations/001_baseline_up.sql
run_sql_file fixtures/seed.sql
run_sql_file tests/capture_before.sql
run_sql_file migrations/002_add_order_status_up.sql
run_sql_file tests/assert_forward.sql
run_sql_file migrations/002_add_order_status_down.sql
run_sql_file tests/assert_rollback.sql
run_sql_file migrations/002_add_order_status_up.sql
run_sql_file tests/assert_forward.sql

printf '%s\n' 'PASS: forward, rollback, and second forward migration verified'

Make it executable and run it from the lab root:

chmod +x scripts/run-rollback-test.sh
./scripts/run-rollback-test.sh

The trap destroys the dedicated Compose resources after success or failure. Starting from an empty volume prevents a developer's earlier manual repair from making the test pass. In CI, run this script for every migration pull request and preserve the psql output as a job log. For very large chains, cache a sanitized baseline image at a known migration version, then apply every later migration in order. Periodically run the entire chain from zero to catch drift in the cached base.

Do not weaken the script with || true. A failed down statement, assertion, or second up must stop the job. If your tool manages transactions itself, keep the same observable sequence while calling the framework's official migrate and rollback commands. Test conditional schema migration blocks is useful when migrations vary by tenant, extension, or feature state.

Verification: The last line must be PASS: forward, rollback, and second forward migration verified, and docker compose ps -a must show no remaining service for this project after the trap runs. A CI job passes only when both conditions hold.

Troubleshooting

Problem: psql continues after an error and the script appears green -> Ensure every invocation includes -v ON_ERROR_STOP=1, and keep set -Eeuo pipefail at the top of the runner. Check the exit code immediately with echo $?; a deliberately invalid assertion must produce a nonzero result.

Problem: DROP TYPE order_status reports that other objects depend on it -> Inspect dependencies with \dT+ order_status and query pg_depend before editing the down script. Remove dependent columns, defaults, functions, and casts in a deliberate order. Avoid CASCADE in rollback migrations because it can erase objects that the migration did not create.

Problem: the fingerprint differs although row counts match -> Compare ordered rows by primary key and inspect columns that the migration rewrote. Check timezone, collation, padding, numeric rounding, and trigger side effects. Do not simply update the expected hash; explain every changed value and decide whether the rollback contract permits it.

Problem: the first forward migration passes but the second fails with already exists -> The down migration left an object or migration-history entry behind. Query pg_type, pg_class, pg_proc, pg_trigger, and schema_migrations for the version's object names, then add explicit assertions for the leaked object.

Problem: rollback blocks while production-like traffic is running -> Inspect pg_stat_activity and pg_locks in the test environment. Measure lock acquisition and statement duration with representative table size. Use a lock timeout, drain writers, or redesign as an expand-and-contract sequence instead of learning about an access-exclusive lock during an incident.

Problem: rollback succeeds in the lab but the old application fails -> The database contract omitted an application assumption. Run the previous production artifact against the rolled-back database, including ORM startup, prepared queries, feature flags, and background workers. Add the failing interaction as a permanent compatibility smoke test.

Where To Go Next

Turn this small lab into a release control, not a one-time exercise. Apply the pattern to the next real migration, add table-specific invariants, and run the prior application artifact after the down step. Use seed ephemeral databases with Testcontainers when each integration test suite needs an isolated database from code.

Changes involving row-level security need policy-specific assertions; follow testing RLS policy migration regressions. For changes that transform millions of rows, combine chunk-level reconciliation from testing data migrations with lock and runtime measurements. The goal is one auditable gate per risk, not one universal SQL script.

Interview Questions and Answers

Q: What does a safe database rollback test prove?

It proves that the intended pre-migration contract is restored, protected data is unchanged, introduced objects are removed, baseline constraints still work, migration history is correct, and the forward change remains applicable. I also run the previous application build because catalog equality alone does not establish application compatibility.

Q: Why use an ephemeral database instead of a shared QA database?

An ephemeral database starts from known state, eliminates collisions with other testers, and can be destroyed after destructive SQL. It also makes the migration sequence reproducible in CI. A shared environment may still be used for a final rehearsal, but it should not be the first place a down migration is exercised.

Q: Why is an up-down-up sequence valuable?

The first up validates the forward path, down validates reversal, and the second up exposes leftovers such as types, indexes, triggers, or version records. Those remnants can be invisible after rollback until a later deployment tries to recreate them.

Q: How would you test a rollback that drops a populated column?

I would reject the assumption that reverse DDL can restore lost values. I would require a backup or shadow column, prove the copy and restore queries with checksums, set a retention window, and define the point after which rollback becomes roll-forward recovery.

Q: Which PostgreSQL catalogs are useful for migration assertions?

I use information_schema.columns for portable column checks, pg_constraint for named constraints, pg_class or to_regclass for relations and indexes, pg_type or to_regtype for types, and pg_depend when diagnosing dependencies. Assertions should identify the exact object and expected state.

Q: How do you prevent a rollback test from damaging the wrong database?

I use a dedicated container, a clearly named test database, scoped credentials, and a preflight assertion on database name and server identity. Production credentials are unavailable to the job. Destructive tests run only after those checks pass.

Q: What evidence would you attach to a migration release?

I would attach the migration and reverse-script review, clean-room job log, schema assertions, data reconciliation result, duration and lock observations, and the prior-version application smoke result. For lossy changes, I would also attach the restore procedure and a completed rehearsal.

Best Practices

• Make rollback expectations part of migration design, not an emergency task after deployment fails.
• Name constraints and indexes so checks report useful failures.
• Test from a production-shaped schema with synthetic or masked data, never an unmanaged production dump.
• Record evidence before migration in storage the migration cannot accidentally rewrite.
• Assert absence after rollback as carefully as presence after migration.
• Keep each migration's reverse script scoped to objects created or changed by that version.
• Measure locks and runtime with realistic volume before approving operational safety.
• Pair database checks with old-application smoke tests and background-worker checks.
• Prefer roll-forward repair when reversal would destroy accepted writes or violate business meaning.

Conclusion

To test database migration rollback safely, build a clean database, define the restoration contract, capture evidence, verify the forward behavior, run deliberate reverse SQL, compare the restored state, and apply the migration again. That sequence detects silent data changes and leaked schema objects that a successful rollback command cannot reveal.

Run the lab for one additive migration first. Then replace its generic counts and fingerprint with invariants from your domain, add the previous application artifact, and make the clean-room script a required release check. A rollback plan becomes trustworthy only after the exact path has produced evidence under controlled conditions.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Test Database Backfill Scripts Safely (2026)
• Database Rider vs DbUnit Test Data (2026)
• Test scenarios vs test cases (2026)
• A/B test validation: A Complete Guide for QA (2026)
• AI assisted API test generation (2026)
• AI for exploratory test charters (2026)