Text-to-SQL with LLMs: schema context, read-only roles and validation that holds

Every company with a database has someone who wants to ask it questions in plain language: how many orders came from Austria last quarter, which customers stopped buying, which articles are below minimum stock. LLMs write decent SQL, so the demo takes an afternoon. Production takes longer, because the hard parts are not generation: they are giving the model the right schema context, and making sure a wrong or manipulated query cannot do damage.

This article starts with what the benchmarks say about accuracy, then walks through the architecture I use: curated schema context, a database role that can only read what it should, validation in code, and a review loop with the user. The examples use PostgreSQL, but the layers apply to any SQL database.

How accurate is text-to-SQL today?

BIRD is the standard benchmark for realistic databases: 12,751 question and SQL pairs over 95 databases totaling 33.4 GB, with dirty values and external knowledge required. Its leaderboard lists human performance (data engineers and database students) at 92.96% execution accuracy on the test set. The best systems in September 2026 reach about 83%, and they get oracle knowledge hints that your users will not provide.

Spider 2.0 shows the enterprise end: 632 workflow problems on real databases in BigQuery, Snowflake and others, often with more than 1,000 columns and SQL over 100 lines. At publication, an o1-preview based code agent solved 21.3% of them, against 91.2% on the original Spider and 73.0% on BIRD. The lesson is not that text-to-SQL fails, but that accuracy depends on how much of the schema's meaning the model can find.

What does a safe text-to-SQL architecture look like?

The order matters. The database layer is the guarantee; the validator is a fast second layer that gives the model useful errors; the user review catches queries that are valid, safe and still answer the wrong question.

What schema context does the model need?

Raw CREATE TABLE statements are not enough. Column names like kz_status or art_typ mean nothing to a model, and they mean little to new employees either. What helps, roughly in order of impact:

1. Curated views with readable names that join the tables people actually ask about, such as v_orders with customer country and net amount in euros.
2. Column descriptions with units, codes and their meanings: status 3 means shipped, amounts are net, dates are in local time.
3. Sample values for low-cardinality columns, so the model writes country = 'AT' and not country = 'Austria'.
4. A business glossary: what counts as an active customer, which orders count as revenue, how a fiscal year is defined.
5. Verified example queries, question and SQL pairs that someone checked, retrieved by similarity to the new question.

For large schemas, do not paste everything into the prompt. Retrieve the relevant views and descriptions per question, the same way you would retrieve documents in RAG. The glossary and examples are also where most of the ongoing work goes: every wrong answer a user reports should end up as a new verified example or a corrected description.

Which guardrails belong in the database?

OWASP lists excessive agency as a top risk for LLM applications and uses almost exactly this case as its example: an agent that only needs read access to a products table should not be able to touch other tables or modify records, and this should be enforced with database permissions for the identity the agent connects as. That is the same principle as in AI agents are identities.

-- A role for the assistant: SELECT on curated views, nothing else
CREATE ROLE sql_assistant LOGIN PASSWORD :'pw' NOSUPERUSER NOCREATEDB NOCREATEROLE;
REVOKE ALL ON SCHEMA public FROM sql_assistant;
GRANT USAGE ON SCHEMA reporting TO sql_assistant;
GRANT SELECT ON reporting.v_orders, reporting.v_customers, reporting.v_stock TO sql_assistant;

-- Every transaction read-only, every statement time-limited
ALTER ROLE sql_assistant SET default_transaction_read_only = on;
ALTER ROLE sql_assistant SET statement_timeout = '5s';
ALTER ROLE sql_assistant SET transaction_timeout = '10s';

-- Row-level security on the base table: each session sees only its tenant
-- (views need security_invoker = true so the policy applies to the caller)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_only ON orders FOR SELECT TO sql_assistant
  USING (tenant_id = current_setting('app.tenant_id')::int);
ALTER VIEW reporting.v_orders SET (security_invoker = true);

Two details from the PostgreSQL row security documentation are easy to miss: the table owner is normally not subject to row security policies, so the assistant must never connect as the owner, and once RLS is enabled with no policy, the default is deny.

How do you validate generated SQL in code?

Parse the SQL with a real parser instead of using regular expressions. SQLGlot parses more than 30 dialects into a syntax tree you can inspect. The checks below reject anything that is not a single read query on allowed views, and add a row limit.

# Illustrative validator with sqlglot (second layer; the database role is the guarantee)
import sqlglot
from sqlglot import exp

ALLOWED = {"v_orders", "v_customers", "v_stock"}
WRITE_NODES = (exp.Insert, exp.Update, exp.Delete, exp.Merge, exp.Create, exp.Drop, exp.Alter, exp.Command)
MAX_ROWS = 1000

def validate(sql: str) -> str:
    statements = sqlglot.parse(sql, read="postgres")
    if len(statements) != 1 or statements[0] is None:
        raise ValueError("exactly one statement is allowed")
    tree = statements[0]
    if not isinstance(tree, exp.Query):
        raise ValueError("only SELECT queries are allowed")
    if any(tree.find_all(*WRITE_NODES)):
        raise ValueError("data-modifying statements are not allowed")
    ctes = {c.alias_or_name for c in tree.find_all(exp.CTE)}
    tables = {t.name for t in tree.find_all(exp.Table)} - ctes
    if unknown := tables - ALLOWED:
        raise ValueError(f"unknown tables: {sorted(unknown)}; allowed: {sorted(ALLOWED)}")
    if not tree.args.get("limit"):
        tree = tree.limit(MAX_ROWS)
    return tree.sql(dialect="postgres")

Return the error message to the model and allow one or two repair attempts. The messages are written for the model: naming the allowed views is often enough for it to fix the query.

Can data in the database attack the model?

Yes. Results go back into the model's context, and text columns, such as product descriptions, customer notes or ticket bodies, can contain instructions written by anyone who could edit them. If the same agent can also send email or call external APIs, you have all three parts of the lethal trifecta: private data, untrusted content and a way out. Keep the SQL assistant's tools to querying and displaying, and summarize free-text columns only when the user explicitly asks.

Why should users see the generated SQL?

The most dangerous failure is a query that runs fine and answers a slightly different question: net instead of gross, order date instead of invoice date, cancelled orders included. Show the SQL, or a plain-language summary of its filters, next to the result table, and give users a one-click way to flag a wrong answer. Those flags feed the glossary and the verified examples.

How do you evaluate a text-to-SQL feature?

Use execution accuracy, the BIRD metric: run the generated query and the reference query, and compare the result sets, not the SQL text. Build 50 to 100 questions from real requests, with a reference query each, and run them in CI whenever the model, the prompt or the schema context changes, as in LLM evals for product features. Keep a slice of questions that should be refused, such as requests for data the role cannot see, and check that the system says so instead of guessing.

When should you not generate free SQL at all?

If most questions are about a few defined metrics, revenue, margin, open orders, a semantic layer or a set of parameterized query tools is more accurate than free SQL: the model picks the metric, the dimensions and the filters, and code builds the query. Free SQL is worth its risk for exploratory questions by analysts who can read the result critically, not for numbers that go into a board report unchecked.

Sources

1. BIRD-bench: leaderboard and dataset description
2. Lei et al.: Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows (2024)
3. PostgreSQL 18 Documentation: Row Security Policies
4. PostgreSQL 18 Documentation: Client Connection Defaults (read-only and timeouts)
5. SQLGlot: SQL parser, transpiler and optimizer (GitHub)
6. OWASP Gen AI Security Project: LLM06:2025 Excessive Agency