Shop MCP Server

Local MCP server for analytics of an online store's SQLite database. The AI agent calls an MCP tool, the server converts the request into a safe SQL SELECT, directly reads shop.db, and returns a structured result.

Host-приложение
├── AI-агент / LLM
└── MCP-клиент
        │ JSON-RPC / stdio
        ▼
    Shop MCP Server
        │ read-only SQLite
        ▼
    data/shop.db

Features

• direct SQLite connection without HTTP or a separate DB server;

• MCP stdio transport;

• query_shop and inspect_database discovery via tools/list;

• eight example tasks in the description and JSON Schema of the tool;

• plain text, JSON intent, or a ready-made safe SELECT;

• exclusion of cancelled orders from all analytics;

• pagination and result limiting;

• multi-level read-only protection.

Related MCP server: Shop Analytics MCP Server

Installation

Requires Python 3.11 or newer.

python3 -m venv .venv
.venv/bin/python -m pip install -e '.[dev]'

The prepared database is already located in data/shop.db. It contains 150 customers, the customers.country field is filled in for all customers, and 45 customers belong to Germany.

Re-preparing the database

The source database is not modified. The command creates a new working copy and adds country TEXT NOT NULL with reproducible synthetic values:

.venv/bin/shop-prepare-db /path/to/original/shop.db ./data/shop.db

The distribution depends only on customers.id, so re-preparation yields the same values. The countries are test data and do not describe the actual location of customers.

Path configuration

The path is determined in the following order:

1. SHOP_DB_PATH;

2. JSON file from SHOP_MCP_CONFIG or config/shop_mcp.json;

3. data/shop.db relative to the working directory.

An example environment variable is in .env.example, and the MCP client configuration is in config/mcp.example.json. Replace /absolute/path/to/MCPDeveloper with the absolute path to the project.

Running

Usually the MCP client starts the server from its own configuration. To run the process manually:

SHOP_DB_PATH=./data/shop.db .venv/bin/python -m shop_mcp.server

stdout is reserved for MCP JSON-RPC. Diagnostics are not output to the protocol stream.

Tools

inspect_database

Arguments:

{"action": "list_tables"}

or:

{"action": "describe_table", "table": "customers"}

query_shop

Arguments:

{
  "request": "Сколько клиентов из Германии?",
  "limit": 100,
  "offset": 0
}

Examples declared via tools/list:

1. What tables are in the database and what fields do they have?

2. How many customers are from Germany?

3. Which country has the most customers?

4. Which customer spent the most? Return the name, email, and total amount.

5. Show the top 5 products by quantity sold and revenue.

6. Show the top 3 categories by revenue.

7. What was the revenue in 2025?

8. Which customer placed the most orders?

You can pass a structured intent:

{
  "request": "{\"intent\": \"revenue_by_year\", \"year\": 2025}"
}

Or a ready-made query:

{
  "request": "SELECT name, category FROM products ORDER BY name"
}

Business rules

• orders.status = 'cancelled' does not participate in metrics.

• Customer spending and annual revenue are calculated from orders.total_amount.

• Product and category revenue is calculated as quantity * unit_price.

• The year interval is half-open: from January 1 inclusive to January 1 of the next year exclusive.

• In the provided database, all orders belong to 2026, so revenue for 2025 is 0.

Security

The server opens SQLite via mode=ro, enables query_only, sets up a SQLite authorizer, forbids multiple statements, DML, DDL, ATTACH, modifying PRAGMA, extension loading, and limits execution time.

Any attempt to perform an operation other than SELECT returns a precise answer:

Не доступный вариант запроса

Internal SQLite errors, stack traces, and local paths are not returned to the client.

Testing

.venv/bin/pytest
.venv/bin/ruff check .
.venv/bin/ruff format --check .

The tests include a real MCP server launch as a subprocess, a ClientSession connection via stdio, tools/list, calls to both tools, analytical scenarios, and attempts to modify the database.

The full approved specification is in docs/specification.md.