Editing a migration
A migration is TypeScript you own. Fill in backfills, reorder steps, or write raw SQL, then recompile it with one command.
Each of your app's migrations is a directory in migrations/app/, and the file you edit inside it is migration.ts, a TypeScript file that describes the change as a list of calls such as this.addColumn(...). npx prisma migration plan writes the first draft of that file for you, but it can only work out the parts that follow from your contract. The decisions that depend on your data are left to you: what value existing rows should get, which order the steps should run in, and how to write a statement that has no method of its own.
The file that actually runs against your database is not the one you edit. npx prisma db migrate, which replaces prisma migrate deploy, runs the SQL in each migration's ops.json, and fixing that SQL by hand does not work, because Prisma ORM notices: npx prisma migration check fails and npx prisma db migrate refuses to run. Edit migration.ts instead and then recompile it, which means running the file itself with Node.js from your project root. Recompiling rewrites both ops.json and migration.json for you, and never connects to a database:
node migrations/app/20260707T1008_add_display_name/migration.tsWrote ops.json + migration.json to /path/to/my-app/migrations/app/20260707T1008_add_display_nameYou can run the file like this because the Prisma ORM CLI already needs Node.js 22.18 or newer on the 22 line, 24.11 or newer on the 24 line, or 26 or newer, and all of those run migration.ts directly, with no build step. Without --config, the file uses the prisma.config.ts that owns it, the one whose migrations directory holds the file: the nearest one above the migration's directory, else the one in the working directory. So it runs from the project root, from inside its own directory, or from anywhere else. When no config owns it, it fails with CLI.MIGRATION_CONFIG_NOT_FOUND; pass --config <path>.
Recompiling also rewrites migration.json, which records the contract state the migration starts from, the state it ends at, to, and a hash of the migration. That hash is how migration check and db migrate notice when ops.json or migration.json was changed by hand. Neither command reads migration.ts, so an edit you make there counts for nothing until you recompile.
Editing a migration only changes what happens on databases that have not applied it yet, because a database that already applied it will not apply it again. When you need to change a database that has already applied the migration, plan a new migration for the change instead. When you only want to try your edit against a development database that already applied it, change that database back to the state the migration starts from with npx prisma db update --to <dir>^, where <dir> is the migration's directory name. db update asks before it loses data, and Prisma ORM 8 never resets a database. Then run npx prisma db migrate --advance-ref db. The db ref is a file in migrations/app/refs/ that names the contract state you last applied in development, and --advance-ref db points it at what you just applied, so the next migration plan starts from there. This works only for a migration that changes the schema, and it changes back only the schema: rows the migration's data transform changed in a column the earlier state keeps stay changed, so to try an edited data transform on the original rows, restore a copy of the database from before it ran. A migration that only changes data starts and ends at the same contract state, so <dir>^ names the state the database is already at, and db update changes nothing. To try an edited data migration again, restore a copy of the database from before it ran.
Worked example: making a column required
Say you add a required displayName field to User in your contract, the contract.prisma file that replaced schema.prisma. The rows already in your user table have no displayName, so you have to decide what those rows get. Start by running npx prisma contract emit, which replaces prisma generate and writes contract.json and contract.d.ts, and then plan the migration as Generating a migration describes:
npx prisma migration plan --name add_display_name --backfill User.displayName⚠ Planned migration with placeholder(s) — edit migration.ts then run `node migration.ts` to self-emit
from: 967cb9f63587c1d90f64716780f04eec22b263424e89f07139233fc2c3d710c4
to: 866ab885a8a4bd8d9f5adc47784c068be7895a7182c55cc29f9f9967d3019484
app space: migrations/app/20260707T1008_add_display_nameIn that output, self-emit is what the CLI calls recompiling, and app space is the directory the new migration was written to. Run the file as node migrations/app/<dir>/migration.ts from the project root, or as node migration.ts from its directory. Because of --backfill User.displayName, migration plan writes the change as three steps rather than one, so you decide what the rows that already exist get: addColumn adds the column as nullable, dataTransform holds the backfill you write, and setNotNull makes the column required:
override get operations() {
return [
this.addColumn({
schema: 'public',
table: 'user',
column: col('displayName', 'text', { codecRef: { codecId: 'pg/text@1' } }),
}),
this.dataTransform(endContract, 'backfill-user-displayName', {
check: () => placeholder('backfill-user-displayName:check'),
run: () => placeholder('backfill-user-displayName:run'),
}),
this.setNotNull({ schema: 'public', table: 'user', column: 'displayName' }),
];
}The table is called user because the contract from Generating a migration maps User to it with @@map("user"). Without @@map, the table name is the model name exactly as written, so it would be User.
A dataTransform is where you say what should happen to the rows already in the table, and it takes two callbacks. Each one returns a query built with the sql query builder, not a SQL string. check returns the rows that still need the backfill, which is how Prisma ORM tells whether there is any work left to do, and run is the query that changes them. Inside each where, f holds the table's columns and fns holds functions such as eq, which is why fns.eq(f.displayName, null) becomes IS NULL. When you want to set a column from another column, pass update() a callback instead of a plain object: update((f) => ({ displayName: f.email })). The imports at the top of the file below come from snapshots, which are copies of your contract that migration plan saves in migrations/snapshots/<hash>/, and this migration imports the snapshot from before it and the one from after it, so the queries you write are typed against the contract you mean.
#!/usr/bin/env -S node
import type { Contract as End } from '../../snapshots/866ab885a8a4bd8d9f5adc47784c068be7895a7182c55cc29f9f9967d3019484/contract';
import endContractJson from '../../snapshots/866ab885a8a4bd8d9f5adc47784c068be7895a7182c55cc29f9f9967d3019484/contract.json' with { type: 'json' };
import type { Contract as Start } from '../../snapshots/967cb9f63587c1d90f64716780f04eec22b263424e89f07139233fc2c3d710c4/contract';
import startContractJson from '../../snapshots/967cb9f63587c1d90f64716780f04eec22b263424e89f07139233fc2c3d710c4/contract.json' with { type: 'json' };
import { Migration, MigrationCLI, col } from '@prisma/orm-postgres/migration';
import postgresAdapter from '@prisma/orm-postgres/adapter/runtime';
import { sql } from '@prisma/orm-postgres/builder/runtime';
import { createExecutionContext, createSqlExecutionStack } from '@prisma/orm-postgres/family-runtime';
import postgresTarget, { PostgresContractSerializer } from '@prisma/orm-postgres/target/runtime';
const endContract = new PostgresContractSerializer().deserializeContract<End>(endContractJson);
const stack = createSqlExecutionStack({ target: postgresTarget, adapter: postgresAdapter });
const db = sql<End>({
context: createExecutionContext({ contract: endContract, stack }),
rawCodecInferer: stack.adapter.rawCodecInferer,
});
export default class M extends Migration<Start, End> {
override readonly endContractJson = endContractJson;
override readonly startContractJson = startContractJson;
override get operations() {
return [
this.addColumn({
schema: 'public',
table: 'user',
column: col('displayName', 'text', { codecRef: { codecId: 'pg/text@1' } }),
}),
this.dataTransform(endContract, 'backfill-user-displayName', {
check: () =>
db.public.user
.select('id')
.where((f, fns) => fns.eq(f.displayName, null))
.limit(1),
run: () =>
db.public.user
.update({ displayName: 'Anonymous' })
.where((f, fns) => fns.eq(f.displayName, null)),
}),
this.setNotNull({ schema: 'public', table: 'user', column: 'displayName' }),
];
}
}
MigrationCLI.run(import.meta.url, M);If you compare that with the file migration plan wrote, the differences are all things you add or rename. The planned file's migration import also brings in placeholder, and it does not have the four other @prisma/orm-postgres imports or the endContract, stack, and db statements, which only build queries and never connect to a database, so you write those in yourself. The planned file also names its snapshot contract.json imports endContract and startContract, renamed here to endContractJson and startContractJson so that the name endContract is free for the deserialized contract. The only other thing you touch is the placeholder(...) calls, which become your check and run queries. The rest of each operation, such as codecRef, stays exactly as migration plan wrote it.
Now recompile the file with node migrations/app/20260707T1008_add_display_name/migration.ts. If you left a placeholder(...) call in it, recompiling fails with an error whose code is MIGRATION.UNFILLED_PLACEHOLDER, and npx prisma db migrate fails as well, because ops.json is still empty. Once it recompiles, read the UPDATE it produced in ops.json and check that it sets the value you meant on the rows you meant:
{
"id": "data_migration.backfill-user-displayName",
"label": "Data transform: backfill-user-displayName",
"operationClass": "data",
"target": { "id": "postgres" },
"precheck": [
{
"description": "Check backfill-user-displayName has work to do",
"sql": "SELECT EXISTS (SELECT \"id\" AS \"id\" FROM \"public\".\"user\" WHERE \"displayName\" IS NULL LIMIT 1) AS ok",
"params": []
}
],
"execute": [
{
"description": "Run backfill-user-displayName",
"sql": "UPDATE \"public\".\"user\" SET \"displayName\" = $1 WHERE \"displayName\" IS NULL",
"params": ["Anonymous"]
}
],
"postcheck": [
{
"description": "Verify backfill-user-displayName resolved all violations",
"sql": "SELECT NOT EXISTS (SELECT \"id\" AS \"id\" FROM \"public\".\"user\" WHERE \"displayName\" IS NULL LIMIT 1) AS ok",
"params": []
}
]
}There is one case where a correct-looking backfill still leaves you stuck, and it involves timestamps that Prisma ORM fills in for you. A column declared with @onCreate(now()) @onUpdate(now()), such as temporal.UpdatedAt, gets the current time whenever a row is created or updated, the way @updatedAt did in Prisma ORM 7. The User in this example has no such column, but the starter contract from npx prisma orm init does have one, called updatedAt. On a model like that, your backfill's update also sets updatedAt, and that is enough to make npx prisma migration check fail right after every recompile with an error whose code is MIGRATION.CHECK_HASH_MISMATCH, so npx prisma db migrate refuses to run.
The way out is to write the backfill against a contract of your own rather than the real one. Build db from an intermediate contract and pass that same contract to dataTransform, which the next section works through in full. In that contract, make displayName optional and declare updatedAt as TimestamptzString, a type Prisma ORM does not set for you, so the backfill leaves that column alone.
When a backfill reads a column the migration removes
Sooner or later a backfill has to read a column that the same migration takes away. Suppose the next migration replaces the boolean isAdmin on User with a required text column, role. Change your contract, run npx prisma contract emit, and plan the migration with npx prisma migration plan --name replace_is_admin. The examples below use the directory that command printed, migrations/app/20260708T0900_replace_is_admin, and yours has a different timestamp. The backfill reads isAdmin to decide each row's role, and that is where the trouble starts, because the end contract has no isAdmin and the start contract has no role. If you build db from the end contract, TypeScript rejects f.isAdmin, and recompiling fails with an error that does not name the column, so the cause is hard to spot.
What you need instead is a contract that describes the database as it is while the backfill runs, and you write that one yourself. Copy your whole contract into the migration directory as intermediate.prisma, then in its User model add isAdmin back and make role optional, because at that point no row has a role yet.
// use prisma-8
model User {
id Int @id
displayName String
isAdmin Boolean
role String?
@@map("user")
}Keep // use prisma-8 as the first line of the copy. contract emit skips any .prisma file that does not start with it, and because intermediate.prisma is the only file in the intermediate contract, emitting it without that line fails with an error whose code is CONTRACT.SOURCE_LOAD_FAILED.
In the same migration directory, add intermediate.config.ts, a second config file whose contract is intermediate.prisma. It needs no db.connection, because emitting a contract never connects to a database. If your contract uses an extension package, such as pgvector, add the same extensions array here as in your project's prisma.config.ts, such as extensions: [pgvector] inside ormConfig({ ... }), because this file does not take it from there:
import { definePrismaConfig } from 'prisma/config';
import { defineConfig as ormConfig } from '@prisma/orm-postgres/config';
export default definePrismaConfig({ orm: ormConfig({ contract: './intermediate.prisma' }) });Emit the intermediate contract with --config pointing at that file. Run this from your project root, because the --config path starts there. The ./intermediate.prisma path inside the config resolves from the directory the config file is in, so it names the copy in the migration directory:
npx prisma contract emit --config migrations/app/20260708T0900_replace_is_admin/intermediate.config.tsThe command writes intermediate.json and intermediate.d.ts next to intermediate.prisma, so the migration directory now holds four intermediate.* files. Commit all four along with the migration, because the migration no longer recompiles without them:
├── intermediate.config.ts
├── intermediate.d.ts
├── intermediate.json
├── intermediate.prisma
├── migration.json
├── migration.ts
└── ops.jsonNow fill in this migration's own planned migration.ts, rather than copying the displayName file, whose snapshot imports point at other contracts. In the listing, <start hash> and <end hash> stand for the hashes your planned file already imports, so keep those imports as they are.
The listing builds db from the intermediate contract and passes that same contract to dataTransform. Those two have to be the same contract: if you pass a different one, node migration.ts fails with an error whose code is MIGRATION.DATA_TRANSFORM_CONTRACT_MISMATCH.
The listing also moves the planned dropColumn for isAdmin to after the backfill, so isAdmin is still there when the backfill reads it. The backfill's dataTransform has two run callbacks, which run in the order you list them, so the second one sets member only on the rows the first one left empty.
#!/usr/bin/env -S node
import type { Contract as End } from '../../snapshots/<end hash>/contract';
import endContractJson from '../../snapshots/<end hash>/contract.json' with { type: 'json' };
import type { Contract as Start } from '../../snapshots/<start hash>/contract';
import startContractJson from '../../snapshots/<start hash>/contract.json' with { type: 'json' };
import type { Contract as Mid } from './intermediate';
import midContractJson from './intermediate.json' with { type: 'json' };
import { Migration, MigrationCLI, col } from '@prisma/orm-postgres/migration';
import postgresAdapter from '@prisma/orm-postgres/adapter/runtime';
import { sql } from '@prisma/orm-postgres/builder/runtime';
import { createExecutionContext, createSqlExecutionStack } from '@prisma/orm-postgres/family-runtime';
import postgresTarget, { PostgresContractSerializer } from '@prisma/orm-postgres/target/runtime';
const stack = createSqlExecutionStack({ target: postgresTarget, adapter: postgresAdapter });
const midContract = new PostgresContractSerializer().deserializeContract<Mid>(midContractJson);
const db = sql<Mid>({
context: createExecutionContext({ contract: midContract, stack }),
rawCodecInferer: stack.adapter.rawCodecInferer,
});
export default class M extends Migration<Start, End> {
override readonly endContractJson = endContractJson;
override readonly startContractJson = startContractJson;
override get operations() {
return [
this.addColumn({ schema: 'public', table: 'user', column: col('role', 'text', { codecRef: { codecId: 'pg/text@1' } }) }),
this.dataTransform(midContract, 'backfill-user-role', {
check: () => db.public.user.select('id').where((f, fns) => fns.eq(f.role, null)).limit(1),
run: [
() => db.public.user.update({ role: 'admin' }).where((f, fns) => fns.eq(f.isAdmin, true)),
() => db.public.user.update({ role: 'member' }).where((f, fns) => fns.eq(f.role, null)),
],
}),
this.setNotNull({ schema: 'public', table: 'user', column: 'role' }),
this.dropColumn({ schema: 'public', table: 'user', column: 'isAdmin' }),
];
}
}
MigrationCLI.run(import.meta.url, M);If you would rather not keep an intermediate contract around, you can reach the same result by splitting the change into two migrations. First add role to your contract as optional and plan, and in that migration add a dataTransform that sets role from isAdmin, with db built from its end contract, which still describes both columns. Then remove isAdmin, make role required, and plan again. That second plan writes a handle-nulls-user-role placeholder, which you fill in with what should happen to rows where role is still empty. Splitting the change needs no intermediate.* files at all, unless User has an @onUpdate(now()) column, such as temporal.UpdatedAt.
Raw SQL
When the statement you need has no method of its own, such as COMMENT ON, you write the SQL yourself. The Migration API reference lists every method that does exist, so check it first. Import rawSql from @prisma/orm-postgres/migration and add the call to the operations array alongside the other operations. You also say what kind of change the statement is, by setting operationClass to one of the four classes: additive, widening, destructive, or data. npx prisma db migrate runs all four of them, and adds a data-loss warning for destructive. Give each operation its own id, because error messages name it and Prisma ORM does not check that it is unique, so two operations sharing an id leave you unable to tell which one an error is about. The label is the text the CLI prints. One limit matters before you write any SQL: one npx prisma db migrate run on PostgreSQL is one transaction, so rawSql cannot run CREATE INDEX CONCURRENTLY, or anything else that has to run outside a transaction. For an index, use this.createIndex, which is a plain CREATE INDEX and blocks writes while it builds. The example below is only an illustration, because it enables the pgcrypto PostgreSQL extension, which you would really do with createExtension('pgcrypto') from the same module:
rawSql({
id: 'extension.pgcrypto',
label: 'Enable extension "pgcrypto"',
operationClass: 'additive',
target: { id: 'postgres' },
precheck: [
{ description: 'not yet enabled', sql: "SELECT NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pgcrypto')" },
],
execute: [{ description: 'enable it', sql: 'CREATE EXTENSION IF NOT EXISTS pgcrypto' }],
postcheck: [
{ description: 'now enabled', sql: "SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pgcrypto')" },
],
}),Give each operation a precheck and a postcheck if you can, because they are what lets Prisma ORM decide whether your SQL needs to run at all and whether it did what you meant, in the order that Every operation checks itself describes. Each check is a query whose first row must have true in its first column. Leaving either one out has a consequence worth knowing about before you do it, because an empty precheck lets the operation run, and an empty postcheck never passes before the operation, which means that operation runs on every npx prisma db migrate that applies its migration.
A check about one object can also carry expectation: { expects: 'absent' | 'present', object }, such as expectation: { expects: 'absent', object: 'extension "pgcrypto"' } on the precheck above. When the check fails, the error then names the object: the database already has extension "pgcrypto". Writing the checks in the Migration API reference has the details.
The same pattern on MongoDB
The same backfill pattern works on MongoDB, with different imports and a differently shaped check. Import dataTransform from @prisma/orm-mongo/target/migration, and write its check as an object whose source is a callback that returns the query. The example below needs no db statements, because it builds its queries directly: AggregateCommand for the check query and RawUpdateManyCommand for the run update, both from @prisma/orm-mongo/query-ast/execution. A call such as new RawUpdateManyCommand('products', { status: { $exists: false } }, { $set: { status: 'active' } }) takes the collection name, a filter, and an update. Here those two queries come back from the helpers existingProductsWithoutStatus and backfillRun, and each of them takes the end contract's storageHash, because every MongoDB query records the hash of the contract it was built for. The setValidation step is in the listing only because this migration also adds fields to products, and the collection's validator has to include them.
In this example both helpers are in the same migration.ts as the migration. MongoDB operations in the Migration API reference shows them in full, together with their import. The listing below is the part of the migration that calls them:
import { dataTransform, setValidation } from '@prisma/orm-mongo/target/migration';
override get operations() {
const storageHash = this.endContract.storage.storageHash;
const productsValidator = this.endContract.collection.products.validator;
return [
setValidation('products', productsValidator.jsonSchema, {
validationLevel: productsValidator.validationLevel,
validationAction: productsValidator.validationAction,
}),
dataTransform('backfill-product-status', {
check: { source: () => existingProductsWithoutStatus(storageHash) },
run: () => backfillRun(storageHash),
}),
];
}Starting from a blank migration
Not every migration comes from a contract change. When you want a data-only migration, which changes rows and leaves your contract state as it is, or a migration you intend to write entirely by hand, run npx prisma migration new. It writes an empty ops.json, a migration.json, and a migration.ts that already has the snapshot imports, the class, and an empty operations array. Nothing was planned for you, so there is no placeholder import to remove, but you do have to add the @prisma/orm-postgres imports and the endContract, stack, and db statements from the filled-in displayName listing.
migration new always ends at the contract state in your contract.json, and a data-only migration starts there too, so you pass that state's hash with --from. --from takes the contract hash in the to field of an existing migration's migration.json, or any start of that hash that matches only one migration's to hash.
To find your current contract's hash, run npx prisma migration graph and look for the row labelled @contract. It shows the first 7 characters of the hash, and you can pass those 7 characters to --from. The migration new reference lists the rest of the options:
npx prisma migration new --name backfill_scores --from <hash>For a data-only migration, always pass --from. Without it, migration new starts from the db ref, which names your current contract after db migrate --advance-ref db, so the migration would start and end at the same state. Without --from, migration new takes that as a mistake and stops with an error whose code is MIGRATION.NO_CHANGES, which tells you to change your contract first, or to pass --from if you want a data-only migration.
Write your operations in the new migration.ts and recompile it. Until you do, the migration is still the empty one the command wrote, so npx prisma migration check and npx prisma migration plan both fail.
Editing checklist
- Edit
migration.ts, neverops.json, then recompile it. - Review the diff of
ops.json, the file that runs. - Run
npx prisma migration check, which prints✔ All checks passedwhen nothing is wrong. - Commit
migration.ts,ops.json,migration.json, themigrations/snapshots/<hash>/directories thatmigration.tsimports, and yourcontract.jsonandcontract.d.ts. If the migration hasintermediate.*files, commit those too.
Hand-written MongoDB data transforms cannot use the query builder for updates. No command notices a migration.ts edit that you did not recompile, so recompile after every edit.
Prompt your coding agent
Projects created with npm create prisma@latest include the Prisma ORM skills, instruction files for your coding agent. In an existing project, run npx prisma skills sync. Ask your agent to:
- "Fill in the placeholder in the latest migration: backfill
displayNamewith the user's email prefix." - "Add a data transform to this migration that normalizes existing
phonevalues before the unique constraint." - "Recompile the migration I just edited and show me the ops.json diff."
See also
- Generating a migration: where the first draft of
migration.tscomes from - Applying a migration: run the edited migration
- Migration API: every method and helper
migration.tscan call - Data Migrations in Prisma 8: how
dataTransformwas designed
Updated