Your application’s code is in Git, reviewed and deployed automatically. Your database schema should be too. Schema migrations are versioned scripts that change the database structure step by step (add a table, add a column, create an index), applied in the same order on every environment. They keep development, staging and production in sync, and turn risky manual SQL into a repeatable deployment step.

How migration tools work

  1. Each change is a file with a version or timestamp: 20261104_add_orders_status.sql.
  2. The tool keeps a table in the database recording which migrations have run (flyway_schema_history, alembic_version, migrations…).
  3. migrate applies the pending files in order. Running it twice does nothing the second time.
Tool Ecosystem Style
Flyway Any (Java CLI, Docker) Plain SQL files
Liquibase Any XML/YAML/SQL changesets
Alembic Python / SQLAlchemy Python scripts, autogenerate
Django migrations Django Generated from models
Laravel migrations PHP Schema builder classes
Prisma Migrate, Drizzle Kit Node.js / TypeScript Generated from schema
golang-migrate, Atlas Go / any SQL files, declarative

Use the one that fits your framework. ORM-integrated tools generate most migrations for you, but always read the generated SQL before applying it.

Example: Flyway with plain SQL

db/migration/
  V1__create_users.sql
  V2__create_orders.sql
  V3__add_orders_status.sql
-- V3__add_orders_status.sql
ALTER TABLE orders ADD COLUMN status varchar(20) NOT NULL DEFAULT 'pending';
CREATE INDEX idx_orders_status ON orders (status);
docker run --rm -v "$PWD/db/migration:/flyway/sql" flyway/flyway \
  -url=jdbc:postgresql://db:5432/app -user=app_owner -password="$DB_PASSWORD" migrate

Running migrations in deployment

  • Run migrations as a separate step before (or during) the deploy, from CI/CD or a release job, not at the startup of every app instance (several instances would race).
  • Use a database role that’s allowed to change the schema (the owner). Run the application itself with a role that can’t. See PostgreSQL roles and permissions.
  • Back up before migrations that change or delete data. See database backup automation.
  • Test migrations on a copy of production data in staging. Something that takes 50 ms on a dev database can take 20 minutes on a big table.

See GitHub Actions deployment to a VPS and deployment strategies.

Zero-downtime: expand and contract

During a rolling or blue-green deploy, old and new application versions run at the same time, against the same schema. So every migration must work with both. The pattern is expand → migrate → contract:

Renaming users.name to users.full_name:

  1. Expand: add full_name (nullable). Deploy code that writes both columns, and reads full_name with a fallback to name.
  2. Migrate data: backfill full_name from name in batches.
  3. Switch: deploy code that only uses full_name.
  4. Contract: in a later release, drop name.

Never rename or drop a column in the same release as the code change that stops using it.

Locking pitfalls

Some schema changes lock tables and block traffic:

Operation Risk Safer approach
Create index (PostgreSQL) Blocks writes CREATE INDEX CONCURRENTLY (outside a transaction)
Add column with default (PG 11+, MySQL 8 instant) Usually instant Check your version
Add NOT NULL to existing column Full table scan under lock Add a CHECK … NOT VALID, validate, then set NOT NULL
Change column type Table rewrite New column + backfill + switch
Large UPDATE backfill Long locks, replication lag Batches of a few thousand rows
MySQL ALTER on big tables Possible copy and lock ALGORITHM=INSTANT/INPLACE, gh-ost, pt-online-schema-change

In PostgreSQL, also set a lock_timeout for migrations, so a migration waiting for a lock fails fast instead of queuing all other queries behind it:

SET lock_timeout = '5s';

Rollbacks

Many tools support “down” migrations, but rolling back schema changes on production is risky: a dropped column’s data is gone. Prefer roll-forward: fix problems with a new migration. Make destructive steps (drops) separate, late, and only after backups.

Good habits

  • One logical change per migration, with a descriptive name.
  • Never edit a migration that has already run anywhere shared. Write a new one.
  • Keep migrations in the same repository and pull request as the code that needs them.
  • Seed and reference data (countries, plans) via migrations or idempotent seed scripts.
  • Review migrations like code, with special attention to locks and data loss.

Quick reference

Goal Practice
Track schema Versioned migration files in Git
Apply One migrate step in CI/CD, before rollout
No downtime Expand → migrate → contract
Indexes (PG) CREATE INDEX CONCURRENTLY
Big backfills Batches
Mistakes Roll forward with a new migration

Related: database security and MySQL partitioning.

  • Database
  • DevOps
  • Deployment