Database Migrations

Create safe, reversible database migrations.

Detect Migration Tool

bash
1ls prisma/schema.prisma migrations/ db/migrate/ alembic.ini flyway.conf src/main/resources/db/migration/ 2>/dev/null

Universal Migration Rules

1. Always reversible — every migration must have an up() and down()
2. Never delete data in a migration (archive first, delete later)
3. One change per migration — don't bundle multiple schema changes
4. Test on a copy first — never run untested migration on production
5. Backup before running on production

SQL Migration Pattern

sql
1-- V001__create_users_table.sql (Flyway naming)
2-- or 001_create_users.up.sql
3
4-- UP
5CREATE TABLE users (
6    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
7    email VARCHAR(255) UNIQUE NOT NULL,
8    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
9    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
10);
11
12CREATE INDEX idx_users_email ON users(email);
13
14-- DOWN (in separate file or section)
15DROP TABLE IF EXISTS users;

Zero-Downtime Column Add

sql
1-- ✅ Safe: adding nullable column
2ALTER TABLE users ADD COLUMN display_name VARCHAR(100);
3
4-- ✅ Safe: adding column with default (new rows get default)
5ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
6
7-- ❌ Dangerous: adding NOT NULL column without default (locks table)
8ALTER TABLE users ADD COLUMN required_field VARCHAR(100) NOT NULL;
9-- Instead: add nullable → backfill → add NOT NULL constraint

Zero-Downtime Column Rename (3 migrations)

sql
1-- Migration 1: Add new column
2ALTER TABLE users ADD COLUMN user_name VARCHAR(100);
3
4-- Migration 2 (after deploy): Copy data + update app to write both
5UPDATE users SET user_name = username;
6
7-- Migration 3 (after next deploy): Drop old column
8ALTER TABLE users DROP COLUMN username;

Prisma (TypeScript)

bash
1# Create migration
2npx prisma migrate dev --name add_user_avatar
3
4# Apply to production
5npx prisma migrate deploy
6
7# Rollback (manual — Prisma doesn't auto-rollback)
8# Keep manual rollback SQL in migrations/rollbacks/

prisma
1// schema.prisma
2model User {
3  id        String   @id @default(cuid())
4  email     String   @unique
5  avatar    String?  // ← new nullable field (safe)
6  createdAt DateTime @default(now())
7  updatedAt DateTime @updatedAt
8}

Alembic (Python)

python
1# alembic/versions/001_add_avatar.py
2def upgrade():
3    op.add_column("users", sa.Column("avatar", sa.String(255), nullable=True))
4
5def downgrade():
6    op.drop_column("users", "avatar")

Flyway (Java/Spring Boot)

sql
1-- src/main/resources/db/migration/V002__add_user_avatar.sql
2ALTER TABLE users ADD COLUMN avatar VARCHAR(255);
3
4-- src/main/resources/db/migration/V002__add_user_avatar__undo.sql (for Flyway Teams)
5ALTER TABLE users DROP COLUMN avatar;

Migration Checklist

Before running any migration:

• Migration has both up and down operations
• Tested on development database first
• Large table migrations tested for lock duration
• Database backup taken (production)
• Rollback plan documented
• Zero-downtime pattern used for live tables

Usage

/database-migrations add-column users avatar
/database-migrations create-table orders
/database-migrations rename-column users username user_name
/database-migrations zero-downtime    # for high-traffic tables