Migrations

Migrations are a way to version control your database schema changes. They allow you to track, apply, and rollback database changes in a controlled and reproducible manner.

Migration Workflow

The typical migration workflow consists of three steps:

1. Generate Migration

After updating your schema files, generate a migration:

npm run db:generate

This command:

• Analyzes your schema files in lib/db/schema/
• Compares them with the current database state
• Generates SQL migration files in lib/db/migrations/
• Creates a migration metadata file

2. Review Migration

Before applying, review the generated migration file to ensure it contains the expected changes:

ALTER TABLE "users" ADD COLUMN "phone" varchar(32);

You can edit the migration file if needed, but be careful - only modify the SQL if you understand the implications.

3. Apply Migration

To apply the migration to your database, run:

npm run db:migrate

This command:

• Executes the migration SQL against your database
• Updates the migration history table
• Ensures your database schema matches your code

Migration Commands

Generate Migration

Generate a new migration from schema changes:

npm run db:generate

This creates migration files but doesn't apply them to the database.

Apply Migration

Apply pending migrations to the database:

npm run db:migrate

This runs all pending migrations in order.

Push Changes (Development Only)

For rapid prototyping, push schema changes directly without creating a migration:

npm run db:push

Regenerate Migrations

If you need to regenerate migrations (e.g., after restoring from a backup):

npm run db:regenerate

This restores the migration files from the main branch and regenerates new migrations based on your current schema.

Production Migrations

For production deployments, follow these steps:

1. Generate migrations locally - Run npm run db:generate after schema changes
2. Review migrations - Check the generated SQL files
3. Test on staging - Apply migrations to a staging database first
4. Commit migrations - Commit migration files to version control
5. Deploy - Run npm run db:migrate as part of your deployment process

Migration Best Practices

1. Always generate migrations for production deployments
2. Review migration files before applying them
3. Test migrations on a staging database first
4. Commit migration files to version control
5. Never edit existing migrations - create new ones instead
6. Use descriptive migration names - The migration name should describe what it does
7. Keep migrations small - Break large changes into multiple migrations
8. Don't delete migrations - Even if you rollback, keep the migration files

Migration History

Drizzle tracks migration history in a special table. You can view which migrations have been applied by checking your database.

The migration history table stores:

• Migration name
• Applied timestamp
• Migration SQL

Troubleshooting

Migration Fails

If a migration fails:

1. Check the error message - It usually indicates what went wrong
2. Review the migration SQL - Ensure the SQL is correct
3. Check database state - Verify the current database schema
4. Fix the migration - Edit the migration file if needed
5. Re-run - Try applying the migration again

Migration Already Applied

If you see an error that a migration is already applied:

1. Check migration history - Verify which migrations have been applied
2. Skip if safe - If the migration was already applied, you can skip it
3. Regenerate if needed - Use npm run db:regenerate if migrations are out of sync

Schema Out of Sync

If your schema files don't match your database:

1. Review schema files - Ensure they're up to date
2. Check migration history - See which migrations have been applied
3. Generate new migration - Run npm run db:generate to create a migration that brings the database in sync
4. Apply migration - Run npm run db:migrate to apply the changes

Advanced Topics

Custom Migration SQL

You can write custom SQL in migration files for complex changes:

-- Custom migration SQL
ALTER TABLE "users" ADD COLUMN "full_name" varchar(255);
UPDATE "users" SET "full_name" = "name" || ' ' || "last_name";

Data Migrations

Migrations can also include data transformations:

-- Data migration example
UPDATE "users" SET "status" = 'active' WHERE "status" IS NULL;

Rollback Migrations

While Drizzle doesn't have built-in rollback support, you can:

1. Create a new migration - Write a migration that reverses the changes
2. Manual rollback - Manually revert the database changes
3. Restore from backup - Restore the database to a previous state

Migration Files Structure

Migration files are organized as follows:

lib/db/migrations/
├── meta/
│   ├── _journal.json          # Migration journal
│   └── 0000_snapshot.json     # Schema snapshot
├── 0001_initial.sql           # Initial migration
├── 0002_add_users.sql         # Add users table
└── 0003_add_phone_field.sql   # Add phone field

Each migration file contains the SQL needed to apply that specific change.