# Database migration safety

Migration-safety analysis checks a schema migration against a rule set of known
hazards, and flags the statements that are unsafe to run online. The migration
runs against a live database while the application keeps serving, and the hazard
is rarely that the SQL is wrong. An operation that takes a lock blocking reads
and writes turns a routine deploy into an outage, and a backward-incompatible
change breaks the code still running during a rolling deploy.

It is a [linter](https://quality.stereobooster.com/linters.md) applied to migration files rather than application
code: it does not run the migration, but matches the DDL against rules such as
"no index built without `CONCURRENTLY`" or "no `NOT NULL` added to a populated
column". What separates it from a general linter is the property it checks —
database availability through the change, not a code-quality pattern.

## What it catches

- **Table-locking DDL.** An `ALTER TABLE` that takes an exclusive lock while it
  rewrites the table or validates a constraint, blocking reads and writes for
  the duration.
- **Non-concurrent index builds.** `CREATE INDEX` without `CONCURRENTLY` on
  Postgres blocks writes until the index is built.
- **Backward-incompatible changes.** Dropping or renaming a column, or adding a
  `NOT NULL` column without a default, breaks the code still running mid-rollout.
- **Full-table rewrites.** A column type change or a volatile default that
  rewrites every row under a lock.

## Approaches and tools

Two approaches complement each other: a migration linter flags the unsafe
statement, and an online schema-change tool applies it without the blocking
lock.

### Detect it: migration linters

[strong_migrations](https://github.com/ankane/strong_migrations) checks Rails and ActiveRecord
migrations against Postgres, MySQL, and MariaDB rules, and prints the safe
rewrite for each dangerous operation. [squawk](https://github.com/sbdchd/squawk) lints raw Postgres
SQL, so it works whatever generated the migration, and runs as a CLI, a
pre-commit hook, or a pull-request bot. [django-migration-linter](https://github.com/3YOURMIND/django-migration-linter)
flags backward-incompatible Django migrations in CI. Atlas offers a
`migrate lint` subcommand for schema as code across several engines (an
open-source core with a commercial tier).

### Avoid it: online schema change

The by-construction fix performs the migration without the long lock, building a
shadow copy of the table in the background and swapping it in.
[gh-ost](https://github.com/github/gh-ost) and [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html)
do this for MySQL; [pgroll](https://github.com/xataio/pgroll) runs expand-and-contract migrations for
Postgres. Reaching for one of these is often better than hand-writing a
multi-step safe migration.

## When to use, when not

**Use:**

- To gate every migration in CI on a service that deploys without a maintenance
  window. The check is cheap and the failure it prevents is an outage.
- On raw-SQL migrations regardless of ORM, where squawk parses the DDL directly.
- To hold teams to the expand-and-contract discipline a rolling deploy needs:
  add the new shape, backfill, switch reads, drop the old shape.

**Don't:**

- On a system that can take a maintenance window or serves no concurrent
  traffic: the lock a linter forbids is harmless when nothing is reading.
- As a substitute for testing the migration's data transformation. The linter
  checks how the change is applied, not whether the resulting data is correct.

## Evidence

The case is operational rather than drawn from controlled studies. A lock that
blocks reads and writes is a service outage by definition, and every mainstream
database documents which DDL statements acquire one. The linters encode that
per-engine lock behavior as rules, so their reach is bounded by the rule set: a
migration whose danger the rules do not model still needs review.

## Classification

- **Quality dimensions:** Reliability.
- **Area:** Database schema migrations (DDL) under rolling or zero-downtime deploys: ORM migration files and raw SQL for Postgres, MySQL, MariaDB.
- **Guarantee:** Empirical: a fixed set of known-unsafe patterns per database engine, tuned for false-positive rate; a novel unsafe migration outside the rules passes.

## Referenced by

- [Static analysis](https://quality.stereobooster.com/static-analysis.md) · Methods