mcp-db-explorer

A read-only MCP server that lets an LLM explore a SQL database in plain English — without being able to change anything in it.

Point it at a SQLite file and any MCP client (Claude Desktop, Claude Code, your own) can ask questions like "which order statuses are most common?" or "what columns does the users table have?" — no SQL from the human, no write access for the model.

you  ▸ Which countries do most of our users come from?

llm  ▸ list_tables            → users (25), products (6), orders (240)
     ▸ describe_table(users)  → id, email, full_name, password_hash*, api_key*, country, created_at
     ▸ query("SELECT country, COUNT(*) n FROM users GROUP BY country ORDER BY n DESC")

     US leads with 5 users; AU, CA, DE, GB and PK have 4 each.

                                          * values withheld — see Redaction below

---

Why this exists

Wiring an LLM to a database is easy. Wiring it up so you'd let it near production data is not, and the two hard parts aren't the parts that look hard:

1. Scoping the tool surface so it can't leak or damage anything — and doing it with capabilities rather than instructions.
2. Shaping responses so the model returns something accurate rather than something plausible — most of which is about being explicit when data is missing.

Everything below is about those two problems. The MCP plumbing is ~20 lines and the SDK does it for you.

---

Run it

Requires Node 22.5+ (for the built-in node:sqlite).

npm install
npm test          # builds, seeds a demo DB, runs the server over real stdio

npm test starts the actual binary and drives it with JSON-RPC exactly as a client would, then asserts the guards hold. It's the fastest way to see what the server does.

Then wire it into a client. For Claude Desktop, in claude_desktop_config.json:

{
  "mcpServers": {
    "db-explorer": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-db-explorer/dist/index.js", "/absolute/path/to/your.db"]
    }
  }
}

Tools

Tool	Purpose
list_tables	Every table with its row count. The cheap first call.
describe_table	Columns, types, nullability, primary keys — and which columns are withheld.
sample_rows	A few real rows, so the model stops guessing at data formats.
query	A validated read-only SELECT.

---

Part 1 — Capability restriction beats instruction

There is no prompt anywhere in this server telling the model not to write to the database. Asking nicely is not a security boundary: it's one clever user message away from failing, and it fails silently.

Instead the model cannot write, enforced in two independent layers:

Layer 1 — the connection is opened read-only. openDatabase() passes { readOnly: true }, so SQLite itself rejects any write. Even if every line of validation below were deleted, a DELETE would still fail.

Layer 2 — SQL is validated before it reaches SQLite. assertSelectOnly() requires a single statement starting with SELECT or WITH, and rejects 24 forbidden verbs.

Two layers because one check is one bug away from failing open — which is precisely what happened here (see below).

The bit that's easy to get wrong

You cannot scan raw SQL for forbidden keywords. This is a perfectly legitimate query:

SELECT 'we should not drop this table' AS note

A naive sql.includes('DROP') rejects it, and the model — which did nothing wrong — retries, rephrases, and eventually gives up or works around you. So neutralize() blanks comments and string/identifier literals before the keyword scan, preserving offsets. There's a test for exactly this case.

The same asymmetry runs the other way: describe_table needs PRAGMA table_info, which assertSelectOnly forbids. That's deliberate. The server may use privileged reads to build a safe answer; the model may not issue them. Fixed, audited queries on one side of the boundary; arbitrary SQL on the other.

Redaction

Columns are withheld by name, not value — the server can't know what a secret looks like, but whoever named the column password_hash already told us. Defaults cover passwords, tokens, API keys, private keys, card numbers, SSNs, and session IDs; override with SENSITIVE_COLUMNS.

Withheld columns are reported, not hidden:

Withheld columns (present, values not shown): api_key, password_hash.

A model that doesn't know a column was withheld will describe the data as complete. Telling it "this exists but you can't see it" is strictly more useful than pretending it isn't there.

A bug worth keeping in the README

The original pattern was /pass(word|wd|_hash)?$/i. It's end-anchored, so it matches password and not password_hash — the exact column you least want to leak. It looked right in review and passed a glance. The smoke test caught it on the first run.

The fix is segment-anchored — /(^|_)pass(word|wd|phrase)?(_|$)/i — which matches password_hash and hashed_password while still leaving passenger_count alone. The same anchoring bug was in the SSN pattern: \bssn\b never fires on user_ssn, because _ is a word character.

The lesson isn't "write tests." It's that a security guard which fails open produces no error, no stack trace, and no symptom — just data quietly going somewhere it shouldn't. It has to be tested from the outside, against the real binary, asserting on what actually came back.

---

Part 2 — Accurate beats plausible

An LLM will always produce an answer. If the tool response is ambiguous, it fills the gap with something reasonable-sounding — and you won't be able to tell the difference. So every response here is written to remove the gaps.

Empty is not the same as failed. These are three different facts and the model gets three different answers:

The query ran successfully and matched 0 rows.
SQLite rejected the query: no such column: emial
Rejected: The keyword "DELETE" is not permitted — this server exposes read access only.

Collapse them into one empty result and the model invents a reason for the emptiness. It will sound confident.

Truncation is never silent. Results cap at 100 rows. The query fetches 101 so it can know whether more exist rather than guess, and says so:

100 row(s) returned. Result was truncated at 100 rows — more rows match.
Add LIMIT/OFFSET or an aggregate to see the rest.

Silently returning 100 of 240 rows is how you get a confident, precise, wrong answer.

Errors are written for the reader. SQLite's own message (no such column: emial) is passed through rather than flattened to "query failed" — the model can act on a typo it can see. Guard rejections say what was wrong and what to do instead; a rejection the model can't act on just becomes a retry loop.

Tool descriptions state when to call, not just what. list_tables says "start here — it is the cheapest way to learn the shape of the data." Trigger conditions in the description measurably change whether a tool gets called at the right moment.

---

Notes

• Results render as TSV. Cheaper in tokens than JSON and easier for a model to read across rows.
• node:sqlite is still flagged experimental in Node; it's used here to keep the dependency footprint at two packages and avoid a native build step.
• Stdio transport means stdout is the protocol channel. Every diagnostic in this server goes to stderr — one stray console.log corrupts the JSON-RPC stream and the client dies with a parse error pointing nowhere near the cause.
• Built on @modelcontextprotocol/server v2.

License

MIT