MCP Analytics ServerGoverned MCP Gateway Between an LLM and a Database
An MCP server exposing a SQLite analytics database as governed, schema-typed tools with a read-only SQL guard and auth-gated mutations, plus a Claude agent that answers business questions through it.
Headline results
- Protocol
- MCP over stdio
- Guard
- Read-only SQL enforced
- Mutations
- Auth-gated
- Client
- Claude agent consuming it
System architecture
Problem
MCP is the 2026 standard for connecting agents to tools and data, with thousands of servers already published and native support across major clients. What teams keep needing is a governed gateway between an LLM and a database, not raw SQL access. A bash or raw-SQL tool hands the model unbounded power, whereas dedicated typed tools can be validated, gated, and audited.
Approach
An MCP server on the standard mcp Python SDK (FastMCP) exposes a SQLite e-commerce database as typed tools: list_tables, describe_table, run_query, top_products, revenue_summary, and an auth-gated create_support_ticket, plus a schema://database resource. A read-only SQL guard makes run_query accept only a single SELECT or WITH, reject INSERT/UPDATE/DELETE/DROP/ALTER/ATTACH/PRAGMA/CREATE, block multi-statement injection, and cap rows, executed on a mode=ro SQLite connection as defence in depth. The only mutation is behind the MCP_WRITE_API_KEY write key, and unknown tables or customers raise typed errors surfaced to the agent. Tool logic lives in a pure, testable db.py while server.py is a thin FastMCP adapter, and a Claude agent converts the MCP tools to Anthropic tools and runs the tool loop. The server runs over stdio so any MCP client can point at it directly.
Impact
The offline protocol demo connects with 6 tools and shows the guards working end to end: revenue_summary returns revenue 184293.5, orders 968, customers 188, and a blocked mutation returns only SELECT / WITH queries are allowed. The security-critical surface, the read-only guard and auth, is covered by an 11-case test suite. It is a clean, production-shaped reference for the 2026 agent-integration standard.
Decisions & tradeoffs
Typed tools over raw SQL access
A bash or raw-SQL tool hands the model unbounded power. Dedicated, schema-typed tools can be validated, gated, and audited by the host.
Defence-in-depth read-only guard
run_query parses for a single SELECT or WITH and rejects mutation keywords and multi-statement injection. It also runs on a mode=ro SQLite connection so the guard is backed by the database itself.
Protocol-free query layer
Tool logic lives in a pure db.py that is unit-tested without the protocol, while server.py stays a thin FastMCP adapter. This keeps the security-critical logic directly testable.
Build spec
- Transport
- stdio (JSON-RPC)
- SDK
- mcp / FastMCP
- Tools exposed
- 6 typed tools + schema resource
- Guard connection
- mode=ro SQLite
- Tests
- 11 cases (guard + auth)
System notes
- Read-only SQL guard on run_query: single SELECT/WITH, mutation keywords blocked, row-capped
- Auth-gated create_support_ticket mutation; all other tools read-only
- Offline demo exercises the live MCP protocol with 6 tools and no API key
- Security-critical surface covered by an 11-case test suite
Stack
MCP · FastMCP · SQLite · Claude · Python · pytest