# sqlite-explorer-mcp

A production-grade [Model Context Protocol](https://modelcontextprotocol.io) server that exposes a
**read-only** SQLite database to any MCP client (Claude Code, Claude Desktop, …). It ships both
transports, real input/output bounds, structured logging, auth, tests, CI, and a container.

Built on the official `@modelcontextprotocol/sdk` (v1.29.x).

## Design

| Capability | MCP primitive | Why |
|------------|---------------|-----|
| Discover tables / columns | **Resource** (`schema://tables`, `schema://table/{name}`) | Read-only context the model reads; cacheable, side-effect-free |
| Run a query | **Tool** (`query`) | An action with cost/risk: validated input, can fail, auditable |

The server is defined **once** (`src/server.ts`) and exposed over two transports from shared code:

- `src/stdio.ts` — for **local** clients (the client launches it as a subprocess).
- `src/http.ts` — Streamable HTTP for **remote** clients on other machines.

## Safety

- **The read-only boundary is the engine, not a string check.** The DB is opened
  `{ readonly: true }` with `PRAGMA query_only = ON`; SQLite rejects any write regardless of the SQL
  sent. The single-statement / SELECT-only gate in `src/db.ts` only produces clearer errors.
- **Output is bounded** — `MAX_ROWS` cap + `busy_timeout` so a runaway scan can't exhaust memory.
- **Secrets via Zod-validated env** (`src/env.ts`); nothing hardcoded. HTTP requires a 32+ char
  `MCP_BEARER_TOKEN`, checked with a constant-time compare and fail-closed.
- **The stdout rule** — on stdio, stdout is the JSON-RPC channel. All logging goes to **stderr**
  (`src/logger.ts`); an ESLint `no-console` rule enforces it.

## Quick start

```bash
npm install
npm run seed          # creates data.db (sample customers + orders)
npm test              # unit + in-memory integration tests
npm run build
```

### Run locally (stdio) + connect to Claude Code

```bash
npx @modelcontextprotocol/inspector node build/stdio.js   # debug standalone first
claude mcp add --transport stdio sqlite -- node "$(pwd)/build/stdio.js"
```

### Run remotely (Streamable HTTP)

```bash
export MCP_BEARER_TOKEN=$(openssl rand -hex 32)
npm run start:http
# from any machine (behind TLS in production):
claude mcp add --transport http sqlite https://your-host/mcp \
  --header "Authorization: Bearer $MCP_BEARER_TOKEN"
```

Confirm with `claude mcp list` and `/mcp` inside a session.

### Docker

```bash
docker build -t sqlite-explorer-mcp .
docker run --rm -p 8080:8080 \
  -e MCP_BEARER_TOKEN=$(openssl rand -hex 32) \
  -e BIND_HOST=0.0.0.0 \
  -v "$(pwd)/data.db:/app/data.db:ro" \
  sqlite-explorer-mcp
```

Terminate TLS at a reverse proxy in front of the container; never expose the plain HTTP port
publicly. For per-user identity/revocation, Streamable HTTP also supports OAuth 2.0 — ship the
bearer token first and add OAuth only when compliance requires named-user attribution.

## Configuration

All via environment (see `.env.example`): `SQLITE_PATH`, `PORT`, `BIND_HOST`, `MCP_BEARER_TOKEN`,
`MAX_ROWS`, `QUERY_TIMEOUT_MS`, `LOG_LEVEL`.

## License

MIT