db-mcp

A read-only MCP 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

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

npm run build

Run

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:

{
  "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

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

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.