Eric Campbell 5 min read Databases

Database Migrations: Schema Version Control

Every developer has experienced the pain of environment drift. Your local database has that new column, but staging doesn't. Production has an index that nobody remembers adding. A teammate's feature...

Key Insights

  • Database migrations treat schema changes as versioned, executable code that can be tested, reviewed, and deployed consistently across all environments—eliminating the “it works on my machine” problem for database schemas.
  • Successful migration strategies separate schema changes from data transformations, use timestamped filenames to prevent conflicts, and maintain both forward (up) and backward (down) migration paths.
  • Production deployments require defensive migration patterns: additive-only changes, backward-compatible schemas during transition periods, and zero-downtime techniques for large tables.

The Problem with Manual Schema Changes

Every developer has experienced the pain of environment drift. Your local database has that new column, but staging doesn’t. Production has an index that nobody remembers adding. A teammate’s feature branch requires a schema change that conflicts with yours. Manual SQL scripts scattered across wikis, shared drives, or worse—tribal knowledge—create chaos.

The fundamental issue is that database schemas are code, but we often don’t treat them that way. We version control application code meticulously, but then apply database changes through ad-hoc SQL scripts executed manually. This creates inconsistencies, makes rollbacks nearly impossible, and turns deployments into high-stress events.

Consider the difference:

-- Manual approach: schema_updates_2024.sql
-- (Someone runs this... maybe? Did staging get it?)
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;
CREATE INDEX idx_users_email ON users(email);

Versus a migration-based approach:

migrations/
  20240315143022_add_email_verification.sql
  20240315143023_add_email_index.sql

The migration approach provides traceability, ordering, and automation. Each change is timestamped, named, and tracked.

Core Concepts of Database Migrations

Database migrations are versioned scripts that modify your schema. Each migration represents a single, atomic change to the database structure or data. The migration system tracks which migrations have been applied, ensuring each runs exactly once and in the correct order.

Migration Files

Migration files follow a strict naming convention: a version identifier (typically a timestamp) followed by a descriptive name:

V20240315143022__add_email_verification.sql
20240315_143023_add_email_index.py
2024-03-15-143024-create-orders-table.sql

The timestamp ensures chronological ordering and prevents conflicts when multiple developers create migrations simultaneously.

Version Tracking

Migration tools maintain a metadata table that records which migrations have been applied:

CREATE TABLE schema_migrations (
    version VARCHAR(255) PRIMARY KEY,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    execution_time_ms INTEGER,
    checksum VARCHAR(64)
);

When you run migrations, the tool:

  1. Reads all migration files from your migrations directory
  2. Compares them against the schema_migrations table
  3. Executes any unapplied migrations in order
  4. Records each successful migration

Up and Down Migrations

Well-designed migration systems support both forward (up) and backward (down) migrations:

-- up migration: 20240315143022_add_email_verification.up.sql
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

-- down migration: 20240315143022_add_email_verification.down.sql
ALTER TABLE users DROP COLUMN email_verified;

Down migrations enable rollbacks, though in production, rolling forward with a new migration is often safer than rolling back.

Implementing Migrations: Practical Patterns

Creating Tables

Start with clean, complete table definitions:

-- V001__create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

Zero-Downtime Column Additions

For large tables, adding columns requires care:

-- V002__add_user_status.sql
-- Step 1: Add column as nullable
ALTER TABLE users ADD COLUMN status VARCHAR(20);

-- Step 2: Backfill in batches (separate migration or background job)
-- Step 3: Add NOT NULL constraint (in a future migration after backfill)

Never combine adding a column with a NOT NULL constraint and a default value on a large table in a single migration. This can lock the table for extended periods.

Separating Schema and Data Changes

Keep schema changes separate from data transformations:

-- V003__add_user_roles_table.sql (schema only)
CREATE TABLE user_roles (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    role VARCHAR(50) NOT NULL,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- V004__migrate_admin_flags.sql (data only)
INSERT INTO user_roles (user_id, role)
SELECT id, 'admin'
FROM users
WHERE is_admin = TRUE;

This separation makes migrations easier to test and reason about.

Migration Tools and Frameworks

Flyway

Flyway uses simple SQL files with version prefixes:

-- V1__initial_schema.sql
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- V2__add_product_categories.sql
CREATE TABLE categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

ALTER TABLE products ADD COLUMN category_id BIGINT REFERENCES categories(id);

Flyway integrates easily with Java applications and supports repeatable migrations for views and stored procedures.

Alembic (Python)

Alembic generates migration files with programmatic control:

# migrations/versions/20240315_add_email_verification.py
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('users',
        sa.Column('email_verified', sa.Boolean(), 
                  server_default='false', nullable=False)
    )
    op.create_index('idx_users_email_verified', 
                    'users', ['email_verified'])

def downgrade():
    op.drop_index('idx_users_email_verified', table_name='users')
    op.drop_column('users', 'email_verified')

Alembic’s programmatic approach provides flexibility for complex transformations and works seamlessly with SQLAlchemy.

Advanced Strategies and Edge Cases

Blue-Green Deployments

When deploying with zero downtime, your schema must support both the old and new application versions simultaneously:

-- Phase 1: Add new column (compatible with old code)
ALTER TABLE orders ADD COLUMN status_v2 VARCHAR(20);

-- Phase 2: Deploy new application code that writes to both columns

-- Phase 3: Backfill old data
UPDATE orders SET status_v2 = status WHERE status_v2 IS NULL;

-- Phase 4: Deploy code that reads from status_v2 only

-- Phase 5: Drop old column
ALTER TABLE orders DROP COLUMN status;

This multi-phase approach requires multiple deployments but ensures zero downtime.

Large Table Modifications

For tables with millions of rows, use online schema change tools:

-- Instead of direct ALTER TABLE on huge_table
-- Use pt-online-schema-change (Percona Toolkit) or similar

-- Command (not SQL):
-- pt-online-schema-change --alter "ADD COLUMN new_field INT" \
--   D=mydb,t=huge_table --execute

These tools create a shadow table, copy data in chunks, and swap tables atomically.

Coordinating with Application Deployments

Migrations should run before application deployment:

# CI/CD pipeline example
deploy:
  steps:
    - name: Run database migrations
      run: flyway migrate
    - name: Wait for migration completion
      run: ./wait-for-migrations.sh
    - name: Deploy application
      run: ./deploy-app.sh

Never deploy application code that depends on schema changes before running the migrations.

Common Pitfalls and Solutions

Avoiding Destructive Operations

Never drop columns or tables in the same deployment as code changes:

-- BAD: Immediate drop
ALTER TABLE users DROP COLUMN old_field;

-- GOOD: Multi-phase approach
-- Phase 1: Deploy code that stops using old_field
-- Phase 2: Wait several deployments
-- Phase 3: Drop column in separate migration
ALTER TABLE users DROP COLUMN old_field;

Handling Migration Conflicts

When multiple developers create migrations simultaneously, use timestamps:

migrations/
  20240315_143022_add_email_field.sql      (Developer A)
  20240315_143045_add_phone_field.sql      (Developer B)

Timestamps naturally order migrations, preventing conflicts.

Production Migration Checklist

Include this template in production migrations:

-- Migration: V042__add_user_preferences.sql
-- Author: jane@company.com
-- Date: 2024-03-15
-- Estimated execution time: 30 seconds
-- Rollback plan: V043 removes this column
-- Tested on staging: 2024-03-14
-- Reviewed by: john@company.com

-- Pre-flight checks:
-- [ ] Backed up production database
-- [ ] Verified migration on staging with production-sized data
-- [ ] Confirmed rollback procedure
-- [ ] Scheduled maintenance window (if needed)

BEGIN;

ALTER TABLE users ADD COLUMN preferences JSONB DEFAULT '{}';
CREATE INDEX idx_users_preferences ON users USING GIN(preferences);

COMMIT;

Building a Robust Migration Workflow

Integrate migrations into your development workflow from day one. Treat migration files as first-class code: review them in pull requests, test them in CI pipelines, and maintain the same standards you apply to application code.

Establish team conventions: naming patterns, when to combine versus split migrations, and how to handle long-running changes. Document your rollback procedures and practice them in staging.

Your CI/CD pipeline should run migrations automatically in lower environments but require manual approval for production. Always test migrations against production-sized datasets in staging—performance characteristics change dramatically with scale.

Database migrations transform schema changes from risky manual operations into predictable, versioned deployments. They’re not optional for professional software development—they’re fundamental infrastructure that pays dividends every single deployment.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.