DEV Community

The AI Leverage Weekly
The AI Leverage Weekly

Posted on

How to Use AI to Write SQL Queries from Plain English

#sql #database #ai #tutorial

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.

Why Most "AI + SQL" Attempts Fail

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.

Step 1: Give the AI Your Schema

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'
);
Enter fullscreen mode Exit fullscreen mode

If your schema is large, include only the tables relevant to your question.

Step 2: Write the Prompt

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Clean, readable, and correct on the first try — because the prompt had enough context.

Step 3: Verify Before You Run

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.
Enter fullscreen mode Exit fullscreen mode

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.

Step 4: Iterate for Edge Cases

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.
Enter fullscreen mode Exit fullscreen mode

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.

The Workflow, Condensed

  1. Paste relevant CREATE TABLE statements
  2. Use the prompt template: role + schema + plain English goal + formatting requirements
  3. Ask for a plain-English explanation to catch wrong assumptions
  4. Stress-test edge cases in the same conversation thread
  5. Run EXPLAIN ANALYZE on the result for any query touching large tables

That'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

Top comments (0)