Text-to-SQL: Querying Your Database in Plain Language

How it works

Text-to-SQL means a user types a question in plain language, a language model writes a SQL query, the system runs it against the database and returns a table or a short answer.

The pipeline looks like this:

1. A user asks: "Which five products sold best last month?"
2. The backend adds a description of the database schema and the rules.
3. The LLM returns SQL.
4. Your code validates the query and runs it as a read-only user.
5. The result is shown together with the query text so it can be checked.

The model does not "know" your data. It only knows what you put in its context, so the quality depends almost entirely on preparation.

Schema context: the biggest lever on accuracy

Give a model a bare CREATE TABLE and it will guess what the columns mean. It often guesses wrong: status = 3 means nothing to it.

What to include:

• Only the relevant tables. Sales questions do not need log or settings tables. Extra tables confuse the model and waste tokens.
• Column descriptions in plain words. "amount is the order total in UZS including VAT", "deleted_at is set when the order was deleted".
• Lookup values. Which statuses exist and what each one means.
• Relationships — which keys join to which.
• Business definitions. What counts as "revenue", an "active customer", "last month" — by creation date or by payment date.
• A few examples of "question → correct SQL" for typical requests.

A strong technique is to build analytics views with clear names and filters already applied: v_paid_orders instead of raw tables plus a dozen conditions. The model has an easier job and makes fewer mistakes.

Security: read-only and hard limits

Generated SQL is code from an unpredictable source. Treat it that way.

• A dedicated database user with SELECT only, and only on approved tables or views. This is the real protection, not a prompt saying "never write DELETE".
• A replica instead of the primary database, if you have one, so a heavy query does not slow down production.
• An execution timeout and a row limit on results.
• Hide personal data. If managers do not need customer phone numbers, keep them out of the exposed views.
• A query log: who asked, what SQL was produced, how long it ran.

Validating the query before running it

Run automatic checks before execution:

• Parse the SQL and make sure it is exactly one SELECT statement (or WITH ... SELECT).
• Check that only allowed tables are referenced.
• Add a LIMIT if there is none.
• Run EXPLAIN and reject queries with an overly expensive plan.

import sqlglot

def validate(sql: str, allowed: set[str]) -> str:
    statements = sqlglot.parse(sql, read="postgres")
    if len(statements) != 1:
        raise ValueError("Expected a single statement")
    tree = statements[0]
    if tree.key not in ("select", "union"):
        raise ValueError("Only SELECT is allowed")
    tables = {t.name for t in tree.find_all(sqlglot.exp.Table)}
    if not tables <= allowed:
        raise ValueError(f"Tables not allowed: {tables - allowed}")
    return tree.sql(dialect="postgres")

If a query fails, you can send the error text back to the model once or twice and ask for a fix. Endless retries are not worth it.

Catching wrong answers

The real danger is not an execution error but a plausible wrong answer: the query ran, the number looks reasonable, but it measures the wrong thing.

What helps:

• Show the SQL next to the answer and restate its meaning briefly: "counted paid orders from March 1 to March 31".
• A test set of questions with known answers. Run it after every change to the prompt, schema or model.
• Ask for clarification when a question is ambiguous: "revenue with or without VAT?" beats a silent choice.
• Reconcile with reports. Compare key metrics with what BI or accounting already calculates.
• A "this answer is wrong" button, so you collect hard cases and add them to the examples.

Common mistakes

• Connecting the model to the primary database with write permissions.
• Passing the whole schema of hundreds of tables with no descriptions.
• Leaving business terms undefined, so "customer" and "revenue" are computed differently across answers.
• Showing only a number without the query, which makes it impossible to verify later.
• Expecting Text-to-SQL to replace an analyst on complex multi-step questions.