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.

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

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

npm run build

Compiles src/ to dist/.

Run

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:

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

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

To read a database somewhere else:

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

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.

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

describe_table

{ table: string }

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

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

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

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

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

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

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

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

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.