Git for Database Work
Version control for a schema change is not quite the same activity as version control for application code, because a merged migration file describes a change to a stateful system that git itself cannot roll back.
This page builds the mental model for that difference on PostgreSQL 18.4 teams: why two separate ledgers exist, how they stay reconciled, and why that reconciliation drives almost every git convention a database team adopts.
Summary
- Core Idea: Git and the database each keep their own ledger of schema history, and the whole discipline of "git for database work" is about keeping those two ledgers reconciled.
- Why It Matters: A migration file, unlike an application code change, has already had an irreversible side effect on a live system the moment it merges and runs.
- Key Concepts: dual ledger, migration immutability, deploy ordering, expand-contract, drift detection.
- When to Use: Reach for this model whenever you are designing branch policy, writing a PR template, or debugging why staging and production schemas disagree.
- Limitations / Trade-offs: No amount of git discipline substitutes for an actual restore drill, since git only proves intent was recorded, not that the change is safe.
- Related Topics: code review for SQL, expand-contract pattern, migration runners.
Foundations
Every team doing schema work is actually maintaining two separate records of history at the same time.
The first ledger is git, which records the intent to change the schema as a reviewed, ordered sequence of commits.
The second ledger is the database's own migration history table - flyway_schema_history or its Liquibase equivalent - which records what has actually been applied, and in what order, on each specific environment.
These two ledgers agree when every environment has run every migration that git has merged, in the same order, and they disagree the moment someone runs SQL by hand outside that process.
A useful analogy is a shipping manifest versus a warehouse's physical inventory: the manifest says what should be there, the warehouse says what actually is, and a mismatch between them is where incidents live.
Because a migration changes live state the instant it runs, a merged migration file is treated as effectively immutable - the fix for a mistake is always a new forward migration, never an edit to history that other environments have already applied.
This is the single biggest mental shift from application-code git habits, where amending or rebasing a recent commit is routine and mostly harmless.
Mechanics & Interactions
The reconciliation between the two ledgers is what shapes almost every convention in this section, starting with branch policy.
main is kept "always migratable," meaning every migration merged there has already passed on staging, so a broken migration on main is treated as a deploy-blocking incident rather than a normal bug.
Deploy order is a direct consequence of the dual-ledger model: the migration must apply before the application code that depends on the new column or table ships, or the running app will query a schema that does not exist yet.
The expand-contract pattern exists specifically because git commits and a live schema change can never land atomically together - expand adds the new shape while the old shape still works, and contract removes the old shape only after every consumer has moved.
Code review on a migration PR carries more weight than review on typical application code, because the reviewer is the last checkpoint before a change with real side effects reaches a live system.
Drift detection - comparing the migration history table across environments - is the mechanism that catches the moment someone bypasses git and runs SQL directly against a database.
The snippet below is the kind of query a reviewer runs to confirm the two ledgers still agree before approving the next migration in sequence.
SELECT installed_rank, version, description, success
FROM flyway_schema_history
ORDER BY installed_rank DESC
LIMIT 5;If the most recent row on production doesn't match what git says was last merged, the two ledgers have already drifted apart.
Advanced Considerations & Applications
At scale, the dual-ledger model has to answer a question application-code git rarely faces: who owns the next version number when multiple PRs are in flight at once.
A shared-schema monorepo typically assigns a platform DBA or an automated bot to allocate the next migration version, precisely to prevent two PRs from colliding on the same slot.
Database-per-service architectures sidestep that collision entirely by giving each service its own migration sequence, at the cost of losing a single, cluster-wide view of schema history.
Squashing history is safe for pre-production baselines but dangerous past that point, because squashing after a migration has already applied on any real environment breaks the checksum that the migration runner uses to detect tampering.
Emergency hotfixes complicate the model further: an operator running SQL by hand at 3am to stop an outage is, by definition, breaking the dual-ledger reconciliation, which is why the convention is to commit that same SQL as a proper migration within hours, not days.
As teams mature, the git workflow itself becomes the audit trail regulators and security reviewers actually trust, since it ties every DDL statement to a ticket, an author, and a reviewer in a way an ad hoc production shell session never can.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
| Forward-only migrations with documented rollback SQL | Simple mental model, git history stays linear | Rollback SQL is written but rarely rehearsed until needed | Most application teams shipping incrementally |
| Down migrations applied automatically | Rollback is a single command | Down scripts drift from reality just as easily as forward ones, and rarely get tested | Teams with strong CI discipline enforcing down-migration tests |
| Manual prod DDL, committed after the fact | Fastest response during a live incident | Ledgers are out of sync until the commit lands, and that window is real risk | Emergency-only, never a standing practice |
Common Misconceptions
- "A migration PR can be amended or rebased like normal code" - once any environment has applied it, editing the file breaks checksum verification on every environment that already ran the old version.
- "The git history alone proves what's on production" - only the database's own migration history table proves what has actually applied; git only proves what was intended.
- "Squashing commits is always safe cleanup" - squashing is safe before production has applied those migrations, and unsafe afterward, because it rewrites history the migration runner depends on.
- "Rollback SQL means you can always revert cleanly" - a documented down-migration is a best effort, not a guarantee, especially once data has been written against the new shape.
- "App deploy and migration deploy order don't really matter" - deploying application code ahead of its migration is one of the most common causes of a production outage in schema-change workflows.
FAQs
Why does database work need a different git discipline than application code?
Because a merged migration has already had an irreversible side effect on a live system the moment it runs, unlike an application code deploy that can usually just be rolled back.
What are the "two ledgers" this page keeps referring to?
- Git, which records the reviewed intent to change the schema.
- The database's own migration history table, which records what has actually applied on each environment.
Can I edit a migration file after it's merged?
Not once any environment has applied it - the safe fix is always a new forward migration, since editing breaks checksum verification elsewhere.
Why does deploy order matter so much for database changes?
Because application code that assumes a new column or table exists will fail if it deploys before the migration that creates it has actually run.
What is expand-contract, conceptually?
A pattern for changing schema in two safe steps - add the new shape while the old one still works, then remove the old shape only after every consumer has moved - because git and a live schema can't change atomically together.
Who should own the next migration version number on a shared schema?
Usually a platform DBA or an automated allocator, specifically to prevent two in-flight PRs from colliding on the same version slot.
Is it ever okay to run SQL directly on production outside of git?
Only as a genuine emergency measure, and even then the same SQL should be committed as a proper migration within hours to restore ledger reconciliation.
When is squashing migration commits safe?
Only before any real environment has applied those migrations - squashing after that point breaks the checksum the migration runner relies on.
Does forward-only or down-migration policy matter more than picking one?
Consistency matters more than the specific choice - what breaks teams is switching policies mid-project without updating every existing migration's assumptions.
How do you detect that the two ledgers have drifted apart?
By comparing the migration history table's most recent applied version against what git says was last merged on that branch.
Why does code review matter more for a migration PR than a typical app PR?
Because the reviewer is the last checkpoint before a change with real, often irreversible, side effects reaches a live system.
Does a database-per-service architecture change this model?
It removes the version-collision problem by giving each service its own sequence, at the cost of a single cluster-wide view of schema history.
Related
- Git for Database Work Basics - the concrete folder layout and PR workflow this model underlies.
- Code Review for SQL - the review checklist that enforces this page's ledger-reconciliation discipline.
- Migrations Basics - the migration runner mechanics referenced throughout.
- Zero Downtime DDL - the expand-contract pattern in practice.
- Migration Safety Skill - an agent skill that scores this page's lock-risk concerns.
Stack versions: This page is conceptual and targets PostgreSQL 18.4 teams; specific migration-runner versions (Flyway, Liquibase) are covered on their dedicated pages in the migrations-schema-change section.