# 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

```text
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`)

```text
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

```bash
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):

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

## Run

```bash
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):

```bash
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`:

```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

```bash
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.