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.

Related MCP server: MySQL MCP Server

Quick start

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.

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

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:

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 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).

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 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; keys starting with _ or // are comments.

Security model

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

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:

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.

Tools

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

Operations

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.