Skip to content
Back to search

db-mcp-server

A powerful multi-database server implementing the Model Context Protocol (MCP) to provide AI assistants with structured access to databases.

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Checked today

Check it deeper on your machine

It has changed since its scan on 11 Nov 2026.

As a Claude Code plugin, install it and then ask Claude Code:

/plugin marketplace add keithmackay/mackayi/plugin install scanskillsafety@mackayiDo a deep scan of https://github.com/FreePeak/db-mcp-server with scanskillsafety

Or from a terminal, clone it and run it:

git clone https://github.com/keithmackay/scanskillsafety-skill ~/.claude/skills/scanskillsafetynode ~/.claude/skills/scanskillsafety/cli.cjs --deep 'https://github.com/FreePeak/db-mcp-server'

It checks the exact commit: every file, its packages against OSV (api.osv.dev), how it compares with this page's scan (findsafeskills.com receives the repository's address), and, if you agree, Claude reading every file with your own Claude subscription. It never runs the code.

Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/freepeak--db-mcp-server/badge.svg)](/listings/freepeak--db-mcp-server)

HTML

<a href="/listings/freepeak--db-mcp-server"><img src="/api/listings/freepeak--db-mcp-server/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md · README · 903 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

This file has changed since it was scanned, so this is not exactly the text that was scanned, and no line is marked. The findings are listed with the safety scan above.

  1. <div align="center">
  2. <img src="assets/logo.svg" alt="DB MCP Server Logo" width="300" />
  3. # Multi Database MCP Server
  4. [![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](https://opensource.org/licenses/MIT)
  5. [![Go Report Card](https://goreportcard.com/badge/github.com/FreePeak/db-mcp-server)](https://goreportcard.com/report/github.com/FreePeak/db-mcp-server)
  6. [![Go Reference](https://pkg.go.dev/badge/github.com/FreePeak/db-mcp-server.svg)](https://pkg.go.dev/github.com/FreePeak/db-mcp-server)
  7. [![Contributors](https://img.shields.io/github/contributors/FreePeak/db-mcp-server)](https://github.com/FreePeak/db-mcp-server/graphs/contributors)
  8. <h3>A powerful multi-database server implementing the Model Context Protocol (MCP) to provide AI assistants with structured access to databases.</h3>
  9. <div class="toc">
  10. <a href="#overview">Overview</a> •
  11. <a href="#core-concepts">Core Concepts</a> •
  12. <a href="#features">Features</a> •
  13. <a href="#supported-databases">Supported Databases</a> •
  14. <a href="#deployment-options">Deployment Options</a> •
  15. <a href="#configuration">Configuration</a> •
  16. <a href="#available-tools">Available Tools</a> •
  17. <a href="#examples">Examples</a> •
  18. <a href="#troubleshooting">Troubleshooting</a> •
  19. <a href="#contributing">Contributing</a>
  20. </div>
  21. <div align="center">
  22. <a href="docs/PRD.md"><b>📄 Product Requirements — current capabilities, roadmap & decisions</b></a>
  23. </div>
  24. </div>
  25. ## Overview
  26. The DB MCP Server provides a standardized way for AI models to interact with multiple databases simultaneously. Built on the [FreePeak/cortex](https://github.com/FreePeak/cortex) framework, it enables AI assistants to execute SQL queries, manage transactions, explore schemas, and analyze performance across different database systems through a unified interface.
  27. ## Core Concepts
  28. ### Multi-Database Support
  29. Unlike traditional database connectors, DB MCP Server can connect to and interact with multiple databases concurrently:
  30. ```json
  31. {
  32. "connections": [
  33. {
  34. "id": "mysql1",
  35. "type": "mysql",
  36. "host": "localhost",
  37. "port": 3306,
  38. "name": "db1",
  39. "user": "user1",
  40. "password": "password1"
  41. },
  42. {
  43. "id": "postgres1",
  44. "type": "postgres",
  45. "host": "localhost",
  46. "port": 5432,
  47. "name": "db2",
  48. "user": "user2",
  49. "password": "password2"
  50. },
  51. {
  52. "id": "oracle1",
  53. "type": "oracle",
  54. "host": "localhost",
  55. "port": 1521,
  56. "service_name": "XEPDB1",
  57. "user": "user3",
  58. "password": "password3"
  59. }
  60. ]
  61. }
  62. ```
  63. ### Dynamic Tool Generation
  64. For each connected database, the server automatically generates specialized tools:
  65. ```go
  66. // For a database with ID "mysql1", these tools are generated:
  67. query_mysql1 // Execute SQL queries
  68. execute_mysql1 // Run data modification statements
  69. transaction_mysql1 // Manage transactions
  70. schema_mysql1 // Explore database schema
  71. performance_mysql1 // Analyze query performance
  72. ```
  73. ### Clean Architecture
  74. The server follows Clean Architecture principles with these layers:
  75. 1. **Domain Layer**: Core business entities and interfaces
  76. 2. **Repository Layer**: Data access implementations
  77. 3. **Use Case Layer**: Application business logic
  78. 4. **Delivery Layer**: External interfaces (MCP tools)
  79. ## Features
  80. - **Simultaneous Multi-Database Support**: Connect to multiple MySQL, PostgreSQL, SQLite, and Oracle databases concurrently
  81. - **Lazy Loading Mode**: Defer connection establishment until first use - perfect for setups with 10+ databases (enable with `--lazy-loading` flag)
  82. - **Database-Specific Tool Generation**: Auto-creates specialized tools for each connected database
  83. - **Clean Architecture**: Modular design with clear separation of concerns
  84. - **OpenAI Agents SDK Compatibility**: Full compatibility for seamless AI assistant integration
  85. - **Dynamic Database Tools**: Execute queries, run statements, manage transactions, explore schemas, analyze performance
  86. - **Unified Interface**: Consistent interaction patterns across different database types
  87. - **Connection Management**: Simple configuration for multiple database connections
  88. - **Health Check**: Automatic validation of database connectivity on startup
  89. - **Production Guardrails**: Per-database `read_only` enforcement (blocks writes through both `query_*` and `execute_*` tools), `max_rows` result truncation with explicit notices, and per-query timeouts
  90. ### Production Guardrails
  91. Protect agent sessions against runaway queries and accidental writes:
  92. | Setting | Scope | Effect |
  93. |---------|-------|--------|
  94. | `"read_only": true` | per database | Blocks write statements (`INSERT`, `UPDATE`, `DELETE`, DDL, data-modifying CTEs, stacked writes) through **both** query and execute tools, **and** enforces rejection at the database engine itself on PostgreSQL/TimescaleDB (`default_transaction_read_only=on`) and MySQL (`transaction_read_only=1`); SQLite opens `mode=ro`. Classification strips comments and string literals and defaults to deny for unrecognized statements. |
  95. | `"max_rows": 1000` | per database | Truncates result sets at N rows and appends an explicit `Truncated: showing first N rows (max_rows=N)` notice so the model knows to refine its query instead of losing context. `0` (default) means unlimited. On PostgreSQL (including TimescaleDB), MySQL and SQLite the query tool also appends `LIMIT max_rows+1` to a single SELECT/WITH statement with no LIMIT, OFFSET, FETCH, FOR, INTO or LOCK clause of its own and not ending in a VALUES list, so the database stops at the cap; statements containing a backslash, `#` or a MySQL `/*!` comment are sent unchanged; select-list functions with side effects (for example `nextval()`) run for at most max_rows+1 rows; an explicit `LIMIT` sends the query unchanged; Oracle results are truncated client-side only. |
  96. | `"masking_rules": [...]` | per database | Masks values of result columns whose **name** matches a rule's regex before they leave the server — applies to every query shape including `SELECT *`. Strategies: `"fixed_string"` (replace with `value`), `"null"`, and `"partial"` (`keep_last` trailing characters visible; shorter values fully masked). First matching rule wins; invalid patterns or unknown strategies abort config load (fail closed); masked-cell counts are reported in the result footer. Renaming a column with an alias bypasses name matching by design. See [docs/design/column-masking-scoping.md](docs/design/column-masking-scoping.md). |
  97. | `"mask_pii": true` | per database | Redacts PII in query results by **content shape**, not just column name: emails, phone numbers, credit-card numbers (Luhn-checked), SSNs, IPv4 addresses and 19+ digit identifiers are replaced with `[EMAIL]`/`[PHONE]`/`[CREDIT_CARD]`/`[SSN]`/`[IP_ADDRESS]`/`[LONG_NUMBER]` wherever they appear — including inside free-text columns no rule covers. Column names like `email`/`card_number`/`iban` are redacted whole. Operator-controlled: once set, an agent **cannot** turn it off (the per-query `mask_pii` parameter is additive only). Composes with `masking_rules`. |
  98. | `"query_timeout": 30` | per database | Cancels statements that exceed the timeout in seconds; enforced at the repository layer for every tool (queries, statements, transactions, explain, schema inspection). Unset defaults to 30s; `-1` disables. Env-only deployments can set `QUERY_TIMEOUT_SECONDS` to fill connections without an explicit value (JSON keeps precedence). |
  99. | `DB_MCP_AUDIT_LOG=/path/audit.jsonl` | process | Appends one JSONL record per executed statement — timestamp, op (`query`/`execute`/`tx_*`), database, statement (capped at 10k chars), duration, error. Includes rejected attempts against read-only databases. Best-effort writes never fail a query; file is created with `0600`. |
  100. > **Defense in depth**: read-only is enforced in three layers — application classifier, engine session defaults, and (recommended) least-privilege database users. Oracle currently relies on the classifier plus user privileges.
  101. ## Supported Databases
  102. | Database | Status | Features |
  103. | ---------- | ------------------------- | ------------------------------------------------------------ |
  104. | MySQL | ✅ Full Support | Queries, Transactions, Schema Analysis, Performance Insights |
  105. | PostgreSQL | ✅ Full Support (v9.6-17) | Queries, Transactions, Schema Analysis, Performance Insights |
  106. | SQLite | ✅ Full Support | File-based & In-memory databases, SQLCipher encryption support |
  107. | Oracle | ✅ Full Support (10g-23c) | Queries, Transactions, Schema Analysis, RAC, Cloud Wallet, TNS |
  108. | TimescaleDB| ✅ Full Support | Time-Series Queries, Hypertable Discovery (write policies via SQL) |
  109. ## Deployment and transport status
  110. The safest local integration is **STDIO**. HTTP transports are still under hardening; do not expose the current server directly to a shared network.
  111. ### Personal/local STDIO (recommended)
  112. ```bash
  113. # Build from the audited source revision
  114. make build
  115. # Use a private, single-connection config
  116. ./bin/server -t stdio -c /absolute/path/to/config.local.json
  117. ```
  118. For Cursor, point the client at the actual build output and use absolute paths:
  119. ```json
  120. {
  121. "mcpServers": {
  122. "local-db": {
  123. "command": "/absolute/path/to/db-mcp-server/bin/server",
  124. "args": ["-t", "stdio", "-c", "/absolute/path/to/config.local.json"]
  125. }
  126. }
  127. }
  128. ```
  129. Use a read-only database account and a bounded `query_timeout` for a personal
  130. PostgreSQL/MySQL connection. For SQLite, use an absolute `database_path` and
  131. `"read_only": true` when the agent only needs to inspect data.
  132. ### Legacy SSE (compatibility only)
  133. The historical Cortex SSE transport has no built-in authentication. It is
  134. **disabled by default** and must only be enabled explicitly behind a trusted
  135. private reverse proxy:
  136. ```bash
  137. ./bin/server -t sse -h 127.0.0.1 -p 9092 \
  138. -allow-insecure-sse -c /absolute/path/to/config.local.json
  139. ```
  140. Do not use this mode for a shared deployment. The legacy endpoint does not
  141. apply `DB_MCP_API_KEY`; terminate TLS, authentication, and network policy at the
  142. proxy. A future release will replace it with a maintained Streamable HTTP
  143. implementation.
  144. ### Streamable HTTP (experimental)
  145. The `streamable` endpoint is backed by the maintained Go MCP SDK and supports
  146. stateful sessions, JSON or SSE responses, notifications, GET/DELETE session
  147. lifecycle, and JSON-RPC dispatch. The process refuses to start it without
  148. `DB_MCP_API_KEY`; keep it behind a private TLS gateway until the identity and
  149. conformance rollout is complete.
  150. For browser-origin requests, set an explicit comma-separated allowlist:
  151. ```bash
  152. MCP_ALLOWED_ORIGINS=https://agent.example.com
  153. ```
  154. The built-in static bearer key is a bounded pilot mechanism. It grants
  155. `db:read` by default; set `DB_MCP_API_SCOPES=db:read,db:write` only for a
  156. trusted write client. `db:admin` is reserved for controlled administration.
  157. Set `DB_MCP_OIDC_ISSUER` and `DB_MCP_OIDC_AUDIENCE` to use OIDC discovery,
  158. JWKS signature validation, issuer/audience checks, and subject identity for
  159. shared deployments; database-specific scopes are still intentionally not
  160. enabled until that policy is defined.
  161. ### Docker development image
  162. The published image is a development/demo artifact, not an enterprise
  163. hardening recommendation. The current Dockerfile runs as an unprivileged
  164. user, omits an embedded config, and includes the legacy
  165. `-allow-insecure-sse` behavior for local Compose compatibility. It exposes a
  166. separate health port (`9093`) and is not suitable for public publication.
  167. ```bash
  168. # Build a local image from this source revision
  169. docker build -t db-mcp-server:local .
  170. # Keep it private; the image's default SSE mode is compatibility-only
  171. docker run --rm -p 127.0.0.1:9092:9092 \
  172. -p 127.0.0.1:9093:9093 \
  173. -v "$(pwd)/config.local.json:/app/config.json:ro" \
  174. db-mcp-server:local
  175. ```
  176. For a team deployment, wait for the authenticated Streamable transport and use
  177. a non-root, read-only-filesystem image with a private network, external secret
  178. store, and a TLS/OIDC gateway.
  179. ### Source Code Installation
  180. ```bash
  181. # Clone the repository
  182. git clone https://github.com/FreePeak/db-mcp-server.git
  183. cd db-mcp-server
  184. # Build the server
  185. make build
  186. # Run the server
  187. ./bin/server -t stdio -c config.json
  188. ```
  189. ## Configuration
  190. ### Database Configuration File
  191. Create a `config.json` file with your database connections. `schema_version: 1`
  192. enables strict unknown-field validation:
  193. ```json
  194. {
  195. "schema_version": 1,
  196. "connections": [
  197. {
  198. "id": "mysql1",
  199. "type": "mysql",
  200. "host": "mysql1",
  201. "port": 3306,
  202. "name": "db1",
  203. "user": "user1",
  204. "password_env": "DB_MCP_MYSQL_PASSWORD",
  205. "query_timeout": 60,
  206. "max_open_conns": 20,
  207. "max_idle_conns": 5,
  208. "conn_max_lifetime_seconds": 300,
  209. "conn_max_idle_time_seconds": 60,
  210. "read_only": false,
  211. "max_rows": 1000,
  212. "masking_rules": [
  213. { "pattern": "(?i)email", "strategy": "fixed_string", "value": "***MASKED***" },
  214. { "pattern": "(?i)(ssn|tax_id)", "strategy": "null" }
  215. ]
  216. },
  217. {
  218. "id": "postgres1",
  219. "type": "postgres",
  220. "host": "postgres1",
  221. "port": 5432,
  222. "name": "db1",
  223. "user": "user1",
  224. "password_env": "DB_MCP_POSTGRES_PASSWORD"
  225. },
  226. {
  227. "id": "sqlite_app",
  228. "type": "sqlite",
  229. "database_path": "./data/app.db",
  230. "journal_mode": "WAL",
  231. "cache_size": 2000,
  232. "read_only": false,
  233. "use_modernc_driver": true,
  234. "query_timeout": 30,
  235. "max_open_conns": 1,
  236. "max_idle_conns": 1
  237. },
  238. {
  239. "id": "sqlite_encrypted",
  240. "type": "sqlite",
  241. "database_path": "./data/secure.db",
  242. "encryption_key": "your-secret-key-here",
  243. "journal_mode": "WAL",
  244. "use_modernc_driver": false
  245. },
  246. {
  247. "id": "sqlite_memory",
  248. "type": "sqlite",
  249. "database_path": ":memory:",
  250. "cache_size": 1000,
  251. "use_modernc_driver": true
  252. }
  253. ]
  254. }
  255. ```
  256. #### Secret references
  257. Do not put production credentials directly in JSON. Use either an
  258. environment variable name or a mounted secret file per connection:
  259. ```json
  260. {
  261. "id": "prod",
  262. "type": "postgres",
  263. "host": "db.internal",
  264. "port": 5432,
  265. "name": "app",
  266. "user": "mcp_reader",
  267. "password_env": "DB_MCP_PROD_PASSWORD"
  268. }
  269. ```
  270. `password_file` and `encryption_key_env`/`encryption_key_file` are also
  271. supported. Relative file paths resolve from the config file directory; an
  272. inline value cannot be combined with a reference. The resolved value stays in
  273. process memory and is not printed by the config loader.
  274. ### Command-Line Options
  275. ```bash
  276. # Basic syntax
  277. ./bin/server -t <transport> -c <config-file>
  278. # HTTP transport options (legacy SSE requires an explicit opt-in)
  279. ./bin/server -t sse -h <hostname> -p <port> -allow-insecure-sse -c <config-file>
  280. # Lazy loading mode (recommended for 10+ databases)
  281. ./bin/server -t stdio -c <config-file> --lazy-loading
  282. # Customize log directory (useful for multi-project setups)
  283. ./bin/server -t stdio -c <config-file> -log-dir /tmp/db-mcp-logs
  284. # Inline database configuration
  285. ./bin/server -t stdio -db-config '{"connections":[...]}'
  286. # Environment variable configuration
  287. export DB_CONFIG='{"connections":[...]}'
  288. ./bin/server -t stdio
  289. # Print the build version
  290. ./bin/server --version
  291. # Upgrade to the latest published release (verifies SHA-256, swaps the binary)
  292. ./bin/server update
  293. ./bin/server update -check # only report whether a newer release exists
  294. ./bin/server update -version 1.13.0 # install a specific release
  295. ```
  296. **Available Flags:**
  297. - `-t, -transport`: Transport mode (`stdio`, legacy `sse`, or experimental `streamable`)
  298. - `-c, -config`: Path to database configuration file
  299. - `-p, -port`: HTTP port (default: 9092)
  300. - `-h, -host`: HTTP bind host (default: `127.0.0.1`)
  301. - `--version`: Print the build version and exit
  302. - `-allow-insecure-sse`: Explicitly allow legacy SSE for a private development proxy
  303. - `-api-key`: Bearer key for the experimental Streamable HTTP entrypoint (or use `DB_MCP_API_KEY`)
  304. - `-log-level`: Log level (`debug`, `info`, `warn`, `error`)
  305. - `-log-dir`: Directory for log files (default: `./logs` in current directory)
  306. - `-db-config`: Inline JSON database configuration
  307. - `-lazy-loading`: Establish connections on first use
  308. - `-unified-tools`: Register one tool set with a `database` parameter
  309. **Updating:** `db-mcp-server update` resolves the latest GitHub release, checks
  310. the download against the release's `SHA256SUMS` manifest, and replaces the
  311. running binary in place (the previous binary is left untouched if verification
  312. fails). It installs the asset matching your OS and architecture — restart the
  313. server afterwards. On Windows a running executable cannot be replaced, so update
  314. through the npm installer there. `-check` reports without downloading; `-force`
  315. reinstalls the current version.
  316. ### Environment Variables
  317. Values in a `.env` file are loaded first; real environment variables take precedence. A JSON config file (`CONFIG_PATH`/`DB_CONFIG_FILE`) takes precedence over inline and legacy database variables.
  318. | Variable | Default | Purpose |
  319. |---|---|---|
  320. | `CONFIG_PATH` / `DB_CONFIG_FILE` | `config.json` | Path to the multi-database JSON config |
  321. | `DB_CONFIG` | — | Inline JSON database configuration (used when no file is present) |
  322. | `TRANSPORT_MODE` | `stdio` | Transport mode when `-t` is not passed |
  323. | `SERVER_PORT` | `9092` | HTTP port |
  324. | `MCP_ALLOWED_ORIGINS` | empty | Comma-separated browser origins allowed by experimental Streamable HTTP |
  325. | `DB_MCP_API_KEY` | empty | Bearer key for experimental Streamable HTTP; keep it in a secret manager |
  326. | `DB_MCP_API_SCOPES` | `db:read` | Required/granted scopes: `db:read`, `db:write`, and/or `db:admin` |
  327. | `DB_MCP_OIDC_ISSUER` | empty | OIDC issuer URL; when set, OIDC/JWT verification replaces the static pilot key |
  328. | `DB_MCP_OIDC_AUDIENCE` | empty | Required OIDC audience/client ID |
  329. | `LOG_LEVEL` | `info` | Log verbosity (`debug`, `info`, `warn`, `error`) |
  330. | `DISABLE_LOGGING` | `false` | `true`/`1` silences logging entirely |
  331. | `DB_TYPE`, `DB_HOST`, `DB_PORT`, `DB_USER`, `DB_PASSWORD`, `DB_NAME` | engine defaults | Single-database fallback when no JSON config exists |
  332. | `QUERY_TIMEOUT_SECONDS` | unset | Fills connections that don't set their own `query_timeout`; negative disables the cap. JSON configs keep precedence. |
  333. | `DB_MCP_AUDIT_LOG` | unset | Optional append-only JSONL audit path |
  334. | `VERBOSE` | unset | `true`/`1` sets the startup log level to `debug` (the default is `error`, i.e. silent unless `LOG_LEVEL` is set) |
  335. | `MCP_DISABLE_LOGGING` | unset | `true`/`1` discards MCP protocol logs (writer-level switch, independent of `LOG_LEVEL`/`DISABLE_LOGGING`) |
  336. ## SQLite Configuration Options
  337. When using SQLite databases, you can leverage these additional configuration options:
  338. ### SQLite Connection Parameters
  339. | Parameter | Type | Default | Description |
  340. |-----------|------|---------|-------------|
  341. | `database_path` | string | Required | Path to SQLite database file or `:memory:` for in-memory |
  342. | `encryption_key` | string | - | Key for SQLCipher encrypted databases |
  343. | `read_only` | boolean | false | Open database in read-only mode |
  344. | `max_rows` | integer | unlimited | Maximum rows returned per query; larger results are truncated with an explicit notice. Works on all database types; the query tool also caps the query server-side (see Production Guardrails). |
  345. | `cache_size` | integer | 2000 | SQLite cache size in pages |
  346. | `journal_mode` | string | "WAL" | Journal mode: DELETE, TRUNCATE, PERSIST, WAL, OFF |
  347. | `use_modernc_driver` | boolean | true | Use modernc.org/sqlite (CGO-free) or mattn/go-sqlite3 |
  348. ### SQLite Examples
  349. #### Basic File Database
  350. ```json
  351. {
  352. "id": "my_sqlite_db",
  353. "type": "sqlite",
  354. "database_path": "./data/myapp.db",
  355. "journal_mode": "WAL",
  356. "cache_size": 2000
  357. }
  358. ```
  359. #### Encrypted Database (SQLCipher)
  360. ```json
  361. {
  362. "id": "encrypted_db",
  363. "type": "sqlite",
  364. "database_path": "./data/secure.db",
  365. "encryption_key": "your-secret-encryption-key",
  366. "use_modernc_driver": false
  367. }
  368. ```
  369. #### In-Memory Database
  370. ```json
  371. {
  372. "id": "memory_db",
  373. "type": "sqlite",
  374. "database_path": ":memory:",
  375. "cache_size": 1000
  376. }
  377. ```
  378. #### Read-Only Database
  379. ```json
  380. {
  381. "id": "reference_data",
  382. "type": "sqlite",
  383. "database_path": "./data/reference.db",
  384. "read_only": true,
  385. "journal_mode": "DELETE"
  386. }
  387. ```
  388. ## Oracle Configuration Options
  389. When using Oracle databases, you can leverage these additional configuration options:
  390. ### Oracle Connection Parameters
  391. | Parameter | Type | Default | Description |
  392. |-----------|------|---------|-------------|
  393. | `host` | string | Required | Oracle database host |
  394. | `port` | integer | 1521 | Oracle listener port |
  395. | `service_name` | string | - | Service name (recommended for RAC) |
  396. | `sid` | string | - | System identifier (legacy, use service_name instead) |
  397. | `user` | string | Required | Database username |
  398. | `password` | string | Required | Database password |
  399. | `wallet_location` | string | - | Path to Oracle Cloud wallet directory |
  400. | `tns_admin` | string | - | Path to directory containing tnsnames.ora |
  401. | `tns_entry` | string | - | Named entry from tnsnames.ora |
  402. | `edition` | string | - | Edition-Based Redefinition edition name |
  403. | `pooling` | boolean | false | Enable driver-level connection pooling |
  404. | `standby_sessions` | boolean | false | Allow queries on standby databases |
  405. | `nls_lang` | string | AMERICAN_AMERICA.AL32UTF8 | Character set configuration |
  406. ### Oracle Examples
  407. #### Basic Oracle Connection (Development)
  408. ```json
  409. {
  410. "id": "oracle_dev",
  411. "type": "oracle",
  412. "host": "localhost",
  413. "port": 1521,
  414. "service_name": "XEPDB1",
  415. "user": "testuser",
  416. "password": "testpass",
  417. "max_open_conns": 50,
  418. "max_idle_conns": 10,
  419. "conn_max_lifetime_seconds": 1800
  420. }
  421. ```
  422. #### Oracle with SID (Legacy)
  423. ```json
  424. {
  425. "id": "oracle_legacy",
  426. "type": "oracle",
  427. "host": "oracledb.company.com",
  428. "port": 1521,
  429. "sid": "ORCL",
  430. "user": "app_user",
  431. "password": "app_password"
  432. }
  433. ```
  434. #### Oracle Cloud Autonomous Database (with Wallet)
  435. ```json
  436. {
  437. "id": "oracle_cloud",
  438. "type": "oracle",
  439. "user": "ADMIN",
  440. "password": "your-cloud-password",
  441. "wallet_location": "/path/to/wallet_DBNAME",
  442. "service_name": "dbname_high"
  443. }
  444. ```
  445. #### Oracle RAC (Real Application Clusters)
  446. ```json
  447. {
  448. "id": "oracle_rac",
  449. "type": "oracle",
  450. "host": "scan.company.com",
  451. "port": 1521,
  452. "service_name": "production",
  453. "user": "app_user",
  454. "password": "app_password",
  455. "max_open_conns": 100,
  456. "max_idle_conns": 20
  457. }
  458. ```
  459. #### Oracle with TNS Entry
  460. ```json
  461. {
  462. "id": "oracle_tns",
  463. "type": "oracle",
  464. "tns_admin": "/opt/oracle/network/admin",
  465. "tns_entry": "PROD_DB",
  466. "user": "app_user",
  467. "password": "app_password"
  468. }
  469. ```
  470. #### Oracle with Edition-Based Redefinition
  471. ```json
  472. {
  473. "id": "oracle_ebr",
  474. "type": "oracle",
  475. "host": "oracledb.company.com",
  476. "port": 1521,
  477. "service_name": "production",
  478. "user": "app_user",
  479. "password": "app_password",
  480. "edition": "v2_0"
  481. }
  482. ```
  483. ### Oracle Connection String Priority
  484. When multiple connection methods are configured, the following priority is used:
  485. 1. **TNS Entry** (if `tns_entry` and `tns_admin` are configured)
  486. 2. **Wallet** (if `wallet_location` is configured) - for Oracle Cloud
  487. 3. **Standard** (host:port/service_name) - default method
  488. ## Available Tools
  489. For each connected database, DB MCP Server automatically generates these specialized tools:
  490. ### Query Tools
  491. | Tool Name | Description |
  492. |-----------|-------------|
  493. | `query_<db_id>` | Execute SELECT queries and get results as a tabular dataset. Optional `mask_pii` boolean redacts PII by content shape (see the guardrails table); it is additive — a database configured with `"mask_pii": true` masks regardless. |
  494. | `execute_<db_id>` | Run data manipulation statements (INSERT, UPDATE, DELETE). Optional `dry_run` boolean classifies the statement offline — kind, risk level, advisories — and executes nothing; destructive statements that do run carry a `⚠ Risk notice` in their result. |
  495. | `transaction_<db_id>` | Begin, commit, and rollback transactions. Also carries the undo actions: `list_snapshots` and `rollback_snapshot` (with `snapshot_id`). Every `DELETE`/`UPDATE` run through `execute_<db_id>` records a pre-mutation snapshot of the rows it will affect — best-effort, bounded to the last 25 per database — and reports its ID in the result. |
  496. ### Schema Tools
  497. | Tool Name | Description |
  498. |-----------|-------------|
  499. | `schema_<db_id>` | Get information about tables, columns, indexes, and foreign keys |
  500. | `generate_schema_<db_id>` | Generate SQL or code from database schema |
  501. ### Performance Tools
  502. | Tool Name | Description |
  503. |-----------|-------------|
  504. | `performance_<db_id>` | Analyze query performance via actions: `stats` / `slow_queries` (in-process tracker), `engine_slow_queries` (pg_stat_statements / MySQL digest tables / Oracle v$sqlarea), `suggest` (static SQL lint), `suggest_indexes` (heuristic CREATE INDEX advice for one statement, equality-first composites, verify with EXPLAIN), `validate_suggestions` (PostgreSQL: installs the same suggestions as cost-free hypothetical indexes via the hypopg extension and reports whether the planner actually picks each one — ground-truth validation instead of manual EXPLAIN), `workload_suggestions` (same analysis across the top-N expensive workload statements, weighted by executions), `index_health` (duplicate/redundant/unused/invalid indexes and table bloat findings from catalogs; usage evidence where engine statistics exist), `db_health` (everything index_health covers plus connection-pressure utilization vs max_connections), `reset` |
  505. | `explain_<db_id>` | Show the execution plan for a SQL statement without running it; `analyze: true` executes with timing/buffer stats (PostgreSQL/MySQL). Writes stay blocked on read-only databases |
  506. | `describe_<db_id>` | Inspect one table's columns, indexes, and row estimate via engine catalog queries |
  507. | `health_<db_id>` | Report connectivity, ping latency, connection-pool state, and engine stats (PostgreSQL buffer-cache hit ratio, MySQL InnoDB buffer efficiency) |
  508. ### TimescaleDB Tools
  509. For PostgreSQL databases with the `timescaledb` extension installed, these additional
  510. specialized tools are registered automatically at startup (registration is config-driven,
  511. so it also works under `--lazy-loading`; each handler verifies the extension at call time
  512. and returns an actionable error when it is absent):
  513. | Tool Name | Description |
  514. |-----------|-------------|
  515. | `timescaledb_timeseries_query_<db_id>` | Execute optimized time-series queries with time bucketing (`time_bucket`), filtering, and window functions |
  516. | `timescaledb_analyze_timeseries_<db_id>` | Analyze time-series patterns (trend, seasonality summary) for one table/column |
  517. | `timescaledb_list_hypertables_<db_id>` | List hypertables with their time column and dimension count (read-only) |
  518. | `timescaledb_compression_settings_<db_id>` | Show compression configuration for hypertables (read-only) |
  519. | `timescaledb_retention_policy_<db_id>` | Show configured retention policies (read-only) |
  520. | `timescaledb_list_continuous_aggregates_<db_id>` | List continuous aggregates with bucket interval and refresh policy (read-only) |
  521. | `timescaledb_continuous_aggregate_info_<db_id>` | Inspect one continuous aggregate in detail (read-only) |
  522. In unified mode the same seven tools appear once as `timescaledb_timeseries_query`,
  523. `timescaledb_analyze_timeseries`, `timescaledb_list_hypertables`,
  524. `timescaledb_compression_settings`, `timescaledb_retention_policy`,
  525. `timescaledb_list_continuous_aggregates`, and `timescaledb_continuous_aggregate_info`,
  526. each taking a required `database` parameter.
  527. > **Scope note**: read-only discovery above goes through the query pipeline and therefore
  528. > stays usable on `read_only` databases; each handler checks for the `timescaledb`
  529. > extension first. Write-policy operations (hypertable creation, compression toggles,
  530. > add/remove retention or refresh policies) remain unexposed — use plain SQL through
  531. > the query/execute tools in the meantime. For detailed documentation, see
  532. > [TIMESCALEDB_TOOLS.md](docs/TIMESCALEDB_TOOLS.md).
  533. ### Unified Tool Mode
  534. If you connect many databases (5+), the per-database tool naming generates a large
  535. number of tools (5 × N). Some MCP clients — Claude in particular — apply strict
  536. limits on the total number of tools and tool description size that can cause the
  537. agent to fail to load the server, ignore tools, or refuse to call them. Issue #18
  538. documents this exact symptom: "the db-mcp-server does not function properly with
  539. Claude, even though it works fine with OpenAI".
  540. For these clients, launch the server with the `--unified-tools` flag to register six consolidated tools (`query`, `execute`, `transaction`, `performance`, `explain`, `describe`, `schema`, `filter_tables`) instead of per-database tools:
  541. ```bash
  542. ./bin/server -t stdio -c config.json --unified-tools
  543. ```
  544. **Context-window cost (measured, `TestToolTokenBenchmark`, re-verified 2026-08 via `scripts/token-benchmark.sh`):** unified mode costs ~1.25–1.6k tokens **regardless of how many databases are connected**, while per-database mode costs ~800 tokens per database (7 tools each) — 10 connected databases ≈ 8k tokens, an 80% wire-payload saving with unified. With one database only, per-database naming is slightly cheaper; unified wins from two databases onward and scales flat thereafter. Re-measure the real wire payload yourself with `scripts/token-benchmark.sh`; methodology and results in [docs/benchmark-token-efficiency.md](docs/benchmark-token-efficiency.md).
  545. ```
  546. In unified mode, each tool accepts a required `database` parameter that names
  547. which database the call should target. See the [Configuration](#configuration)
  548. section for the full list of available databases. This dramatically reduces the
  549. tool count and the cumulative description size, which resolves the Claude
  550. compatibility issues.
  551. For very large configurations, also enable `--lazy-loading` so that startup
  552. doesn't open connections to databases that may never be queried during the
  553. session.
  554. ## Examples
  555. ### Querying Multiple Databases
  556. ```sql
  557. -- Query the MySQL database
  558. query_mysql1("SELECT * FROM users LIMIT 10")
  559. -- Query the PostgreSQL database in the same context
  560. query_postgres1("SELECT * FROM products WHERE price > 100")
  561. -- Query the SQLite database
  562. query_sqlite_app("SELECT * FROM local_data WHERE created_at > datetime('now', '-1 day')")
  563. -- Query the Oracle database
  564. query_oracle_dev("SELECT * FROM employees WHERE hire_date > SYSDATE - 30")
  565. ```
  566. ### Managing Transactions
  567. The `transaction_<db_id>` tool supports `begin`, `execute`, `commit`, and `rollback` actions. Each `begin` returns a `transactionId`; pass it back to stage statements and to commit or roll back:
  568. ```json
  569. // 1. Start a transaction
  570. { "action": "begin" }
  571. // → { "transactionId": "tx_mysql1_1730000000000000000" }
  572. // 2. Execute statements within the transaction
  573. {
  574. "action": "execute",
  575. "transactionId": "tx_mysql1_1730000000000000000",
  576. "statement": "INSERT INTO orders (customer_id, product_id) VALUES (1, 2)"
  577. }
  578. // 3a. Commit — persists all staged statements
  579. { "action": "commit", "transactionId": "tx_mysql1_1730000000000000000" }
  580. // 3b. OR rollback — discards all staged statements
  581. { "action": "rollback", "transactionId": "tx_mysql1_1730000000000000000" }
  582. ```
  583. Unknown or already-retired transaction IDs return a clear error instead of a silent success, so agents can detect and recover from lost-transaction situations.
  584. ### Exploring Database Schema
  585. ```sql
  586. -- Get all tables in the database
  587. schema_mysql1("tables")
  588. -- Get columns for a specific table
  589. schema_mysql1("columns", "users")
  590. -- Get constraints
  591. schema_mysql1("constraints", "orders")
  592. ```
  593. ### Working with SQLite-Specific Features
  594. ```sql
  595. -- Create a table in SQLite
  596. execute_sqlite_app("CREATE TABLE IF NOT EXISTS local_cache (key TEXT PRIMARY KEY, value TEXT, timestamp DATETIME)")
  597. -- Use SQLite-specific date functions
  598. query_sqlite_app("SELECT * FROM events WHERE date(created_at) = date('now')")
  599. -- Query SQLite master table for schema information
  600. query_sqlite_app("SELECT name, sql FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
  601. -- Performance optimization with WAL mode
  602. execute_sqlite_app("PRAGMA journal_mode = WAL")
  603. execute_sqlite_app("PRAGMA synchronous = NORMAL")
  604. ```
  605. ### Working with Oracle-Specific Features
  606. ```sql
  607. -- Query user tables (excludes system schemas)
  608. query_oracle_dev("SELECT table_name FROM user_tables ORDER BY table_name")
  609. -- Use Oracle-specific date functions
  610. query_oracle_dev("SELECT employee_id, hire_date FROM employees WHERE hire_date >= TRUNC(SYSDATE, 'YEAR')")
  611. -- Oracle sequence operations
  612. execute_oracle_dev("CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 1")
  613. query_oracle_dev("SELECT emp_seq.NEXTVAL FROM DUAL")
  614. -- Oracle-specific data types
  615. query_oracle_dev("SELECT order_id, TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') FROM orders")
  616. -- Get schema metadata from Oracle data dictionary
  617. query_oracle_dev("SELECT column_name, data_type, nullable FROM user_tab_columns WHERE table_name = 'EMPLOYEES'")
  618. -- Use Oracle analytic functions
  619. query_oracle_dev("SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) as salary_rank FROM employees")
  620. ```
  621. ## Troubleshooting
  622. ### Common Issues
  623. - **Connection Failures**: Verify network connectivity and database credentials
  624. - **Permission Errors**: Ensure the database user has appropriate permissions
  625. - **Timeout Issues**: Check the `query_timeout` setting in your configuration
  626. ### Logs
  627. Enable verbose logging for troubleshooting:
  628. ```bash
  629. ./bin/server -t sse -c config.json -v
  630. ```
  631. ## Testing
  632. ### Running Tests
  633. The project includes comprehensive unit and integration tests for all supported databases.
  634. #### Unit Tests
  635. Run unit tests (no database required):
  636. ```bash
  637. make test
  638. # or
  639. go test -short ./...
  640. ```
  641. #### Integration Tests
  642. Integration tests require running database instances. We provide Docker Compose configurations for easy setup.
  643. **Test All Databases:**
  644. ```bash
  645. # Start test databases
  646. docker-compose -f docker-compose.test.yml up -d
  647. # Run all integration tests
  648. go test ./... -v
  649. # Stop test databases
  650. docker-compose -f docker-compose.test.yml down -v
  651. ```
  652. **Test Oracle Database:**
  653. ```bash
  654. # Start Oracle test environment
  655. ./oracle-test.sh start
  656. # Run Oracle tests
  657. ./oracle-test.sh test
  658. # or manually
  659. ORACLE_TEST_HOST=localhost go test -v ./pkg/db -run TestOracle
  660. ORACLE_TEST_HOST=localhost go test -v ./pkg/dbtools -run TestOracle
  661. # Stop Oracle test environment
  662. ./oracle-test.sh stop
  663. # Full cleanup (removes volumes)
  664. ./oracle-test.sh cleanup
  665. ```
  666. **Test TimescaleDB:**
  667. ```bash
  668. # Start TimescaleDB test environment
  669. ./timescaledb-test.sh start
  670. # Run TimescaleDB tests
  671. TIMESCALEDB_TEST_HOST=localhost go test -v ./pkg/db/timescale ./internal/delivery/mcp
  672. # Stop TimescaleDB test environment
  673. ./timescaledb-test.sh stop
  674. ```
  675. #### Regression Tests
  676. Run comprehensive regression tests across all database types:
  677. ```bash
  678. # Ensure all test databases are running
  679. docker-compose -f docker-compose.test.yml up -d
  680. ./oracle-test.sh start
  681. # Run regression tests
  682. MYSQL_TEST_HOST=localhost \
  683. POSTGRES_TEST_HOST=localhost \
  684. ORACLE_TEST_HOST=localhost \
  685. go test -v ./pkg/db -run TestRegression
  686. # Run connection pooling tests
  687. go test -v ./pkg/db -run TestConnectionPooling
  688. ```
  689. ### Continuous Integration
  690. All tests run automatically on every pull request via GitHub Actions. The CI pipeline includes:
  691. - **Unit Tests**: Fast tests that don't require database connections
  692. - **Integration Tests**: Tests against MySQL, PostgreSQL, SQLite, and Oracle databases
  693. - **Regression Tests**: Comprehensive tests ensuring backward compatibility
  694. - **Linting**: Code quality checks with golangci-lint
  695. ## Contributing
  696. We welcome contributions to the DB MCP Server project! To contribute:
  697. 1. Fork the repository
  698. 2. Create a feature branch (`git checkout -b feature/amazing-feature`)
  699. 3. Commit your changes (`git commit -m 'feat: add amazing feature'`)
  700. 4. Push to the branch (`git push origin feature/amazing-feature`)
  701. 5. Open a Pull Request
  702. Please see our [CONTRIBUTING.md](docs/CONTRIBUTING.md) file for detailed guidelines.
  703. ### Testing Your Changes
  704. Before submitting a pull request, please ensure:
  705. 1. All unit tests pass: `go test -short ./...`
  706. 2. Integration tests pass for affected databases
  707. 3. Code follows the project's style guidelines: `golangci-lint run ./...`
  708. 4. New features include appropriate test coverage
  709. ## License
  710. This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.