skip to content

Migrations

How a schema change reaches the database: migrate dev versus deploy versus reset, the migration history, the shadow database, drift detection, and baselining a database that already exists. Interviewers ask because a migration flow that is fine locally but rewrites production history is a classic outage story.

on this pageshow

explore

questions

6

In Prisma 7, what is the difference between `prisma db push` and `prisma migrate dev`, and when should each be used?

level: juniorimportance: must knowfreq 58%

answer

  1. end state versus recorded history
  2. no files, no migrations table rows
  3. prototype first, then capture a migration
  4. pushed tables look like drift later

basics

~20 s

Prisma's db push makes the database match the schema directly and leaves no migration files or migrations-table rows; migrate dev records each change as a SQL migration, applies it and tracks it. Push suits local prototyping; migrations suit anything shared.

solid answer

~50 s

`prisma db push` compares the Prisma schema with the live database and runs whatever SQL makes them match. It writes nothing to `prisma/migrations`, never creates or updates `_prisma_migrations`, and gives you no chance to edit the SQL; if a change could lose data it warns and asks, and in a non-interactive shell it fails unless you pass `--accept-data-loss`. `prisma migrate dev` compares the schema with the end state of the migration history instead, writes the difference to a new `migration.sql`, applies it and records it, so `prisma migrate deploy` can replay the same steps in every other environment. Use `db push` while sketching a schema on a disposable local database; once a change has to reach a teammate, CI or production, capture it as a migration. In Prisma 7 neither command runs `prisma generate`, so regenerate the client yourself.

code

bash · 8 lines
bash
# Prototype on a disposable local database
npx prisma db push
npx prisma generate

# Capture the settled schema as a migration
npx prisma migrate reset
npx prisma migrate dev --name add_orders
npx prisma generate

go deeper

for a junior

Know that db push syncs the database with no history while migrate dev writes migration files and records them, and that only migrations reach other environments.

for a middle

Explain what each command diffs against, what it leaves on disk and in _prisma_migrations, and why pushed tables show up as drift for the next migrate dev.

for a senior

Show where push ends in your team's workflow: disposable databases only, a reset before capturing the first migration, and an explicit prisma generate after either command in Prisma 7.

for a principal

Weigh the speed of push against the replayable history a team needs, and decide at what point in a project every schema change must travel as a reviewed migration.

## Two ways to change a database from a Prisma schema Prisma 7 has two commands that take the models in `schema.prisma` and make a SQL database match them. Both use the same schema engine, and both diff the Prisma schema against something to work out which SQL to run. The difference is **what they diff against and what they leave behind**. - `prisma db push` diffs the Prisma schema against the **live database** and executes the result immediately. - `prisma migrate dev` diffs the Prisma schema against the **end state of the migration history** (the files in `prisma/migrations`), saves the result as a new `migration.sql`, applies it and records it. The first gives you a database in the right shape. The second gives you a database in the right shape **plus a replayable record of how it got there**, and that record is what every other environment needs. ## What `db push` does - Introspects the database, works out the changes needed to reflect the Prisma schema, and runs them. - Writes no migration files and never creates or updates the `_prisma_migrations` table, so nothing is left for another environment to replay. - Offers no step where you could edit the SQL. When a change could lose data (dropping a column that still holds values, for example) it lists the warnings and asks whether to ignore them; in a non-interactive shell it fails and tells you to rerun with `--accept-data-loss`. - When a change cannot be executed against the current data at all, it stops and suggests `--force-reset`, which drops the database before pushing. - In Prisma 7 it no longer runs `prisma generate`, and the `--skip-generate` flag it used to accept has been removed. ## What `migrate dev` does 1. Replays the existing migration history in a temporary **shadow database** and compares the result with the development database, to detect **drift**: changes made outside migrations. 2. Applies any pending migration files, such as ones a teammate committed. 3. Diffs the history's end state against the Prisma schema, warns about possible data loss, and saves the difference as `prisma/migrations/<timestamp>_<name>/migration.sql`. 4. Applies that migration to the development database and records it in `_prisma_migrations`. In Prisma 7 it, too, leaves `prisma generate` (and the seed script) to you. ## Side by side | | `prisma db push` | `prisma migrate dev` | |---|---|---| | Diffs the Prisma schema against | the live database | the migration history | | Writes migration files | no | yes, one folder per change | | Writes to `_prisma_migrations` | never | one row per applied migration | | Lets you edit the SQL first | no | yes, with `--create-only` | | Uses a shadow database | no | yes | | Replayable by `prisma migrate deploy` | no | yes | | Runs `prisma generate` in Prisma 7 | no | no | ## When to use which `db push` fits the early, throwaway phase: sketching models on a local database, trying a relation, changing your mind several times an hour. You care about the end state, not the steps that reach it. Switch to `migrate dev` as soon as a change has to reach anyone else: a teammate, a CI database, staging, production. `prisma migrate deploy` replays only committed migration files, so a change made with `db push` simply does not exist for it. Migrations also let you shape the SQL. Prisma renders a renamed field as one column dropped and another added, and only an edited migration can turn that into a `RENAME COLUMN` that keeps the data. ## Moving from push to migrations The two are often combined: push while prototyping, then capture the result as a migration. The catch is that tables created by `db push` are **drift** as far as the migration history is concerned. The next `prisma migrate dev` replays the history in the shadow database, finds tables the history never created, prints `Drift detected: Your database schema is not in sync with your migration history.`, says the database has to be reset, suggests `prisma migrate reset` and exits without changing anything. Running `prisma migrate reset` then drops the development data and replays the history, and the following `prisma migrate dev` generates one migration containing everything you pushed. On a disposable local database that costs nothing, which is exactly why `db push` belongs there and not on a database whose data you need to keep.

  • What happens when you run `prisma migrate dev` on a database you have been changing with `db push`?
    It replays the migration history in the shadow database, compares the result with your database and finds tables the history never created. It prints `Drift detected`, says the database must be reset, suggests `prisma migrate reset` and exits without changing anything. After the reset, the next `migrate dev` generates one migration for everything you pushed.
  • Why can `db push` not rename a column without losing its data?
    Push only knows the desired end state. A renamed field looks like one column removed and another added, so it plans a drop plus an add and warns about losing the column's data. A migration created with `prisma migrate dev --create-only` can be edited into a `RENAME COLUMN` before it runs; push has no such step.

db push is editing a shared document with track changes switched off: the text ends up right, but nobody can replay how it got there. migrate dev keeps every change as a numbered revision that other copies can apply in order.

  • db push records a migration that migrate deploy can apply later
  • db push is fine for production because it is simply faster
  • In Prisma 7, db push and migrate dev regenerate the client automatically
  • --accept-data-loss makes db push keep the data it would drop
  • db push writes rows to _prisma_migrations so deploy skips them

Assumes Prisma 7, where neither db push nor migrate dev runs prisma generate and the --skip-generate flag was removed.

open as a page

In Prisma 7, how do `prisma migrate dev` and `prisma migrate deploy` differ, and why should only `migrate deploy` run against production?

level: middleimportance: must knowfreq 76%

basics

~20 s

Prisma's migrate dev authors migrations: it checks for drift in a shadow database, generates SQL from schema changes and may require a reset. Migrate deploy only applies committed, pending migration files, never generates or resets, so it is the production command.

open as a page

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%

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.

open as a page

With Prisma 7, after someone added a production column by hand, why does `prisma migrate deploy` fail with P3018 and then keep reporting P3009, and how do you recover?

level: seniorimportance: should knowfreq 40%

basics

~20 s

The hand-made column was drift that migrate deploy never checks for, so the migration adding it failed (P3018) and was recorded as failed, which blocks all later deploys (P3009). Mark it with prisma migrate resolve, applied or rolled back, then redeploy.

open as a page

After an upgrade from Prisma 6 to 7, why does `prisma migrate reset` leave tables empty and `migrate dev --skip-seed` fail, and how do you fix it?

level: seniorimportance: should knowfreq 32%

basics

~20 s

Prisma 7 stopped running the seed from migrate dev and migrate reset, stopped running generate from migrate dev and db push, and removed --skip-seed and --skip-generate. Run prisma generate and prisma db seed yourself, with the seed under migrations.seed.

open as a page

In Prisma 7, how do you adopt Prisma Migrate on an existing production database without recreating its tables or losing data?

level: seniorimportance: nice to knowfreq 28%

basics

~20 s

Baseline it: introspect with prisma db pull, write an initial migration with prisma migrate diff from empty to the schema, then mark it applied on each existing database with prisma migrate resolve --applied so deploy never runs it there.

open as a page