LLM Best Practices

Prisma Migrations

4 min read

View markdown source

Overview

Prisma Migrate generates and applies SQL migration files derived from schema.prisma. Two commands do the work: migrate dev for local authoring and migrate deploy for CI and production. Each migration is a named SQL file stored in prisma/migrations/. Treat migration files as append-only once they have been applied to any environment. For the broader migration contract, see migrations.

Use migrate dev to author and migrate deploy to ship

The commands have different purposes and different risk profiles.

# Local development: generate the next migration, apply it, regenerate the client.
npx prisma migrate dev --name add_product_sku
 
# CI and production: apply pending migrations in order, no prompts, no client regen.
npx prisma migrate deploy
  • migrate dev uses a shadow database to calculate the diff between the current state and schema.prisma. It may prompt you to reset the local database if the history is inconsistent.
  • migrate deploy applies any migration that is not yet recorded in the _prisma_migrations table. It never creates new migrations. Run it before the new application binary takes traffic.
  • Never run migrate dev in production. It touches the shadow database and can reset state.

Understand the shadow database

migrate dev creates a temporary “shadow” database, replays all existing migrations on it, and compares that state to schema.prisma to compute the next migration’s SQL.

  • Prisma needs CREATE DATABASE and DROP DATABASE permissions for the shadow database. Grant them on the migration user.
  • Hosted databases that disallow those permissions (PlanetScale, some Supabase configs) require you to set shadowDatabaseUrl explicitly in datasource db.
  • If the shadow database diverges (for example, you edited a migration file), migrate dev detects the drift and warns you. Resolve it before authoring the next migration.

Baseline an existing database

When you add Prisma Migrate to a database that already has a schema, you must baseline it. Otherwise, migrate deploy will try to run the initial migration and fail on existing tables.

# 1. Create the migrations folder without running any SQL.
npx prisma migrate diff \
  --from-empty \
  --to-schema-datasource prisma/schema.prisma \
  --script > prisma/migrations/0001_initial/migration.sql
 
# 2. Mark that migration as applied without executing it.
npx prisma migrate resolve --applied 0001_initial
  • After baselining, every subsequent migrate dev will diff against the current DB state and produce correct incremental migrations.
  • Run the baseline step on every environment that already has the schema. A new environment (staging, fresh CI) can simply run migrate deploy and the baseline migration executes as normal SQL.

Detect and fix drift

Drift occurs when the database schema no longer matches what the migration history would produce. Common causes: a DBA ran ALTER TABLE by hand, a migration was partially applied, or someone edited a shipped migration file.

# Check for drift between the DB and the migration history.
npx prisma migrate diff \
  --from-migrations prisma/migrations \
  --to-schema-datasource prisma/schema.prisma \
  --exit-code
  • A non-zero exit means the database is ahead of or behind the migration history.
  • To fix drift in a development environment, reset: npx prisma migrate reset. This drops and recreates the DB.
  • In production, write a corrective migration that brings the DB in line with what the history expects. Do not edit shipped migrations.

Squash migrations when the history becomes unwieldy

Long migration histories slow down CI shadow-database replay and migrate reset. Squash by merging old migrations into a single baseline.

  1. Export the current production schema with pg_dump --schema-only.
  2. Replace all existing migration folders with a single 0001_baseline/migration.sql containing the dump.
  3. Mark it applied in every environment with prisma migrate resolve --applied 0001_baseline.
  4. New migrations continue from 0002_....
  • Squash only migrations that are applied everywhere. Never squash a migration that is pending in any environment.
  • Squashing does not change the live schema. It only compresses the history Prisma uses for diffing.

Never edit a shipped migration

A migration is “shipped” as soon as any environment has applied it and recorded it in _prisma_migrations. Editing it after the fact breaks the shadow-database replay and may cause migrate deploy to error or skip rows.

  • Write a follow-up migration for any correction.
  • Use -- prisma-migrate-disable-tx at the top of a migration file when the SQL cannot run inside a transaction (for example, CREATE INDEX CONCURRENTLY on hot tables).
  • Commit the generated SQL file alongside the schema.prisma change. Reviewers should read the SQL diff, not infer it from the schema diff. See prisma-schema for model conventions.

Graph View

Backlinks