skip to content

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.