Database Migrations¶

Schema changes are the most dangerous operations in production Adding a column sounds simple until that column has a NOT NULL constraint with no default value and your migration locks the table for 30 minutes while Postgres backfills millions of rows and your entire application grinds to a halt because every query is waiting on the DDL lock held by your "simple migration" that you ran during peak hours on a Friday because "it's just adding a column bro relax"

Migrations are version-controlled, sequential, and (ideally) reversible changes to your database schema. They replace the Wild West of "let me just run this ALTER TABLE in production real quick" with a disciplined process where every schema change is reviewed, tested, and applied systematically across environments

Why migrations matter¶

Without migrations, your database schema is whatever someone last ran against it - which is either whatever mohsen typed in production at 3AM while drunk on energy drinks , or whatever the latest developer's local database looks like , which is guaranteed to be different from staging , which is different from production , and nobody can explain why the profiles table has a favorite_color column in prod but not in dev

Migrations give you: * Reproducibility - every environment gets the exact same schema in the exact same order * Audit trail - who changed what , when , and what the SQL looked like * Rollback capability - when a migration breaks production (it will) , you can reverse it * Code review - schema changes go through PR review just like application code * CI/CD integration - migrations run automatically in deployment pipelines

Prisma Migrate¶

Prisma's migration system generates SQL from your schema.prisma changes:

# Create migration from schema changes
npx prisma migrate dev --name add_user_roles

# Review the generated SQL in prisma/migrations/YYYYMMDDHHMMSS_add_user_roles/
# The SQL is generated - always review it before applying to production

# Apply to production
npx prisma migrate deploy

# Reset (dev only - drops and recreates everything)
npx prisma migrate reset

Example generated migration:

-- prisma/migrations/20240620120000_add_user_roles/migration.sql
-- AlterTable
ALTER TABLE "users" ADD COLUMN "role" TEXT NOT NULL DEFAULT 'user';

-- CreateIndex
CREATE INDEX "users_role_idx" ON "users"("role");

Always review generated SQL before deploying. Prisma is smart but not infallible - it might generate an index that already exists or miss a concurrent index creation that would avoid locking

Knex migrations (Node.js)¶

Knex.js is a query builder with a mature migration system that gives you full control over the SQL:

npm install knex
npx knex init  # Creates knexfile.js
npx knex migrate:make create_users_table

// migrations/20240620120000_create_users_table.js

exports.up = function(knex) {
    return knex.schema.createTable('users', (table) => {
        table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
        table.string('email').notNullable().unique();
        table.string('username').notNullable();
        table.string('password_hash').notNullable();
        table.string('role').notNullable().defaultTo('user');
        table.boolean('is_active').notNullable().defaultTo(true);
        table.timestamp('created_at').notNullable().defaultTo(knex.fn.now());
        table.timestamp('updated_at').notNullable().defaultTo(knex.fn.now());

        table.index(['role', 'is_active']);
    });
};

exports.down = function(knex) {
    return knex.schema.dropTableIfExists('users');
};

# Apply migrations
npx knex migrate:latest

# Rollback last batch
npx knex migrate:rollback

# Check status
npx knex migrate:status

node-pg-migrate (Postgres-specific)¶

For projects using raw pg driver without an ORM, node-pg-migrate provides a lightweight migration framework:

npm install node-pg-migrate

// migrations/1720000000000_create-users.js

exports.up = (pgm) => {
    pgm.createTable('users', {
        id: { type: 'uuid', primaryKey: true, default: pgm.func('gen_random_uuid()') },
        email: { type: 'varchar(255)', notNull: true, unique: true },
        username: { type: 'varchar(100)', notNull: true },
        password_hash: { type: 'varchar(255)', notNull: true },
        role: { type: 'varchar(20)', notNull: true, default: 'user' },
        is_active: { type: 'boolean', notNull: true, default: true },
        created_at: { type: 'timestamptz', notNull: true, default: pgm.func('now()') },
        updated_at: { type: 'timestamptz', notNull: true, default: pgm.func('now()') }
    });

    pgm.createIndex('users', ['role', 'is_active']);
};

exports.down = (pgm) => {
    pgm.dropTable('users');
};

# Add to package.json scripts
"migrate:up": "node-pg-migrate up",
"migrate:down": "node-pg-migrate down"

Writing up/down migrations¶

Every migration should have both up (apply the change) and down (revert the change) functions. Down migrations are your escape hatch when something goes wrong in production

-- UP migration: add a column
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
CREATE INDEX idx_users_phone ON users (phone_number);

-- DOWN migration: remove the column
DROP INDEX IF EXISTS idx_users_phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone_number;

Critical: Not all migrations are reversible. If your up migration deletes a column with data, the down migration can't restore the deleted data. Always think about reversibility before writing a migration:

exports.up = async (pgm) => {
    // BAD: deleting data that can't be restored
    // ALTER TABLE users DROP COLUMN temporary_field;

    // GOOD: making column nullable instead of dropping
    // ALTER TABLE users ALTER COLUMN optional_field DROP NOT NULL;
};

Seeding - populating development databases¶

Seeds are scripts that populate your database with test data for development and staging environments:

// prisma/seed.js
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
    // Create admin user
    const admin = await prisma.user.upsert({
        where: { email: 'admin@example.com' },
        update: {},
        create: {
            email: 'admin@example.com',
            username: 'admin',
            passwordHash: '$2b$12$...',  // Pre-hashed
            role: 'admin',
            isActive: true
        }
    });

    // Create sample posts
    const posts = await Promise.all([
        prisma.post.create({
            data: {
                title: 'Getting Started with Postgres',
                content: 'PostgreSQL is...',
                published: true,
                authorId: admin.id
            }
        }),
        prisma.post.create({
            data: {
                title: 'MongoDB Best Practices',
                content: 'MongoDB is...',
                published: true,
                authorId: admin.id
            }
        })
    ]);

    console.log('Seed completed:', { admin, posts });
}

main()
    .catch(console.error)
    .finally(() => prisma.$disconnect());

# Run Prisma seed
# Add to package.json:
# "prisma": { "seed": "node prisma/seed.js" }
npx prisma db seed

CI/CD migration strategies¶

Zero-downtime migration approach¶

Additive changes first - add columns as nullable, create new tables, add indexes CONCURRENTLY (Postgres). These don't break existing code
Deploy application code - deploy the updated code that reads/writes both old and new schema
Backfill data - populate new columns/tables with data from old schema
Migration cleanup - remove old columns (make them nullable first), drop old indexes, remove dead code

# Migration script for CI/CD pipeline
#!/bin/bash
set -e

echo "Running database migrations..."

# For Prisma:
npx prisma migrate deploy

# For Knex:
# NODE_ENV=production npx knex migrate:latest

# For node-pg-migrate:
# node-pg-migrate up

echo "Running seeds (staging only)..."
if [ "$NODE_ENV" = "staging" ]; then
    npx prisma db seed
fi

echo "Migrations complete"

Safety checks in CI/CD¶

# Check for destructive operations before deploying
npx prisma migrate status  # Check pending migrations

# Dry-run the migration against a copy of production data
# pg_dump production_db | psql staging_db
# npx prisma migrate deploy  # Test against staging copy

Safe ALTER TABLE patterns for Postgres¶

-- Adding a column with a default - locks the table in Postgres < 11
-- Postgres 11+: ALTER TABLE ... ADD COLUMN ... DEFAULT ... is instant
ALTER TABLE users ADD COLUMN notification_prefs JSONB DEFAULT '{}'::jsonb;

-- Adding a NOT NULL column without default
-- Step 1: Add as nullable
ALTER TABLE users ADD COLUMN timezone VARCHAR(50);
-- Step 2: Backfill data
UPDATE users SET timezone = 'UTC' WHERE timezone IS NULL;
-- Step 3: Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN timezone SET NOT NULL;

-- Creating indexes without locking writes (Postgres)
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
-- CONCURRENTLY avoids locking but takes longer and can't run in a transaction

When migrations go wrong in production¶

# Scenario: migration fails halfway through
# 1. Don't panic - the migration tool tracks which ran
# 2. Run the down migration if available
# 3. If no down migration exists, manually undo the partial changes
# 4. Fix the migration, test on staging, re-run

# Check migration status
npx prisma migrate status

# Force-mark a migration as applied (if it actually ran but wasn't recorded)
# npx prisma migrate resolve --applied 20240620120000_add_user_roles

# Force-mark as rolled back (if you manually undid it)
# npx prisma migrate resolve --rolled-back 20240620120000_add_user_roles

prerequisites¶

db_05_postgres_security.md or db_14_prisma_intro.md - you need to have actually worked with a database before migrations make sense. The pain of "I changed my schema locally and now staging won't start" is the prerequisite that teaches you why migrations matter

next → db_17_resources.md