If you've ever stared at a complex schema and spent 20 minutes writing a query you could describe in one sentence, this walkthrough is for you. I'll show you exactly how to prompt an AI assistant to generate accurate, production-ready SQL from plain English — including the context you need to provide, the prompts that work, and how to verify what comes back.
The usual failure mode: you paste a vague question into ChatGPT or Claude, get back a query that looks plausible, run it, and get wrong results or an error. The fix isn't a better AI — it's a better prompt. AI models write SQL accurately when you give them the same context a senior teammate would need.
Never ask for a query without first providing your table definitions. Copy the CREATE TABLE
statements directly from your database.
-- Paste this into your prompt first
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
status VARCHAR(20) NOT NULL, -- 'pending', 'shipped', 'cancelled'
total_amount NUMERIC(10, 2),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
signup_date DATE NOT NULL,
plan VARCHAR(20) DEFAULT 'free' -- 'free', 'pro', 'enterprise'
);
If your schema is large, include only the tables relevant to your question.
Use this template every time:
You are a SQL expert working with PostgreSQL.
Here are my table definitions:
[PASTE SCHEMA HERE]
Write a query that: [PLAIN ENGLISH DESCRIPTION]
Requirements:
- Use CTEs if the logic has more than one step
- Add a comment explaining any non-obvious join condition
- Do not use SELECT * — name every column
Example request:
Write a query that returns, for each pro or enterprise customer who signed up
in the last 90 days, their email, total number of orders, and total spend —
but only where total spend exceeds $500. Sort by total spend descending.
What the AI returns:
-- Customers on paid plans, signed up in the last 90 days
WITH recent_paid_customers AS (
SELECT id, email
FROM customers
WHERE plan IN ('pro', 'enterprise')
AND signup_date >= CURRENT_DATE - INTERVAL '90 days'
),
customer_order_totals AS (
SELECT
c.email,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spend
FROM recent_paid_customers c
-- Left join so customers with zero orders still appear; filter below
JOIN orders o ON o.customer_id = c.id
WHERE o.status != 'cancelled'
GROUP BY c.email
)
SELECT
email,
order_count,
total_spend
FROM customer_order_totals
WHERE total_spend > 500
ORDER BY total_spend DESC;
Clean, readable, and correct on the first try — because the prompt had enough context.
Ask the AI to explain the query back to you in plain English:
Explain this query step by step. Call out any assumptions you made
about the data or business logic.
This surfaces hidden assumptions fast. If the explanation doesn't match your intent, correct it in the next message — the model retains the schema context in the same conversation.
Follow up with edge-case questions in the same thread:
What happens if a customer has no orders at all? Will they appear in results?
Rewrite using a LEFT JOIN if needed so they show up with zero values.
The model will revise the query and explain the change. This loop — generate, explain, stress-test, revise — usually gets you to a production-safe query in three to four messages.
CREATE TABLE
statementsEXPLAIN ANALYZE
on the result for any query touching large tablesThat's the full loop. No magic — just structured context.
I break down one workflow like this every week in The AI Leverage Weekly — practical, no fluff, free. Subscribe: https://theaileverageweekly.beehiiv.com/subscribe?utm_source=devto&utm_medium=article&utm_campaign=medium_w9