# Expand-and-contract migrations (/docs/guides/database/data-migration)

> For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/llms.txt). A markdown version of any docs page is available by appending `.md` to its URL.

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

Location: Guides > Database > Expand-and-contract migrations

## Introduction [#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.

> [!NOTE]
> Using Prisma ORM 7?
> 
> Prisma ORM 8 is the current release. Prisma ORM 7 remains fully supported; the Prisma ORM 7 version of this guide is at [/guides/v7/database/data-migration](https://www.prisma.io/docs/guides/v7/database/data-migration).

## Prerequisites [#prerequisites]

* [Node.js](https://nodejs.org) 24 or later
* 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 [#use-with-your-agent]

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

```text
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 [#1-set-up-the-project]

If you already have a Prisma ORM project whose contract is applied through migrations, skip to [step 2](#2-expand-the-contract). 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:

```bash
mkdir expand-contract && cd expand-contract
```

  

#### bun

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

#### pnpm

```bash
pnpm init
pnpm dlx prisma@latest orm init --yes --target postgres --authoring psl
```

#### yarn

```bash
yarn init
yarn dlx prisma@latest orm init --yes --target postgres --authoring psl
```

#### npm

```bash
npm init
npx 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:

```bash title=".env"
DATABASE_URL="postgres://user:password@localhost:5432/expand_contract"
```

Replace the generated contract with the starting model:

```prisma title="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:

  

#### bun

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

#### pnpm

```bash
pnpm prisma contract emit
pnpm prisma migration plan --name init
pnpm prisma db migrate --advance-ref db
```

#### yarn

```bash
yarn prisma contract emit
yarn prisma migration plan --name init
yarn prisma db migrate --advance-ref db
```

#### npm

```bash
npx prisma contract emit
npx prisma migration plan --name init
npx prisma db migrate --advance-ref db
```

```text no-copy
✔ 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 24 runs TypeScript directly:

```typescript title="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();
```

```bash
node src/seed.ts
```

```text no-copy
[
  { 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 [#2-expand-the-contract]

Create a branch for the expand step:

```bash
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:

```prisma title="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) // [!code ++]
}

enum Status { // [!code ++]
  @@type("pg/text@1") // [!code ++]
  Draft      = "Draft" // [!code ++]
  InProgress = "InProgress" // [!code ++]
  InReview   = "InReview" // [!code ++]
  Published  = "Published" // [!code ++]
} // [!code ++]
```

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

  

#### bun

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

#### pnpm

```bash
pnpm prisma contract emit
pnpm prisma migration plan --name add_status
```

#### yarn

```bash
yarn prisma contract emit
yarn prisma migration plan --name add_status
```

#### npm

```bash
npx prisma contract emit
npx prisma migration plan --name add_status
```

```text no-copy
✔ 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 [#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:

```typescript title="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'; // [!code ++]
import { sql } from '@prisma/orm-postgres/builder/runtime'; // [!code ++]
import { createExecutionContext, createSqlExecutionStack } from '@prisma/orm-postgres/family-runtime'; // [!code ++]
import postgresTarget, { PostgresContractSerializer } from '@prisma/orm-postgres/target/runtime'; // [!code ++]

const endContract = new PostgresContractSerializer().deserializeContract(endContractJson) as End; // [!code ++]

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

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', { // [!code ++]
        check: () => // [!code ++]
          db.public.Post // [!code ++]
            .select('id') // [!code ++]
            .where((f, fns) => fns.and(fns.eq(f.published, true), fns.ne(f.status, 'Published'))) // [!code ++]
            .limit(1), // [!code ++]
        run: () => // [!code ++]
          db.public.Post // [!code ++]
            .update({ status: 'Published' }) // [!code ++]
            .where((f, fns) => fns.and(fns.eq(f.published, true), fns.ne(f.status, 'Published'))), // [!code ++]
      }), // [!code ++]
    ];
  }
}

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`:

```bash
node migrations/app/20260910T1552_add_status/migration.ts
```

```text no-copy
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:

  

#### bun

```bash
bunx prisma migration show 20260910T1552_add_status
```

#### pnpm

```bash
pnpm prisma migration show 20260910T1552_add_status
```

#### yarn

```bash
yarn prisma migration show 20260910T1552_add_status
```

#### npm

```bash
npx prisma migration show 20260910T1552_add_status
```

```text no-copy
✔ 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
```

```json title="migrations/app/20260910T1552_add_status/ops.json (the data operation)" no-copy
{
  "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 [#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:

  

#### bun

```bash
bunx prisma db migrate --advance-ref db
```

#### pnpm

```bash
pnpm prisma db migrate --advance-ref db
```

#### yarn

```bash
yarn prisma db migrate --advance-ref db
```

#### npm

```bash
npx prisma db migrate --advance-ref db
```

```text no-copy
✔ 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:

```typescript title="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();
```

```bash
node src/posts.ts
```

```text no-copy
[
  { 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:

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

```text no-copy
[
  { 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 [#5-contract-the-schema]

Create a branch for the cleanup:

```bash
git checkout -b drop-published-column
```

Remove the old field:

```prisma title="src/prisma/contract.prisma"
model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false) // [!code --]
  status    Status  @default(Draft)
}
```

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

  

#### bun

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

#### pnpm

```bash
pnpm prisma contract emit
pnpm prisma migration plan --name drop_published
```

#### yarn

```bash
yarn prisma contract emit
yarn prisma migration plan --name drop_published
```

#### npm

```bash
npx prisma contract emit
npx prisma migration plan --name drop_published
```

```text no-copy
✔ 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:

  

#### bun

```bash
bunx prisma db migrate --advance-ref db
```

#### pnpm

```bash
pnpm prisma db migrate --advance-ref db
```

#### yarn

```bash
yarn prisma db migrate --advance-ref db
```

#### npm

```bash
npx prisma db migrate --advance-ref db
```

```text no-copy
✔ 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:

```text no-copy
[
  { 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:

  

#### bun

```bash
bunx prisma migration log
```

#### pnpm

```bash
pnpm prisma migration log
```

#### yarn

```bash
yarn prisma migration log
```

#### npm

```bash
npx prisma migration log
```

```text no-copy
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 [#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](https://www.prisma.io/docs/orm/migrations/the-migration-graph), and applies whatever is pending, backfill included. Your deploy pipeline needs one command:

  

#### bun

```bash
bunx prisma db migrate --db "$PRODUCTION_DATABASE_URL"
```

#### pnpm

```bash
pnpm prisma db migrate --db "$PRODUCTION_DATABASE_URL"
```

#### yarn

```bash
yarn prisma db migrate --db "$PRODUCTION_DATABASE_URL"
```

#### npm

```bash
npx 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](https://www.prisma.io/docs/orm/migrations/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:

```text no-copy
ℹ The following 2 migrations will run:

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

```text no-copy
✔ 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 [#common-gotchas]

> [!WARNING]
> 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.

> [!NOTE]
> 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.

> [!NOTE]
> 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.

> [!NOTE]
> 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 [#prompt-your-coding-agent]

Run [`npx prisma@latest init`](https://www.prisma.io/docs/cli/init) once to install the [Prisma ORM skills](https://www.prisma.io/docs/ai/tools/skills#available-skills-for-prisma-8) 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 [#next-steps]

* [Editing a migration](https://www.prisma.io/docs/orm/migrations/editing-a-migration): placeholders, `rawSql`, and the recompile loop in depth.
* [Applying a migration](https://www.prisma.io/docs/orm/migrations/applying-a-migration): targets, refs, and what happens when a run fails.
* [Rollbacks and recovery](https://www.prisma.io/docs/orm/migrations/rollbacks-and-recovery): a rollback is one more planned migration, and a failed run is safe to retry.
* [The migration graph](https://www.prisma.io/docs/orm/migrations/the-migration-graph): why two branches with migrations merge cleanly.

## Related pages

- [`Multiple databases`](https://www.prisma.io/docs/guides/database/multiple-databases): Connect one Next.js app to two PostgreSQL databases with Prisma ORM: one contract, config, and client per database, selected with the --config flag.
- [`Schema management in teams`](https://www.prisma.io/docs/guides/database/schema-changes): Plan, merge, and apply Prisma ORM migrations when several developers change the schema at the same time.