Schema Diffing & Migration Generation

Schema diffing is the process of comparing two database schemas and generating the SQL statements needed to transform one into the other. This is the core mechanism behind Prisma Migrate’s declarative workflow.

Overview

Diffing powers several critical features:

Migration generation (migrate dev) - Diff Prisma schema vs. current database
Drift detection - Compare applied migrations vs. actual schema
Schema comparison - Understand differences between environments
Migration preview - See what will change before applying

Diffing is declarative - you define the desired end state, and the Schema Engine figures out how to get there.

How Diffing Works

The diffing process involves multiple steps:

1. Load Schemas

Both “from” and “to” schemas are loaded into the internal DatabaseSchema representation. Sources can be:

Prisma schema file - Parsed and converted to database schema
Existing database - Introspected via connector
Migration history - Applied to shadow database
Empty schema - For initial migrations

2. Structural Comparison

The connector’s differ walks both schemas and identifies:

Added tables/columns/indexes
Removed tables/columns/indexes
Modified types, defaults, constraints
Renamed entities
Changed relationships

3. DDL Generation

Database-specific SQL is generated to effect the changes:

-- Added columns
ALTER TABLE "User" ADD COLUMN "email" TEXT NOT NULL;

-- Modified columns
ALTER TABLE "Post" ALTER COLUMN "published" SET DEFAULT true;

-- New indexes
CREATE INDEX "User_email_idx" ON "User"("email");

-- New tables
CREATE TABLE "Comment" (
    "id" SERIAL PRIMARY KEY,
    "content" TEXT NOT NULL,
    "postId" INTEGER NOT NULL,
    FOREIGN KEY ("postId") REFERENCES "Post"("id")
);

4. Warning Detection

The DestructiveChangeChecker analyzes changes for potential issues:

Data loss (dropped tables/columns)
Type changes that might fail
Added non-nullable columns without defaults
Constraint violations

The diff Command

The diff method is exposed via JSON-RPC:

interface DiffParams {
  from: DiffTarget;
  to: DiffTarget;
  script: boolean;      // Output SQL or summary?
  exitCode: boolean;    // Return exit code 2 if diff?
  filters: SchemaFilter;
}

Diff Targets

Both from and to can be:

1. Empty Schema

{ "type": "empty" }

Represents a completely empty database.

2. Schema Datasource

{
  "type": "schemaDatasource",
  "files": [
    { "path": "schema.prisma", "content": "..." }
  ]
}

Uses the current database referenced by the Prisma schema (introspects it).

3. Schema Datamodel

{
  "type": "schemaDatamodel",
  "files": [
    { "path": "schema.prisma", "content": "..." }
  ]
}

Uses the Prisma schema itself as the source (without touching the database).

4. Migration History

{
  "type": "migrations",
  "lockfile": "...",
  "migrations": [...]
}

Applies migrations to a shadow database and introspects the result.

The url diff target type has been removed. Use schema-based targets instead.

Migration Generation Workflow

When you run prisma migrate dev, here’s what happens:

Step 1: Determine Current State

The Schema Engine needs to know the current schema state:

Connect to shadow database (or create one)
Apply all existing migrations to the shadow database
Introspect the shadow database
This introspected schema is the “from” state

Why use a shadow database?

The shadow database is necessary because:

Migrations are black boxes (can contain arbitrary SQL)
Schema Engine doesn’t parse SQL
The only way to know what migrations do is to run them
Running them on your dev database would be destructive

The shadow database is ephemeral and can be safely reset.

Step 2: Determine Desired State

The “to” schema comes from your Prisma schema file:

Parse the Prisma schema
Convert it to the internal DatabaseSchema representation
This is the “to” state

Step 3: Generate Diff

Compare the two schemas:

let migration = dialect.diff(from_schema, to_schema, &filter);

This returns a connector-specific Migration object containing:

DDL statements to execute
Warnings about destructive changes
Metadata about the changes

Step 4: Render Migration File

The migration is rendered as SQL:

let sql = dialect.render_script(&migration, &Default::default())?;

The SQL is written to a new migration file:

migrations/
  20240301120000_add_email/
    migration.sql

Step 5: Apply and Verify

The new migration is applied to your dev database and the shadow database is reset.

Diff Output Formats

Summary Mode

When script: false, returns a human-readable summary:

[+] Added tables
  - Comment

[*] Changed the `User` table
  [+] Added column `email`
  [+] Added index `User_email_idx`

[*] Changed the `Post` table
  [*] Changed column `published` (default changed)

Script Mode

When script: true, returns executable SQL:

-- CreateTable
CREATE TABLE "Comment" (
    "id" SERIAL PRIMARY KEY,
    "content" TEXT NOT NULL,
    "postId" INTEGER NOT NULL,
    CONSTRAINT "Comment_postId_fkey" FOREIGN KEY ("postId") 
        REFERENCES "Post"("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

-- AlterTable
ALTER TABLE "User" ADD COLUMN "email" TEXT NOT NULL;

-- CreateIndex
CREATE INDEX "User_email_idx" ON "User"("email");

Exit Codes

When exitCode: true:

0 - No differences
2 - Differences detected

Useful in CI to detect drift:

schema-engine diff --exit-code && echo "No drift" || echo "Drift detected!"

Destructive Changes

The DestructiveChangeChecker identifies changes that might cause data loss or failures:

Warnings

pub struct MigrationWarning {
    /// Human-readable description
    pub description: String,
    /// Structured warning type
    pub step_index: usize,
}

Example warnings:

Dropped table: “You are about to drop the User table. All data will be lost.”
Dropped column: “You are about to drop the column email on the User table. All data in the column will be lost.”
Type change: “You are changing the type of age from String to Int. This may fail if the data cannot be cast.”

Unexecutable Migrations

pub struct UnexecutableMigration {
    /// Why this migration cannot be executed
    pub description: String,
}

Example unexecutable migrations:

Added non-nullable column without default: “Cannot add required column email without a default value to existing rows.”
Added unique constraint on non-unique data: “Cannot add unique constraint on email because duplicate values exist.”

Unexecutable migrations will prevent migration creation. You must modify your schema or add migration steps to handle the issue.

Rename Detection

Diffing can detect renames vs. drop+create:

Without Rename Tracking

ALTER TABLE "User" DROP COLUMN "name";
ALTER TABLE "User" ADD COLUMN "fullName" TEXT;

❌ Data is lost

With Rename Tracking

ALTER TABLE "User" RENAME COLUMN "name" TO "fullName";

✅ Data is preserved Prisma uses heuristics to detect renames:

Similar names (edit distance)
Same type
Position in table
Annotations (@map)

You can manually edit generated migrations to change DROP+CREATE into RENAME when the differ doesn’t detect it.

Database-Specific Diffing

Each connector implements its own differ:

PostgreSQL

Full DDL support
Enums, arrays, composite types
Advanced constraints
Schema/namespace handling

MySQL

Limited DDL (can’t modify multiple columns in one statement on older versions)
No transactional DDL
Storage engine considerations

SQL Server

Schema-qualified names
Different default handling
Identity columns vs. sequences

SQLite

Very limited ALTER TABLE support
Often requires table recreation
No DROP COLUMN before 3.35.0

MongoDB

No traditional migrations
Schema is implicit
Limited diffing support

SQLite migration strategy

Because SQLite doesn’t support most ALTER TABLE operations, the differ generates:

Create new table with desired schema
Copy data from old table to new table
Drop old table
Rename new table to old name
Recreate indexes and triggers

This is slow and risky with large datasets.

Schema Filters

The SchemaFilter controls what’s compared:

pub struct SchemaFilter {
    /// Schemas/namespaces to include
    schemas: Vec<String>,
    /// Tables to exclude
    excluded_tables: Vec<String>,
}

Useful for:

Multi-tenant databases
Excluding non-Prisma tables
Focusing on specific schemas

Implementation Details

Connector Trait

pub trait SchemaDialect {
    fn diff(
        &self,
        from: DatabaseSchema,
        to: DatabaseSchema,
        filter: &SchemaFilter,
    ) -> Migration;
    
    fn render_script(
        &self,
        migration: &Migration,
        diagnostics: &DestructiveChangeDiagnostics,
    ) -> ConnectorResult<String>;
    
    fn migration_summary(
        &self,
        migration: &Migration,
    ) -> String;
    
    fn migration_is_empty(
        &self,
        migration: &Migration,
    ) -> bool;
}

Key Files

Diff command: schema-engine/commands/src/commands/diff.rs
Connector trait: schema-engine/connectors/schema-connector/src/diff.rs
SQL differ: schema-engine/connectors/sql-schema-connector/src/sql_schema_differ/
Destructive checker: schema-engine/connectors/schema-connector/src/destructive_change_checker.rs

Best Practices

1. Review Generated Migrations

Always review migrations before committing:

prisma migrate dev --create-only
# Review migration file
# Edit if necessary
prisma migrate dev

2. Handle Unexecutable Migrations

When adding non-nullable columns:

-- Generated (unexecutable)
ALTER TABLE "User" ADD COLUMN "email" TEXT NOT NULL;

Edit to:

-- Make it nullable first
ALTER TABLE "User" ADD COLUMN "email" TEXT;

-- Backfill data
UPDATE "User" SET "email" = '[email protected]' WHERE "email" IS NULL;

-- Add NOT NULL constraint
ALTER TABLE "User" ALTER COLUMN "email" SET NOT NULL;

3. Use Transactions

Add BEGIN; and COMMIT; to migrations when possible (see migrations guide).

4. Test in Staging

Always test migrations in a staging environment with production-like data before running in production.

5. Preserve Custom SQL

Generated migrations can be edited to add:

Custom indexes
Triggers
Views
Functions
Row-level security policies

The differ will respect custom SQL in existing migrations but won’t understand it when diffing.

Comparison with Other Tools

vs. Liquibase/Flyway

Those tools require you to write migrations manually. Prisma:

✅ Generates migrations automatically
✅ Detects schema changes
❌ Less control over exact SQL

vs. Entity Framework Migrations

Entity Framework is similar but:

Prisma uses actual database introspection (shadow DB)
Entity Framework uses in-memory models
Prisma handles arbitrary SQL in migrations

vs. Django Migrations

Django is similar but:

Django uses Python-based migrations
Prisma uses SQL migrations
Django’s migration graph is more complex

vs. db push

prisma db push is not migration-based:

No migration files generated
No history tracking
Immediate schema sync
Good for development, not for production

Never use db push in production. Always use migrate deploy with version-controlled migrations.

Migration System

Learn how generated migrations are tracked and applied

Introspection

Understand how database schemas are read

Schema Engine Overview

High-level architecture and concepts

Overview

Core Components

Query Compiler

Schema Engine

Prisma Schema Language

Development

​Overview

​How Diffing Works

​1. Load Schemas

​2. Structural Comparison

​3. DDL Generation

​4. Warning Detection

​The diff Command

​Diff Targets

​1. Empty Schema

​2. Schema Datasource

​3. Schema Datamodel

​4. Migration History

​Migration Generation Workflow

​Step 1: Determine Current State

​Step 2: Determine Desired State

​Step 3: Generate Diff

​Step 4: Render Migration File

​Step 5: Apply and Verify

​Diff Output Formats

​Summary Mode

​Script Mode

​Exit Codes

​Destructive Changes

​Warnings

​Unexecutable Migrations

​Rename Detection

​Without Rename Tracking

​With Rename Tracking

​Database-Specific Diffing

​PostgreSQL

​MySQL

​SQL Server

​SQLite

​MongoDB

​Schema Filters

​Implementation Details

​Connector Trait

​Key Files

​Best Practices

​1. Review Generated Migrations

​2. Handle Unexecutable Migrations

​3. Use Transactions

​4. Test in Staging

​5. Preserve Custom SQL

​Comparison with Other Tools

​vs. Liquibase/Flyway

​vs. Entity Framework Migrations

​vs. Django Migrations

​vs. db push

​Related Documentation

Migration System

Introspection

Schema Engine Overview

Build docs developers (and LLMs) love

Overview

How Diffing Works

1. Load Schemas

2. Structural Comparison

3. DDL Generation

4. Warning Detection

The diff Command

Diff Targets

1. Empty Schema

2. Schema Datasource

3. Schema Datamodel

4. Migration History

Migration Generation Workflow

Step 1: Determine Current State

Step 2: Determine Desired State

Step 3: Generate Diff

Step 4: Render Migration File

Step 5: Apply and Verify

Diff Output Formats

Summary Mode

Script Mode

Exit Codes

Destructive Changes

Warnings

Unexecutable Migrations

Rename Detection

Without Rename Tracking

With Rename Tracking

Database-Specific Diffing

PostgreSQL

MySQL

SQL Server

SQLite

MongoDB

Schema Filters

Implementation Details

Connector Trait

Key Files

Best Practices

1. Review Generated Migrations

2. Handle Unexecutable Migrations

3. Use Transactions

4. Test in Staging

5. Preserve Custom SQL

Comparison with Other Tools

vs. Liquibase/Flyway

vs. Entity Framework Migrations

vs. Django Migrations

vs. db push

Related Documentation