shop-mcp

A local, read-only MCP (Model Context Protocol) server that lets an AI agent analyze a SQLite e-commerce database (shop.db) — customers, products, orders and order items — over the stdio transport. No HTTP server, no separate database process: the server opens shop.db directly and exposes two small, general-purpose tools an agent can use to explore the schema and run its own analytical SQL.

Built with the official Python MCP SDK (mcp on PyPI).

Project structure

mcp-sql/
├── server.py                    # the MCP server (stdio transport)
├── shop.db                      # SQLite database (not modified by this project)
├── requirements.txt
├── .env.example
├── mcp-config.example.json
├── tests/
│   ├── conftest.py
│   ├── test_server.py           # unit tests (call tool functions directly)
│   └── test_stdio_integration.py# protocol-level test (spawns server.py over stdio)
└── README.md

Database schema (as actually found in shop.db)

customers(id PK, first_name, last_name, email UNIQUE, phone, created_at)
products(id PK, name, category, price, stock_quantity, created_at)
orders(id PK, customer_id -> customers.id, order_date, status, total_amount)
order_items(id PK, order_id -> orders.id, product_id -> products.id, quantity, unit_price)

orders.status is constrained to: new, processing, shipped, completed, cancelled. products.category currently has 5 distinct values. Foreign keys: orders.customer_id → customers.id, order_items.order_id → orders.id, order_items.product_id → products.id. The server derives all of this from the live database at query time (via sqlite_master / PRAGMA table_info / PRAGMA foreign_key_list) — nothing here is hard-coded, so if shop.db is swapped for another file with a different schema, get_database_schema will reflect that automatically.

Known data characteristics of the provided shop.db: customers has no country column, so "customers from Germany" style questions cannot be answered — the schema tool makes this discoverable, and query_database returns a clear no such column: country error instead of guessing. All 750 orders currently in the database are dated in 2026 (none in 2025), so a "revenue in 2025" query correctly returns 0/null, not an error.

Installation

cd mcp-sql
python3 -m venv .venv
source .venv/bin/activate        # on Windows: .venv\Scripts\activate
pip install -r requirements.txt

Configuration

The database path is never hard-coded in the source. It is resolved as:

1. the SHOP_DB_PATH environment variable, if set;

2. otherwise shop.db next to server.py.

Copy .env.example to .env and edit it if you want to point the server at a different database file (you'll need to load it into your shell/agent launcher yourself, e.g. export $(cat .env | xargs), or just set SHOP_DB_PATH directly):

cp .env.example .env
# edit .env, or simply:
export SHOP_DB_PATH=/absolute/path/to/shop.db

Run

source .venv/bin/activate
python server.py

The process speaks MCP over stdio and waits for a client — it will look "stuck" with no output, which is expected: connect an MCP client (an AI agent, or mcp-inspector, see below) rather than running it standalone in a terminal.

Quick manual check with the official MCP Inspector (no install needed):

npx @modelcontextprotocol/inspector --cli .venv/bin/python server.py --method tools/list

Connect to an AI agent

Most MCP-compatible clients (Claude Desktop, Claude Code, etc.) read a JSON config block like mcp-config.example.json:

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

Notes:

• Use the absolute path to the venv's Python interpreter (as above) so the mcp package is found without activating the venv manually; using a bare python3 also works if mcp is installed in whatever environment that resolves to.

• SHOP_DB_PATH is optional — omit it to use the bundled shop.db.

• Absolute paths belong in this configuration file, supplied by whoever connects the server — never inside server.py itself.

• Client-specific placement of this block varies (e.g. Claude Desktop uses claude_desktop_config.json with the same mcpServers shape; other clients may want just the inner {"command": ..., "args": ..., "env": ...} object). Check your client's docs for where the file lives.

Testing

source .venv/bin/activate
python -m pytest tests/ -v

This runs 48 tests, including:

• schema discovery (tables, columns, PK/FK, relationships, row counts);

• SELECT, JOIN, WHERE, GROUP BY, ORDER BY, aggregates (COUNT/SUM/AVG/MIN/MAX), subqueries, a safe WITH ... SELECT CTE, and date filtering (strftime);

• row-limit clamping and offset-based pagination;

• friendly error handling for invalid SQL, unknown tables/columns, an empty query, and a missing database file;

• read-only safety: every statement type listed in the assignment (DELETE, UPDATE, DROP, CREATE, INSERT, plus ALTER, REPLACE, TRUNCATE, ATTACH, DETACH, VACUUM, REINDEX, a destructive PRAGMA, a stacked SELECT 1; DROP TABLE ..., and a WITH x AS (...) DELETE ... CTE-disguised delete) is rejected, and the database file's row counts and SHA-256 hash are asserted unchanged afterwards;

• tests/test_stdio_integration.py launches server.py as a real subprocess and drives it through the actual MCP client SDK over stdio (initialize → list_tools → call_tool), rather than calling Python functions directly — this is the same path a real agent uses.

MCP tools

get_database_schema()

No parameters. Call this first whenever you don't already know the exact table/column names — don't guess them. Returns, per table: row_count, columns (name, SQLite type, not_null, default_value, is_primary_key), primary_key, foreign_keys (column, referenced table/column, ON DELETE/ON UPDATE), and a few sample_rows so the agent can see real date formats, status values, price magnitudes, etc. A top-level relationships list gives table.column -> other_table.column strings derived from the live foreign keys.

query_database(sql, limit=100, offset=0)

Runs one read-only SQL statement (SELECT, or WITH ... SELECT) and returns {columns, rows, row_count, limit, offset, truncated, total_matching_rows}. Supports JOIN, WHERE, GROUP BY, ORDER BY, aggregate functions, subqueries, and CTEs. limit is clamped to 1..500 (default 100); use offset to page through larger results. total_matching_rows and truncated tell the caller whether the current page is the whole result or there is more to fetch. Errors (bad syntax, unknown table/column, or a rejected write attempt) are raised as a short, specific message — never a raw Python traceback.

Security: how read-only is enforced

The assignment explicitly asks not to rely on a single regex/keyword check, so this server layers four independent defenses — verified in tests/test_server.py:

1. OS-level read-only file handle. The SQLite file is opened with the URI file:<path>?mode=ro. SQLite itself then refuses any write (OperationalError: attempt to write a readonly database) no matter what SQL is executed — this holds even if every check below has a bug.

2. PRAGMA query_only = ON is set on every connection as a second, independent SQLite-level guard against writes.

3. A sqlite3 authorizer callback (Connection.set_authorizer) allow-lists only the SELECT / READ / FUNCTION / RECURSIVE actions at the SQLite engine level and denies everything else — INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, REPLACE, TRUNCATE, ATTACH, DETACH, VACUUM, REINDEX, PRAGMA, transactions, etc. This runs on the parsed statement, so it also catches the classic CTE bypass WITH x AS (SELECT 1) DELETE FROM ... that a naive "must start with SELECT" text check would miss.

4. Statement-shape checks in server.py: the submitted text must start with SELECT/WITH (fast, friendly rejection before touching SQLite), and every query is executed wrapped as SELECT * FROM (<query>) LIMIT :limit OFFSET :offset — a single statement is required for this to parse at all, so a stacked SELECT 1; DROP TABLE customers becomes a plain SQL syntax error rather than two executed statements.

Because layer 1 (mode=ro) is enforced by SQLite/the OS independently of this server's own logic, shop.db cannot be modified through this server even if a bug existed in layers 2-4.

Known limitations

• customers has no country/location column in the provided shop.db, so questions like "customers from Germany" cannot be answered from this data — the schema tool surfaces this rather than the server inventing a column.

• All orders in the provided data are dated in 2026; a 2025 revenue query correctly returns 0 rather than an error.

• total_matching_rows in query_database is computed with a second COUNT(*) wrapping the same query; for very expensive queries this roughly doubles the work. Given the size of this database (hundreds to a few thousand rows per table) this is not a practical concern.