How to Use Claude for SQL and Database Work

If you've ever stared at a complex JOIN query at 11pm, wondering why the execution plan is doing a full table scan, you know exactly why developers are turning to Claude for database work. Claude isn't just a writing assistant — it's a surprisingly capable database engineer that can write, debug, optimize, and explain SQL across PostgreSQL, MySQL, SQLite, and more.

This tutorial walks through the five most valuable ways to use Claude for SQL and database work, with real examples you can adapt immediately.

Why Claude Excels at SQL Work

Before diving in, it's worth understanding what makes Claude particularly good at database tasks:

• Schema awareness: Claude can reason about relationships between tables, foreign keys, and normalization rules — not just syntax
• Dialect fluency: It handles PostgreSQL-specific features (CTEs, window functions, JSONB operators) as well as MySQL, SQLite, BigQuery, and Snowflake syntax differences
• Performance intuition: Claude has absorbed enough database engineering knowledge to suggest indexes, explain query plans, and flag N+1 problems
• Explanation depth: It can explain why a query works, not just write it — crucial for learning and code review

The key is learning how to prompt it correctly. Let's get practical.

1. Generating SQL Queries from Plain English

The most common use case: describing what you want in English and getting working SQL back.

Prompt:
I have a PostgreSQL database with these tables:
- users (id, email, created_at, plan)
- orders (id, user_id, amount, status, created_at)
- order_items (id, order_id, product_id, quantity, unit_price)

Write a query that shows the top 10 customers by total spend in the last 90 days,
including their email, order count, and total amount. Only include completed orders.

Claude will return something like:

sqlSELECT
  u.email,
  COUNT(DISTINCT o.id)     AS order_count,
  SUM(oi.quantity * oi.unit_price) AS total_spend
FROM users u
JOIN orders o
  ON o.user_id = u.id
  AND o.status = 'completed'
  AND o.created_at >= NOW() - INTERVAL '90 days'
JOIN order_items oi
  ON oi.order_id = o.id
GROUP BY u.id, u.email
ORDER BY total_spend DESC
LIMIT 10;

Multi-Step Query Refinement

Don't try to get everything perfect in one shot. Treat it like a conversation:

Follow-up prompt:
Now add a column showing the customer's plan (free/pro/enterprise) 
and filter to only pro and enterprise users.

Claude will extend the query rather than rewriting from scratch, maintaining context across turns.

2. Schema Design and Normalization

Claude is excellent at designing database schemas from requirements — and at critiquing existing ones.

Prompt:
I'm building a SaaS app for restaurant reservation management. Key requirements:
- Restaurants have multiple locations
- Each location has time slots for reservations
- Customers can book 1-8 people per reservation
- We need waitlist functionality
- Staff need to see today's reservations sorted by time

Design a normalized PostgreSQL schema. Include primary keys, foreign keys, 
indexes for the most common queries, and any constraints you'd recommend.

Claude will produce a full schema with CREATE TABLE statements, including:

• Junction tables where needed
• Appropriate index suggestions for the query patterns you described
• Check constraints (e.g., party_size BETWEEN 1 AND 20)
• Notes on design decisions and trade-offs

Prompt:
Here's our current users table. We're experiencing slow queries as we approach 
5 million rows. What indexes should we add, and are there any design issues 
we should fix in a migration?

CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid(),
  email VARCHAR(255),
  full_name TEXT,
  created_at TIMESTAMP,
  last_login TIMESTAMP,
  metadata JSONB,
  ...
);

Claude will flag missing primary key declaration, suggest NOT NULL constraints, recommend a GIN index on the JSONB column, and propose a partial index on last_login if you're frequently querying active users.

3. Query Optimization and Debugging

This is where Claude saves the most time. Paste a slow query and ask for a diagnosis.

Prompt:
This query runs in 8 seconds on a 2M row table. Here's the query and the 
EXPLAIN ANALYZE output. What's wrong and how do I fix it?

[paste query]
[paste EXPLAIN ANALYZE output]

Claude will read the execution plan and identify the bottleneck — typically one of:

• Missing index causing a sequential scan
• Implicit type cast breaking an index (e.g., comparing VARCHAR to an INTEGER parameter)
• Poor JOIN order
• Missing statistics causing the planner to choose the wrong strategy

sql-- Slow: implicit cast prevents index use
SELECT * FROM orders WHERE user_id = '12345';  -- user_id is INTEGER

-- Fast: explicit cast or use the right type
SELECT * FROM orders WHERE user_id = 12345;

Claude catches these in seconds. Without it, this is the kind of thing you spend 45 minutes on.

Prompt:
This query is supposed to count orders per user, but some users show 
higher counts than I expect. I think there's a duplicate issue but 
I can't find it. Can you review the logic?

[paste query]

Claude is good at spotting accidental cross-joins, GROUP BY columns that create phantom duplicates, and COUNT(*) vs COUNT(DISTINCT id) mistakes.

4. Complex SQL Patterns: CTEs, Window Functions, and Recursive Queries

If you avoid window functions because the syntax is intimidating, Claude removes that friction entirely.

Prompt:
Using window functions, write a query that for each order shows:
- The order amount
- The running total for that customer
- That customer's rank by spending (highest = rank 1)
- The percentage each order represents of that customer's total

Claude produces:

sqlSELECT
  o.id,
  o.user_id,
  o.amount,
  SUM(o.amount) OVER (
    PARTITION BY o.user_id
    ORDER BY o.created_at
    ROWS UNBOUNDED PRECEDING
  ) AS running_total,
  RANK() OVER (
    PARTITION BY o.user_id
    ORDER BY o.amount DESC
  ) AS amount_rank,
  ROUND(
    o.amount / SUM(o.amount) OVER (PARTITION BY o.user_id) * 100,
    2
  ) AS pct_of_customer_total
FROM orders o
ORDER BY o.user_id, o.created_at;

And crucially, it will explain what each window frame clause does if you ask.

Prompt:
I have an org chart table: employees(id, name, manager_id).
Write a recursive CTE that returns each employee's full reporting chain
(all managers up to the CEO).

Recursive queries are notoriously tricky to get right. Claude handles the WITH RECURSIVE pattern correctly, including the base case and the termination condition.

5. Migrations and Database Change Management

Claude can write migration files, generate rollback scripts, and think through the operational risk of schema changes.

Prompt:
I need to add a NOT NULL column `tenant_id UUID` to a `documents` table 
that currently has 50 million rows in production. The app can't have 
more than a few seconds of downtime. Walk me through the safest migration 
strategy and write the SQL for each step.

Claude will outline the zero-downtime pattern:

sql-- Step 1: Add nullable column (fast — no table rewrite)
ALTER TABLE documents ADD COLUMN tenant_id UUID;

-- Step 2: Backfill in batches (run this in a loop)
UPDATE documents
SET tenant_id = '00000000-0000-0000-0000-000000000001'  -- default tenant
WHERE tenant_id IS NULL
LIMIT 10000;

-- Step 3: Add NOT NULL constraint after backfill completes
ALTER TABLE documents ALTER COLUMN tenant_id SET NOT NULL;

This is the kind of production-safe thinking that distinguishes senior engineers. Claude gives you that perspective on demand.

How to Get the Best Results

After using Claude for dozens of database tasks, here's what makes the biggest difference:

Limitations to Know

Claude is not a replacement for a database administrator on high-stakes production systems. Specific limitations:

• No live query execution: Claude can't see your actual data or run EXPLAIN ANALYZE itself — you paste results to it
• No access to real statistics: Query optimization advice is based on patterns, not your actual table statistics and data distribution
• Hallucination risk on proprietary features: For niche DB features (e.g., Citus for distributed PostgreSQL, or cloud-specific syntax), verify against official docs
• Complex stored procedures: Long procedural SQL (PL/pgSQL, T-SQL) benefits from being broken into smaller pieces rather than handed over as one large block

Key Takeaways

• Paste your full schema into every prompt — vague schemas produce generic SQL
• Use Claude iteratively: start with the base query, then refine in follow-up turns
• For optimization, always include EXPLAIN ANALYZE output — Claude reads execution plans accurately
• Window functions, recursive CTEs, and complex aggregations are areas where Claude adds the most leverage
• For production migrations on large tables, ask Claude to "think through operational risk" — it gives zero-downtime patterns by default

Next Steps

Ready to level up your AI and database skills? If you're working toward the Claude Certified Architect (CCA-F) certification, database integration patterns are a tested domain — Claude agents connecting to Postgres via MCP servers appear in the agentic system design questions.

Explore our CCA practice test bank with 200+ questions covering tool use, multi-agent systems, and production deployment patterns. Or browse the AI tools blog for more practical Claude tutorials.

The SQL you write with Claude's help is still your SQL — understanding why a query works is what separates engineers who use AI effectively from those who just copy and paste.