Versioning, Rollback, and Database Changes in Git

Learning outcomes

Database changes are software changes. They should be reviewed, versioned, tested, deployed, observed, and traceable. Keeping migration scripts in Git creates a durable history of how the schema reached its current state and makes application/database changes part of one delivery process.

Migrations belong in Git

migrations/  20260810_001_add_priority.sql  20260810_002_add_priority_index.sql  20260811_001_backfill_priority.sql

Exact naming depends on the migration framework, but order and identity must be deterministic.

Immutable migration history

Once a migration has run in shared/prod environments, avoid editing it in place. Add a new migration that corrects or extends the old one. Otherwise different environments may claim the same version while having different schemas.

Migration metadata table

Most frameworks maintain something like:

schema_migrations(  version,  applied_at,  checksum)

This identifies which changes have been applied.

Schema version is not application version

Application release 4.7 may work with schema versions 120–124 during a compatibility window. Avoid designing a system where every app release requires one exact database version at all times.

Rollback is not always possible

Adding a nullable column can often be reversed safely. Dropping a column after deleting data cannot be reversed without backup/restoration. Data transformations may be irreversible.

Prefer roll-forward for many failures

If migration 203 introduces a bad index, migration 204 can drop or replace it. Roll-forward preserves audit history and avoids pretending destructive data changes can be undone automatically.

When rollback is appropriate

• migration has not changed irreversible data;
• old application remains compatible;
• rollback script is tested;
• operational cost is understood.

Backup is not rollback

Restoring a database backup may lose all legitimate writes after the backup point. It is a disaster-recovery tool, not a routine substitute for migration planning.

Data migrations need explicit reversibility

Transform:

full_name -> first_name + last_name

may be lossy for names that do not split cleanly. A rollback cannot reconstruct original data unless it was preserved.

Migration review checklist

• Does it lock a large table?
• Does it rewrite rows?
• Can old application versions still run?
• Is there a supporting index?
• Is backfill batched?
• Is validation explicit?
• What is the failure mode?
• What is the cleanup phase?

CI tests

Automate:

1. create database from zero using all migrations;
2. upgrade from a recent production-like snapshot;
3. run application integration tests;
4. verify schema checksum/expected structure;
5. run migration linting/safety rules.

Migration linting

CI can flag risky operations such as:

DROP COLUMNSET NOT NULL immediatelyCREATE INDEX without online modetype rewrite on huge tableunbounded UPDATE

Not every flagged operation is forbidden; it requires conscious review.

Environment drift

Developers manually changing production DDL creates drift from Git history. Prefer controlled migrations and detect unexpected schema differences.

Branch conflicts

Two feature branches can both create migration “205.” Migration frameworks need a deterministic ordering/merge strategy. Resolve conflicts before deployment.

Feature code and migration sequencing

A pull request can include:

migration A: expand schemaapplication code: dual-read/writemigration B: later cleanup

But destructive cleanup often belongs in a later release after compatibility is proven.

Migration ownership

Every production migration should have:

• author/owner;
• reviewer;
• deployment window if needed;
• monitoring plan;
• stop conditions;
• follow-up cleanup issue/task.

WorkshopHub Git example

db/  migrations/    V141__add_service_region.sql    V142__add_service_region_index.sql    V143__validate_service_region_fk.sql  backfills/    service_region_backfill.py  checks/    service_region_reconcile.sql

The repo contains not just DDL, but operational tooling and validation.

Release notes

For significant schema changes, document:

new columns/tablescompatibility windowrequired app versionbackfill statusnew constraintscleanup pendingrollback/roll-forward plan

Practice: irreversible migration

Chapter 16 synthesis

safe schema evolution  = compatible expansion  + application migration  + bounded backfill  + validation  + constraint enforcement  + delayed contraction  + versioned history

Summary and next chapter

Chapter 16 turned schema change into an engineering discipline. You can now design compatible evolution, use expand-and-contract, backfill and validate large tables, deploy with minimal downtime, and version changes in Git with realistic rollback/roll-forward plans. Chapter 17 moves into security, privacy, governance, tenant isolation, auditability, retention, lineage, and data quality.

References

• Pramod Sadalage and Scott Ambler, Refactoring Databases.
• Martin Fowler, evolutionary database design and parallel change.
• Martin Kleppmann, Designing Data-Intensive Applications.
• Migration-framework and DBMS documentation for production-safe schema changes.