# 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.

Source: https://www.mlfoundry.in/learn/text-to-sql  
Section: Generative AI › Applications  
Level: core · 8 min read · updated 2026-10-21

**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

> Figure: 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. **Schema 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. **Prompt.** 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. **Generate.** One query at temperature 0, or $n$ samples for voting. Ask for SQL only, or a JSON object with `sql` and `explanation` (see **Structured output (JSON mode)**).
4. **Guard.** Parse the query; allow exactly one `SELECT`; add a `LIMIT`; set a statement timeout; reject system tables.
5. **Execute** 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. **Answer.** Summarise the rows in words, and show the SQL and the result table so the user can check them.

## Scoring and voting

$$
\text{EX}=\frac{1}{N}\sum_{i=1}^{N}\mathbf{1}\big[\,R_D(\hat y_i)=R_D(y_i)\,\big]
$$

- $\hat y_i,\;y_i$: predicted and gold SQL for question i
- $R_D(\cdot)$: the rows a query returns on database D, compared as a multiset unless the question asks for an order
- $N$: 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.

$$
\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}
$$

- $q_1,\dots,q_n$: n candidate queries sampled for the same question
- $\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

> Figure: 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

Five sampled queries for "Revenue per store in 2023?" on a toy database with 5 orders (run with the code below)

| 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.

```python
import sqlite3
from collections import Counter

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE stores (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, store_id INTEGER, ordered_at TEXT, total REAL);
INSERT INTO stores VALUES (1,'Leeds'),(2,'York'),(3,'Bath');
INSERT INTO orders VALUES (1,1,'2023-02-01',120),(2,1,'2023-07-09',80),(3,2,'2023-03-15',150),
                          (4,3,'2022-12-30',300),(5,3,'2023-05-05',40);
""")

def read_only(action, *args):                     # deny everything except reads
    ok = (sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ, sqlite3.SQLITE_FUNCTION)
    return sqlite3.SQLITE_OK if action in ok else sqlite3.SQLITE_DENY
db.set_authorizer(read_only)

def run(sql, limit=1000):
    try:
        rows = db.execute(sql).fetchmany(limit)   # execute() refuses multiple statements
        return tuple(sorted(map(tuple, rows)))    # order-insensitive result key
    except sqlite3.Error as e:
        return "error: " + str(e)                 # fed back to the model for repair

candidates = [                                    # n = 5 samples at temperature 0.7
    "SELECT s.name, SUM(o.total) FROM orders o JOIN stores s ON s.id = o.store_id "
    "WHERE o.ordered_at >= '2023-01-01' AND o.ordered_at < '2024-01-01' GROUP BY s.name",
    "SELECT s.name, SUM(o.total) FROM orders o JOIN stores s ON s.id = o.store_id "
    "WHERE strftime('%Y', o.ordered_at) = '2023' GROUP BY s.name",
    "SELECT s.name, SUM(o.total) FROM orders o JOIN stores s ON s.id = o.store_id GROUP BY s.name",
    "SELECT s.name, SUM(o.total) FROM orders o JOIN stores s ON s.id = o.store "
    "WHERE o.ordered_at LIKE '2023%' GROUP BY s.name",
    "DELETE FROM orders",
]
results = [run(q) for q in candidates]
votes = Counter(r for r in results if not isinstance(r, str))
print(votes.most_common(1))
# [((('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)

> **The database is the security boundary, not the prompt:** "Only write SELECT statements" in a system prompt is not a control. Questions, and text stored in the database itself, can carry **Prompt injection**. Connect with a role that has `SELECT` on an allow-listed set of views and nothing else, use row-level security for per-user data, and set statement timeouts and row limits so one query cannot scan the warehouse. The application-level guard is a second layer (see **Guardrails**).

> **Put the business logic in the schema:** Most production errors are not syntax but meaning: which date column defines "2023", whether revenue includes refunds, which status counts as an active customer. Curated views with clear names and comments, or a semantic layer that defines metrics once, remove these choices from the model. A clarifying question beats a confident guess when the question is ambiguous.

**Strengths**

- 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

**Limitations**

- 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

**Question:** 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?

- A. Exact set match counts it; execution accuracy catches it
- B. Execution accuracy counts it; test-suite accuracy on several distinguishing databases catches it
- C. Both metrics reject it
- D. BLEU against the gold SQL catches it

**Answer:** B. Execution accuracy counts it; test-suite accuracy on several distinguishing databases catches it — Execution accuracy compares results on one database, so a filter that happens to remove nothing there goes unnoticed. Exact set match would reject the missing clause. Test-suite accuracy runs both queries on databases built so that semantically different queries return different rows.

**Question:** 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?

- A. The model ignores tables after the fourth
- B. Irrelevant tables add wrong-join errors and the cost per call more than doubles, offsetting the recovered questions
- C. Execution accuracy cannot exceed 65%
- D. Voting is disabled at k = 12

**Answer:** B. Irrelevant tables add wrong-join errors and the cost per call more than doubles, offsetting the recovered questions — The 51 questions recovered by full recall are offset by more valid-but-wrong queries (42 at k = 4, 78 at k = 12) because every irrelevant table is another chance to join wrongly. Prompt tokens per call go from 461 to 1,024.

**Key points**

- 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 $\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.

---

From MLFoundry (https://www.mlfoundry.in). The web page adds interactive figures and diagrams.
