# mcp-devdb

[![CI](https://github.com/aminyx/mcp-devdb/actions/workflows/ci.yml/badge.svg)](https://github.com/aminyx/mcp-devdb/actions/workflows/ci.yml)

Safe, **read-only** MCP server for local development databases. Coding agents
constantly need to see your dev database — schema, sample data, query plans,
table sizes — but a naive database connector hands them full write access.
mcp-devdb is the guarded alternative: a [Model Context
Protocol](https://modelcontextprotocol.io) server that exposes introspection
tools behind a hardened read-only SQL guard, column masking, result caps, and
a per-session query budget.

Backends in v1: **PostgreSQL** (via [`postgres`](https://github.com/porsager/postgres))
and **SQLite** (via [`better-sqlite3`](https://github.com/WiseLibs/better-sqlite3)).
The adapter interface is engine-neutral, so MySQL can be added later.

## Quickstart

1. Create `mcp-devdb.json` next to where the server will run (see
   [`mcp-devdb.example.json`](mcp-devdb.example.json)):

```json
{
  "databases": {
    "app": { "url": "postgres://dev:dev@localhost:5432/app_development" },
    "cache": { "url": "sqlite:./data/cache.db" }
  }
}
```

2. Run it:

```sh
npx mcp-devdb --config ./mcp-devdb.json
```

The server speaks MCP over stdio; point your MCP client at that command.
Connection strings live **only** in the config file or environment variables
(`"url": "env:MY_DB_URL"`, or the `MCP_DEVDB_URL` fallback with no config
file) — the model can never supply one.

### Claude Code

```sh
claude mcp add devdb -- npx mcp-devdb --config /absolute/path/to/mcp-devdb.json
```

### Claude Desktop (`claude_desktop_config.json`)

```json
{
  "mcpServers": {
    "devdb": {
      "command": "npx",
      "args": ["mcp-devdb", "--config", "/absolute/path/to/mcp-devdb.json"]
    }
  }
}
```

## Tools

| Tool             | Input                                   | What it returns                                                                                         |
| ---------------- | --------------------------------------- | ------------------------------------------------------------------------------------------------------- |
| `list_tables`    | `database?`                             | Schemas, tables, views with row estimates and on-disk sizes                                             |
| `describe_table` | `database?`, `table`                    | Columns, types, nullability, defaults, PK, FKs, indexes                                                 |
| `sample_rows`    | `database?`, `table`, `limit?` (max 50) | First N rows; cells > 200 chars truncated; sensitive columns masked as `***`                            |
| `run_query`      | `database?`, `sql`                      | Guarded read-only query; row cap (200) + byte cap (256 KiB); consumes query budget                      |
| `explain_query`  | `database?`, `sql`                      | Execution plan — PostgreSQL `EXPLAIN (FORMAT JSON)`, SQLite `EXPLAIN QUERY PLAN`; consumes query budget |
| `db_overview`    | `database?`                             | Database name, size, table count, largest tables, extensions (PG)                                       |

`database` is optional when exactly one database is configured; with several,
name the one you want.

## Configuration

`mcp-devdb.json` in the working directory, or any path via `--config`:

```json
{
  "databases": {
    "app": {
      "url": "postgres://dev:dev@localhost:5432/app_development",
      "allowTables": ["users", "orders", "public.events_*"],
      "denyTables": ["audit_log"]
    },
    "billing": { "url": "env:BILLING_DEV_DATABASE_URL" }
  },
  "maskPatterns": ["password", "secret", "token", "key", "hash", "ssn", "card"],
  "queryBudget": 100,
  "rowLimit": 200,
  "byteLimit": 262144,
  "statementTimeoutMs": 5000
}
```

- `allowTables` / `denyTables` — case-insensitive names with `*` wildcards;
  rules containing a dot match `schema.table`. Deny wins; a non-empty
  allowlist is exclusive.
- `maskPatterns` — case-insensitive regexes matched against column names.
- CLI flags: `--config <path>`, `--no-mask` (disable column masking),
  `--help`, `--version`.

## Security model (summary)

The full threat model lives in [SECURITY.md](SECURITY.md). In short:

- **Read-only guard**: every `run_query`/`explain_query` statement is
  tokenized (quotes, `E'...'` escapes, comments, dollar-quoted strings) and
  must start with SELECT / WITH / EXPLAIN / SHOW / VALUES; multi-statement
  input and write/DDL keywords anywhere at top level are rejected — a CTE
  followed by INSERT is caught, `SELECT 'DROP TABLE x'` is not a false
  positive.
- **Engine-level enforcement**: SQLite files are opened read-only; PostgreSQL
  sessions run with `default_transaction_read_only=on`, explicit `BEGIN READ
ONLY` transactions, and statement timeouts.
- **Column masking** on by default (`--no-mask` to opt out), **result caps**,
  and a **per-session query budget** (default 100; exhaustion tells you to
  restart the server).
- **Credentials never reach the model**: connection strings come only from
  local config/env and are scrubbed from every error message.

## Smoke test

`scripts/verify-stdio.mjs` builds a temp SQLite database, spawns
`node dist/cli.js`, and drives a real MCP handshake over stdio with raw
JSON-RPC. Actual output:

```text
$ node scripts/verify-stdio.mjs
initialize -> mcp-devdb 0.1.0 (protocol 2025-06-18)
tools/list -> db_overview, describe_table, explain_query, list_tables, run_query, sample_rows
tools/call list_tables ->
{
  "database": "demo",
  "dialect": "sqlite",
  "tableCount": 2,
  "tables": [
    {
      "schema": null,
      "name": "orders",
      "type": "table",
      "rowEstimate": 3,
      "sizeBytes": 4096,
      "sizePretty": "4.0 KiB"
    },
    {
      "schema": null,
      "name": "users",
      "type": "table",
      "rowEstimate": 2,
      "sizeBytes": 4096,
      "sizePretty": "4.0 KiB"
    }
  ]
}
tools/call run_query "DROP TABLE users" -> isError=true
  Query rejected by read-only guard: Only read-only statements are allowed; the statement must start with one of: SELECT, WITH, EXPLAIN, SHOW, VALUES
SMOKE TEST PASSED
```

## Limitations

- **No MySQL yet.** The `DbAdapter` interface in `src/adapters/types.ts` is
  the extension point.
- **Dev databases only.** The guard blocks SQL-level writes, but a SELECT can
  still invoke badly-labeled or extension functions with side effects (e.g.
  `dblink` opening its own non-read-only connection). Accepted for
  development databases; never point this at production. See SECURITY.md.
- The guard is conservative: unquoted columns named like forbidden keywords
  (e.g. a column literally named `update`) are rejected — quote them
  (`"update"`) to proceed.
- `SELECT ... FOR UPDATE` is rejected (it takes row locks).
- SQLite row counts use `COUNT(*)`; on huge files `list_tables` can be slow.

## Development

```sh
npm install
npm run lint && npm run typecheck && npm test && npm run build
node scripts/verify-stdio.mjs
```

## License

[MIT](LICENSE) — Copyright (c) 2026 Aminyx