NL-to-SQL — MCP + GraphRAG

Natural-language querying over a SQLite database (the Chinook music-store sample: artists, albums, tracks, genres, customers, invoices, employees, playlists — 11 tables, 11 FK relationships), built around two independent guardrail layers, a schema-graph retrieval step, and an LLM-driven generation pipeline.

Two ways to use this

This repo is actually two separate products sharing the same guardrail and retrieval code:

1. MCP server (server/main.py) — exposes list_schemas, get_table_metadata, retrieve_relevant_schema, and execute_safe_query as tools for any MCP client (Claude Desktop, Claude Code). In this mode, the connected LLM does its own reasoning about which tables/SQL to use — our code only provides schema discovery, GraphRAG-assisted retrieval, and guardrailed execution. No OpenAI calls happen in this path.

2. Standalone pipeline (pipeline/pipeline.py) — a fully self-contained 5-stage NL-to-SQL system (Plan → Retrieve → Generate → Validate → Execute) that uses the OpenAI API directly. This is what the web app and CLI script call; Claude Desktop is not involved at all in this path.

These don't talk to each other — pick whichever fits how you want to interact with the database.

Architecture

                    ┌─────────────────────────────────────┐
                    │         Schema Graph (NetworkX)      │
                    │  tables + columns as nodes,          │
                    │  FKs as edges — built once,          │
                    │  persisted to GraphML                │
                    └───────────────┬───────────────────────┘
                                    │
              ┌─────────────────────┼─────────────────────┐
              │                     │                     │
   ┌──────────▼─────────┐  ┌────────▼────────┐  ┌─────────▼──────────┐
   │     MCP Server      │  │  Pipeline (CLI)  │  │   Web App (FastAPI) │
   │  4 tools, stdio      │  │  scripts/        │  │  web/app.py          │
   │  Claude Desktop/Code │  │  run_pipeline.py │  │  + static HTML/JS    │
   └──────────┬───────────┘  └────────┬─────────┘  └─────────┬────────────┘
              │                        └──────────┬───────────┘
              │                                    │
              │                          pipeline/pipeline.py
              │                    Plan → Retrieve → Generate → Validate → Execute
              │                                    │
              └──────────────────┬─────────────────┘
                                   │
                      ┌────────────▼────────────┐
                      │   Guardrail layer         │
                      │  sql_guard.py (AST allow-  │
                      │  list) + read-only conn +  │
                      │  timeout + row cap         │
                      └────────────┬────────────┘
                                   │
                            db/chinook.db (SQLite)

Guardrails

Security is layered, not a single check:

• AST-based SQL validation (server/sql_guard.py) — allowlist, not keyword blocklist. Parses generated SQL with sqlglot; only accepts a single SELECT/UNION/INTERSECT/EXCEPT statement, every table reference must exist in the live schema (CTE aliases correctly excluded), load_extension/pragma_* calls blocked, and a LIMIT is always injected/clamped to 1000 rows by rewriting the AST — never trusting whatever the caller or LLM wrote.
• Read-only connection (server/query_executor.py) — SQLite opened via mode=ro URI, an independent backstop at the driver level even if a write somehow passed AST validation.
• Timeout — a progress-handler wall-clock timeout aborts long-running scans instead of blocking.
• Plan-stage scope filtering (pipeline/plan_stage.py) — an LLM call classifies whether a question is in-scope before any SQL generation is attempted. This is a quality/cost filter, not the security boundary — the AST validator is what actually stops unsafe SQL regardless of what Plan decides.

Multi-database support

The web app isn't limited to the bundled Chinook demo. Switch to "Upload your own" and provide either:

• a SQLite .db file — used as-is, no conversion
• a MySQL or Postgres SQL dump (.sql) — transpiled to SQLite statement-by-statement via sqlglot (web/db_import.py) and imported into a fresh, isolated per-session database

Each upload gets its own session (web/sessions.py): its own SQLite file, its own schema graph, expiring after 24h, never touching the shared demo DB or another session's data. You must supply your own OpenAI API key to query an uploaded database — it's used only for that session's requests and never persisted to disk; the bundled demo continues to use the server's own key from .env, unaffected.

Import is deliberately best-effort, not all-or-nothing: dump syntax with no SQLite equivalent (MySQL's inline KEY/INDEX clauses, Postgres's CREATE SEQUENCE/CREATE EXTENSION, session-config SET statements, schema qualifiers like public.customers which SQLite would otherwise misparse as a cross-database reference) is detected and skipped, with a report of what was skipped — not an opaque failure over one unsupported statement. Capped at 20MB per upload; larger/async imports are a v2 concern.

Setup

python -m venv .venv
.venv/Scripts/pip install -r requirements.txt      # Windows
# .venv/bin/pip install -r requirements.txt         # macOS/Linux

cp .env.example .env      # then edit .env with your real OPENAI_API_KEY

python scripts/build_graph.py    # build graphrag/schema_graph.graphml from db/chinook.db

Running it

MCP server (for Claude Desktop/Code — see .mcp.json, already configured for this project):

python server/main.py

Web app:

uvicorn web.app:app --reload
# open http://127.0.0.1:8000

CLI:

python scripts/run_pipeline.py "Which artist has the most albums?"
python scripts/query_graph.py "your question"     # GraphRAG retrieval only, no LLM

Testing

# Free, deterministic (no API calls):
python tests/test_sql_guard.py              # 21 adversarial/legit SQL cases
python tests/test_query_executor.py         # timeout, read-only backstop, row cap
python tests/test_retrieval.py              # GraphRAG retrieval regression cases
python tests/test_pipeline_stages_offline.py

python tests/test_db_import.py              # SQLite/MySQL/Postgres import + transpilation

# Real API calls (needs OPENAI_API_KEY):
python tests/test_plan_stage.py
python tests/test_generate_stage.py
python eval/run_eval.py                     # full 40-question golden set

Eval harness

eval/run_eval.py grades on executed results, not SQL text — two differently-written queries can both be correct, so it compares the pipeline's output rows against a hand-verified reference query's output (value-subset matching, tolerant of extra descriptive columns and float precision). 30 legitimate questions (counts, sums, joins, self-joins, nullable-FK handling, literal lookups) plus 10 adversarial (out-of-scope, prompt injection, destructive intent) — currently 40/40.

The first real eval run caught two genuine bugs (not eval-harness artifacts): Plan being too conservative about a self-referencing FK relationship it had no schema access to verify, and Generate grouping by a non-unique display column (Playlist.Name — two different playlists share that name in Chinook) instead of the primary key, silently merging distinct rows. Both are fixed in the current prompts.

Wired into .github/workflows/eval.yml: deterministic tests run first (fail fast, free), then the eval harness (needs an OPENAI_API_KEY repo secret), with the report uploaded as a CI artifact.

Deployment

docker build -t nl2sql-mcp-graphrag .
docker run -p 8000:8000 --env-file .env nl2sql-mcp-graphrag

render.yaml is a Render blueprint (Docker runtime, free tier) — set OPENAI_API_KEY in the Render dashboard after connecting the repo, it's intentionally not committed. Note: the Docker build hasn't been verified in this environment (no Docker available) — test docker build . locally before deploying.

Known limitations / v2

• No multi-tenancy — no row-level access control; deferred deliberately to keep v1 scoped.
• No schema-drift detection — scripts/build_graph.py must be re-run manually after a schema change; no polling/webhook invalidation.
• Lexical/fuzzy schema matching, not embeddings — free and deterministic, but can't bridge true vocabulary gaps beyond the small synonym map in graphrag/matching.py (e.g. "revenue" → Invoice.Total is hardcoded, not learned). Swap-in point is match_schema_nodes() if eval ever shows this as a bottleneck.
• Retrieval is high-recall, not high-precision — an FK column like Invoice.CustomerId legitimately contains the word "customer", so simple questions can pull in more tables than strictly needed. Generate has so far proven robust to this noise (see Phase 4 testing), but it's a known tradeoff, not a solved problem.
• Dump transpilation isn't guaranteed complete — best-effort, with a skip report for unsupported syntax (stored procedures, triggers, engine-specific types beyond what's already handled). Complex enterprise dumps may import partially. No background/async import, so upload is capped at 20MB and blocks the request until done.
• No accounts — sessions are anonymous and expire after 24h; there's no way to return to an uploaded database later or share it across devices.

Project structure

db/            chinook.db (SQLite sample data)
server/        MCP server + AST guardrails (sql_guard.py, query_executor.py)
graphrag/      NetworkX schema graph, fuzzy matching, retrieval
pipeline/      5-stage LLM pipeline (plan/retrieve/generate/validate/execute)
web/           FastAPI app + static frontend + multi-DB upload/import/sessions
eval/          golden question set, grader, eval runner
scripts/       CLI entrypoints (build_graph, query_graph, run_pipeline, inspect_schema)
tests/         deterministic test suites