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

A developer outlines a practical method for getting reliable SQL from AI coding assistants, arguing that most AI-written queries fail silently rather than with errors. The approach supplies three inputs the model typically lacks: the full DDL schema with keys and nullability, the exact database engine and version, and the query plan via EXPLAIN, backed by a reusable prompt that forces the model to state grain, assumptions, and join cardinalities before writing SQL.

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