sqlite-guard-mcp

Let an AI agent work on your SQLite database without trusting it. An MCP server with four tools — schema, query, execute, audit_log — and three guarantees:

1. Reads can't write. query runs on a separate connection opened SQLITE_OPEN_READONLY at the C level. A disguised write (/* just checking */ UPDATE …) isn't caught by a regex — it's refused by SQLite itself. Enforcement by construction, not by inspection.
2. Writes are dry-run first. execute runs the statement inside a transaction that is always rolled back, and reports what would have happened (changes, lastInsertRowid). Committing requires re-calling with confirm: true — the agent must state its intent twice, and its operator sees the intended effect in between.
3. Committed writes leave a trail you can walk back. Before any commit, the DB file is snapshotted (VACUUM INTO — transactionally consistent even under WAL with active readers). The write and its append-only audit row commit in the same transaction: you can't end up with a change that has no audit entry, or an audit entry for a change that didn't happen.

Why this exists

I run a personal-finance dashboard whose UI is deliberately read-only — every number in it is edited by AI agents over SQL. That architecture is wonderful (no forms, no write endpoints, agents keep the books) right up until an agent runs a plausible-looking UPDATE with a wrong WHERE clause.

The insight from operating that system: what agent SQL needs isn't a smarter model, it's the same thing human ops needed for decades — read/write separation, a plan/apply step, backups, and an audit log. This server packages those four things behind MCP so any agent (Claude Code, or anything else that speaks MCP) gets them for free on any SQLite file.

Quick start

npm install
npm run demo        # full guardrail walkthrough on a temp DB — 10 seconds, no setup
npm test            # 10 tests: rollback semantics, backup consistency, audit atomicity

Wire it into Claude Code:

claude mcp add sqlite-guard \
  -e SQLITE_GUARD_DB=/path/to/app.db \
  -- npx tsx src/server.ts

Or inspect it interactively: npx @modelcontextprotocol/inspector npx tsx src/server.ts (with SQLITE_GUARD_DB set).

The tools

Tool	Contract
schema	Every table with columns, types, PKs, row counts — the agent's map.
query	Read-only SQL with ? params. Row-capped (SQLITE_GUARD_MAX_ROWS, default 200) so a SELECT * on a big table can't blow out the agent's context window; the real count is always reported.
execute	Single write statement with ? params. Dry-run by default → confirm: true to commit (backup + audit). Single-statement only — which also kills ; DROP TABLE piggybacking.
audit_log	The append-only trail of every committed write, newest first.

Design notes

• The dry-run is real execution, not EXPLAIN-based estimation: the statement genuinely runs (triggers, constraints, and all) and is rolled back. What you see is what a commit would do — including the constraint error you'd hit.
• Restore is one file copy. Backups are plain SQLite files named <db>-backup-<timestamp>; recovery from a bad committed write is cp + restart, and the audit row records exactly which snapshot predates which write.
• BEGIN/COMMIT from the agent are refused — transaction lifecycle belongs to the guard. Otherwise a stray BEGIN would let a later statement commit the "rolled-back" dry run.
• The audit table is readable through query on purpose. Transparency beats secrecy here: the agent can review its own history, and the operator can ask the agent to summarize what it changed and when.
• Statement classification (classify.ts) is labeling, not security. It tags audit rows and error messages; the security boundaries are the connection flag and the transaction protocol. Anything a regex decides, a determined input can undecide.

Limitations (honest ones)

• Per-table allow/deny lists aren't implemented (SQLite's authorizer API isn't exposed by better-sqlite3); the boundary is per-database. Point the server at a database you intend agents to manage.
• VACUUM INTO needs SQLite ≥ 3.27 (2019); older builds fall back to a file copy, which is only safe when quiescent.
• One MCP server = one database file. Run several instances for several files.

Stack

TypeScript · @modelcontextprotocol/sdk (stdio transport) · better-sqlite3 · zod · vitest.

License

MIT © Brandon Ta