# dbmcp

A small **MCP server** that proxies SQL queries to PostgreSQL and MySQL. It:

- **Routes to multiple databases by name** — callers pick `devDb`, `stageDb`,
  etc. (case-insensitive). Each can live on a different host with different
  credentials; the caller only ever knows the name.
- **Supports PostgreSQL and MySQL** — engine is auto-detected from the
  connection-string scheme (`postgres://`, `postgresql://`, or `mysql://`).
  Mix engines freely across databases.
- **Enforces read-only by default** — every database rejects writes and DDL at
  the database level, enforced via a native read-only transaction
  (`BEGIN TRANSACTION READ ONLY` on PostgreSQL, `START TRANSACTION READ ONLY`
  on MySQL). The server always rolls back; any write attempt gets a real DB
  error. Databases can be opted out per-DB with an explicit `DB_<NAME>_WRITABLE`
  flag (see [Configuration](#configuration-env) below).
- **Isolates credentials** — the caller sends SQL and gets results back; the
  connection strings live only in the server's environment and are never returned.
- **Enforces a hard timeout** — every query is capped (default **30s**) both at
  the DB level and by an in-process backstop.
- **Shrinks output** — the inline response is kept under **1000 characters**:
  each cell is truncated to the first **100 characters**, and only the leading
  rows that fit are returned.
- **Reports what was cut** — metadata includes the total row count, which
  columns were truncated, and whether rows were omitted.
- **Exports the full result as a public CSV** — the complete, untruncated result
  is written to `/files/<uuid>.csv`, served from the same server so callers can
  fetch and `grep` it.

Everything runs **inside Docker** — no Node packages are installed on the host.

> **Upgrading?** All databases are now **read-only by default**. Any database
> that previously accepted writes must add `DB_<NAME>_WRITABLE=true` (or
> `DATABASE_WRITABLE=true` for the unnamed `DATABASE_URL`) to keep working.
> See [Read-only by default](#read-only-by-default) below.

## Run

```bash
cp .env.example .env        # set DATABASE_URL etc.
docker compose up --build   # builds the image and runs the server
```

This starts:
- `dbmcp` — the MCP server, mapped to host port `${HOST_PORT:-3991}`.
- `devdb`, `stagedb` — two optional local PostgreSQL servers (different users,
  passwords, and hostnames) for testing multi-database routing. In real use,
  delete them and point the `DB_*_URL` env vars at your actual hosts.

### Configuring databases

Each database is one env var following the `DB_<NAME>_URL` convention; the
`<NAME>` becomes the case-insensitive name callers use. The engine is
auto-detected from the connection-string scheme — mix PostgreSQL and MySQL
freely:

```bash
DB_DEVDB_URL=postgres://devuser:devpass@dev-host:5432/dev       # -> "devDb"  (postgres)
DB_STAGEDB_URL=postgres://stageuser:secret@stage-host:5432/app  # -> "stageDb" (postgres)
DB_ANALYTICSDB_URL=mysql://user:pass@mysql-host:3306/analytics  # -> "analyticsDb" (mysql)
```

Optionally set `DATABASE_URL` as the unnamed default used when a caller omits
`database`. If exactly one database is configured, it is the default
automatically.

### Read-only by default

Every database is **read-only by default**. Read-only is enforced at the
database level by wrapping each query in a native read-only transaction
(`BEGIN TRANSACTION READ ONLY` on PostgreSQL, `START TRANSACTION READ ONLY`
on MySQL) that is always rolled back. Any write or DDL attempt is rejected by
the database with an error (PostgreSQL SQLSTATE `25006`; MySQL errno `1792`).

To make a database writable, set an explicit opt-out flag:

```bash
# For a named database (DB_<NAME>_URL):
DB_DEVDB_WRITABLE=true

# For the unnamed DATABASE_URL default:
DATABASE_WRITABLE=true
```

Accepted values: `true`, `1`, `yes` (case-insensitive). Anything else
(including unset) keeps the database read-only.

> Node is pinned to `node:24.16.0-alpine` (the latest published Node 24 LTS;
> `24.17.0` is not yet on Docker Hub).

## MCP endpoint

Streamable HTTP, stateless, at `POST /mcp`. Two tools:

| Tool             | Input                                          | Returns                                       |
|------------------|------------------------------------------------|-----------------------------------------------|
| `query`          | `sql` (string), `database` (string, see below) | truncated preview + metadata + public CSV URL |
| `list_databases` | none                                           | available databases with engine + readonly flag + the default (never credentials) |

`database` selects which configured database to run against (case-insensitive).
It is optional when a default exists, otherwise required.

Example (raw JSON-RPC over HTTP):

```bash
curl -s -X POST http://localhost:3991/mcp \
  -H 'Content-Type: application/json' \
  -H 'Accept: application/json, text/event-stream' \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/call",
       "params":{"name":"query","arguments":{"database":"devDb","sql":"SELECT * FROM demo"}}}'
```

Response payload (inside the MCP tool result text):

```json
{
  "columns": ["id", "big_text", "label"],
  "rows": [["1", "xxxx…", "row-1"], ...],
  "metadata": {
    "database": "devDb",
    "totalRows": 50,
    "returnedRows": 6,
    "truncatedColumns": ["big_text"],
    "cellsTruncated": true,
    "rowsOmitted": true,
    "csvUrl": "http://localhost:3991/files/<uuid>.csv",
    "note": "Showing first 6 of 50 rows. Fetch <url> for the full result."
  }
}
```

Fetch the full result:

```bash
curl -s http://localhost:3991/files/<uuid>.csv | grep something
```

## Connecting a client

The server speaks **Streamable HTTP** at `http://localhost:3991/mcp`. Start it
first (`docker compose up`), then point your client at that URL. None of the
options below require Node on your host — they connect over HTTP directly.

### Claude Code (CLI)

```bash
claude mcp add --transport http dbmcp http://localhost:3991/mcp
claude mcp list            # should show dbmcp as connected
```

Or add it to a project's `.mcp.json`:

```json
{
  "mcpServers": {
    "dbmcp": { "type": "http", "url": "http://localhost:3991/mcp" }
  }
}
```

### Cursor (cursor-cli)

Add the server to `~/.cursor/mcp.json` (global) or `.cursor/mcp.json` (project):

```json
{
  "mcpServers": {
    "dbmcp": { "url": "http://localhost:3991/mcp" }
  }
}
```

Then list tools from the CLI:

```bash
cursor-agent mcp list
```

### Codex (CLI)

Codex reads `~/.codex/config.toml`. Recent versions speak Streamable HTTP
natively:

```toml
experimental_use_rmcp_client = true

[mcp_servers.dbmcp]
url = "http://localhost:3991/mcp"
```

```bash
codex mcp list             # verify dbmcp shows up
```

### Fallback: older clients that only support stdio

If a client can't talk HTTP directly, bridge stdio→HTTP with `mcp-remote`
(this one *does* run a Node helper on the host):

```jsonc
// Claude Code / Cursor
{ "mcpServers": { "dbmcp": {
    "command": "npx", "args": ["-y", "mcp-remote", "http://localhost:3991/mcp"]
} } }
```

```toml
# Codex (~/.codex/config.toml)
[mcp_servers.dbmcp]
command = "npx"
args = ["-y", "mcp-remote", "http://localhost:3991/mcp"]
```

Once connected, call `list_databases` to see available names, then `query`
with a `database` and `sql`.

### Reaching it from another container

The server binds to `0.0.0.0`, and the port is published on the host, so other
containers can reach it via `host.docker.internal`:

```bash
curl http://host.docker.internal:3991/mcp ...
```

On Docker Desktop this name resolves automatically. On plain Linux, give the
*calling* container the host gateway mapping:

```yaml
# in the consuming container's compose service
extra_hosts:
  - "host.docker.internal:host-gateway"
```

So that the exported CSV links are fetchable from those containers (rather than
pointing back at the caller's own `localhost`), set on **this** server:

```bash
PUBLIC_BASE_URL=http://host.docker.internal:3991
```

> If the caller is part of *this* compose project, it can also just use the
> service name directly: `http://dbmcp:3991/mcp`.

## Configuration (env)

| Variable                | Default                  | Purpose                                                        |
|-------------------------|--------------------------|----------------------------------------------------------------|
| `HOST_PORT`             | `3991`                   | Host port mapped to the container.                             |
| `DB_<NAME>_URL`         | —                        | A named database connection (server-side only). Engine auto-detected from scheme (`postgres://`, `postgresql://`, `mysql://`). |
| `DB_<NAME>_WRITABLE`    | —                        | Set to `true`/`1`/`yes` to allow writes on that named database. Default: read-only. |
| `DATABASE_URL`          | —                        | Optional unnamed default database.                             |
| `DATABASE_WRITABLE`     | —                        | Set to `true`/`1`/`yes` to allow writes on `DATABASE_URL`. Default: read-only. |
| `QUERY_TIMEOUT_MS`      | `30000`                  | Hard per-query timeout.                                        |
| `MAX_OUTPUT_CHARS`      | `1000`                   | Inline payload cap.                                            |
| `MAX_CELL_CHARS`        | `100`                    | Per-cell truncation length.                                    |
| `PUBLIC_BASE_URL`       | `http://localhost:3991`  | Base URL used in CSV links.                                    |

## Security notes

- Callers never receive the connection string or password — only query results.
- **All databases are read-only by default** (enforced by a DB-native read-only
  transaction). Even if a caller sends an `INSERT` or `DROP TABLE`, the database
  itself rejects it. Databases can be opted into writes with `DB_<NAME>_WRITABLE`.
- For defence-in-depth, still connect with a least-privilege database user —
  the read-only transaction guard is a safety net, not a substitute for proper
  DB permissions.
- Exported CSVs are world-readable by anyone who can reach `/files/<uuid>.csv`;
  the filename is an unguessable UUID, but treat the endpoint as public.