Prisma Migrate Explained: migration.sql, Shadow Database and Drift

Prisma Migrate is a schema-first migration tool: you edit schema.prisma, Prisma computes the SQL that takes the database from its current state to the new one, and that SQL is committed as a plain migration.sql file. Everything else, the shadow database, drift detection, migrate deploy, exists to make that one loop safe for a team and for production.

What is on disk#

A Prisma project keeps its whole migration history in one directory, and the layout is fixed. Each migration is a folder named with a timestamp and a title, and the folder holds exactly one file of SQL:

prisma/
  schema.prisma
  migrations/
    migration_lock.toml
    20240101000001_create_owners_and_pets/
      migration.sql
    20240101000002_create_vets/
      migration.sql
    20240101000003_create_visits/
      migration.sql

The migration_lock.toml file records the database provider the history was generated for. Prisma refuses to mix providers inside one history, because the SQL in the files is dialect-specific: a history generated for PostgreSQL contains PostgreSQL DDL and will not run on MySQL.

The SQL itself is ordinary DDL. This is the first migration of a small clinic schema:

CREATE TABLE owners
(
    id         INT          NOT NULL,
    first_name VARCHAR(50)  NOT NULL,
    last_name  VARCHAR(50)  NOT NULL,
    address    VARCHAR(255),
    city       VARCHAR(80),
    telephone  VARCHAR(20),
    CONSTRAINT pk_owners PRIMARY KEY (id)
);

CREATE TABLE pets
(
    id         INT         NOT NULL,
    name       VARCHAR(50) NOT NULL,
    birth_date DATE,
    owner_id   INT         NOT NULL,
    CONSTRAINT pk_pets PRIMARY KEY (id),
    CONSTRAINT fk_pets_owners FOREIGN KEY (owner_id) REFERENCES owners (id)
);

Because the files are plain SQL, they can be read in code review, executed by a DBA without Prisma installed, and, as we will see at the end, run by any tool that knows the layout.

migrate dev versus migrate deploy#

Prisma splits the workflow into a development command and a deployment command, and the split is the single most important thing to understand about the tool.

The development command is interactive and destructive by design. It compares the migration history with your schema, writes a new migration, applies it to your local database and regenerates the client. When it finds a problem it cannot solve by adding a migration, it offers to drop the database and replay everything. That is fine on a laptop and catastrophic anywhere else, which is why the deployment command exists as a separate, non-interactive verb that only ever applies files that are already committed:

npx prisma migrate dev --name add_visits     # locally: create and apply
npx prisma migrate deploy                    # in a pipeline: apply what is pending
npx prisma migrate status                    # which migrations have run where

Why the shadow database exists#

migrate dev needs to answer two questions it cannot answer by looking at your development database alone: what schema does the migration history produce when applied from scratch, and does the development database still match it? To answer them, Prisma creates a second, temporary database, replays every committed migration into it, and then compares. That temporary database is the shadow database.

Two things follow from this. First, the database user Prisma runs as needs permission to create and drop databases, which is why migrate dev often fails against hosted services with restricted users. In that case you point Prisma at a database you created yourself with the shadowDatabaseUrl setting of the datasource. Second, the shadow database is purely a development concern: migrate deploy never creates one, so production credentials never need that privilege.

What drift is and how Prisma detects it#

Drift means the actual database schema no longer matches what the migration history says it should be. The classic causes are a column added by hand in a SQL console, a hotfix applied with prisma db push, or an applied migration file that was edited after the fact. Prisma detects drift in migrate dev by diffing the shadow database, which is the history's idea of the schema, against the development database. When the two differ, the command explains what differs and proposes a reset.

In production you never want that proposal, and you will not get it, because migrate deploy does not look for drift at all. It reads the _prisma_migrations table, applies the migration folders that are not recorded there, and stops. If production has drifted, the tool that tells you is prisma migrate diff, which can compare any two states, a live database, a schema file, a migrations directory or an empty database, and either describe the difference or emit the SQL that would close it:

npx prisma migrate diff \
  --from-url "$DATABASE_URL" \
  --to-migrations ./prisma/migrations \
  --shadow-database-url "$SHADOW_URL" \
  --script

A reliable way to notice drift early is to keep a picture of what the history produces and compare it with the diagram your database tooling shows you. The article on schema drift detection and prevention covers the organisational side of the problem.

Baselining a database that already exists#

Teams rarely start Prisma on an empty database. The usual situation is an existing production schema and a fresh, empty migrations directory. Running migrate deploy at that point would try to create tables that are already there and fail. The procedure Prisma documents for this is baselining:

1. Generate a first migration that describes the current schema from scratch, without applying it:

   mkdir -p prisma/migrations/0_init
   npx prisma migrate diff \
     --from-empty \
     --to-schema-datamodel prisma/schema.prisma \
     --script > prisma/migrations/0_init/migration.sql
   

2. Tell every existing environment that this migration is already applied, so it is recorded in _prisma_migrations without running:

   npx prisma migrate resolve --applied 0_init
   

3. From now on, new environments run migrate deploy from the beginning, and existing ones only pick up what comes after the baseline.

migrate resolve has a second form, --rolled-back, for a migration that failed halfway. Prisma records the failure in the history table and refuses to continue until you either fix the database by hand and mark the migration rolled back, or mark it applied because you finished its work manually. On engines with transactional DDL such as PostgreSQL a failed migration leaves nothing behind; on MySQL a partial migration is exactly what you must expect, as explained in the article on transactional DDL.

Editing the SQL Prisma generates#

Generated SQL is a proposal, not a decision. The most common reason to change it is a rename. Prisma cannot know that telephone became phone; it sees one column gone and another one added, and generates a DROP COLUMN followed by an ADD COLUMN, which would discard every value. The fix is to create the migration without applying it, edit the file, and only then apply:

npx prisma migrate dev --create-only --name rename_telephone
# edit prisma/migrations/<timestamp>_rename_telephone/migration.sql
npx prisma migrate dev

-- what Prisma wrote
ALTER TABLE "owners" DROP COLUMN "telephone";
ALTER TABLE "owners" ADD COLUMN "phone" VARCHAR(20);

-- what you want
ALTER TABLE "owners" RENAME COLUMN "telephone" TO "phone";

The same technique covers data backfills, custom indexes, check constraints Prisma's schema language cannot express, and anything else you would rather write by hand. Once a migration has been applied somewhere shared, treat the file as immutable: Prisma stores a checksum of every applied migration and reports a changed file as an error.

The _prisma_migrations table#

Prisma keeps its bookkeeping in a table named _prisma_migrations in the target database. For each migration folder it records the folder name, a checksum of the SQL, when the run started and finished, how many steps were applied, the log output of a failure and, if you resolved it that way, when it was marked rolled back. The table is the only state migrate deploy consults, which has a practical consequence: copying a database with its data but without that table makes Prisma believe nothing has ever been applied.

Prisma never removes the table, and tooling that reads your schema, whether an ORM introspection, a documentation generator or a diagram, has to know to skip it, in the same way it skips Flyway's flyway_schema_history or Django's django_migrations.

Where SchemaDrawer fits#

Because a Prisma history is a directory of plain SQL in a fixed layout, it can be run anywhere, and that is exactly what SchemaDrawer does with it. Upload the contents of prisma/migrations as they are, one folder per migration with its migration.sql, with migrationType=PRISMA and the engine and version you deploy to. The service starts a throw-away database in Docker, runs prisma migrate deploy against it, reads the resulting tables, columns, primary keys and foreign keys back over JDBC and returns an SVG diagram; the _prisma_migrations table is left out. It draws Prisma histories on PostgreSQL, MySQL, MariaDB, SQL Server and SQLite. The Prisma page shows the exact upload layout and a ready-made GitHub Action step.

Key takeaways#

• migrate dev creates migrations and may reset a database; migrate deploy only applies committed files. Never run the first one outside development.
• The shadow database is how migrate dev learns what the history produces; it needs create-database rights or an explicit shadowDatabaseUrl.
• Drift is detected by migrate dev and diagnosed by migrate diff; production is never reset automatically.
• Baseline an existing database with migrate diff --from-empty and migrate resolve --applied.
• Use --create-only to edit generated SQL before it runs, especially for renames.