# readonly-db-mcp

**Let an AI agent explore your MySQL database — safely.** A [Model Context
Protocol](https://modelcontextprotocol.io) server that gives Claude (or any MCP
client) the ability to run `SELECT` queries and inspect schema, indexes, and
foreign keys on your databases, with **write-protection enforced mechanically at
three independent layers**. Credentials never appear in tool input/output, logs,
git, or the conversation.

> Point an LLM at a real database and the obvious fear is that it writes —
> a stray `UPDATE`, a `DROP` in a hallucinated migration, a `DELETE` without a
> `WHERE`. This server makes that *structurally impossible*, not merely
> discouraged: even a query that slips past the parser is rejected by the
> database engine itself.

## Why this exists

AI agents are great at "why is this row wrong?", "what does this schema look
like?", and "which index would help this query?" — but you don't want to hand
them write access to find out. Existing options force a bad trade: give the
agent full DB credentials (scary), or copy data out into a sandbox (stale,
high-effort, and a fresh PII surface).

`readonly-db-mcp` is the third option: a thin, auditable boundary that lets the
agent read *live* data and metadata while making writes impossible by
construction.

## The guarantee — three independent layers

A query reaches your data only if it survives all three. They are independent on
purpose: each would hold even if the others were bypassed.

| Layer | Catches | Enforced by |
|---|---|---|
| **AST validation** (`safety.py`) | DDL, DML, statement stacking, `INTO OUTFILE`, dangerous functions (`SLEEP`, `BENCHMARK`, …), anything unparseable (fails **closed**) | this code, via [sqlglot](https://github.com/tobymao/sqlglot) |
| **`START TRANSACTION READ ONLY`** (`db.py`) | any `INSERT`/`UPDATE`/`DELETE` that slipped past the parser — InnoDB rejects it with `ERROR 1792` | the MySQL engine |
| **`MULTI_STATEMENTS` off** | `SELECT 1; DROP …` stacking | the PyMySQL driver |

Plus `max_execution_time` per statement and output caps (rows / cell width /
total characters) so a `SELECT *` can't hang the DB or flood the model's context
window.

**Prove it yourself** before trusting it — `scripts/verify_boundary.py` bypasses
the parser and confirms the *engine* rejects writes against your real database
(everything is rolled back). See [Verifying the boundary](#verifying-the-boundary).

### Honest limits (read these)

Defense-in-depth, not magic. The boundary protects **data integrity**; it does
not protect:

- **Query *load*.** A read-only transaction still lets a giant `SELECT` table-
  scan a huge table. Point connections at a **read replica** where one exists,
  and keep `max_rows` / `statement_timeout_ms` tight. Use read-only DB
  credentials as a fourth, belt-and-suspenders layer.
- **Data confidentiality.** Query *results* are sent to your MCP client's model
  provider, like any other tool output. If a connection holds PII, treat it
  accordingly — mark it `require_opt_in` (below) so it's off until you
  deliberately enable it.

## Quickstart

You don't need to install anything or create a file — [`uvx`](https://docs.astral.sh/uv/)
runs the published package on demand. Pick the path that fits you.

### Fastest: one connection, zero files (great for a local DB)

Put the connection in your MCP client's `env` block. For Claude Code:

```bash
claude mcp add --scope user readonly-db \
  --env RODB_HOST=127.0.0.1 \
  --env RODB_USER=root \
  --env RODB_PASSWORD=secret \
  --env RODB_DATABASE=mydb \
  -- uvx readonly-db-mcp
```

That's the whole setup — no config file, no `~/.config`, no keyring. If
`RODB_HOST`/`RODB_USER` are set and there's no config file, the server runs as a
single connection built from those vars.

### Guided: the `init` wizard (for a saved config file)

```bash
uvx readonly-db-mcp init      # prompts for host/user/password/db, writes the file,
                              # and prints exactly where it went + the next command
```

### Manual / multiple databases

```bash
pipx install readonly-db-mcp        # or: pip install readonly-db-mcp
mkdir -p ~/.config/readonly-db-mcp
cp config.example.toml ~/.config/readonly-db-mcp/config.toml   # then edit it
```

See [Configuration](#configuration) for the file format and where passwords come
from.

---

Once registered, open Claude Code, run `/mcp` to confirm `readonly-db` is
connected, and ask questions in English — *"what columns does the orders table
have?"*, *"show me the 10 newest customers"*, *"why is this query slow?"*.

> **Working from a clone instead of the published package?** Replace
> `uvx readonly-db-mcp` with `uv run --directory /path/to/readonly-db-mcp readonly-db-mcp`.

## Tools

| Tool | What it does |
|---|---|
| `run_select(sql, connection?, schema?, max_rows?, offset?)` | Run a validated, read-only `SELECT` (or `WITH`/`UNION`); paginate via `offset` |
| `explain(sql, connection?, schema?)` | `EXPLAIN FORMAT=JSON` query plan, without executing |
| `list_connections()` | The servers you can query: default schema, described schemas, gated/enabled state |
| `list_schemas(connection?)` | The schemas (databases) visible on a connection's server |
| `list_tables(connection?, schema?)` | Tables/views in a schema (name, type, engine, approx rows) |
| `describe_table(table, connection?, schema?)` | Columns: type, nullability, key, default, comment |
| `get_indexes(table, connection?, schema?)` | Indexes: columns, uniqueness, type, cardinality |
| `get_foreign_keys(table, connection?, schema?)` | Outbound + inbound foreign keys |
| `table_stats(table, connection?, schema?)` | Approx rows, data/index size, engine, timestamps |

`connection` defaults to your `default_connection`; `schema` defaults to that
connection's `default_schema`. The agent typically calls `list_connections`
first to discover what's available.

## Configuration

Two concepts, one file (`~/.config/readonly-db-mcp/config.toml`):

- **Connection** — a named server you can query (`host`, `port`, `user`, creds).
  The name is yours to choose; there is intentionally no built-in notion of
  "production".
- **Schema** — a namespace inside a server (MySQL's "schema"/"database"). One
  connection exposes many; queries can join across schemas on the same server.

```toml
default_connection = "shop"

[limits]                          # global; per-connection limits can override
max_rows = 1000
statement_timeout_ms = 15000

[connections.shop]
host = "db.example.com"
user = "readonly"
default_schema = "shop_core"
description = "Main application server"
  [connections.shop.schemas.shop_core]
  description = "Customers, orders, products"   # shown to the agent in list_schemas

[connections.warehouse]
host = "warehouse.example.com"
user = "readonly"
require_opt_in = true             # off until you enable it (see below)
  [connections.warehouse.limits]
  max_rows = 200
```

### The opt-in gate (default-safe)

Any connection with `require_opt_in = true` is **refused** unless you name it in
the `RODB_ENABLE_GATED` allowlist on the process that launches the server:

| `RODB_ENABLE_GATED` | Effect |
|---|---|
| unset / `0` | all gated connections refused (default) |
| `1` / `all` | every gated connection enabled |
| `warehouse` | only `warehouse` enabled |
| `warehouse,billing` | both enabled |

This is a neutral mechanism — a PII or production database is the canonical
thing you'd gate, but it works for anything you want off-by-default. With Claude
Code, set it on the registration: `claude mcp add ... --env RODB_ENABLE_GATED=warehouse ...`.

### Where secrets come from

Connections are non-secret and safe to commit. Only the password is a secret,
resolved per connection, in order: **`RODB_PASSWORD_<CONNECTION>` env →
OS keyring (`readonly-db-mcp` / connection name) → `password` in the config**.
A `.env` beside `config.toml` (or `RODB_ENV_FILE`) is loaded first and can supply
the env var (see `.env.example`). Discovery is cwd-independent — an MCP server's
working directory is the client's, so a project-relative `.env` would silently
not load.

### No config file at all (single connection)

If no config file exists but `RODB_HOST` and `RODB_USER` are set, the server runs
as one connection built entirely from the environment — this is the zero-file
quickstart path. An explicit config file always takes precedence.

| Var | Purpose |
|---|---|
| `RODB_HOST` | server host (enables the zero-file path) |
| `RODB_PORT` | server port (default `3306`) |
| `RODB_USER` | username |
| `RODB_PASSWORD` | password for the single env connection |
| `RODB_DATABASE` | default schema for the single env connection |

### Environment variables (all)

| Var | Purpose |
|---|---|
| `RODB_CONFIG` | path to config.toml (default `~/.config/readonly-db-mcp/config.toml`) |
| `RODB_ENV_FILE` | path to a `.env` (default: beside config.toml) |
| `RODB_HOST` / `RODB_PORT` / `RODB_USER` / `RODB_PASSWORD` / `RODB_DATABASE` | the zero-file single connection (above) |
| `RODB_PASSWORD_<CONN>` | password for a named connection in the config file |
| `RODB_ENABLE_GATED` | allowlist of gated connections to enable |
| `RODB_LOG_DIR` | audit-log directory (default `<config dir>/logs/`) |
| `RODB_DISABLE_LOG` | set to `1` to disable the audit log |

## Verifying the boundary

```bash
readonly-db-mcp-verify                       # the default connection
readonly-db-mcp-verify --connection warehouse
readonly-db-mcp-verify --all                 # every connection
# from a clone: uv run python scripts/verify_boundary.py [--all]
```

It connects to your real DB, deliberately attempts writes **with the AST layer
bypassed**, and confirms the MySQL engine itself rejects them (`ERROR 1792`, or a
read-only grant / replica). It reports which layer caught each attempt and rolls
everything back. Expected ending: `ALL BOUNDARIES PROVEN.`

## Audit log

Every query that reaches a DB — and every blocked (gated) attempt — is appended
as one JSON line to `<config dir>/logs/<connection>.log`: timestamp, connection,
schema, tool, the **SQL**, row count, duration, outcome. **Result data is never
logged**, so the log is not a PII sink. Files are created `0600`.

## Development

```bash
uv run pytest          # safety + config + formatting tests; no DB required
```

The test suite runs entirely without a database — the boundary's AST layer and
the config resolution are pure functions. CI runs it on every push.

## License

MIT — see [LICENSE](LICENSE).