NLQueries GitHub

Blog  / 

Text-to-SQL, explained: how a question becomes a query, and where it goes wrong

Published . Written for nlqueries-core 0.3.

text-to-sql knowledge-base validation

Text to SQL, also called natural language to SQL, takes a question such as "How many orders did we ship last month?" and turns it into a query a database can run. A language model writes the SQL, but in our experience most of the work, and most of the failures, happen before and after the model call: what the model is told about your database, what is checked before the statement runs, and what happens when the database refuses it.

This post walks through each stage as nlqueries-core 0.3 implements it, and names the way each one tends to go wrong. Most stages have a reference page, and the links take you there rather than repeating it.

1. Schema extraction

Everything starts with what the database says about itself. After you register a connection with nlqueries connect, nlqueries extract-schema inspects it and reports the number of tables, the total number of columns, and a column and row count per table. The column types, keys and foreign keys it reads go into the knowledge base in the next step. See extract-schema, and the connector notes for engines with gaps: Redshift has no column descriptions, and DuckDB foreign keys are skipped.

Where it goes wrong: a schema tells the model what exists, not what anything means. A column called status with values 1 to 4, or two tables that both look like "customers", give the model nothing to choose between. A missing foreign key means the model has to guess the join.

2. The knowledge base

nlqueries export-kb turns the schema into a YAML knowledge base, which is what query and ask actually read. It holds each table and column with its type, key flags and description, the foreign keys, a business_context section for a glossary and business rules, and the query capsules mined from your query history. See export-kb.

A few details matter here:

  • Descriptions have a precedence. A description you write by hand beats one from dbt, which beats one from the database's own comments, which beats one the LLM generated with --describe-columns. Re-running export-kb keeps your edits. See import-dbt.
  • Sensitive values stay out of the model's view. --describe-columns reads a few sample rows to write descriptions, but values from columns whose names look like passwords, tokens, emails, phone numbers, addresses or dates of birth are not sent to the model.
  • The glossary can be hierarchical. A term may name a parent:, and kb-stats reports orphans and cycles. See glossary hierarchy.

Where it goes wrong: a knowledge base built from the schema alone is accurate but thin. The fix is to add meaning, and the most useful source of meaning is the queries your team already runs. The next post covers that.

3. Generation

For each question nlqueries query first checks the semantic cache, then hands the question to the SQL agent. The orchestrator can also route to a document agent, but in 0.3 the CLI and the MCP server send every question to the SQL agent. The SQL agent builds a prompt in two parts:

  • a static block with the schema (in a compact format by default, set by NLQ_SCHEMA_FORMAT), the glossary and the business rules. It is identical for every question to the same agent, so the provider can cache it.
  • a dynamic block for this question. When a verified example promoted from thumbs-up feedback is similar enough to the question, it goes here. Otherwise the block holds the most frequent query capsules from the knowledge base, up to five.

The model is called once, streams its reasoning, and wraps the SQL in markers. nlqueries ask goes straight to the SQL agent, without the cache or routing, and stops after validation, showing you the SQL without running it. See ask and architecture.

Where it goes wrong: the model picks a plausible column that is not the right one, joins on the wrong key, or reads an ambiguous term ("active customer", "revenue") differently from your team. Validation does not catch any of these, because the SQL is valid. Glossary entries and capsules are how you narrow them.

4. Validation

Before anything runs, the statement goes through two checks.

The SQL policy parses the whole statement with sqlglot and refuses it unless it is exactly one statement whose root is a SELECT or a UNION. It also refuses data-changing and schema-changing nodes anywhere in the tree, including inside a CTE or subquery: INSERT, UPDATE, DELETE, MERGE, CREATE, DROP, ALTER, TRUNCATE, COPY, SET, GRANT, SELECT ... INTO and row locks such as FOR UPDATE. Functions sqlglot does not recognise are refused unless they are on a short per-dialect allowlist, which keeps out things like pg_read_file. A statement that cannot be parsed is refused, not waved through.

The schema check then confirms that every table the statement references (CTE names aside) exists in the knowledge base. It also checks named columns, on a best-effort basis (SELECT * passes, and a statement it cannot analyse is let through), and refuses a quoted string as a LIMIT or OFFSET.

If a check fails, a mechanical repair is tried first (fixing table-name case, or keeping only the first statement when the model returned several), then one call to the model with the error. Either way the result is checked again.

Where it goes wrong: static validation knows the knowledge base, not the engine. A statement sqlglot accepts can still be one your database rejects, and a table that is in the knowledge base may be one the database login was never granted.

5. Execution

nlqueries query runs the validated statement through the connector (see query). Three things bound that run:

  • A read-only execution where the engine allows it. PostgreSQL and Redshift use SET TRANSACTION READ ONLY. SQLite and DuckDB open the file read-only and lock down file access. Other engines get a transaction that is never committed, and BigQuery gets nothing. What each connector enforces has the per-engine table.
  • A statement timeout, CONNECTOR_STATEMENT_TIMEOUT_SECONDS, 120 seconds by default. SQL Server applies it as the driver's connection-wide query timeout and ignores a shorter per-call budget.
  • Result limits. A connector stops reading at 10,000 rows or an estimated 64 MB, and query shows at most 200 of them, next to the number of rows the connector read.

In 0.3 the MCP server's query tool generates and validates SQL but does not run it, so an MCP client gets the SQL and no rows.

Where it goes wrong: the database is the only layer that knows what the login may touch. If the login can write, nothing above is a guarantee. The security post covers the role to give it.

6. The correction after a database error

When the database rejects a statement, the error is usually the most precise feedback available: a compilation error names the exact fault. So the orchestrator gives the model one more try. It sends the original question, the failed SQL and the database's error, truncated to 1,000 characters and labelled as data rather than instructions. The corrected statement goes through the same policy and schema checks as a first attempt, and only runs if it passes.

The correction is deliberately narrow. It happens once. It is skipped when the error is a permission refusal, because the only "fix" would be reading a different table, which answers a different question. It is skipped when the failed attempt used most of the statement timeout, so a slow query is not run twice. And if the model returns the same statement, it is not re-run.

Where it goes wrong: a correction that runs is not a correction that is right. It removes the error the database named; it does not prove the new query answers the question. nlqueries eval re-asks the capsules that have a description (up to --max-cases, 50 by default) and, optionally, a golden set of questions, and checks the SQL still parses and uses known tables, which catches regressions but not every wrong answer. See eval.

The short version

Most text-to-SQL failures are not syntax errors. They are valid queries that answer a slightly different question, and the defences against them are the knowledge base, the examples, and a human reading the SQL. The checks after generation keep a bad statement from doing damage. The context before it is what makes a good statement likely.

Reference

All posts · RSS feed · Documentation