Database and Code Migrations

Your users table has 12 million rows and a column named name that needs to become first_name and last_name. Your ORM has a new major version with 40 breaking changes. The payment provider deprecated their v1 API six months ago and the sunset deadline is next Friday. Each of these migrations is a multi-step process where one wrong move means downtime, data loss, or both. You cannot afford to get it wrong, and you cannot afford to spend three weeks on it either.

What You’ll Walk Away With

• A Claude Code workflow for planning and generating zero-downtime database migrations, including the expand-contract pattern for column renames and type changes
• Copy-paste prompts that produce migration files, data backfill scripts, and rollback procedures for Prisma, Drizzle, Knex, and raw SQL
• A framework upgrade strategy that uses Claude Code to identify breaking changes, generate codemods, and verify the migration path

Zero-Downtime Database Migrations

The fundamental challenge with database migrations on a live system is that old code and new code run simultaneously during deployment. Your migration strategy must ensure both versions can operate correctly.

Tip

Copy-paste prompt — zero-downtime column rename:

I need to rename the 'name' column to 'first_name' on the users table (12 million rows, PostgreSQL). Generate a multi-step migration plan using the expand-contract pattern: Step 1 (pre-deploy): add the new column as nullable, add a database trigger to keep both columns in sync, backfill existing data. Step 2 (deploy): update application code to write to both columns and read from the new one. Step 3 (post-deploy): drop the trigger, make the new column NOT NULL, drop the old column. Generate the actual migration files for each step using our ORM. Include rollback scripts for each step.

Claude Code generates three separate migration files plus the application code changes. The critical insight is the database trigger that keeps both columns synchronized during the transition window. Without it, rows written by old-code instances would have an empty first_name.

After generating the migration, verify it locally:

Run the Step 1 migration against our local database, then verify that: 1) the trigger correctly syncs data when I insert a row with only the old column, 2) the backfill script handles NULL values, 3) the rollback script cleanly reverses everything.

Large Table Migrations Without Locking

Adding an index or changing a column type on a multi-million row table can lock the table for minutes. Claude Code can generate the non-blocking approach.

Tip

Copy-paste prompt — non-blocking schema change:

I need to change the type of the 'amount' column from INTEGER to NUMERIC(10,2) on the transactions table (50 million rows). The table handles 500 writes per second and cannot be locked. Generate a migration strategy that: 1) creates a new column with the correct type, 2) adds a trigger to populate the new column on write, 3) backfills existing rows in batches of 10,000 with a 100ms sleep between batches to avoid overloading the DB, 4) swaps the column names once backfill is complete, 5) drops the old column. Include progress logging and the ability to resume if interrupted.

The batch processing with sleep intervals is critical for large tables. Claude Code generates a script that tracks progress (the last processed ID) so it can resume from where it left off if interrupted, rather than starting over.

Framework and Library Upgrades

Major version upgrades are tedious because they involve finding every usage of a changed API and updating it. Claude Code can do this systematically.

We are upgrading from Express 4 to Express 5. Read the Express 5 migration guide (I am pasting the changelog below) and scan our codebase for every breaking change that affects us. For each affected file, show what needs to change and make the update. After all changes, run our test suite to verify nothing is broken.

For ORM migrations, the prompt is similar:

We are upgrading from Prisma 4 to Prisma 6. The breaking changes include: new transaction API, changed findUnique behavior with null, removed deprecated methods. Scan our entire codebase, find every affected call site, and update them to the new API. Also update the Prisma schema if the schema format changed.

Note

When upgrading frameworks, start Claude Code in plan mode (Shift+Tab twice) to review the scope of changes before making them. This gives you a chance to identify changes that need careful testing, like transaction behavior modifications, before Claude starts editing files.

Data Format Migrations

Sometimes the data structure itself needs to change — moving from a flat table to a JSON column, splitting a monolithic table into normalized relations, or migrating from one storage system to another.

Tip

Copy-paste prompt — data restructuring migration:

Our orders table has a JSON column called 'metadata' that contains customer address, shipping preferences, and payment info mixed together. We need to extract these into separate tables: order_addresses, order_shipping, and order_payments. Generate: 1) the new table schemas with proper foreign keys, 2) a migration script that reads from the JSON column and inserts into the new tables in batches, 3) updated ORM models and relations, 4) a verification query that confirms no data was lost, 5) the application code changes to read from the new tables instead of the JSON column.

Claude Code generates the entire pipeline: schema, migration script, ORM updates, and verification. The verification query is often overlooked but essential — it compares row counts and checksums between the old JSON data and the new normalized tables.

API Version Migration

When a third-party API deprecates a version, you need to update every call site without breaking the integration.

Our payment provider is deprecating their v1 API. Here is their v2 migration guide. Find every call to their API in our codebase, map each v1 endpoint to its v2 equivalent, update the request payloads and response handling, and add error handling for the new error format. Create a feature flag (PAYMENT_API_V2=true) so we can test the new integration alongside the old one before cutting over completely.

The feature flag approach lets you run both API versions simultaneously, directing a percentage of traffic to v2 while monitoring for errors before committing to the switch.

Generating Rollback Scripts

Every migration should have a rollback. Claude Code can generate both directions simultaneously.

For every migration file in our migrations/ directory that does not have a corresponding down migration, generate the rollback. For addColumn operations, generate dropColumn. For createTable, generate dropTable. For data transformations, generate the reverse transformation. Flag any migrations that are not safely reversible (like dropping a column with data) and explain why.

Caution

Some migrations are genuinely irreversible — dropping a column destroys data that cannot be recreated. Claude Code will flag these, but the decision to proceed is yours. For irreversible migrations, generate a backup step instead of a rollback: “Before dropping the legacy_email column, export its contents to a CSV backup stored in S3.”

Testing Migrations Safely

Never run a migration in production for the first time. Claude Code can generate a test harness.

Create a migration test script that: 1) creates a fresh database from our schema, 2) seeds it with realistic test data (10,000 users, 50,000 orders), 3) runs all pending migrations, 4) runs our application test suite against the migrated database, 5) runs the rollback for each migration and verifies the schema returns to its original state, 6) measures migration execution time and reports if any step takes longer than 30 seconds.

This test script becomes part of your CI pipeline, catching migration issues before they reach staging.

When This Breaks

The backfill script runs out of memory. Backfilling millions of rows into memory will crash Node.js. Ask Claude Code: “Rewrite the backfill script to use a cursor-based approach that processes 5,000 rows at a time, committing each batch separately, and logs progress every 10,000 rows.”

The database trigger causes a cascade of updates. Triggers that call other triggers can create infinite loops. Claude Code accounts for this by using pg_trigger_depth() guards, but verify: “Add a safety check to the sync trigger that prevents recursive execution.”

The migration passes in test but fails in production. Production data has edge cases your test data does not. Common culprits: NULL values in columns you assumed were always populated, unicode characters, extremely long strings. Ask Claude Code: “Generate a pre-migration validation query that checks for NULL values, empty strings, and strings longer than 255 characters in the columns we are migrating.”

The ORM upgrade changes query behavior subtly. After a Prisma or Drizzle upgrade, queries might return slightly different results (different NULL handling, changed default ordering). Run your existing test suite after the upgrade and feed any failures to Claude Code: “These 3 tests failed after the Prisma upgrade. The query results differ. Analyze why and update either the queries or the test expectations.”

What’s Next

Database Work Deeper dive into schema design, query optimization, and ORM patterns with Claude Code

Performance Analysis Measure the performance impact of your migrations and optimize where needed

Testing Strategies Build migration-specific test suites that catch edge cases before production