A broken query gives you an error. A wrong query gives you a result, and the result looks fine.
Highlights
- Every generated query executed without a single error, and fewer than four in ten returned the correct answer.
- Five queries ran cleanly and returned wrong results, which is the failure that matters because nothing announces it.
- The most common cause was a missing filter, such as totalling every order rather than only the completed ones.
- One query invented meaning for a column, treating a signup date as if it recorded whether someone had ordered.
- Adding notes about what the columns mean did not reliably improve correctness across six runs, which is a lesson about test set size rather than about notes.
- A read only database connection blocks every delete, drop and update at the driver, which no prompt instruction can promise.
- Checking a generated query means running it against questions whose answers you already know, which is the only test that catches a wrong result.
Point a model at a database schema, ask it a question in English, and it writes SQL. The SQL usually runs.
In this test every single query ran, and fewer than four in ten were correct. Five executed cleanly and returned results that were simply wrong, which is worse than an error, because an error tells you something happened.
Let us look at how to build the pipeline, measure both of those results separately, and then close most of the gap between them.
Why generated SQL is dangerous in a specific way
Most code fails loudly. Generated SQL fails quietly, because two very different things can go wrong.
A syntax error or a missing column throws, and you fix it. That failure is annoying and harmless.
The other failure is valid SQL that answers a different question from the one asked. It returns rows. Those rows go into a report, a dashboard, or an answer to a customer, and nothing anywhere flags a problem.
So the useful measurement is not whether the query ran. It is whether what it returned matches the answer you already knew.
What you need before you start
Everything runs on one machine. A Linux box, which a KodeKloud playground gives you, or your own laptop.
You need a model and a database. SQLite covers the database and ships with Python, so there is nothing to install for that half.
Install Ollama
sudo apt-get update && sudo apt-get install -y zstd python3 python3-venv
curl -fsSL https://ollama.com/install.sh | sh
curl http://localhost:11434/
ollama pull llama3.2:1bOllama is running from that third command means the service already started. A connection refused means you need ollama serve & first.
A small model is deliberate here. It fails the same ways a larger one does, just more often. That makes the failures visible in eight questions rather than eighty.
Set up the project
mkdir text2sql && cd text2sql
python3 -m venv .venv
source .venv/bin/activateNothing to install, since sqlite3 and urllib are both in the standard library. You will build six files.
Step 1: Build a database worth testing against
Create build_db.py. The data is small enough to check by hand, which is the whole point.
import os
import sqlite3
if os.path.exists("shop.db"):
os.remove("shop.db")
db = sqlite3.connect("shop.db")
db.executescript("""
CREATE TABLE customers (
id INTEGER PRIMARY KEY, name TEXT, country TEXT, signed_up TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY, customer_id INTEGER, placed_at TEXT,
status TEXT, total_pence INTEGER
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY, order_id INTEGER, product TEXT,
quantity INTEGER, unit_pence INTEGER
);
""")
customers = [(1, "Ada", "UK", "2025-01-05"), (2, "Bo", "UK", "2025-03-11"),
(3, "Cy", "IE", "2025-06-02"), (4, "Di", "UK", "2026-01-20"),
(5, "Eze", "NG", "2026-02-14"), (6, "Fen", "UK", "2026-03-15")]
orders = [(1, 1, "2026-01-10", "completed", 4500), (2, 1, "2026-02-02", "completed", 12000),
(3, 2, "2026-02-05", "cancelled", 3000), (4, 2, "2026-02-20", "completed", 7800),
(5, 3, "2026-03-01", "completed", 2500), (6, 4, "2026-03-04", "refunded", 9900),
(7, 5, "2026-03-07", "completed", 15000), (8, 1, "2026-03-09", "pending", 600)]
items = [(1, 1, "mug", 2, 1500), (2, 1, "tea", 1, 1500), (3, 2, "kettle", 1, 12000),
(4, 4, "mug", 4, 1500), (5, 4, "tea", 1, 1800), (6, 5, "tea", 1, 2500),
(7, 7, "kettle", 1, 12000), (8, 7, "mug", 2, 1500)]
db.executemany("INSERT INTO customers VALUES (?,?,?,?)", customers)
db.executemany("INSERT INTO orders VALUES (?,?,?,?,?)", orders)
db.executemany("INSERT INTO order_items VALUES (?,?,?,?,?)", items)
db.commit()
print("shop.db built")python3 build_db.pyThree things in that data are deliberate traps. They are the same traps your real database has.
Orders have a status, and four of the eight are not completed. So any revenue question has a right answer and a tempting wrong one. Both are one line of SQL.
One customer, Fen, has never ordered anything. Questions about customers who have not done something need a join, and that join is easy to get subtly wrong.
And order_items has a quantity column, so counting rows is not the same as counting units. Eight mugs were sold across three rows.
Step 2: Write questions you already know the answers to
Create cases.py. Work out each answer by hand or with a query you trust, because these are the yardstick.
QUESTIONS = [
{"q": "How much revenue have we made in total?",
"answer": 41800, "note": "completed orders only"},
{"q": "How many customers do we have?",
"answer": 6, "note": "every row in customers"},
{"q": "How many orders were completed?",
"answer": 5, "note": "status filter"},
{"q": "How many mugs have we sold?",
"answer": 8, "note": "sum of quantity, not a row count"},
{"q": "What is our average completed order value in pence?",
"answer": 8360, "note": "41800 over 5, not over 8"},
{"q": "How many customers have never placed an order?",
"answer": 1, "note": "needs a left join or a not in"},
{"q": "Which country do most of our customers come from?",
"answer": "UK", "note": "group and order"},
{"q": "How many orders were placed in March 2026?",
"answer": 4, "note": "date range on placed_at"},
]The note field is for you rather than the model. When a query returns the wrong thing, the note tells you which trap it fell into. That turns debugging into reading.
Watch out
Without known answers you can only measure whether a query ran, and that number will tell you everything is fine. Execution success is the metric that comes for free, since a query either throws or it does not. It is also the metric that misses every wrong answer. Eight questions with answers you worked out by hand take twenty minutes, and they are the only thing standing between you and a dashboard full of confident wrong answers.
Step 3: Generate the SQL
Create ask.py. It reads the schema straight out of the database, so the prompt never goes stale.
import json
import re
import sqlite3
import urllib.request
from cases import QUESTIONS
PROMPT = """You are writing SQLite queries.
Schema:
{schema}
Question: {question}
Reply with one SQL query and nothing else. No explanation, no markdown fence."""
def read_schema(path="shop.db"):
db = sqlite3.connect(path)
rows = db.execute("SELECT sql FROM sqlite_master WHERE type='table'").fetchall()
return "\n".join(row[0] for row in rows)
def write_sql(question, schema, model="llama3.2:1b"):
body = json.dumps({
"model": model,
"prompt": PROMPT.format(schema=schema, question=question),
"stream": False,
"options": {"num_predict": 150, "temperature": 0},
}).encode()
request = urllib.request.Request(
"http://localhost:11434/api/generate", data=body,
headers={"Content-Type": "application/json"},
)
reply = json.loads(urllib.request.urlopen(request).read())["response"].strip()
fence = "`" * 3
reply = re.sub(rf"^{fence}\w*\n?|{fence}$", "", reply, flags=re.M).strip()
return reply.split(";")[0].strip()
if __name__ == "__main__":
schema = read_schema()
generated = [{**case, "sql": write_sql(case["q"], schema)} for case in QUESTIONS]
json.dump(generated, open("generated.json", "w"), indent=1)
for row in generated:
print(f" {row['q'][:44]:46s} {row['sql'][:60]}")python3 ask.pyTwo details in that code matter more than they look.
The schema comes from sqlite_master rather than a string you maintain. A hand written schema drifts the moment someone adds a column, and the model then writes queries against a database that no longer exists.
And the reply is stripped of markdown fences, then cut at the first semicolon. Models wrap SQL in fences however firmly you ask them not to. A second statement after a semicolon is how one generated query becomes two.
Temperature is 0, which makes the model pick its most likely word each time rather than sampling. So the same question produces the same query twice. Without it you cannot tell whether your change helped or the dice landed differently.
Step 4: Measure two things, not one
Create measure.py. It runs every query against a read only connection and compares the result with the answer you already know.
import json
import sqlite3
GENERATED = json.load(open("generated.json"))
# mode=ro means the driver refuses writes, whatever the SQL says
db = sqlite3.connect("file:shop.db?mode=ro", uri=True)
ran = correct = 0
print(f" {'question':44s}{'runs':>6s}{'expected':>10s}{'got':>10s}")
for row in GENERATED:
try:
result = db.execute(row["sql"]).fetchone()
got = result[0] if result else None
ran += 1
except Exception:
got = "error"
is_right = str(got) == str(row["answer"])
correct += is_right
flag = "" if is_right else " <-- wrong"
runs = "yes" if got != "error" else "NO"
print(f" {row['q'][:42]:44s}{runs:>6s}{str(row['answer']):>10s}{str(got):>10s}{flag}")
total = len(GENERATED)
print(f"\n executed : {ran}/{total} = {ran / total:.0%}")
print(f" correct : {correct}/{total} = {correct / total:.0%}")python3 measure.pyWhat went wrong, and why none of it threw
All eight queries executed. Three were right. So five ran cleanly and returned a wrong result, and each failed for a reason worth recognising.
Revenue forgot the filter. The model wrote SELECT SUM(total_pence) FROM orders and returned 55,300 against a correct 41,800. It totalled cancelled, refunded and pending orders alongside the real ones. Nothing in the schema says revenue means completed orders, so the model had no way to know.
The average made the same mistake twice. It divided the wrong total by the wrong count, giving 6,912 instead of 8,360. Both are averages of something. Only one is the average anybody asked for.
The mug count summed the wrong column. Asked how many mugs were sold, it summed unit_pence rather than quantity, returning 4,500 instead of 8. It added up prices and called the result a count.
The date filter was too wide. Asked about March 2026, the model wrote placed_at LIKE '%2026%', which matches the whole year. It returned 8 rather than 4, and the query is entirely valid.
And one query invented a meaning. Asked how many customers had never ordered, it wrote WHERE signed_up = 'false'. The signed_up column holds a date. There is no false in it, so the query returned 0 and looked like an answer.
That last one is the most instructive. The model saw a column name that sounded about right and used it that way. A column called signed_up could plausibly be a boolean, and the schema alone gives nothing to contradict that.
In the wild
Every wrong query here failed on business meaning rather than on SQL, and a stronger model narrows that gap without closing it. The model knew the syntax perfectly. What it did not know is that your company counts revenue from completed orders, that signed_up is a date, and that quantity is units rather than rows. None of that is in a CREATE TABLE statement, and none of it is guessable. So the fix is not a better model. It is telling the model the things a new colleague would ask in their first week.
Step 5: Give the model what the schema leaves out
The schema says what columns exist. It says nothing about what they mean, and that is where every failure above came from.
Add this to ask.py, beside read_schema.
def rich_schema(db):
"""Schema, plus the things a new colleague would have to ask."""
parts = [read_schema(), "", "Notes:"]
statuses = [row[0] for row in db.execute("SELECT DISTINCT status FROM orders")]
parts.append(f"- orders.status is one of: {', '.join(statuses)}. "
f"Revenue means completed orders only.")
parts.append("- all money columns are integer pence, not pounds.")
parts.append("- placed_at and signed_up are TEXT dates like 2026-03-04.")
parts.append("- order_items.quantity is how many units, so counting rows "
"is not counting units.")
parts.append("")
parts.append("Sample rows from orders:")
for row in db.execute("SELECT * FROM orders LIMIT 3"):
parts.append(f" {row}")
return "\n".join(parts)Then create compare_schema.py to run both versions against the same questions.
import sqlite3
from ask import read_schema, rich_schema, write_sql
from cases import QUESTIONS
db = sqlite3.connect("shop.db")
readonly = sqlite3.connect("file:shop.db?mode=ro", uri=True)
print(f" {'schema given':18s}{'executed':>10s}{'correct':>9s}")
for name, schema in [("bare", read_schema()), ("with notes", rich_schema(db))]:
ran = correct = 0
for case in QUESTIONS:
sql = write_sql(case["q"], schema)
try:
result = readonly.execute(sql).fetchone()
got = result[0] if result else None
ran += 1
if str(got) == str(case["answer"]):
correct += 1
except Exception:
pass
total = len(QUESTIONS)
print(f" {name:18s}{ran}/{total:<8}{correct}/{total:<7} {correct / total:.0%}")python3 compare_schema.pyHere is the honest result, and it is not the one this section was going to report.
The notes look better once and then stop looking better. Two of the runs tied exactly. On eight questions, the difference between these two prompts is inside the noise.
That is worth reporting rather than hiding, because the mistake it prevents is a common one. Run the comparison once, see 38 percent become 62 percent, and you have a finding. Run it six times and you have an argument for a bigger test set.
Everything in the notes is still sound. Telling the model that revenue means completed orders addresses exactly the failure measured earlier, and there is no version of that being harmful. What eight questions cannot do is prove it helped, since a single question flipping either way moves the score by twelve points.
So the practical advice has two halves. Add the notes, since the reasoning behind them is solid and the cost is four lines. And build a test set large enough to measure whether they worked, which means something closer to fifty questions than eight.
One thing did move consistently. The execution rate dropped from 8 of 8 to 6 of 8 with the richer schema, in every run. A longer prompt gave the model more to work with and more to get wrong, which is a real trade and only visible because both numbers were tracked.
In the wild
A single run that shows a big improvement is the easiest way to convince yourself of something that is not true. Eight questions move twelve points when one answer flips, so almost any change looks significant on one run. That applies to prompt tweaks, schema notes and model swaps equally. The fix is not more careful reading of the result. It is more questions, and running the comparison more than once before believing it.
Quick tip
Generate the notes from the database rather than writing them by hand. Distinct values for status columns, a few real rows, and column types can all be queried at startup. A hand maintained description drifts the moment someone adds an order status, and the model then confidently filters on a value that no longer exists. Anything you could look up should be looked up.
Practise the whole pipeline
Querying a database in plain English sits alongside retrieval and evaluation in building something people can use. The AI Agents Fundamentals course covers tokens, embeddings, retrieval, orchestration and MCP through an end to end project.
The part that is not optional
Everything so far is about accuracy. This part is about not destroying your database.
Ask a model to delete something and it writes the SQL without hesitating. Create danger.py and see for yourself.
import sqlite3
from ask import read_schema, write_sql
readonly = sqlite3.connect("file:shop.db?mode=ro", uri=True)
schema = read_schema()
for request in ["Delete all cancelled orders.",
"Clear out the orders table.",
"Set every order status to completed."]:
sql = write_sql(request, schema)
print(f" asked: {request}")
print(f" wrote: {sql}")
try:
readonly.execute(sql)
print(" ran: allowed\n")
except Exception as exc:
print(f" ran: blocked, {exc}\n")python3 danger.pyYou could tell the model in the prompt to write only SELECT statements. Worth doing, and not a control, for the same reason a system prompt is not a guardrail. It is a request that usually gets honoured.
The control is the connection.
db = sqlite3.connect("file:shop.db?mode=ro", uri=True)That one parameter makes the driver refuse every write, whatever the SQL says. A DELETE raises attempt to write a readonly database before touching anything.
Every database has this. In PostgreSQL and MySQL it is a user with SELECT permission and nothing else. That is better still, because it also limits which tables and columns are reachable.
Use the bottom two. The top two are worth having too, since a clear error beats a permission denied. Neither is what keeps your data.
When this is worth building
Text to SQL is genuinely useful in a narrow band. Knowing where that band ends saves a lot of work.
It works well for exploratory questions over a database somebody already understands, where a wrong answer gets noticed quickly and costs nothing. Ask how many orders came from Ireland last month and you will spot a number that is obviously off.
Unattended use is where it breaks down. A generated query feeding a scheduled report, an alert, or a customer facing number is a wrong answer with nobody positioned to notice. The failure in this post is exactly the one that survives that path.
Most useful systems sit in the middle. Generate the SQL, show it beside the result, and let the person see what was actually asked of the database. That costs a little screen space and turns a silent wrong answer into an obvious one.
What you should be able to answer now
Why is execution success a misleading measure? Because valid SQL can answer a different question. Here, all eight queries ran and only three were right.
What causes most wrong answers? Missing business meaning rather than broken syntax. Revenue that includes cancelled orders, a date filter matching a whole year, and a column used for something it does not hold.
What improves it most cheaply? Telling the model what the columns mean, though eight questions could not prove it helped. Add the notes for the reasoning, and build a bigger test set to measure them.
What stops a generated query deleting your data? A read only connection or a database user with only SELECT. Instructions in the prompt are worth adding and are not the thing that stops it.
That gap between a query that runs and one that is right is the whole of this topic. Everything here exists to measure it, narrow it, and make sure the queries still wrong cannot do damage.
Ready to Build the Rest of the Pipeline?
Turning questions into queries is one piece of building something people trust. The AI Agents Fundamentals course covers the surrounding pieces end to end, and the AI Learning Path sequences it alongside retrieval, vector databases and agents. Start by writing eight questions about your own database and working out the answers yourself.
FAQs
Q1: How accurate is an LLM at writing SQL?
More accurate at syntax than at meaning. That gap is the thing to plan around. In this test a small model produced SQL that ran in all eight cases and returned the correct answer in three. So five queries ran cleanly and returned wrong results. Every failure was about business meaning rather than SQL. One totalled revenue across cancelled and refunded orders. One matched a whole year when asked about one month. One summed prices when asked for a count. One used a date column as though it were a true or false flag. A larger model narrows the gap without closing it. The missing information is not in the schema and is not guessable. So measure correctness against answers you already know. Execution success will tell you everything is fine.
Q2: Why does generated SQL run fine and still give the wrong answer?
Because a schema says what columns exist and nothing about what they mean. Your database knows orders has a status column. It does not record that your company counts revenue from completed orders only. Or that money is stored in pence. Or that signed_up holds a date rather than a flag. The model fills those gaps with reasonable guesses, and a reasonable guess produces valid SQL. Valid SQL returns rows, and rows look like an answer. Nothing in the output marks the difference between a query that answered your question and one that answered a similar one. That makes this failure more dangerous than a syntax error, which at least announces itself. And it means the fix is supplying the meaning, not reaching for a better model.
Q3: What do I need to build this?
Python, a model, and a database. SQLite covers the database and ships with Python, so nothing to install for that half. A small local model through Ollama covers the model, with no key or quota. A small one suits learning, because it fails the same ways a larger one does, only more often. Everything in this post uses the standard library, so sqlite3 and urllib are all you need. The part that takes real effort is not code. It is eight questions about your own data, with answers you worked out by hand. That is what turns a demo into something you can measure. It takes about twenty minutes, and it is the only thing that catches a wrong result.
Q4: How do I stop the model deleting my data?
Use a read only connection. Better still, a database user with SELECT permission and nothing else. In SQLite that is sqlite3.connect("file:shop.db?mode=ro", uri=True). Any DELETE, DROP or UPDATE then raises before touching anything. In PostgreSQL and MySQL it is a dedicated user. Stronger still, since it also limits which tables and columns are reachable at all. Telling the model to write only SELECT statements is worth doing and is not a control, for the same reason a system prompt is not a guardrail. It is a request that usually gets honoured. Ask plainly to delete cancelled orders and a model writes the DELETE without hesitating. Scanning generated SQL for dangerous keywords helps and is not enough either, since comments and casing get past simple checks.
Q5: How do I improve the accuracy of generated queries?
Give the model the context a new colleague would ask for in their first week, and then measure properly enough to know whether it worked. Measured here across six runs, four lines of notes and three sample rows did not reliably change correctness. The bare schema scored 3 or 4 of 8 and the richer one scored 4 or 5, so the ranges overlap and two runs tied exactly. The notes that mattered most listed what values a status column actually holds. They stated that money is in pence, that the date columns are text, and that a quantity column means counting rows is not counting units. Generate these from the database rather than writing them by hand. Distinct values and sample rows can both be queried at startup, and a hand maintained description drifts as soon as somebody adds a status. Anything you could look up should be looked up.
Q6: Should I let users query my production database in English?
Not unattended, and with care even then. It works well for exploratory questions over data somebody already understands, where a wrong answer gets noticed quickly and costs nothing. Ask how many orders came from Ireland last month and you will spot a figure that is obviously off. It works badly anywhere nobody is watching. A scheduled report, an alert threshold, or a number shown to a customer. The failure measured in this post is a plausible wrong answer, and it survives exactly that path. The useful middle ground is showing the generated SQL next to the result, so the person sees what was actually asked of the database. That costs a little screen space and turns a silent wrong answer into an obvious one. Always run it read only.
Discussion