ecommerce-sql-mcp

A Model Context Protocol (MCP) server that exposes a small e-commerce SQLite database as tools, plus an agent that answers natural-language questions by driving those tools over the real MCP wire protocol (streamable HTTP, mcp 2.x).

An agent asks "which product category sold the most units?", the MCP server discoverable tools let it inspect the schema, write a SELECT, and read the result — all through the standard MCP tool-call round-trip.

┌────────────┐    MCP streamable HTTP    ┌──────────────┐     ┌──────────┐
│  DeepSeek  │ ◀──── tools / result ───▶ │  MCP server  │ ──▶ │  SQLite  │
│  (agent)   │                           │  (mcp 2.x)   │     │ ecommerce│
└────────────┘                           └──────────────┘     └──────────┘

Tools

Related MCP server: shop

Design decisions

• Read-only at the storage layer. run_query opens SQLite with file:...?mode=ro, so even a slipped keyword can't write. A token guard rejects non-SELECT and every write keyword, plus multi-statement (;) and trailing-semicolon inputs.

• Writes are explicitly gated. run_mutation refuses unless confirm=True; the agent must state the change before a side effect happens. This is a small but real "permissioned write" boundary, the same shape as OAuth-2.1-tool designs meant for production.

• Errors feed the model, not the user. SQL errors are returned with a hint ("table X doesn't exist → list_tables(); no such column → get_schema()"), the MCP-idiomatic way to let the agent self-correct instead of returning a bare stack message.

• Reproducible data. The seed writes a fixed-RNG dataset, so the same question yields the same rows run over run.

Running

python -m venv .venv && .venv/Scripts/python -m pip install -r requirements.txt

# 1. Build the sample DB
.venv/Scripts/python scripts/seed_db.py

# 2. Smoke-test the MCP protocol end to end (no LLM needed)
.venv/Scripts/python scripts/test_mcp.py

# 3. Ask the agent a question (needs an OpenAI-compatible LLM endpoint)
set MCP_LLM_BASE=https://api.deepseek.com
set MCP_LLM_MODEL=deepseek-v4-flash
set MCP_LLM_KEY=sk-...
.venv/Scripts/python scripts/demo.py "销量最高的商品类别是哪个?卖了多少件?"

Layout

server/   MCP server: db.py (safety + introspection), main.py (MCPServer + tools)
client/   agent: llm.py (thin OpenAI-compatible client), agent.py (tool-call loop)
scripts/  seed_db.py, test_mcp.py, demo.py
data/     generated ecommerce.db (gitignored)

Limitations

• Single SQLite file, local-only; no persistence layer beyond the seed.

• The agent talks to exactly one MCP server endpoint.

• max_rows/statement-length caps and the token guard are the current safety boundary; a production version would add a DB role with SELECT-only on a separate connection and per-request auth.

• DeepSeek's reasoning_content must be echoed back in the assistant message on follow-up turns (handled in client/agent.py); providers without that field simply omit it.