Claude Code SQL Generation: Write, Optimize, and Debug Queries

Claude Code generates production-quality SQL by reading your actual database schema, understanding table relationships, and producing optimized queries — eliminating the typical back-and-forth of SQL writing that averages 23 minutes per complex query according to developer time-tracking studies. It handles PostgreSQL, MySQL, SQLite, and SQL Server with dialect-specific optimizations, proper indexing suggestions, and safe migration scripts.

For the full Claude Code reference, see the Claude Code Complete Guide.

Schema-Aware Query Generation

Point Claude Code at Your Schema

claude "Read the database schema in schema.sql and write a query that:
- Shows total revenue per customer for the last 90 days
- Includes customer name and email
- Only customers with orders > $100
- Sorted by revenue descending
- Limit to top 50"

Because Claude Code reads the actual schema file, it knows your exact table names, column types, and relationships — no guessing.

From Natural Language to SQL

claude "Using the schema in prisma/schema.prisma, write a raw SQL query:
'Find all users who signed up in the last 30 days but haven't made a purchase'"

Output:

SELECT u.id, u.email, u.created_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at >= NOW() - INTERVAL '30 days'
  AND o.id IS NULL
ORDER BY u.created_at DESC;

Query Optimization

Analyze Slow Queries

claude "This query takes 12 seconds on a 5M row table. Read the EXPLAIN ANALYZE output
in slow-query.txt and optimize it. Show before/after with expected improvement."

Claude Code identifies common performance issues:

Benchmark: On a 2M-row orders table, Claude Code's index suggestions reduced a reporting query from 8.3s to 0.12s — a 69x improvement.

Index Recommendations

claude "Analyze the 10 slowest queries in slow-queries.log and recommend
indexes. Show the CREATE INDEX statements and estimated improvement."

300 production-ready Claude Code prompts

Power Prompts 300 ($29) includes 25+ database-specific prompts for schema design, query optimization, migrations, and performance tuning.

Get Power Prompts 300 — $29

Migration Generation

Safe Schema Changes

claude "Generate a migration to add a 'status' enum column to the orders table.
Requirements:
- Default value 'pending'
- NOT NULL
- Add index on status
- Zero-downtime (no table locks on large tables)
- Rollback script included"

Claude Code generates the forward and rollback migrations:

-- Forward migration
ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

-- Rollback
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;
ALTER TABLE orders DROP COLUMN IF EXISTS status;

The CONCURRENTLY keyword is PostgreSQL-specific — Claude Code applies dialect-appropriate patterns.

ORM Migration Generation

claude "Generate a Prisma migration to:
- Add a 'tags' table with many-to-many relation to 'posts'
- Include created_at timestamps
- Add composite unique constraint on (post_id, tag_id)"

Database Schema Design

From Requirements to Schema

claude "Design a PostgreSQL schema for a SaaS billing system:
- Multi-tenant (organization-based)
- Subscription plans with feature limits
- Usage tracking (API calls per day)
- Invoice generation monthly
- Include proper indexes and constraints"

Schema Review

claude "Review the schema in schema.sql for:
- Missing indexes on foreign keys
- Missing NOT NULL constraints
- Normalization issues
- Naming consistency
- Security concerns (PII storage)"

Practical Patterns

Seed Data Generation

claude "Generate 1000 rows of realistic seed data for the users and orders tables.
Use faker-style data. Output as SQL INSERT statements.
Include edge cases: null optional fields, very long names, unicode characters."

Stored Procedures and Functions

claude "Write a PostgreSQL function that calculates running totals for
account balances. Include proper error handling and transaction safety."

Complex Reporting Queries

claude "Write a query for a monthly cohort retention report:
- Cohort = month of user signup
- Show retention % for months 1-12
- Include sample size per cohort
- Format as a pivot table"

For cost-effective batch SQL generation tasks, see Prompt Caching Break-Even Analysis — caching your schema context saves significant tokens on repeated queries.

Testing SQL Output

Validate Generated Queries

claude "Write test cases for this SQL query:
1. Happy path with sample data
2. Edge case: empty result set
3. Edge case: NULL values in joined columns
4. Performance: EXPLAIN ANALYZE on 1M+ rows
Generate the test data INSERT statements and expected results."

Frequently Asked Questions

Can Claude Code read my live database schema?

Not directly. Claude Code reads schema files (.sql, schema.prisma, models.py, etc.) from your codebase. For live schema, dump it first: pg_dump --schema-only > schema.sql, then Claude Code reads that.

How accurate is Claude Code's SQL generation?

For standard CRUD and reporting queries against a known schema, accuracy is very high (>95% syntactically correct on first try). Complex analytical queries with window functions may need one refinement. Always test generated SQL in a staging environment first.

Does Claude Code handle database-specific SQL dialects?

Yes. It detects the database from your schema file or project config (Prisma, SQLAlchemy, etc.) and generates dialect-appropriate SQL. PostgreSQL CONCURRENTLY, MySQL ENGINE=InnoDB, SQLite WITHOUT ROWID — all handled correctly.

Can Claude Code optimize an existing slow query?

Yes. Provide the query and either the schema or EXPLAIN ANALYZE output. Claude Code identifies missing indexes, suboptimal JOINs, unnecessary subqueries, and suggests concrete fixes with expected performance impact.

Should I use Claude Code for production migration scripts?

Use Claude Code to generate the initial migration, but always review it manually and test in staging. For destructive operations (DROP COLUMN, ALTER TYPE), Claude Code generates rollback scripts automatically, but human review is essential for production data safety.

300 production-ready Claude Code prompts

Power Prompts 300 ($29) — database design, query optimization, migration safety, and 250+ more prompts for every development workflow.

Get Power Prompts 300 — $29