Short definition
A database migration is the process of moving, transforming, or restructuring data and schema definitions from one state to another. This can mean changing table structures, moving data between database engines, or promoting schema changes through environments. Done correctly, it allows software to evolve without data loss or extended downtime.
Extended definition
Database migration covers two related but distinct activities: schema migrations and data migrations. A schema migration modifies the structure of a database, adding columns, dropping tables, renaming fields, or changing data types. A data migration moves or transforms the actual records, either within the same database or from one system to another.
Teams typically run migrations as part of deployment pipelines. A migration script runs before or alongside new application code, ensuring the database state matches what the code expects. Tools like Flyway, Liquibase, Alembic (Python), Prisma Migrate, and Knex handle versioned migration files that track what has and has not been applied to each environment.
The operational importance of database migration is often underestimated until something breaks in production. A migration that locks a table on a high-traffic system can cause cascading timeouts across services. A poorly tested rollback plan can leave data in an inconsistent state. For this reason, database migrations require the same review discipline as application code, often more.
In practice, migrations must be coordinated with deployment strategy. Blue-green deployments, feature flags, and backward-compatible schema changes all help teams ship schema changes without forcing simultaneous application restarts or maintenance windows.
Deep technical explanation
Migration versioning and execution order
Migration tools track applied migrations in a dedicated metadata table, often called schema_migrations or flyway_schema_history. Each migration file is assigned a version number or timestamp. The tool compares the metadata table against the set of available migration files and runs only those not yet applied, in order. This prevents double-application but also means out-of-order migrations in branched development workflows need careful coordination.
Forward and backward compatibility
The safest migrations are backward compatible: the old version of the application code can still run against the new schema. This is achieved through the expand-contract pattern. In the expand phase, new columns or tables are added alongside old ones. The application is updated to write to both. In the contract phase, after the old code is fully retired, the old columns are removed. This pattern eliminates downtime during schema changes.
Locking and performance risk
DDL statements such as ALTER TABLE can acquire exclusive locks on large tables in PostgreSQL and MySQL, blocking reads and writes for the duration of the operation. On a table with tens of millions of rows, adding a NOT NULL column with a default can take minutes. Tools like pg_repack and gh-ost (GitHub’s online schema change tool for MySQL) perform the equivalent operation without long-duration locks by rebuilding the table in the background and swapping it atomically.
Rollback strategies
Not all migrations are reversible. Dropping a column or deleting rows has no automatic undo. Teams handle this with pre-migration backups, point-in-time recovery configurations, or by writing explicit down migrations. Down migrations must be tested independently and should be part of every migration review. For destructive changes, a soft-delete approach (marking rows as deleted rather than removing them) preserves the ability to recover data without a full restore.
Cross-database engine migrations
Migrating between database engines, such as from MySQL to PostgreSQL or from a relational database to a document store, is significantly more complex. Data type mappings differ, SQL dialects vary, and features like stored procedures or triggers may not have direct equivalents. ETL pipelines, AWS Database Migration Service, or custom Python scripts are commonly used. These migrations require a thorough audit of application query patterns before the cutover, and a parallel-run period to validate output consistency.
Practical examples
Adding a column to a high-traffic table
A SaaS product needed to add a nullable status column to a users table with 40 million rows on PostgreSQL. A direct ALTER TABLE would lock the table. The team used the expand-contract pattern: first adding the column as nullable with no default, backfilling it in batches using a background job, then adding the NOT NULL constraint after backfill completed. Zero downtime was maintained throughout.
Multi-tenant schema migration
A platform using per-tenant PostgreSQL schemas needed to run the same migration across 300 schemas. A Python script iterated over tenant schema names, applied the Alembic migration in each schema within a transaction, and logged failures per tenant. Partial failures were retried in isolation without affecting completed tenants.
Engine switch from MySQL to PostgreSQL
A data-intensive application moved from MySQL to PostgreSQL to use JSONB columns and window functions. The team ran both databases in parallel for two weeks, comparing query output on production read traffic. After validating parity, they switched write traffic over with a brief maintenance window and decommissioned MySQL.
Fixing a broken migration in a CI pipeline
A migration file was merged that referenced a column not yet created by a preceding migration. The CI pipeline caught the failure before staging. The team reordered migration files and added an integration test that runs all migrations against a fresh database on every pull request, preventing regression.
Why it matters
- Schema changes that are not coordinated with application deployments are one of the most common causes of production incidents in web applications.
- Versioned migration files make database changes auditable, reviewable, and repeatable across every environment from local development to production.
- Backward-compatible migration patterns allow teams to deploy schema changes independently of application releases, reducing deployment risk.
- Testing migrations against a production-sized dataset before rollout reveals locking and performance issues that small staging databases do not expose.
- A documented rollback plan for every migration reduces mean time to recover when a release needs to be reverted.
- Proper migration tooling removes manual SQL execution from deployment processes, eliminating a class of human error that frequently causes data loss.