Malhar Ujawane

Blog

Explainer·(updated October 31, 2026)

What is text-to-SQL? Natural language queries with LLMs

Text-to-SQL uses an LLM to turn a plain-English question into a SQL query for your database. How it works, the benchmarks, and the guardrails, with Python.

What is text-to-SQL? Natural language queries with LLMs

Text-to-SQL is the task of turning a natural-language question, such as “what was October revenue from Surat customers?”, into a SQL query that runs against a real database and returns the answer. With LLMs, it works by putting the database schema (and often example rows and past queries) into the prompt, asking the model to write the SQL, then validating and executing that query before showing the result. It is retrieval-augmented generation where the knowledge source is a database and the retrieved context is the schema.

How does text-to-SQL work with an LLM?

A production pipeline has more steps than “prompt the model with the question”:

  1. Schema retrieval. Find the tables and columns relevant to the question. For a database with five tables you paste them all. For a warehouse with thousands, this is a retrieval problem in its own right, the same one I cover in what is RAG, with table and column descriptions as the documents.
  2. Context. Add what the schema alone cannot say: column meanings, enum values, join paths, business definitions (“revenue excludes refunds”), and a few verified question-to-SQL examples.
  3. Generation. The model writes the SQL in the right dialect. Structured output helps keep it to a parseable query plus nothing else.
  4. Validation. Parse or compile the query without running it, check that it only reads, and check it against an allow-list of tables.
  5. Execution with a read-only role, row limits and a timeout.
  6. Answer. Return the rows, the SQL and, if needed, a summary. Showing the SQL is what makes the answer auditable.

Steps 4 and 5 are where I spend the design time, because a wrong SQL query does not throw an error. It returns a confident, precise, wrong number.

How good are LLMs at text-to-SQL?

It depends heavily on how realistic the benchmark is, and the benchmarks tell that story well.

Spider (Yu et al., 24 September 2018) set the standard for cross-domain text-to-SQL: 10,181 questions and 5,693 unique SQL queries over 200 databases covering 138 domains, written by 11 college students. Models are tested on databases they never saw in training.

BIRD (Li et al., 4 May 2023) made it messier and bigger: 12,751 question-SQL pairs over 95 databases totaling 33.4 GB across 37 professional domains, with dirty values and the need for outside knowledge. At release, ChatGPT reached 40.08 percent execution accuracy against 92.96 percent for humans.

Spider 2.0 (Lei et al., 12 November 2024) moved to enterprise workflows: 632 tasks on real databases that often have over 1,000 columns, stored in systems like BigQuery and Snowflake, with solutions that can need multiple queries in different dialects and run past 100 lines of SQL. An o1-preview code agent solved 21.3 percent of them, against 91.2 percent on Spider 1.0 and 73.0 percent on BIRD with the same setup.

That spread is the main lesson. Clean, small schemas are close to solved. Real warehouses, with ambiguous column names, undocumented business rules and thousands of fields, are not. Models have improved since those numbers were published, but the gap between toy schemas and enterprise ones is a property of the data, and it has not gone away.

What does a safe text-to-SQL pipeline look like in code?

The model is the easy part to swap. The guardrails are what I’d build first, and SQLite in Python’s standard library is enough to show them. This script builds a two-table shop database, opens it read-only, adds an authorizer that denies anything except reads, and then runs four candidate queries a model might return.

# text_to_sql.py: the guardrails around an LLM's SQL, using only sqlite3. Python 3, stdlib only.
# The candidate queries below are what a model might return; no model is called here.
import os, sqlite3, tempfile

path = os.path.join(tempfile.mkdtemp(), "shop.db")
setup = sqlite3.connect(path)
setup.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
                     total_inr INTEGER, placed_on TEXT);
INSERT INTO customers VALUES (1,'Asha','Surat'),(2,'Ravi','Pune'),(3,'Meera','Surat');
INSERT INTO orders VALUES (1,1,1200,'2026-09-02'),(2,1,800,'2026-10-11'),
                          (3,2,2500,'2026-10-15'),(4,3,400,'2026-10-20');
""")
setup.commit(); setup.close()

# 1. The schema the model sees, pulled from the database itself.
db = sqlite3.connect(f"file:{path}?mode=ro", uri=True)          # read-only connection
schema = "\n".join(r[0] for r in db.execute("SELECT sql FROM sqlite_master WHERE type='table'"))
print(f"schema prompt: {len(schema)} characters, {schema.count('CREATE TABLE')} tables\n")

# 2. A second wall: an authorizer that only allows reads.
ALLOWED = {sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ, sqlite3.SQLITE_FUNCTION}
db.set_authorizer(lambda action, *_: sqlite3.SQLITE_OK if action in ALLOWED else sqlite3.SQLITE_DENY)

question = "Total October 2026 revenue from customers in Surat?"
gold = """SELECT SUM(o.total_inr) FROM orders o JOIN customers c ON c.id = o.customer_id
          WHERE c.city = 'Surat' AND o.placed_on BETWEEN '2026-10-01' AND '2026-10-31'"""
expected = db.execute(gold).fetchall()

candidates = {
    "correct":        gold,
    "wrong filter":   "SELECT SUM(o.total_inr) FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.city = 'Surat'",
    "made-up column": "SELECT SUM(amount) FROM orders WHERE city = 'Surat'",
    "destructive":    "DELETE FROM orders",
}
print(question, "->", expected)
for label, sql in candidates.items():
    try:
        db.execute("EXPLAIN " + sql)                        # 3. compile without running
        got = db.execute(sql).fetchall()                    # 4. run, then compare results
        verdict = "match" if got == expected else f"runs, but returns {got}"
    except sqlite3.Error as e:
        verdict = f"rejected: {e}"
    print(f"  {label:<15} {verdict}")

Output (Python 3.13.11):

schema prompt: 216 characters, 2 tables

Total October 2026 revenue from customers in Surat? -> [(1200,)]
  correct         match
  wrong filter    runs, but returns [(2400,)]
  made-up column  rejected: no such column: amount
  destructive     rejected: not authorized

Read the four lines from the bottom up. The DELETE never gets near the data: the authorizer denies it, and the mode=ro connection would have refused the write anyway. The hallucinated amount column fails at compile time, which is the cheap case, because you can feed the error back to the model and let it retry. The dangerous line is “wrong filter”. It is valid SQL, it runs, and it returns 2,400 instead of 1,200 because it dropped the October condition. No guardrail catches that. Only an evaluation against a known answer does.

Comparing result sets is also how the benchmarks score: BIRD reports execution accuracy, which checks whether the predicted query returns the same result as the gold one, rather than whether the SQL text matches.

Where does text-to-SQL break?

Silent semantic errors. As above: a missing filter, an inner join that should be a left join, a COUNT that double-counts after a join. These look right and run fine.

Business definitions. “Active customer”, “revenue” and “churn” have definitions that live in people’s heads or in a BI tool, not in the schema. The model will invent a reasonable definition, which is worse than an error.

Schema scale. Spider 2.0’s databases often exceed 1,000 columns. You cannot paste that into every prompt, and choosing the right ten tables is where many enterprise failures start.

Security. A user who can type questions can try to make the model write queries over data they should not see. Read-only access is necessary but not enough; the database role the query runs under must carry the same row and column permissions as the user asking. Treat the question itself as untrusted input, as I’d treat any text in prompt injection scenarios.

Cost and load. A model can write a correct query that scans a 10 TB table. Use EXPLAIN plans, row limits, timeouts and query cost caps on warehouses that bill per byte scanned.

What I would do

From a data-platform seat, the line I would circle is the semantic layer. On any data platform, the hard part of an analytics question is rarely the SQL syntax; it is knowing which table is the source of truth and what a metric means. Text-to-SQL inherits all of that. So I’d start by writing down definitions for the 20 metrics people ask about most, with a verified SQL query for each, and put those in the context as examples.

Then:

  1. Run every query under a read-only role scoped to the asking user’s permissions, with a row limit and a timeout.
  2. Compile before executing and feed errors back to the model for one or two retries.
  3. Always show the SQL next to the answer, so an analyst can check the filter that matters.
  4. Build an eval set of real questions with verified answers and score execution accuracy on every prompt or model change, the same discipline as in how to evaluate RAG.
  5. For multi-step analysis across several tables or systems, put the SQL tool inside an agentic RAG loop so the model can inspect intermediate results before writing the final query.

FAQ

What is text-to-SQL in AI?

It is the task of converting a natural-language question into an executable SQL query. LLM-based systems do it by giving the model the database schema and examples, then validating and running the SQL it writes.

How accurate is text-to-SQL?

It depends on the data. Agents score well on clean benchmarks like Spider (91.2 percent for an o1-preview agent) but far lower on enterprise ones: the same setup solved 21.3 percent of Spider 2.0 tasks.

Is text-to-SQL safe to run on production databases?

Only with guardrails: a read-only connection or role, permissions matched to the user, query validation before execution, row limits, timeouts and cost caps. Never run model-written SQL with write access.

What is execution accuracy in text-to-SQL?

A metric that counts a predicted query as correct if it returns the same result as the reference query on the database. It is used by BIRD and is more forgiving than exact string match, since many different SQL queries produce the same answer.

Sources

Get new posts in your inbox

© 2026 Malhar Ujawane. Built with Astro.