# mcp-db-explorer

A read-only [MCP](https://modelcontextprotocol.io) 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`).

```bash
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`:

```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:

```sql
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