Database Migrations vs Desired State: Two Models, Different Tradeoffs

Shannon Lowder

Shannon Lowder

— 3 min read

By 2014, the two camps in database deployment tooling were well-defined. In one corner: migration-based tools — Flyway, Liquibase, DbUp — where you write numbered scripts representing each change and the tool tracks which ones have been applied. In the other: desired-state tools — SSDT dacpac model, ReadyRoll — where you describe the target state and the tool generates the migration. I'd used both extensively by this point and had opinions. Here they are.

The Migration Model: How It Works

In a migration-based system, every schema change is a versioned script:

-- V001__create_customers_table.sql
CREATE TABLE dbo.Customers (
    CustomerId   INT           NOT NULL IDENTITY PRIMARY KEY,
    CustomerName NVARCHAR(200) NOT NULL
);

-- V002__add_email_to_customers.sql
ALTER TABLE dbo.Customers
    ADD Email NVARCHAR(256) NULL;

-- V003__make_email_not_null.sql
UPDATE dbo.Customers SET Email = 'unknown@placeholder.com' WHERE Email IS NULL;
ALTER TABLE dbo.Customers ALTER COLUMN Email NVARCHAR(256) NOT NULL;

A migration history table in the database tracks which scripts have run. Deploy to a fresh environment: all scripts run in order. Deploy to an existing environment: only the unapplied scripts run. Simple and explicit.

The Desired State Model: How It Works

In a desired-state system, you maintain the current schema definition:

-- dbo.Customers.sql (current desired state)
CREATE TABLE dbo.Customers (
    CustomerId   INT           NOT NULL IDENTITY PRIMARY KEY,
    CustomerName NVARCHAR(200) NOT NULL,
    Email        NVARCHAR(256) NOT NULL
);

Deploy by comparing this definition to the target and generating the needed changes. Fresh environment: generates and runs the full CREATE. Existing environment: generates only the ALTER statements needed to bring the target into alignment.

Where Migration Wins

Data transformations with complex logic. The migration for V003 above — backfill the Email column before making it NOT NULL — can't be expressed as a state difference. The desired-state tool knows the column should be NOT NULL, but it doesn't know the data migration needed to get there safely. For any change where the data needs to move with the schema, migration scripts win cleanly.

Audit trails for compliance. Some compliance requirements need a record of each schema change as an explicit, immutable artifact. Migration scripts are that record. A desired-state deployment generates the delta at deploy time — you can capture the generated script, but it's generated, not authored. For auditors who want to see what was changed and when it was deployed, migration scripts are a cleaner answer.

Teams already using them well. Switching deployment tooling in a production environment has a real cost. If a team has good Flyway or Liquibase discipline and it's working, the improvement from switching to a desired-state model has to outweigh the disruption. Often it doesn't.

Where Desired State Wins

Current schema readability. In a migration-based system, understanding the current state of a table requires reading all the migrations that touch it. In a desired-state system, the file is the current state. Code review of a schema change shows the final state of the object, not just the delta. For anyone who needs to understand what the schema looks like right now, desired state is much faster to read.

Deploying to fresh environments. A migration-based system deploying to a fresh environment needs to replay every migration from the beginning. If V001 through V847 are all still present (and they are, because you don't delete migration history), the fresh deployment runs all 847 scripts. In theory this is fine; in practice you'll find scripts that depended on data that no longer exists, scripts that were written before a convention was established, and other archaeology. A desired-state dacpac deploys the current schema in one operation, regardless of how it evolved.

Drift detection. Comparing current state to the model is natural in a desired-state system. Doing the equivalent with migrations — "does the current database match what all migrations would produce?" — is complex and rarely done.

The Hybrid I Used in Practice

SSDT for structural schema changes: tables, indexes, views, stored procedures. When the change was purely structural — add a column, change a data type, add an index — the desired-state model handled it correctly and the readable schema in the project was worth having.

Explicit data migration scripts alongside SSDT for any change involving data movement. These ran as part of the deployment pipeline, before or after the dacpac apply as needed, and were tracked separately.

This hybrid is more complex to explain than "pick one tool," but it's more honest about what each model is good at. Neither tool solves the whole problem. Knowing which to reach for when is the actual skill. As always, I'm here to help.

Read more