Skip to content

PATTERN Cited by 1 source

Declarative schema management

Problem

Database schemas drift over time — columns get added on one environment but not another, indexes get created manually during an incident and never propagated, naming conventions evolve. The classical remedy is imperative migration files — a numbered sequence of SQL scripts that each environment replays in order. But migration files are lossy (they capture the delta, not the end state), drift-blind (no way to tell if an environment matches the sum of applied migrations), and awkward to review at the pull-request level (reviewers see DDL deltas, not the resulting schema shape).

Shape

Express the desired end state of the schema as a single text file in a declarative DSL. The file is the source of truth; the live database is a reconcilable materialisation. Four primitives form the ceremony:

  1. Desired-state file — the schema expressed in a DSL (HCL, SQL, YAML, Prisma schema language). Versioned in Git alongside application code.
  2. Inspect primitive — inspect / dump / pull command that introspects the live database and emits the file matching current state. Bootstraps a greenfield declarative workflow without rewriting schema.
  3. Diff engine — computes the minimal DDL needed to transform the live database into the desired-state file. Handles add / drop / modify / rename (with heuristics) of tables, columns, indexes, constraints, and (where supported) charset + collation.
  4. Apply primitive — apply / push command that executes the diff, optionally with operator-in-the- loop confirmation ("Planned Changes: … Apply / Abort") or --auto-approve for CI/CD.

The Git commit history of the desired-state file becomes the authoritative schema change log — every schema change is a reviewable PR against the file.

Worked example — Atlas CLI on PlanetScale

"Atlas makes it easy to apply a 'Database as Code' approach to an existing database by generating a file representing the schema of that database … Modifying the schema simply involves making a change to the schema definition file and applying it with the atlas schema apply command." (Source: sources/2026-04-21-planetscale-declarative-mysql-schemas-with-atlas-cli)

# 1. Bootstrap the desired-state file from live DB
atlas schema inspect -u "mysql://user:pass@host/db?tls=true" > schema.hcl

# 2. Edit schema.hcl to add a "description" column
#    column "description" {
#      null = false
#      type = varchar(100)
#    }

# 3. Apply — Atlas computes the diff, prompts, executes
atlas schema apply -u "mysql://user:pass@host/db?tls=true" -f schema.hcl
# -- Planned Changes:
# -- Modify "hotels" table
# ALTER TABLE `hotels_db`.`hotels` ADD COLUMN `description` varchar(100) NOT NULL
# ? Are you sure?: ▸ Apply / Abort

Why it works

  • Drift-detection is free — inspect against any environment tells you whether it matches the file. Dev / staging / prod schema skew is immediately visible.
  • Multi-environment reconciliation is free — apply the same file against every environment; every environment converges to the same schema.
  • Review ergonomics improve — reviewers see the resulting schema shape in the PR diff, not a DDL script.
  • Bootstrap from existing DBs — inspect eliminates the green-field barrier.

When not to use

  • Imperative intent is load-bearing. A column rename looks like drop + add to a naive diff engine; the data is lost. Teams that need explicit rename / backfill / data-migration intent should use imperative migrations (or hybrid mode — Atlas's migrate sub-command supports versioned migrations alongside pure declarative reconciliation).
  • Vendor-managed schema-change pipelines are already in use. Declarative tools and vendor pipelines are rivals, not complements. PlanetScale's safe- migrations feature blocks direct DDL against production; using Atlas requires disabling safe migrations ("when using Atlas with PlanetScale, you'll need to make sure you don't turn on safe migrations, as that will prohibit you from running DDL on production." — Source: sources/2026-04-21-planetscale-declarative-mysql-schemas-with-atlas-cli). The customer picks one ceremony.
  • Multi-step schema evolution is mandatory. The declarative tool applies one step at a time. For schema changes that touch existing production-serving columns, the operator still needs the patterns/expand-migrate-contract sequencing discipline — add new schema, dual-write, backfill, switch reads, drop old schema. The declarative tool executes each step; the operator owns the sequence.

Relationship to sibling patterns

  • patterns/expand-migrate-contract — complementary sequencing discipline. Declarative-schema-management is the apply mechanism; expand-migrate-contract is the sequencing discipline. The two compose.
  • patterns/schema-validation-before-deploy — the declarative tool's diff step is a form of pre-apply schema validation; some tools (Atlas's policy/lint engine, Bytebase's review rules) extend this with custom constraints (no-drop-without-backup, no-rename- without-alias, etc.).
  • concepts/schema-as-code — the concept this pattern operationalises. Schema-as-code says "the file is the truth"; declarative-schema-management says "here's how you reconcile the DB to the file."

Implementations

  • Atlas CLI — canonical wiki reference (Ariga, open-source, HCL-first, engine- agnostic MySQL / Postgres / MariaDB / SQLite / SQL Server / ClickHouse).
  • Skeema — MySQL-focused, .sql as the schema DSL.
  • Bytebase — commercial GitOps-style schema-change platform.
  • Prisma Migrate (in prisma db push mode) — Prisma- ORM-integrated declarative apply.

Seen in

  • sources/2026-04-21-planetscale-declarative-mysql-schemas-with-atlas-cli — first canonical wiki instance. Brian Morrison II demonstrates the pattern in its simplest form (Atlas CLI, two-command inspect + apply, MySQL target on PlanetScale). The post frames the pattern as "Database as Code" by direct analogy to IaC; flags PlanetScale's safe-migrations feature as a must-disable precondition — canonical datum that declarative tools and vendor-managed schema-change pipelines cannot run simultaneously.

Related

Last updated · 347 distilled / 1,201 read