Analytics Server

A Model Context Protocol (MCP) server, built with FastMCP, that lets an LLM safely explore and analyse a SQLite database through well-designed tools — list tables, inspect schema, run guarded read-only SQL, compute aggregations, and import CSVs.

It ships with a seeded sample e-commerce dataset, so you can clone and run it in under a minute with zero API keys or external services.

• Language: Python 3.10+
• Framework: FastMCP (fastmcp)
• Data: SQLite (stdlib) + pandas
• Transport: stdio (local) — the standard for desktop MCP clients
• Tested: 16 pytest cases, incl. read-only safety and pagination

---

Why this exists

MCP servers expose tools that an LLM can call. The hard parts are (1) safety — never letting a model mutate or exfiltrate data it shouldn't — and (2) ergonomics — tools with clear schemas, pagination, and actionable errors so the model uses them correctly. This project demonstrates both.

---

Quick start

git clone https://github.com/kshitiz305/analytics-mcp-server.git
cd analytics-mcp-server

python -m venv .venv && source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt
pip install -e .

python scripts/seed_data.py     # generates sample.db

Run the server over stdio:

analytics-mcp            # console script
# or:  python -m analytics_mcp.server

Try it without an MCP client

Use the built-in MCP Inspector:

npx @modelcontextprotocol/inspector analytics-mcp

Register it with Claude Desktop

Add to claude_desktop_config.json:

{
  "mcpServers": {
    "analytics": {
      "command": "analytics-mcp",
      "env": { "ANALYTICS_DB_PATH": "/absolute/path/to/sample.db" }
    }
  }
}

Point ANALYTICS_DB_PATH at any SQLite file to analyse your own data.

---

Tools

Tool	Purpose	Write?
analytics_list_tables	List tables with row counts	read-only
analytics_describe_table	Column schema, row count, sample rows	read-only
analytics_run_query	Run a guarded, paginated SELECT	read-only
analytics_aggregate	Group-by + count/sum/avg/min/max (no SQL needed)	read-only
analytics_import_csv	Load a CSV into a table (validated via pandas)	write

Every tool supports response_format="markdown" (default, human-readable) or "json" (machine-readable, full precision), carries MCP annotations (readOnlyHint, destructiveHint, …), and returns actionable Error: … messages.

Example output

analytics_list_tables:

### Tables

| table | rows |
| --- | --- |
| customers | 200 |
| order_items | 2420 |
| orders | 800 |
| products | 40 |

analytics_aggregate(table="orders", group_by="status", agg="count"):

### count(*) by status in orders

| status | value |
| --- | --- |
| completed | 379 |
| shipped | 175 |
| processing | 119 |
| cancelled | 87 |
| returned | 40 |

analytics_run_query with a join + pagination (top customers by spend):

Returned 5 of 196 rows (offset 0, next_offset 5)

| name | country | spend |
| --- | --- | --- |
| Arjun Khan | Japan | 22462.72 |
| Hiro Gupta | Japan | 21656.45 |
| Fatima Lee | Japan | 21249.44 |
| Liam Gupta | Canada | 19013.48 |
| Liam Brown | India | 18822.85 |

Attempting a write is rejected:

analytics_run_query(sql="DROP TABLE customers")
→ Error: Only read-only queries are permitted. The statement must start with SELECT or WITH.

---

Safety model

User-supplied SQL is treated as untrusted and guarded on three independent layers:

1. Read-only connection — queries execute over a file:…?mode=ro SQLite URI, so writes are rejected at the storage engine level.
2. Authorizer callback — an allow-list set_authorizer permits only read actions (SELECT/READ/FUNCTION), blocking ATTACH, PRAGMA writes, etc.
3. Statement validation — analytics_run_query accepts a single SELECT/WITH statement only, with fast, clear errors before touching the database.

Tools that build SQL internally (list_tables, describe_table, aggregate) never interpolate raw user text — table/column names are validated against the live schema and quoted, so they are injection-safe. The only write path, analytics_import_csv, validates the destination name against an identifier allow-list.

---

Sample dataset

scripts/seed_data.py generates a deterministic (seeded) e-commerce dataset:

• customers (200) — id, name, email, country, signup_date
• products (40) — id, name, category, price
• orders (800) — id, customer_id, order_date, status
• order_items (2420) — id, order_id, product_id, quantity, unit_price

Because the RNG is seeded, the numbers above are reproducible on any machine.

---

Testing

pip install -e ".[dev]"
pytest

The suite (tests/test_server.py) covers schema discovery, pagination, aggregation, CSV import, rejection of write/multi-statement SQL, and an end-to-end call through FastMCP's in-memory client.

---

Docker

docker build -t analytics-mcp .
docker run --rm -i analytics-mcp        # serves MCP over stdio

The image installs the package and bundles a freshly seeded sample.db.

---

Project structure

analytics-mcp-server/
├── src/analytics_mcp/
│   ├── server.py        # FastMCP server + tool definitions
│   ├── database.py      # SQLite access layer (read-only safety)
│   ├── models.py        # Enums for tool inputs
│   ├── formatting.py    # JSON / Markdown formatting + pagination
│   └── sample_data.py   # Deterministic dataset generator
├── scripts/seed_data.py # CLI to (re)build sample.db
├── tests/test_server.py # pytest suite
├── Dockerfile
└── pyproject.toml

---

License

MIT © 2026 Kshitiz Gupta