# shop-sql-mcp

A small MCP server that gives an AI agent **read-only** analytical access to the
`shop.db` SQLite database over **stdio**.

The server does three things and nothing else: it lists tables, describes their
schema, and runs one read-only SQL statement per call with server-enforced
pagination. All reasoning — which joins to make, how to aggregate, when to look
at the schema — belongs to the agent.

```text
AI Agent
    |
    |  MCP over stdio
    v
shop-sql-mcp
    |
    +-- list_tables
    +-- describe_table
    +-- query_database
    |
    v
read-only SQLite connection
    |
    v
shop.db
```

## Requirements

- Node.js **22.5 or newer** (24+ recommended). The server uses the built-in
  `node:sqlite` module, so there is no native SQLite dependency to compile.
- No other runtime prerequisites.

## Install

```bash
npm install
```

## Configure

Configuration is optional. By default the server opens `shop.db` in the project
root.

| Variable        | Default              | Meaning                                                                 |
| --------------- | -------------------- | ----------------------------------------------------------------------- |
| `DATABASE_PATH` | `<project>/shop.db`  | Path to the SQLite file. Relative paths resolve against the project root, so the server does not depend on the working directory it is spawned in. |

Copy `.env.example` to `.env` if you want to keep local overrides. The server
itself reads plain environment variables; `ANTHROPIC_API_KEY`, `EVAL_MODEL` and
`EVAL_MAX_STEPS` in `.env.example` are used only by `npm run eval`.

## Build

```bash
npm run build
```

Compiles `src/` to `dist/`.

## Run

```bash
npm start              # runs the built server (dist/index.js)
npm run dev            # runs src/index.ts directly, no build step
```

The server speaks MCP on stdin/stdout and prints nothing but diagnostics to
stderr, so running it in a terminal looks like it hangs — that is correct. It is
meant to be launched by an MCP host.

## Connect to an MCP agent

Add this to your MCP host configuration (Claude Desktop's
`claude_desktop_config.json`, `.mcp.json` for Claude Code, or the equivalent
file for your host), using an absolute path to the project:

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

To run from source without building, point at the TypeScript entry point
instead — Node executes it directly:

```json
{
  "mcpServers": {
    "shop-sql": {
      "command": "node",
      "args": ["/absolute/path/to/shop-sql-mcp/src/index.ts"]
    }
  }
}
```

To read a database somewhere else:

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

For Claude Code you can also register it from the command line:

```bash
claude mcp add shop-sql -- node /absolute/path/to/shop-sql-mcp/dist/index.js
```

## Tools

### `list_tables`

No arguments. Returns the user tables; internal `sqlite_*` tables are hidden.

```json
{
  "tables": [
    { "name": "customers" },
    { "name": "order_items" },
    { "name": "orders" },
    { "name": "products" }
  ]
}
```

### `describe_table`

```ts
{ table: string }
```

Reads the schema live from SQLite — nothing is hardcoded — and reports columns,
types, nullability, primary keys and foreign keys:

```json
{
  "table": "order_items",
  "columns": [
    { "name": "id", "type": "INTEGER", "nullable": false, "primaryKey": true },
    { "name": "order_id", "type": "INTEGER", "nullable": false, "primaryKey": false }
  ],
  "foreignKeys": [
    { "column": "order_id", "referencesTable": "orders", "referencesColumn": "id" },
    { "column": "product_id", "referencesTable": "products", "referencesColumn": "id" }
  ]
}
```

An unknown name is a recoverable error, not a crash:

```json
{ "error": { "code": "TABLE_NOT_FOUND", "message": "TABLE_NOT_FOUND: Table \"foo\" does not exist." } }
```

Note: a column that is an `INTEGER PRIMARY KEY` is reported as `nullable: false`.
SQLite's `table_info` says otherwise, but such a column is a rowid alias and can
never hold `NULL`.

### `query_database`

```ts
{ sql: string; limit?: number; offset?: number }
```

Runs one read-only statement — `SELECT ...` or `WITH ... SELECT ...` — with
`JOIN`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, subqueries, aggregates and
date filtering all supported.

```json
{
  "columns": ["category", "revenue"],
  "rows": [["Electronics", 1234567.89]],
  "returnedRows": 1,
  "limit": 100,
  "offset": 0,
  "hasMore": false
}
```

Rows are arrays of values in `columns` order. That keeps result payloads compact
and stays unambiguous when a query produces two columns with the same name.

Failures come back as a normal tool result with `isError` set and a short,
actionable payload, so the agent can fix its SQL and retry:

```json
{ "error": { "code": "SQL_ERROR", "message": "no such column: total" } }
```

Error codes: `SQL_ERROR`, `READ_ONLY_VIOLATION`, `MULTIPLE_STATEMENTS`,
`TABLE_NOT_FOUND`, `INVALID_ARGUMENT`, `DATABASE_UNAVAILABLE`. Stack traces are
never returned.

## Pagination

Pagination is enforced by the server, not by the model's SQL.

- `limit` defaults to **100**, maximum **500**; `offset` defaults to **0**.
- The agent's query is wrapped as `SELECT * FROM (<your sql>) LIMIT ? OFFSET ?`,
  so a query carrying its own `LIMIT 100000` still cannot return more rows than
  `limit`.
- The server internally fetches `limit + 1` rows to decide `hasMore` without a
  second counting query, and returns at most `limit`.
- A single call therefore never returns more than 500 rows, which is what keeps
  a wide `SELECT *` from flooding the model's context.

To page through results, keep the SQL identical (with a deterministic
`ORDER BY`) and advance `offset` by `limit` while `hasMore` is true.

## Read-only safety

Two independent layers, so neither one is load-bearing on its own.

**1. SQL validation** (`src/sqlSafety.ts`). A small lexer skips comments, string
literals and quoted identifiers, then requires that:

- the statement starts with `SELECT` or `WITH` — a naive `startsWith("SELECT")`
  would reject valid read-only CTEs;
- there is exactly one statement (anything after the first `;` is rejected, and
  a `;` inside a literal or comment is not a separator);
- no forbidden keyword appears anywhere, including nested inside a CTE:
  `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `DROP`, `ALTER`, `REPLACE`, `ATTACH`,
  `DETACH`, `VACUUM`, `REINDEX`, `PRAGMA`, `ANALYZE`, `BEGIN`, `COMMIT`,
  `ROLLBACK`, `SAVEPOINT`, `load_extension`, `writable_schema`.

Forbidden SQL is always rejected with an explicit error — never silently
ignored, and never partially executed. `REPLACE(a, b, c)` is still allowed as a
scalar function, since only the `REPLACE INTO` statement is a write.

**2. The SQLite connection itself.** `shop.db` is opened with
`new DatabaseSync(path, { readOnly: true })`. Even if a write slipped past
validation, SQLite refuses it with *"attempt to write a readonly database"*. The
test suite asserts this directly by issuing writes on the connection while
bypassing the validator.

Bad or forbidden queries are returned as tool errors and never terminate the
process, so a session survives any number of failed attempts.

## Run tests

```bash
npm test
```

Runs the **deterministic** suite only — no network, no API keys, no LLM. Node's
built-in test runner executes the TypeScript sources directly. Coverage
includes: `list_tables`, `describe_table` (columns, types, nullability, primary
keys, foreign keys, unknown tables), simple selects, filtering, aggregation,
joins, `GROUP BY`, read-only CTEs, date filtering, pagination (default limit,
maximum limit, offset, `hasMore` boundaries), invalid SQL, unknown columns and
tables, rejection of `INSERT`/`UPDATE`/`DELETE`/`CREATE`/`DROP`/`ALTER`/
`REPLACE`/`ATTACH`/`DETACH`/`VACUUM`/`REINDEX`/`PRAGMA` and multiple statements,
proof that the database is byte-identical after every rejected write, and
end-to-end MCP calls over stdio confirming the server stays usable after errors.

## Run eval manually

```bash
export ANTHROPIC_API_KEY=sk-...
npm run eval
```

**Start this manually.** It is deliberately excluded from `npm test` because it
drives a real LLM against the real MCP server over stdio and makes paid API
calls.

It spawns the server, hands the model the three MCP tools plus a
`submit_answer` tool whose JSON schema is fixed per task, and compares the
**structured** answer against a reference value computed directly from SQLite —
not against natural-language text. Tasks cover table discovery, multi-step
schema discovery, filtering, aggregation, joins, customer spending, customer
order counts, product sales, category revenue, revenue in 2025, and a
destructive request that must be refused (the check also verifies the database
is unchanged afterwards).

Optional: `EVAL_MODEL` (default `claude-sonnet-5`) and `EVAL_MAX_STEPS`
(default `12`). Exit code is non-zero if any task fails.

## Layout

```text
src/
  index.ts       MCP server: tool registration, stdio wiring, error shaping
  db.ts          read-only connection, path resolution, row/value normalisation
  tools.ts       the three tools: list_tables, describe_table, query_database
  sqlSafety.ts   single-statement read-only SQL validation
tests/
  sqlSafety.test.ts   validator, allowed and forbidden SQL
  tools.test.ts       tools against the real shop.db
  mcp.test.ts         end-to-end over stdio with a real MCP client
eval/
  tasks.ts       eval tasks and their SQLite reference values
  run.ts         LLM + MCP eval runner (manual)
shop.db
```

## Dependencies

| Package                          | Why                                                                 |
| -------------------------------- | ------------------------------------------------------------------- |
| `@modelcontextprotocol/server`   | The official MCP TypeScript SDK (v2). Provides `McpServer` and the stdio transport, so the protocol is not implemented by hand. |
| `zod`                            | Required by the SDK for tool input/output schemas; it is what publishes machine-readable argument types to the agent. |
| `typescript`, `@types/node`      | Dev only: build and typecheck.                                      |
| `@modelcontextprotocol/client`   | Dev only: the official MCP client, used by the stdio end-to-end tests and the eval runner. |

SQLite comes from Node's built-in `node:sqlite`, tests from Node's built-in test
runner, and the eval's HTTP calls from built-in `fetch` — no driver, ORM, query
builder, web framework, logger, test framework, SQL parser or LLM SDK is
installed.