From Schema to ProductionLesson 6.1

Database migrations: versioning schema changes safely

migration definition, up and down migrations, migration file naming, Flyway, Liquibase, zero-downtime migration, idempotent migrations, rollback strategy

What Is a Migration?

A migration is a versioned script that changes the database schema. Migrations are stored in source control alongside application code. Every environment (local, staging, production) runs the same migration files in order, keeping schemas in sync.

Structure of a Migration

-- V2__add_phone_to_customers.sql (Flyway naming: V{version}__{description}.sql)

-- UP: apply the change
ALTER TABLE customers
  ADD COLUMN phone VARCHAR(20) NULL;

CREATE INDEX idx_customers_phone ON customers(phone);

-- DOWN (in a separate file or section): undo
DROP INDEX idx_customers_phone ON customers;
ALTER TABLE customers DROP COLUMN phone;

Rules for Safe Migrations

Never edit a migration that has already run in production. Write a new migration to correct it.
Make migrations idempotent where possible — use IF NOT EXISTS / IF EXISTS guards.
Test rollback. Run the down migration in a staging environment before merging.
Small and focused. One change per migration file — easier to debug and roll back.

Tools

Flyway (SQL-first, simple) and Liquibase (XML/YAML/SQL, more features) are the two dominant tools. Most ORM frameworks (Django, Rails ActiveRecord, Laravel) include a built-in migration runner.