PHASE 05 · TEXT-TO-SQL
Goal: Let users ask structured questions in plain English.
Text-to-SQL is the pattern where the LLM writes SQL from a natural-language question. It's the highest-leverage pattern for structured data — most internal 'BI tools' are just text-to-SQL with a chart on top.
The hard parts aren't writing the first prompt. They're schema grounding (telling the model which tables and columns exist), validation (making sure the generated SQL actually runs), and safety (read-only, no dropping tables, capped row counts).
CREATE TABLE statements, plus 2–3 few-shot Q/SQL examples and explicit constraints (read-only, LIMIT 100).text_to_sql.py — A schema-aware text-to-SQL tool with validation and a one-shot repair loop.
Concepts unlocked: schema injection · query validation · repair loop · structured outputs
Your Phase 4 agent can already search documents, browse the web, and do math. But think about where most of a company's answers actually live. At Zentara Logistics — our made-up shipping company — the answer to "how many customers signed up in May?" isn't written in any document. It sits in a database: neat rows and tables of customers, orders, and payments. Your Phase 2 RAG is great with prose, but this answer isn't in any paragraph — it has to be counted.
Databases already have a precise language for exactly these questions: SQL. The only catch is that most people can't write it. So here's the idea — let the model write the SQL for you, from plain English.
Don't worry about reading that query — here it is, word for word: SELECT COUNT(*) means "count the rows", FROM customers means "in the customers table", and WHERE month = 'May' means "but only the rows where the month is May." One line of SQL, one plain-English sentence: count the customers who signed up in May. And you'll never have to write these yourself — writing them is the model's whole job in this phase.
What text-to-SQL is. Text-to-SQL: the model turns a plain-English question into a database query. Most internal BI tools (business intelligence — the dashboard apps full of charts that your finance team stares at all day) are exactly this: text-to-SQL with a chart drawn on top.
The flow is short. You give the model three things: the question, the schema — the database's table of contents: which tables exist and what columns each one has — and a few examples: two or three question→SQL pairs pasted into the prompt, so the model can see the pattern it should copy. The model writes a SQL query. Your code runs that query, hands the rows back, and the model turns them into a plain answer.
This is Phase 4's loop wearing a new hat. Look at who does what: the model asks (by writing SQL as text), the harness runs the query for real, and the result comes back as a new message. Same pattern as the calculator in Phase 4 — the LLM never touches the database itself. Text-to-SQL is just one more tool in your agent's toolbox.
Let's watch one real question travel the whole pipeline. A Zentara manager asks: "Which three customers shipped the most packages in June?"
Notice who did what. The model wrote text twice — once as SQL, once as the friendly answer. The harness did all the actual running. And the number 1,204 came from the database, not from the model's memory — that's why it's exact instead of a plausible guess.
That end-to-end run looked smooth. Writing the first query is easy — making it reliable comes down to three things:
CREATE TABLE lines) into the prompt so it knows exactly what exists.SELECT, and add a LIMIT so one query can't pull a million rows."Schema" has come up a few times now — here's what one actually looks like, so it stops being abstract. This is Zentara's orders table:
CREATE TABLE orders (
id INTEGER, -- one row per shipment
customer_name TEXT, -- who sent it
ship_month TEXT, -- e.g. 'June'
weight_kg REAL -- package weight
);That's the whole trick: a table's name, its column names, and what type each column holds. Paste a few of these into the prompt and the model knows precisely what it's allowed to query — no more guessing whether the table is called orders or shipments.
Treat generated SQL as untrusted. A read-only connection, a row limit, and a SELECT-only rule are not optional — they're how you stop an LLM from accidentally wiping a table. Never run generated SQL with write access.
LIMIT has a quiet failure mode. A row limit protects you, but it's blunt: if a legitimate answer needed 500 rows and your cap is 100, the result gets silently truncated — the query "works," the answer looks fine, and nobody notices it's incomplete. A good build flags it whenever the row count comes back exactly at the limit, so you know to look closer.
What happens when the SQL doesn't run? You don't give up — you tell the model what broke. Send the database's error message back and ask it to fix the query. One retry catches most mistakes.
Concretely: you ask "how many users signed up?" and the model writes SELECT COUNT(*) FROM user. The database throws no such table: user — the table is actually called users. You send that exact error back with the query, and the model returns SELECT COUNT(*) FROM users. It runs. Fixed.
If this rhythm feels familiar, it should — it's Phase 4's agent loop again: the model asks, the harness runs, the result comes back as a new message. The only twist is that here the "result" is sometimes an error message — and that error is exactly the feedback the model needs to repair its own query. This "try → if it fails, feed the error back → try again" pattern is a repair loop, and you'll see it all over agent code. Cap it (one or two retries) so a truly broken query can't loop forever.
And when the cap runs out? Stop, and show the error honestly: "I couldn't answer this — the query failed with: no such column: signup_date." That feels like failure, but it's the right move. The one thing a data tool must never do is invent a plausible-looking number after the database refused to give a real one.
Fail honest, not confident. A capped repair loop has exactly two exits: a query that ran, or an error shown to the user as-is. There is no third exit where the model "just answers anyway" — that would turn your exact tool back into a guessing machine.
Build text_to_sql.py against a small sample database, then wrap it as a tool and plug it into the agent.py from Phase 4 — now your agent can answer from documents and from data.
Read what it builds — the prompt carries the schema, and a small loop handles errors safely, with an honest exit when the one allowed repair also fails:
# text_to_sql.py — question in, answer from your database out
import sqlite3
from anthropic import Anthropic
client = Anthropic()
SCHEMA = open("schema.sql").read() # your CREATE TABLE statements
def to_sql(question):
prompt = f"{SCHEMA}\n\nWrite ONE read-only SQL query (SELECT only) for:\n{question}"
return client.messages.create(
model="claude-sonnet-4-6", max_tokens=400,
messages=[{"role": "user", "content": prompt}]).content[0].text.strip()
def ask(question, db="chinook.db"):
sql = to_sql(question)
if not sql.lower().startswith("select"):
return "Refused: only read-only queries are allowed."
con = sqlite3.connect(f"file:{db}?mode=ro", uri=True) # read-only connection
try:
return con.execute(sql + " LIMIT 100").fetchall()
except sqlite3.Error as e:
fixed = to_sql(f"{question}\n\nThat query failed with: {e}. Fix it.") # repair, once
try:
return con.execute(fixed + " LIMIT 100").fetchall()
except sqlite3.Error as e2: # cap reached — fail honest
return f"Query failed after one repair attempt: {e2}" # never invent data
Deliverable — text_to_sql.py. A schema-aware tool that writes SQL, runs it safely (read-only, limited), repairs itself once on error — and reports the error honestly if the repair fails too. Wired into your agent, it answers questions straight from your data.