How to migrate data with Prisma ORM using the expand and contract pattern

Replace a column without downtime using the expand and contract pattern, with the data backfill inside a Prisma ORM migration.

Introduction

When you change a production schema, you want the data to stay consistent and the app to keep running. The expand and contract pattern gets you there in two reviewable steps: first add the new column and copy the data across (expand), then remove the old column once nothing reads it (contract). In this guide you replace a published boolean on a Post model with a status enum.

In Prisma ORM the data move is not a separate script. A migration is a TypeScript file, and the backfill is one more operation in it, a dataTransform that runs in the same transaction as the schema change. Every command and output below was run against a local PostgreSQL database.

Using Prisma ORM 7?

Prisma ORM 8 is a release candidate. Prisma ORM 7 remains fully supported; the Prisma ORM 7 version of this guide is at /guides/v7/database/data-migration.

Prerequisites

Node.js 22.18 or newer on the 22 line, 24.11 or newer on the 24 line, or 26 or newer (24 recommended)
A PostgreSQL connection string for a development database, and one for the database you deploy to
Git, for the branch-per-migration workflow

Use with your agent

To delegate this guide to your coding agent, copy the prompt below and hand it over:

Use with your agent

Perform an expand-and-contract migration with Prisma ORM, following https://www.prisma.io/docs/guides/database/data-migration.md.

1. In a project with a `Post` model that has a `published Boolean @default(false)` field, make sure Prisma ORM is set up (`npx prisma@latest orm init --yes --target postgres --authoring psl` if it is not), `DATABASE_URL` is in `.env`, and the current contract is applied as a migration (`npx prisma contract emit`, `npx prisma migration plan --name init`, `npx prisma db migrate --advance-ref db`). Run `npx prisma@latest init` so the Prisma agent skills are installed, and use them.
2. Expand: add `enum Status { @@type("pg/text@1") Draft = "Draft" InProgress = "InProgress" InReview = "InReview" Published = "Published" }` and `status Status @default(Draft)` to `Post`. Keep `published`. Run `contract emit` and `migration plan --name add_status`.
3. Open the planned `migration.ts` and append a `this.dataTransform(endContract, 'backfill-post-status', { check, run })` operation built with the typed SQL builder against the migration's own end contract: `check` selects `id` from `Post` where `published = true AND status != 'Published'` with `limit(1)`, `run` updates those rows to `status = 'Published'`. Recompile with `node <migration-dir>/migration.ts`, confirm `ops.json` contains a `data` operation, then apply with `npx prisma db migrate --advance-ref db`. Verify with a query that every row with `published = true` now has `status = 'Published'`.
4. Update application code to read and write `status` instead of `published`.
5. Contract: remove `published` from the contract, run `contract emit`, `migration plan --name drop_published`, review the destructive warning, and apply with `npx prisma db migrate --advance-ref db`.
6. For the production database, run `npx prisma db migrate --db "$PRODUCTION_DATABASE_URL"`, adding `--show` first if you want to preview the route. Do not use `db update` against production.

1. Set up the project

If you already have a Prisma ORM project whose contract is applied through migrations, skip to step 2. This step builds the starting point: a Post model with a published boolean, an init migration, and three rows of data.

Create a project and add Prisma ORM to it:

mkdir expand-contract && cd expand-contract

bun init
bunx prisma@latest orm init --yes --target postgres --authoring psl

orm init writes prisma.config.ts, src/prisma/contract.prisma, src/prisma/db.ts, and .env.example, installs @prisma/orm-postgres and the prisma CLI, and emits the contract once. There is nothing to generate afterwards: the runtime reads the emitted contract.json directly, so there is no prisma generate step and no client package to install.

npm init -y sets "type": "commonjs", and orm init warns that the generated db.ts needs ES modules. Change it to "type": "module" in package.json.

Put your development connection string in .env. Both prisma.config.ts and db.ts load it:

.env

DATABASE_URL="postgres://user:password@localhost:5432/expand_contract"

Replace the generated contract with the starting model:

src/prisma/contract.prisma

// use prisma-8

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

Emit the contract, plan the first migration, and apply it. --advance-ref db records where your development database is, so the next migration plan knows what to diff against:

bunx prisma contract emit
bunx prisma migration plan --name init
bunx prisma db migrate --advance-ref db

✔ Applied 1 migration(s) (2 operation(s)) across 1 contract space(s)

App space
├─ Create schema "public"
├─ Create table "Post"
└─ marker e8804d8e27d8737475c7be0c538afd9665907df8b82c605bd5cd50926dd73fde

✔ Advanced ref "db" → e8804d8e27d8737475c7be0c538afd9665907df8b82c605bd5cd50926dd73fde

Seed a few posts so the migration has data to move. Node.js runs TypeScript directly:

src/seed.ts

import { db } from "./prisma/db.ts";

await db.orm.public.Post.create({ title: "Hello World", content: "First post", published: true });
await db.orm.public.Post.create({ title: "Draft ideas", content: null, published: false });
await db.orm.public.Post.create({ title: "Release notes", content: "v1.0", published: true });

const posts = await db.orm.public.Post.select("id", "title", "published").all();
console.log(posts);

await db.close();

node src/seed.ts

[
  { id: 1, title: 'Hello World', published: true },
  { id: 2, title: 'Draft ideas', published: false },
  { id: 3, title: 'Release notes', published: true }
]

2. Expand the contract

Create a branch for the expand step:

git checkout -b expand-post-status

Add the Status enum and a status field. Leave published in place; the app keeps working against it until the contract step. Prisma ORM stores an enum as text with a CHECK constraint, and the @@type line says so:

src/prisma/contract.prisma

// use prisma-8

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  status    Status  @default(Draft) 
}

enum Status { 
  @@type("pg/text@1") 
  Draft      = "Draft" 
  InProgress = "InProgress" 
  InReview   = "InReview" 
  Published  = "Published" 
}

Emit the contract and plan the migration. Planning is offline; it diffs the emitted contract against the db ref and writes a migration directory:

bunx prisma contract emit
bunx prisma migration plan --name add_status

✔ Planned 2 operation(s)

migrations/app/20260910T1552_add_status
├─ Add column "status" to "Post"
└─ Add check constraint "Post_status_check_eaa35e2a" on "Post"

from:       e8804d8e27d8737475c7be0c538afd9665907df8b82c605bd5cd50926dd73fde
to:         0e9486dcd7f4216d673cbe0ec503edb638520dec9b0a8a22f48bb88edd1442c4
app space:  migrations/app/20260910T1552_add_status

ℹ DDL preview

ALTER TABLE "public"."Post" ADD COLUMN "status" text DEFAULT 'Draft' NOT NULL;
ALTER TABLE "public"."Post" ADD CONSTRAINT "Post_status_check_eaa35e2a" CHECK ("status" IN ('Draft', 'InProgress', 'InReview', 'Published'));

Your directory name carries your own timestamp. The planner did not ask for a backfill because the column has a default, so every existing row would become Draft. That is wrong for posts that are already published. The next step fixes it.

3. Add the data migration

Open migrations/app/<timestamp>_add_status/migration.ts. It is the file the planner wrote, and you edit it like any other TypeScript. Add the imports that wire up a typed SQL builder against this migration's own end contract, then append a dataTransform operation after the two schema operations:

migrations/app/20260910T1552_add_status/migration.ts

#!/usr/bin/env -S node
import type { Contract as End } from '../../snapshots/0e9486dcd7f4216d673cbe0ec503edb638520dec9b0a8a22f48bb88edd1442c4/contract';
import endContractJson from '../../snapshots/0e9486dcd7f4216d673cbe0ec503edb638520dec9b0a8a22f48bb88edd1442c4/contract.json' with { type: 'json' };
import type { Contract as Start } from '../../snapshots/e8804d8e27d8737475c7be0c538afd9665907df8b82c605bd5cd50926dd73fde/contract';
import startContract from '../../snapshots/e8804d8e27d8737475c7be0c538afd9665907df8b82c605bd5cd50926dd73fde/contract.json' with { type: 'json' };
import { Migration, MigrationCLI, col, lit } 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(endContractJson) as End; 

const db = sql<End>({ 
  context: createExecutionContext({ 
    contract: endContract, 
    stack: createSqlExecutionStack({ target: postgresTarget, adapter: postgresAdapter }), 
  }), 
  rawCodecInferer: postgresAdapter.rawCodecInferer, 
}); 

export default class M extends Migration<Start, End> {
  override readonly startContractJson = startContract;
  override readonly endContractJson = endContractJson;

  override get operations() {
    return [
      this.addColumn({
        schema: 'public',
        table: 'Post',
        column: col('status', 'text', {
          notNull: true,
          default: lit('Draft'),
          codecRef: { codecId: 'pg/text@1' },
        }),
      }),
      this.addCheckConstraint({
        schema: 'public',
        table: 'Post',
        constraint: 'Post_status_check_eaa35e2a',
        expression: "\"status\" IN ('Draft', 'InProgress', 'InReview', 'Published')",
      }),
      this.dataTransform(endContract, 'backfill-post-status', { 
        check: () =>
          db.public.Post 
            .select('id') 
            .where((f, fns) => fns.and(fns.eq(f.published, true), fns.ne(f.status, 'Published'))) 
            .limit(1), 
        run: () =>
          db.public.Post 
            .update({ status: 'Published' }) 
            .where((f, fns) => fns.and(fns.eq(f.published, true), fns.ne(f.status, 'Published'))), 
      }), 
    ];
  }
}

MigrationCLI.run(import.meta.url, M);

The snapshot paths in your file carry your own contract hashes; keep the ones the planner wrote. The planner writes the JSON import as endContract. Rename that import to endContractJson, as the version above does, and bind endContract to the deserialized contract, because the builder and dataTransform both need the deserialized form.

Three things to notice:

check is a query that returns a row while work remains. Prisma compiles it twice: as an EXISTS precheck that decides whether the step runs, and as a NOT EXISTS postcheck that proves the step finished.
run is the update. Both closures are typed against the migration's own contract snapshot, which is why f.status type-checks even though the column does not exist in the database yet.
There is no published: false branch. Those rows take the column default, Draft, when ADD COLUMN runs.

Recompile the migration. Running the file regenerates ops.json, the file db migrate actually executes, and re-signs migration.json:

node migrations/app/20260910T1552_add_status/migration.ts

Wrote ops.json + migration.json to /Users/you/expand-contract/migrations/app/20260910T1552_add_status

Review what the backfill compiled to. migration show lists the operation, and ops.json holds the exact SQL:

bunx prisma migration show 20260910T1552_add_status

✔ 20260910T1552_add_status

3 operation(s)
├─ Add column "status" to "Post"
├─ Add check constraint "Post_status_check_eaa35e2a" on "Post"
└─ Data transform: backfill-post-status

migrations/app/20260910T1552_add_status/ops.json (the data operation)

{
  "id": "data_migration.backfill-post-status",
  "operationClass": "data",
  "precheck": [{ "sql": "SELECT EXISTS (SELECT \"id\" AS \"id\" FROM \"public\".\"Post\" WHERE (\"published\" = $1 AND \"status\" != $2) LIMIT 1) AS ok", "params": [true, "Published"] }],
  "execute": [{ "sql": "UPDATE \"public\".\"Post\" SET \"status\" = $1 WHERE (\"published\" = $2 AND \"status\" != $3)", "params": ["Published", true, "Published"] }],
  "postcheck": [{ "sql": "SELECT NOT EXISTS (SELECT \"id\" AS \"id\" FROM \"public\".\"Post\" WHERE (\"published\" = $1 AND \"status\" != $2) LIMIT 1) AS ok", "params": [true, "Published"] }]
}

Commit migration.ts, ops.json, and migration.json together. Recompiling stores a hash of ops.json and migration.json inside migration.json. If either file changes after that, db migrate refuses to run the migration, and npx prisma migration check reports the same problem before you commit.

4. Apply the expand migration

Apply it to your development database. On PostgreSQL the whole run is one transaction, so the column, the constraint, and the backfill land together or not at all:

bunx prisma db migrate --advance-ref db

✔ Applied 1 migration(s) (3 operation(s)) across 1 contract space(s)

App space
├─ Add column "status" to "Post"
├─ Add check constraint "Post_status_check_eaa35e2a" on "Post"
├─ Data transform: backfill-post-status
└─ marker 0e9486dcd7f4216d673cbe0ec503edb638520dec9b0a8a22f48bb88edd1442c4

✔ Advanced ref "db" → 0e9486dcd7f4216d673cbe0ec503edb638520dec9b0a8a22f48bb88edd1442c4

Check the rows. The published posts moved to Published, and the draft kept the default:

src/posts.ts

import { db } from "./prisma/db.ts";

const posts = await db.orm.public.Post
  .select("id", "title", "published", "status")
  .orderBy((p) => p.id.asc())
  .all();
console.log(posts);

await db.close();

node src/posts.ts

[
  { id: 1, title: 'Hello World', published: true, status: 'Published' },
  { id: 2, title: 'Draft ideas', published: false, status: 'Draft' },
  { id: 3, title: 'Release notes', published: true, status: 'Published' }
]

Both columns exist now, so this is the moment to move application code from published to status. The new field is typed as 'Draft' | 'InProgress' | 'InReview' | 'Published' in the emitted contract:

const published = await db.orm.public.Post
  .select("id", "title", "status")
  .where((p) => p.status.eq("Published"))
  .orderBy((p) => p.id.asc())
  .all();

[
  { id: 1, title: 'Hello World', status: 'Published' },
  { id: 3, title: 'Release notes', status: 'Published' }
]

Merge the branch and deploy it. Wait until nothing reads or writes published before you continue.

5. Contract the schema

Create a branch for the cleanup:

git checkout -b drop-published-column

Remove the old field:

src/prisma/contract.prisma

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false) 
  status    Status  @default(Draft)
}

Emit and plan. The planner flags the drop as destructive, which is the review signal for this step:

bunx prisma contract emit
bunx prisma migration plan --name drop_published

✔ Planned 1 operation(s)

migrations/app/20260910T1554_drop_published
└─ ⚠ Drop column "published" from "Post"

⚠ This migration contains destructive operations that may cause data loss.

ℹ DDL preview

ALTER TABLE "public"."Post" DROP COLUMN "published";

Nothing to edit this time. Apply it:

bunx prisma db migrate --advance-ref db

✔ Applied 1 migration(s) (1 operation(s)) across 1 contract space(s)

App space
├─ ⚠ Drop column "published" from "Post"
└─ marker 79567c619bcb9790bf476efc358730a10317c94fc352e908e19424fc6441350a

The data survived the round trip. Drop published from the select in src/posts.ts and run it again:

[
  { id: 1, title: 'Hello World', status: 'Published' },
  { id: 2, title: 'Draft ideas', status: 'Draft' },
  { id: 3, title: 'Release notes', status: 'Published' }
]

migration log reads the database's own ledger of what ran:

bunx prisma migration log

Applied at                  Migration                     Change             Ops
2026-09-10 17:51:19 +02:00  20260910T1551_init            ∅ → e8804d8        2 ops
2026-09-10 17:53:46 +02:00  20260910T1552_add_status      e8804d8 → 0e9486d  3 ops
2026-09-10 17:55:39 +02:00  20260910T1554_drop_published  0e9486d → 79567c6  1 ops

6. Deploy to production

Production runs the same migrations from the same files; only the connection string changes. db migrate reads the database's marker, finds the path through the migration graph, and applies whatever is pending, backfill included. Your deploy pipeline needs one command:

bunx prisma db migrate --db "$PRODUCTION_DATABASE_URL"

It refuses to run a migration whose ops.json or migration.json changed after it was compiled. If your pipeline deploys each environment to a ref, as Applying a migration shows, add --to <ref> to that command. To see the route before it runs, add --show, which previews it without changing anything. Here is that preview against a production database that shipped the init migration and holds live rows, followed by the apply:

ℹ The following 2 migrations will run:

  20260910T1552_add_status    e8804d8 → 0e9486d
  20260910T1554_drop_published  0e9486d → 79567c6

✔ Applied 2 migration(s) (4 operation(s)) across 1 contract space(s)

App space
├─ Add column "status" to "Post"
├─ Add check constraint "Post_status_check_eaa35e2a" on "Post"
├─ Data transform: backfill-post-status
├─ ⚠ Drop column "published" from "Post"
└─ marker 79567c619bcb9790bf476efc358730a10317c94fc352e908e19424fc6441350a

In practice the two migrations reach production in separate deploys, one per branch, and the app moves from published to status between them. The commands are the same either way. Production never runs your TypeScript: the runner executes ops.json, which is plain data, so no application code runs with production credentials.

Common gotchas

Do not use db update for this change. It reconciles the database with the contract directly and does not carry data operations, so the backfill would not run. Use migration plan and db migrate, which is also what production needs: a reviewed, checked-in migration.

If migration plan stops with MIGRATION.PLAN_ORIGIN_UNKNOWN, the db ref is missing. That happens when you applied a migration with plain db migrate. Either re-run npx prisma db migrate --advance-ref db, or pass --from <migration-dir> to the plan.

If db migrate reports MIGRATION.HASH_MISMATCH, the migration's ops.json or migration.json changed after it was compiled, for example by a hand edit. Restore both files from Git, or move the edit into migration.ts and recompile with node <migration-dir>/migration.ts, then apply again. Never edit ops.json by hand.

The check query must return rows, not a count. Prisma wraps it in EXISTS and NOT EXISTS; an aggregate always returns one row, so the step would never be skipped and the postcheck would always fail.

Prompt your coding agent

Run npx prisma@latest init once to install the Prisma ORM skills for your coding agent and keep them matching your installed packages. Prompts that map to this guide:

"Using the prisma-8 skill, add a dataTransform to the latest migration that backfills status from published, recompile it, and show me the compiled SQL in ops.json."
"Plan a migration that drops the published column and explain the destructive warning."
"Preview what db migrate would run against staging and summarize the operations."

Next steps

Editing a migration: placeholders, rawSql, and the recompile loop in depth.
Applying a migration: targets, refs, and what happens when a run fails.
Rollbacks and recovery: a rollback is one more planned migration, and a failed run is safe to retry.
The migration graph: why two branches with migrations merge cleanly.

Expand-and-contract migrations

Introduction

Prerequisites

Use with your agent

1. Set up the project

2. Expand the contract

3. Add the data migration

4. Apply the expand migration

5. Contract the schema

6. Deploy to production

Common gotchas

Prompt your coding agent

Next steps

On this page