readonly-d1-mcp

A read-only MCP server for a Cloudflare D1 database, built so that giving an AI client (or a colleague) access to your data does not mean trusting it with your data.

MCP is a standard way for AI clients to call tools. This server exposes three: list the tables, describe one, run a SELECT. The interesting part is everything around them.

Safety model

Three layers, and it matters which one is the guarantee.

Why REST instead of a Workers D1 binding? A binding always has full access, so it cannot give the credential-level guarantee. That is the one deliberate cost of this design.

Related MCP server: AI SQL Agent MCP Server

What a client sees

The data dictionary is the part that earns its keep

A model that sees only column names writes plausible SQL that returns a wrong answer. The dictionary carries the facts a column name hides: "empty text is stored as '', never NULL", "this table has no personal data, so finding nothing proves nothing", "never join these raw tables yourself, use the view". The notes in the repository describe the tables of the companion project zero-cost-marketing-data-hub; replace them with the traps of your own data. A note is worth adding the moment a wrong number was nearly shipped because of something the schema did not say.

First-use approval

1. A person with a valid token calls a tool for the first time.

2. The call is blocked and returns a one-time link (valid one hour). A retry returns the same link, not a new one.

3. A human opens it. GET only shows a button; only POST approves. Chat apps, mail scanners and link previewers open links automatically, so if GET approved, pasting the link into a message could grant access with nobody deciding.

4. Only then does the person's first call go through. The link is single use.

Set up

Needs Node 20+, a Cloudflare account and a D1 database.

npm install
cp wrangler.toml.example wrangler.toml      # fill in your own ids; never commit it
npx wrangler kv namespace create STATE      # paste the id into wrangler.toml
npx wrangler secret put CF_D1_READ_TOKEN    # a token with ONLY  Account > D1 > Read
npx wrangler deploy
npm run token -- "Ana Perez"                # prints a token once + the command to register it

Check the guarantee yourself, once. This repository's tests prove the guard and the flow; they cannot prove how your token is scoped. Send a real write with your read token to the D1 REST endpoint and confirm Cloudflare refuses it. A harmless way to do it is an update that rewrites one row with the value it already has, for example UPDATE some_table SET some_column = some_column WHERE id = (SELECT MIN(id) FROM some_table): if the token is read-only it is refused and nothing changes; if it succeeds, your token is too broad, so fix it. Do not use a statement that changes no rows (such as DELETE ... WHERE 1 = 0) as the test: see the next paragraph.

Revoke a person: delete their token:<hash> key (the script prints the command). Nobody else is affected and nothing is redeployed.

Calling it

The server speaks MCP over HTTP at /mcp and expects Authorization: Bearer <token>. How you register a remote server differs between MCP clients, so follow your client's documentation for custom headers. A raw check:

curl -X POST https://<your-worker>/mcp \
  -H "Authorization: Bearer <token>" \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/list"}'

Try it without an account

node scripts/mock-d1-api.mjs      # a stand-in D1 REST endpoint with demo data (needs Node 22.5+)
npx wrangler dev --var CF_API_BASE:http://127.0.0.1:8799 --var CF_D1_READ_TOKEN:local

The mock refuses writes the way a read-only token would. It exists to exercise this server end to end, not to imitate D1.

What was tested, and what was not

• 67 unit tests (npm test): the SQL guard (accepted and rejected queries, including comment tricks, a second statement, a data-modifying CTE and breaking out of the row cap), token hashing and revocation, the approval flow (single use, retries, GET does not approve), the REST client, and the tools against real SQLite.

• An end-to-end run on a local wrangler dev against the mock: no token and a wrong token are refused, the first call is blocked with a link, opening the link does not approve, POST approves, the three tools work, writes and a second statement are refused, the link cannot be reused.

• Checked against live D1 (2026-10-20): the SQL behind list_tables, the pragma_table_info query behind describe_table, and the row-cap wrapper with a trailing comment all ran correctly through the real D1 REST API (sent through an existing read-only D1 connection, schema metadata only, no table data).

• The write refusal was checked against live D1 too (2026-10-20): with a read-scoped token, a real one-row UPDATE (rewriting the value it already had, so no data could change) was refused with error 7500, "You do not have permission to perform this operation". Note what is not refused: statements that change no rows, such as DELETE ... WHERE 1 = 0 or UPDATE ... WHERE 1 = 0, returned success with changes: 0. D1 enforces the permission when a write actually happens, not by statement type. That is why the guarantee is described as "writes are impossible" and not "write statements are rejected", and why the SQL guard still refuses them: it should not depend on that distinction.

• Not tested: a deployment of this Worker on Cloudflare, the bound-parameter (params) path of the REST call against live D1, and a real MCP client.

• Not measured: CPU time per request on the Workers Free plan (10 ms). An MCP server object is built on every request; test under your own load.

Known limits

• The approval route has no identity check of its own. Anyone holding the link can approve, so put /approve behind an identity layer such as Cloudflare Access, and remember the Worker is also reachable on its workers.dev address unless you disable it.

• The row cap limits the size of the response, not the rows the database reads. A full-table aggregate still scans the table.

• Workers KV free plan: 1,000 writes per day (checked 2026-10-20). Tokens and trust are read-mostly, but each new approval writes, and audit writes once per call. Audit is therefore off by default (AUDIT_ENABLED).

• A token is a bearer secret. Hashing it in KV protects against a leaked namespace, not against a leaked token. There is no per-person rate limit.

• Read access is still access. Decide what the database holds before exposing it; this server cannot hide a column you stored.

Layout

src/sql-guard.js     single-SELECT check + row cap
src/auth.js          hashed per-person bearer tokens
src/approval.js      first-use approval (single use, retry-safe)
src/approve-route.js GET shows a button, POST approves
src/d1-client.js     D1 REST client (read-only token)
src/tools.js         list_tables, describe_table, query
src/dictionary.js    notes the model reads before writing SQL
src/audit.js         optional, best-effort trail
src/server.js        MCP tool registration + the common gate
src/worker.js        routing: /approve, auth, /mcp
scripts/             create-token.mjs, mock-d1-api.mjs
test/                node:test suite

License

MIT