Declarative Schema Management

In a versioned workflow you write the steps: migration 41 adds a column, migration 42 backfills it, migration 43 adds the index. In a declarative workflow you write the destination — the CREATE TABLE statements or HCL describing what the schema should look like — and a tool compares that desired state with the live database and computes the DDL to get there. Atlas, Skeema, sqldef, and the db push modes of several ORMs all work this way. The appeal is obvious: no ordering conflicts between branches, no long chain of files to replay, and a schema definition you can read in one place. The risk is equally obvious to anyone who has run migrations under load: the tool decides which statements to run, and a diff engine has no idea that renaming a column is not the same as dropping one and adding another, that a unique index needs CONCURRENTLY, or that the application fleet still reads the column it is about to remove. This part of Database Migration Fundamentals covers how to keep the convenience of desired-state schema management while retaining the control that zero-downtime patterns require. It serves platform teams choosing a schema workflow, and the engineers who review the plans those tools produce.

The short answer is that the plan, not the desired state, is what you review and approve. A declarative tool is a DDL generator; everything the rest of this site says about lock classes, transactional boundaries and expand-and-contract applies to the statements it generates.

Concept & Mechanism

Every declarative tool performs the same three operations. It inspects the target database by reading the system catalog (information_schema and pg_catalog in PostgreSQL, information_schema in MySQL) and builds an in-memory model of tables, columns, indexes, constraints and, depending on the tool, views, functions and triggers. It normalises the desired-state files into the same model — Atlas and Skeema do this by actually executing the desired DDL against a scratch “dev database”, so defaults, type aliases and generated names come out exactly as the engine would produce them. Then it diffs the two models and emits an ordered list of DDL statements that transforms one into the other.

The diff is purely structural. It knows that orders.region exists in the database and not in the file, so it emits DROP COLUMN region. It cannot know that orders.fulfilment_region in the file is the same data under a new name, so a rename becomes a drop plus an add, losing every value in the column. It does not know how many rows a table has, how hot it is, or which application versions are running, so it has no basis for choosing CREATE INDEX CONCURRENTLY over CREATE INDEX, or for splitting SET NOT NULL into a validated CHECK first. Some tools let you configure those choices globally — Atlas can be told to create and drop PostgreSQL indexes concurrently, Skeema can route ALTER TABLE through an external online schema change tool — but none of them can decide when a contract step is safe, because that depends on deploy state outside the database.

That boundary defines the workable model. Declarative tools are excellent at additive changes and at telling you what differs. For changes that need sequencing across deploys, you either use the tool’s escape hatches — generating a versioned migration file from the diff and editing it, as described in combining declarative diffs with versioned migration files — or you stage the desired state itself across several commits, so that each individual diff is one safe step of an expand-and-contract sequence.

Prerequisites & Decision Criteria

Declarative management fits some teams and schemas far better than others. Decide deliberately, using the criteria below, rather than drifting into it because an ORM offers db push.

If you adopt a declarative tool, these guardrails are non-negotiable:

• Destructive statements (DROP TABLE, DROP COLUMN, DROP INDEX on a unique index) are blocked by default and require an explicit, per-change override.
• Plans are generated in CI against a scratch dev database of the same major version and posted to the pull request for review.
• The tool is configured to use online forms: concurrent index builds on PostgreSQL, ALGORITHM=INPLACE, LOCK=NONE or an online schema change wrapper on MySQL.
• The apply step runs with lock_timeout or lock_wait_timeout, exactly like any other migration, per DDL Lock Management & Timeouts.
• A scheduled job compares production with the desired state and alerts on drift.

The versioned vs state-based migrations comparison covers the trade-off in more depth, including hybrid models.

Step-by-Step Procedure

1. Pin a dev database for normalisation. Declarative tools need a scratch database to compile the desired state. Use a disposable container of the same engine and major version as production so type names and defaults normalise identically. Verify by running a no-op plan against production immediately after adoption: it must be empty.

# Shell · CI job with Docker available · Atlas CLI installed
# WARNING: --dev-url must point at a disposable database; Atlas creates and drops objects in it.
atlas schema diff \
  --from "postgres://readonly:***@prod-replica:5432/app?sslmode=require" \
  --to   "file://schema.sql" \
  --dev-url "docker://postgres/16/dev"
# expected on day one: "Schemas are synced, no changes to be made."

2. Configure safe defaults before the first real change. Tell the tool to skip destructive changes and to prefer online DDL. In Atlas this lives in the project file:

# HCL · atlas.hcl · checked into the repository
# WARNING: skip rules hide drops from the plan; drops then require a deliberate, separate change.
env "prod" {
  src = "file://schema.sql"
  dev = "docker://postgres/16/dev"
  diff {
    skip {
      drop_schema = true
      drop_table  = true
    }
    concurrent_index {
      create = true
      drop   = true
    }
  }
}

3. Generate the plan in CI and post it for review. Run the diff against a read-only connection to production (or a fresh snapshot) and publish the SQL. Reviewers approve the SQL, not the schema file. Verify that the plan contains only statements you would have written by hand.

4. Lint the plan. Run the tool’s analyzers or a separate linter over the generated statements to catch destructive, data-dependent and blocking changes; the guide on preventing destructive changes in declarative diffs shows the rules to enable.

5. Apply the approved plan, not a fresh diff. Between review and apply, production may have changed. Either apply the exact reviewed plan (Atlas supports pre-planned applies; with others, save the SQL and run it) or make the apply step abort if the freshly computed plan differs from the reviewed one.

# Shell · deploy job · migration role credentials
# WARNING: --auto-approve is only acceptable because the plan was reviewed and linted in step 3-4.
atlas schema apply --env prod \
  --url "postgres://migrator:***@prod-primary:5432/app?sslmode=require" \
  --dry-run > plan.sql
diff -u reviewed-plan.sql plan.sql || { echo "plan changed since review"; exit 1; }
atlas schema apply --env prod \
  --url "postgres://migrator:***@prod-primary:5432/app?sslmode=require" \
  --auto-approve

6. Stage contract steps across releases. When a change needs expand-and-contract, change the desired state in stages: first add the new structure and ship; after the application stops using the old structure, remove it from the file in a later pull request, with the drop explicitly allowed for that one change.

Verification & Observability

After every apply, re-run the diff. An empty plan proves the database now matches the desired state; a non-empty plan means something failed partway or another actor changed the schema during the deploy.

# Shell · post-deploy check · read-only credentials
atlas schema diff \
  --from "postgres://readonly:***@prod-primary:5432/app" \
  --to "file://schema.sql" --dev-url "docker://postgres/16/dev" | tee post-apply.diff
test "$(grep -c 'no changes' post-apply.diff)" -eq 1 || exit 1

For MySQL with Skeema the equivalent is skeema diff production, which exits with status 0 when there are no differences and 1 when differences exist, which makes it convenient as a CI assertion. Observe the apply itself the same way as any migration: watch lock waits with pg_blocking_pids() or performance_schema.metadata_locks, and alert on replication lag for any statement that rewrites a table. A scheduled drift check — the same diff run hourly against production — is the declarative world’s version of a checksum audit, and it is covered in detecting production schema drift against a desired state.

Rollback Path

Declarative tools make rollback look trivial — revert the schema file and apply again — and that is exactly the trap. Reverting a file that added a column produces a plan that drops the column, including any data written since. Reverting a file that dropped a column produces a plan that adds an empty column; the data is gone. The rollback plan must be reviewed as carefully as the forward plan.

# Shell · incident response · generates, does not apply, the reverse plan
# WARNING: review for DROP statements — a reverted file often means deleting new data.
git revert --no-commit HEAD -- schema.sql
atlas schema apply --env prod --url "$READONLY_URL" --dry-run > rollback-plan.sql
grep -nE "DROP (TABLE|COLUMN)" rollback-plan.sql && echo "rollback destroys data: escalate"

Rollback is safe when the reverse plan is purely additive or drops only objects that hold no data yet. Otherwise roll forward: fix the desired state and apply a new plan. Pipelines that automate this decision are described in Rollback Automation.

Common Errors & Fixes

Plan contains DROP COLUMN and ADD COLUMN for what was meant to be a rename. Root cause: diff engines cannot infer renames. Fix: do not rename in the desired state; perform the rename with an expand-and-contract sequence, as in renaming a column with expand and contract, updating the desired state at each step.

Non-empty plan immediately after adoption. Root cause: normalisation differences — a dev database of a different major version, a missing extension, collation defaults, or objects created outside the tool. Fix: match the dev database version and extensions to production, and add intentionally unmanaged objects to the tool’s exclude list.

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Root cause: the tool wraps the plan in a transaction while concurrent index creation is enabled. Fix: use the tool’s option to run such statements outside a transaction, or apply index changes as a separate, non-transactional step.

Skeema refuses to push with Unsafe changes detected. Root cause: the diff includes a destructive or column-modifying change, which Skeema blocks by default. Fix: this is the guardrail working. Review the change; if it is intended, allow it for that single push with --allow-unsafe, or configure safe-below-size so small tables are not blocked.

Child Page Index

Five guides take this topic into specific tools and failure modes. Planning safe diffs with Atlas schema apply walks through a PostgreSQL workflow from dev-database setup to a reviewed apply. Preventing destructive changes in declarative diffs covers the skip rules, lint analyzers and review gates that stop a missing line in a schema file from dropping a table. MySQL teams should read managing MySQL schemas with Skeema, including routing large changes through gh-ost. Combining declarative diffs with versioned migration files shows the hybrid model most production teams end up with, and detecting production schema drift against a desired state turns the diff into a continuous audit.

The tools that underpin these workflows are compared alongside Flyway and Liquibase in Migration Tool Comparison, and ORM-driven equivalents such as Prisma’s db push are covered in Prisma Migration Strategies.

Frequently Asked Questions

Is declarative schema management safe for production? Yes, if you treat the generated plan as the change under review and configure the tool to block destructive statements and use online DDL. It is unsafe when the desired state is applied directly without review, because the diff engine cannot know about renames, table sizes or which application versions are still running.

How does a declarative tool handle column renames? Generally it does not: a renamed column looks like one column removed and another added, so the plan drops the old column and its data. Some tools accept rename hints or detect likely renames interactively, but the safe approach is an expand-and-contract rename carried out over several desired-state changes.

Why does the tool need a dev database? To normalise the desired state. Engines rewrite definitions — expanding type aliases, naming constraints, formatting defaults — and the only reliable way to compare like with like is to execute the desired DDL on a scratch database and inspect the result. Use the same engine and major version as production.

Can I use a declarative tool and versioned migrations together? Yes, and many teams do. The declarative definition is the source of truth for what the schema should be, and the tool generates versioned migration files from the diff, which engineers edit to add online-DDL options, backfills and staging before committing them.