Database Migrations Reference

Versioned schema changes, migration frameworks, and safe production migration patterns.

Migration Concepts

A migration is a versioned, reversible change to the database schema. Each migration has an "up" (apply) and "down" (revert) direction. Migrations run in order and track which have been applied.

Why migrations instead of editing the schema directly:

Reproducibility — apply the same changes to dev, staging, production
Version control — schema changes are committed alongside code changes
Rollback — revert a bad migration without manual SQL surgery
Team coordination — no conflicting ad-hoc ALTER TABLE statements

Manual Migration Pattern

When not using a migration framework, maintain numbered SQL files.

Directory structure

migrations/
├── 001_create_devices.up.sql
├── 001_create_devices.down.sql
├── 002_add_vlan_column.up.sql
├── 002_add_vlan_column.down.sql
├── 003_create_events.up.sql
└── 003_create_events.down.sql

Example up migration (002_add_vlan_column.up.sql)

ALTER TABLE devices ADD COLUMN vlan_id INTEGER DEFAULT 10;
CREATE INDEX idx_devices_vlan ON devices (vlan_id);

Example down migration (002_add_vlan_column.down.sql)

DROP INDEX IF EXISTS idx_devices_vlan;
ALTER TABLE devices DROP COLUMN vlan_id;

Apply migrations in order with a shell script

for f in migrations/*.up.sql; do
    echo "Applying: $f"
    psql -d myapp -f "$f"
done

Migration Tracking Table

Track which migrations have been applied so they run exactly once.

Create a migration tracking table

CREATE TABLE IF NOT EXISTS schema_migrations (
    version     INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    applied_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Check current version

SELECT MAX(version) AS current_version FROM schema_migrations;

Record a migration

INSERT INTO schema_migrations (version, name)
VALUES (3, 'create_events');

Alembic (Python / SQLAlchemy)

Alembic is the standard migration tool for SQLAlchemy and FastAPI projects.

Initialize Alembic

pip install alembic
alembic init migrations

Generate migration from model changes

alembic revision --autogenerate -m "add vlan column to devices"

Apply all pending migrations

alembic upgrade head

Rollback one migration

alembic downgrade -1

Check current version

alembic current
alembic history --verbose

Generated migration file (migrations/versions/abc123_add_vlan.py)

def upgrade():
    op.add_column('devices', sa.Column('vlan_id', sa.Integer(), default=10))
    op.create_index('idx_devices_vlan', 'devices', ['vlan_id'])

def downgrade():
    op.drop_index('idx_devices_vlan', 'devices')
    op.drop_column('devices', 'vlan_id')

Safe Migration Practices

Adding Columns

Safe: add nullable column, then backfill, then add constraint

-- Step 1: add column (nullable, no lock)
ALTER TABLE devices ADD COLUMN region TEXT;

-- Step 2: backfill in batches
UPDATE devices SET region = 'west' WHERE id BETWEEN 1 AND 1000;
UPDATE devices SET region = 'west' WHERE id BETWEEN 1001 AND 2000;

-- Step 3: add NOT NULL constraint (after backfill)
ALTER TABLE devices ALTER COLUMN region SET NOT NULL;

Adding a column with a DEFAULT value in PostgreSQL 11+ is instant (does not rewrite the table). In older versions or MySQL, it rewrites the entire table and locks it.

Renaming Columns

Safe: add new column, backfill, deploy code for both, drop old

-- Step 1: add new column
ALTER TABLE devices ADD COLUMN mgmt_ip TEXT;

-- Step 2: copy data
UPDATE devices SET mgmt_ip = ip_address;

-- Step 3: deploy code that reads/writes both columns
-- Step 4: drop old column (after all code uses the new one)
ALTER TABLE devices DROP COLUMN ip_address;

Never rename a column in one step on a production database with running applications. The old column name will cause errors until the code is updated.

Dropping Columns

Safe: stop writing, then drop

-- Step 1: deploy code that no longer reads/writes the column
-- Step 2: drop the column
ALTER TABLE devices DROP COLUMN legacy_field;

Adding Indexes

PostgreSQL: create index without locking writes

CREATE INDEX CONCURRENTLY idx_devices_region ON devices (region);

CONCURRENTLY allows reads and writes to continue while the index is built. Without it, the table is locked for the entire build duration.

SQLite Migration Limitations

SQLite has limited ALTER TABLE support. You cannot:

Drop a column (before 3.35)
Rename a column (before 3.25)
Add a constraint to an existing column

Workaround: recreate the table

-- 1. Create new table with desired schema
CREATE TABLE devices_new (
    id INTEGER PRIMARY KEY,
    hostname TEXT NOT NULL,
    mgmt_ip TEXT                -- renamed from ip_address
);

-- 2. Copy data
INSERT INTO devices_new (id, hostname, mgmt_ip)
SELECT id, hostname, ip_address FROM devices;

-- 3. Replace
DROP TABLE devices;
ALTER TABLE devices_new RENAME TO devices;

This is destructive — wrap it in a transaction and test on a copy first.

Version Compatibility

Feature	PostgreSQL	MySQL	SQLite
ADD COLUMN (instant)	11+	8.0 (INSTANT)	Always instant
DROP COLUMN	All	All	3.35+
RENAME COLUMN	All	8.0+	3.25+
Online index build	CONCURRENTLY	ALGORITHM=INPLACE	N/A
Transactional DDL	Yes	No (implicit COMMIT)	Yes

Feature

PostgreSQL

MySQL

SQLite

ADD COLUMN (instant)

11+

8.0 (INSTANT)

Always instant

DROP COLUMN

All

3.35+

RENAME COLUMN

All

8.0+

3.25+

Online index build

CONCURRENTLY

ALGORITHM=INPLACE

N/A

Transactional DDL

Yes

No (implicit COMMIT)

Yes

PostgreSQL and SQLite support transactional DDL — if a migration fails partway through, the schema change is rolled back. MySQL auto-commits DDL statements, so a failed migration can leave the schema in a broken state.