Text-to-SQL — Deep Dive

Turning a plain language question into an SQL query is a core challenge in data access. Modern large language models make it practical because they can generate candidate SQL code automatically. But their first attempt is often wrong. So systems add an error-driven correction loop. They execute the candidate query against the database. If they get a syntax error or an empty result set, they feed that real feedback back to the model. The model then rewrites the query. This repair cycle boosts accuracy far more than just asking the model to double-check itself. The loop is limited to two repair attempts to keep costs low. Safety is equally important. You cannot trust a raw model output to run directly on production data. The code might drop tables or access other tenants. So engineers enforce a read-only gate. They parse the query into an abstract syntax tree, using a parser like sqlglot. This confirms the statement is only a select query. No insert, delete, or drop is allowed. String matching is too easy to bypass with comments or nesting. The trade-off is between expressiveness and safety. Some systems constrain what users can ask before errors happen. Others let users type freely and fix errors afterward. Which approach yields higher user satisfaction is still an open question. The field is new, with very recent papers and no prior work on Cloudflare D1. Each design choice remains unsettled.

To write accurate SQL, the system needs the real database schema. The model is given actual table names, not guesses. For a dynamic schema, the catalog is read at the start of the graph. The graph first identifies which tables are relevant. Only then does it generate the structured query language, or SQL. This two-stage approach separates choosing tables from writing the query. Using the true table structure drives accuracy. The model avoids inventing column or table names. The SQL output is then checked. A parser builds a syntax tree to ensure it is a read-only statement. This is safer than just looking for keywords. If the SQL has an error, the database error message is fed back. The model then repairs the query. This loop improves accuracy. But grounding the schema requires knowing the exact table catalog. For a database with hundreds of tables, the system must select the relevant ones first. That is why the two-stage approach is used. The graph's code lists four CRM tables as constants. It also reads the catalog at the start for dynamic schemas. This careful grounding prevents many mistakes before they happen.

To get accurate SQL from a large language model, you need careful prompt design. One pattern is to constrain the dialect the model outputs. When the target database is Cloudflare D one, which uses SQLite, the prompt tells the model to use only SQLite functions and syntax. No Postgres specific features like double colon casts. This prevents invalid code.

Chain of thought decomposition is another pattern. The model first reasons through the question in steps before writing the SQL. The Sel ECT SQL system uses self correcting ensemble chain of thought to boost accuracy. SQL of Thought also uses guided error correction with a similar approach.

You can also ground the model by giving it explicit column and table names from the database. This stops it from guessing names that do not exist. Pre validated question to SQL examples show the model the correct format. These few shot examples guide generation toward the right syntax.

The trade off is between expressiveness and safety. Constraining the dialect and using pre validated examples limits what users can ask. But free form queries with healing loops allow more variety. The best choice depends on the users and the task.

Turning plain English questions into database queries is no longer a one-shot translation. Instead, the system enters a self-healing loop. It executes the generated query, catches the error message from the database, and feeds that diagnostic back to regenerate a corrected version. The loop is bounded, so it never runs forever. As soon as a query executes without error, the loop ends. That early-accept rule prevents a working query from being rewritten into a different one. An empty result set also counts as success, because no rows matching a filter is often the true answer. If all attempts fail, the system returns the last error and stops. The database itself acts as the verifier, providing real feedback like a missing column name or a type mismatch. These hard execution errors are easy to fix. But the loop cannot catch a query that runs cleanly and answers the wrong question—only a human reader spots that mistake. Using a smaller open-source agent like DeepSeek in this repair loop can match or even beat a larger single-shot model, because the loop corrects real mistakes rather than guessing at them. Two repair attempts is a sensible default, and the first success ends the process. The key insight is that execution errors carry far more information than any static validator ever could.

Running generated SQL safely over a real database requires multiple layers of protection. The most important gate is a SELECT-only check that parses the query, not just scans the text. A simple string search can be fooled by comments, whitespace, or stacked statements. Instead, a tool called an abstract syntax tree, or AST, understands the query exactly as the database will. It rejects any statement that tries to insert, update, delete, or drop data. This gate is the only path to execution. Every repaired SQL must pass it again, so a fix never widens permissions.

A second layer fences the user's original question. The system treats that text strictly as data, not as instructions. It strips tricky characters and blocks any attempt to forge the end of the safe zone. Even if an attacker slips in a hidden command, the SELECT-only gate catches it. That is defense in depth: no single control is trusted alone.

The loop that repairs failed queries is also bounded. At most two repair attempts are allowed. After a fix, the SQL must re-enter the same validation gate. No repaired query can escape the read-only rule. The database itself never receives more than fifty rows per query. That limit prevents runaway scans. Together, these layers let domain experts ask questions without risking their data.

Measuring a text-to-SQL system is harder than it looks. Two different SQL strings can be semantically identical. Two similar strings can return different data. So the field uses execution-based metrics. These are tested on a small set of standard benchmarks. Spider is the original large-scale cross-domain benchmark. It has over ten thousand questions across two hundred databases. It covers one hundred thirty-eight domains. Spider defines two metrics. One is exact-set-match accuracy. It compares the query clause by clause but ignores literal values. A correct query written differently is scored wrong. The other is execution accuracy. It runs both queries and compares results. But a single database can let a wrong query return the right rows. So Spider uses test-suite accuracy. It runs queries against many database instances. This minimizes false positives. The BIRD benchmark is newer. It uses real, dirty, large databases. It has nearly thirteen thousand question-SQL pairs over ninety-five databases. BIRD reports execution accuracy. It also reports a valid efficiency score that rewards correct and fast queries. An empty result set is a correct answer. It is not a failure to repair. It simply means the query ran and found no matching data.