Database Operations from the CLI

You are staring at a query that takes eight seconds. The EXPLAIN output is a wall of sequential scans and nested loops. Your product manager wants a report that joins five tables with three levels of aggregation. The intern just pushed a migration that dropped a column in production. Database work is where small mistakes are expensive and expertise is hard to come by.

Claude Code brings that expertise to your terminal. It reads your schema, understands your query patterns, and generates migrations that are safe to run in production. It writes complex SQL that you would spend an hour debugging, and it catches the mistakes in migrations before they hit staging. This lesson covers the database workflows that work in real projects.

What You’ll Walk Away With

• A workflow for designing schemas and generating safe migrations from the terminal
• Prompts for writing and optimizing complex queries
• The migration safety pattern that prevents production disasters
• Headless mode patterns for automated database audits

Schema Design from Requirements

When starting a new feature that needs database changes, have Claude design the schema before writing any code.

Tip

Copy-paste prompt for schema design:

I need to add a teams feature to our application. Read the
existing schema in src/db/schema.ts (or schema.prisma, or
migrations/).

Design new tables/models for:
- Teams with name, slug, and billing info
- Team membership with roles (owner, admin, member)
- Invitations with email, role, and expiry
- Team settings (a flexible key-value store)

Requirements:
- A user can belong to multiple teams
- Each team has exactly one owner
- Invitations expire after 7 days
- Team slugs must be unique

Follow the exact conventions from our existing schema:
same naming style, same ID type, same timestamp columns.
Show me the schema changes before generating any migration.

Review the schema design carefully. Column types, constraints, and indexes are much harder to change after the migration runs. Specifically check:

• Are foreign keys set up with the right ON DELETE behavior?
• Are unique constraints where they need to be?
• Are the indexes sufficient for the queries you plan to run?
• Did Claude use the same conventions as your existing schema?

Generating Safe Migrations

Migrations are the most dangerous database operation in a production system. A bad migration can lock tables, drop data, or cause downtime. Claude can generate migrations that avoid these pitfalls.

1. Generate the migration

   Based on the schema changes we just designed, generate a
   migration file. Follow our existing migration patterns in
   migrations/ (or prisma/migrations/, or drizzle/).
   
   The migration must:
   - Use IF NOT EXISTS for all CREATE statements
   - Add columns as nullable first, then backfill, then add NOT NULL
   - Include an explicit DOWN migration for rollback
   - Never lock large tables (use concurrent index creation)
   - Include comments explaining each step

2. Review the migration before running it

   Show me the generated migration SQL. For each statement,
   explain: will it lock the table? How long will it take
   on a table with 5 million rows? Is it reversible?

3. Test the migration on a copy

   Run the migration against our test database. Then verify:
   - All new tables/columns exist
   - Existing data is preserved
   - The application still connects and queries work
   - The rollback migration works (run down, then up again)

4. Deploy with confidence

   Once the migration passes review and testing, you can deploy knowing it is safe.

The zero-downtime migration pattern

For changes to existing tables with production traffic, use the expand-contract pattern:

I need to rename the column "fname" to "first_name" on the users
table. This table has 2 million rows and serves 500 requests/second.

Generate a migration sequence that avoids downtime:

Phase 1 (expand): Add the new column "first_name" alongside "fname"
Phase 2 (dual-write): Application writes to both columns
Phase 3 (backfill): Copy data from "fname" to "first_name" in batches
Phase 4 (switch reads): Application reads from "first_name"
Phase 5 (contract): Drop the "fname" column

Generate the SQL migration for each phase. The backfill must
process in batches of 10,000 rows with a brief pause between
batches to avoid overwhelming the database.

Writing Complex Queries

Claude excels at SQL. It understands joins, window functions, CTEs, and the performance implications of each approach.

Analytical queries

Tip

Copy-paste prompt for complex query generation:

Read our database schema. Write a query that produces a monthly
revenue report with:

- Revenue by product category
- Month-over-month growth percentage
- Running total for the year
- Top 5 products by revenue in each category
- Average order value per customer segment (new vs returning)

Use CTEs for readability. Optimize for our PostgreSQL database
with the indexes we already have. Show me the EXPLAIN plan
and suggest any indexes that would improve performance.

Query optimization

When a query is slow, pipe the query and its EXPLAIN output to Claude:

This query takes 8 seconds. Here's the EXPLAIN ANALYZE output:
[paste output]

The query:
[paste query]

Read our schema to understand the table structure and existing
indexes. Tell me:
1. Why is it slow? (identify the specific bottleneck)
2. What indexes would help?
3. Can the query be restructured for better performance?
4. Show me the optimized version and its expected EXPLAIN plan.

ORM query generation

For projects using Prisma, Drizzle, or SQLAlchemy, have Claude write the ORM queries instead of raw SQL:

Read our Drizzle schema in src/db/schema.ts. Write a query
using the Drizzle query builder that:

1. Finds all orders from the last 30 days
2. Includes the customer name and email
3. Includes the order items with product names
4. Filters by status "completed" or "shipped"
5. Sorts by order total descending
6. Paginates with cursor-based pagination

Use the Drizzle relational query API where possible.
Show me the generated SQL to verify it's efficient.

Seeding Test Data

Good tests need realistic data. Claude can generate seed scripts that create comprehensive test datasets.

Read our database schema and generate a seed script that creates:

- 50 users with realistic names and emails
- 10 teams with 3-15 members each
- 200 products across 5 categories
- 500 orders with realistic distribution (most recent month
  has more orders, weekends have fewer)
- Reviews for ~60% of products with realistic rating distribution
  (most are 4-5 stars, few are 1-2)

Use our existing seed utility in scripts/seed.ts as a reference.
The seed should be idempotent -- running it twice should not
create duplicates.

For test fixtures specifically:

Generate factory functions for our test suite following the
patterns in tests/fixtures/.

Create:
- createTestUser(overrides?) - creates a user with sensible defaults
- createTestTeam(owner, overrides?) - creates a team with the owner
- createTestOrder(user, products, overrides?) - creates an order

Each factory should return the created object and clean up
after the test (or use transactions that roll back).

Database Auditing with Headless Mode

Use headless mode for automated database health checks:

# Check for common schema issues
claude -p "Read our database schema and check for:
1. Tables missing primary keys
2. Foreign keys without indexes
3. Columns that should be NOT NULL but aren't
4. Missing created_at/updated_at timestamps
5. Inconsistent naming conventions
6. Tables without any indexes beyond the primary key

Report each issue with the table name, column name,
and suggested fix." --output-format json > db-audit.json

# Analyze query performance
claude -p "Read the slow query log at /var/log/postgresql/slow.log
and analyze the top 10 slowest queries. For each one:
1. Explain why it's slow
2. Suggest an index that would help
3. Show the optimized query if it can be restructured
4. Estimate the improvement

Read our schema to understand the table structure." \
  --output-format json > query-audit.json

Working with Multiple Database Types

PostgreSQL-specific features

We use PostgreSQL. Generate queries that use PG-specific features:

1. Use JSONB columns for flexible metadata with GIN indexes
2. Use array columns for tags with array operators
3. Use full-text search with ts_vector and ts_query
4. Use row-level security for multi-tenant isolation
5. Use LISTEN/NOTIFY for real-time updates

Show me how to set up each feature and integrate it with
our ORM (Drizzle/Prisma).

SQLite for development (D1 for production)

Our production database is Cloudflare D1 (SQLite). Generate
migrations that work within SQLite's constraints:

- No ALTER TABLE for column renames or type changes
- No concurrent index creation
- Limited ALTER TABLE support (add column only)

For schema changes that SQLite can't handle directly, generate
the migration as: create new table, copy data, drop old table,
rename new table. Include proper transaction handling.

Sub-agents for Database Research

When you need to understand how the database is being used before making changes:

Use sub-agents to investigate our database usage:

1. Find all queries in the codebase that touch the users table.
   Group them by: SELECT, INSERT, UPDATE, DELETE. List the
   file and line number for each.

2. Find all places where we use raw SQL instead of the ORM.
   Check if any of them are vulnerable to SQL injection.

3. Analyze the migration history and list any migrations
   that were destructive (dropped columns, tables, or data).
   Check if they had proper rollback procedures.

Report findings so we can plan the schema changes safely.

When This Breaks

The generated migration locks the table in production. Claude did not account for table size. Always ask: “This table has N million rows. Will this migration lock it? How long will it take?” For large tables, use online schema change tools or the expand-contract pattern.

ORM queries are correct but slow. The ORM generates suboptimal SQL. Ask Claude to show you the generated SQL: “Show me the raw SQL this Prisma/Drizzle query generates. Then compare its EXPLAIN plan with a hand-written query that does the same thing.”

Seed data causes constraint violations. The seed script creates data in the wrong order (orders before users, for example). Tell Claude: “Generate the seed data in dependency order. Users first, then teams, then products, then orders. Use actual IDs from previously created records.”

The rollback migration does not work. This happens when the down migration was an afterthought. Always test the full cycle: “Run the migration up, verify data, run the migration down, verify the schema is exactly as it was before. Show me the schema diff.”

Schema design does not account for future queries. Claude designed the schema based on current requirements, but you know a feature is coming that will need different access patterns. Tell Claude: “We’ll also need to query this data by [describe the future query]. Does the current schema support that efficiently, or do we need additional indexes or denormalization?”

What’s Next

Your database layer is designed, migrated, and optimized. Now build the API endpoints that sit on top of it.

API Development Build REST and GraphQL endpoints that efficiently query your database

Deployment Deploy your database migrations and application code safely to production