Originally published at claudeguide.io/claude-code-sql-generation

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 

---

## 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:

sql
-- 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

sql
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

sql
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

sql
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

sql
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

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

### Complex Reporting Queries

sql
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](/claude-api-cost-prompt-caching-break-even) — caching your schema context saves significant tokens on repeated queries.

---

## Testing SQL Output

### Validate Generated Queries

sql
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