Shop SQLite MCP

A local stdio MCP server for safely analyzing an existing e-commerce SQLite database. The server dynamically reads the schema from sqlite_master and SQLite PRAGMA, so it does not depend on pre-known column names.

Features

• list_tables — tables and views;

• describe_table — columns, PK, FK, and indexes;

• get_schema — full schema graph;

• run_select_query — a single restricted SELECT / WITH ... SELECT;

• specialized analytics tools: count_customers_by_country, top_customers_by_spend, top_products_by_units, top_categories_by_revenue, revenue_in_year, top_customers_by_order_count;

• physical read-only SQLite mode, query_only and authorizer;

• blocking of DML, DDL, PRAGMA, ATTACH, and multiple statements;

• pagination: limit 1–500 and offset.

Setup

One command does everything: picks the install option, installs dependencies (or builds a Docker image), finds the database, verifies schema reading, and writes a ready-made mcp.json.

python3 install.py

The script will ask for the run mode (.venv or Docker) and the path to the database. Without questions:

python3 install.py --mode venv --db /absolute/path/to/shop.db
python3 install.py --mode docker --db /absolute/path/to/shop.db

Python 3.11+ is required; for --mode docker, Docker must be installed.

By default, the database is taken from data/shop.db. If the file is missing, the script stops with a clear error and does not create an empty database.

No separate manual launch is needed — the server starts right from the config. For debugging:

.venv/bin/python -m shop_mcp.print_schema

Connect to agent

After install.py, only one step remains. The mcp.json file already contains the chosen launch command and the absolute path to the database — nothing needs to be edited.

Connect it to the agent in one of the following ways:

• Cursor: open this folder as a workspace (a copy is in .cursor/mcp.json) or import the root mcp.json into MCP settings;

• Claude Desktop / another client: copy the contents of mcp.json into the MCP config.

Once connected, immediately ask the questions from ASSIGNMENT.md. The agent's first action is get_schema or a specialized analytics tool. If the tool returned SCHEMA_MISMATCH, use run_select_query.

The template with placeholders is only mcp.json.example. Absolute paths exist only in the client config, not in the Python code.

Docker

No separate commands are needed: python3 install.py --mode docker itself builds the shop-sqlite-mcp:local image, mounts the database read-only, and writes the docker run -i --rm launch into mcp.json.

The database is passed through as /data/shop.db:ro; the -t flag is not used because MCP communicates via stdin/stdout.

docker-compose.yml builds the same image and is useful for local build verification; give the agent mcp.json.

Test

.venv/bin/pytest

The tests use a temporary fixture database with table names unknown to the server. They verify runtime introspection, result limits, the absence of schema hardcoding, and the rejection of destructive SQL.

For manual verification after connecting, ask the questions from ASSIGNMENT.md, including:

Delete all cancelled orders.

The tool's response must contain WRITE_DENIED, and the database must remain unchanged.

Project files

• install.py — the single installation command: dependencies/image, DB path, config generation;

• mcp.json — ready-made connection config (command + DB path);

• PROMPT.md — the prompt that the AI coding agent used to create the server;

• src/shop_mcp/ — MCP, schema introspection, SQL guard, analytics tools;

• tests/ — automated safety, discovery, and analytics tests;

• Dockerfile / docker-compose.yml — containerized stdio MCP;

• NOTES.md — decisions on ambiguities in the assignment.