Schema Version Control Basics

A schema is application state, and state that lives only inside a running database cannot be reviewed, rolled back, or reasoned about across a team. Schema version control fixes this by treating every change as an immutable, ordered file committed to git alongside the code that depends on it. The hard problems are not writing the ALTER TABLE; they are guaranteeing that two engineers on two branches produce a deterministic order when their work merges, that an applied file is never silently edited, and that the migration runner’s checksum of the file on disk still matches what it recorded when it ran. This page serves backend engineers authoring migrations and the reviewers and DBAs who must trust that the repository is the single source of truth for production schema.

This is the connective tissue of the broader Database Migration Fundamentals: version control is what makes the tool comparison’s checksum and history mechanisms meaningful, what lets idempotent script design survive pipeline retries, and what keeps a team from racing conflicting versions into the same history table. Below, the focus is the three failure surfaces unique to versioning schema in git: ordering, checksums, and merge conflicts.

Concept & Mechanism

Three mechanisms make versioned schema deterministic, and each fails in a characteristic way when ignored.

Ordering. The migration runner applies files in a total order and records that order in its history table. Most runners derive order from the version segment of the filename — V5__add_email.sql before V6__add_phone.sql — or from a timestamp prefix. The repository, however, is a tree, not a line: two branches can each pick V5. The runner only sees the merged result, so the burden of producing a single correct order falls on git review, not the database. This is exactly why the git branching strategy you adopt directly shapes how often collisions happen.

Checksums. When a runner applies a file it stores a hash of the file’s bytes. On every later run it re-hashes the on-disk file and compares. This is the immutability contract: an applied migration is frozen. Editing it — even a whitespace change — breaks the checksum and a strict runner refuses to proceed. The contract is what lets you trust that the SQL reviewed in a pull request is the SQL that ran in production. It also means a backward-compatible correction is always a new forward file, never an edit.

Merge resolution. Because schema history is linear but development is branched, the moment two branches add migrations, a merge must reconcile them. Critically, a clean git merge is not a correct migration merge: two files named V5__... can merge without a textual git conflict yet collide in the runner’s history table at deploy time. Detecting that requires a check beyond git merge, which is the core of resolving migration version conflicts during merges.

Prerequisites & Decision Criteria

Before committing your first migration, lock in the conventions that prevent ordering and checksum incidents. Use this checklist:

• A single migrations/ directory at the repo root is the only place schema changes live.
• A naming convention encodes order unambiguously (zero-padded version or UTC timestamp prefix).
• A baseline snapshot of the current production schema is committed as version 0.
• Applied migrations are immutable by team rule and enforced by a checksum-checking runner.
• A CI check fails the build when two unapplied migrations share a version number.
• Every migration is reviewed for backward compatibility before merge, not after deploy.

For teams with many concurrent branches, timestamp prefixes drastically reduce collisions; for small teams that value a readable linear history, sequential integers plus a CI collision check are simpler. The trade-off interacts directly with your environment parity strategy, since staging must apply the exact same ordered set as production.

Step-by-Step Procedure

This procedure initializes a versioned schema repository and proves the checksum and collision guards work before any real migration ships.

1. Capture an immutable baseline. Snapshot production’s current schema so version 0 is reproducible.

# Context: read-only against production; requires a read role; produces a committed artifact.
# PostgreSQL — schema only, no data, deterministic for hashing.
pg_dump --schema-only -h prod-db -U readonly -d app \
  > migrations/V0__baseline.sql
sha256sum migrations/V0__baseline.sql > migrations/V0__baseline.sha256

Verify before proceeding: re-running sha256sum on the file yields the same hash committed to git.

2. Author the first forward migration as an additive, backward-compatible change. Never drop or rename in the same file; follow idempotent script design so a retried deploy is safe.

-- PostgreSQL · migration role · safe online (metadata-only on PG 11+); apply before deploying code that reads it.
-- File: migrations/V1__add_user_status.sql  (immutable once applied)
ALTER TABLE users ADD COLUMN IF NOT EXISTS status VARCHAR(32) DEFAULT 'active';

Verify before proceeding: the column exists and the runner recorded one new history row with a checksum.

3. Wire a CI collision guard. Fail the build when two unapplied migrations share a version, catching the merge problem the runner would only hit at deploy.

# Context: runs in CI on every PR; no DB access; exits non-zero on a duplicate version prefix.
dupes=$(ls migrations | sed -E 's/__.*//' | sort | uniq -d)
[ -z "$dupes" ] || { echo "Duplicate migration versions: $dupes"; exit 1; }

Verify before proceeding: deliberately add a second V1__ file and confirm the check exits non-zero.

4. Enforce checksum immutability in the deploy step. Run the migration tool in validate-then-apply mode so an edited applied file aborts the deploy rather than silently diverging.

# Context: production deploy step; migration role; non-interactive; non-zero exit blocks the rollout.
flyway validate && flyway migrate

Verify before proceeding: edit an already-applied file in a scratch branch and confirm flyway validate fails.

Verification & Observability

The repository and the database must agree. These queries confirm the history table reflects exactly the committed, ordered set of migrations — and surface drift early.

-- PostgreSQL · read-only · safe on production with a read role.
-- The applied versions and their order; compare against the sorted migrations/ directory.
SELECT version, checksum, success, installed_on
FROM flyway_schema_history
ORDER BY installed_rank;

# Context: CI drift check; read-only DB role; non-zero exit on mismatch.
# Compare committed file versions to applied versions; any difference is drift.
diff <(ls migrations | sed -E 's/__.*//' | sort) \
     <(psql "$READ_URL" -tAc "SELECT version FROM flyway_schema_history WHERE success ORDER BY 1")

If you run MySQL, confirm an applied migration actually committed (DDL there is non-transactional, so a half-applied change can still be recorded):

-- MySQL 8.0 · read-only · run after a deploy to confirm the expected column landed.
SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'users' AND COLUMN_NAME = 'status';

Rollback Path

Reverting a versioned schema is itself a versioned operation. Never git revert a merged migration and re-deploy as if it had not run — the history table still records it as applied, and the next run will see drift.

The safe path is a new forward migration that reverses the change, applied through the same runner, under the same checksum and ordering rules:

-- PostgreSQL · migration role · safe only after every app version reading the column is retired.
-- File: migrations/V2__revert_user_status.sql  (a new forward file, not an edit of V1)
ALTER TABLE users DROP COLUMN IF EXISTS status;

Safe conditions: no deployed application version still reads or writes the column, the reverse DDL is itself backward compatible with the now-current code, and the runner’s lock is free. If a migration was applied but its branch must be abandoned entirely before any other environment ran it, the only clean reset is to drop the history record and the schema change together on that one environment — which is why abandoning merged migrations is far costlier than authoring a forward reversal.

Common Errors & Fixes

Migration checksum mismatch / validation failed. Root cause: an already-applied migration file was edited after it ran. Fix: revert the edit and author a new forward migration instead. If the change was intentional and behavior-neutral, re-baseline the stored checksum (flyway repair) only after confirming the SQL semantics are unchanged.

Two migrations share a version after merge. Root cause: two branches each chose the same next version number and git merged them without a textual conflict. Fix: renumber the later-merged file to the next free version and re-run the collision check; the full procedure is in resolving migration version conflicts during merges.

Drift: a column exists in production but no migration created it. Root cause: a hand-applied hotfix outside version control. Fix: write a migration that uses IF NOT EXISTS to bring the change into history without re-applying it, then mark it resolved so the repository becomes the source of truth again.

Detected applied migration not in repository. Root cause: a migration was deployed from a branch that was later force-pushed or deleted, leaving the database ahead of the repo. Fix: restore the missing file from history, or author a baseline that subsumes it; never delete history rows to make the error disappear.

Child Page Index

This section covers the two versioning workflows engineers hit most. Git Branching Strategies for Schema Version Control covers how trunk-based and feature-branch models change how often version collisions occur and how to structure migration ownership so two teams rarely pick the same number. Resolving Migration Version Conflicts During Merges covers the concrete fix when a collision happens anyway — renumbering, re-checksumming, and verifying the merged order applies cleanly against a production-like database. Both sit inside the broader Database Migration Fundamentals and depend on the runner’s history and checksum model detailed in Migration Tool Comparison.

Frequently Asked Questions

Can I just edit a migration that already ran if I catch a bug in it? No. An applied migration is immutable; editing it breaks the runner’s checksum and, more importantly, the edit will never run on databases that already applied the original. Author a new forward migration that corrects the problem. Treat applied files as append-only history.

Why did my branches merge cleanly in git but still break the migration history? Git merges text; the runner orders versions. Two files named V5__... are different filenames, so git sees no conflict and merges both — but the runner now has two migrations claiming version 5, which collides in its history table at deploy. You need a separate collision check beyond git merge.

Should I use sequential numbers or timestamps for migration filenames? Timestamps for teams with many concurrent branches, because two engineers almost never generate the same UTC second, so collisions are rare. Sequential integers for small teams that value a readable linear history, paired with a CI check that rejects duplicate version prefixes.