MCP SQL Analytics

A real MCP stdio server exposing bounded, read-only queries over synthetic retail data.

Problem

An assistant with SQL access should not receive unrestricted database privileges. This prototype exposes schema inspection and one-query analytics with database-enforced guardrails and explicit resource limits.

Related MCP server: DB Insights MCP Server

Demo

The demo runs in the terminal with plain text output. Add --json to the demo CLI for the complete machine-readable result.

Run python -m analytics.demo after installing dependencies. Recorded output comes from 120 deterministic synthetic orders:

The same demo attempts DELETE FROM orders and reports blocked. This is synthetic currency-denominated test data, not business performance. Interview walkthrough.

Architecture

flowchart LR
    A[MCP client] --> B[stdio / MCPServer]
    B --> C[Schema inspection]
    B --> D[Parameter and query budgets]
    D --> E[SQLite authorizer]
    E --> F[Read-only database connection]
    F --> G[Row and response limits]
    G --> A

Tech stack

Python, the official MCP Python SDK v2, SQLite, pytest, and GitHub Actions. No LLM is required to run or test this tool server.

Quickstart

The standalone demo is one command with Docker:

docker compose up --build

For a real MCP session:

python -m venv .venv
# Activate .venv, then:
pip install -r requirements.txt -r requirements-dev.txt
python -m analytics.seed
python -m analytics.server

The last command waits for MCP JSON-RPC on stdin; it is not a conversational terminal. Configure your MCP client's stdio transport to launch the environment's Python with -m analytics.server, working directory set to this repository, and ANALYTICS_DB pointing to the generated database. If your client cannot set a working directory, use python -m analytics.server with PYTHONPATH set to this repository's absolute path.

Tools: inspect_schema() and query_readonly(sql, parameters). Example query:

SELECT region, SUM(revenue_cents) AS revenue_cents
FROM orders WHERE region = ? GROUP BY region

Use ["North"] as the parameter list. The server never accepts a database path from a tool call. .env.example documents process configuration; no automatic dotenv loading is performed.

Evaluation

Recorded locally on 2026-09-29, Python 3.10.4:

Reproduce with pytest -q and ruff check .. These cases are a regression suite, not a security certification or proof against every SQLite resource-exhaustion strategy.

Design choices

• Enforce below the prompt. mode=ro, query_only, disabled extension loading and a deny-by-default SQLite authorizer are independent of model behavior.

• Allow only the intended data. orders and products are the only readable tables. Functions are explicitly allowlisted; schema access through user SQL, attachment, PRAGMAs and recursive CTEs are rejected.

• Bound work and output. 8,000 SQL characters, 100 parameters, a default 250 ms progress-handler budget, 100 rows and a 100 KB serialized result cap. The timeout is cooperative, not a hard OS sandbox.

• Keep MCP stdout clean. Audit records go to stderr and contain a query hash, status and timing; no SQL text, parameters or result values are logged.

Limitations and next steps

This is a local stdio prototype using synthetic data. It does not implement remote authentication, tenant isolation or row-level permissions. The allowlist is at table level: every column of those tables is readable. A local user who can replace the database or modify the process environment is trusted. Python 3.11+ adds SQLite value-size limits; Python 3.10 still enforces serialized output limits but not the same allocation limit.

Next: fuzz SQL policy, isolate workers for hard memory/time limits, test additional SQLite versions, and design a separate least-privilege PostgreSQL adapter. No employer or client database is used. I designed and built it with a strict focus on evaluation, fact validation and reproducibility.