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.

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: expand → migrate data → contract

Instead of a single migration that both changes shape and risks losing data, split it into two or three migrations:

Expand — add the new column/table alongside the old one (nullable, no constraint yet).
Migrate the data — run a script that copies/transforms old data into the new shape.
Contract — a follow-up migration that drops the old column/table or adds the NOT NULL/unique constraint, once you’ve confirmed the data migration succeeded.

This keeps the destructive step isolated and reversible, and makes the data-migration script itself reviewable in the same PR as the schema change — database migration and data migration should be proposed and reviewed together, not treated as separate concerns.

Writing the data migration: TS script via tsx

Since we’re already on Prisma + TypeScript + tsx, data migrations are plain scripts under prisma/data-migrations/, run through tsx with no new dependency — fitting the same pattern as permissions:generate:*.

1. Track which migrations have run

Add a tracking model so old scripts can be safely deleted later without breaking new clones of the repo:

model DataMigration {
  id        String   @id @default(cuid())
  name      String   @unique
  appliedAt DateTime @default(now())
}

Run a schema migration to create it:

pnpm prisma:migrate:local

2. Create the runner

prisma/data-migrations/run-all.ts:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

const migrations = [
  {
    name: "2026-07-01-backfill-user-email",
    fn: () => import("./2026-07-01-backfill-user-email"),
  },
  // Add new migrations here as you create them
];

async function runAll() {
  try {
    for (const migration of migrations) {
      const exists = await prisma.dataMigration.findUnique({
        where: { name: migration.name },
      });

      if (exists) {
        console.log(`✓ ${migration.name} already applied, skipping`);
        continue;
      }

      console.log(`→ Running ${migration.name}...`);
      const mod = await migration.fn();
      await mod.default(prisma);

      await prisma.dataMigration.create({
        data: { name: migration.name, appliedAt: new Date() },
      });

      console.log(`✓ ${migration.name} completed`);
    }
    console.log("\n✓ All data migrations completed");
  } catch (error) {
    console.error("Data migration failed:", error);
    throw error;
  } finally {
    await prisma.$disconnect();
  }
}

runAll().catch(console.error);

3. Write a migration file

prisma/data-migrations/2026-07-01-backfill-user-email.ts:

import { PrismaClient } from "@prisma/client";

export default async function migrate(prisma: PrismaClient) {
  const usersWithoutEmail = await prisma.user.findMany({
    where: { email: null },
  });

  for (const user of usersWithoutEmail) {
    await prisma.user.update({
      where: { id: user.id },
      data: { email: `user-${user.id}@placeholder.local` },
    });
  }

  console.log(`✓ Backfilled ${usersWithoutEmail.length} emails`);
}

Export a default async function taking prisma: PrismaClient — the runner passes its own client instance in.

4. Wire it into bootstrap — data migrations run first

Data migrations must run after the schema migration that expands the shape, not before:

"db:bootstrap:local": "dotenv -e .env.local -- pnpm prisma:migrate:local && dotenv -e .env.local -- tsx prisma/data-migrations/run-all.ts && dotenv -e .env.local -- pnpm prisma:seed:local",

"db:bootstrap:dev": "dotenv -e .env.dev -- pnpm prisma:migrate:dev && dotenv -e .env.dev -- tsx prisma/data-migrations/run-all.ts && dotenv -e .env.dev -- pnpm prisma:seed:dev",

"db:bootstrap:stage": "dotenv -e .env.stage -- pnpm prisma:migrate:stage && dotenv -e .env.stage -- tsx prisma/data-migrations/run-all.ts && dotenv -e .env.stage -- pnpm prisma:seed:stage",

"db:bootstrap:prod": "dotenv -e .env.prod -- pnpm prisma:migrate:prod && dotenv -e .env.prod -- tsx prisma/data-migrations/run-all.ts && dotenv -e .env.prod -- pnpm prisma:seed:prod"

Order: schema migration (expand) → data migration → seed. This matches the expand → migrate → contract pattern directly: the schema migration adds the new nullable column/table first (the “expand” step), then the data migration script backfills/transforms rows into that new shape, and only a later, separate schema migration does the “contract” (dropping the old column, adding NOT NULL, etc.) once you’ve confirmed the data migration succeeded.

For a rename, column drop, or type narrowing: expand first (new column added, old one still present), then run the data migration to populate the new column from the old one, then contract in a later migration once verified.For a table drop: don’t rely on a data migration script alone. Take a full backup of the table before the schema migration runs, and only write a restore/backfill script later, if and when it turns out that data is actually needed — there’s no “expand” step for a dropped table, so the backup is your only safety net.

Adding a new data migration

Create the file under prisma/data-migrations/ with a date prefix.
Write the transform (see template above).
Add an entry to the migrations array in run-all.ts.
Test locally: pnpm db:bootstrap:local.
Commit the data migration file, the run-all.ts update, and the schema migration in the same PR.

Because every run is logged in the DataMigration table, old script files can later be deleted from the repo without breaking new environments — they simply won’t be in the migrations array anymore, and their row in the table stays as history.

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 → migrate → contract plan, add the data migration file, and register it in run-all.ts
Test the full bootstrap (pnpm db:bootstrap:local) against a real backup, not an empty local DB
Confirm the data migration is committed in the same PR as the schema migration
Get explicit reviewer sign-off specifically on the data-migration step, separate from normal code review

Communication is mandatory, not optional

If a data migration exists — even a small one-line backfill — you must say so explicitly. Silence is not an acceptable default here.

Developer → Reviewer: When opening the PR, explicitly call out in the PR description that a data migration is included, what it does, and which environment(s) it affects. Don’t assume the reviewer will notice it just by reading the diff — call it out in words.
Reviewer → Prod-responsible: When handing off a migration for production deployment, explicitly state whether a data migration ran as part of it, what it did on dev, and any anomalies observed (warnings, skipped rows, unexpected counts). Don’t let “it worked on dev” stand in for a real handoff — the prod-responsible person needs the same context you had, in writing.

A missed data migration handoff is exactly how “the schema migration succeeded” quietly becomes “we lost data” three environments later. This step is not optional, regardless of how small or obvious the migration seems.

​The core rule

​Recommended approach: expand → migrate data → contract

​Writing the data migration: TS script via tsx

​1. Track which migrations have run

​2. Create the runner

​3. Write a migration file

​4. Wire it into bootstrap — data migrations run first

​Adding a new data migration

​Checklist before merging a migration with data-loss risk

​Communication is mandatory, not optional

The core rule

Recommended approach: expand → migrate data → contract

Writing the data migration: TS script via tsx

1. Track which migrations have run

2. Create the runner

3. Write a migration file

4. Wire it into bootstrap — data migrations run first

Adding a new data migration

Checklist before merging a migration with data-loss risk

Communication is mandatory, not optional