mcp-sql-explorer

An MCP server that lets an LLM client explore a SQLite database without being able to change it. It exposes schema discovery, sampling, querying and query plans as MCP tools, publishes the schema as resources, and ships an "analyse this table" prompt. Every query passes through several independent safety layers: a statement guard, SQLite's own read-only classification, a read-only connection, row/byte/cell caps and a hard timeout enforced by killing a worker process. Built on the official MCP TypeScript SDK (v2) and better-sqlite3.

Features

• Resources: sqlite://schema (all DDL) and sqlite://table/{name} (one table as JSON, listed per table, with name completion).

• Prompt: analyse-table (arguments table, optional focus) embeds the table's structure and asks the model for a data-quality, distribution, relationship and anomaly review using the tools.

• Structured output: every tool declares an output schema and returns structuredContent next to a Markdown rendering for the model.

• Safety: see Safety model.

• Demo data: npm run demo:db generates a deterministic music-store database (artists, albums, tracks, customers, invoices, invoice lines, a view) from a seeded PRNG.

Related MCP server: mcp-sqlite

Architecture

flowchart LR
  host["MCP host<br/>(Claude Code, Claude Desktop, ...)"] -- "JSON-RPC over stdio" --> tools

  subgraph server["mcp-sql-explorer process"]
    tools["tools / resources / prompt<br/>(McpServer, zod schemas)"]
    guard["statement guard<br/>one SELECT or WITH"]
    catalog["schema catalog<br/>identifier resolution"]
    exec["executor<br/>FIFO queue + timeout"]
    tools --> guard --> exec
    tools --> catalog --> exec
  end

  subgraph worker["worker child process"]
    conn["read-only connection<br/>query_only, trusted_schema=OFF"]
    classify["sqlite3_stmt_readonly check"]
    caps["row / byte / cell caps"]
    conn --> classify --> caps
  end

  exec -- "IPC request / response" --> conn
  exec -. "SIGKILL on timeout" .-> worker
  conn --> db[("SQLite file")]

All SQLite work runs in a child process. The parent process speaks MCP, validates input and enforces the time limit; it never opens the database itself.

Quickstart

Requires Node.js 22 or newer.

npm ci
npm run build
npm run demo:db                 # writes data/music-store.db
node dist/index.js --help

Claude Code

claude mcp add --transport stdio music-store -- \
  node /absolute/path/to/mcp-sql-explorer/dist/index.js --db /absolute/path/to/data/music-store.db

Use --scope project to write the entry to .mcp.json so it is shared with a repository. Limits can be passed as flags after the -- or as environment variables with --env (see Configuration).

Claude Desktop

Add the server to claude_desktop_config.json (Settings > Developer > Edit Config):

{
  "mcpServers": {
    "music-store": {
      "command": "node",
      "args": [
        "/absolute/path/to/mcp-sql-explorer/dist/index.js",
        "--db",
        "/absolute/path/to/data/music-store.db"
      ],
      "env": { "SQL_EXPLORER_TIMEOUT_MS": "3000" }
    }
  }
}

Configuration

Usage examples

Calls as an MCP client sends them (tools/call arguments):

// run_query: parameters are bound, never interpolated
{ "name": "run_query",
  "arguments": { "sql": "SELECT title, duration_ms FROM tracks WHERE album_id = ? ORDER BY track_number",
                 "params": [12], "max_rows": 20 } }

// sample_rows: names are resolved against the live schema (case-insensitive) and quoted
{ "name": "sample_rows", "arguments": { "table": "Customers", "columns": ["city", "country"], "random": true } }

// explain_query: plan only, the query is not executed
{ "name": "explain_query",
  "arguments": { "sql": "SELECT * FROM invoices WHERE customer_id = ? ORDER BY invoiced_at", "params": [7] } }

explain_query returns the plan as rows (structuredContent.plan) and as text:

QUERY PLAN
`--SEARCH invoices USING INDEX idx_invoices_customer_date (customer_id=?)

Rejected input comes back as a tool error (isError: true) whose text starts with a stable code, so the model can correct itself:

[NOT_SELECT] Only read-only queries are allowed: a single SELECT or WITH ... SELECT statement. Got DELETE.
[NOT_READ_ONLY] SQLite reports that this statement would modify the database. Only read-only SELECT queries are allowed.
[MULTIPLE_STATEMENTS] Exactly one statement is allowed per call; found 2. Remove the extra statements.
[UNKNOWN_TABLE] No table or view named "track". Known tables: albums, artists, customers, ...
[TIMEOUT] The query exceeded the 5000 ms time limit and was cancelled. Add a LIMIT, ...

Safety model

Each layer works on its own; none relies on another having caught the problem.

Design decisions and trade-offs

• Child process instead of in-process execution or a worker thread. better-sqlite3 (like node:sqlite) is synchronous and does not expose sqlite3_interrupt or a progress handler, so a statement stuck inside SQLite cannot be stopped from JavaScript. Worker.terminate() only takes effect when control returns to JavaScript; a process can be killed at any point. The cost is one extra process and an IPC hop per query, which is small next to LLM latency.

• Serialised execution. One worker runs one statement at a time. This keeps the timeout precise (a kill only ever cancels the statement that caused it; queued requests continue on a fresh worker) at the cost of no parallelism, which an interactive MCP session rarely needs.

• The timeout covers execution only. The worker reports ready after opening the database, and the clock starts when a statement is handed to a ready worker, so start-up time never makes a cheap query fail.

• A guard and SQLite's classifier. The text guard gives clear, early error messages; the authoritative read-only decision is SQLite's, because a hand-written SQL parser will always miss something.

• better-sqlite3 over node:sqlite. Both work on Node 22 without flags. better-sqlite3 exposes statement.readonly/statement.reader (the classifier above) and ships N-API prebuilds, so npm ci needs no compiler. node:sqlite still prints an experimental warning on Node 22.

• Markdown text plus structured content. Models read small Markdown tables reliably; programs get typed structuredContent validated against the declared output schema.

• Errors as tool results. Safety rejections are isError results with a code, not protocol errors, because the model is the party that can fix the query. Resource and prompt lookups of unknown tables are protocol errors (-32602), because the host made that request.

Testing

npm test            # vitest, 112 tests
npm run typecheck   # strict TypeScript, sources and tests
npm run lint        # eslint + typescript-eslint
npm run smoke       # compiled server over stdio (needs npm run build and npm run demo:db)

The tests need no network or external services. They build the demo database (and a few odd schemas) in a temp directory and talk to a real server through the SDK's in-memory transport pair, with real worker processes underneath. Covered:

• every tool, the resources, resource template, completions and the prompt;

• every safety rule above, including hostile input that must leave the database file byte-for-byte unchanged, a WITH ... DELETE, a runaway recursive CTE that must be killed while queued queries still succeed, a worker killed mid-query, and a file that is not a SQLite database;

• the statement scanner on its own (comments, literals, quoted identifiers, unterminated tokens);

• the stdio entry point, launched as a subprocess by the SDK's stdio client.

CI runs lint, typecheck, build, tests and the stdio smoke test on Node 22. Locally the suite was run on Node 22.23.4 and 25.6.1 (macOS, Apple M4 Pro): 112 tests pass in about 2.5 seconds.

Limitations and roadmap

• SQLite only. A Postgres backend would fit behind the executor (read-only role, SET TRANSACTION READ ONLY, statement_timeout), but is not implemented.

• No memory cap: a query such as SELECT randomblob(500000000) can make the worker allocate a lot of memory. The worker is a separate process, so a crash does not take the server down, but limiting memory needs an OS or container limit. (PRAGMA hard_heap_limit has no effect in the better-sqlite3 build, which disables SQLite's memory statistics.)

• trusted_schema = OFF and query_only protect the file, not the host: a schema that relies on application-defined functions will fail to query rather than run them.

• Row counts in describe_table are exact count(*); on a very large table the count can hit the timeout and is then reported as null.

• stdio transport only; a Streamable HTTP entry point would need authentication in front of it.

• Possible next steps: per-table allow/deny lists, column masking for sensitive fields, query result caching, and Postgres.

License

MIT, see LICENSE.