Preventing Destructive Changes in Declarative Schema Diffs

A merge conflict in schema.sql was resolved by taking “theirs”, and the resolution quietly removed a table definition that only existed on your branch. The next declarative apply computed the difference faithfully: the table exists in production and not in the desired state, so it must go. DROP TABLE customer_notes ran in a few milliseconds, and three years of support history disappeared. In a versioned workflow somebody would have had to write that drop; in a declarative one, the absence of a line is enough. This guide builds the layered defence that makes destructive changes impossible to ship by accident while keeping them possible on purpose — the guardrail list from Declarative Schema Management, implemented.

How a Deleted Line Becomes a DROP Four steps. A merge removes a table definition from schema.sql; the diff engine sees the table in production but not in the desired state; the plan contains DROP TABLE; an unguarded apply executes it and the data is gone. How a Deleted Line Becomes a DROP STEP 1 Merge drops a line customer_notes definition lost STEP 2 Diff sees absence in prod, not in desired state STEP 3 Plan emits DROP DROP TABLE customer_notes STEP 4 Unguarded apply rows gone in milliseconds
No step in this chain is a bug in the tool — the diff is correct for the file it was given, which is why the defence has to sit between the plan and the apply.

Symptom / Error Signatures

You need these guardrails if any of the following has happened, or could:

  • A plan in a pull request contains DROP TABLE, DROP COLUMN, DROP SCHEMA, or ALTER COLUMN ... TYPE that narrows a type, and the pull request description does not mention it.
  • A reviewer approved a long plan in which the destructive statement was line 40 of 60.
  • The declarative tool runs with an auto-approve flag in CI and no linting step.
  • After a deploy, application errors such as relation "customer_notes" does not exist or column "legacy_code" does not exist appear — the destructive change has already run.

When guardrails are in place, the symptoms become tool messages instead of incidents: Skeema reports Detected 1 unsafe change and refuses to push; Atlas’s lint reports a destructive-change diagnostic; a custom CI check prints the offending statement and fails the job.

Root Cause Analysis

Declarative tools are, by design, complete: they converge the database to the desired state, and removal is part of convergence. Three properties turn that into data loss. The desired state is edited by humans and merged by version control, which has no notion of “this deletion is dangerous”. The plan is reviewed as a whole, and destructive statements look typographically identical to harmless ones. And the tool cannot distinguish an intentional removal from an accidental one, because both are the same diff.

Destructive change Why it happens by accident Data impact
DROP TABLE definition lost in a merge or file split all rows lost
DROP COLUMN column removed from model before code stopped reading it column data lost; running code errors
rename seen as drop + add column renamed in the desired state old values lost; new column empty
narrowing ALTER TYPE varchar(255) edited to varchar(64) fails or truncates, depending on engine and mode
DROP INDEX on a unique index index definition removed uniqueness no longer enforced

The defence therefore has to live outside the desired state: in tool policy that refuses destructive statements by default, in linting that classifies each planned statement, and in a review gate that makes destructive changes visually and procedurally distinct.

Three Independent Layers Layered defence. Layer one, tool policy, skips or refuses drops by default. Layer two, a linter classifies each planned statement and fails CI on destructive codes unless an override label is present. Layer three, a review rule requires a second approver and a data-retention check for any pull request carrying the override. Only then does the apply job run. Three Independent Layers Generated plan contains DROP COLUMN 1 · Tool policy skip drops / refuse unsafe 2 · Plan lint destructive → fail CI 3 · Review rule override label + 2nd approver Apply job runs only if all pass Blocked no data touched
Any single layer can fail — a policy misconfigured, a lint rule skipped, a tired reviewer — so the three are independent and each alone blocks the drop.

Immediate Mitigation

1. Remove auto-approve from every production apply today. Until the layers below exist, a human reads every plan. Generate plans with a dry run and apply only saved, reviewed SQL.

2. Turn on the tool’s own destructive-change refusal. In Atlas, add skip rules so drops are never emitted in the normal path. In Skeema, the default already refuses unsafe changes; make sure nobody set allow-unsafe in a .skeema file. In sqldef, table drops are only emitted when --enable-drop-table is passed, so keep that flag out of CI.

# INI · .skeema file for the production environment · checked into git
# WARNING: never set allow-unsafe here; pass --allow-unsafe on the command line for one reviewed push.
[production]
host=prod-mysql.internal
schema=shop
allow-unsafe=0
safe-below-size=10M

3. Add a plan scan to CI that fails on destructive statements. A simple pattern check over the generated SQL is crude but effective, and it works for any tool.

# Shell · CI job after plan generation · reads plan.sql, touches no database
# WARNING: this is a backstop, not a parser; keep the tool policy and linter as the primary layers.
if grep -nEi "DROP[[:space:]]+(TABLE|SCHEMA|COLUMN|DATABASE)|ALTER[[:space:]]+COLUMN.*TYPE" plan.sql; then
  if [ "${ALLOW_DESTRUCTIVE_LABEL:-false}" != "true" ]; then
    echo "destructive statement in plan; add the 'destructive-migration' label with justification"; exit 1
  fi
fi

4. Run the tool’s linter. Atlas ships analyzers that classify destructive changes (the DS family of diagnostics, such as a dropped column), backward-incompatible changes (BC, such as a renamed column) and data-dependent changes (MF, such as a new unique index that may fail on existing duplicates). Skeema’s skeema lint covers table-definition hygiene. Fail CI on the destructive and backward-incompatible classes.

Permanent Fix / Long-Term Pattern

Make destructive changes a separate, visible workflow rather than something a diff can smuggle in. The pattern that holds up is: drops are skipped or refused by default in tool policy; any pull request whose plan contains a destructive statement must carry an explicit label, a written justification and a second approver; the drop happens in its own pull request, never alongside additive changes; and before approval, someone confirms that no deployed application version reads the object and that a recoverable copy of the data exists.

That last point connects declarative workflows to the contract phase of Expand and Contract Methodology. A column is removed from the desired state only after the application has stopped reading and writing it for at least one full release, and — for anything valuable — after its data has been archived. The same retention thinking appears in recovering data after an irreversible migration, which is where you end up if all three layers fail. For MySQL tables above a size threshold, Skeema’s safe-below-size lets tiny tables be dropped freely in development while production-sized ones always require the override.

Is This Drop Allowed Through? Decision tree for a plan containing a destructive statement. If the pull request lacks the destructive-migration label, fail CI. If it has the label, check whether any deployed version still reads the object; if yes, block until the contract phase. If no, check whether the data is archived or reproducible; if yes, allow with second approval; if no, archive first. Is This Drop Allowed Through? PR carries the destructive-migration label? yes no Any deployed version still reads it? yes no Block until contract phase Data archived or reproducible? yes no Allow with 2nd approver Archive first, then retry Fail CI: unlabelled drop
The override is not a single flag — it is a short checklist that proves the object is unused and its data is recoverable.

Verification Checklist

Frequently Asked Questions

Why not just disable drops permanently? Because unused structure accumulates, and some drops are necessary to finish an expand-and-contract migration or to remove sensitive data. The goal is not to make drops impossible but to make them deliberate: a separate change with an explicit override and evidence that the data is unused and recoverable.

Does skipping drops leave the schema out of sync? Yes, intentionally and temporarily. The live database keeps the object that the desired state no longer mentions, and drift checks will report it. Track these as pending contract steps and remove them with a reviewed drop once the preconditions are met.

Can a linter catch every destructive change? No. Pattern and analyzer based checks catch explicit drops and narrowing type changes well, but a rename seen as drop plus add, or a changed default that alters application behaviour, may need human judgement. That is why linting is one layer among three rather than the whole defence.

How should renames be handled in a declarative workflow? As an expand-and-contract sequence: add the new column in one change, dual-write and backfill, switch reads, and only then remove the old column from the desired state in a separate, labelled change. Editing the name in place produces a drop and an add.

Related Articles