Database Migrations: Managing Schema Changes as Version-Controlled Code

A practical guide to database migrations — the discipline of evolving a database schema through version-controlled, repeatable scripts rather than manual changes, covering migration tools (EF Core, Flyway, Liquibase, DbUp), safe schema-change patterns, zero-downtime deployments, and rollback strategy.

---

Table of Contents

1. Introduction
2. Why Migrations Exist
3. Migration Tools
4. Anatomy of a Migration
5. Safe Schema-Change Patterns
6. Zero-Downtime Migrations for Live Systems
7. Data Migrations vs. Schema Migrations
8. Rollback Strategy
9. Migrations in CI/CD
10. Team Workflow and Conflict Resolution
11. Common Pitfalls
12. Quick Reference Table
13. Conclusion

---

Introduction

A database migration is a version-controlled, ordered script that changes a database schema from one known state to the next — adding a column, creating a table, adding an index, backfilling data. Instead of connecting to production and running an ad-hoc ALTER TABLE by hand, migrations turn schema evolution into the same disciplined, reviewable, repeatable process as application code changes: committed to Git, reviewed in a pull request, and applied consistently across every environment in the same order.

-- V12__add_discount_percent_to_products.sql
ALTER TABLE products ADD COLUMN discount_percent NUMERIC(5,2);

That one file, checked into source control alongside a migration tool that knows it hasn't been applied yet, is the entire idea — the tool tracks which migrations have run, in what order, against which environment, so "what does this database's schema look like right now" is always answerable by reading the migration history rather than inspecting the live database and hoping nothing was changed out-of-band.

---

1. Why Migrations Exist

The problem with manual schema changes

Manually running ALTER TABLE statements against a database — even carefully — creates problems that compound over time:

• No record of what changed and when. Six months later, nobody can say with confidence why a column exists, or whether a specific fix was actually applied to production.
• Environment drift. Dev, staging, and production schemas quietly diverge as manual changes get applied inconsistently — a fix made directly in production but never replicated to staging, or vice versa.
• No repeatability. Standing up a new environment (a new staging instance, a developer's local database, a disaster-recovery restore) means either restoring from a backup or manually replaying every change anyone can remember making.
• No review process. A schema change that would benefit from a second pair of eyes (does this column need a default? will this index lock the table?) often just... happens, with no gate comparable to a pull request review.

What migrations provide

• A single source of truth — the sequence of migration files is the schema's history, in order, matching exactly what's been applied to any given environment.
• Reproducibility — a fresh database, brought up to date by replaying every migration in order, ends up in exactly the same schema state as any other environment that's done the same.
• Reviewability — a migration is a diff, reviewable in a pull request exactly like an application code change, before it's ever run against a real database.
• Environment consistency — the same migrations, applied in the same order, keep dev/staging/production from drifting apart silently.
• Auditability — a Git history of every schema change, tied to a commit, an author, and (ideally) a linked ticket/PR explaining why.

The core principle: the schema is code

Just as Infrastructure as Code (covered elsewhere in this series) treats infrastructure definitions as version-controlled artifacts rather than manual console clicks, migrations treat the database schema the same way — a change to the schema is a change to the codebase, subject to the same review, testing, and deployment discipline as any other code change.

---

2. Migration Tools

Different ecosystems and teams reach for different tools, but they share the same underlying model: ordered, tracked, repeatable scripts.

EF Core Migrations (C#/.NET, model-first)

dotnet ef migrations add AddDiscountPercentToProducts
dotnet ef database update

EF Core generates migrations by diffing your current C# model against its record of the last-applied migration — the migration file is auto-generated C# code (translated to SQL at apply-time), tightly coupled to the ORM's own model. Covered in depth in this series' EF Core guide; included here for completeness since it's the most common migration tool for .NET-first teams specifically.

Flyway (polyglot, SQL-first)

sql/
  V1__create_products_table.sql
  V2__add_categories_table.sql
  V3__add_discount_percent_to_products.sql

flyway migrate

Flyway is a widely used, database-and-language-agnostic migration tool — migrations are plain, hand-written SQL files, named with a strict versioned convention (V{version}__{description}.sql), applied in version order and tracked in a flyway_schema_history table it manages in your database. Because migrations are raw SQL, Flyway works identically regardless of which application language/framework sits on top of the database, which makes it a common choice for polyglot organizations or teams that specifically want SQL-first, framework-independent migrations rather than an ORM-generated abstraction.

Liquibase (polyglot, format-flexible)

<changeSet id="3" author="ada">
    <addColumn tableName="products">
        <column name="discount_percent" type="numeric(5,2)"/>
    </addColumn>
</changeSet>

liquibase update

Liquibase is conceptually similar to Flyway but supports defining changes in XML, YAML, JSON, or plain SQL — its changelog format is somewhat more abstracted from raw SQL than Flyway's approach, which can make certain changes more portable across different database engines, at the cost of an extra layer of abstraction over the SQL that ultimately runs.

DbUp (.NET, SQL-first, lightweight)

var upgrader = DeployChanges.To
    .SqlDatabase(connectionString)
    .WithScriptsFromFileSystem("Scripts")
    .LogToConsole()
    .Build();

var result = upgrader.PerformUpgrade();

DbUp is a lightweight .NET library for running versioned SQL scripts against a database — a common choice for .NET teams who want the "plain SQL files, applied in order" model (like Flyway) but as a small embeddable library rather than a separate standalone tool with its own CLI, letting migration execution be triggered directly from a deployment pipeline or even application startup code.

Choosing a tool

Scenario	Reasonable choice
.NET application already using EF Core as its ORM	EF Core Migrations — keeps schema and model changes coupled and generated together
Polyglot organization, or a strong preference for hand-written SQL	Flyway or DbUp
Need for XML/YAML changelog format, or multi-database-engine portability	Liquibase
.NET team wanting SQL-first migrations without EF Core as the ORM	DbUp

None of these are mutually exclusive with the database itself — the tool choice is about how migrations are authored and tracked, not a database-specific decision, and all of the tools above work against SQL Server, PostgreSQL, MySQL, and most other mainstream relational databases.

---

3. Anatomy of a Migration

The schema history table

Every migration tool creates and manages its own tracking table in the target database:

-- Flyway's schema history table (simplified)
SELECT version, description, installed_on, success FROM flyway_schema_history ORDER BY installed_rank;

-- EF Core's migrations history table
SELECT MigrationId, ProductVersion FROM __EFMigrationsHistory ORDER BY MigrationId;

This table is the tool's source of truth for "what's already been applied here" — on each run, the tool compares the set of migration files it finds against this table and applies only what's missing, in order.

Up and down (forward and reverse)

// EF Core
protected override void Up(MigrationBuilder migrationBuilder) =>
    migrationBuilder.AddColumn<decimal>(name: "DiscountPercent", table: "Products", nullable: true);

protected override void Down(MigrationBuilder migrationBuilder) =>
    migrationBuilder.DropColumn(name: "DiscountPercent", table: "Products");

-- Flyway "undo" migration (a separate, optional file convention)
-- U3__add_discount_percent_to_products.sql
ALTER TABLE products DROP COLUMN discount_percent;

Most tools support (or at least conventionally encourage) pairing every forward migration with a way to reverse it — though as covered in Section 7, "can technically be reversed" and "should actually be reversed in production" are different questions, especially once a migration involves data, not just structure.

Idempotency and repeatable migrations

-- Flyway "repeatable" migration (re-applied whenever its content changes, not just once)
-- R__refresh_product_summary_view.sql
CREATE OR REPLACE VIEW product_summary AS
SELECT category_id, COUNT(*) AS product_count, AVG(price) AS avg_price
FROM products GROUP BY category_id;

Most schema-changing migrations are meant to run exactly once, ever — but some tools support a distinct "repeatable" migration type for things like views, stored procedures, or seed reference data that should be re-applied whenever their content changes, rather than tracked as a one-time historical step.

---

4. Safe Schema-Change Patterns

Some schema changes are safe to apply directly; others carry real risk to a live, in-use database and deserve more care.

Additive changes: generally safe

ALTER TABLE products ADD COLUMN discount_percent NUMERIC(5,2) NULL;
CREATE INDEX idx_products_category ON products (category_id);

Adding a new, nullable column, or adding a new index, generally doesn't break existing application code that doesn't know about it yet — this is the safest, most common category of schema change, and the reason "always prefer additive, backward-compatible changes" is a recurring theme across this series (it came up for REST API evolution and for Terraform/Bicep configuration changes too — the same underlying principle applies to schema evolution).

Adding a NOT NULL column to a table with existing rows: needs care

-- ❌ Fails immediately on a table with existing rows and no default
ALTER TABLE products ADD COLUMN sku VARCHAR(50) NOT NULL;

-- ✅ Safer, three-step approach
ALTER TABLE products ADD COLUMN sku VARCHAR(50) NULL;
UPDATE products SET sku = 'UNKNOWN-' || id WHERE sku IS NULL; -- backfill existing rows
ALTER TABLE products ALTER COLUMN sku SET NOT NULL;

Adding a NOT NULL column directly to a table that already has rows fails outright unless a default is provided (and even with a default, rewriting every existing row can be a slow, locking operation on a large table in some databases) — the safer pattern is add-nullable, backfill, then constrain, as three separate, individually reviewable steps.

Renaming a column: never do it directly in one migration for a live system

-- ❌ Breaks any application code still expecting the old column name, the instant this deploys
ALTER TABLE products RENAME COLUMN name TO product_name;

-- ✅ Expand/contract pattern instead
-- Migration 1: add the new column
ALTER TABLE products ADD COLUMN product_name VARCHAR(200);
UPDATE products SET product_name = name;
-- (application code updated to write to both columns, read from the new one)

-- Migration 2 (later, after all instances are confirmed running the new code): drop the old column
ALTER TABLE products DROP COLUMN name;

A direct rename is a breaking change for any application instance still running the old code that references the old column name — during a rolling deployment, it's entirely normal for old and new application code to run simultaneously for a period, and a hard rename breaks the old instances the moment the migration applies, not when the new code deploys. See Section 5 for this "expand/contract" pattern in full.

Changing a column's type: similarly risky

-- Can silently truncate or fail on existing data depending on the database and the specific type change
ALTER TABLE products ALTER COLUMN price TYPE INTEGER;

Narrowing a type (e.g., DECIMAL to INTEGER, or shrinking a VARCHAR length) risks data loss or outright failure if existing values don't fit — always inspect existing data for compatibility before applying a type-narrowing migration, and consider the same expand/contract approach (add a new correctly-typed column, migrate data, drop the old one) for anything beyond a clearly safe widening change.

Dropping a column or table: the point of no return

ALTER TABLE products DROP COLUMN legacy_field;

Once applied (and once any point-in-time backup window for the pre-drop state has passed), this data is genuinely gone. Treat drops as the highest-risk category of migration — confirm nothing still reads the column/table (including reporting queries, other services, or BI tools that might not be obvious from the application codebase alone), and consider a "deprecate first, drop later" waiting period rather than dropping in the same release that stops using something.

---

5. Zero-Downtime Migrations for Live Systems

The core challenge: old and new code run simultaneously during deployment

During any rolling deployment (see this series' ASP.NET Core and Azure/AWS compute guides for the deployment-slot and rolling-update mechanics), there's a window where some instances are running the old application code against the database, while others are already running the new code — a schema change needs to be compatible with both versions during that window, not just the new one.

The expand/contract pattern

This is the general-purpose solution to the rename/retype problem from Section 4, and it applies to most breaking schema changes:

1. Expand — add the new schema element (column, table) alongside the old one, without removing anything. Deploy this migration first, on its own.
2. Migrate — deploy application code that writes to both the old and new schema elements (or reads from the new one with a fallback to the old), and backfill any existing data into the new shape.
3. Verify — confirm every application instance is running the dual-write/new-read code, and that data is fully backfilled and consistent.
4. Contract — once confident nothing still depends on the old schema element, deploy a final migration that removes it.

Migration 1 (expand):    ADD COLUMN product_name
Deploy code v2:          writes to both `name` and `product_name`, reads from `product_name`
Backfill:                UPDATE products SET product_name = name WHERE product_name IS NULL
Verify:                  confirm all traffic is on code v2, data is consistent
Migration 2 (contract):  DROP COLUMN name

This is more steps and more deployments than a single direct rename, but it's the difference between a migration that's safe to apply to a live, continuously-serving production system and one that causes an outage (or subtle data corruption) during the rollout window.

Long-running migrations and locking

-- Can hold a long-lived lock on a large table, blocking reads/writes for the duration
ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

Some schema changes — adding a column with a default value to a very large table, rebuilding an index, adding a constraint that requires validating every existing row — can take a long time and, depending on the specific database engine and change, hold locks that block application traffic for the duration. Increasingly, modern versions of PostgreSQL and SQL Server have optimized many of these operations to avoid full-table rewrites/locks (adding a nullable column with a constant default, for instance, is a metadata-only change in modern PostgreSQL), but it's still worth checking the specific database engine's documented behavior for a given change on a large table before assuming it's instant, especially for older engine versions or less common operations like adding a NOT NULL constraint retroactively.

Feature flags as a complementary tool

For especially risky or complex schema transitions, pairing the expand/contract pattern with an application-level feature flag — controlling whether new code paths that depend on the new schema are actually active — gives an additional, fast rollback lever that doesn't require reverting a deployment or a migration, just flipping a flag, if something looks wrong during the transition.

---

6. Data Migrations vs. Schema Migrations

Schema migrations: structural changes

ALTER TABLE products ADD COLUMN category_id INT;
CREATE TABLE order_audit_log (id SERIAL PRIMARY KEY, order_id INT, changed_at TIMESTAMPTZ);

Changes to the shape of the data — tables, columns, indexes, constraints — without necessarily touching existing row values.

Data migrations: transforming existing data

UPDATE products SET category_id = (
    SELECT id FROM categories WHERE categories.name = products.category_name
);

Data migrations backfill, transform, or clean up the values within existing rows — often paired with a schema migration (add a column, then populate it) but a genuinely distinct concern with its own risks: a data migration can silently corrupt or lose information in a way a pure schema change generally can't.

Why data migrations deserve extra caution

• They're often much slower than a schema change alone, especially on large tables — an UPDATE touching every row is a different order of magnitude of work than an ALTER TABLE ADD COLUMN.
• They can't always be cleanly reversed — once a data transformation has run and the original values have been overwritten or discarded, there may be no way back short of a full backup restore.
• They benefit from being batched on large tables, rather than one enormous UPDATE statement, to avoid extremely long-running transactions and excessive lock duration:

-- Batch the backfill in chunks rather than one massive UPDATE
UPDATE products SET category_id = /* ... */ WHERE id BETWEEN 1 AND 10000 AND category_id IS NULL;
-- repeat for the next range, and the next, checking progress between batches

Keep data migrations separate from schema migrations where practical

Bundling "add this column" and "populate every row's value for it" into a single migration file is convenient for small tables, but for large or critical tables, separating them into distinct migrations (or even distinct deployment steps entirely) gives more granular control, easier troubleshooting if the data transformation needs adjusting, and a natural checkpoint between "the schema changed" and "the data is fully migrated."

---

7. Rollback Strategy

Schema rollbacks are often genuinely harder than forward migrations

A Down migration that drops a newly-added column is technically simple to write — but if the application already wrote real data into that column before the rollback runs, reverting the schema change means discarding that data, which is rarely actually the desired outcome of a "rollback."

// This "successfully" rolls back the schema...
protected override void Down(MigrationBuilder migrationBuilder) =>
    migrationBuilder.DropColumn(name: "DiscountPercent", table: "Products");
// ...but any discount values already saved by users during the time the new code was live are now gone

Roll forward instead of rolling back, where possible

For this reason, many teams' practical rollback strategy for a problematic migration is to write and deploy a new, corrective forward migration rather than reverting to a prior schema state — "roll forward" preserves any data that accumulated in the interim and is often genuinely safer than attempting to reverse a migration that's already been running in production with real writes against it.

When a true rollback is the right call

• The migration hasn't yet been applied to production, or has been applied but no meaningful data has been written against the new schema yet (a very short window, common right after a bad deploy is caught quickly).
• The change is purely structural with no data implications (an index that turned out to be unnecessary, for instance).
• A full point-in-time database restore is an acceptable option and the data loss window is understood and accepted.

Backups as the real safety net

Regardless of a migration tool's Down support, a recent, verified backup (see this series' SQL Server and PostgreSQL guides for backup strategy specifics) remains the actual safety net for a schema or data migration that goes seriously wrong — Down migrations are a convenience for straightforward, low-stakes reversals, not a substitute for a genuine disaster-recovery plan.

---

8. Migrations in CI/CD

Applying migrations as a distinct pipeline step

# GitHub Actions
- name: Apply database migrations
  run: dotnet ef database update --connection "${{ secrets.PROD_CONNECTION_STRING }}"

As covered in this series' Background Services and EF Core guides, running migrations automatically at every application instance's startup is risky for anything beyond small, low-traffic systems — with multiple instances starting concurrently during a rolling deployment, you risk several instances attempting to apply the same migration simultaneously. The more robust pattern is a single, dedicated migration step in the deployment pipeline, run once, before the new application version starts receiving traffic.

Migration ordering relative to code deployment

Given the expand/contract discipline from Section 5, the deployment order matters:

1. Deploy the "expand" migration (new schema element added, old one untouched)
2. Deploy new application code (writes to both, reads from new)
3. Run the data backfill
4. Verify
5. Deploy the "contract" migration (old schema element removed) — often in a later, separate release

Migrations generally need to run before the application code that depends on them is deployed (so the schema is ready when the new code starts expecting it), while destructive/contracting migrations should run only after confirming no running code still depends on what's being removed.

Testing migrations before they hit production

# Apply migrations against a fresh copy of the schema (or a recent production snapshot) in CI
dotnet ef database update --connection "$TEST_CONNECTION_STRING"

Running every pending migration against a test database (ideally seeded with a realistic data volume, not an empty schema) as part of CI catches a meaningful class of problems — a migration that works fine against an empty table but times out or locks unacceptably against a production-sized one — before they're discovered the hard way in production.

Approval gates for production schema changes

For higher-stakes environments, some teams add an explicit manual approval step between "migration reviewed and merged" and "migration actually applied to production" — similar in spirit to the terraform plan/what-if review pattern covered in this series' Terraform/Bicep guide, giving a human a last look at exactly what SQL is about to run against the production database.

---

9. Team Workflow and Conflict Resolution

Migration ordering conflicts

When two developers each create a migration independently from the same starting point, both might generate a migration intended to be "next" — merging both branches can produce two migrations that both claim the same version number/order, or migrations that conflict with each other's assumptions about the schema's current state.

# Developer A creates: 20260715120000_AddDiscountPercent.cs
# Developer B creates: 20260715130000_AddSkuColumn.cs (independently, same day)

Most tools order migrations by timestamp or an explicit sequence number generated at creation time, which usually resolves this automatically as long as migrations are generated close to when they're committed (not held in a long-lived branch and generated much earlier) — but it's still worth regenerating/rebasing a migration if a long time has passed between creating it locally and actually merging it, to catch any conflicting assumptions about the schema's current state.

Never edit an already-applied migration

// ❌ Don't modify a migration that's already been applied to any shared environment
// (dev, staging, or production) — its content is now part of the historical record
// other environments have already executed

Once a migration has been applied anywhere other than your own local, throwaway database, treat it as immutable — editing it means environments that already ran the old version are now out of sync with anyone who runs the edited version fresh, since the migration tool has no way to know the file's content changed after being marked as applied. If a migration needs correcting after being shared, write a new, corrective migration instead (the same "roll forward" principle from Section 7 applies here too).

Keeping local development databases in sync

dotnet ef database update  # brings a local dev database up to the latest migration

A quick, low-friction way for every developer to bring their local database schema up to date with the latest merged migrations is essential for a smooth team workflow — friction here (a developer working against a stale local schema, hitting confusing errors unrelated to their actual change) is a common, avoidable source of wasted time.

---

10. Common Pitfalls

Pitfall	Why it hurts	Better approach
Manual, undocumented changes made directly in production	Breaks the "migrations are the source of truth" model entirely, causes drift	Every schema change goes through a migration, no exceptions, including emergency hotfixes
Renaming/dropping columns directly in one step	Breaks old application instances still running during a rolling deployment	Use the expand/contract pattern (Section 5) for anything beyond additive changes
Editing an already-applied migration	Desyncs environments that already ran the original version	Write a new corrective migration instead
Running migrations automatically on every instance's startup	Risk of concurrent migration attempts during a rolling deployment	Run migrations as one distinct, single pipeline step before new instances start serving traffic
Bundling a massive data backfill into the same migration as a schema change	Long lock duration, hard to troubleshoot or resume if interrupted	Separate schema and data migrations; batch large data migrations
No testing of migrations against realistic data volume	A migration that's instant on an empty table can lock/timeout against a production-sized one	Test migrations in CI against a database seeded with a representative row count
Treating Down migrations as a full rollback safety net	Data written after the migration applied is lost on rollback	Prefer rolling forward with a corrective migration; rely on backups for genuine disaster recovery
No review process for schema changes	A risky change (a long-locking index build, a destructive drop) ships without a second pair of eyes	Review migrations in a pull request exactly like application code, before merging

---

Quick Reference Table

Concept	Purpose
Schema history table	The tool's record of which migrations have been applied, and in what order
Up/Down (or forward/undo)	Applying a change vs. reversing it
Additive change	Generally safe: new nullable column, new index, new table
Expand/contract	Multi-step pattern for safely renaming/removing schema elements on a live system
Data migration	Transforming existing row values, distinct from structural schema changes
Roll forward	Fixing a bad migration with a new corrective migration, rather than reverting
Idempotent/repeatable migration	Re-applied whenever its content changes (views, seed data), not a one-time step
CI migration testing	Catching locking/timeout issues against realistic data volume before production
Migration as a distinct pipeline step	Avoids concurrent-application races during a rolling deployment

---

Conclusion

Database migrations turn schema evolution from an ad-hoc, easily-drifted manual process into version-controlled, reviewable, repeatable code — the same discipline this series has advocated for application code, infrastructure (Terraform/Bicep), and everything in between, applied to the one part of a system that's often hardest to safely change after the fact: the shape of the data itself.

The tooling (EF Core, Flyway, Liquibase, DbUp) matters less than the underlying discipline: prefer additive, backward-compatible changes; use the expand/contract pattern for anything that would otherwise break code running during a rolling deployment; separate risky data transformations from simple structural changes; and treat "roll forward with a corrective migration" as the default recovery strategy, with real backups as the actual safety net for when something goes seriously wrong. Get those habits right, and schema evolution becomes a routine, low-drama part of shipping software — rather than the thing everyone's quietly afraid to touch.

---

Found this useful? Feel free to star the repo, open an issue with corrections, or share the rename-gone-wrong that taught you to respect the expand/contract pattern.