# MCP SQLite Analyst

A read-only SQL analysis server for LLM agents, built on the official
[Model Context Protocol](https://modelcontextprotocol.io) 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+.

```bash
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):

```bash
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:

```bash
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:

```bash
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`:

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

**Claude Code** -- one-liner:

```bash
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](LICENSE).