Versioned vs State-Based Migrations: Which to Choose

You are staring at a pull request that changes schema.sql by adding a column, and you cannot tell what SQL the tool will actually run against production — or whether it will quietly DROP an index someone added by hand last month. That uncertainty is the core tension between the two migration models. Imperative versioned tools (Flyway, Alembic, Liquibase changesets) ask you to write the exact ALTER statements and apply them in order. Declarative state-based tools (Atlas, Skeema, and the migra/prisma db push family) ask you to describe the schema you want and let the tool compute a diff at deploy time. Each model trades away a different guarantee: versioned scripts give you a reviewable, ordered history but drift silently; state-based tools eliminate drift but generate DDL you did not write. This page helps backend and platform engineers, and the DBAs who sign off on their deploys, decide which model to standardize on — it sits inside the broader migration tool comparison within the migration fundamentals guide.

Symptom / Error Signatures

You are on the wrong side of this decision if any of these show up in your pipeline or incident channel:

• A migration was applied that is not reflected in the desired schema — a state-based planner (Atlas) found the live database drifted from the declared model.
• The generated plan is destructive: DROP COLUMN "legacy_status" — the declarative diff wants to delete an object you did not intend to remove.
• Detected changes to the database schema not present in migrations — Alembic autogenerate or a drift check found manual DDL the versioned history never captured.
• flyway_schema_history says version V37 is applied, but the live table has an extra index no script created.
• Two engineers open PRs editing the same schema.sql; the merge is clean but the resulting diff drops the other’s column.
• prisma db push reports Data loss warning on a table that already holds production rows.

These are not bad scripts — they are the model’s blind spot surfacing. Versioned tools cannot see drift; state-based tools cannot see intent.

Root Cause Analysis

The two models compute the migration at different times, and that single difference cascades into every trade-off. A versioned tool records a linear list of hand-written scripts and a Flyway schema history table of what ran; it never inspects the live schema, so any change made outside the tool — an emergency index, a manual ALTER during an incident — becomes invisible drift that no checksum can catch. A state-based tool stores only the target schema and diffs it against the live database at plan time; drift is impossible to ignore because the diff surfaces it, but the tool authors the DDL itself, and a naive diff will cheerfully generate a DROP for anything present in the database but absent from your declared model.

Neither model is safe by default at the moment that matters most: the transaction boundary. Whichever tool generates the DDL, PostgreSQL will wrap most of it transactionally while MySQL commits each statement implicitly, so a state-based planner that emits three ALTERs can still leave MySQL half-migrated exactly as a versioned script would — a hazard covered in the transactional versus non-transactional split. The comparison below maps the trade-offs onto the axes that decide operational risk.

The mnemonic: versioned tools trust your scripts and distrust reality; state-based tools trust reality and distrust your scripts. Zero-downtime work needs both trusts, which is why mature setups rarely run a state-based diff straight into production without a review-and-approve gate.

Immediate Mitigation

If you are firefighting a drift alert or a destructive plan right now, stabilize before you re-architect. Run these as a DBA or migration service account with DDL and pg_stat_activity visibility, outside peak traffic, and never approve a generated plan you have not read.

1. Freeze the pipeline and capture the live schema. Stop concurrent deploys so no runner competes, then snapshot the real structure as the source of truth for reconciliation.

# Context: read-only structural dump; run as a role with schema read access; safe on production.
# PostgreSQL 16 — capture structure only, no data.
pg_dump --schema-only --no-owner "$DATABASE_URL" > live_schema.sql

2. Diff the live schema against your declared or expected state before applying anything. For a state-based tool, generate the plan in dry-run mode and read every statement.

# Context: PLAN ONLY — generates no changes; safe to run against production read replica.
# Atlas — show the DDL it WOULD run without executing it.
atlas schema apply --url "$DATABASE_URL" --to file://schema.sql --dry-run

3. Quarantine destructive statements. If the plan contains a DROP COLUMN or DROP TABLE you did not intend, do not approve it — the drop usually means the live object is missing from your declared model, not that it should be deleted.

-- PostgreSQL · read-only diagnostic · run as a role that can read catalogs · safe on production.
-- Confirm the "unexpected" column actually holds data before anyone approves a DROP.
SELECT count(*) AS rows_present, count(legacy_status) AS non_null
FROM orders;

4. Reconcile drift into version control rather than deleting it. When a state-based diff exposes a manually added index, add it to your declared schema (state-based) or write a forward CREATE INDEX IF NOT EXISTS migration (versioned) so the two views agree — the guard keeps the step idempotent if it re-runs.

Route reads to a replica during reconciliation if the diff touches a hot table, and take a logical backup before you approve any plan that contains a drop.

Permanent Fix / Long-Term Pattern

The durable answer is not “pick one and ban the other” — it is to use each model where its guarantee is strongest and close the gap with a gate. Most zero-downtime teams converge on a hybrid: author intent as versioned, forward-only scripts so every deploy has an ordered, human-reviewed artifact, and run a state-based diff as a CI check that fails the build when the live schema drifts from the declared model. That gives you the versioned model’s reviewability and the state-based model’s drift detection without letting a tool auto-DROP in production.

Whichever primary model you choose, harden it the same way. Treat applied migrations as immutable and add new forward files instead of editing old ones, so the ordered history stays trustworthy — the discipline detailed under schema version control basics. Never let a generated plan apply unattended: require a human to approve the diff, and configure the planner to refuse destructive changes by default (Atlas --exclude and lint rules, Skeema’s --safe / --allow-unsafe gate). Make every change backward-compatible first — add columns and indexes before the code reads them, drop legacy objects only in a later release — so neither model can strand you mid-deploy; that ordering is the heart of the migration tool comparison lock-and-rollback analysis. Finally, keep environments identical through environment parity strategies so a diff computed against staging matches what production will produce, and pin the same engine behavior across both.

For rollback specifically, the models diverge in a way worth planning for. A versioned tool rolls back by applying an explicit inverse migration you wrote (or a paid undo); a state-based tool rolls back by re-declaring the previous schema and letting the diff reverse it — which is elegant until the reverse diff wants to drop a column that now holds data written since the deploy. In both cases the safe condition is identical: the inverse DDL must be non-destructive to any data the still-running app version depends on. When the primary model itself is changing, treat it as a full cutover like migrating from Flyway to Liquibase without downtime rather than a flag flip.

Verification Checklist

• Every production deploy runs a state-based diff (atlas schema apply --dry-run or migra) in CI that fails the build on drift.
• No generated plan is applied without a human reading and approving the exact DDL.
• The planner refuses destructive statements by default (--safe / lint rules), requiring an explicit override for any DROP.
• Applied versioned migrations are immutable; drift is reconciled by a new forward file, never by editing history.
• Every change is backward-compatible — additive first, legacy drops deferred to a later release.
• A rollback dry-run in a staging clone confirms the inverse diff is non-destructive to live data.

Up one level: Migration Tool Comparison.

Frequently Asked Questions

Is a state-based tool safer than a versioned one because it eliminates drift? It eliminates silent drift, which is a real advantage, but it introduces a different hazard: the tool generates the DDL, and a naive diff will emit a DROP for anything in the database but missing from your declared schema. Safety comes from the gate around the tool — human approval of the plan and a default refusal of destructive statements — not from the model alone. Most teams get the best of both by authoring versioned scripts and running a state-based diff purely as a drift check in CI.

Can I use both models at the same time without them fighting? Yes, and it is the recommended pattern. Make the versioned scripts the single source that actually mutates production, so you keep an ordered, reviewable history, and run the state-based diff read-only in CI to detect when the live schema no longer matches your declared model. The two only conflict if you let both write to production; keep exactly one model as the writer and the other as the auditor.

How does the choice affect rollback during a zero-downtime deploy? A versioned tool rolls back with an explicit inverse migration you wrote in advance, which is predictable but requires discipline to keep current. A state-based tool rolls back by re-declaring the previous schema and re-diffing, which is less work to author but can generate an unintended data-losing drop if rows were written after the forward deploy. In both models the safe condition is the same: the reverse DDL must not remove anything the still-running application version still reads or writes.