Shipping a read-only MCP SQLite server

The problem

I wanted Claude to answer questions about a local SQLite database without handing it a SQL console. "Give the model a run_sql tool" is the obvious move and the wrong one — one confidently-wrong DELETE and you're restoring from backup at 2am AEST.

So the constraint was simple: read by default, write only where I explicitly allow it, and never let a single query hang the server.

The architecture

Instead of one god-tool, I modelled the database as a small set of intention-revealing tools. The model picks the right one; it can't reach for DROP TABLE because that verb doesn't exist in the surface I expose.

• query — parameterised SELECT only, rejected if it parses to anything else
• list_tables — schema introspection
• describe — column types for one table
• mutate — gated behind an allowlist, off unless configured

Everything runs over stdio so it works in both Claude Desktop and Claude Code with no network surface.

The code that mattered

The guard rail that earns its keep is the statement check — every incoming query is parsed and rejected unless it's a single read:

import Database from "better-sqlite3";
 
const READ_ONLY = /^\s*select\b/i;
 
function assertReadOnly(sql: string) {
  if (!READ_ONLY.test(sql) || sql.includes(";")) {
    throw new Error("Only single read-only SELECT statements are allowed.");
  }
}
 
export function query(db: Database.Database, sql: string, params: unknown[] = []) {
  assertReadOnly(sql);
  const stmt = db.prepare(sql);
  // Hard row cap so a careless SELECT * can't stream the whole table into context.
  return stmt.all(...params).slice(0, 500);
}

Writes are opt-in and narrow — the server reads an allowlist of named, pre-written statements rather than accepting arbitrary SQL:

const WRITE_ALLOWLIST: Record<string, string> = {
  mark_done: "UPDATE tasks SET done = 1 WHERE id = ?",
};
 
export function mutate(db: Database.Database, name: string, params: unknown[]) {
  const sql = WRITE_ALLOWLIST[name];
  if (!sql) throw new Error(`No write named "${name}".`);
  return db.prepare(sql).run(...params);
}

The model never composes a write. It can only invoke one I've already vetted.

Results

• 4 tools, and the only write path is the allowlist — there's no way to express a destructive query.
• p95 under 40ms locally on a ~50k-row database, thanks to better-sqlite3 being synchronous and the 500-row cap.
• Snapshot tests for every tool call via mcp-inspector-cli, so a refactor that changes a response shape fails CI instead of a user's session.

What I'd do differently

The regex statement check is deliberately blunt — it rejects valid multi-CTE reads that happen to contain a semicolon. Next version I'll parse to an AST and assert the top-level node is a SELECT, which is both stricter and more permissive in the right ways.

I'd also expose query cost estimates as part of describe, so the model can self-limit before running something expensive rather than relying on my row cap to save it.

For the wider "why model the world as tools at all" argument, I wrote that up separately — it's the through-line of everything I build here.