skip to content

In Prisma Migrate, how do you customise a generated migration's SQL, and what happens if you edit one that has already been applied?

level: middleimportance: should knowfreq 42%

answer

  1. draft first, apply second
  2. --create-only, then migrate dev again
  3. rename becomes drop plus add
  4. checksum in _prisma_migrations

basics

~20 s

Run prisma migrate dev --create-only to write the migration without applying it, edit migration.sql, then run migrate dev to apply it. Editing an applied migration breaks its stored checksum: migrate dev demands a reset, migrate deploy only warns.

solid answer

~40 s

Prisma writes each migration to `prisma/migrations/<timestamp>_<name>/migration.sql`. To change the SQL before it runs, use `prisma migrate dev --create-only`, which writes the file without applying it; edit it, then run `prisma migrate dev` to apply the edited version. You need this for a rename, which Prisma generates as a dropped column plus a new one, and for things the schema cannot express, such as a trigger. Once a migration has run, `_prisma_migrations` holds a checksum of its file. Edit the file afterwards and `migrate dev` treats it as a history conflict, says the database must be reset and exits, while `migrate deploy` prints a modified-migration warning and carries on without re-running anything. The fix is to restore the original file and put the change in a new migration.

code

bash · 3 lines
bash
npx prisma migrate dev --create-only --name rename_biography
# edit prisma/migrations/<timestamp>_rename_biography/migration.sql
npx prisma migrate dev

go deeper

for a junior

Recall that migration files live in prisma/migrations, that --create-only lets you edit one before it runs, and that an applied migration should never be edited.

for a middle

Explain the --create-only flow, why a rename needs it, and how the checksum in _prisma_migrations makes migrate dev and migrate deploy react differently to an edit.

for a senior

Describe how an edited applied migration lets environments diverge silently, how you would spot it from the deploy warning, and how you repair the history without touching production data.

for a principal

Set the team rule that applied migrations are immutable and hand-written SQL goes through --create-only drafts, and decide how such drafts are reviewed before merge.

## Where a Prisma migration lives `prisma migrate dev` writes each change to its own folder, `prisma/migrations/<timestamp>_<name>/migration.sql`, where the name is the `--name` you passed, slugified with underscores. Beside the folders sits `migration_lock.toml`, which records the database provider so Prisma can detect a history written for a different provider. The folder is the **source of truth** for the history, and the whole of it, lock file included, is committed to version control. In the database, the `_prisma_migrations` table holds one row per migration Prisma has run: its name, a **checksum** of the file, when it started and finished, and a `logs` column holding the error if it failed. ## Editing before applying: `--create-only` The SQL Prisma generates is a starting point, and some changes need a hand on it: - a field or model was **renamed**: Prisma sees one column dropped and another added, which throws the data away; - you need something the Prisma schema cannot express, such as a trigger, a stored procedure or a PostgreSQL extension; - data has to be handled in between, for example filling a new column before it becomes required. The flow: 1. Change the Prisma schema. 2. Run `prisma migrate dev --create-only --name <name>`. Prisma writes the migration folder but does not apply it. With no schema change at all, it writes an empty migration you can fill with hand-written SQL. 3. Edit `migration.sql`. 4. Run `prisma migrate dev` again. It applies the edited migration and records the checksum of the file as it now is. For a rename, the generated `DROP COLUMN` plus `ADD COLUMN` becomes one `RENAME COLUMN`. The end state still matches the Prisma schema, so `migrate dev` has nothing further to generate. ## Editing after applying Once a migration has run on any database, its file is the record of what happened there. Edit it and the stored checksum no longer matches the file. The commands react differently: | Command | Reaction to a modified, already-applied migration | |---|---| | `prisma migrate dev` | treats it as a history conflict, says the development database must be reset, suggests `prisma migrate reset` and exits | | `prisma migrate deploy` | prints `WARNING The following migrations have been modified since they were applied:` and the names, then applies pending migrations as usual | Two consequences follow: - **The edit never runs where the original already ran.** Production keeps whatever the original SQL did. Only databases built from scratch, such as a new developer's or a CI database, get the edited version, and the environments drift apart without any error. - **The warning comes back on every deploy.** The checksum mismatch is permanent until the file is restored. It is the **checksum**, not the shadow database, that notices an edited file; the shadow database checks the schema the history produces. If the edit also changes that end state (the Prisma docs widen a `VARCHAR(550)` to `VARCHAR(560)` while the schema still says 550), then after a reset `migrate dev` generates yet another migration to bring the column back in line with the Prisma schema. ## Doing it right - Restore the original file from version control, so the checksum matches again. - Put the change you wanted in a **new** migration, created with `--create-only` if it needs hand-written SQL. - The one expected edit is to a migration that **failed** and must change before it can succeed: fix the file, mark it with `prisma migrate resolve --rolled-back`, run `prisma migrate deploy` again, and commit the fixed file so every environment runs the same SQL. ## The other half: missing migrations Deleting an applied migration's folder is the mirror image of editing it, and it happens without anyone meaning to: switch from one feature branch to another, and the development database's `_prisma_migrations` still lists migrations that the checked-out folder no longer has. - `prisma migrate dev` treats a missing applied migration as a history conflict too, and again stops with a request to reset the development database. - `prisma migrate deploy` does **not** warn when an applied migration is missing locally; it simply applies whatever is pending. - `prisma migrate status` compares both sides and, when they have diverged, prints the last common migration, the local migrations not yet applied, and the applied migrations that are not found locally. On a development database, resetting is usually the cheapest answer. On a shared database, restore the missing folder instead, because the history in version control has to describe what that database has actually run.

  • How do you add SQL that has no Prisma schema change behind it, such as a trigger?
    Run `prisma migrate dev --create-only --name add_audit_trigger` with the schema unchanged. Prisma writes an empty `migration.sql`; put the SQL in it and run `prisma migrate dev` to apply it. From then on it travels with the history like any other migration, and `migrate deploy` applies it everywhere.
  • A migration you edited after applying it has already reached production. What do you do?
    Restore the file to exactly what production ran, so the checksum matches and the warning stops. If you still need the change, write it as a new migration so every database runs it. Never rely on the edited file, because databases that already applied the original will never run it.

  • Editing an applied migration re-runs it on the next migrate deploy
  • The shadow database is what detects an edited migration file
  • migration_lock.toml stores the checksum of every migration
  • Prisma generates RENAME COLUMN when you rename a field
  • --create-only applies the migration but skips recording it