# How to get correct SQL from AI: give it the schema, the dialect, and EXPLAIN

> Source: <https://dev.to/mars_peakul/how-to-get-correct-sql-from-ai-give-it-the-schema-the-dialect-and-explain-nfm>
> Published: 2026-10-19 02:42:12+00:00

AI tools are genuinely good at writing SQL. Ask ChatGPT, Claude, Cursor or Claude Code for "monthly revenue per plan" and you'll have a query in seconds, and it will probably run.

That's the problem. Wrong SQL rarely fails loudly. It returns a number, and the number looks plausible. Here are the mistakes I see most often in AI-written SQL, why they happen, and the three inputs that fix most of them: **the schema, the dialect, and the plan**.

The model isn't careless. It's missing information:

`users.created_at`, `orders.customer_id`).
Each of those turns into a specific, recognisable bug.

```
-- "Revenue per plan from subscription invoices"
SELECT i.plan_id, SUM(i.amount_cents) AS revenue_cents
FROM invoices i
JOIN invoice_lines l ON l.invoice_id = i.id
WHERE l.kind = 'subscription'
GROUP BY i.plan_id;
```

If an invoice has three subscription lines, its total is summed three times. The query runs, the number is wrong, and nothing warns you. The fix is to stop joining at the wrong grain:

```
SELECT i.plan_id, SUM(i.amount_cents) AS revenue_cents
FROM invoices i
WHERE EXISTS (
  SELECT 1 FROM invoice_lines l
  WHERE l.invoice_id = i.id AND l.kind = 'subscription'
)
GROUP BY i.plan_id;
```

A model can only spot this if it knows `invoices` → `invoice_lines` is one-to-many, which means it needs the schema with its keys.

```
-- "Customers who have never ordered"
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
```

If any `orders.customer_id` is NULL, `NOT IN` is never true and this returns **zero rows**. `NOT EXISTS` doesn't have that problem:

```
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
```

Related traps: `status <> 'cancelled'` also drops rows where `status` is NULL; `COUNT(col)` skips NULLs while `COUNT(*)` doesn't; `SUM` over no rows returns NULL, not 0. The model needs to know which columns are nullable, so give it the DDL, not a summary.

Some differences fail loudly (`date_trunc` doesn't exist in MySQL; MySQL has no `RETURNING`). The dangerous ones run fine and return different results:

`7 / 2` is `3` in PostgreSQL and SQLite, `3.5000` in MySQL.`LIKE` is case-sensitive in PostgreSQL, case-insensitive for ASCII by default in SQLite, and collation-dependent in MySQL.`ON CONFLICT ... DO UPDATE` in PostgreSQL and SQLite (3.24+), `ON DUPLICATE KEY UPDATE` in MySQL.
Tell the model the engine **and version**. "MySQL" isn't enough: window functions and CTEs arrived in MySQL 8.0, `EXCEPT` in 8.0.31.

Build a context block once and reuse it. Get the DDL straight from the database:

`pg_dump --schema-only -t orders -t invoices ...`
`SHOW CREATE TABLE orders;`
`.schema orders` in the `sqlite3` shell
Keep nullability, foreign keys, unique constraints and composite index column order. Add rough row counts and your conventions (soft-delete column, tenant column, time zone of timestamps). In Cursor, Claude Code or Codex, save it as a file in the repo (`docs/db-context.md`) and reference it from your rules file so it's loaded every time.

Most wrong reports are wrong because of an unstated definition, not bad syntax. Here's the prompt I start with:

```
You are a senior SQL developer. Write ONE query for the exact engine and version below.

Engine: [ENGINE AND VERSION]
Schema (use only these tables and columns; if something is missing, say so):
[DDL]

Question: [QUESTION]
Definitions: [e.g. "month = calendar month in UTC", "exclude deleted_at IS NOT NULL"]

Before writing SQL:
1. State the grain (one output row per what), filters, and the time window as
   >= start AND < end.
2. List your assumptions. If one would change the answer materially, ask me.
3. Name each join and its cardinality (1:1, 1:N, N:M) and how you avoid
   double counting in aggregates.
Then write the query, and give me 2 sanity-check queries (a total that should
reconcile, and a spot check for one entity).

Rules: bind parameters for user values, SELECT only, syntax valid for this engine.
```

The "join cardinality" step catches fan-out. The "assumptions" step catches the definitions. The sanity checks give you something to run instead of something to trust.

"Add an index on every column in the WHERE clause" is what you get when the model has no plan to look at. Give it one:

`EXPLAIN (ANALYZE, BUFFERS) <query>;` (from PostgreSQL 18, buffer counts are included with `ANALYZE` automatically).`EXPLAIN FORMAT=TREE <query>;` for the plan, `EXPLAIN ANALYZE <query>;` (8.0.18+) for actual timings.`EXPLAIN QUERY PLAN <query>;` (look for `SCAN` vs `SEARCH ... USING INDEX`).
One warning: `EXPLAIN ANALYZE` **executes** the statement in PostgreSQL and MySQL. For an `UPDATE` or `DELETE`, wrap it in a transaction and roll back, and do it on a copy of the data when you can.

Then ask the model to walk the plan node by node and to cite the node behind every claim. In PostgreSQL plans, tell it that actual time and rows are per loop, so a cheap-looking inner node with `loops=50000` may be where the time goes. Ask it to flag estimated-vs-actual row mismatches (often a statistics problem, not an index problem) and sorts that spill to disk. And get the plan from production-like data: the planner picks different plans for a thousand rows than for ten million.

AI-written SQL that only reads is low risk. AI-written DDL and DML deserves a rule: **nobody runs it on production without reading every statement.** In practice:

`WHERE` of every `UPDATE`/` DELETE` as a `SELECT COUNT(*)` before running the write.`COMMIT`. (PostgreSQL and SQLite DDL is transactional; MySQL DDL commits implicitly and can't be rolled back.)
Schema in, dialect stated, definitions written down, plan attached, verification out. The model is the same; the inputs are what change the answer.

*I packaged the prompts I use for this (32 of them: natural language → SQL with schema context, query review for NULLs, fan-out, N+1 and index use, EXPLAIN reading for PostgreSQL, MySQL and SQLite, schema and index reviews, zero-downtime migrations and rollbacks, ORM and injection checks, seed data and DB tests, window-function reports) plus a verification checklist and a PDF guide as the AI SQL & Database Prompt Pack: [https://payhip.com/b/VhqW7](https://payhip.com/b/VhqW7). Everything above works without it.*

*— Pluto · [@Mars_Peakul](https://x.com/Mars_Peakul)*