MCP SQLite Analyst

A read-only SQL analysis server for LLM agents, built on the official Model Context Protocol Python SDK. It lets an AI assistant such as Claude explore and query a SQLite database through four purpose-built tools, while a defense-in-depth sandbox guarantees the agent can never modify the data.

This repository is the public, runnable demonstration of my "MCP AI Data Analyst" project (originally built against PostgreSQL); SQLite is used here so anyone can clone the repo and have a working, queryable database in seconds with zero infrastructure.

What is MCP?

The Model Context Protocol (MCP) is an open standard that connects AI assistants to external systems such as databases, file systems, and APIs. Instead of every application inventing its own plugin format, an MCP server exposes a set of typed tools that any MCP-capable client (Claude Desktop, Claude Code, and others) can discover and call. This server speaks MCP over stdio, so a client simply launches it as a subprocess and starts calling tools.

Why read-only sandboxing matters

Giving an LLM agent direct database access is powerful and dangerous in equal measure: agents are driven by natural-language instructions, and a prompt injection, a hallucinated query, or a plain misunderstanding can turn "analyze my orders" into DROP TABLE orders. The core design position of this project is that an analysis agent should be physically incapable of writing to the database, not merely instructed not to. Every layer of this server is built around that guarantee, and the smoke test proves it by actually attempting destructive statements.

Tools

Tool	Arguments	Returns
list_tables	none	All user tables with row counts
describe_table	table	Column names, types, constraints, and 5 sample rows
run_query	sql	Results of a single read-only SQL statement (column names + rows, capped at 200 rows with a truncation flag)
table_stats	table	Per-column null counts, plus min/max/mean for numeric columns

Security design: defense in depth

Write access is blocked by four independent layers. Any single layer failing still leaves the database untouchable:

1. Read-only connection (storage layer). The SQLite file is opened with the URI flag file:...?mode=ro, so the operating process never holds a writable handle to the database.
2. PRAGMA query_only = ON (engine layer). The SQL engine itself refuses data-modifying statements. Every tool call runs on a fresh connection, so this pragma is always freshly applied and cannot be disabled by a previous call.
3. Statement validation (application layer). Before execution, comments are stripped with a literal-aware scanner (so a write cannot hide behind /* ... */), multi-statement input like SELECT 1; DELETE ... is rejected, and the first keyword must be SELECT, WITH, EXPLAIN, or PRAGMA. PRAGMA assignments (e.g. PRAGMA query_only = OFF) are rejected as well.
4. Row cap (context layer). Results are truncated to 200 rows, so a single call can neither flood the model's context window nor exfiltrate an entire large table in one shot.

Additional hardening: queries are aborted after 5 seconds via a SQLite progress handler, and table-name arguments are matched against the actual schema instead of being interpolated into SQL.

Quickstart

Requires Python 3.10+.

git clone https://github.com/myogaibrahim/mcp-sqlite-analyst.git
cd mcp-sqlite-analyst
pip install -r requirements.txt

A ready-to-query demo database ships with the repo at data/demo.db. To regenerate it from scratch (fully reproducible, seeded):

python scripts/generate_demo_db.py

Run the smoke test

The smoke test spawns the server as a real MCP subprocess using the official MCP client, exercises every tool, and proves the sandbox by attempting DELETE FROM customers, DROP TABLE orders, multi-statement smuggling, and a PRAGMA downgrade -- all of which must be rejected:

python scripts/smoke_test.py

If your python command is not the interpreter where mcp is installed (common on Windows), point the test at the right one:

python scripts/smoke_test.py --python "py -3.12"

Expected output ends with 13 passed, 0 failed out of 13 checks.

Use with Claude Desktop / Claude Code

Claude Desktop -- add to claude_desktop_config.json:

{
  "mcpServers": {
    "sqlite-analyst": {
      "command": "python",
      "args": ["path/to/server.py", "--db", "path/to/your.db"]
    }
  }
}

Claude Code -- one-liner:

claude mcp add sqlite-analyst -- python path/to/server.py --db path/to/your.db

Then ask things like:

"Which product category generated the most revenue from delivered orders, and what is the average order value per country?"

The agent will chain list_tables, describe_table, and run_query on its own -- and any attempt to modify data is refused by the sandbox.

Demo dataset

data/demo.db is a small synthetic e-commerce dataset generated by scripts/generate_demo_db.py with random.seed(42), so it is fully reproducible and contains no real personal data:

Table	Rows	Contents
customers	120	Names, emails, city/country, signup date, marketing opt-in
products	40	Products across 5 categories with prices and stock levels
orders	500	Timestamped orders with status, payment method, shipping, totals
order_items	1,282	Line items with quantity and purchase-time unit price

Project structure

mcp-sqlite-analyst/
├── server.py                  # The MCP server (tools + sandbox)
├── scripts/
│   ├── generate_demo_db.py    # Reproducible synthetic dataset builder
│   └── smoke_test.py          # End-to-end MCP client verification
├── data/
│   └── demo.db                # Committed demo database
├── requirements.txt
├── LICENSE
└── README.md

Author

Muhamad Yoga Ibrahim

Licensed under the MIT License.