Migration System

The Schema Engine implements a traditional migrations system similar to ActiveRecord migrations or Flyway. It manages plain SQL migration files and tracks their application state using the _prisma_migrations table.

The _prisma_migrations Table

Every database managed by Prisma Migrate contains a migrations table that serves the same purpose as migration tables in nearly all migration tools. The terminology is SQL-specific because migrations tables are only used with SQL connectors.

Prisma’s migrations table is more extensive than typical migration tools, tracking additional metadata like checksums, logs, and rollback state.

Table Schema

The table schema is identical across all SQL databases (with minor dialect differences):

CREATE TABLE _prisma_migrations (
    id                      VARCHAR(36) PRIMARY KEY NOT NULL,
    checksum                VARCHAR(64) NOT NULL,
    finished_at             TIMESTAMPTZ,
    migration_name          VARCHAR(255) NOT NULL,
    logs                    TEXT,
    rolled_back_at          TIMESTAMPTZ,
    started_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    applied_steps_count     INTEGER NOT NULL DEFAULT 0
);

Column Descriptions

Column	Type	Description
`id`	VARCHAR(36)	Random unique identifier (v4 UUID). Never changes once written.
`checksum`	VARCHAR(64)	SHA-256 checksum of the migration file. Used to detect modifications. Never overwritten.
`finished_at`	TIMESTAMP	Completion timestamp. Only set for successful migrations. NULL means incomplete.
`migration_name`	VARCHAR(255)	Complete name of the migration directory (without path prefix).
`logs`	TEXT	Error messages recorded if the migration fails.
`rolled_back_at`	TIMESTAMP	Set by `prisma migrate resolve --rolled-back`. Causes the row to be ignored.
`started_at`	TIMESTAMP	Row creation timestamp, written before applying the migration.
`applied_steps_count`	INTEGER	Deprecated. Should not be used.

The applied_steps_count column is deprecated and should be considered legacy. Do not rely on it.

Migration States

A migration row can be in one of several states based on column values:

Successful Migration

finished_at IS NOT NULL
rolled_back_at IS NULL

Migration completed successfully and is active.

Failed Migration

finished_at IS NULL
rolled_back_at IS NULL
logs IS NOT NULL

Migration started but failed. Must be resolved before continuing.

Rolled Back

rolled_back_at IS NOT NULL

Marked as rolled back via migrate resolve. Ignored during deployment.

Pending (not in table)

No row exists for the migration

Migration file exists on disk but hasn’t been applied yet.

Migration Lifecycle

During `prisma migrate deploy`

migrate deploy is the command for unattended migrations (CI/CD). It exclusively:

Reads the migrations directory on disk
Queries _prisma_migrations to determine applied migrations
Checks for failed migrations (started but not finished)
Applies pending migrations in chronological order

Deploy algorithm in detail

For each migration in the migrations directory:If row exists in migrations table:

rolled_back_at IS NOT NULL → Skip (ignored)
finished_at IS NOT NULL → Skip (already applied)
finished_at IS NULL AND rolled_back_at IS NULL → ERROR: Failed migration must be resolved

If no row exists:

Insert row with id, migration_name, checksum, and started_at
Execute the migration SQL
On success: Set finished_at to current timestamp
On error: Do nothing (started without finished = error state)

migrate deploy will never reset your database and never uses a shadow database. It’s designed for production reliability.

During `prisma migrate resolve`

Resolves failed or stuck migrations: With --applied <migration>:

Marks the existing row as rolled back (rolled_back_at = now())
Creates a new row with finished_at = started_at

This preserves the historical record while marking the migration as complete. With --rolled-back <migration>:

Sets rolled_back_at = now() on the existing row
Future deployments will ignore this migration

migrate resolve doesn’t actually run or roll back SQL. It only updates the tracking table. You must manually fix the database state before resolving.

During `prisma migrate dev`

The development workflow is more strict:

Detects drift between migrations and actual database schema
Checks for modified migrations (via checksum comparison)
Checks for missing migrations (in table but not on disk)
Offers to reset the database when inconsistencies are detected

Why does migrate dev want to reset?

The dev workflow is pedantic because:

Migrations can contain arbitrary SQL (views, triggers, check constraints)
These features might not be representable in Prisma schema
The only way to guarantee the database matches migrations is to reset and reapply
Common causes: branch switching, editing migrations, merge conflicts

Migrate could use db push to match your schema, but that would lose custom SQL features the engines don’t understand.

Why No Down Migrations?

Prisma Migrate intentionally does not support automatic down/rollback migrations. Here’s why:

In Development

Down migrations are typically used to:

Iterate on a migration (down → edit → up)
Switch branches and undo changes

Prisma’s solution: migrate dev detects discrepancies and offers to reset your dev database. This is simpler and more reliable than down migrations.

In Production

Down migrations seem safe but have many hidden assumptions:

❌ The migration might have partially failed
❌ The migration must be reversible (no dropped tables/data)
❌ The down migration must work (are they tested?)
❌ Rolling back schema but not code causes version mismatch
❌ Large datasets make rollbacks too slow/lock-heavy

Down migrations provide a false sense of security.

Recommended approach: Follow the expand-and-contract pattern and roll forward, never backward.

Manual Rollbacks

Prisma supports manual rollbacks:

Diagnose the issue
Manually fix the database
Use migrate resolve to update the tracking table

This gives you control without false automation.

Best Practices

Separate Data and Schema Migrations

Prisma recommends completely separating data migrations from schema migrations:

Use a different tool/workflow for data migrations
Small projects can inline data changes in SQL
Large projects should run data migrations separately

Why? Data migrations make schema migrations:

Longer-running
Riskier
Harder to test
More likely to fail

Separating them de-risks schema changes.

Use Transactions When Possible

Migrations are not wrapped in transactions by default for:

Determinism - Same behavior in dev and production
Flexibility - You can add BEGIN;/COMMIT; yourself
Consistency - Not all databases support DDL in transactions (MySQL)
Performance - Large migrations without transactions are faster

How to add transactions

Add BEGIN; and COMMIT; to your migration file:

migration.sql

BEGIN;

ALTER TABLE "User" ADD COLUMN "email" TEXT NOT NULL;
CREATE INDEX "User_email_idx" ON "User"("email");

COMMIT;

MySQL does not support transactional DDL. Adding BEGIN/COMMIT will have no effect.

Never Use `IF NOT EXISTS`

Generated migrations should not use IF NOT EXISTS clauses because:

Diffing should always know if something exists
Precision is critical for migration generation
Conditional logic hides schema state

The rule: “Never use IF NOT EXISTS, we should always know if something exists.”

Drift Detection

Drift occurs when the actual database schema doesn’t match the applied migrations.

In Development (`migrate dev`)

✅ Detects drift and offers to resolve it (usually by reset)

In Production (`migrate deploy`)

❌ Does not detect drift by design Why? Drift detection requires a shadow database, which:

Adds complexity to deployment
Many teams are uncomfortable with temporary databases in prod pipelines
Deploy should be light, simple, and never block deployments

By design, deploy errs on the side of not standing in the way.

If drift occurs in production, use prisma migrate status to diagnose, then manually resolve and use migrate resolve to update the tracking table.

Command	Purpose
`migrate deploy`	Apply pending migrations (production)
`migrate dev`	Create and apply migrations (development)
`migrate status`	Check migration history and detect issues
`migrate resolve`	Mark migrations as applied/rolled-back
`migrate reset`	Reset database and reapply all migrations

Source Code References

Key implementation files:

Table creation: schema-engine/connectors/sql-schema-connector/src/flavour/{postgres,mysql,mssql}.rs
Migration persistence: schema-engine/connectors/sql-schema-connector/src/sql_migration_persistence.rs
Core logic: schema-engine/core/src/commands/

Overview

Core Components

Query Compiler

Schema Engine

Prisma Schema Language

Development

The _prisma_migrations Table

Table Schema

Column Descriptions

Migration States

Successful Migration

Failed Migration

Rolled Back

Pending (not in table)

Migration Lifecycle

During `prisma migrate deploy`

During `prisma migrate resolve`

During `prisma migrate dev`

Why No Down Migrations?

In Development

In Production

Manual Rollbacks

Best Practices

Separate Data and Schema Migrations

Use Transactions When Possible

Never Use `IF NOT EXISTS`

Drift Detection

In Development (`migrate dev`)

In Production (`migrate deploy`)

Source Code References

Build docs developers (and LLMs) love

Overview

Core Components

Query Compiler

Schema Engine

Prisma Schema Language

Development

​The _prisma_migrations Table

​Table Schema

​Column Descriptions

​Migration States

​Successful Migration

​Failed Migration

​Rolled Back

​Pending (not in table)

​Migration Lifecycle

​During prisma migrate deploy

​During prisma migrate resolve

​During prisma migrate dev

​Why No Down Migrations?

​In Development

​In Production

​Manual Rollbacks

​Best Practices

​Separate Data and Schema Migrations

​Use Transactions When Possible

​Never Use IF NOT EXISTS

​Drift Detection

​In Development (migrate dev)

​In Production (migrate deploy)

​Related Commands

​Source Code References

Build docs developers (and LLMs) love

The _prisma_migrations Table

Table Schema

Column Descriptions

Migration States

Successful Migration

Failed Migration

Rolled Back

Pending (not in table)

Migration Lifecycle

During `prisma migrate deploy`

During `prisma migrate resolve`

During `prisma migrate dev`

Why No Down Migrations?

In Development

In Production

Manual Rollbacks

Best Practices

Separate Data and Schema Migrations

Use Transactions When Possible

Never Use `IF NOT EXISTS`

Drift Detection

In Development (`migrate dev`)

In Production (`migrate deploy`)

Related Commands

Source Code References