How to Use Claude Code for Database Design and Migration

Databases Are Where AI Shines Brightest

Of all the tasks I delegate to AI coding tools, database work delivers the highest return on investment. Schema design, migration scripts, query optimization, seed data generation -- these tasks are well-defined, pattern-heavy, and tedious to do manually. They are exactly the kind of work where AI excels.

Claude Code handles database work particularly well because it can reason about data relationships, understand existing schemas, and generate code that integrates with your ORM and migration framework. Here is how I use it across the full database lifecycle.

Schema Design from Requirements

The fastest path to a good schema is describing your domain in plain language and letting Claude Code translate it into a proper data model.

Instead of starting with tables and columns, I start with what the application needs to do:

• "Users create projects. Projects contain experiments. Experiments have variants and metrics. Each experiment belongs to one project. Users can belong to multiple projects through memberships with role assignments."

Claude Code takes this description and generates a complete schema with:

• Appropriate table definitions with correct data types
• Primary and foreign key relationships
• Indexes for common query patterns
• Timestamps and soft-delete columns where appropriate
• Enum types for status fields
• Unique constraints where business logic requires them

The initial output is usually eighty to ninety percent correct. The remaining ten to twenty percent requires human judgment about edge cases and performance tradeoffs that only someone who understands the application can make.

Tips for Better Schema Output

Include constraints in your description. "An experiment must have at least two variants" or "project names must be unique within an organization" gives the AI information it needs to generate proper database constraints rather than leaving them for you to add later.

Specify your database. PostgreSQL, MySQL, and SQLite have different feature sets. Tell Claude Code which database you use so it generates compatible SQL. PostgreSQL-specific features like JSONB columns, array types, and partial indexes will not work on MySQL.

Mention your ORM. If you use Prisma, Drizzle, TypeORM, or another ORM, say so. Claude Code generates schema definitions in the ORM's format instead of raw SQL, which integrates directly into your workflow.

Describe query patterns. "We will frequently query experiments by project and status" tells the AI to create indexes for those columns. Without this information, it guesses which indexes you need, and those guesses are often incomplete.

State your scale expectations. "This table will have millions of rows within a year" changes the AI's decisions about indexing, partitioning, and data types. A table with a thousand rows and a table with ten million rows need different designs.

Migration Generation

Migrations are where AI saves the most time. Writing migration scripts is repetitive, error-prone, and requires careful attention to both the "up" and "down" directions.

I describe migrations in natural language:

• "Add a settings JSON column to the projects table with a default empty object"
• "Create a join table for the many-to-many relationship between users and organizations"
• "Rename the experiments status column from state to status and update the enum values"
• "Split the address field into separate street, city, state, and zip columns"

Claude Code generates the migration file in your framework's format (Prisma Migrate, Knex, TypeORM migrations, raw SQL -- whatever you use). It handles:

• The forward migration with correct SQL
• The rollback migration that cleanly reverses the change
• Data transformations when columns are renamed or types change
• Default values for new non-nullable columns

The Migration Review Checklist

Even with AI, I review every migration against this checklist before running it:

1. Does the up migration do what I intended? Read the SQL and confirm.
2. Does the down migration cleanly reverse it? Test the rollback.
3. Are there data loss risks? Column drops, type changes, and constraint additions can lose data.
4. What about existing data? If adding a non-nullable column, is the default value correct?
5. Performance impact? Large table alterations can lock the table. Is this migration safe to run on production with live traffic?
6. Ordering dependencies? Does this migration depend on a previous migration that might not have run yet?

Query Optimization

Paste a slow query and your schema into Claude Code. Describe the performance problem: "This query takes several seconds on a table with millions of rows."

Claude Code analyzes the query and suggests:

• Missing indexes that would improve performance
• Query restructuring to avoid full table scans
• Subquery elimination or conversion to joins
• Proper use of database-specific features like partial indexes or materialized views
• Pagination strategies for large result sets

I have seen AI suggestions reduce query times dramatically. The key is providing the actual query, the schema, and the approximate data volume. Without all three, the suggestions are generic and potentially wrong.

Seed Data Generation

Generating realistic test data is tedious. Claude Code generates seed scripts that create:

• Realistic names, emails, and text content
• Proper relationships between related records
• Edge cases (empty strings, null values, maximum-length text)
• Configurable volumes (ten records for development, thousands for performance testing)
• Date ranges that reflect realistic usage patterns

Describe your data: "Generate seed data for the projects and experiments tables. Each project should have between three and ten experiments. Include a mix of active, paused, and completed experiments. Use realistic project names related to e-commerce optimization."

The output is a seed script that integrates with your existing framework and can be run repeatedly without duplicate key errors.

Complex Operations

Data Backfills

When you add a new column that needs to be populated from existing data, describe the logic and let Claude Code generate the backfill script:

"Backfill the new experiment_duration column by calculating the difference between created_at and completed_at for completed experiments. Set it to null for active experiments. Process in batches of a thousand to avoid locking the table."

Schema Refactoring

When your data model needs restructuring, describe the current state and desired state:

"Currently user preferences are stored as columns on the users table. Refactor these into a separate user_preferences table with a one-to-one relationship. Migrate existing data and add a foreign key constraint."

Claude Code generates the complete migration including data transfer, constraint creation, and old column cleanup.

Multi-Step Migrations

For changes that require multiple deployment steps (like renaming a column that live code depends on):

"This column rename needs to be deployed in three steps: first add the new column and backfill data, then update application code to use the new column, then remove the old column. Generate three separate migrations."

Claude Code understands the deployment sequencing and generates safe, incremental migrations that can be deployed independently.

Working With Existing Databases

When starting with an existing database, I give Claude Code the current schema by exporting it:

• For PostgreSQL, the schema dump
• For ORMs, the schema definition file
• For undocumented databases, the output of introspection queries

Once Claude Code has the current schema, it can generate migrations that work with your existing data rather than assuming a clean start. This is essential for working with production databases.

Common Mistakes to Avoid

Running AI-generated migrations on production without testing: Always test migrations on a copy of your production data first. AI gets the logic right most of the time, but "most of the time" is not good enough for production databases.

Trusting AI index suggestions blindly: AI suggests indexes based on query patterns, but it does not know your write volume. An index that speeds up reads can slow down writes. Consider the full workload.

Skipping rollback verification: Just because the AI generated a rollback migration does not mean it works. Test both directions.

Ignoring database-specific behavior: AI might generate SQL that is technically valid but behaves differently across databases. If you are using MySQL and the AI's knowledge is strongest in PostgreSQL, verify the behavior.

FAQ

Can AI design a schema from scratch for a complex application?

It can generate a solid first draft, but complex applications have domain nuances that require human judgment. Use AI for the initial design and refine based on your domain knowledge. Expect to iterate two or three times before the schema is production-ready.

Should I let AI write migrations for production databases?

AI writes the migration. You review and test it thoroughly. Never run an unreviewed migration on production regardless of whether a human or AI wrote it. The review process is the safety net.

How does AI handle database-specific features like PostgreSQL extensions?

Remarkably well, if you tell it what you are using. Mention your database, version, and any extensions (PostGIS, pg_trgm, etc.) in your prompt. The output quality improves substantially with this context.

What about NoSQL databases?

The same principles apply to document schemas, key-value designs, and other NoSQL databases. Describe your access patterns clearly and AI generates appropriate schema designs. The emphasis on access patterns is even more important for NoSQL because the schema design is driven entirely by how you query the data.