Skip to main content
Glama
sql-analyst
by Yehiak
README.md
# sql-analyst-agent

Ask a business question in English or Arabic. The agent writes SQL, checks that it is a single
read-only query, runs it, fixes it if it fails, and explains the result with a chart.

![An Arabic question about monthly revenue: the agent's steps appear one by one, then the answer and a line chart](docs/demo.gif)

<sub>The GIF is shortened while the model works; the real time (18.5 s, local model) is shown under the answer.</sub>

## Results

Execution accuracy: the share of questions where the agent's query returns the same result as the
reference query. Qwen: mean of 3 runs (min–max). Claude: 1 run.

| Model | Dataset | **Agent** | Agent with table selection | One-shot baseline |
|---|---|---|---|---|
| Qwen 3.8 27B (local) | BIRD mini-dev, 150 questions | **40.0** (38.7–41.3) | 39.3 (38.0–41.3) | 38.9 (38.0–40.0) |
| Qwen 3.8 27B (local) | Olist, 50 questions × EN + AR | **90.7** (90.0–91.0) | 90.7 (89.0–94.0) | 88.0 (87.0–90.0) |
| Claude Opus 5.5 | BIRD mini-dev, 150 questions | **93.3** | 94.0 | 90.7 |
| Claude Opus 5.5 | Olist, 50 questions × EN + AR | **98.0** | 88.0 | 98.0 |

- **The agent beats the one-shot baseline by 1 to 3 points, or ties it.** None of these
  differences is statistically significant at this size. A paired McNemar test, question by
  question, gives p = 0.29 at best (Qwen on Olist: 6 questions only the agent gets right, 2 only
  the baseline).
- **Most of the gain comes from self-correction.** When the first query failed, Claude's agent
  recovered every time (a timeout or an error became a correct query). Qwen recovered 100% on
  Olist and 19% on BIRD.
- **Picking tables first did not help on these schemas and once cost 10 points.** Claude's
  table-selection step kept leaving out `orders`, which holds the order status that the revenue
  definition filters on. The agent now sends the whole schema when it fits the prompt (it always
  does here: at most about 11,000 characters) and selects tables only for bigger databases.
- **Arabic matches English:** Qwen 90.7 / 90.7, Claude 98 / 98 with the default agent.
- **The baseline is a fair check on BIRD.** The BIRD mini-dev page reports 90.0% for Claude Opus
  5.5 on all 500 questions; our one-shot baseline gets 90.7% on our 150.

| Default agent, per question | Qwen BIRD | Qwen Olist | Claude BIRD | Claude Olist |
|---|---|---|---|---|
| First query failed | 4.7% | 2.0% | 3.3% | 0% |
| …then recovered and correct | 19% | 100% | 100% | – |
| Refused the question | 0.4% | 0% | 0% | 0% |
| Answers with a number not in the result | – | 0% | – | 0% |
| Answers rewritten once to fix a number | – | 3% | – | 5% |
| Median latency | 13.6 s | 15.9 s | 4.8 s | 5.1 s |
| Tokens | 3,005 | 3,025 | 4,170 | 5,186 |
| Cost | free (local GPU) | free | $0.021 | $0.021 |

Olist runs include writing the answer; BIRD runs stop after the query. Qwen runs on a 16 GB
consumer GPU with the 17 GB model partly offloaded to the CPU, which is why it is slow.

**Safety.** The guard blocks all 107 queries in the red-team suite (writes, `ATTACH`, file and URL
reads, settings, stacked statements, comment and case tricks, resource abuse). The read-only
database connection alone, with the guard switched off, also blocks 79 of them: a second layer.
During the evaluation runs the guard stopped 8 model-written queries, all on BIRD with Qwen. The 3
whose reason was kept in the final record were model mistakes: a table alias with no table behind
it, and SQLite's `json_each()`, which the guard doesn't allow.

Full numbers: [docs/results/qwen.json](docs/results/qwen.json), [docs/results/claude.json](docs/results/claude.json),
[docs/redteam.json](docs/redteam.json).

## How it works

```mermaid
flowchart LR
    Q[Question<br/>EN or AR] --> R[Read schema<br/>whole, or picked tables]
    R --> D[Draft SQL<br/>LLM, JSON reply]
    D -->|refusal| X[Explain why not]
    D --> V[Guard<br/>sqlglot]
    V -->|unsafe or invalid| D
    V --> E[Run<br/>read-only, row cap, timeout]
    E -->|error or timeout| D
    E --> A[Answer + chart spec<br/>numbers checked]
    D -.->|3 failed tries| F[Stop and report]
```

The loop is a [LangGraph](https://langchain-ai.github.io/langgraph/) state graph. Each box is a
node; the arrows back to *Draft SQL* are conditional edges that carry the failed SQL and the error
message, so the next draft can fix it (at most 2 retries). The web UI streams one event per node,
so you watch the agent work.

- **Schema context.** Olist ships with hand-written docs: what each table is, column meanings,
  joins, and business definitions (revenue is item price without freight, over orders that were
  not canceled). For databases without docs, such as BIRD, each column gets its three most common
  values, so the model knows how values are spelled (`'K-12'`, not `'K12'`).
- **The guard.** sqlglot parses the query into a tree. It must be exactly one SELECT (or
  WITH … SELECT) over this database's tables, with no denied functions (files, URLs, settings,
  extensions). The query that runs is the one the guard re-renders from that tree, with a row
  limit added, never the model's text.
- **The connection.** DuckDB is opened read-only, with external access, extension loading and
  settings changes locked. SQLite is opened read-only with an authorizer that allows only reads.
  Queries have a timeout.
- **The answer.** A second call explains the result in the question's language and proposes a
  chart (bar, line, pie or none). Every number in the answer must appear in the result (allowing
  for rounding, percentages and "1.2 million"). If one doesn't, the answer is rewritten once. The
  chart spec is checked against the columns, so a chart can't plot a column that doesn't exist.

### Why these tools

| Tool | Why |
|---|---|
| LangGraph | The retry loop is explicit: state, nodes and conditional edges you can test one by one and stream. No hidden prompt chains. |
| DuckDB | Embedded and columnar: fast group-bys on 100k orders without running a server, and it can be opened read-only with external access locked. |
| sqlglot | Parses SQL into a tree in the database's own dialect, so the guard checks structure (statement type, tables, function names) instead of matching text. |
| SQLite | BIRD ships SQLite databases; running them as SQLite keeps the benchmark's own dialect. |
| Ollama + Qwen 3.8 27B | Free, local, and seedable, so 3 runs per setup cost nothing and can be repeated. |
| Claude Opus 5.5 | A strong reference that shows how much is the model and how much is the agent. |
| MCP | Any MCP client (Claude Desktop, an IDE, another agent) can use the database through the same guard. |
| FastAPI | Typed request checks and a streaming response for the step-by-step UI. |
| Next.js + Recharts | A right-to-left UI with charts. In Arabic, the x axis runs right to left and the value axis sits on the right. |

## Get the data

Data is never committed. Both datasets are downloaded by the CLI into `data/`.

**Olist** (Brazilian e-commerce, about 100k orders from 2016–2018, 9 tables). Create a Kaggle API
token, put it in `.env` as `KAGGLE_API_TOKEN=...` (see `.env.example`), then:

```bash
uv run sql-analyst get-olist
```

Or download the zip from the [Kaggle page](https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce)
yourself and run `uv run sql-analyst get-olist --zip path/to/archive.zip`. Either way it builds
`data/olist/olist.duckdb`. A test checks that the schema docs match the built database.

**BIRD mini-dev** (only for the benchmark; about 800 MB):

```bash
uv run sql-analyst get-bird
```

This downloads a pinned revision of the questions (checked by SHA-256) and the SQLite databases.

## Install and use

Needs [uv](https://docs.astral.sh/uv/) and Python 3.11+. For the local model, install
[Ollama](https://ollama.com) and pull a model (`ollama pull qwen3.8`). Any model that can return
JSON works; set `SQL_ANALYST_MODEL`.

```bash
uv sync --extra mcp --extra api
```

**Command line**

```bash
uv run sql-analyst ask "ما هي الإيرادات الشهرية في عام 2017؟" --chart revenue.png
```

```bash
uv run sql-analyst ask "Which 5 sellers had the most revenue?" --json
```

**Web UI** (two terminals):

```bash
uv run sql-analyst serve
```

```bash
cd web && npm install && npm run dev
```

Open http://localhost:3000. The API listens on 127.0.0.1 only and accepts questions, never SQL.

**MCP server.** Add this to your MCP client's config (for Claude Desktop, `claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "sql-analyst": {
      "command": "uv",
      "args": ["run", "--directory", "/path/to/sql-analyst-agent", "--extra", "mcp", "sql-analyst", "mcp"],
      "env": { "SQL_ANALYST_LLM": "ollama" }
    }
  }
}
```

| Tool | What it does |
|---|---|
| `list_tables` | Tables with a one-line description |
| `describe_table` | Columns, types, meanings and common values |
| `run_sql` | Runs one checked, read-only SELECT that the client wrote; a refusal says why |
| `ask_data` | Runs this agent end to end; returns the answer, SQL, rows and a PNG chart |

Resources: `schema://overview`, `schema://definitions`. Prompt: `analyse`.

**Claude instead of Ollama.** It makes paid calls, so it needs two settings: `SQL_ANALYST_LLM=anthropic`
and `SQL_ANALYST_ALLOW_PAID=1`, plus `ANTHROPIC_API_KEY` in the environment. A spending cap
(`SQL_ANALYST_BUDGET_USD`, default $1) stops the process before it goes over.

**Your own database.** Set `SQL_ANALYST_DB` to a `.duckdb` or `.sqlite` file (and optionally
`SQL_ANALYST_DOCS` to a schema-docs YAML in the format of `src/sql_analyst/db/schema_docs.yaml`).

**As a library**

```python
from sql_analyst.service import Analyst

analyst = Analyst()  # Olist + Ollama by default; or Analyst(db=..., llm=...)
result = analyst.ask("What share of orders arrived late?")
print(result.sql, result.rows, result.answer, result.chart)
```

## Reproduce the evaluation

```bash
uv run sql-analyst eval olist --system agent --runs 3
```

```bash
uv run sql-analyst eval bird --system baseline --runs 3
```

```bash
uv run sql-analyst report runs/*.jsonl --json docs/results/qwen.json
```

`--system` is `agent`, `agent-table-selection` or `baseline`. Runs resume where they stopped.
Paid models need `--llm anthropic --yes-paid --budget <usd>`.

**What is measured**

- **BIRD:** 150 of the 500 mini-dev questions (SQLite version), sampled in proportion to each
  database with a fixed seed ([eval/bird_subset.json](eval/bird_subset.json)). This revision labels
  92% of its questions "challenging". The BIRD hint is given with each question. Scoring is BIRD's
  own rule: the two results are equal as sets of rows.
- **Olist:** 50 questions written for this project, each in English and Arabic, with a reference
  query ([eval/olist_questions.yaml](eval/olist_questions.yaml)): 15 easy, 22 medium, 13 hard.
  Scoring is more lenient than BIRD's, because a business answer has more than one correct shape:
  - extra columns in the agent's result are allowed;
  - row order matters only when the question asks for an order;
  - numbers may differ by 0.1%, but counts must match exactly;
  - a share may be written as 0.58 or 58%;
  - two questions accept a second reference query, because day counts can be taken as whole or
    fractional days.
- **Runs:** Qwen at temperature 0.7 with seeds 1–3, Claude 1 run (it has no seed). The timeout is
  30 s per query, and a result cut off by the row cap counts as wrong.
- **Faithfulness:** a number in the answer that is not in the result (after rounding,
  percentages and scale words) counts as unsupported.

## Limits

- **Small samples.** 150 BIRD questions give a margin of about ±4 points; Claude was run once.
  Read the agent-vs-baseline gaps as "the agent does not hurt and sometimes helps", not as a
  proven gain.
- **The Olist questions and their reference queries were written by the same person who built
  the agent.** They follow the schema docs' definitions, which helps both the agent and the
  baseline.
- **Prompts differ slightly from the official BIRD setup.** Example column values are added to the
  schema (BIRD's own baseline prompt also includes descriptions and examples). Qwen's reply has a
  short "reasoning" field to plan in. Claude's doesn't: in a pilot most Claude requests that
  asked for that field were declined, and removing it stopped the declines.
- **Only small schemas were measured.** Table selection is kept for schemas over about 24,000
  characters, but it hasn't been evaluated on one, and on these schemas it never helped.
- **The number check proves a number came from the result, not that it is attached to the right
  label.** A wrong month next to a right number would pass.
- **Prompt injection.** Text stored in the database reaches the answer step. The prompt tells the
  model to treat it as data. The model can only ever run checked read-only queries, so the worst
  case is a misleading answer, not a changed database or a leaked file.
- **No authentication.** The API and MCP server are meant for one person on their own machine.
  The API binds to 127.0.0.1 and allows only the web UI's origin. Put it behind auth before
  exposing it.
- **The function denylist can miss a new function.** A new DuckDB release could add one that reads
  files, which is why the read-only connection with external access locked is a second, independent
  layer.
- **Arabic means Modern Standard Arabic.** Dialect questions weren't tested.
- **Two measurement bugs were found and fixed during the evaluation; affected records were redone.**
  The guard wrongly blocked SQLite's `GLOB` operator, which cost 7 baseline records; they were rerun
  with the same seeds. The number check misread Arabic text where «و» ("and") is attached to a number;
  answers were rechecked against their stored queries.

## Project layout

```
src/sql_analyst/
  agent.py        LangGraph loop (also streams one event per step)
  guard.py        sqlglot checks: one read-only SELECT, known tables, denied functions, row cap
  executor.py     read-only DuckDB and SQLite connections, timeouts
  context.py      schema text for prompts (docs or sampled example values)
  retrieval.py    table selection for big schemas (keywords + LLM, Arabic-aware)
  checks.py       answer number check
  chart.py        chart spec checks;  render.py  PNG charts (Arabic shaped right to left)
  service.py      shared database + agent for the API, MCP server and other code
  api.py          FastAPI app;  mcp_server.py  MCP server
  evaluation/     comparators, data loading, runner, report
  llm/            Ollama and Anthropic adapters (spending cap, refusal circuit breaker)
web/              Next.js UI (Arabic right to left, or English)
eval/             Olist questions, BIRD subset ids
scripts/          red-team report, demo recording
tests/            373 tests, no network or model needed
```

## Data and credits

- **Olist:** [Brazilian E-Commerce Public Dataset by Olist](https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce),
  CC BY-NC-SA 4.0. Non-commercial use, with attribution. It is not redistributed here.
- **BIRD:** [BIRD mini-dev](https://github.com/bird-bench/mini_dev), CC BY-SA 4.0. Li et al., *Can LLM
  Already Serve as a Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs*,
  NeurIPS 2023. Not redistributed here.
- **Software:** LangGraph, DuckDB, sqlglot, the MCP Python SDK, FastAPI, Next.js, Recharts,
  matplotlib, arabic-reshaper and python-bidi, under their own licenses.

## License

MIT. See [LICENSE](LICENSE). The license covers this code only, not the datasets.

TDQS

A4/5.0

Scored across 4 tools

Disambiguation4/5

list_tables and describe_table are clearly separated (breadth vs. column detail), and run_sql vs. ask_data differ by input type (raw SQL vs. natural-language question). The one soft overlap is that ask_data also ends up running SQL, so an agent may over-rely on it and skip run_sql, but the descriptions make the intended split explicit.

Naming Consistency5/5

All four names follow a verb_noun snake_case pattern: list_tables, describe_table, run_sql, ask_data. The verbs are distinct and descriptive, with no mixed conventions or casing.

Tool Count5/5

Four tools is lean but each has a distinct role: discovery (list/describe), direct execution (run_sql), and delegated analysis (ask_data). Nothing looks redundant or padded for a read-only SQL analyst server.

Completeness4/5

Schema discovery plus query execution plus a natural-language fallback covers the core read-only analytics lifecycle with no dead ends. Minor gaps exist: no way to search across schema objects or preview sample rows outside describe_table's few values, and no explain/plan tool for debugging slow queries.

Maintenance

ActivityMaintained
ResponsivenessNo recent community issues