Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 36 ยท Migrations

Migration Files, Customizing SQL and Drift

Read and edit Prisma migration.sql files safely with --create-only, handle renames and data backfills, and understand schema drift, shadow databases and migrate reset.

A Prisma migration is plain SQL. You can open it, read it, review it in a pull request, and, before it is applied, change it. This lesson shows when editing is the right move, how to do it safely with --create-only, and what drift means when the real database stops matching your history.

The Prisma commands below are read-only; the TryIts are labelled stand-ins on node:sqlite (see the workflow lesson for why).

Reading a generated migration

Suppose you add a field to a model:

PRISMA
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  published Boolean  @default(false)
}

prisma migrate dev --name add_published produces SQL like this on PostgreSQL:

SQL
-- AlterTable
ALTER TABLE "Post" ADD COLUMN "published" BOOLEAN NOT NULL DEFAULT false;

Because the column has a default, existing rows get false and the migration succeeds. Without a default, adding a required column to a table that already has rows fails, and Prisma warns you before it even tries.

Why you sometimes must edit the SQL

Prisma generates the structure change by comparing two schemas. It cannot read your mind about intent. The classic case is a rename. If you rename name to fullName in the schema, Prisma sees one column disappear and another appear, and writes DROP COLUMN followed by ADD COLUMN. That destroys the data. You want RENAME COLUMN.

The safe workflow is:

Terminal
npx prisma migrate dev --create-only --name rename_name_to_full_name

--create-only writes the migration file but does not apply it. Open the file, replace the generated drop and add with the rename, then run npx prisma migrate dev to apply it.

SQL
-- Generated (destroys data):
-- ALTER TABLE "User" DROP COLUMN "name", ADD COLUMN "fullName" TEXT NOT NULL;

-- Edited (keeps data):
ALTER TABLE "User" RENAME COLUMN "name" TO "fullName";

The same technique lets you add things Prisma's schema language cannot express: partial indexes, check constraints, triggers, extensions (CREATE EXTENSION), views, or a data backfill.

Node.js Stand-in: generated drop+add loses data, edited rename keeps it

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The naive version leaves null everywhere. This is the single most common reason to use --create-only.

Splitting risky changes

Changes that touch data are best done in steps, often called expand and contract:

  1. Expand: add the new nullable column (migration 1).
  2. Backfill: copy or compute data into it (migration 2, or a script).
  3. Switch: deploy code that reads and writes the new column.
  4. Contract: make it required, drop the old column (migration 3, later).

Each step is safe to deploy on its own while the old version of your app may still be running. See seeding and data migrations for the backfill step.

Drift

Drift means the actual database differs from what your migration history would create. Typical causes: someone ran SQL by hand in production, a failed manual hotfix, or two branches that both added migrations with overlapping changes.

migrate dev detects drift by building the shadow database from your history and comparing it with your real development database. When they differ it says so and normally offers prisma migrate reset, which drops the database, replays all migrations and, if configured, seeds it. Never run reset on production.

migrate deploy does not detect drift (it has no shadow database). To see what differs, use the diff tool:

Terminal
npx prisma migrate diff \
  --from-migrations prisma/migrations \
  --to-config-datasource \
  --script

This prints the SQL that would turn the history-built schema into the live one, or the reverse, depending on how you order --from and --to. It is a read-only inspection command.

Node.js Stand-in: detect drift by comparing two schemas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Resolving a failed migration

If a migration fails halfway (on databases without transactional DDL, such as MySQL, part of it may have run), Prisma marks it as failed and refuses to continue. After you repair the database by hand, tell Prisma what happened:

Terminal
npx prisma migrate resolve --rolled-back 20260112093000_add_published   # it did not apply; try again
npx prisma migrate resolve --applied 20260112093000_add_published       # you applied it manually

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does prisma migrate dev --create-only do?

    --create-only generates migration.sql and stops, giving you a chance to review or change the SQL before applying.
  2. 2. You rename a column in schema.prisma. What would Prisma generate by default?

    Prisma compares structures and cannot tell a rename from a drop plus add. Edit the SQL to a RENAME before applying.
  3. 3. What is schema drift?

    Drift usually comes from manual changes made directly in the database outside migrations.
  4. 4. Why is expand and contract safer for removing a column?

    By adding first, switching code, and dropping last, no deployed version ever reads a column that is already gone.

Summary

  • Migrations are reviewable SQL files; edit one only before it has been applied elsewhere.
  • Use --create-only to fix renames, add constraints or triggers, or include data backfills.
  • Split destructive changes into expand, backfill, switch and contract steps.
  • Drift means the live database differs from the history; migrate dev detects it, migrate diff shows it, migrate reset fixes it in development only.
  • migrate resolve tells Prisma the outcome of a migration you repaired by hand.

Next: db push, db pull and Baselining