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
- 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.
- 2Prompt. Send the linked tables as
CREATE TABLEstatements 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) foundCREATE TABLEstatements plus a few sample rows an effective prompt for Codex. - 3Generate. One query at temperature 0, or samples for voting. Ask for SQL only, or a JSON object with
sqlandexplanation(see Structured output (JSON mode)). - 4Guard. Parse the query; allow exactly one
SELECT; add aLIMIT; set a statement timeout; reject system tables. - 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).
- 6Answer. Summarise the rows in words, and show the SQL and the result table so the user can check them.
Scoring and voting
- predicted and gold SQL for question i
- the rows a query returns on database D, compared as a multiset unless the question asks for an order
- 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.
- n candidate queries sampled for the same question
- 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
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
| Candidate | Difference | Result | Vote |
|---|---|---|---|
| 1 | date range with >= and < | Bath 40, Leeds 200, York 150 | A |
| 2 | strftime('%Y', …) = '2023' | Bath 40, Leeds 200, York 150 | A |
| 3 | forgot the 2023 filter | Bath 340, Leeds 200, York 150 | B |
| 4 | joins on o.store | error: no such column: o.store | none |
| 5 | DELETE FROM orders | error: not authorized | none |
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.
1import sqlite32from collections import Counter34db = 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""")1213def read_only(action, *args): # deny everything except reads14 ok = (sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ, sqlite3.SQLITE_FUNCTION)15 return sqlite3.SQLITE_OK if action in ok else sqlite3.SQLITE_DENY16db.set_authorizer(read_only)1718def run(sql, limit=1000):19 try:20 rows = db.execute(sql).fetchmany(limit) # execute() refuses multiple statements21 return tuple(sorted(map(tuple, rows))) # order-insensitive result key22 except sqlite3.Error as e:23 return "error: " + str(e) # fed back to the model for repair2425candidates = [ # n = 5 samples at temperature 0.726 "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)]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 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.