Text-to-SQL is the use of a language model to turn a question asked in plain language, such as “what were last quarter’s sales by region”, into an SQL query, run that query against a database and return the result. It lets managers and analysts ask data questions without writing SQL or waiting for a report. The model writes the query, the database calculates the answer, and the person sees both.
How Text-to-SQL Works
A production system is more than one prompt:
- Understand the question. The model identifies the metrics, filters and time periods asked for, and asks a clarifying question when something is ambiguous (“sales” gross or net?).
- Find the right schema. Real databases have hundreds or thousands of tables and columns. The system retrieves only the relevant tables, column descriptions, example values and approved business definitions, often with retrieval-augmented generation over a data catalogue.
- Write the query. A large language model, sometimes a reasoning model for complex joins, generates SQL in the right dialect.
- Check before running. The query is parsed, validated against the schema and allow-lists, and limited in cost and row count.
- Run and correct. If the database returns an error, the model sees it and retries within a limit.
- Return the evidence. The answer shows the number, the SQL that produced it and, where useful, a chart.
Why It Is Harder Than It Looks
Public benchmarks once suggested the problem was nearly solved. The Spider 2.0 benchmark, built from real enterprise workflows on databases that often have over 1,000 columns and live in systems like BigQuery and Snowflake, showed otherwise: an agent based on a then-leading reasoning model solved 21.3% of its tasks, against 91.2% on the original Spider and 73.0% on the BIRD benchmark. Enterprise text-to-SQL fails less on SQL syntax and more on meaning:
- Column names that do not say what they hold.
- Several tables that look like the right one.
- Business definitions (“active customer”, “revenue”) that exist only in people’s heads or in a BI tool.
- Dialect differences between database engines.
That is why a semantic layer or curated metric definitions matter more than the choice of model.
Safeguards a Production System Needs
- Read-only access. The database user the system runs as can only run SELECT queries on approved views. PostgreSQL and other databases support this through ordinary privileges.
- Row-level and column-level security so a user never sees data through the assistant that they could not see directly. The assistant never has more permissions than its user.
- Query limits on run time, scanned data and returned rows.
- Show the SQL with every answer, so results are traceable and an analyst can check them.
- Numbers come from the database, never from the model’s own arithmetic or memory.
- Prompt injection defence, because text stored in the database can contain instructions.
Common Failure Modes
- A wrong but plausible number. The query runs, returns a figure, and nobody notices it used the wrong join. Evaluation sets with known answers catch this.
- Silent filter errors, such as the wrong date range or currency.
- Over-trusting the summary. The model’s sentence about the result can misstate the table it was given.
Related Terms
Text-to-SQL is a form of function calling where the tool is a database. It works on structured data, while RAG covers documents, and many assistants combine both. Quality is tracked through LLM evaluation on question-and-answer pairs approved by data owners.
In Practice
The evaluation set comes before the assistant: real questions from users, each with the correct SQL and result confirmed by the data owner. The assistant runs on a read-only database account limited to an approved set of tables and views, and shows its query next to every answer. Rollout starts with analysts who can spot a wrong join, before the assistant reaches business users who would take the figure on trust.
See the report AI assistant case for a text-to-SQL assistant we delivered, and our data analytics services and AI agent development for how we build them.