# db-mcp

A read-only [MCP](https://modelcontextprotocol.io/) server that gives an AI
agent safe access to a small SQLite shop database (`shop.db`): schema
discovery, free-form read-only SQL, and pre-built analytics reports.

## Install

```bash
npm install
```

## Configure

The server reads `SHOP_DB_PATH` from the process environment to locate the
database file. It is optional — if unset, the server uses `shop.db` in the
project root. `.env` files are **not** read automatically (there is no
`dotenv` dependency and no `--env-file` flag in `package.json`'s scripts);
`.env.example` is provided only as a reminder of the variable name. Set
`SHOP_DB_PATH` in the actual environment the server runs in — for a stdio
MCP client, that means the `env` key of its config (see "Connect a client"
below).

## Build

```bash
npm run build
```

## Run

```bash
npm start
# or directly:
node dist/index.js
```

The server speaks MCP over stdio — it expects to be launched as a child
process by an MCP client/host, not run interactively.

## Connect a client

Generic stdio MCP client config:

```json
{
  "mcpServers": {
    "db-mcp": {
      "command": "node",
      "args": ["/absolute/path/to/db-mcp/dist/index.js"],
      "env": { "SHOP_DB_PATH": "/absolute/path/to/shop.db" }
    }
  }
}
```

Claude Desktop: add the same block under `mcpServers` in
`claude_desktop_config.json` (see `MCP.md` for the per-OS config file
location), then restart Claude Desktop.

## Tools

| Tool | Description |
|---|---|
| `list_tables` | List all tables with a short description and row count. Call this first. |
| `describe_table` | Column definitions, keys, row count, and sample rows for one table (`customers`, `products`, `orders`, `order_items`). |
| `query` | Run a single read-only `SELECT` (or `WITH ... SELECT`) with `limit`/`offset` pagination. |
| `get_top_customers` | Top customers by total spending or order count. Excludes cancelled orders. |
| `get_top_products` | Best-selling products by units sold, with revenue. Excludes cancelled orders. |
| `get_revenue_report` | Revenue grouped by category, year, or month, optionally filtered to one year. Excludes cancelled orders. |

## Security notes

- The database connection is opened with `{ readonly: true, fileMustExist: true }`
  — the SQLite engine itself physically refuses any write, regardless of what
  SQL is sent.
- The `query` tool additionally validates that the input is a single
  `SELECT` (or `WITH ... SELECT`) statement before it ever reaches the
  database, rejecting `INSERT`/`UPDATE`/`DELETE`/`DROP`/`ALTER`/`CREATE`/
  `PRAGMA`/`ATTACH`/`DETACH`/`REPLACE`/`VACUUM`/`REINDEX`/`TRIGGER` and
  multi-statement input, with a friendly error message.
- Errors are caught at each tool's boundary and returned as an MCP tool
  result with `isError: true` and a short message — no stack traces or
  filesystem paths are ever sent to the client.
- All logs go to `stderr` (`console.error`) — `stdout` is reserved
  exclusively for the JSON-RPC protocol stream.

## Tests

```bash
npm test
```

Runs `node --test` (via `tsx`) across every `*.test.ts` file: unit tests for
the SQL guard, the DB connection, and each tool's query logic against the
real `shop.db`, plus an end-to-end test that spawns the compiled server and
drives it with a real MCP client over stdio.

## Docker

```bash
docker build -t db-mcp .
docker run -i db-mcp
```

Not required to run the server locally (the client config above expects a
local `node` process), but provided as an alternative way to verify/run it.