text2sql-agent

A self-correcting natural-language-to-SQL agent for SQLite. It links the schema, validates every query as read-only, runs it in a sandbox, and repairs failed or empty queries using feedback from execution.

$ t2s ask "How many canceled orders are there?"
tables : orders

-- attempt 1: empty (query returned only zero/NULL values)
SELECT COUNT(*) FROM orders WHERE orders.status = 'canceled'
   hint: orders.status has no value 'canceled'. Closest values: 'cancelled'.

-- attempt 2: ok
SELECT COUNT(*) FROM orders WHERE orders.status = 'cancelled'

COUNT(*)
--------
174

The answer is 174.

Pure standard library, Python 3.11+. Runs fully offline by default, and works with Claude or any OpenAI-compatible endpoint (Ollama, vLLM, OpenAI) when you want a real model.

Why

Single-shot text-to-SQL breaks in a handful of ways that repeat and can be diagnosed:

• Wrong literal. The model writes 'canceled', but the data says 'cancelled'. It writes city = 'Japan' when Japan is a country. The query runs and silently returns 0 rows or COUNT = 0, and nothing errors.
• Wrong or missing table. On a real warehouse the full DDL doesn't fit in the prompt. Irrelevant tables also mislead the model (warehouses.city instead of customers.city).
• Unsafe SQL. Model output is untrusted input. DROP, ATTACH, load_extension(), a second statement after ;, DML hidden inside a CTE, or a cartesian join that never finishes.

A bigger model fixes none of these reliably. Better context and better feedback do. This project is a small but complete reference implementation of that idea. It comes with an eval harness that measures execution accuracy, so every design claim below has a number behind it.

Quickstart

git clone https://github.com/lijo123ferdinand/text2sql-agent && cd text2sql-agent
python3 -m venv .venv && . .venv/bin/activate
pip install -e '.[dev]'

t2s ask "Which category has the highest revenue in 2024?"
t2s eval --split dev,holdout --by-tag
pytest -q

On first use, a deterministic sample e-commerce database is generated into ~/.cache/text2sql-agent/ (override with T2S_CACHE_DIR). Real output from the second command:

tables : order_items, products, orders

SELECT products.category, SUM(order_items.quantity * order_items.unit_price) AS value FROM order_items JOIN orders ON orders.id = order_items.order_id JOIN products ON products.id = order_items.product_id WHERE strftime('%Y', orders.order_date) = '2024' GROUP BY products.category ORDER BY value DESC LIMIT 1

category     value
-----------  ---------
Electronics  133298.93

Found 1 row (category: Electronics, value: 133298.93).

Here, "revenue" came from the business glossary, which pulled in order_items. "In 2024" pulled in orders, because it is the nearest table with a date column. products came from "category".

With a real model:

pip install -e '.[anthropic]'
export ANTHROPIC_API_KEY=...          # the key itself is never stored or logged
t2s ask "Top 3 customers by total spend" --provider anthropic        # default model: claude-sonnet-5-5

t2s ask "..." --provider openai --model qwen2.5-coder:7b              # Ollama at localhost:11434/v1
OPENAI_BASE_URL=https://api.openai.com/v1 OPENAI_API_KEY=... t2s ask "..." --provider openai --model <model>

Use your own database with --db path/to/file.sqlite. See examples/bring_your_own_db.py.

As a library:

from text2sql_agent import Text2SQLAgent, ensure_sample_db

with Text2SQLAgent(ensure_sample_db()) as agent:          # MockProvider by default
    res = agent.ask("How many orders were placed by customers in Germany?")
    print(res.sql, res.result.rows, res.answer)
    for a in res.attempts:                               # the full repair trace
        print(a.outcome, a.sql, a.hints)

Architecture

question
   │
   ▼
┌──────────────────┐   lexical (stems, synonyms) + hashed char-trigram embedding
│  schema linking  │   + value linking ("Germany" ∈ customers.country)
│  linking.py      │   → top-k tables, FK bridge tables, temporal table, glossary
└────────┬─────────┘
         ▼
┌──────────────────┐   TF-IDF over literal-masked questions
│ few-shot retrieval│  ("2023" ≡ "2024", "Japan" ≡ "France")
│  fewshot.py      │
└────────┬─────────┘
         ▼
┌──────────────────┐   DDL + sample values + business definitions
│  prompt + LLM    │   + value hints + examples (+ previous attempts)
│  prompts.py      │   Provider: Mock | Anthropic | OpenAI-compatible
└────────┬─────────┘
         ▼
┌──────────────────┐   layer 1: tokenizer-based validator: 1 statement,
│  safety          │   SELECT/WITH only, no DDL/DML/PRAGMA/ATTACH anywhere,
│  safety.py       │   function deny-list, LIMIT added/clamped at depth 0
└────────┬─────────┘
         ▼
┌──────────────────┐   layer 2: file opened mode=ro + PRAGMA query_only
│  sandboxed exec  │   layer 3: sqlite authorizer allows only READ/SELECT/FUNCTION
│  executor.py     │   timeout: progress_handler deadline; fetchmany(max_rows+1)
└────────┬─────────┘
         ▼
   ok? ──yes──────────────────────────────────────────┐
    │no: error / timeout / rejected / empty / COUNT=0 │
    ▼                                                 │
┌──────────────────┐  error   → SQLite message, column owners; unknown   │
│  repair feedback │            table/column → widen schema to all tables│
│  agent.py        │  empty   → probe real values of each literal filter: │
│  (≤ max_attempts)│            closest values + other columns holding it │
└────────┬─────────┘  repeat  → stop (same SQL twice = no new information)│
         └──────────── back to prompt ◄────────────┘                      │
                                                                          ▼
                                                            answer synthesis

module	what it does
sampledb.py	Seeded generator for 12 tables (4 core, 3 related, 5 distractors). Byte-stable content, atomic writes.
schema.py	Introspection (types, PKs, FKs, top-k frequent values, numeric ranges). Renders to DDL and parses it back.
linking.py	Table scoring, value index, glossary, FK graph and shortest paths, bridge and temporal tables.
fewshot.py	Example store with literal masking.
sqlparse.py	A real tokenizer (strings, quoted identifiers, comments, numbers, params) and paren depth.
safety.py	Static validator. Errors are phrased so they can go back to the model as repair hints.
executor.py	Read-only, authorizer-guarded, time- and row-bounded execution.
agent.py	The loop, feedback construction and value probing.
providers/	Provider ABC, plus MockProvider (rule-based NL→SQL), AnthropicProvider and OpenAICompatProvider (urllib).
evaluation.py	Execution-accuracy harness and result-set comparator.

The MockProvider is a real baseline, not a stub

The offline provider is a rule-based semantic parser. It reads only the prompt: the rendered DDL, sample values, glossary, value hints and previous-attempt feedback. That is exactly what an LLM gets. From that, it plans entities, intent (count / aggregate / group / top-N / list), filters (literals, adjectives like "cancelled", years and months, numeric comparisons, HAVING), FK joins (a greedy Steiner tree over shortest paths), and COUNT(DISTINCT pk) wherever a join fans out.

It also makes the mistakes models make. If a literal isn't among the visible sample values, it guesses the column ("in Japan" → city) and keeps the user's spelling ("canceled"). The repair loop's probes then correct it. That's why the repair path can be measured offline, in CI, with no API key.

Evaluation

• Metric: execution accuracy (EX). Gold SQL and predicted SQL are both executed and their result sets compared. SQL strings are never compared. Rows are a multiset, unless the gold query has a top-level ORDER BY. Floats are rounded to 2 dp. Column order doesn't matter, and the prediction may include extra columns (for example name, revenue for "top 5 products by revenue"). Each gold column must map to a distinct predicted column with row alignment preserved, which is found by backtracking over column permutations.
• Data: 66 questions with gold SQL in two splits.
  • dev (48 questions) was used while building the MockProvider.
  • holdout (18 questions) was written after the mock was frozen. Its first run (value hints off, which was the default then) gave 77.8% → 83.3%, the same as the --no-value-hints row below. The mock has not been changed since, so holdout failures (h05, h08, h09) are left as they are.
  • Four dev questions are tagged hard (nested aggregate, negation, ratio, HAVING over a subquery). The rule-based mock fails all four by design.
• "Without repair" is attempt #1 of the same run. Attempt #1's prompt contains no feedback, so it is identical to a --max-attempts 1 run. tests/test_eval.py asserts that equivalence.

Results (MockProvider, measured on this repo, t2s eval --json)

All 66 questions (--split dev,holdout), max 3 attempts:

configuration	EX, 1 attempt	EX, with repair	fixed / broken by repair	mean attempts
default (3 sample values per column, value hints, linking)	89.4%	90.9%	1 / 0	1.02
no value hints	86.4%	89.4%	2 / 0	1.05
no sample values, no value hints	71.2%	86.4%	10 / 0	1.18
no sample values, value hints	87.9%	90.9%	2 / 0	1.03
no schema linking (all 12 tables in prompt)	81.8%	83.3%	1 / 0	1.02

Per split (default configuration): dev 89.6% → 91.7%, holdout 88.9% → 88.9%. With no samples and no hints: dev 72.9% → 87.5%, holdout 66.7% → 83.3%.

Schema linking keeps 1.64 of 12 tables per prompt on average, with 98.5% recall of the gold query's tables. Mean agent latency with the mock is about 2 ms per question.

What the numbers say:

1. Repair and value linking are substitutes. When the prompt shows no values, the repair loop lifts EX by +15.2 points (71.2% → 86.4%). It fixes 10 questions, breaks none, and costs only 0.18 extra model calls per question. When value hints already put the right literals in the first prompt, repair has little left to fix. Value linking needs a value index built over the database up front. Repair needs nothing but the database at query time, and only pays when something went wrong.
2. Where repair helps (no samples/hints, by tag): value-column 0% → 100%, value-mismatch 0% → 75%, count 54% → 88%, join 79% → 100%. Where it can't help: hard 0% → 0%. Feedback can fix a literal but can't teach a parser nested aggregation.
3. Schema linking is an accuracy feature, not just a token saver: 90.9% with linking vs 83.3% with the full schema. With all 12 tables visible, the generator binds literals and columns to distractor tables.

No real-LLM numbers are reported, because none were run for this release (no API key was available while building it). The harness is provider-agnostic: t2s eval --provider anthropic produces the same table for Claude.

Design decisions and trade-offs

• A tokenizer, not regexes, for safety. '; DROP TABLE x; --' inside a string, or DELETE inside a comment, must not fool the validator. An unterminated quote is a rejection, not a guess.
• Defence in depth. The validator is there for explainable early rejection, which can be fed back to the model. The read-only URI, query_only and the authorizer are there for guarantees, and they don't trust the validator. Tests run hostile SQL directly against the executor with the validator bypassed and check the file hash is unchanged.
• The timeout is a progress_handler deadline, not a thread kill. SQLite aborts cleanly with SQLITE_INTERRUPT and the connection stays usable (tested).
• Empty is suspicious, but not always wrong. An empty result, or a single row of 0/NULL, triggers value probing. If every literal filter matches a real value, the empty answer is accepted without spending another model call.
• The loop stops when the model repeats itself. Sending the same SQL again buys nothing.
• Widening on unknown table or column. If the linker dropped a needed table, the next attempt sees the full schema.
• Glossary over cleverness. "Revenue" from line items is a business definition, not something any model can infer from a schema. It is configurable per database (data/shop_glossary.json).
• Hashed trigram "embeddings". They are deterministic, have no dependencies and need no download, and they catch morphology the stemmer misses. The Embedder protocol lets you plug in a real sentence-embedding model.
• No temperature for Claude. Current Claude models reject non-default sampling parameters. The Anthropic provider also opts into the API's server-side refusal fallback (fallbacks: "default") by default; pass fallbacks=False to disable it.
• EX tolerates extra columns. This is a deliberate leniency (documented above). It means SELECT * can pass a "list names" question whose row set matches, which is arguably correct for a user but more forgiving than BIRD/Spider strict EX.

Limitations

• The eval set is small (66 questions) and covers one domain. It is a regression and ablation harness, not a leaderboard. The MockProvider was developed against dev, so trust holdout and the ablation deltas more than the absolute dev numbers.
• The MockProvider can't do negation, nested aggregates, ratios or equality on numbers ("rating of 5"), and it sometimes misreads "per X Y" phrasings (holdout h09).
• SQLite only. The executor's sandbox relies on SQLite's authorizer. Other engines need their own read-only role and statement timeout.
• Value probing looks at =/LIKE string literals. It doesn't diagnose wrong numeric thresholds or wrong join paths that return plausible-looking rows.
• The answer synthesizer for the mock is a template. With a real provider it is an LLM call constrained to the result rows.

Roadmap

• Real-model results (Claude, a local Qwen-Coder) in the results table, with cost per question.
• Spider/BIRD-style dataset loader, so EX can be measured on public benchmarks.
• Self-consistency: sample N candidates, execute all, majority-vote on result sets.
• Join-path diagnostics in repair (fan-out detection: COUNT(*) vs COUNT(DISTINCT) disagreement).
• PostgreSQL/DuckDB executors (read-only role + statement_timeout).
• Persisted value index for large databases (min-hash / FTS5 instead of an in-memory dict).

Layout

src/text2sql_agent/   agent, linking, safety, executor, providers/, evaluation, cli
src/text2sql_agent/data/   glossary, few-shot examples, shop_dev.jsonl, shop_holdout.jsonl
examples/             quickstart.py, bring_your_own_db.py, safety_demo.py
tests/                111 tests (pytest -q)

License

MIT © 2026 Lijo Ferdinand