practiceEngineering & architecture

Database Migration

A versioned, repeatable change that evolves database structure or data while preserving application correctness and recoverability.

Download Markdown (.md)

01What it means

A database migration is a controlled change to database structure, constraints, indexes, or stored data, recorded so environments can move between known versions. Safe migration practice coordinates the database change with application deployments and live traffic rather than assuming every component changes atomically.[1][2]

Expand, migrate, contract Keep old and new application versions compatible during change
  1. 01ExpandAdd compatible structures first
  2. 02DeployCode can use old and new shapes
  3. 03MigrateBackfill data and shift reads or writes
  4. 04VerifyCheck traffic, data, recovery
  5. 05ContractRemove obsolete structures later

Breaking schema changes require coordinated stages because databases and application instances rarely change atomically.

02Why it matters

Products must evolve without losing data or requiring fragile manual edits. Versioned migrations make changes reviewable, repeatable, testable, and auditable, while compatibility planning reduces downtime and failures when old and new application versions overlap.

03When to use it

Use migrations for every durable database change that must be reproduced across development, test, staging, and production. Direct manual repair may be necessary during an incident, but it should be controlled, recorded, verified, and reconciled into the managed history.

04How it works

  1. Describe the desired schema or data change and assess locking, duration, volume, compatibility, and recovery risk.
  2. Create a versioned migration with deterministic operations and explicit preconditions.
  3. For breaking changes, expand the schema first so old and new application versions can operate together.
  4. Test against production-like data, concurrent traffic, backups, failure points, and deployment order.
  5. Apply with monitoring, bounded execution, ownership, and a tested rollback or forward-recovery plan.
  6. Migrate reads and writes, verify results, then remove obsolete structures in a later deployment.

05A practical example

Example

A team replaces a customer-name column with separate fields. It first adds nullable columns, deploys code that writes both forms, backfills in bounded batches, switches reads after verification, and removes the old column only after every running version is compatible.

06Where it breaks

Some data transformations are not safely reversible, and rollback code cannot undo external effects or discarded information. Large tables, locks, replicas, mixed versions, and long backfills make seemingly simple changes operationally risky.

Common mistakes

  • Combining a destructive schema change and dependent application release with no compatibility window.
  • Running an unbounded backfill inside one transaction on a heavily used table.
  • Assuming a down migration guarantees safe recovery after data has changed.
  • Editing migration history after it has already been applied to shared environments.

07Sources

  1. Active Record MigrationsRuby on Rails Guides · Official framework documentationActive Record Migrations
  2. MigrationsDjango Documentation · Official framework documentationMigrations
View saved topics

Start typing to search titles, definitions, categories, and frameworks.