How to Set Up Database Migrations in Prisma
Database migrations are the version control system for your schema. Without them, schema changes are ad-hoc SQL scripts run manually against production, which is a recipe for inconsistency, data loss, and deployment failures. Prisma's migration system generates SQL migration files from your schema changes, tracks which migrations have been applied, and provides safe rollback paths when things go wrong.
The key advantage of Prisma's approach is that your schema definition in 'schema.prisma' is the single source of truth. You change the schema, Prisma generates the migration SQL, you review it, and then it gets applied consistently across development, staging, and production. This guide covers setting up the full migration workflow from local development to production deployment.
Step-by-step guide
Initialize Prisma and your first migration
If starting fresh, run 'npx prisma init' to create the Prisma directory with a 'schema.prisma' file and '.env' for your database URL. Define your initial models in 'schema.prisma' with proper field types, relations, and indexes. Then run 'npx prisma migrate dev --name init' to create your first migration. This generates a timestamped SQL file in 'prisma/migrations/' and applies it to your development database.
- Run 'npx prisma init' to create the Prisma directory
- Define your initial models in schema.prisma
- Run 'npx prisma migrate dev --name init' to create the first migration
Understand the migration file structure
Open the generated migration file in 'prisma/migrations/[timestamp]_init/migration.sql.' Review the SQL to ensure it matches your expectations. Prisma generates CREATE TABLE statements, indexes, constraints, and enum types. This SQL file is what gets committed to Git and run in production, so understanding it is critical. Never modify a migration file after it has been applied to any environment.
Set up the development workflow
Your daily workflow is: modify 'schema.prisma,' run 'npx prisma migrate dev --name descriptive_name,' review the generated SQL, and commit both the schema change and migration file. The 'migrate dev' command also regenerates the Prisma Client, so your TypeScript types immediately reflect the new schema. Use descriptive migration names like 'add_user_email_index' or 'create_audit_logs_table' so the migration history is readable.
- Edit schema.prisma with your changes
- Run 'npx prisma migrate dev --name descriptive_name'
- Review the generated SQL migration file
- Commit both schema.prisma and the migration directory to Git
Handle data migrations alongside schema changes
When a schema change requires data transformation (like splitting a 'name' column into 'first_name' and 'last_name'), create the migration in two steps. First, run 'prisma migrate dev' to generate the schema migration. Then, manually add data migration SQL to the generated file before committing. Add the data transformation INSERT/UPDATE statements after the schema changes but before any column drops, ensuring data is preserved.
Configure production deployment
In production, never use 'prisma migrate dev.' Instead, use 'npx prisma migrate deploy' which applies pending migrations without generating new ones. Add this command to your deployment pipeline so migrations run automatically before the application starts. Set the DATABASE_URL environment variable in your CI/CD to point to the production database. Always run migrations against a staging database first to catch issues before production.
- Add 'npx prisma migrate deploy' to your CI/CD pipeline
- Configure DATABASE_URL for each environment
- Run migrations in staging before production
Set up migration testing and validation
Add a CI step that runs 'prisma migrate diff' to verify that the schema.prisma file and the migration files are in sync. This catches cases where someone modifies the schema without generating a migration. Also add a step that applies all migrations from scratch against an empty database to verify the full migration chain works. If any migration fails in this clean run, the chain is broken and needs repair before merging.
Common mistakes
Editing migration files after they have been applied
Once a migration has been applied to any environment (including another developer's local database), modifying its SQL file creates a checksum mismatch that causes future migrations to fail. If you need to change something, create a new migration with the correction. The only exception is adding data migration SQL to a file before it has been committed and applied anywhere.
Not reviewing generated SQL
Prisma's migration generator is good but not perfect. It may generate destructive operations (like dropping and recreating a column instead of renaming it) that lose data. Always read the generated SQL before committing. If the generated migration does something destructive, create an empty migration with 'prisma migrate dev --create-only' and write the SQL manually.
Running 'prisma migrate dev' in production
The 'migrate dev' command can reset the database if it detects drift between the schema and migration state. In production, always use 'migrate deploy' which only applies pending migrations and never resets data. Accidentally running 'migrate dev' against production can delete your entire database.
Ignoring migration ordering in team environments
When two developers create migrations simultaneously, the ordering can conflict when merged. Coordinate migration creation through communication or use a locking mechanism. When conflicts occur, one developer should delete their migration, pull the other developer's changes, and regenerate their migration on top.
Tips
Use 'npx prisma migrate diff --from-schema-datasource prisma/schema.prisma --to-schema-datamodel prisma/schema.prisma' to preview what migration would be generated without actually creating it.
Add 'prisma/migrations/' to your code review checklist. Every PR with schema changes should have its migration SQL reviewed by someone who understands the production data implications.
Create a seed script ('prisma/seed.ts') that populates your development database with realistic test data after migrations, so developers always have a usable local environment.
Set up a weekly job that runs 'prisma migrate status' against production and alerts if there are pending migrations, catching deployment pipeline failures early.
How Vantage helps
Vantage helps PMs include database schema requirements in their PRDs with enough specificity for engineers to plan migrations confidently. When you define data model changes as requirements in Vantage, the generated tickets include migration considerations like backward compatibility and data transformation needs, reducing surprises during implementation.