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. Create Migration

After updating your schema.prisma file, create a migration:

npx prisma migrate dev --name add_phone_field

This command:

• Analyzes your schema.prisma file
• Compares it with the current database state
• Generates SQL migration files in prisma/migrations/
• Applies the migration to your database
• Regenerates Prisma Client automatically

2. Review Migration

Before committing, review the generated migration file:

-- AlterTable
ALTER TABLE "User" ADD COLUMN "phone" TEXT;

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

3. Production Deployment

For production deployments, use:

npx prisma migrate deploy

This command:

• Applies pending migrations to the production database
• Does not generate new migrations
• Does not regenerate Prisma Client (run prisma generate separately if needed)

Migration Commands

Create Migration (Development)

Create a new migration and apply it immediately:

npx prisma migrate dev --name migration_name

This is the recommended command for development. It:

• Creates the migration
• Applies it to your database
• Regenerates Prisma Client

Create Migration Without Applying

Create a migration file without applying it:

npx prisma migrate dev --create-only --name migration_name

This is useful when you want to review or modify the migration SQL before applying it.

Apply Migrations (Production)

Apply pending migrations to the database:

npx prisma migrate deploy

This command:

• Applies all pending migrations in order
• Does not generate new migrations
• Safe to run in production

Push Changes (Development Only)

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

npx prisma db push

Reset Database

Reset your database and apply all migrations from scratch:

npx prisma migrate reset

Production Migrations

For production deployments, follow these steps:

1. Create migrations locally - Run npx prisma migrate dev --name migration_name after schema changes
2. Review migrations - Check the generated SQL files in prisma/migrations/
3. Test on staging - Apply migrations to a staging database first using npx prisma migrate deploy
4. Commit migrations - Commit migration files to version control
5. Deploy - Run npx prisma migrate deploy as part of your deployment process

Migration Best Practices

1. Always use migrations for production deployments
2. Review migration files before committing 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 (e.g., add_phone_to_user)
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

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

The migration history table stores:

• Migration name
• Applied timestamp
• Migration checksum
• Logs

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. Mark as applied - If the migration was partially applied, you may need to mark it as applied manually
6. 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 - Query the _prisma_migrations table
2. Resolve conflict - Use npx prisma migrate resolve --applied migration_name if needed
3. Continue - Prisma will skip already applied migrations

Schema Out of Sync

If your schema.prisma doesn't match your database:

1. Review schema file - Ensure it's up to date
2. Check migration history - See which migrations have been applied
3. Create new migration - Run npx prisma migrate dev --name sync_schema to create a migration that brings the database in sync
4. Apply migration - The migration will be applied automatically

Resolve Failed Migrations

If a migration failed and you need to mark it as resolved:

npx prisma migrate resolve --applied migration_name

Or mark it as rolled back:

npx prisma migrate resolve --rolled-back migration_name

Advanced Topics

Custom Migration SQL

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

-- Custom migration SQL
ALTER TABLE "User" ADD COLUMN "full_name" TEXT;
UPDATE "User" SET "full_name" = "name" || ' ' || "last_name";

Data Migrations

Migrations can also include data transformations:

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

Rollback Migrations

While Prisma 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. Reset database - Use npx prisma migrate reset (development only)
4. Restore from backup - Restore the database to a previous state

Baseline Migrations

If you have an existing database and want to start using Prisma migrations:

1. Create initial migration - Run npx prisma migrate dev --name init
2. Mark as applied - Use npx prisma migrate resolve --applied init to mark it as already applied
3. Continue - Future migrations will work normally

Migration Files Structure

Migration files are organized as follows:

prisma/migrations/
├── 20240101000000_initial/
│   └── migration.sql
├── 20240102000000_add_users/
│   └── migration.sql
└── 20240103000000_add_phone_field/
    └── migration.sql

Each migration directory contains:

• migration.sql - The SQL statements to apply
• Optional migration metadata

Generate Prisma Client

After schema changes, regenerate the Prisma Client to update TypeScript types:

npx prisma generate