nl2sql

A natural-language-to-SQL agent. You ask a question in plain English; the agent inspects the database schema, looks up how values are actually stored, writes read-only SQL, runs it and answers — or says so when the data isn't there. Works with any SQLAlchemy database (tested: SQLite, Postgres). The live demo runs Gemma 4 31B via OpenRouter on the MIMIC-IV demo database inside Open WebUI, with every tool call visible in the chat.

No setup at all: download demo/sql_agent_demo.html and open it in a browser — it shows recorded runs of four questions, step by step.

Try the demo

These steps are written to be followed top to bottom by a person or by a coding agent. Every step is safe to re-run. Time: about 10 minutes, most of it downloads. Cost: well under 1 cent per question.

1. Check the prerequisites

uv installs the Python versions this needs (3.13 for this project, 3.11 for Open WebUI) by itself.

2. Get the code

git clone https://github.com/albertd01/nl2sql.git
cd nl2sql

All following commands run from this nl2sql folder.

3. Install Open WebUI (once, ~2 GB)

uv tool install --python 3.11 open-webui

Check: uv tool list lists open-webui.

4. Download the database (once, 37 MB)

scripts/get_mimic_db.sh

Expected last line: Saved and verified: .../data/mimic_iv.sqlite (or Already present and verified).

5. Add the OpenRouter API key

Create a file .env in the project folder containing one line:

OPENROUTER_API_KEY=sk-or-v1-...

.env is git-ignored. The scripts read the key from there and never print it.

6. Start the demo

openwebui/start.sh

It prepares the Python environment, starts the database tool server (MCP, port 8765) and Open WebUI (port 8080), waits until Open WebUI is ready, and configures it. The first start can take several minutes. Expected last line:

READY: http://127.0.0.1:8080

7. Verify it works

uv run --no-project --with httpx python openwebui/check.py

This asks one question through Open WebUI exactly like the chat does and prints the tool calls and the answer. Expected: one or more tool call: mimic_... lines, an answer saying that 24 female patients were admitted through the emergency room, and finally OK: the demo works end to end (exit code 0).

8. Use it

Open http://127.0.0.1:8080 in a browser (no login). The model SQL Agent · MIMIC-IV is preselected; click one of the four example questions or type your own, e.g.:

• How many female patients were admitted through the ER?

• Which 5 lab tests were performed most often, and how many times each?

• What is the average ICU stay in days for patients diagnosed with atrial fibrillation?

• What is the phone number of the doctor who treated patient 10004235? (it should decline)

Each tool call appears in the answer as an expandable block with its input and result.

9. Stop

openwebui/stop.sh

Start again any time with openwebui/start.sh (steps 3–5 are not needed again).

Notes for coding agents

• Follow the steps in order. The only thing to ask the user for is the OpenRouter API key (step 5). Write it only to .env; never print, log or commit it.

• Commands don't depend on shell state (no export needed), so running each step in a fresh shell is fine — just run them from the nl2sql folder.

• openwebui/start.sh blocks until Open WebUI is ready (up to 6 minutes on a first start). Use a long command timeout, or run it in the background and poll curl -fs http://127.0.0.1:8080/health.

• Success criterion: openwebui/check.py exits 0 and prints OK: the demo works end to end. Then tell the user to open http://127.0.0.1:8080.

• Never stop or kill other programs that use ports 8080 or 8765; set different ports instead (see Troubleshooting). openwebui/stop.sh only stops the demo's own processes.

• On failure, the scripts print an ERROR: line with the fix; logs are in ~/.local/share/nl2sql/logs/.

Troubleshooting

How the live demo works

• Open WebUI is the chat UI and runs the tool-calling loop. It talks to Gemma 4 31B through OpenRouter, pinned to full-precision (bf16) providers.

• nl2sql mcp serves the database tools over MCP: list_tables, describe_table, search_values, check_sql, run_query. The database is opened read-only and only single SELECT queries are accepted.

• openwebui/configure.py (run by start.sh) creates the SQL Agent · MIMIC-IV preset: the system prompt with the schema and answerability rules (nl2sql prompt --chat), native tool calling, the MCP tools enabled by default, the four example questions, and the provider pin. Open WebUI's own built-in tools are switched off for the preset: they would add ~27 tool definitions, and one of the Gemma providers rejects requests with more than 20.

• Open WebUI keeps its data in ~/.local/share/open-webui; logs and process IDs are in ~/.local/share/nl2sql; the project's Python environment is ~/.venvs/nl2sql (or $UV_PROJECT_ENVIRONMENT). Everything listens on 127.0.0.1 only.

• In Open WebUI the tool loop is Open WebUI's, not nl2sql.agent: same tools and prompt rules, but no self-check or forced final answer, so answers can differ slightly from the evaluated agent.

Related MCP server: Smart MCP Server

Development setup

Requires uv (Python 3.13 is installed automatically).

uv sync
cp .env.example .env                               # add OPENROUTER_API_KEY
uv run pytest -q

If the project sits in an iCloud-synced folder (e.g. macOS Desktop), iCloud marks files inside .venv as hidden and Python 3.13 skips the editable-install .pth file (import nl2sql fails). Keep the virtualenv elsewhere: export UV_PROJECT_ENVIRONMENT=$HOME/.venvs/nl2sql.

Data (not included)

The agent works with any database URL. The examples, evaluation and demos use:

Usage

# Inspect what the agent sees
uv run nl2sql schema --db sqlite:///data/mimic_iv.sqlite
uv run nl2sql schema --db sqlite:///data/mimic_iv.sqlite --table admissions

# Ask one model, or all three in parallel
uv run nl2sql ask --db sqlite:///data/mimic_iv.sqlite \
  --reference-time "2100-12-31 23:59:00" --model all \
  "How many female patients were admitted as urgent this year?"

# Postgres sample database
scripts/chinook_postgres.sh start
uv run nl2sql ask --db postgresql+psycopg://nl2sql_ro@127.0.0.1:55432/chinook --model qwen \
  "Which 5 countries generated the most revenue?"

--json runs/x.jsonl appends full results (every step, SQL, tokens, cost, provider).

Demo page

A static page showing 4 recorded runs on the MIMIC-IV demo database (question → tool calls → answer, SQL, result). Recording and page are separate, so the page never depends on a live call:

uv run --env-file .env python demo/record.py   # runs the prompts -> demo/traces.json
uv run python demo/build_page.py                           # -> demo/sql_agent_demo.html

Prompts live in demo/record.py; the per-question notes shown on the page live in demo/template.html (NOTES) — re-check them after re-recording, since they describe what the agent did in that recording.

Evaluation

Suites (fixed, stratified, seeded splits — tune on dev, run test once at the end):

BIRD databases (~1.4 GB) live outside iCloud in ~/.local/share/nl2sql/bird (NL2SQL_BIRD_ROOT to override): minidev.zip from the BIRD mini-dev README, plus the canonical question file from Hugging Face saved as mini_dev_sqlite_hf.json.

uv run nl2sql eval prepare                       # -> evals/suites/*.jsonl
uv run nl2sql eval check-gold                    # every gold query runs? matches stored answers?
uv run nl2sql eval run runs/eval/NAME --split dev --concurrency 16   # resumable
uv run nl2sql eval score runs/eval/NAME          # execution match, then LLM judge on mismatches
uv run nl2sql eval report runs/eval/NAME         # -> runs/eval/NAME/report.md

Scoring:

1. EX — gold SQL and the agent's final SQL are executed and compared as result sets (order/duplicates ignored, numbers to 4 significant figures, strings case-folded, extra columns allowed if a projection reproduces the gold result).

2. Correct — EX, or Claude Sonnet 5 (via OpenRouter) judges the answer text equivalent to the gold answer. Catches right answers with differently shaped SQL (yes/no questions, "peak month = 04" answered as "April 2013"). Judge also labels an error type.

3. Unanswerable questions are correct only if the agent returns status unanswerable.

Agent flags (Milestone 3)

--config flag1,flag2 on ask and eval run; all off = baseline. Measured with Gemma on the dev split against the baseline (fixed/broken questions out of 273; an unchanged rerun flipped 2).

Recommended: --config abstain_rule_v2,force_final,self_check — dev: BIRD 77.8%, MIMIC answerable 83.3%, MIMIC refusals 83.3% (baseline 76.5 / 81.7 / 76.7).

Compare two runs question by question: uv run nl2sql eval compare runs/eval/A runs/eval/B --model gemma. Error labels (taxonomy v2) are assigned after scoring; nl2sql eval relabel RUN redoes them.

Known benchmark noise: BIRD gold SQL sometimes contradicts its own evidence hint (e.g. bird-1482 divides by the 2012 value while the hint says 2013), and one EHRSQL stored answer contradicts its gold SQL (overridden in datasets.py).

Layout

License

MIT — see LICENSE.

Safety model

Two independent layers: the validator rejects anything but a single SELECT/WITH, and the connection itself is read-only. For Postgres/MySQL outside the demo, also connect as a role with only SELECT grants — session settings are the weakest of the three layers.

Model precision pins

OpenRouter endpoints checked 2026-09-15. Requests never fall back to lower-precision or unlabeled endpoints; if all allowed providers are down, the call fails visibly.