# db-mcp

Enterprise MCP server that exposes **N MySQL databases** to AI services through
one endpoint, with **API-key auth + per-key database scoping**, a layered
**SQL guard**, and a markdown **schema-knowledge graph** that teaches AI
clients what your columns actually mean.

```
                        ┌─────────────────────────────────────────────┐
 Claude Code ──stdio──▶ │                 db-mcp                      │──▶ MySQL #1 (crm, read-only)
 Claude Desktop ──────▶ │  auth keys ─▶ per-key scope ─▶ SQL guard    │──▶ MySQL #2 (warehouse)
 Remote agents ──HTTP─▶ │  knowledge graph (markdown schema docs)     │──▶ MySQL #N (ops, read-write)
   Bearer <api-key>     │  audit trail (JSONL)                        │
                        └─────────────────────────────────────────────┘
```

## Why this exists

- **One server, many databases.** Each database gets a stable id, its own
  credentials, pool, access mode and limits — AI clients address `crm` or
  `warehouse`, never a connection string. Cross-database access is blocked;
  scoping is structural.
- **Keys are capabilities.** Every AI service gets its own key with a database
  allowlist and a mode ceiling (`read_only` < `read_write` < `admin`).
  A key never sees databases outside its scope — not even their names.
- **Schema ≠ semantics.** `information_schema` says `status tinyint`; the
  knowledge doc says `1=active, 2=paused, 3=settled — soft deletes, filter
  deleted_at`. Every `describe_table`/`search_schema` response merges both.
- **Defense in depth.** Guard layers (below) + driver hardening + session
  caps + audit trail. A hostile prompt should at worst produce a polite error.

## Quick start

```bash
npm install
cp .env.yml.example .env.yml               # the config — array of databases, keys, defaults
touch .env                                 # secrets referenced from .env.yml via ${VAR}
npm run keys:generate -- --id my-agent     # mint an API key (secret goes into .env)
npm run config:validate                    # sanity-check everything
npm run knowledge:generate                 # scaffold schema docs from live DBs
npm run dev                                # stdio | npm run dev:http for HTTP
```

Wire it into Claude Code / Desktop / Cursor / remote services:
see **[examples/client-configs.md](examples/client-configs.md)**.

Try it instantly against the bundled demo (dockerized MySQL, two databases):

```bash
npm run e2e:up        # starts MySQL on 127.0.0.1:3307 with demo schemas
npm run smoke         # full end-to-end check (38 assertions)
# point a client at test/e2e/databases.e2e.json to explore the demo
npm run e2e:down
```

## Configuration

The setup lives in **`.env.yml`** (gitignored): a YAML file declaring the
databases as an array, the API keys as an array, and global defaults. Secrets
never live in it — `${ENV_VAR}` / `${ENV_VAR:-fallback}` pull them from the
environment or a slim `.env` next to it:

```yaml
auth:
  keys:
    - id: reporting-agent                      # npm run keys:generate -- --id reporting-agent
      key: ${DB_MCP_KEY_REPORTING_AGENT}
      databases: [crm_replica, warehouse]      # or "*"
      modeCap: read_only

defaults:
  mode: read_only                              # read_only | read_write | admin
  maxRows: 200

databases:
  - id: crm_replica
    label: CRM production replica
    description: Read replica of the CRM primary (~5s lag).
    connection:
      host: replica.internal.example.com
      user: dbmcp_readonly
      password: ${CRM_DB_PASSWORD}
      database: crm
    tableDenylist: [_migrations, "audit_*"]
    columnMasks:
      users: [password_hash, remember_token]

  - id: warehouse
    label: Analytics warehouse
    connection: { host: wh.internal, user: ro, password: ${WH_DB_PASSWORD}, database: warehouse }
```

**[.env.yml.example](.env.yml.example) is the complete annotated reference** —
every setting, with the full per-database knob list (`maxRows`,
`queryTimeoutMs`, `cellMaxChars`, `ssl`, `connectionLimit`,
`allowSystemSchemas`, `allowStoredProcedures`, `safeUpdates`, `knowledgeDir`,
…). Validation stays strict: invalid values fail with the precise field,
unknown keys are rejected as probable typos, missing `${VARS}` are reported
all at once, and inline passwords trigger a warning. Relative paths resolve
against the config file's directory. Also auto-discovered: `.env.yaml`,
`config/databases.{yml,yaml,json}`; or pass any path with `--config` /
`$DB_MCP_CONFIG` (an explicit file always wins).

<details>
<summary><b>Alternative: pure env-var config</b> (docker --env-file, MCP client "env" blocks)</summary>

The entire setup can also travel in flat `DB_MCP_*` variables — no file at
all. `DB_MCP_DATABASES=crm,warehouse` declares ids, then
`DB_MCP_DB_<ID>_HOST/_USER/_PASSWORD/_DATABASE/...` configure each one
(`<ID>` = id uppercased, `-` → `_`), and `DB_MCP_KEYS` +
`DB_MCP_KEY_<ID>_SECRET/...` declare the auth keys. See
[.env.example](.env.example) for the annotated reference. Note: when
`DB_MCP_DATABASES` is set, env mode takes over and `.env.yml` is ignored
(a startup warning tells you so). JSON configs remain supported too — see
[config/databases.example.json](config/databases.example.json); keys starting
with `_` or `//` are comments.
</details>

## Security model

**Access modes** — effective mode = `min(database.mode, key.modeCap)`:

| mode | allows |
|---|---|
| `read_only` (default) | SELECT, SHOW, DESCRIBE, EXPLAIN |
| `read_write` | + INSERT, UPDATE, DELETE, REPLACE (WHERE required on UPDATE/DELETE) |
| `admin` | + table-level DDL (CREATE/ALTER/DROP/TRUNCATE …) |

**Guard layers** (all before any SQL reaches a pool):

1. Versioned-comment (`/*!`) rejection; single-statement enforcement.
2. Keyword scanning on a **string-literal-blanked skeleton** — `WHERE note =
   'please INSERT'` can't false-positive; keywords in comments can't hide.
3. Always-denied statements regardless of mode: `SET`, `USE`, `GRANT`,
   `KILL`, `LOAD DATA`, transactions, prepared statements, plugins, …
4. Dangerous constructs: `INTO OUTFILE/DUMPFILE`, `LOAD_FILE`, `SLEEP`,
   `BENCHMARK`, named locks, `INTO @var`.
5. System schemas (`mysql.*`, `sys.*`, `performance_schema.*`) blocked unless
   `allowSystemSchemas`; `information_schema` always readable.
6. AST deep checks (node-sql-parser, MySQL dialect): cross-database refs,
   table denylist (joins/subqueries included), `LIMIT` ceiling, WHERE-less
   UPDATE/DELETE. Statements that can't be parsed run **only if read-only**.
7. User/role/database-level DDL is denied even in `admin` mode.

**Runtime backstops** (can't be bypassed by clever SQL):

- `multipleStatements: false` at the driver.
- `SET SESSION TRANSACTION READ ONLY` on every read-only pool connection;
  `sql_safe_updates=1` where enabled.
- `sql_select_limit` + `max_execution_time` per query, hard client timeout
  with **`KILL QUERY`** via a second connection, result re-capping, cell
  truncation, column masking.
- Secrets (DB passwords, API keys) are scrubbed from every error message and
  audit line; MySQL creds live only in env vars.

**Auth:** keys are compared by SHA-256 digest with `timingSafeEqual`;
per-IP backoff after repeated failures; HTTP refuses to start without active
keys (override: `DB_MCP_ALLOW_INSECURE_HTTP=1`); sessions are pinned to the
key that created them; DNS-rebinding protection is on. stdio runs under the
launching user's OS trust — keys there provide audit attribution and scoping.

**MySQL-side**: give db-mcp a dedicated MySQL user with least privilege
(e.g. only `SELECT` on replicas). The guard is a second wall, not the first.

## Knowledge graph

Markdown docs under `knowledge/<db-id>/` describe databases, tables, columns,
enum values, relationships and gotchas — searchable (BM25 with field boosts),
graph-connected (`relates_to` + links + live FKs), merged into every schema
response, and exposed as `knowledge://<db>/<table>` resources.

Bootstrap from the live schema, then curate:

```bash
npm run knowledge:generate            # writes status: generated skeletons
$EDITOR knowledge/crm/tables/…        # fill meanings, set status: documented
# curated files are never overwritten; live-vs-doc drift is reported
```

Authoring guide: **[knowledge/README.md](knowledge/README.md)**.

## Tools

| tool | purpose |
|---|---|
| `list_databases` | ids, purpose, access mode, knowledge coverage, health |
| `list_tables` | tables/views + row estimates + documented flags |
| `describe_table` | live columns/keys/FKs merged with documented meanings/values |
| `search_schema` | find data by meaning across knowledge + live names/comments |
| `get_table_relationships` | FK + documented edges, Mermaid ER snippet |
| `preview_table` | sample rows without SQL (filters/order/limit; masked) |
| `query` | guarded SQL with `?` params; modes gate writes/DDL |
| `explain_query` | execution plan (traditional/json/tree) without executing |
| `get_knowledge` | full doc for a table, or database overview + doc index |
| `refresh_knowledge` | hot-reload docs from disk |
| `server_status` | uptime, pools, per-db health, knowledge state |

Every call is audit-logged to `logs/audit.jsonl`:
`{ts, keyId, tool, database, sqlPreview, rowCount, durationMs, ok, error}`.

## Operations

```bash
npm run build          # tsc -> dist/
npm start              # stdio
npm run start:http     # HTTP on server.http.host:port  (/healthz for probes)
npm test               # 113 unit tests (guard matrix, config, auth, knowledge)
npm run smoke          # 38-check E2E against dockerized MySQL
docker build -t db-mcp .   # container (HTTP mode; --env-file .env carries the config)
```

Logs are structured JSON on **stderr** (stdout belongs to the MCP protocol).
`DB_MCP_LOG_LEVEL=debug` for verbosity. Graceful shutdown drains pools and
flushes the audit log on SIGINT/SIGTERM.

## Layout

```
src/
├── index.ts            CLI entry (--config/--transport/--validate-config)
├── server.ts           per-session McpServer factory (auth-scoped)
├── config/             zod schema + env assembler + loader (strict validation)
├── auth/               API keys, scoping, mode ceilings
├── db/                 guard (SQL policy) · manager (pools) · executor (limits/KILL)
│                       introspect (information_schema) · service (glue)
├── knowledge/          parser (frontmatter+columns) · store (index/graph) · search (BM25)
├── tools/              catalog · query · knowledge · status (+ audit wrapper)
├── transport/          stdio · streamable HTTP (sessions, key auth, backoff)
└── logging/ util/      pino->stderr · audit JSONL · errors+secret scrubbing
scripts/                generate-knowledge · generate-key · smoke-client
test/                   unit tests · e2e (docker MySQL + demo config)
knowledge/              your schema docs (see knowledge/README.md)
```

## Design notes

Patterns adopted from prior art: per-connection read-only sessions, KILL-on-
timeout and layered SQL validation (prism-mcp); multi-database config file and
row/time caps (bytebase/dbhub); per-database write gating (benborla
mcp-server-mysql); markdown-bundle knowledge with frontmatter contracts, index
files and link graphs (Google OKF v0.1). Deliberate divergences: policy
scanning happens on a literal-blanked skeleton (no false rejections on string
contents), the parser runs in MySQL dialect, per-key servers fix visibility-
vs-execution permission drift, and HTTP simply refuses to run unauthenticated.