Migration Tool Comparison

The migration tool you adopt is a long-term operational contract, not a syntax preference. Each runner makes three decisions on your behalf that determine whether a deploy is safe: how it detects that an already-applied migration has been edited (its checksum model), how it records which versions ran (its schema-history table), and how it stops two runners from racing the same database (its locking strategy). Get these wrong and you discover the mismatch at 3 a.m., when a hand-edited migration fails its checksum on production while a concurrent deploy holds a stale advisory lock. This page serves backend and platform engineers selecting a runner, and DBAs who must guarantee the history table and lock behavior survive every release.

Tool choice sits at the root of the broader Database Migration Fundamentals discipline: the runner enforces ordering and immutability so that Schema Version Control Basics hold under merge pressure, and it must respect the transactional versus non-transactional split between PostgreSQL and MySQL or it will leave a database half-migrated on failure. This guide compares Flyway, Liquibase, Alembic, and Prisma Migrate on exactly those mechanisms — not on which has the nicer CLI.

Concept & Mechanism

A migration runner is a small state machine wrapped around your DDL. Understanding the comparison means understanding the three mechanisms it implements, because that is where the tools genuinely diverge.

Checksum validation. Once a migration is applied, the runner stores a hash of the file alongside its version number. On the next run it re-hashes the on-disk file and compares. If they differ — because someone edited an applied migration — strict tools refuse to run. Flyway computes a CRC32 over the SQL file and aborts with Migration checksum mismatch. Liquibase stores an MD5 in the MD5SUM column of DATABASECHANGELOG and likewise refuses. Alembic does not checksum migration bodies at all — it trusts the revision graph — so an edited migration that already ran is silently ignored. Prisma records a checksum in _prisma_migrations and treats a mismatch as drift, prompting a reset in development and failing migrate deploy in production.

Schema-history tracking. The runner needs a durable record of what ran, in what order. The table name and ordering model differ sharply. Flyway uses flyway_schema_history with monotonic version strings (V1, V1.1, V2). Liquibase uses DATABASECHANGELOG, keyed by the changeset’s id/author/filename triple rather than a single version, which is why Liquibase tolerates out-of-order authoring better. Alembic stores a single version_num row in alembic_version and resolves order through a down_revision pointer graph, not a sortable string. Prisma writes one row per migration directory into _prisma_migrations, ordered by directory timestamp prefix.

Lock behavior. To stop two deploys racing the same database, each runner acquires a lock. Flyway and Liquibase take a database-level lock — Flyway via a session-level advisory lock (a pg_advisory_lock on PostgreSQL), Liquibase via a row it inserts into a DATABASECHANGELOGLOCK table. The Liquibase table-row lock is the dangerous one: if a runner is killed mid-migration, the lock row is never released and every subsequent run hangs until you manually clear it. Alembic and Prisma take a transaction-scoped lock only where the engine supports transactional DDL, which on MySQL means effectively no cross-statement lock — a critical interaction with the transactional versus non-transactional behavior covered separately.

Prerequisites & Decision Criteria

Pick the runner against your operational constraints, not its feature list. Use this checklist to score candidates before committing:

• The runner exposes the schema-history table as queryable SQL (you will need it for drift checks and incident triage).
• Its lock is released automatically on process crash, OR you have a documented manual-unlock runbook.
• It can inject session settings (lock_timeout, statement_timeout) before each migration.
• It supports your stricter engine’s DDL model — transactional rollback on PostgreSQL, or per-statement recovery on MySQL.
• Checksum strictness matches your team: hard-fail (Flyway/Liquibase) if you forbid editing applied files; graph-trust (Alembic) only if your review process guarantees it.
• It runs as a discrete, non-interactive CI step with a non-zero exit code on any failure.

If your team forbids editing applied migrations and runs primarily PostgreSQL, Flyway’s session advisory lock and CRC32 hard-fail are the safest defaults — the deeper trade-offs are in Flyway vs Liquibase: Choosing the Right Migration Tool.

Step-by-Step Procedure

Evaluate candidates against a fresh, production-like database rather than trusting documentation. Each step produces evidence you can attach to an architecture decision record.

1. Provision an ephemeral database that mirrors production. Match the engine major version and privilege model from your environment parity baseline so lock and DDL behavior is representative.

# Context: throwaway CI database; full DDL privileges; never point this at production.
# PostgreSQL 16 to match the prod LTS line.
docker run -d --name tool-eval -e POSTGRES_PASSWORD=eval -p 5432:5432 postgres:16

Verify before proceeding: psql -h localhost -U postgres -c '\l' lists the database and you can create a table.

2. Apply a representative migration and capture the history table. Use a real additive change, not a toy one.

-- PostgreSQL · run as the migration role · safe on an empty eval DB, NOT production.
-- After the runner applies this, inspect its history table.
ALTER TABLE orders ADD COLUMN IF NOT EXISTS region_code VARCHAR(8);
-- Then, for Flyway:
SELECT version, checksum, success FROM flyway_schema_history ORDER BY installed_rank;

Verify before proceeding: exactly one new history row exists with success = true and a non-null checksum.

3. Force a checksum mismatch. Edit the already-applied migration file and re-run the runner. Record the exact error and exit code.

# Context: deliberately corrupting an applied migration on the EVAL db to test strictness.
echo "-- harmless trailing comment" >> migrations/V2__add_region.sql
flyway migrate   # expect: "Migration checksum mismatch for migration version 2"
echo $?          # expect non-zero

Verify before proceeding: Flyway and Liquibase abort; note whether Alembic silently proceeds, which tells you it will not protect you here.

4. Simulate a crashed runner mid-lock. Start a migration and kill the process, then start a second runner. This reveals the stuck-lock failure mode.

# Context: EVAL db only. Kill the runner while it holds the lock, then retry.
liquibase update & sleep 1 && kill -9 $!
liquibase update   # Liquibase: hangs / "Could not acquire change log lock"

Verify before proceeding: confirm whether the second run blocks, and document the unlock command (liquibase releaseLocks) in your runbook.

Verification & Observability

Trust the database’s own state, not the runner’s exit message. These queries confirm the history table and locks are clean before you promote a tool or a deploy.

-- PostgreSQL · read-only · safe on production with a read role.
-- Confirm no failed migration was left half-recorded (Flyway).
SELECT version, success FROM flyway_schema_history WHERE success = false;

-- Confirm no advisory lock is still held by a dead session.
SELECT pid, locktype, objid, granted
FROM pg_locks WHERE locktype = 'advisory';

-- PostgreSQL/MySQL · read-only · run against the live DB during a deploy.
-- Liquibase: detect a stuck lock row that will block every future run.
SELECT id, locked, lockedby, lockgranted FROM databasechangeloglock WHERE locked = TRUE;

For MySQL, inspect lock contention directly while a migration runs, since the runner cannot wrap statements in one transaction:

-- MySQL 8.0 · read-only · run from a second session during the migration.
-- Look for the migration thread holding a metadata lock on the target table.
SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'orders';
SHOW ENGINE INNODB STATUS\G

Rollback Path

Rolling back a tool evaluation is cheap; rolling back a tool migration in production is not. The safe path depends on what you changed.

If a candidate failed its checks, simply drop the ephemeral database and record the failure in the ADR — no production state was touched. If you applied a migration through a runner on a real database and must reverse it, do not delete history rows by hand. Run the runner’s own reverse if it has one (Flyway undo requires the paid edition; Liquibase rollback uses the changeset’s declared reverse), and only then verify the history table reflects the reversal:

-- PostgreSQL · migration role · safe only after the inverse DDL has committed.
-- Confirm the history no longer lists the reversed version as applied.
SELECT version, success FROM flyway_schema_history ORDER BY installed_rank DESC LIMIT 5;

Safe conditions for any rollback: the inverse DDL is non-destructive (it does not DROP a column the previous app version still reads), the runner’s lock is released, and no concurrent deploy is in flight. If those do not hold, halt and restore from a pre-migration snapshot instead.

Common Errors & Fixes

Migration checksum mismatch for migration version N (Flyway) / Validation Failed: changeset checksum changed (Liquibase). Root cause: an already-applied migration file was edited. Fix: never edit applied migrations — author a new forward migration. If the edit was cosmetic and intentional, run flyway repair (or Liquibase clearCheckSums) to re-baseline the stored hash, but only after confirming the SQL body did not change behavior.

Could not acquire change log lock. Currently locked by <host> (Liquibase). Root cause: a previous runner was killed before releasing its DATABASECHANGELOGLOCK row. Fix: confirm no migration is actually running, then liquibase releaseLocks. Add a deploy-timeout so a hung runner cannot hold the lock indefinitely.

Target database is not up to date (Alembic). Root cause: the alembic_version row points to a revision that is not the head of your down_revision graph, usually after a bad merge. Fix: run alembic heads to find divergence, then author a merge revision — this is the conflict-resolution pattern that Schema Version Control Basics addresses for every runner.

The migration ... was modified after it was applied (Prisma). Root cause: a file inside an already-deployed migrations/ directory changed, so its recorded checksum no longer matches. Fix: in production, never edit applied directories; generate a new migration. Resolve the recorded state with prisma migrate resolve only when you are certain the database already reflects the intended schema.

Child Page Index

This section drills into the choices the comparison surfaces. Flyway vs Liquibase: Choosing the Right Migration Tool compares the two most common SQL-centric runners head to head on checksum strictness, the table-row versus advisory lock trade-off, and multi-database syntax handling. When you have already standardized on one tool and need to switch without an outage, Migrating from Flyway to Liquibase Without Downtime walks through reconciling the two history tables, preserving applied-version state, and running both runners in parallel during the cutover. Both build on the broader Database Migration Fundamentals that define what a correct, ordered, immutable migration is in the first place.

Frequently Asked Questions

Does the migration tool I pick affect whether a deploy can be zero-downtime? Indirectly but significantly. The tool decides lock behavior and whether a failed step rolls back cleanly. A runner that holds a long advisory lock, or one that leaves a MySQL migration half-applied because it assumed transactional DDL, can turn a routine additive change into an outage regardless of how careful the SQL is.

Why does Alembic let me edit an applied migration when Flyway refuses? Alembic does not checksum migration bodies; it trusts the revision graph defined by down_revision pointers. That makes branching and merging flexible but removes a safety net — an edited migration that already ran is silently ignored. If your team needs hard protection against tampering, prefer a CRC/MD5-checksumming runner like Flyway or Liquibase.

What is the single most dangerous difference between these tools in production? The lock model. Liquibase’s lock is a row in DATABASECHANGELOGLOCK; if a runner is killed mid-migration, that row is never cleared and every future run hangs until you manually run releaseLocks. Flyway’s session-level advisory lock frees automatically when the connection dies, which is why it fails safer under crash conditions.