dbmole-mcp/README.md

# dbmole-mcp

dbmole-mcp is a stdio MCP server for PostgreSQL and MySQL. It exposes named
connections, optional SSH tunnels, and runtime connection management: you add,
edit, and remove connections through MCP tools without restarting the harness.
It ships as both an npx package and a Docker image, so it runs anywhere your MCP
client does.

## Quick start (npx)

Add the server to your client's `.mcp.json`:

```json
{
    "mcpServers": {
        "dbmole": {
            "command": "npx",
            "args": ["-y", "dbmole-mcp"]
        }
    }
}
```

That is enough to start the server. With no connections configured it still runs;
use `add_connection` to register a database, or supply connections up front
through one of the sources below.

## Connection sources

Connections are merged from three layers. Higher layers win on name collisions.

| Priority | Source | How to set | Mutable at runtime |
| --- | --- | --- | --- |
| High | `DBMOLE_CONNECTIONS` env | JSON array of connection objects | No (read-only) |
| Medium | Config file | `--config <path>` or `DBMOLE_CONFIG`, shape `{ "connections": [...] }` | No (read-only) |
| Low | Persistent store | `~/.config/dbmole/connections.json` (mode `0600`); override with `DBMOLE_STORE` | Yes (via tools) |

Rules:

- Reads resolve name collisions by layer priority: env > config > store, so a
  higher layer shadows a lower one with the same name. `list_connections`
  reports the source layer that won.
- Writes never create a new shadow: `add_connection` refuses a name that already
  exists in any layer (env, config, or store), even one it cannot edit.
- `add_connection`, `update_connection`, and `remove_connection` only touch the
  store layer. Editing a connection that came from the env or a config file
  returns an error naming its source.
- The store is re-read on every operation, so several dbmole instances sharing
  the same store file see each other's changes immediately.

## Connection config reference

Each connection is an object with the following fields. Unknown fields are
rejected (the schemas are strict).

| Field | Type | Notes |
| --- | --- | --- |
| `name` | string | matches `[a-zA-Z0-9_-]+`; `add_connection` refuses a name already present in any layer |
| `type` | `postgres` \| `mysql` | the database engine |
| `host` | string | database host |
| `port` | number | defaults to `5432` (postgres) or `3306` (mysql) |
| `user` | string | database user |
| `password` | string | database password |
| `database` | string | default database for the schema and query tools |
| `readonly` | boolean | defaults to `false`; see Readonly mode below |
| `ssh` | object | optional SSH tunnel (see below) |

For PostgreSQL, a connection without a `database` falls back to the conventional
`postgres` maintenance database for `list_databases` and `test_connection`. On
servers that lack it, set `database` explicitly.

The `ssh` object accepts:

| Field | Type | Notes |
| --- | --- | --- |
| `host` | string | bastion host |
| `port` | number | defaults to `22` |
| `user` | string | SSH user |
| `password` | string | password auth |
| `privateKey` | string | key contents |
| `privateKeyPath` | string | path to a key file; a leading `~` is expanded |
| `agent` | boolean | use the SSH agent at `SSH_AUTH_SOCK` |
| `passphrase` | string | passphrase for an encrypted key |

A full example — a PostgreSQL database reachable only through a bastion, opened
read-only:

```json
{
    "name": "prod-replica",
    "type": "postgres",
    "host": "10.0.0.12",
    "port": 5432,
    "user": "readonly",
    "password": "s3cret",
    "database": "app",
    "readonly": true,
    "ssh": {
        "host": "bastion.example.com",
        "port": 22,
        "user": "deploy",
        "privateKeyPath": "~/.ssh/id_ed25519"
    }
}
```

## Tools

| Tool | What it does |
| --- | --- |
| `list_connections` | list configured connections and their source layer |
| `add_connection` | register a new connection in the store |
| `update_connection` | patch a stored connection; a `null` value removes that field |
| `remove_connection` | delete a stored connection |
| `test_connection` | open a connection and report success or the failure reason |
| `execute_sql` | run a single statement with positional params (`$1..` for postgres, `?` for mysql); `rowLimit` defaults to `100`, max `1000` |
| `list_databases` | list databases on the server |
| `list_tables` | list tables in a database |
| `describe_table` | describe a table's columns |

## SQL guardrails

`execute_sql` runs exactly one statement per call. Multi-statement input is
rejected before anything is sent to the database. Session-level statements
(`BEGIN`, `SET`, `USE`, and similar) are also rejected, because every call runs
on a connection drawn from a pool and must not leave session state behind.

## Readonly mode

Readonly is server-enforced, not advisory:

- PostgreSQL sessions start with `default_transaction_read_only=on`, set through
  the libpq startup options.
- MySQL sessions run `SET SESSION TRANSACTION READ ONLY` on first checkout.

Both block DML and DDL; this is verified against `postgres:17` and `mysql:8.4`
in the integration suite. Trying to flip the flag back with a `SET` is also
blocked by the SQL guard. For a hard guarantee in untrusted contexts, still
connect with a read-only database user — readonly mode is defence in depth, not
a substitute for database permissions.

The PostgreSQL mechanism is a libpq startup option, which PgBouncer in
transaction or statement pooling mode does **not** forward to the server. Behind
such a pooler the readonly flag is silently ineffective; use a read-only
database user there instead.

## Security notes

- The store is a plaintext JSON file written with `0600` permissions. The trust
  model is the same as `~/.pgpass`: whoever can read the file owns the
  credentials in it.
- For secrets you do not want written to disk by the tools, prefer
  `DBMOLE_CONNECTIONS` or `--config`. Those layers are read-only at runtime, so
  the tools never persist them.
- Database error messages — codes, hints, and object names — are returned to the
  MCP client by design. The caller is the database client and is meant to see
  them.

## Docker

Build the image:

```bash
docker build -t dbmole-mcp .
```

Run it from your client, persisting the store in a named volume:

```json
{
    "mcpServers": {
        "dbmole": {
            "command": "docker",
            "args": [
                "run",
                "-i",
                "--rm",
                "-v",
                "dbmole-store:/home/node/.config/dbmole",
                "dbmole-mcp"
            ]
        }
    }
}
```

The image runs as the non-root `node` user, so the store lives at
`/home/node/.config/dbmole` — mount the volume there (as above).

Note: when an SSH tunnel targets a bastion that is only reachable from the host,
the usual Docker networking caveats apply. Use `host.docker.internal` (or host
networking) so the container can reach it. On Docker Desktop that name resolves
automatically; on plain Linux Docker Engine you must add
`--add-host=host.docker.internal:host-gateway` to the `docker run` args.

## Development

```bash
npm run test:unit   # fast unit tests (mocked IO)
npm run test:int    # integration tests (needs Docker)
npm run coverage    # unit tests with coverage
npm run lint        # Biome check
npm run build       # bundle to dist/
```

See [AGENTS.md](AGENTS.md) for the contributor style contract: code style,
TypeScript discipline, testing rules, and commit conventions.