Schema Versioning & Migrations
The database schema changes while the system is live and holding real customer data. An old version of the code may still be running next to the new one. Migrations let you change a live system safely. They must be versioned, ordered, reversible in intent, and safe to run on production-sized data.
A migration is code, and it is some of the most important code you will write. It runs once, against data you cannot replace, often under deployment pressure. Treat every schema change as a versioned, reviewed migration. By default it should be forward-only. Test it against realistic data. Design it so a deploy can never break either the old or the new application version.
We use Dapper, not an ORM that migrates from models. So our schema changes are explicit SQL migrations in source control. This is an advantage, because the change is exactly what gets reviewed. But it means the care is entirely on us. Nothing generates or checks these migrations for free.
Version every change
- AlwaysExpress every schema change as a migration in source control. Keep it ordered, unchangeable once applied, and applied the same way in every environment.
- DoMake migrations idempotent or guarded so re-running is safe. Test them against a production-like dataset, not an empty schema.
- DoKeep migrations small and focused, one logical change each, so failures are easy to find and understand.
- DoRun migrations through the pipeline as part of deployment, with the same review and gates as application code.
- ConsiderA documented rollback or compensating migration for anything risky, and a backup and restore plan before destructive changes.
- NeverEdit a migration that has already run in a shared environment. Write a new one instead. Rewriting history corrupts state.
Deploy without downtime or data loss
- DoUse expand and contract for breaking changes. Add the new shape, backfill it, move readers and writers across, then remove the old shape in a later release. Never do it all at once.
- DoKeep each step backward-compatible, so the running app version keeps working while the migration is in progress.
- DoPlan large data backfills to run in batches that do not lock tables or starve production traffic.
- ConsiderMaking column drops and renames a two-release process: stop using it first, then drop it. A rollback then never hits a missing column.
- Do notAssume a migration that is instant on a dev database is instant on production. Test against real volumes and watch for locking.
- NeverHard-delete or drop columns or tables holding regulated data without confirming retention obligations and taking an audited backup.
EXEC sp_rename 'Customers.Surname', 'LastName';
The moment this runs, every still-deployed instance of the old code that reads Surname breaks. A rename looks like a small change, but it is a breaking one.
-- release 1: add the new column, backfill it, dual-write in the app
ALTER TABLE Customers ADD LastName nvarchar(200) NULL;
UPDATE Customers SET LastName = Surname WHERE LastName IS NULL;
-- release 2 (after every instance uses LastName): drop Surname
At no point does a running app version need a column the database does not have. Rollback stays safe throughout.
Self-review checklist
- AskIs this change a versioned migration that runs the same way everywhere?
- AskWill the running app version still work while this migration is applied?
- AskHave I tested this against production-sized data, and will it lock anything?
- AskIf this deploy is rolled back, does the database still match a working app version?