State-Based vs Changelog Migrations
Two fundamentally different approaches to managing database schema changes
By the SchemaSmith Team · Last reviewed
Two fundamentally different approaches to managing database schema changes
By the SchemaSmith Team · Last reviewed
State-based tools define the desired end state and generate changes automatically. Changelog tools require developers to write each change as a sequential script. Each approach has real trade-offs around rollback, merge conflicts, new environment setup, and CI/CD integration. The right choice depends on team size, deployment complexity, and how much control you need over individual changes.
In a state-based approach, you define what the database should look like. The tool handles everything else. It compares your desired state against the live database, calculates the differences, and generates only the changes needed to bring them into alignment.
Examples of state-based tools include SchemaSmith, DACPAC/SSDT, and Atlas.
The key insight: the tool owns the "how," you own the "what." You never write migration scripts by hand for routine structural changes. You describe the target, and the tool figures out how to get there from wherever the database currently is.
In a changelog approach, you write each schema change as a numbered or timestamped migration script. These scripts run in order, building up the database incrementally from an empty state to its current form.
V003__add_orders_table.sql)Examples of changelog tools include Flyway, Liquibase, DbUp, EF Migrations, and Alembic.
The key insight: you own both the "what" and the "how." Every change is an explicit, hand-written script. The tool's job is to track which scripts have run and apply the ones that have not.
Each approach makes different trade-offs. The ten rows below cover the areas where the differences matter most in practice — rollback, drift detection, merge behavior, CI/CD fit, and the day-to-day shape of the work.
| Aspect | State-Based | Changelog |
|---|---|---|
| Schema definition | Declarative (what you want) | Imperative (what to do) |
| Change generation | Automatic (tool diffs and generates) | Manual (developer writes each script) |
| Drift detection | Built-in (compare state vs live DB) | Not available (assumes scripts are complete) |
| New environment setup | Apply current state directly | Replay all scripts from the beginning |
| Rollback | Revert to prior state definition | Write and run reverse migration script |
| Merge conflicts | Rare (metadata merges cleanly) | Common (script ordering and naming conflicts) |
| CI/CD | Idempotent (safe to run repeatedly) | Order-dependent (must track migration history) |
| Auditability | Diff between state versions | Explicit script history |
| Data migrations | Separate tooling needed | Can include data changes in scripts |
| Learning curve | Learn the definition format | Write SQL ALTER/CREATE scripts |
State-based tools tend to shine in environments where consistency across many targets matters more than fine-grained control over individual changes. The five situations below are where the declarative model pays for itself.
Changelog tools are strongest when you need explicit control over every change and a complete, ordered record of how the database evolved. The five situations below are where the imperative model is the better fit.
SchemaSmith is state-based at its core. You declare what the database should look like as structured JSON metadata, and SchemaQuench computes the delta against the target and applies only the changes needed to align it with your declared state. You describe the destination; the tool figures out the route.
For the cases where state-based falls short — data backfills, table splits, one-time data cleanups, renames that would otherwise read as drop-and-create — SchemaSmith provides migration scripts as a deliberate escape hatch. They run in named slots inside a deployment so they fire at the right moment relative to the structural changes around them, and they live in the same schema package as the JSON metadata. Because both the metadata and migration scripts ride source control, you get the audit trail changelog teams rely on: every change is a commit with a message, a reviewer, and a timestamp.
The result is a hybrid that doesn't force a choice between the two models. State-based handles the structural schema where it's strongest; migration scripts cover the genuinely imperative operations where they are. The goal is a schema package where the JSON describes the target state and migration scripts are reserved for the exceptions that truly need them.