AI-Powered Data Migrations
Use AI to plan, execute, and validate database migrations — schema changes, data transformations, and zero-downtime rollouts.
Why data migrations are the riskiest operation
A data migration changes the structure or content of your database — and unlike code deployments, database changes are hard to reverse. Drop a column and the data is gone. Rename a table and every query that references the old name breaks. Change a column type and data that does not fit the new type is corrupted or lost. Migrations are the single most common cause of production outages in web applications. AI assistance transforms this process. Instead of writing migrations by hand and hoping you covered every edge case, you describe what you want to change and Claude Code generates the migration, validates it against your existing data, and creates a rollback plan. Ask Claude Code: Create a Node.js project with TypeScript and Prisma for a migration management system. Set up a PostgreSQL database with a sample schema: a users table (id, email, name, created_at), a posts table (id, user_id, title, body, published, created_at), and a comments table (id, post_id, user_id, body, created_at). Seed with 1000 users, 5000 posts, and 15000 comments to have enough data to test migration performance. Ask Claude Code: Show me the current database schema with all tables, columns, types, constraints, and indexes. Also show me the data distribution — how many rows per table, the range of values in each column, and any null values. This baseline is essential before making changes. You need to understand what exists before you change it. Every migration should follow a four-step process: plan (describe the change and generate the migration SQL), validate (verify the migration will not corrupt or lose data), execute (run the migration with proper locking and timing), and verify (confirm the migration succeeded and all data is intact). Common error: testing migrations on an empty database. A migration that runs in milliseconds on an empty table might lock a production table with millions of rows for minutes. Always test with production-scale data.
Schema versioning and migration files
Every database change should be tracked as a versioned migration file — this ensures every environment (development, staging, production) has the same schema and changes are applied in the correct order. Ask Claude Code: Create a migration system at src/lib/migrations.ts. Each migration is a file in the migrations/ directory with a timestamp-based name: 20240315120000_add_user_profiles.ts. The file exports two functions: up (apply the change) and down (reverse the change). The system maintains a migrations table in the database tracking which migrations have been applied and when. Create the first migration. Ask Claude Code: I want to add a profiles table linked to users. Generate a migration that creates the profiles table (id, user_id unique foreign key, bio text, avatar_url, location, website_url, created_at, updated_at) and populates it with a row for each existing user (bio set to null, timestamps set to current time). The up function creates the table and backfills data. The down function drops the table. Ask Claude Code: Create a CLI for the migration system. Commands: migrate up (apply all pending migrations), migrate down (revert the last migration), migrate status (show applied and pending migrations), migrate create name (generate a new migration file from a template), and migrate validate (check all pending migrations against the current schema without executing them). Add dry-run support. Ask Claude Code: When the --dry-run flag is passed, print the SQL that would be executed without actually running it. This is essential for reviewing migrations before applying them to production. Also print the estimated execution time based on table sizes and the type of operation (adding a column to a table with 1 million rows takes longer than adding a column to a table with 100 rows). Common error: migration files must be immutable once applied. Never edit a migration that has already run in any environment. If you need to fix a mistake, create a new migration that reverses or corrects the previous one. Editing applied migrations causes schema drift between environments.
Data transformation and backfilling
Schema changes often require data transformation — converting data from the old format to the new format. This is the most error-prone part of migrations because it involves assumptions about existing data that may not hold true. Ask Claude Code: Create a migration that splits the users name column into first_name and last_name columns. This seems simple but has edge cases: names with more than two parts (John van der Berg), single-word names (Prince), empty names, and null values. Generate the migration SQL that adds the new columns, writes a transformation function that splits existing names (first word becomes first_name, remaining words become last_name), handles all edge cases with sensible defaults (single-word names go to first_name with last_name as empty string), and drops the old column only after verifying all data was transformed correctly. Ask Claude Code: Before running the migration, analyse the actual data in the name column. Show me: the total count, null count, empty string count, names with 1 word, names with 2 words, names with 3 or more words, and any names with special characters. This analysis reveals the edge cases you need to handle. Build a backfill system for large tables. Ask Claude Code: Create a batched backfill function at src/lib/backfill.ts. For tables with millions of rows, updating all rows in a single transaction locks the table and can cause downtime. Instead, process in batches of 1000 rows with a 100 millisecond delay between batches. Track progress (X of Y rows processed, estimated time remaining). Resume from where you left off if the process is interrupted. Log any rows that could not be transformed (malformed data) for manual review. Add a data validation step. Ask Claude Code: After the backfill completes, run validation queries. Check that no rows have null values in the new columns (unless explicitly allowed), that the total row count has not changed (no rows were accidentally deleted or duplicated), and that a sample of 100 rows has the expected transformation applied correctly. Print a validation report. Common error: running the old and new code simultaneously during migration. If you drop the name column before deploying code that uses first_name and last_name, the old code will crash. Always deploy the new code (that reads both formats) before running the migration, then clean up the old column in a separate migration after the new code is stable.
Zero-downtime migration strategies
Production databases serve live traffic. A migration that locks a table for 30 seconds means 30 seconds of errors for your users. Zero-downtime migrations avoid this by breaking dangerous changes into safe, incremental steps. Ask Claude Code: Implement the expand-contract migration pattern. Instead of renaming a column (which locks the table and breaks running queries), do it in three steps. Step 1 — Expand: add the new column alongside the old one. Write to both columns. Read from the old column. Step 2 — Migrate: backfill the new column from the old column data. Switch reads to the new column. Verify the new column has correct data. Step 3 — Contract: stop writing to the old column. Drop the old column. Each step is a separate migration that can be deployed and verified independently. Ask Claude Code: Create a migration plan for renaming the posts.body column to posts.content. Generate three migration files following the expand-contract pattern. Include the application code changes needed at each step. Handle the case where some rows might be written between step 1 and step 2 — use a database trigger or application-level dual-write to keep both columns in sync. Add an online index creation strategy. Ask Claude Code: Create a migration that adds an index to the posts.user_id column. On a large table, CREATE INDEX locks the table for the entire duration. Use CREATE INDEX CONCURRENTLY (PostgreSQL) which builds the index without blocking writes. The migration should: check if the index already exists (idempotent), create the index concurrently, verify the index was created successfully (concurrent index creation can fail silently), and retry once if the creation failed. Build a migration safety checker. Ask Claude Code: Create a function that analyses a migration SQL statement and flags dangerous operations: DROP TABLE, DROP COLUMN, RENAME (anything), ALTER COLUMN TYPE (can lose data), adding a NOT NULL constraint to an existing column (fails if null values exist), and any operation that acquires an ACCESS EXCLUSIVE lock. For each flagged operation, suggest the safe alternative. Common error: adding a column with a DEFAULT value to a large table in PostgreSQL versions before 11 rewrites the entire table. In PostgreSQL 11 and later, defaults are handled efficiently. Check your PostgreSQL version before assuming defaults are safe.
Rollback plans and disaster recovery
Every migration needs a rollback plan — a tested way to reverse the change if something goes wrong. The plan must be written before the migration runs, not during a crisis at 3 AM. Ask Claude Code: Create a rollback system at src/lib/rollback.ts. Every migration file has a down function, but some migrations are not reversible. If you drop a column, the data is gone — the down function can recreate the column but not the data. Categorise migrations by reversibility: fully reversible (adding a column — just drop it), partially reversible (splitting a column — can be merged back but may lose precision from the split logic), and irreversible (dropping a column, truncating a table). For irreversible migrations, create a backup step. Ask Claude Code: Before running an irreversible migration, automatically export the affected data. For a column drop: export the column's data as a CSV linked to the primary key. Store the backup in a migrations/backups/ directory with the migration timestamp. The rollback function restores the column and reimports the data from the backup. Build an automated rollback trigger. Ask Claude Code: Create a monitoring check that runs after each migration. The check verifies: the application can still connect to the database, key queries return results (run a predefined set of smoke test queries), the row counts for affected tables have not changed unexpectedly (a migration should not accidentally delete rows), and the application health check endpoint returns 200. If any check fails, automatically run the rollback and alert the operator. Create a migration runbook template. Ask Claude Code: Generate a migration runbook document for each migration. Include: pre-migration checklist (backup verified, rollback tested, deployment plan reviewed), the exact commands to run (with environment-specific connection strings), expected execution time, monitoring instructions during execution, post-migration verification steps, and rollback procedure with exact commands. This document ensures anyone on the team can execute the migration, not just the person who wrote it. Common error: never test rollbacks in production for the first time. Test the complete cycle (migrate up, verify, migrate down, verify) in staging with production-scale data before running in production.
Migration testing and CI integration
Migrations should be tested as rigorously as application code. A bug in a migration is harder to fix than a bug in code — you cannot just deploy a fix because the data may already be corrupted. Ask Claude Code: Create a migration test suite at src/test/migrations.test.ts. For each migration, test: the up function applies cleanly to the current schema, the down function cleanly reverses the up, the up function is idempotent (running it twice does not cause errors or duplicate data), and the migration preserves data integrity (data before migration equals data after migration and rollback). Set up a test database that is created fresh for each test run. Ask Claude Code: Create a test setup that spins up a PostgreSQL test database (using Docker or a test database URL), applies all existing migrations to bring it to the current state, runs the migration under test, validates the result, rolls back, and verifies the schema returned to the previous state. Tear down the test database after the suite completes. Integrate with CI. Ask Claude Code: Create a GitHub Actions workflow that runs on every pull request containing migration files. The workflow: starts a PostgreSQL service container, applies all existing migrations, applies the new migration(s) from the PR, runs the validation checks, rolls back and verifies, and posts the results as a PR comment with details on execution time and any warnings. Add a migration linter. Ask Claude Code: Create a linter that checks migration files for common issues before they reach the database. Rules: no DROP TABLE or DROP COLUMN without a backup step, no raw SQL strings (use parameterised queries), all table and column names are quoted (prevents issues with reserved words), every up function has a corresponding down function, and file names follow the timestamp convention. Run the linter as a pre-commit hook. Deploy the complete migration system. Ask Claude Code: Create a deployment guide covering: how to run migrations in development (npm run migrate up), how to run migrations in CI (automated in the test pipeline), how to run migrations in staging (manual trigger with dry-run first), and how to run migrations in production (runbook-based with monitoring). The production workflow should be: run dry-run, review output, take a database snapshot, execute migration, run post-migration checks, and keep the snapshot for 48 hours before deleting.
Database Management
This guide is hands-on and practical. The full curriculum covers the conceptual foundations in depth with structured lessons and quizzes.
Go to lesson