Autonomous SQL-Agent & MCP Server

A Python MCP server that gives an LLM schema-aware, read-only access to a SQL database, so non-technical users can ask business questions in plain English and get back the exact data table.

Built as a portfolio version of a production pattern used in real client data-pipeline work — same architecture, demo data.

Why this exists

Naively dumping an entire database schema into an LLM prompt wastes tokens and increases hallucinated column/table names on anything but a toy schema. This server instead exposes two tools and lets the model decide when to use them:

• get_db_schema(table_names=None) — called with no arguments, returns just the list of table names. Called with specific table names, returns those tables' columns, primary/foreign keys, and 3 sample rows. The model only pulls in the schema detail it actually needs for the question at hand.

• run_sql_query(query) — executes a read-only SQL query (anything that isn't a SELECT is rejected before it touches the database) and returns the result set as text.

Related MCP server: mcp-db-server

The self-healing loop

If run_sql_query returns a SQL error (bad column name, syntax issue, etc.), that error text is fed straight back to the model on the next turn instead of failing the request. The model reads the error and retries with a corrected query. agent.py caps this at MAX_RETRIES = 3 — an important edge case to flag in review, since an uncapped retry loop on a persistently malformed query would burn tokens indefinitely without ever getting a useful answer back to the user.

Architecture

User question
     │
     ▼
Claude 3.5 Sonnet (agent.py) ── system prompt instructs: always check
     │                          schema before writing SQL
     ├──> get_db_schema tool ──> server.py ──> demo.db (SQLite)
     │
     ├──> run_sql_query tool ──> server.py ──> demo.db (SQLite)
     │        │
     │        └── on ERROR, error text returned to model → retry (≤3x)
     │
     ▼
Final answer + SQL used

The demo database (db/demo.db) is SQLite for portability; server.py is written so the connection layer is the only thing that would need to change to point at PostgreSQL/MySQL in production (this project's production counterpart ran against PostgreSQL).

Evaluation methodology

eval/benchmark.json contains a set of natural-language business questions mapped to hand-written ground-truth SQL (15 questions are included in this public repo as a representative sample of the original 50-question internal benchmark).

Accuracy is measured as execution/result accuracy, not string accuracy. The generated query does not need to match the ground-truth query character-for-character — it's scored correct if executing it returns the identical result set as the ground truth. A query using a different but equivalent JOIN structure, alias names, or column order that still returns the same rows counts as correct.

Run it yourself:

pip install -r requirements.txt
python db/seed.py
export ANTHROPIC_API_KEY=sk-...
python eval/run_eval.py

This writes eval/results.json with per-question pass/fail and retry counts.

Project structure

mcp-sql-agent/
├── server.py          # MCP server: schema + query tools
├── agent.py            # Claude orchestration + self-healing retry loop
├── db/
│   ├── schema.sql       # demo retail schema
│   └── seed.py          # generates db/demo.db with 4,000 synthetic orders
├── eval/
│   ├── benchmark.json    # sample text-to-SQL evaluation set
│   └── run_eval.py       # runs the benchmark, computes execution accuracy
└── requirements.txt

Honest limitations

• The retry loop can still get stuck retrying the same category of error if the model misdiagnoses the root cause — the cap prevents runaway cost, but doesn't guarantee eventual success.

• run_sql_query blocks non-SELECT statements at the string level (regex on the leading keyword). A production deployment against a real database should additionally run the connection itself under a read-only DB role, rather than relying on the guard in this layer alone.

• The demo dataset is synthetic; the accuracy figure is meaningful for this benchmark and schema, not as a general text-to-SQL benchmark claim.