Claude Code Database Migration Guide for Production Teams

Production database migration is not a task you should hand to Claude Code with a vague “change the schema” prompt. The real work is sequencing: application deploys, database locks, backups, backfills, feature flags, CI checks, and rollback decisions all interact. Claude Code is valuable when you use it as a reviewer and implementation assistant, not as an unchecked migration autopilot.

This guide shows a practical workflow for production teams using PostgreSQL and Prisma Migrate. The same ideas apply if your team writes plain SQL migrations: make the database accept old and new code first, move data in batches, validate the change, then remove the old path later.

Keep the official references open during review. Use the Claude Code documentation, PostgreSQL’s pages on explicit locking and ALTER TABLE, Prisma’s development and production migration guide and CLI reference, plus the GitHub Actions workflow syntax.

The Migration Model

Use the expand/contract model. “Expand” means changing the database so both the old application version and the new application version can run. “Contract” means removing old columns, old reads, or old constraints only after production has fully moved to the new path.

flowchart LR
  A["Backup and review"]
  B["Expand: add nullable column or new table"]
  C["Deploy code with dual write or feature flag"]
  D["Backfill data in small batches"]
  E["Validate staging and production metrics"]
  F["Contract: add NOT NULL, remove old path"]
  A --> B --> C --> D --> E --> F

The common failure is asking Claude Code to add a column, copy data, mark it NOT NULL, and drop the old column in one migration. That can pass on a tiny local database and still take an unsafe lock or run for too long on a table with millions of rows. A production migration should be boring and staged.

Some terms matter. A lock is the database’s way of preventing concurrent operations from corrupting the same table. A backfill is a job that fills new columns for existing rows. A shadow database is a temporary database Prisma uses in development to replay migration history and detect drift. It is not a production safety net.

Start With A Review Prompt

Ask Claude Code to review the plan before editing files. Give it table sizes, deploy style, ORM, and rollback expectations.

Review this database migration plan before editing files.

Context:
- Production database: PostgreSQL
- ORM: Prisma Migrate
- Hot tables: users has about 8 million rows, orders has about 25 million rows
- Deploy style: blue/green app deploy, database migration runs in CI/CD
- Requirement: split users.name into users.full_name and users.display_name

Check:
1. Can old and new app versions run at the same time?
2. Which SQL statements may take strong locks or scan the whole table?
3. Which steps must be expand, backfill, validate, and contract?
4. What backup or point-in-time recovery check is needed before deploy?
5. What can be rolled back by app deploy, and what can only be rolled forward?

Return a migration plan first. Do not edit files yet.

That last sentence matters. Claude Code can move quickly, and database work benefits from a deliberate pause. If the first plan mixes dangerous steps, push it again:

Rewrite the plan so that no step drops a column, rewrites a large table, or sets NOT NULL before the backfill is verified. Include a staging rehearsal and a production abort condition.

This turns Claude Code into a migration reviewer. You keep judgment over the risk, while the agent helps list files, commands, SQL, CI checks, and monitoring questions that are easy to forget.

Expand With Small SQL

Assume users.name will eventually be replaced by full_name and display_name. The expand migration adds the new nullable columns and an index, but it does not backfill, set NOT NULL, or drop the old column.

-- 20260602090000_expand_users_names.sql
-- Keep this migration small. Do not backfill and do not drop users.name here.

ALTER TABLE users
  ADD COLUMN full_name text,
  ADD COLUMN display_name text;

-- Run outside a transaction in PostgreSQL migration tools that support it.
-- CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
CREATE INDEX CONCURRENTLY IF NOT EXISTS users_display_name_idx
  ON users (display_name);

PostgreSQL’s ALTER TABLE documentation explains that lock levels vary by subcommand, and the default is conservative when no lighter lock is documented. Treat lock risk as a review item, not as something Claude Code can guess perfectly.

With Prisma, generate the migration without applying it, then inspect the SQL.

npx prisma migrate dev --name expand-users-names --create-only
npx prisma validate

Prisma’s production guide says migrate deploy is the command for production and test environments. It also states that migrate deploy applies pending migrations but does not detect drift and does not rely on a shadow database. That distinction is important: a successful deploy command is not the same as a full production rehearsal.

npx prisma migrate deploy

Switch The Application Safely

After expand, the application must tolerate both schemas. Reads should have a fallback path, and writes should populate both old and new fields until the backfill is complete.

// src/domain/userName.ts
type UserNameRow = {
  name: string | null;
  fullName: string | null;
  displayName: string | null;
};

export function readDisplayName(user: UserNameRow): string {
  return user.displayName ?? user.fullName ?? user.name ?? "Unknown user";
}

export function buildNameUpdate(input: { name: string }) {
  const normalized = input.name.trim().replace(/\s+/g, " ");

  return {
    name: normalized,
    fullName: normalized,
    displayName: normalized.length > 40 ? `${normalized.slice(0, 39)}...` : normalized,
  };
}

A feature flag separates the database change from the user-visible behavior. Keep the flag off while the database accepts both paths, turn it on after the backfill passes validation, and turn it off if the new read path causes application errors. For implementation details, pair this guide with the internal feature flags guide.

This pattern covers at least three production use cases: renaming or splitting user profile fields, adding calculated columns such as invoice balances or search labels, and adding indexes or foreign keys to hot tables after the application has been prepared.

Backfill In Batches

A single massive UPDATE can increase lock time, write-ahead log volume, replication lag, and operational risk. Ask Claude Code for a restartable batch script instead.

// scripts/backfill-user-names.mjs
import pg from "pg";

const { Client } = pg;
const batchSize = Number(process.env.BATCH_SIZE ?? 1000);
const sleepMs = Number(process.env.SLEEP_MS ?? 200);

const client = new Client({ connectionString: process.env.DATABASE_URL });

function sleep(ms) {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

await client.connect();

try {
  let total = 0;

  while (true) {
    const result = await client.query(
      `
      WITH target AS (
        SELECT id, name
        FROM users
        WHERE full_name IS NULL
          AND name IS NOT NULL
        ORDER BY id
        LIMIT $1
        FOR UPDATE SKIP LOCKED
      )
      UPDATE users AS u
      SET
        full_name = target.name,
        display_name = CASE
          WHEN length(target.name) > 40 THEN substring(target.name from 1 for 39) || '...'
          ELSE target.name
        END
      FROM target
      WHERE u.id = target.id
      RETURNING u.id
      `,
      [batchSize],
    );

    total += result.rowCount;
    console.log(`updated=${result.rowCount} total=${total}`);

    if (result.rowCount === 0) break;
    await sleep(sleepMs);
  }
} finally {
  await client.end();
}

Review the script for idempotency, partial failure, parallel execution, and operational controls. A good production backfill has a batch size, a pause between batches, logs, a way to stop, and a query that can be rerun without corrupting data.

CI And Staging Checks

CI should apply the migration history from scratch against an ephemeral database. In GitHub Actions, workflows live under .github/workflows and use YAML syntax, which makes this check easy to keep with the repository.

name: migration-check

on:
  pull_request:
    paths:
      - "prisma/**"
      - "scripts/backfill-*.mjs"
      - ".github/workflows/migration-check.yml"

jobs:
  prisma-migrations:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: app
        ports:
          - "5432:5432"
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    env:
      DATABASE_URL: postgresql://postgres:postgres@localhost:5432/app?schema=public

    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: "22"
          cache: npm
      - run: npm ci
      - run: npx prisma validate
      - run: npx prisma migrate deploy
      - run: npx prisma migrate status
      - name: Detect schema drift after migrations
        run: |
          npx prisma migrate diff \
            --exit-code \
            --from-config-datasource \
            --to-schema=prisma/schema.prisma

This example uses Prisma ORM v7 style config datasource arguments. Do not blindly copy older examples that use removed flags such as --from-url or --shadow-database-url without checking the current CLI reference.

Staging should be closer to reality than CI: similar row counts, similar indexes, similar timeouts, and the same migration runner. Ask Claude Code to produce a rehearsal checklist that includes lock waits, replication lag, query latency, error logs, and abort thresholds.

Contract And Rollback Limits

Only contract after the new app version has been stable and the backfill has been verified. For NOT NULL, use a validation step before making the final column change.

-- 20260602120000_contract_users_names.sql
-- Run only after the new application version has been stable in production.

ALTER TABLE users
  ADD CONSTRAINT users_full_name_present
  CHECK (full_name IS NOT NULL) NOT VALID;

ALTER TABLE users
  VALIDATE CONSTRAINT users_full_name_present;

ALTER TABLE users
  ALTER COLUMN full_name SET NOT NULL;

ALTER TABLE users
  DROP CONSTRAINT users_full_name_present;

-- Drop old columns in a later deploy, not in the same deploy that changes reads.
-- ALTER TABLE users DROP COLUMN name;

The biggest rollback mistake is believing a down migration restores everything. Dropped columns, overwritten values, and lossy type conversions are not restored by running SQL backward. In many production incidents, the realistic rollback is an application deploy or a feature flag change. The database recovery path is a backup, point-in-time recovery, or a forward fix.

Prisma’s migrate resolve --rolled-back also does not undo a successful production migration. It resolves migration history state after a failed migration. Ask Claude Code to separate “app rollback”, “database roll forward”, and “data restore required” in every rollback plan.

Common Failure Cases

The first failure is treating a rename as a drop and add. ORMs do not always know your intent, so inspect the generated SQL before production.

The second failure is combining schema change and data rewrite in one migration. If it fails, you cannot quickly tell whether the issue is a lock, bad data, a timeout, or application compatibility.

The third failure is overtrusting the shadow database. It helps Prisma catch development drift, but it does not simulate production data distribution, table bloat, lock queues, or replication lag.

The fourth failure is skipping the backup check. Before a migration, confirm the latest backup time, restore target, restore owner, and expected recovery time. Claude Code can write the checklist, but the team owns the recovery process.

Team Workflow

Put database rules in CLAUDE.md: no same-PR drop column on production tables, no large backfill inside schema migration, Prisma migrations must be created with --create-only for SQL review, and every migration review must cite the official docs it relies on. For structure, use the internal CLAUDE.md best practices guide.

For revenue-critical SaaS products, a bad migration is not just technical debt; it can stop billing, onboarding, and support workflows. ClaudeCodeLab’s products include reusable prompts and checklists, and Claude Code training can help teams install this workflow on a real repository.

In hands-on use, the biggest improvement came from reducing how much one migration tries to do. An expand-only PR, a separate backfill job, and a later contract PR make Claude Code’s output easier to review and make human go/no-go decisions clearer. The useful habit is to ask the agent for abort conditions and recovery steps before asking for SQL.

Summary

Claude Code does not make production database migration automatically safe. It becomes useful when the team gives it a safe operating model: expand/contract, staged application rollout, small backfills, CI checks, staging rehearsal, feature flags, and explicit rollback limits.

For your next migration, ask Claude Code for a risk review before implementation. If the plan can survive that review, then let it help write the SQL, Prisma migration, GitHub Actions check, and backfill script.