Declarative vs Imperative Migrations
Two fundamentally different ways to manage database schema — declare the end state and let the tool generate changes, or write each change as an explicit migration script.
Two fundamentally different ways to manage database schema — declare the end state and let the tool generate changes, or write each change as an explicit migration script.
Declarative (state-based): you describe what the schema should look like, and the tool figures out how to get there. Imperative (migration-based): you write explicit scripts for each change, and they apply in sequence. Both approaches work; they trade off in different places — team workflows, CI/CD shape, drift handling, rollback authoring, and how new environments come up.
The fundamental difference between the two approaches.
| Aspect | Declarative (State-Based) | Imperative (Migration-Based) |
|---|---|---|
| What you define | Desired end state | Steps to get there |
| Change generation | Tool computes the diff automatically | Developer writes every change |
| Drift handling | Compare and sync anytime — same state, same result | Track applied migrations and reconcile by hand |
| Environment parity | Natural — declared state defines all environments | Requires migration-history discipline |
| Merge conflicts | Minimal — state files merge cleanly in source control | Common — numbered scripts collide on parallel branches |
| New environment setup | Apply current state directly | Replay every migration from baseline |
| Rollback approach | Re-deploy previous state* | Write reverse migrations for every change |
| CI/CD model | Idempotent — same pipeline produces the same result whether run once or ten times | Order-dependent — the migration history table tracks what has been applied |
*Schema rollbacks happen by re-applying the prior release’s declared state. Data preservation (e.g., retaining values from a dropped column) is handled with user-written migration scripts inside the same package.
“Here’s what the database should look like.”
You declare the desired end state:
{
"table": "Users",
"columns": [
{ "name": "Id", "type": "int" },
{ "name": "Email", "type": "nvarchar(255)" },
{ "name": "CreatedAt", "type": "datetime2" }
]
}The tool compares this to the live database and generates the required ALTER / CREATE statements automatically.
Examples: SchemaSmith, DACPAC / SSDT, Redgate SQL Compare, Atlas.
“Here’s how to change the database.”
You write explicit migration scripts:
-- V001_CreateUsers.sql
CREATE TABLE Users (
Id int PRIMARY KEY,
Email nvarchar(255)
);
-- V002_AddCreatedAt.sql
ALTER TABLE Users
ADD CreatedAt datetime2;Scripts apply in sequence; a history table tracks what has run.
Examples: Flyway, Liquibase, DbUp, Entity Framework Migrations.
ALTER / CREATE by handSchemaSmith is built around a declarative state-based core because that approach matches how most teams actually want to work in CI/CD. The benefits we ship for free:
SchemaSmith also supports user-written migration scripts inside the same package — so the rare cases where imperative is the right answer (data transformations, careful column moves, complex backfills) live alongside the declarative state without forcing the whole workflow onto migration scripts.
Pricing and feature data last verified May 2026. Competitor information may change.