MCP server shop-db

Read-only MCP server (stdio transport) that gives an AI agent access to the SQLite database of the online store shop.db: customers, products, orders, and order items.

Built on the official MCP Python SDK (v2).

Development metadata

The project was entirely generated by an AI coding agent (Claude Code, Fable 5 model) from the specification in SPEC.md.

Related MCP server: db-mcp

Tools

Security

It is impossible to modify the database through this server. Three independent layers of protection:

1. Request validation — anything that is not a single SELECT/WITH (INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, PRAGMA, ATTACH, multiple statements in a row, or a write hidden behind a comment) is rejected with a clear message before it is ever executed.

2. Read-only connection — the file is opened via the SQLite URI with mode=ro.

3. PRAGMA query_only = ON on every connection.

Even a write that passes validation (for example, WITH ... INSERT) runs into the read-only constraint at the connection level.

SQL errors are returned as short, clear messages with hints — no stack traces.

Installation

Requires Python 3.10+.

Via uv (recommended — everything installs automatically on first run):

uv sync

Or via pip:

python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

Configuration

The server looks for the database in the shop.db file next to server.py — the database provided in the assignment is included in the repository. To use a different file, set the SHOP_DB_PATH environment variable:

export SHOP_DB_PATH=/path/to/shop.db

seed_db.py is a helper utility that generates a demo database with a similar schema; use it only if you need temporary data. It leaves shop.db alone unless you explicitly ask:

python seed_db.py /tmp/demo.db

Running

The server communicates over stdio — it is launched by an MCP client, not by the user manually. To verify that it starts without errors:

uv run python server.py

(the server will wait for MCP messages on stdin; exit with Ctrl+C)

Connecting to an agent

Claude Code

The repository includes .mcp.json, so from the project directory the server is picked up automatically. To register manually:

claude mcp add shop-db -- uv run --directory /absolute/path/to/sqlite-mcp python server.py

Claude Desktop (or any client with a JSON config)

Add the entry to claude_desktop_config.json (see examples/claude_desktop_config.example.json). When installed via the dependencies, they must be installed to account for the terminal that the config points to:

{
  "mcpServers": {
    "shop-db": {
      "command": "/absolute/path/to/sqlite-mcp/.venv/bin/python",
      "args": ["/absolute/path/to/sqlite-mcp/server.py"]
    }
  }
}

Or via uv (no ... setup needed):

{
  "mcpServers": {
    "shop-db": {
      "command": "uv",
      "args": ["run", "--directory", "/absolute/path/to/sqlite-mcp", "python", "server.py"]
    }
  }
}

Docker

docker build -t shop-db-mcp .

{
  "mcpServers": {
    "shop-db": {
      "command": "docker",
      "args": ["run", "-i", "--rm", "shop-db-mcp"]
    }
  }
}

Example questions the agent answers

• Show me the available tables and explain what information each table contains.

• How many customers are from United States?

• Which country has the most customers?

• Who is the customer who spent the most money?

• What are the top 5 best-selling products?

• What are the top 3 product categories by revenue?

• How much revenue did we generate in 2025?

• Which customer placed the most orders?

Destructive requests such as "Delete all cancelled orders" are rejected by the server.

Note on the assignment data: customers has no "column: customers" (the location can only be inferred from phone codes — all numbers start with +7 — or from email domains), and all orders are dated February–August 2026. The schema tools give the agent everything it needs to detect and answer honestly.

Test results

All 27 tests in the spec were run through a real agent (claude -p "<question>" --mcp-config .mcp.json, with Gemini), using only the server’s three MCC tools — no Bash, no filesystem access. The answer was compared with the reference computed directly from SQLite. Result: 9/9.

Observation from the transcript: agents usually need only be plus one aggregate SQL query, and the schema table descriptions (like the missing country or revenue) do the rest.

Database schema

customers ──< orders ──< order_items >── products

• customers (150 rows) — id, where in, email, phone, created_at

• products (50 rows) — id, name, category, price, stock_quantity, created_at

• orders (750 rows) — id, customer_id → customers, order_date, status (new/processing/shipped/completed/cancelled), total_amount

• order_items (1,900 rows) — id, order_id *orders, product_id, quantity, unit_price

Tests

uv run pytest

36 tests cover all three tools, pagination, error handling, the read-only guarantee (including multi-statement and a write hidden in comments), and the demo data generator.

Project structure

server.py        # MCP-сервер (3 инструмента, read-only защита)
shop.db          # выданная в задании база данных
SPEC.md          # спецификация, по которой сгенерирован проект
seed_db.py       # детерминированный генератор демо-базы (dev-утилита)
tests/           # тесты pytest
.mcp.json        # конфиг проекта для Claude Code
examples/        # пример конфига для Claude Desktop
Dockerfile       # опциональный запуск в контейнере