Data Migration - j-store

Database migration changes the shape of your tables (columns, types, constraints). Data migration moves or transforms the existing rows so they survive that shape change intact. A schema migration completing without an error does not mean no data was lost — Prisma will happily drop a column, narrow a type, or add a NOT NULL field with a default, silently discarding whatever was there before if you don’t handle it explicitly. No data loss is acceptable at any environment level. Dev and local work from the same backup — none of it is throwaway data.

The core rule

Before running any migration — on a PR branch, dev VPS, or prod VPS — always read the migration warning output first. Prisma prints an explicit warning when an operation is potentially destructive: dropping a column, dropping a table, removing a unique constraint, narrowing a type, or adding a required field without a safe default. If you see one of these, stop and write a data migration plan before applying it — don’t just re-run and ignore the warning.

Know which environment you’re touching — but note that “low risk” does not mean data loss is acceptable anywhere in this pipeline:

PR / local schema — the developer works from the same backup the dev-responsible person uses on the dev VPS. This is not throwaway scratch data; any data loss here reflects a real problem that will resurface on dev. Treat warnings with the same seriousness as you would on dev.
Dev VPS — shared team data restored from the same backup. No data loss is acceptable — confirm every warning is resolved before applying.
Prod VPS — real user data. Treat every warning here as a hard stop until resolved.

Recommended approach The Pattern : Expand → Backfill → Contract

Every breaking schema change is split into three separate Prisma migrations, in this exact order:

Expand — add the new column/table. Nullable, no constraints. Old code keeps working.
Backfill — a pure SQL migration that copies/transforms data from the old shape into the new shape.
Contract — drop the old column, add NOT NULL/unique constraints, etc. Only safe once backfill has run. Because all three are standard Prisma migrations, prisma migrate deploy runs them in one batch, in folder-timestamp order — expand, then backfill, then contract, every time. There is no separate runner, no external script, and no risk of contract dropping data before backfill has a chance to copy it.

Never let a contract migration (drop column / add NOT NULL) sit in the same batch with an earlier timestamp than its backfill migration. Since prisma migrate deploy executes strictly by folder name order, getting this order wrong means data loss with no warning.

Why pure SQL instead of a TS script

Data migrations used to be plain TypeScript files run through tsx, tracked in a DataMigration table, and wired into db:bootstrap:* after the schema migration. That approach has one structural problem: it runs the TS runner as a separate step after prisma migrate deploy completes. If a contract migration lands in the same prisma migrate deploy batch as its matching backfill, the contract migration (SQL, run by Prisma) executes before the TS script (run afterward) ever gets a chance to backfill the data. Writing the backfill as a standard Prisma migration removes that gap entirely — it becomes just another migration.sql file, so it’s guaranteed to run in its correct position in the sequence, with no external orchestration.

How to write a backfill migration

1. Generate an empty migration

npx prisma migrate dev --create-only --name backfill_new_column

2. Write the transform as pure SQL

Open the generated migration.sql and write the transform directly — no JS, no Prisma Client calls. Simple case (single UPDATE/INSERT, no row-by-row logic needed):

BEGIN;
 
UPDATE users
SET new_email = LOWER(TRIM(old_email))
WHERE new_email IS NULL AND old_email IS NOT NULL;
 
COMMIT;

Every migration file must be wrapped in its own BEGIN; ... COMMIT; block. This guarantees the transform is all-or-nothing — if any statement in the file fails, the whole migration rolls back instead of leaving data half-migrated. Prisma already wraps each migration in a transaction by default, but wrap it explicitly anyway so the file is correct even if run manually outside Prisma (e.g. directly against prod with psql during an incident).

3. Complex logic — use PL/pgSQL, not a TS loop

Postgres handles more than it looks like at first. Row-by-row logic, conditionals, and validation can all live inside the migration as a FUNCTION or PROCEDURE:

BEGIN;
 
CREATE OR REPLACE FUNCTION backfill_user_emails() RETURNS void AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT id, old_email FROM users WHERE new_email IS NULL LOOP
    UPDATE users
    SET new_email = LOWER(TRIM(rec.old_email))
    WHERE id = rec.id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
 
SELECT backfill_user_emails();
DROP FUNCTION backfill_user_emails();
 
COMMIT;

What PL/pgSQL supports inside a migration:

Loops (FOR, WHILE, LOOP) and conditionals (IF/ELSIF/ELSE, CASE)
Variables, records, arrays
Exception handling (BEGIN ... EXCEPTION WHEN ... END)
Dynamic SQL (EXECUTE), recursive CTEs, jsonb manipulation
Chunked/batched updates (LIMIT/OFFSET loops) to avoid long locks The one hard boundary: PL/pgSQL cannot make outbound HTTP calls or call external APIs. Everything else — conditional transforms, per-row processing, validation — is doable in SQL.

4. Control execution order

Prisma applies migrations in alphanumeric folder-name order. make sure to land it like this:

20260719000001_expand_user_contacts/
20260719000002_backfill_user_contacts/
20260719000003_contract_users_phone/

Full example: moving a field from one table to another

Scenario: move phone_number from users to a new user_contacts table. Migration 1 — Expand (20260719000001_expand_user_contacts/migration.sql)

BEGIN;
 
CREATE TABLE user_contacts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  phone_number TEXT,
  created_at TIMESTAMP DEFAULT now()
);
 -- Phone numbers are now stored in user_contacts instead of users.
ALTER TABLE users
ALTER COLUMN phone_number DROP NOT NULL;
COMMIT;

Migration 2 — Backfill (20260719000002_backfill_user_contacts/migration.sql)

BEGIN;
 
CREATE OR REPLACE FUNCTION backfill_user_contacts() RETURNS void AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT id, phone_number FROM users WHERE phone_number IS NOT NULL LOOP
    INSERT INTO user_contacts (user_id, phone_number)
    VALUES (rec.id, rec.phone_number);
  END LOOP;
END;
$$ LANGUAGE plpgsql;
 
SELECT backfill_user_contacts();
DROP FUNCTION backfill_user_contacts();
 
COMMIT;

Migration 3 — Contract (20260719000003_contract_users_phone/migration.sql)

BEGIN;
 
ALTER TABLE users DROP COLUMN phone_number;
 
COMMIT;

Why this order is safe: migration 1 only adds a table (old code unaffected), migration 2 copies data over while the old column still exists, migration 3 only drops the old column once the copy is confirmed complete — and all three are guaranteed to run in this order in one prisma migrate deploy.

Batching for large tables

A single-statement backfill is fine for small-to-medium tables. For millions of rows, batch it — a single multi-million-row transaction holds locks too long and bloats the table (MVCC keeps old row versions around until commit).

A PROCEDURE (not a FUNCTION) is required for batching, because only a procedure called via CALL can issue an intermediate COMMIT inside its loop. A FUNCTION runs as one transaction start to finish.

Don’t batch with LIMIT/OFFSET. Each iteration re-scans and discards every row before its offset, so the query gets slower with every batch — by the time you’re at offset 5 million, Postgres scans 5 million rows just to find the next 5,000. Use cursor (keyset) pagination instead: chunk by id range, so every batch hits the index directly and stays the same speed from the first row to the last.

BEGIN;
 
CREATE OR REPLACE PROCEDURE backfill_user_contacts() AS $$
DECLARE
  batch_size INTEGER := 5000;
  last_id INTEGER := 0;
  max_id INTEGER;
BEGIN
  SELECT COALESCE(MAX(id), 0) INTO max_id FROM users;
 
  WHILE last_id < max_id LOOP
    INSERT INTO user_contacts (user_id, phone_number)
    SELECT id, phone_number
    FROM users
    WHERE id > last_id
      AND id <= last_id + batch_size
      AND phone_number IS NOT NULL;
 
    last_id := last_id + batch_size;
    COMMIT;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
 
CALL backfill_user_contacts();
DROP PROCEDURE backfill_user_contacts();
 
COMMIT;

Each iteration filters on id, which is indexed (primary key), so every batch is a fast index range scan — no re-scanning already-processed rows, no slowdown as the loop progresses. Rule of thumb:

Table size	Approach
Thousands of rows	Single statement, one transaction
Millions of rows	`PROCEDURE` with batched `COMMIT`s
Tens of millions+	Consider running outside the normal deploy window entirely

Batching isn’t free — it trades speed for reduced lock time. If the table is small or there’s no concurrent prod traffic to protect, skip it; the loop/commit overhead makes batching slower than a single UPDATE for that case.

For a table drop

Don’t rely on a backfill migration alone. Take a full backup of the table before the contract migration runs — there’s no “expand” step for a dropped table, so the backup is the only safety net. Only write a restore script later, if it turns out the data is actually needed.

Adding a new data migration — checklist

Generate the migration: npx prisma migrate dev --create-only --name backfill_<description>.
Write the transform as pure SQL, wrapped in BEGIN; ... COMMIT; (or a batched PROCEDURE for large tables).
Confirm folder timestamps place it strictly between its expand and contract migrations.
Test locally: pnpm db:bootstrap:local.
Commit the expand migration, the backfill migration, and the contract migration together in the same PR.

Mandatory communication

Developer → Reviewer: Call out explicitly in the PR description that a data migration exists, what it does, and which environments it affects.
Reviewer → Prod-responsible: State explicitly whether the data migration ran as part of the release, what it did on dev, and any anomalies. “It worked on dev” is not a handoff.

Checklist before merging a migration with data-loss risk

Read Prisma’s migration warning in full — don’t skip past it
Confirm whether the affected column/table/field currently holds real data (no environment is exempt — see above)
If yes: write the expand → backfill → contract plan, add the data migration using npx prisma migrate dev --create-only --name
Test the full bootstrap (pnpm db:bootstrap:local) against a real backup, not an empty local DB
Get explicit reviewer sign-off specifically on the data-migration step, separate from normal code review

​The core rule

​Recommended approach The Pattern : Expand → Backfill → Contract

​Why pure SQL instead of a TS script

​How to write a backfill migration

​1. Generate an empty migration

​2. Write the transform as pure SQL

​3. Complex logic — use PL/pgSQL, not a TS loop

​4. Control execution order

​Full example: moving a field from one table to another

​Batching for large tables

​For a table drop

​Adding a new data migration — checklist

​Mandatory communication

​Checklist before merging a migration with data-loss risk

The core rule

Recommended approach The Pattern : Expand → Backfill → Contract

Why pure SQL instead of a TS script

How to write a backfill migration

1. Generate an empty migration

2. Write the transform as pure SQL

3. Complex logic — use PL/pgSQL, not a TS loop

4. Control execution order

Full example: moving a field from one table to another

Batching for large tables

For a table drop

Adding a new data migration — checklist

Mandatory communication

Checklist before merging a migration with data-loss risk