SQL Analyst Agent

Ask "which city made the most revenue last quarter?" in plain English and get a sentence with the number in it. The agent inspects the real database schema, writes its own SQL, runs it, reads the error if it fails, corrects it once, and explains the result.

The database is read-only by three independent layers of code — a parser-level allowlist, a SQLite authorizer and a read-only file handle — each sufficient on its own to stop a write. 43 offline tests fire nine known bypass attempts at all three layers and at the live tool server running as a subprocess, then assert the database is unchanged. No API key, no network.

Python 3.11 · LangGraph · Claude (Anthropic Messages API) · Model Context Protocol (FastMCP) · SQLite · sqlparse · pytest

One of three portfolio projects on applied AI for analytics. BigQuery SQL Agent — natural language to guarded BigQuery SQL, with a six-stage safety pipeline · SQL Analyst Agent (this repo) · RAG Document QA — cited answers over a PDF corpus, with a measured evaluation harness.

Overview

Most "text-to-SQL" demos are a single prompt that returns a query string. This is a working agent: the loop is a LangGraph state machine, the database tools are served over MCP as a separate process, and the read-only guarantee is enforced in code rather than by asking the model nicely.

The dataset is 1,000 rows of synthetic supermarket transactions — two branches, three Australian cities, five products, $118,583.90 of revenue. It is small on purpose: the interesting problems are the agent loop and the safety boundary, not the data volume.

Related MCP server: sqlite-mcp-server

Business problem

In most companies the data lives behind a skill gap.

A store manager wants to know whether members really do spend more than non-members. A category buyer wants last month's top five products. A regional lead wants revenue split by city. None of these questions are hard — each is about four lines of SQL — but none of the people asking write SQL.

So the questions queue up at the one or two people who do. Those analysts spend a large share of their week on requests that are individually trivial and collectively enormous, while the requester waits a day or two for a number they needed in a meeting that morning. The analyst does less analysis; the business decides on instinct because the number arrived too late to matter.

The obvious fix — give everyone SQL access — trades one problem for a worse one: non-experts writing queries against production, where a mistyped statement can delete data.

The problem this project addresses: let anyone ask a data question in plain English and get a trustworthy answer in seconds, without giving anyone the ability to change the data.

What it produces

On the outputs below. Every SQL statement and every table was executed against data/sales.db and the figures are real. The prose sentences show the answer format the agent is instructed to produce; no live agent transcript is published here, because capturing one requires an API key.

One sales table, twelve columns: sale_id, branch, city, customer_type, gender, product_name, product_category, unit_price, quantity, tax, total_price, reward_points.

Q: "Which city generated the most revenue?"

SELECT city, ROUND(SUM(total_price), 2) AS revenue
FROM sales GROUP BY city ORDER BY revenue DESC;

Q: "Do members spend more per transaction than normal customers?"

SELECT customer_type, COUNT(*) AS transactions,
       ROUND(AVG(total_price), 2) AS avg_basket
FROM sales GROUP BY customer_type;

Members average $122.51 per transaction against $114.40 — about 7.1% higher.

Q: "Delete all rows from sales" — the model refuses and explains that the database is read-only. And if it did not refuse, the server would still reject the query:

SQL Error: Only SELECT statements are permitted; this is DELETE statement.
This database is strictly read-only.

Q: "What is our profit margin by category?" — the dataset has no cost column, so there is no honest answer. The agent is instructed to name the gap rather than approximate a margin from tax.

Safety model: three layers

The agent is an LLM: it can be argued with, and a question containing instructions could try to talk it into a destructive query. So nothing depends on the model behaving. Every layer below lives in the tool server, across the process boundary, and each alone is sufficient to stop a write.

Layer 1 — a SELECT allowlist (guardrails.py). The query is parsed with sqlparse, comments are stripped, and the statement is refused unless it is exactly one statement whose type is SELECT.

This is an allowlist, not a denylist, and that distinction is the whole lesson of the project. The implementation this replaced checked query.strip().upper().startswith(("INSERT", "UPDATE", "DELETE", ...)). Every row below defeats that check, and every row is now a test case:

An allowlist fails closed: anything unrecognised is refused by default, so the next exotic statement type nobody thought of is blocked too.

Layer 2 — a SQLite authorizer (guardrails.py). sqlite3.Connection.set_authorizer installs a callback the engine consults before every action it takes. This one returns SQLITE_DENY for everything except SQLITE_SELECT, SQLITE_READ, SQLITE_FUNCTION and SQLITE_RECURSIVE (the last is what makes the read-only WITH RECURSIVE work). Functions that can reach outside the database, such as load_extension, are denied by name. This layer sits below the SQL text, inside the engine, so it does not care how the statement was spelled or whether a parser was fooled.

Layer 3 — a read-only connection. The database is opened as sqlite3.connect("file:sales.db?mode=ro", uri=True). The file handle itself cannot write. Even with layers 1 and 2 removed, SQLite refuses with attempt to write a readonly database.

What the audit found

The safety model above is the output of an audit of an existing implementation, documented in docs/failure-modes.md. Eleven defects were found, empirically proved and fixed. The three that mattered:

Dead code that looks like a safety control is worse than no safety control, because it stops you looking. The organising principle that came out of the audit: the prompt shapes behaviour, code constrains it.

Verified, not asserted

tests/test_guardrails.py fires all nine bypasses at the parser, at the authorizer, and at the real MCP server running as a subprocess, then asserts the table list and row count are unchanged. It also confirms that ordinary analyst SQL — CTEs, UNION, subqueries, trailing semicolons — still works, since a guard that blocks legitimate queries is a broken guard. The server never writes to the database at all; building it is the separate, deliberate job of scripts/build_db.py.

How it works

flowchart TD
    U["User question"] --> CM["call_model<br/>Claude, Anthropic Messages API"]
    CM -- "no tool calls" --> E(["END"])
    CM -- "tool_use detected" --> ET["execute_tools"]
    ET -- "results appended to history" --> CM
    subgraph S ["FastMCP tool server — the security boundary"]
        direction TB
        T1["get_schema() → CREATE TABLE statements"]
        T2["run_sql(query) → up to 100 rows"]
        L1["Layer 1 · SELECT allowlist, sqlparse"]
        L2["Layer 2 · SQLite authorizer"]
        L3["Layer 3 · read-only connection, mode=ro"]
        T2 --> L1 --> L2 --> L3
    end
    ET --> T1
    ET -- "MCP over stdio" --> T2
    L3 --> DB[("data/sales.db")]

A typical run: call_model returns a tool_use block for get_schema; execute_tools fetches the real CREATE TABLE statement over MCP and appends it to history; call_model, now grounded in the actual schema, emits run_sql; the server validates it through all three layers and returns rows; call_model returns text rather than a tool call, and should_continue routes to END.

If a query errors, the result is marked is_error and the retry counter increments. On the second failure the tool returns "SQL retry limit exceeded" and the model explains rather than loops. The loop terminates when the model returns text, or when MAX_MODEL_TURNS = 8 is reached — whichever comes first.

Two decisions carry most of the weight. The tools live in a separate process behind MCP, so the safety rules hold regardless of what the model is persuaded to ask for, and get_schema / run_sql are reusable by any MCP client. The LLM client and tool runner are injected into build_graph(...), so the whole agent runs in tests against stubs and importing any module makes no API call.

Tech stack

LangGraph (StateGraph) gives typed state, explicit routing and a termination condition unit-testable in isolation. Claude is reached through the Anthropic Messages API directly, so the agent speaks the native tool-use protocol rather than an abstraction over it. FastMCP over stdio puts the database behind a process boundary. SQLite needs zero setup and its authorizer API is a genuinely strong read-only mechanism, and sqlparse parses statements so the guard can allowlist SELECT rather than blocklist keywords.

Installation and setup

Requires Python 3.11+.

git clone https://github.com/vk4868/sql-analyst-agent.git
cd sql-analyst-agent

python -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scripts\activate

pip install -r requirements.txt   # pinned dependencies
pip install -e .                  # the package lives under src/, so it needs installing

python scripts/build_db.py        # only the CSV is committed; the .db is generated
pytest                            # 43 passed — no API key needed

The editable install is what makes python -m sql_agent and the sql-agent console script resolve; build_db.py refuses to overwrite an existing database unless you pass --force. Running the agent (but not the tests) additionally needs an API key from console.anthropic.com in .env — copy .env.example and set ANTHROPIC_API_KEY.

Usage

python -m sql_agent "What are the top 5 products by total revenue?"

# final answer only, without the step-by-step trace
python -m sql_agent --quiet "Do members spend more per transaction than non-members?"

# the console script is equivalent
sql-agent "Which city generated the most revenue?"

# the MCP tools on their own, for any MCP client
python -m sql_agent.server

Both entry points work from any directory — all paths are resolved relative to the package, not the shell's working directory.

Testing

pytest

43 passed

All 43 tests run offline: no API key, no network, no skips, and GitHub Actions runs them on every push and pull request.

tests/test_guardrails.py fires nine bypass attempts at the parser, the authorizer and the real MCP server spawned as a subprocess, then checks the table list and row count unchanged — and confirms legitimate analyst SQL (CTEs, UNION, subqueries, trailing semicolons) still runs. tests/test_agent_graph.py executes the compiled graph end to end against a stub LLM: tool-use routing, schema caching in state, the retry budget, the turn cap, and structured failure when the budget is exhausted.

The guardrail tests build their own throwaway SQLite database in a temporary directory, so the suite never touches data/sales.db and CI needs no generated data.

Skills demonstrated

Business analysis — framing the analyst bottleneck and the cost of the obvious fix before writing code; identifying read-only access as the constraint that makes self-service acceptable; specifying explicit behaviour for questions the schema cannot answer.

SQL and analytics — schema-grounded query generation, aggregation and GROUP BY design, CTEs and subqueries, and correct interpretation of ambiguous questions such as "do members spend more" (per transaction, not in total).

Data quality and governance — defence in depth across a parser, an engine authorizer and a file handle; an allowlist that fails closed; adversarial regression tests as the evidence; a documented audit of eleven defects with empirical proof for each.

Stakeholder communication — consistent currency, date and percentage formatting; the agent instructed to name a missing column rather than estimate around it; a README that states the limitations as plainly as the capabilities.

Agent engineering — a LangGraph StateGraph with typed state, conditional routing and a bounded tool-use loop; the Anthropic tool protocol handled directly (tool_use / tool_result blocks, is_error propagation, retry budgets); FastMCP over stdio as a process boundary rather than decoration.

Testing and code auditing — dependency injection so the whole agent runs against stubs; 43 offline tests including a live subprocess integration test; eleven defects found, proved and fixed, documented in docs/failure-modes.md and docs/prompt-iteration.md.

Known limitations and what's next

• No automated evaluation of answer correctness. The guardrails prove the agent cannot change anything; they say nothing about whether its SQL expresses the question, and a subtly wrong GROUP BY produces a confident, well-formatted, wrong number. The fix is 30–50 questions with known-correct answers scored in CI — the next thing to build.

• No human-in-the-loop approval step. The agent executes its query immediately, which is acceptable only because the database is read-only at three levels, so the worst outcome is a wrong answer rather than damaged data. Because the loop is a state machine, an approval gate is a new node between call_model and execute_tools, not a rewrite.

• One table, one small SQLite file. No cross-table joins are exercised and nothing has been tested at warehouse scale. The same MCP interface would front Postgres or BigQuery, with the read-only guarantee implemented as a database role instead of a connection flag.

• Prompt injection through data is unmitigated. Row values reach the model as text, so instruction-shaped text in a row could influence the answer. The blast radius is a misleading response, not a write; mitigation means delimiting and escaping tool output before it re-enters the prompt.

• Single-turn, with no cost accounting. Follow-ups like "and by city?" do not work, and only turns are bounded — not tokens or spend. A LangGraph checkpointer adds memory; per-run token accounting is the companion change.

Licence

MIT — see LICENSE. The dataset is synthetic and contains no real customer information.

Author

Vineet Kumar — Melbourne, Australia.

Master of Business Analytics (Specialisation in Artificial Intelligence), Deakin University, July 2026. Previously Technical Analyst at Country Delight, analysing operational sensor data from across India in SQL, Excel and Tableau, mapping business workflows and writing BRDs with cross-functional teams. Australian work rights (Temporary Graduate visa, subclass 485).

Open to Business Analyst, Data Analyst, AI Analyst, AI Automation Analyst and Analytics Consultant roles.

• LinkedIn — https://www.linkedin.com/in/vineetkumar-

• BigQuery SQL Agent — natural language to guarded BigQuery SQL

• SQL Analyst Agent — a LangGraph agent over a read-only database

• RAG Document QA — cited answers over a PDF corpus