Text-to-SQL

An LLM turns a question and the relevant schema into SQL that the database runs. Schema linking, voting by execution result and error-driven repair raise execution accuracy; a read-only, bounded connection keeps it safe.

8 min read852 wordscore0 viewsupdated 2026-10-21

Definition

Text-to-SQL maps a natural-language question and a database schema to an executable SQL query, whose result answers the question. It is a semantic-parsing task with standard benchmarks: WikiSQL (Zhong, Xiong and Socher, 2017), Spider (Yu et al., 2018) and BIRD (Li et al., 2023). It is scored mainly by execution accuracy: does the predicted query return the same result as the gold query?

Business users have questions and the answers sit in a warehouse. Text-to-SQL lets them ask in plain language without writing joins. The key design choice is that the model writes the query and the database computes the numbers. The model never does the arithmetic, so totals and counts are exact once the query is right.

It is a specialised form of Code generation with a big advantage: the output can be run and checked at once. It has two big risks. A query can run fine and still answer the wrong question, and a model with write access to a production database is a security problem.

The pipeline

question → SQL → rows → answer, with the database as the source of truththe model writes the query; the database computes the numbers1 questionRevenue per storein 2023, top 3?2 schema linking412 tables → top 3orders 0.81order_items 0.77stores 0.743 promptdialect: PostgreSQLCREATE TABLE × 33 sample rows eachrules + question4 LLMtemperature 0,or n samplesvoted by result5 guardone statementSELECT onlyLIMIT ≤ 1000timeout 5 s6 executeread-only roleon a replicareturns rowsor an error7 resultstore revenueLeeds 412,880York 398,115Bath 305,4028 answerLeeds led with412,880; SQLshown for auditSQLerror → repairgenerated SQL (step 4)SELECT s.name, SUM(oi.qty * oi.unit_price) AS revenueFROM orders oJOIN stores s ON s.id = o.store_idJOIN order_items oi ON oi.order_id = o.idWHERE o.ordered_at >= '2023-01-01' AND o.ordered_at < '2024-01-01'GROUP BY s.name ORDER BY revenue DESC LIMIT 3;repair turn (if needed)first try used o.storeDB: column o.storedoes not exist→ error text + schemasent back; modelregenerates, ≤ 2 tries
fig.One request end to end. Schema linking picks 3 of 412 tables; the prompt carries their CREATE TABLE statements, a few sample rows and the SQL dialect. The guard admits one bounded SELECT, the query runs under a read-only role on a replica, and an error message (here: column o.store does not exist) goes back to the model for repair. The answer quotes the rows and shows the SQL.
  1. 1Schema linking. Retrieve the tables and columns the question needs, by embedding table and column descriptions or by asking the model to choose. Real warehouses have hundreds of tables; their full DDL does not fit in a prompt, and irrelevant tables invite wrong joins.
  2. 2Prompt. Send the linked tables as CREATE TABLE statements with column comments, foreign keys and 2–3 sample rows, the dialect (PostgreSQL, BigQuery, Snowflake…), business definitions ("revenue excludes refunds") and a few solved examples. Rajkumar et al. (2022) found CREATE TABLE statements plus a few sample rows an effective prompt for Codex.
  3. 3Generate. One query at temperature 0, or nn samples for voting. Ask for SQL only, or a JSON object with sql and explanation (see Structured output (JSON mode)).
  4. 4Guard. Parse the query; allow exactly one SELECT; add a LIMIT; set a statement timeout; reject system tables.
  5. 5Execute under a read-only role, ideally on a replica. On an error, send the error text back to the model and let it regenerate (1–2 rounds).
  6. 6Answer. Summarise the rows in words, and show the SQL and the result table so the user can check them.

Scoring and voting

EX=1N∑i=1N1[ RD(y^i)=RD(yi) ]\text{EX}=\frac{1}{N}\sum_{i=1}^{N}\mathbf{1}\big[\,R_D(\hat y_i)=R_D(y_i)\,\big]
y^i,  yi\hat y_i,\;y_i
predicted and gold SQL for question i
RD(⋅)R_D(\cdot)
the rows a query returns on database D, compared as a multiset unless the question asks for an order
NN
number of evaluation questions

Execution accuracy accepts any correct rewrite (a subquery instead of a join, BETWEEN instead of two comparisons), which exact string match would reject. It also has false positives: a query missing a filter can return the right rows by luck on a small database. Test-suite accuracy (Zhong, Yu and Klein, 2020) runs both queries on several databases built to tell them apart.

r^=arg⁡max⁡r∑j=1n1[RD(qj)=r],qj ran without error\hat r=\arg\max_{r}\sum_{j=1}^{n}\mathbf{1}\big[R_D(q_j)=r\big],\qquad q_j\ \text{ran without error}
q1,…,qnq_1,\dots,q_n
n candidate queries sampled for the same question
r^\hat r
the result returned by the most candidates; its query is kept

This is Self-consistency with execution results as the answers. Different correct queries tend to agree on the rows, while wrong ones tend to scatter, so the most common result is more often right than any single sample. Errors get no vote, so voting also absorbs most syntax failures.

Benchmarks and methods

WikiSQL (2017)
80,654 questions with SQL over 24,241 Wikipedia tables; single table, no joins
Spider (2018)
10,181 questions, 5,693 unique complex queries over 200 databases in 138 domains; test databases are unseen in training, so it measures cross-domain generalisation
BIRD (2023)
12,751 question–SQL pairs over 95 databases totalling 33.4 GB; adds external-knowledge hints, dirty values and a Valid Efficiency Score for query speed
Spider metrics
exact set match (compares query components, ignoring order) and execution accuracy
DIN-SQL (2023)
Pourreza and Rafiei: decompose into schema linking, difficulty classification, generation and self-correction prompts

Benchmark scores overstate what you get in production. Real warehouses have hundreds of cryptically named tables, metrics defined by business rules, and ambiguous questions. Spider 2.0 (Lei et al., 2024) builds enterprise-scale workflows of this kind, and models score far lower on it than on Spider.

Simulating the pipeline

top-4 tables · 1 sample voted by result · 0 repair rounds300 synthetic questions needing 1–3 of 12 tables · simulated model and retrieverexample: needs returns + categoriesreturns54employees104suppliers70orders74payments66categories38customers86products92shipments80promotions60stores58order_items62toka needed table is cut offfinal answer per question (300)correct result185valid SQL, wrong result42needed table not linked51every sample errored22execution accuracy61.7%schema-linking recall83.0%prompt tokens / call461LLM calls / question1.00execution accuracy vstables in the promptsmall k misses tables;large k adds distractorsand prompt tokens— EX - - linking recall0.00.51.0123456789101112k (tables)
repair on error
fig.300 synthetic questions, each needing 1–3 of 12 tables, with a simulated retriever and model. Default (top-4 tables, 1 sample, no repair): schema-linking recall 83.0%, execution accuracy 61.7% at 461 prompt tokens per call; 51 questions fail because a needed table was never shown. One repair round lifts EX to 65.0% at 1.16 calls per question. Five voted samples plus one repair give 80.0%. Raising k to 7 gives 89.7%; all 12 tables give 87.7% at 1,024 tokens per call, since irrelevant tables add wrong joins.

The curve shows the schema-linking trade-off. Too few tables and the right one is missing, which no amount of sampling fixes. Too many and the model has more chances to join the wrong table, while every call costs more tokens. With a 412-table warehouse at about 75 tokens of DDL per table, the full schema is 30,900 tokens per call, so linking is not optional.

Worked example: voting by result

CandidateDifferenceResultVote
1date range with >= and <Bath 40, Leeds 200, York 150A
2strftime('%Y', …) = '2023'Bath 40, Leeds 200, York 150A
3forgot the 2023 filterBath 340, Leeds 200, York 150B
4joins on o.storeerror: no such column: o.storenone
5DELETE FROM orderserror: not authorizednone
Five sampled queries for "Revenue per store in 2023?" on a toy database with 5 orders (run with the code below)

Result A gets 2 votes and B gets 1, so A is returned. Candidates 1 and 2 are different SQL with the same rows, which exact match would treat as different. Candidate 3 looks plausible and runs; only the vote, or a test on a 2022 order, exposes it. The guard stopped candidate 5 before it could delete anything.

python
1import sqlite3
2from collections import Counter
3
4db = sqlite3.connect(":memory:")
5db.executescript("""
6CREATE TABLE stores (id INTEGER PRIMARY KEY, name TEXT);
7CREATE TABLE orders (id INTEGER PRIMARY KEY, store_id INTEGER, ordered_at TEXT, total REAL);
8INSERT INTO stores VALUES (1,'Leeds'),(2,'York'),(3,'Bath');
9INSERT INTO orders VALUES (1,1,'2023-02-01',120),(2,1,'2023-07-09',80),(3,2,'2023-03-15',150),
10 (4,3,'2022-12-30',300),(5,3,'2023-05-05',40);
11""")
12
13def read_only(action, *args): # deny everything except reads
14 ok = (sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ, sqlite3.SQLITE_FUNCTION)
15 return sqlite3.SQLITE_OK if action in ok else sqlite3.SQLITE_DENY
16db.set_authorizer(read_only)
17
18def run(sql, limit=1000):
19 try:
20 rows = db.execute(sql).fetchmany(limit) # execute() refuses multiple statements
21 return tuple(sorted(map(tuple, rows))) # order-insensitive result key
22 except sqlite3.Error as e:
23 return "error: " + str(e) # fed back to the model for repair
24
25candidates = [ # n = 5 samples at temperature 0.7
26 "SELECT s.name, SUM(o.total) FROM orders o JOIN stores s ON s.id = o.store_id "
27 "WHERE o.ordered_at >= '2023-01-01' AND o.ordered_at < '2024-01-01' GROUP BY s.name",
28 "SELECT s.name, SUM(o.total) FROM orders o JOIN stores s ON s.id = o.store_id "
29 "WHERE strftime('%Y', o.ordered_at) = '2023' GROUP BY s.name",
30 "SELECT s.name, SUM(o.total) FROM orders o JOIN stores s ON s.id = o.store_id GROUP BY s.name",
31 "SELECT s.name, SUM(o.total) FROM orders o JOIN stores s ON s.id = o.store "
32 "WHERE o.ordered_at LIKE '2023%' GROUP BY s.name",
33 "DELETE FROM orders",
34]
35results = [run(q) for q in candidates]
36votes = Counter(r for r in results if not isinstance(r, str))
37print(votes.most_common(1))
38# [((('Bath', 40.0), ('Leeds', 200.0), ('York', 150.0)), 2)]
Read-only execution with sqlite3's authorizer, then voting by result (standard library only; runs as shown; the candidates stand in for sampled LLM output)

Use when

  • +Exact numbers: the database does the arithmetic
  • +Every answer is auditable: show the SQL and the rows
  • +Voting and repair use the database as a free verifier

Avoid when

  • −Wrong-but-valid queries look as convincing as right ones
  • −Schema linking caps accuracy: a missing table cannot be recovered
  • −n samples cost n times the tokens and n queries on the database

Check yourself

A predicted query omits a WHERE filter, yet returns exactly the gold rows on the evaluation database. Which metric counts it as correct, and what catches it?

Check yourself

In the simulation, moving from top-4 to all 12 tables raises schema-linking recall from 83% to 100%, but single-sample EX only rises from 61.7% to 64.0%. Why so little?

Remember

  • ✓The model writes SQL; the database computes the answer, so numbers are exact once the query is right.
  • ✓Pipeline: schema linking → DDL + sample rows + dialect + business rules → generate → guard → execute read-only → repair on error → answer with SQL shown.
  • ✓Execution accuracy 1N∑1[RD(y^)=RD(y)]\frac1N\sum\mathbf 1[R_D(\hat y)=R_D(y)] accepts rewrites but admits lucky matches; test suites reduce them.
  • ✓Voting by execution result is self-consistency with the database as judge; error messages drive repair.
  • ✓Security comes from a least-privilege, read-only, time- and row-limited connection, not from the prompt.
← back to Generative AI roadmap