What "Dry-Run" Actually Means - Building a Postgres Migration Tool From Scratch

Every backend engineer has run a database migration and hoped for the best. Most migration tools offer a "dry-run" flag as a safety net but a lot of them dry-run by just printing the SQL they would have executed, never actually touching the database. That's not a simulation. It's a preview, and it can miss the exact class of failure you were hoping to catch: a constraint violation, a typo caught only at execution time, a foreign key that doesn't exist yet.

I wanted to understand what a real dry-run would take, so I built pgmigrate — a small CLI for versioned, reversible PostgreSQL migrations, written in Python. This is a walkthrough of what I learned building it: mostly about a Postgres feature that's easy to take for granted, and a few failure modes that only show up once you start thinking about migrations as a system rather than a script.

The insight that makes everything else possible

Most DDL in Postgres is transactional. CREATE TABLE, ALTER TABLE, adding a constraint, dropping a column - all of it can run inside BEGIN ... ROLLBACK and vanish completely, as if it never happened. This isn't true of every database. In MySQL, for instance, DDL statements trigger implicit commits, so wrapping schema changes in a transaction doesn't give you the same safety net.

That one property changes what a dry-run can mean. Instead of guessing at SQL syntax or hand-parsing statements to check they're valid, pgmigrate's dry-run mode does the obvious but under-used thing: it runs the actual migration, for real, inside a transaction — then rolls it back.

def apply(self, migration, dry_run=False):
    with self.conn.cursor() as cur:
        cur.execute(migration.up_sql)
        cur.execute(
            "INSERT INTO schema_migrations (version, name, checksum) VALUES (%s, %s, %s)",
            (migration.version, migration.name, migration.checksum),
        )
    if dry_run:
        self.conn.rollback()
    else:
        self.conn.commit()

If the migration would fail, a bad column type, a constraint that can't be satisfied by existing data — you find out during the dry-run, because it genuinely executed and genuinely failed. Nothing is committed either way. What you see is what would happen, not what you hope would happen.

The parts that aren't as obvious

Getting the happy path working took an afternoon. Getting the failure modes right took longer, because migrations are a place where "it worked on my machine" has real consequences — a half-applied schema change in production is a bad day.

1. A migration file is a historical record, not a living document

The natural instinct when you find a bug in a migration you already ran is to just fix the file. Don't. Once a migration has been applied — anywhere, by anyone — its file is supposed to describe, permanently, what happened to that database at that point in time. If you edit it after the fact, you've created a lie: the file now describes a change that was never actually run against the database that has it marked as applied.

pgmigrate guards against this with checksums. When a migration is applied, a SHA-256 hash of its up and down SQL is stored alongside the version number:

@property
def checksum(self) -> str:
    h = hashlib.sha256()
    h.update(self.up_sql.encode("utf-8"))
    h.update(b"\x00")
    h.update(self.down_sql.encode("utf-8"))
    return h.hexdigest()

pgmigrate status and pgmigrate validate recompute this checksum against the file on disk and compare it to what's recorded. If someone edited an applied migration — even by accident — it shows up as drift, and the tool refuses to proceed with further migrations until it's resolved. The fix is never "edit the old file." It's "write a new migration that makes the additional change." This is the same principle that makes git commits useful: history you can rewrite isn't really history.

2. Two processes racing to migrate is a real scenario, not a hypothetical

Picture a deploy pipeline that retries a failed step. If the retry logic doesn't know the first attempt already started applying migrations, you can end up with two processes trying to run the same migration concurrently. Best case, one fails on a duplicate key. Worst case, depending on timing, you get a race that's much harder to reason about.

Postgres has a built-in primitive for exactly this: advisory locks. They're application-level locks, keyed by an arbitrary integer, that live for the duration of a session and don't touch any table.

@contextlib.contextmanager
def advisory_lock(self):
    with self.conn.cursor() as cur:
        cur.execute("SELECT pg_try_advisory_lock(%s)", (ADVISORY_LOCK_KEY,))
        got_lock = cur.fetchone()[0]
    if not got_lock:
        raise LockAcquisitionError()
    try:
        yield
    finally:
        with self.conn.cursor() as cur:
            cur.execute("SELECT pg_advisory_unlock(%s)", (ADVISORY_LOCK_KEY,))

pg_try_advisory_lock doesn't block — it returns immediately with true or false. That matters: a migration runner should fail fast and loudly if another instance is already running, not sit there waiting and hope the timing works out. No coordination service required, no extra table to manage — just a feature Postgres already ships with.

3. Not everything can be dry-run, and pretending otherwise is worse than not trying

The rollback-based dry-run model has a real limitation: it only works for statements that are actually transactional. CREATE INDEX CONCURRENTLY is the textbook counterexample. Postgres explicitly forbids running it inside a transaction block, because it works by taking multiple internal snapshots over time so the table stays fully queryable and writable while the index builds — a property that's fundamentally incompatible with being wrapped in a transaction that might get rolled back.

ALTER SYSTEM, VACUUM, and a handful of others have similar constraints, for different reasons.

The tempting shortcut here is to just let these statements through and hope for the best — or worse, silently skip the rollback for them and let the dry-run actually commit. Both are ways of quietly breaking the promise that --dry-run means "nothing changes." Instead, pgmigrate scans for these patterns up front and refuses to proceed:

UNSAFE_STATEMENT_PATTERNS = [
    re.compile(r"\bCREATE\s+INDEX\s+CONCURRENTLY\b", re.IGNORECASE),
    re.compile(r"\bALTER\s+SYSTEM\b", re.IGNORECASE),
    re.compile(r"\bVACUUM\b", re.IGNORECASE),
    # ...
]

It's a deliberate design boundary rather than a missing feature. A tool that's honest about what it can't safely simulate is more useful than one that pretends to handle everything and occasionally lies about it.

The shape of the system

Under the hood, pgmigrate is intentionally small:

• core.py — pure logic: discovering migration file pairs, computing checksums, detecting unsafe statements. Nothing here touches a database, which means it's fully unit-testable without spinning up Postgres.
• db.py — the transactional layer: applying and reverting migrations, taking the advisory lock, tracking applied versions in a schema_migrations table.
• cli.py — five commands (create, up, down, status, validate) that wire the two together.

Migrations themselves are just SQL files, paired as NNNN_name.up.sql / NNNN_name.down.sql, numbered sequentially. No templating language, no conditionals — if a migration needs branching logic, that's usually a sign it should be split into two migrations instead.

migrations/
  0001_create_users_table.up.sql
  0001_create_users_table.down.sql
  0002_add_unique_email_constraint.up.sql
  0002_add_unique_email_constraint.down.sql

Each up and down file is expected to be a genuine inverse of the other. If a change is destructive by nature — say, a migration that drops a column and its data — the honest thing for the down file to do is fail loudly rather than pretend to restore data that's already gone.

What the failure path looks like

Here's the thing that convinced me this was worth building: watching a migration fail mid-flight and confirming, directly against the database, that nothing was left in a broken state.

$ pgmigrate up
Applying: 0004_broken_migration
error applying 0004_broken_migration: column "bad_column" has type "nonexistent_type" which does not exist
Transaction rolled back; schema is unchanged for this migration.

Checking the database afterward: the broken table doesn't exist, and schema_migrations wasn't touched. The migrations applied before it stayed fully intact. That's the entire value proposition of transaction-scoped migrations in one small failure — you don't have to manually diagnose and repair a half-migrated schema, because there wasn't a window where it could become half-migrated in the first place.

What I'd build next

A few edges are still open, on purpose:

• CREATE INDEX CONCURRENTLY support. Handling it properly means stepping outside the transaction-per-migration model — running it non-transactionally, then verifying success separately, since a failed concurrent index build leaves an invalid index behind that needs explicit cleanup.
• Dependency graphs beyond version order. Right now, ordering is purely sequential. Some migration systems support branching/merging histories, closer to how git handles divergent commits — useful for larger teams working on parallel features that both touch the schema.
• Schema diffing. Comparing the live schema against what the migration history implies, to catch cases where someone made a manual change outside the tool entirely.

Why this was worth building even though tools like this already exist

I didn't build this because Flyway or Alembic are missing something. I built it because using a tool and understanding why it's built the way it is are different kinds of knowledge. Writing the advisory lock logic myself made the failure mode it prevents concrete instead of abstract. Hitting the CREATE INDEX CONCURRENTLY wall myself, instead of reading about it in someone else's changelog, made the transactional-DDL boundary something I now recognize immediately instead of something I'd have to look up.

The full source, including the example migrations and test suite, is on GitHub.