Why Universal DB MCP?

Imagine asking your AI assistant: "Show me the top 10 customers by order value this month" and getting instant results from your database - no SQL writing required. Universal DB MCP makes this possible by bridging AI assistants with your databases through the Model Context Protocol (MCP) and HTTP API.

You: "What's the average order value for users who signed up in the last 30 days?"

AI: Let me query that for you...

┌─────────────────────────────────────┐
│ Average Order Value: $127.45        │
│ Total New Users: 1,247              │
│ Users with Orders: 892 (71.5%)      │
└─────────────────────────────────────┘

✨ Features

17 Database Support - MySQL, PostgreSQL, Redis, Oracle, SQL Server, MongoDB, SQLite, and 10 Chinese domestic databases
55+ Platform Integrations - Works with Claude Desktop, Cursor, VS Code, ChatGPT, Dify, and 50+ other platforms
41 MCP Tools - Connection, query, schema, profile, template, governance, sample-data, PII, audit, CSV export/import (full list)
Flexible Architecture - 2 startup modes (stdio/http) with 4 access methods: MCP stdio, MCP SSE, MCP Streamable HTTP, and REST API
Security First - Read-only mode by default prevents accidental data modifications
Intelligent Caching - Schema caching with configurable TTL for blazing-fast performance
50-100× faster get_table_info - Per-table metadata path for Oracle/DM, skips full schema scan
Smart Sample Data - generate_sample_data auto-detects PK type: IDENTITY (skip), UUID (uuid v4), INT/NUMBER (MAX+rowIndex+1)
Batch Query Optimization - Up to 100x faster schema retrieval for large databases
Schema Enhancement - Table comments, implicit relationship inference for better Text2SQL accuracy
Multi-Schema Support - Automatic discovery of all user schemas (PostgreSQL, SQL Server, Oracle, DM, and more)
Data Migration - SQL backup (export_backup) + CSV import/export with RFC 4180 serialization, partitioned reads, batched writes (docs)
Data Masking - Automatic sensitive data protection (phone, email, ID card, bank card, etc.)
Data Governance - Profile backup/restore (export_profiles / import_profiles), schema diff, PII masking, audit log (docs)
Connection Stability - Connection pooling, TCP Keep-Alive, and automatic reconnection for long-running sessions
Production Observability - Prometheus /metrics endpoint + MCP get_metrics tool + slow-query ring buffer, zero new dependencies (docs)

See GitHub Releases for full changelog, and docs/03-features/ for per-feature detail.

Smart Sample Data (v4.0.3) - generate_sample_data auto-detects PK type: IDENTITY (skip), UUID (uuid v4), INT/NUMBER (MAX+rowIndex+1)

Performance Improvements

Operation	Before	After	Improvement
`get_table_info` (Oracle)	60-90s	526-866ms	50-100×
`get_table_info` (DM)	30-60s	696-852ms	50-100×
`get_sample_data` / `get_enum_values`	30-60s	~150ms	200×
Schema cache (50 tables)	~5s	~200ms	25×
Schema cache (500 tables)	~50s	~500ms	100×

🛠️ Available Tools (41 total)

Connection (4)

Tool	Description
`use_profile`	激活保存的 profile + 建立连接 (替换 v4.x 的 connect_database)
`save_profile`	保存命名 profile (host/port/user 等 + permissionMode)
`disconnect_profile`	断开当前 profile 的连接 (替换 v4.x 的 disconnect_database)
`get_connection_status`	Show connection state, schema cache hit rate, last error
`get_metrics`	Prometheus-style counters + histograms + slow-query ring buffer

Query / Schema (7)

Tool	Description
`execute_query`	Run SQL with bound `?` params (SQL injection safe)
`execute_script`	Multi-statement SQL / PL block execution (script permission)
`execute_batch`	Single SQL × multiple param sets (1000-row batch limit)
`get_table_info`	Single-table metadata — 50-100× faster than v3.x (per-table SQL path)
`get_sample_data`	N sample rows with PII auto-masking
`get_enum_values`	Unique values + counts for enum-like columns
`clear_cache`	Invalidate the schema cache

Data Generation / Templates (5)

Tool	Description
`generate_sample_data`	Smart sample data insertion — auto-detects IDENTITY PK (skip), UUID PK (uuid v4), INT PK (MAX+sequence);`rules` API for column-level overrides
`save_template`	Save parameterized SQL template (`${name}` placeholders)
`list_templates`	List templates with tag search
`get_template`	Fetch one template by id
`delete_template`	Delete a template
`execute_template`	Run a template with params (safe substitution)

Profile Management (6)

Tool	Description
`save_profile`	Save named connection profile (credentials encrypted at rest)
`list_profiles`	List with role/tag/enabled filters
`get_profile`	Fetch one profile
`use_profile`	Switch active connection (works without current DB connection)
`enable_profile` / `disable_profile`	Toggle profile active state
`delete_profile`	Delete a profile
`disconnect_profile`	Disconnect a profile without deleting
`export_profiles`	Dump all profiles (passwords REDACTED by default)
`import_profiles`	Restore profiles from YAML/JSON

Data Governance (6)

Tool	Description
`set_pii_config`	Set per-table/per-column PII masking rules
`get_pii_config`	View current PII rules
`audit_log`	Query recorded query history (filters: db, kind, since, until, onlyErrors)
`get_query_history`	Same data via analytics-friendly API
`explain_query`	Get EXPLAIN plan only (no advice)
`explain_query_with_advice`	EXPLAIN + index-tuning hints
`lint_sql`	Static SQL analysis (issues / warnings)
`list_query_plans`	List captured EXPLAIN plans by query hash
`compare_query_plans`	Diff two plans for the same query hash

SQL File / CSV (4) ← v4.0.5 恢复

Tool	Description
`execute_sql_file`	Run a `.sql` file from `DB_ALLOWED_FILE_PATHS` whitelist (script permission)
`export_backup`	Dump schema as SQL DDL to a file
`export_table_csv`	Stream single table to CSV with `WHERE` / `ORDER BY` / `LIMIT` / `OFFSET`; RFC 4180 serialization
`import_csv`	Import CSV back to existing table in batches (APPEND mode, `DB_ALLOWED_FILE_PATHS` whitelist)

Legacy / Removed (v4.0)

❌ use_tool_group — removed (lazy-load removed in v4.0)
❌ use_tool_schema — removed (full schemas in tools/list now)

See tools reference for parameter details.

🚀 Quick Start

v5.0.0 新流程(Profile-based)

所有凭据现在通过 create_profile 管理,不再写进 .mcp.json。同一套 DB 凭据可跨多个项目复用,不需要重复输入。

1. 第一次使用 — 保存 profile

// 在 Claude Desktop / Claude Code 里:
create_profile({
  name: "my-dev-db",             // /^[a-zA-Z0-9_-]+$/
  type: "mysql",                 // oracle / mysql / postgres / redis / dm / ...
  config: {
    host: "localhost",
    port: 3306,
    user: "root",
    password: "your_password",
    database: "your_database",
  },
  permissionMode: "readwrite",   // safe / readwrite / full(默认 readwrite,含 batch)
})

profile 存到 ~/.universal-db-mcp/profiles.db(Windows: %USERPROFILE%\.universal-db-mcp\profiles.db),跨项目保留。

旧 save_profile 名字仍兼容(别名 → create_profile)。想更新已有 profile,用 update_profile。

2. 激活并绑定项目

use_profile({
  name: "my-dev-db",
  // recordToProject 默认 true — 自动写 <cwd>/.db-profile,下次 MCP 启动自动激活
  // recordToProject: false 显式跳过(临时激活不绑项目)
})

下次 MCP 启动时,自动读 <cwd>/.db-profile 并激活指定 profile — 无需手动 use_profile。文件名从 v4.x 的 .profile 改为 .db-profile(避免和 shell/IDE 的 .profile 冲突)。旧 .profile 文件还能作为 fallback 读到(迁移期)。

3. 开始查询

"Show me the structure of the users table"
"Count orders from the last 7 days"
"Find the top 5 products by sales"

数据存储

所有持久化数据都放在 ~/.universal-db-mcp/(可用 DB_GLOBAL_DIR 覆盖):

~/.universal-db-mcp/
├── profiles.db                       # 全局 profile 注册表
├── config.json                       # 配置标记
├── my-dev-db/                        # profile 名作为子目录(per-profile 隔离)
│   ├── history.db                    # 查询历史(按 profile 隔离)
│   ├── templates.db                  # SQL 模板
│   └── plans.db                      # EXPLAIN 历史
└── other-profile/
    ├── history.db
    ├── templates.db
    └── plans.db

多项目工作流

# 项目 A
cd ~/projects/app-a
# MCP 启动自动激活 — 写 <cwd>/.db-profile (recordToProject: true 是默认行为)
use_profile({name: 'my-dev-db'})

# 项目 B(同一 DB)
cd ~/projects/app-b
use_profile({name: 'my-dev-db'})
# 同一 profile,无需重新保存

# 项目 C(不同 DB — staging)
create_profile({name: 'staging-db', type: 'mysql', config: {...}})
use_profile({name: 'staging-db'})

MCP Mode 配置(简化版)

.mcp.json 现在只需要安装信息,不需要凭据:

{
  "mcpServers": {
    "universal-db-mcp": {
      "command": "npx",
      "args": ["@joyous-coder/universal-db-mcp"]
    }
  }
}

启动后用 create_profile + use_profile 配连接。v4.x .mcp.json env vars (DB_HOST/DB_USER/DB_PASSWORD/DB_TYPE) 会被静默忽略 +一次性 stderr 提示迁移。

HTTP API Mode

# Set environment variables
export MODE=http
export HTTP_PORT=3000
export API_KEYS=your-secret-key

### HTTP API Mode

```bash
# Set environment variables
export MODE=http
export HTTP_PORT=3000
export API_KEYS=your-secret-key

# Start the server
npx @joyous-coder/universal-db-mcp

# Test the API
curl http://localhost:3000/api/health

MCP SSE Mode (Dify and Remote Access)

When running in HTTP mode, the server also exposes MCP protocol endpoints via SSE (Server-Sent Events) and Streamable HTTP. This allows platforms like Dify to connect using the MCP protocol directly.

SSE Endpoint (Legacy):

GET http://localhost:3000/sse?type=mysql&host=localhost&port=3306&user=root&password=xxx&database=mydb

Streamable HTTP Endpoint (MCP 2025 Spec, Recommended):

POST http://localhost:3000/mcp
Headers:
  X-DB-Type: mysql
  X-DB-Host: localhost
  X-DB-Port: 3306
  X-DB-User: root
  X-DB-Password: your_password
  X-DB-Database: your_database
Body: MCP JSON-RPC request

Endpoint	Method	Description
`/sse`	GET	Establish SSE connection (legacy)
`/sse/message`	POST	Send message to SSE session
`/mcp`	POST	Streamable HTTP endpoint (recommended)
`/mcp`	GET	SSE stream for Streamable HTTP
`/mcp`	DELETE	Close session

See Dify Integration Guide for detailed setup instructions.

📊 Supported Databases

Database	Type	Default Port	Category
MySQL	`mysql`	3306	Open Source
PostgreSQL	`postgres`	5432	Open Source
Redis	`redis`	6379	NoSQL
Oracle	`oracle`	1521	Commercial
SQL Server	`sqlserver`	1433	Commercial
MongoDB	`mongodb`	27017	NoSQL
SQLite	`sqlite`	-	Embedded
Dameng (达梦)	`dm`	5236	Chinese
KingbaseES	`kingbase`	54321	Chinese
GaussDB	`gaussdb`	5432	Chinese (Huawei)
OceanBase	`oceanbase`	2881	Chinese (Ant)
TiDB	`tidb`	4000	Distributed
ClickHouse	`clickhouse`	8123	OLAP
PolarDB	`polardb`	3306	Cloud (Alibaba)
Vastbase	`vastbase`	5432	Chinese
HighGo	`highgo`	5866	Chinese
GoldenDB	`goldendb`	3306	Chinese (ZTE)

🏗️ Architecture

┌─────────────────────────────────────────────────────────────────────────┐
│                         Universal DB MCP                                 │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│  Startup Modes:                                                          │
│  ┌────────────────────────────┬────────────────────────────────────┐    │
│  │ stdio mode                 │ http mode                          │    │
│  │ (npm run start:mcp)        │ (npm run start:http)               │    │
│  └─────────────┬──────────────┴───────────────┬────────────────────┘    │
│                │                              │                          │
│                ▼                              ▼                          │
│  ┌─────────────────────────┐    ┌───────────────────────────────────┐   │
│  │      MCP Protocol       │    │           HTTP Server             │   │
│  │    (stdio transport)    │    │                                   │   │
│  │                         │    │  ┌─────────────────────────────┐  │   │
│  │  Tools:                 │    │  │      MCP Protocol           │  │   │
│  │  • execute_query        │    │  │  (SSE / Streamable HTTP)    │  │   │
│  │  • get_schema           │    │  │                             │  │   │
│  │  • get_table_info       │    │  │  Tools: (same as stdio)     │  │   │
│  │  • clear_cache          │    │  │  • execute_query            │  │   │
│  │  • get_enum_values      │    │  │  • get_schema               │  │   │
│  │  • get_sample_data      │    │  │  • get_table_info           │  │   │
│  │  • save_profile     │    │  │  • clear_cache              │  │   │
│  │  • dissave_profile  │    │  │  • get_enum_values          │  │   │
│  │  • get_connection_status│    │  │  • get_sample_data          │  │   │
│  │                         │    │  │  • save_profile         │  │   │
│  │  For: Claude Desktop,   │    │  │  • dissave_profile      │  │   │
│  │       Cursor, etc.      │    │  │  • get_connection_status    │  │   │
│  └─────────────┬───────────┘    │  │                             │  │   │
│                │                │  │  For: Dify, Remote Access   │  │   │
│                │                │  └──────────────┬──────────────┘  │   │
│                │                │                 │                 │   │
│                │                │  ┌──────────────┴──────────────┐  │   │
│                │                │  │        REST API             │  │   │
│                │                │  │                             │  │   │
│                │                │  │  Endpoints:                 │  │   │
│                │                │  │  • /api/connect             │  │   │
│                │                │  │  • /api/query               │  │   │
│                │                │  │  • /api/schema              │  │   │
│                │                │  │  • ... (10+ endpoints)      │  │   │
│                │                │  │                             │  │   │
│                │                │  │  For: Coze, n8n, Custom     │  │   │
│                │                │  └──────────────┬──────────────┘  │   │
│                │                └─────────────────┼─────────────────┘   │
│                │                                  │                     │
│                └──────────────────┬───────────────┘                     │
│                                   ▼                                     │
│  ┌──────────────────────────────────────────────────────────────────┐  │
│  │                     Core Business Logic                           │  │
│  │  • Query Execution    • Schema Caching                           │  │
│  │  • Safety Validation  • Connection Management                    │  │
│  └──────────────────────────────────┬───────────────────────────────┘  │
│                                     ▼                                   │
│  ┌──────────────────────────────────────────────────────────────────┐  │
│  │                    Database Adapter Layer                         │  │
│  │  MySQL │ PostgreSQL │ Redis │ Oracle │ MongoDB │ SQLite │ ...    │  │
│  │        (Connection Pool + TCP Keep-Alive + Auto-Retry)           │  │
│  └──────────────────────────────────────────────────────────────────┘  │
│                                                                          │
└─────────────────────────────────────────────────────────────────────────┘

🔒 Security

By default, Universal DB MCP runs in read-only mode, blocking all write operations (INSERT, UPDATE, DELETE, DROP, etc.).

Permission Modes

Fine-grained permission control is supported for flexible configuration:

Mode	Allowed Operations	Description
`safe` (default)	SELECT	Read-only, safest
`readwrite`	SELECT, INSERT, UPDATE	Read/write but no delete
`full`	All operations	Full control (dangerous!)
`custom`	Custom combination	Specify via `--permissions`

Permission Types:

read - SELECT queries (always included)
insert - INSERT, REPLACE
update - UPDATE
delete - DELETE, TRUNCATE
ddl - CREATE, ALTER, DROP, RENAME

Usage Examples:

# Read-only mode (default)
npx @joyous-coder/universal-db-mcp --type mysql ...

# Read/write but no delete
npx @joyous-coder/universal-db-mcp --type mysql --permission-mode readwrite ...

# Custom: only read and insert
npx @joyous-coder/universal-db-mcp --type mysql --permissions read,insert ...

# Full control (equivalent to --danger-allow-write)
npx @joyous-coder/universal-db-mcp --type mysql --permission-mode full ...

Permission Configuration by Transport:

⚠️ Different transports use different parameter naming conventions!

Transport	Parameter Location	Permission Mode	Custom Permissions
STDIO (Claude Desktop)	CLI args	`--permission-mode`	`--permissions`
SSE (Dify, etc.)	URL Query	`permissionMode`	`permissions`
Streamable HTTP	HTTP Header	`X-DB-Permission-Mode`	`X-DB-Permissions`
REST API	JSON Body	`permissionMode`	`permissions`

Best Practices:

Never enable write mode in production
Use dedicated read-only database accounts
Connect through VPN or bastion hosts
Regularly audit query logs

🔌 Supported Platforms

Universal DB MCP works with any platform that supports the MCP protocol or REST API. Here's a comprehensive list:

AI-Powered Code Editors & IDEs

Platform	Access Method	Description	Guide
Cursor	MCP stdio	AI-powered code editor with built-in MCP support	EN / 中文
Windsurf	MCP stdio	Codeium's AI IDE with Cascade agent	EN / 中文
VS Code	MCP stdio / REST API	Via GitHub Copilot agent mode or Cline/Continue extensions	EN / 中文
Zed	MCP stdio	High-performance open-source code editor	EN / 中文
IntelliJ IDEA	MCP stdio	JetBrains IDE with MCP support (2025.1+)	EN / 中文
PyCharm	MCP stdio	JetBrains Python IDE	EN / 中文
WebStorm	MCP stdio	JetBrains JavaScript IDE	EN / 中文
Android Studio	MCP stdio	Via JetBrains MCP plugin	EN / 中文
Neovim	MCP stdio	Via MCPHub.nvim plugin	EN / 中文
Emacs	MCP stdio	Via mcp.el package	EN / 中文

AI Coding Assistants

Platform	Access Method	Description	Guide
Claude Code	MCP stdio	Anthropic's agentic coding tool	EN / 中文
GitHub Copilot	MCP stdio	Agent mode in VS Code/JetBrains	EN / 中文
Cline	MCP stdio / REST API	Autonomous coding agent for VS Code	EN / 中文
Continue	MCP stdio	Open-source AI code assistant	EN / 中文
Roo Code	MCP stdio	Fork of Cline for VS Code	EN / 中文
Sourcegraph Cody	MCP stdio	AI coding assistant	EN / 中文
Amazon Q Developer	MCP stdio	AWS AI coding assistant	EN / 中文
Devin	MCP stdio	AI software engineer	EN / 中文
Goose	MCP stdio	Block's AI coding agent	EN / 中文
Gemini CLI	MCP stdio	Google's command-line AI tool	EN / 中文

Desktop AI Chat Applications

Platform	Access Method	Description	Guide
Claude Desktop	MCP stdio	Anthropic's official desktop app	EN / 中文
ChatGPT Desktop	MCP SSE/Streamable HTTP	OpenAI's desktop app with MCP connectors	EN / 中文
Cherry Studio	MCP stdio	Multi-model desktop chat app	EN / 中文
LM Studio	MCP stdio	Run local LLMs with MCP support	EN / 中文
Jan	MCP stdio	Open-source ChatGPT alternative	EN / 中文
Msty	MCP stdio	Desktop AI chat application	EN / 中文
LibreChat	MCP stdio	Open-source chat interface	EN / 中文
Witsy	MCP stdio	Desktop AI assistant	EN / 中文
5ire	MCP stdio	Cross-platform AI chat	EN / 中文
ChatMCP	MCP stdio	MCP-focused chat UI	EN / 中文
HyperChat	MCP stdio	Multi-platform chat app	EN / 中文
Tome	MCP stdio	macOS app for local LLMs	EN / 中文

Web-Based AI Platforms

Platform	Access Method	Description	Guide
Claude.ai	MCP SSE/Streamable HTTP	Anthropic's web interface	EN / 中文
ChatGPT	MCP SSE/Streamable HTTP	Via custom connectors	EN / 中文
Dify	MCP SSE/Streamable HTTP	LLM app development platform	EN / 中文
Coze	REST API	ByteDance's AI bot platform	EN / 中文
n8n	REST API / MCP	Workflow automation platform	EN / 中文
Replit	MCP stdio	Online IDE with AI agent	EN / 中文
MindPal	MCP SSE/Streamable HTTP	No-code AI agent builder	EN / 中文

Agent Frameworks & SDKs

Platform	Access Method	Description	Guide
LangChain	MCP stdio	Popular LLM framework	EN / 中文
Smolagents	MCP stdio	Hugging Face agent library	EN / 中文
OpenAI Agents SDK	MCP SSE/Streamable HTTP	OpenAI's agent framework	EN / 中文
Amazon Bedrock Agents	MCP SSE/Streamable HTTP	AWS AI agent service	EN / 中文
Google ADK	MCP stdio	Google's Agent Development Kit	EN / 中文
Vercel AI SDK	MCP stdio	Vercel's AI development kit	EN / 中文
Spring AI	MCP stdio	Java/Spring AI framework	EN / 中文

CLI Tools & Terminal

Platform	Access Method	Description	Guide
Claude Code CLI	MCP stdio	Terminal-based coding agent	EN / 中文
Warp	MCP stdio	AI-powered terminal	EN / 中文
Oterm	MCP stdio	Chat with Ollama via CLI	EN / 中文
MCPHost	MCP stdio	CLI chat with LLMs	EN / 中文

Productivity & Automation

Platform	Access Method	Description	Guide
Raycast	MCP stdio	macOS productivity launcher	EN / 中文
Notion	MCP SSE/Streamable HTTP	Workspace with AI integration	EN / 中文
Obsidian	MCP stdio	Via MCP Tools plugin	EN / 中文
Home Assistant	MCP stdio	Home automation platform	EN / 中文

Messaging Platform Integrations

Platform	Access Method	Description	Guide
Slack	MCP stdio / REST API	Via Slack MCP bots	EN / 中文
Discord	MCP stdio / REST API	Via Discord MCP bots	EN / 中文
Mattermost	MCP stdio	Open-source messaging	EN / 中文

Local LLM Runners

Platform	Access Method	Description	Guide
Ollama	MCP stdio	Run local LLMs	EN / 中文
LM Studio	MCP stdio	Local LLM desktop app	EN / 中文
Jan	MCP stdio	Offline ChatGPT alternative	EN / 中文

Development & Testing Tools

Platform	Access Method	Description	Guide
MCP Inspector	MCP stdio	Official MCP debugging tool	EN / 中文
Postman	REST API / MCP	API testing platform	EN / 中文

Note: Any MCP-compatible client can connect via stdio (local) or SSE/Streamable HTTP (remote). Any HTTP client can use the REST API.

📚 Documentation

Getting Started

Deployment

Database Guides

Database Support Overview
MySQL
PostgreSQL
ClickHouse (v3.2.9 — 5 protocol bugs fixed)
DM (达梦) (v3.2.8 — backup + sample-data fixes)
MongoDB (v3.2.7 — authSource + multi-arg)
More databases...

Features

Data Governance — profile backup, schema diff, PII, audit
Data Migration — CSV import/export (v3.3.0) + SQL backup
Multi-Profile — multi-DB routing, schema aggregation
Observability — Prometheus + slow-query ring buffer

HTTP API

Integrations

AI Assistants: Claude Desktop | Claude Code | GitHub Copilot | Cline | Continue

AI Platforms: Dify | Coze | n8n | ChatGPT | LangChain

Desktop Apps: Cherry Studio | LM Studio | Jan | Ollama

Messaging: Slack | Discord

Tools: MCP Inspector | Postman

📁 View all 55+ integration guides | 中文版本请在对应文档名后加 .zh-CN

Advanced

🤝 Contributing

Contributions are welcome! Please read our Contributing Guide before submitting a Pull Request.

# Clone the repository
git clone https://github.com/Anarkh-Lee/universal-db-mcp.git

# Install dependencies
npm install

# Build
npm run build

# Run tests
npm test

📄 License

This project is licensed under the MIT License.

🌟 Star History

If you find this project useful, please consider giving it a star! Your support helps us continue improving Universal DB MCP.

📝 Changelog

See CHANGELOG.md for a detailed version history.