Text-to-SQL with LLMs: Architecture, Accuracy and Safety

Short answer: a production text-to-SQL system is not "send the question to an LLM and run whatever comes back". It is a pipeline: retrieve the relevant parts of the schema, generate SQL with grounded context and examples, validate the query before execution, run it under a locked-down read-only role, and measure accuracy continuously against a set of real questions. The model is one component; the guardrails around it decide whether the system can be trusted.

Why naive text-to-SQL fails

A demo with three tables and obvious column names works on the first try. Real schemas don't look like that. Typical failure modes:

• Hallucinated columns or tables that sound right but don't exist.
• Wrong business semantics: "active customers" means something specific in your company that the model can't guess.
• Subtle join errors that return plausible-looking but duplicated or missing rows.
• Wrong value formats: 'AU' vs 'Australia', enum casing, date granularity.
• Unbounded queries that scan huge tables or return millions of rows.

The dangerous case is not a query that errors out. It's a query that runs and returns a confident, wrong answer.

Reference architecture

question
  -> schema retrieval      (pick relevant tables/columns + descriptions)
  -> prompt assembly       (schema slice + business rules + few-shot examples)
  -> SQL generation        (LLM, structured output)
  -> static validation     (parse, SELECT-only, allow-listed tables/columns, LIMIT)
  -> dry run / EXPLAIN     (catch errors and expensive plans)
  -> repair loop (max N)   (feed the error back to the model)
  -> execute               (read-only role, timeout, replica)
  -> result + SQL shown to the user, logged for evaluation

Schema grounding

Grounding is the single biggest lever on accuracy. For each table you expose, provide:

• Table and column names with types, primary and foreign keys.
• A one-line plain-language description of each table and any non-obvious column.
• Allowed values for low-cardinality columns (enums, country codes, status fields).
• Business definitions: "revenue excludes refunds", "an active user logged in within 30 days".

For large schemas, don't send everything. Index the table and column descriptions with embeddings, retrieve the top candidates for the question, and expand along foreign keys so join tables come along. This is the same retrieval discipline described in building production RAG systems with LangChain, applied to metadata instead of documents.

Often the cleanest move is to create a small set of purpose-built database views with friendly names and pre-applied business logic, and expose only those to the model. A schema designed for humans to query is easier for a model to query too; see database design for scalable applications for the underlying principles.

Few-shot examples

Curate 5–20 question/SQL pairs that cover your domain's tricky patterns: the correct way to count active users, date bucketing, the join that always trips people up. Store them in a library and retrieve the most similar examples per question rather than hard-coding a fixed list. When the system gets a question wrong in production and you fix it, the corrected pair becomes a new example. Over time this library becomes your most valuable asset.

-- Q: How many active subscribers in Australia aged 25-40?
SELECT COUNT(*)
FROM v_subscribers
WHERE country_code = 'AU'
  AND age BETWEEN 25 AND 40
  AND last_active_at >= now() - interval '30 days';

Query validation before execution

Treat generated SQL as untrusted input. Parse it with a real SQL parser (for example sqlglot in Python) and enforce rules in code:

import sqlglot
from sqlglot import exp

ALLOWED_SCHEMA = "analytics"
ALLOWED_TABLES = {"v_subscribers", "v_campaigns", "v_events"}
BLOCKED_FUNCS = {"pg_read_file", "pg_read_binary_file", "pg_ls_dir", "pg_sleep",
                 "dblink", "lo_import", "lo_export", "copy"}

def validate(sql: str, max_rows: int = 1000) -> str:
    statements = sqlglot.parse(sql, read="postgres")
    if len(statements) != 1:
        raise ValueError("Exactly one statement allowed")
    tree = statements[0]
    if not isinstance(tree, exp.Select):
        raise ValueError("Only SELECT queries are allowed")
    if tree.find(exp.Into):
        raise ValueError("SELECT ... INTO is not allowed")
    for t in tree.find_all(exp.Table):
        # Check the full qualified name: no catalog, only the allowed schema
        if t.catalog or t.db not in ("", ALLOWED_SCHEMA) or t.name not in ALLOWED_TABLES:
            raise ValueError(f"Forbidden table: {t.sql(dialect='postgres')}")
    for f in tree.find_all(exp.Func):
        name = f.name if isinstance(f, exp.Anonymous) else f.sql_name()
        if name.lower() in BLOCKED_FUNCS or name.lower().startswith(("pg_", "lo_")):
            raise ValueError(f"Function not allowed: {name}")
    if not tree.args.get("limit"):
        tree = tree.limit(max_rows)
    return tree.sql(dialect="postgres")

Add column-level checks against the schema, then run EXPLAIN to catch errors and reject plans above a cost threshold. If validation fails, send the specific error back to the model for a bounded number of repair attempts (two or three is usually enough), then fail gracefully and ask the user to rephrase.

Model choice and output format

Ask the model for structured output rather than free text: a JSON object containing the SQL, the tables it intends to use, any assumptions it made, and a flag for "this question is ambiguous". The assumptions field is surprisingly useful; it is what you show the user when the system interprets "last quarter" or "top customers" in a particular way, and it's what reviewers read when an evaluation case fails.

Larger models handle complex joins and nested logic better, but a mid-sized model with excellent grounding and examples often beats a frontier model with a raw schema dump. A practical pattern is to route: try a fast, cheap model first, and escalate to a stronger model only when validation fails or the question is classified as complex. Measure both paths on the same evaluation set before committing to either.

Ambiguity and follow-up questions

Many wrong answers come from questions that were never fully specified. "How many users signed up last month?" hides decisions: calendar month or the last 30 days, which time zone, whether test accounts count. Handle this explicitly:

• Encode sensible defaults as business rules in the prompt, and state them in the response.
• When the model flags ambiguity it can't resolve with defaults, return a clarifying question instead of SQL.
• For follow-ups ("now split that by country"), pass the previous question and validated SQL as context so the model modifies a known-good query rather than starting from scratch.

Safety: read-only roles and defence in depth

Prompt instructions like "never write DELETE" are not a security control. Put the guarantees in the database:

-- Group role that holds the privileges
CREATE ROLE text2sql_reader NOLOGIN;
GRANT USAGE ON SCHEMA analytics TO text2sql_reader;
GRANT SELECT ON analytics.v_subscribers, analytics.v_campaigns, analytics.v_events
  TO text2sql_reader;

-- Login role the application connects as; role settings apply to the login role
CREATE ROLE text2sql_app LOGIN PASSWORD '...' IN ROLE text2sql_reader;
ALTER ROLE text2sql_app SET statement_timeout = '5s';
ALTER ROLE text2sql_app SET default_transaction_read_only = on;
ALTER ROLE text2sql_app SET search_path = analytics;

The database role and its permissions are the real security boundary; the parser check above is defence in depth that catches mistakes early and gives the model useful error messages.

• Connect with a dedicated login that has only this role, ideally against a read replica.
• Expose views, not raw tables, and leave out PII columns the use case doesn't need.
• Use row-level security if different users should see different data.
• Rate-limit per user and log every generated query, the question and the result size.

A real example: Audience Craft AI

In the Audience Craft AI case study, a plain-language campaign brief is translated into SQL against a proprietary audience dataset in PostgreSQL to find the most accurate targeting segment, whose matched IDs are then used to launch campaigns on Meta, TikTok and Google. Two design choices from that project generalise well:

• Generation is constrained to the real schema (table names, column types, valid value ranges), so the model can't reference data that doesn't exist.
• Validation happens before execution, not after. A query that fails checks never touches the dataset.

The lesson: the hard part isn't producing a plausible query, it's constraining generation tightly enough that plausible and correct become the same thing.

Evaluating accuracy

Build an evaluation set of real questions (start with 50–100) with reference SQL written or reviewed by someone who knows the data. Then measure:

Compare result sets, not SQL strings: two different queries can be equally correct. Normalise ordering and column names before comparing. Run the suite in CI whenever you change prompts, examples, schema descriptions or the model. The broader approach is covered in how to evaluate LLM and RAG applications in production.

UX that builds trust

• Show the generated SQL (or a plain-language summary of the filters) next to the result.
• Ask a clarifying question when the request is ambiguous instead of guessing.
• Let users flag wrong answers; each flag is a candidate evaluation case and few-shot example.

Frequently asked questions

How accurate is text-to-SQL with modern LLMs?

On simple, well-named schemas, modern models are often right on the first attempt. Accuracy drops on large schemas, ambiguous column names, business-specific definitions and multi-step joins. The honest answer is that you have to measure it on your own schema and question set; public benchmark scores rarely transfer directly.

Is it safe to let an LLM run SQL against my database?

Only with layered controls: a dedicated read-only database role, access restricted to approved views, statement parsing that rejects anything other than a single SELECT, enforced row limits and timeouts, and ideally a read replica. Never rely on the prompt alone to prevent writes.

Do I need to fine-tune a model for text-to-SQL?

Usually not at first. Good schema grounding, curated few-shot examples and a validation-and-repair loop get most teams a long way. Fine-tuning becomes worth considering when you have a large, stable schema, many logged question-and-SQL pairs, and latency or cost pressure to use a smaller model.

How do I handle very large schemas?

Don't put the whole schema in the prompt. Retrieve the relevant tables first, using embeddings over table and column descriptions or a lightweight classification step, then pass only those tables with their descriptions, keys and example values to the generation step.

How should I evaluate a text-to-SQL system?

Build a set of real questions with reference SQL, run both the generated and reference queries, and compare result sets (execution accuracy) rather than SQL strings. Track validity rate, execution accuracy, repair rate and latency, and run the suite on every prompt or model change.

Building something similar?

If you need a natural-language interface over your own data, see my AI engineering services or get in touch to talk through your schema and use case.