Database Migration

Manage schema changes with versioned, repeatable migrations.

Prisma (Node.js)

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

# Apply migrations in production
npx prisma migrate deploy

# Reset database (destroys data)
npx prisma migrate reset

# Check migration status
npx prisma migrate status

# Generate client after schema change
npx prisma generate

# View current schema
npx prisma db pull

Knex (Node.js)

# Create migration file
npx knex migrate:make add_users_table

# Run pending migrations
npx knex migrate:latest

# Rollback last batch
npx knex migrate:rollback

# Rollback all
npx knex migrate:rollback --all

# Check status
npx knex migrate:status

# Create seed file
npx knex seed:make seed_users

# Run seeds
npx knex seed:run

Alembic (Python/SQLAlchemy)

# Initialize
alembic init alembic

# Auto-generate migration from model changes
alembic revision --autogenerate -m "add users table"

# Apply all pending
alembic upgrade head

# Rollback one step
alembic downgrade -1

# Check current version
alembic current

# Show migration history
alembic history --verbose

# Generate SQL without applying
alembic upgrade head --sql > migration.sql

Flyway (Java/multi-language)

# Apply migrations
flyway migrate

# Check status
flyway info

# Validate migrations match applied
flyway validate

# Rollback (Teams edition only)
flyway undo

# Clean database (destroys everything)
flyway clean

# Baseline existing database
flyway baseline

Raw SQL Migrations

# PostgreSQL — apply migration file
psql $DATABASE_URL -f migrations/001_create_users.sql

# MySQL
mysql -u $DB_USER -p$DB_PASS $DB_NAME < migrations/001_create_users.sql

# Track manually with a versions table
psql $DATABASE_URL -c "CREATE TABLE IF NOT EXISTS schema_migrations (version TEXT PRIMARY KEY, applied_at TIMESTAMPTZ DEFAULT NOW());"
psql $DATABASE_URL -c "INSERT INTO schema_migrations (version) VALUES ('001_create_users');"

Schema Diffing

# Prisma — diff current DB vs schema
npx prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma

# PostgreSQL — dump schema for comparison
pg_dump --schema-only $DATABASE_URL > schema_current.sql
# Compare with previous
diff schema_previous.sql schema_current.sql

Notes

• Always test migrations on a copy of production data before applying.
• Migrations should be idempotent where possible (IF NOT EXISTS, IF EXISTS).
• Never edit an already-applied migration. Create a new one.
• For zero-downtime deployments: add columns as nullable first, backfill, then add constraints.
• Back up the database before running migrations in production.