Content
# MCP Server
A unified query server for multiple databases, allowing AI Agents like Claude Code and Cursor to query data and metadata from various databases through standard MCP tools.
## Open Source License
This project follows a "use it however you like, but don't blame me if AI goes rogue and deletes your database" license.
### Best Practices: Using with Skills
It is recommended to use this MCP with Skills to achieve:
- **Config Awareness**: Automatically select the appropriate operation strategy based on the actual database type and connection information used in the project
- **SQL Persistence**: Require the Agent to save the executed SQL migration files to disk for auditing and tracing
- **Security Norms**: Constrain Agent behavior through Skills to prevent misoperations
See the [`skills-template/db-migration-skill.md`](skills-template/db-migration-skill.md) template for more details.
## Feature Overview
- **Unified Interface**: One MCP Tools covering PostgreSQL, MySQL, ClickHouse, Oracle, SQL Server, Trino, Kingbase, and more
- **Centralized Configuration**: Connection information stored in a PostgreSQL or SQLite configuration database, shared among teams and individuals
- **Visual Management**: Admin Web UI for managing connections and data source types
- **Security Defaults**: Queries default to read-only, with automatic LIMIT, and write operations require explicit enablement
- **Query Auditing**: Each query automatically writes to the `query_log` table
## Supported Databases
| Type ID | Database | pip Dependency | Default Port | Status |
| ------------ | ---------------- | ------------------ | ----- | ---------------------- |
| `postgresql` | PostgreSQL | psycopg2-binary | 5432 | ✅ |
| `mysql` | MySQL | pymysql | 3306 | ✅ |
| `clickhouse` | ClickHouse | clickhouse-connect | 8123 | ✅ |
| `oracle` | Oracle 11g / 19c | oracledb | 1521 | ✅ |
| `mssql` | SQL Server | pymssql | 1433 | ✅ |
| `kingbase` | Kingbase | psycopg2-binary | 54321 | ✅ Reuse PG protocol |
| `trino` | Trino | trino | 8080 | ✅ catalog mapped to database |
| `dm` | DM | — | 5236 | ⏳ Type registered, adapter to be implemented |
## Quick Start
### 1. Environment Preparation
```bash
git clone https://github.com/No-name0719/db-mcp-server.git
cd db-mcp-server
conda create -n db-mcp python=3.11 -y
conda activate db-mcp
pip install -r requirements.txt
```
### 2. Initialize Configuration Database
Two configuration storage methods are supported, selected through `.env` `DB_MCP_CONFIG_BACKEND`:
#### Method A: SQLite (recommended for local/single machine, no PostgreSQL required)
SQLite stores the entire database as a local file (default `./data/db_mcp_server.db`), **automatically creating tables and writing preset data source types on first startup**, no manual SQL execution required.
```bash
cp .env.example .env
# Edit .env, set:
# DB_MCP_CONFIG_BACKEND=sqlite
# DB_MCP_SQLITE_PATH=./data/db_mcp_server.db # Optional, default value is fine
```
Backup/migrate configuration by copying the `.db` file.
#### Method B: PostgreSQL (recommended for team sharing)
Use a Bootstrap PostgreSQL to store all connection configurations. Create a database and execute initialization script `sqls/init.sql`:
```bash
createdb -h <pg_host> -p <pg_port> -U postgres mcp
psql -h <pg_host> -p <pg_port> -U postgres -d mcp -f sqls/init.sql
```
Keep `DB_MCP_CONFIG_BACKEND=postgres` (default) in `.env` and fill in `DB_MCP_PG_*` connection information.
### 3. Configure `.env`
```bash
cp .env.example .env
```
Main configuration items:
| Variable | Description | Default Value |
| ----------------------------- | --------------------------------- | ---------------------------- |
| `DB_MCP_CONFIG_BACKEND` | Configuration storage backend: `postgres` / `sqlite` | `postgres` |
| `DB_MCP_SQLITE_PATH` | SQLite database file path (backend=sqlite) | `./data/db_mcp_server.db` |
| `DB_MCP_PG_HOST` | Bootstrap PG host (backend=postgres) | `localhost` |
| `DB_MCP_PG_PORT` | Bootstrap PG port | `9432` |
| `DB_MCP_PG_DB` | Configuration database name | `mcp` |
| `DB_MCP_PG_USER` | Bootstrap PG user | `postgres` |
| `DB_MCP_PG_PASSWORD` | Bootstrap PG password | — |
| `DB_MCP_PG_SCHEMA` | Configuration schema | `db_mcp_server` |
| `DB_MCP_HOST` | MCP SSE listen address | `0.0.0.0` |
| `DB_MCP_PORT` | MCP SSE listen port | `9999` |
| `DB_MCP_PUBLIC_URL` | External SSE address (for client configuration) | `http://localhost:9999/sse` |
| `DB_MCP_ADMIN_HOST` | Admin listen address | `0.0.0.0` |
| `DB_MCP_ADMIN_PORT` | Admin listen port | `9998` |
| `ORACLE_CLIENT_DIR` | Oracle Instant Client path | `./lib/oracle_instantclient` |
| `DB_MCP_QUERY_DEFAULT_LIMIT` | Read-only query default row count | `100` |
| `DB_MCP_QUERY_MAX_LIMIT` | Read-only query maximum row count | `1000` |
| `DB_MCP_SAMPLE_DEFAULT_LIMIT` | Sample default row count | `10` |
| `DB_MCP_SAMPLE_MAX_LIMIT` | Sample maximum row count | `100` |
| `DB_MCP_CONNECT_TIMEOUT` | Connection timeout (seconds) | `10` |
| `DB_MCP_WRITE_CONFIRM_REQUIRED` | Whether write SQL requires confirm_token confirmation | `true` |
| `DB_MCP_WRITE_CONFIRM_TTL` | Confirm_token validity period (seconds) | `600` |
### 4. Build Admin Frontend (first time or after frontend update)
```bash
cd admin/frontend
npm install
npm run build # Output admin/dist/
cd ../..
```
### 5. Add Database Connection
Start Admin management interface:
```bash
python admin/app.py
# Visit http://localhost:9998
```
### 6. Start MCP Server
**Local development (stdio, automatically started by client):**
```bash
python server.py
```
**Team sharing (SSE, deployed on server):**
```bash
python server.py --sse
# Custom port: python server.py --sse --port 9500
```
Verify SSE service:
```bash
curl http://<server_ip>:9999/sse
# Or use scripts/test_local_mcp.py (need to start SSE service first)
python scripts/test_local_mcp.py
```
## Client Integration
### Method 1: Team-shared SSE (recommended)
After deploying the service on the server, team members only need to configure the URL without installing local dependencies.
Add to MCP configuration in the client:
```json
{
"mcpServers": {
"db-mcp": {
"url": "http://<server_ip>:9999/sse"
}
}
}
```
Client configuration locations:
| Client | Configuration method |
|--------|----------|
| **Claude Code** | Command line `claude mcp add db-mcp http://<server_ip>:9999/sse --transport sse -s user`, or edit `~/.claude.json` (Windows: `C:\Users\<username>\.claude.json`) |
| **Cursor** | Project or user-level MCP configuration file |
| **Qoder** | Top-right user settings → Qoder settings → MCP service → Top-right add |
> `DB_MCP_PUBLIC_URL` should be set to the address actually accessible by team members, which will be printed in the console when starting SSE mode.
### Method 2: Local stdio
Each machine runs independently, suitable for personal development or scenarios without a shared server.
**Claude Code / Cursor:**
```json
{
"mcpServers": {
"db-mcp": {
"command": "conda",
"args": [
"run", "-n", "db-mcp", "python",
"D:/path/to/db-mcp-server/server.py"
]
}
}
}
```
Replace the path with the absolute path of the project on your machine. On Windows, you can also use the Python interpreter path instead of `conda run`.
## MCP Tools
All tools return Markdown strings; query results are rendered as Markdown tables.
### Connection Management
| Tool | Description |
| ------------------ | ------------------ |
| `list_connections` | List all available connections and configurations (including remarks and constraints) |
| `test_connection` | Test if a specified connection is available |
### Schema Exploration
| Tool | Description |
| ---------------- | ------------------------------ |
| `list_databases` | List databases / Catalog |
| `list_schemas` | List schemas (⭐ mark default schema) |
| `list_tables` | List tables and views, support `table_pattern` fuzzy filtering |
| `describe_table` | View column name, type, nullable, default value, comment, primary key |
| `get_ddl` | Get table creation DDL |
### Data Query
| Tool | Description |
| -------------- | ----------------------------------------------------------- |
| `prepare_write` | Prepare write operation, generate `confirm_token` and SQL preview, **not executed**; must choose SQL with three options (execute/deny/modify) |
| `query` | Execute SQL; read-only default `readonly=true`; write operation requires `readonly=false` + `confirm_token` (obtained from `prepare_write`) |
| `sample_table` | Quickly sample N rows |
| `count_table` | Count table rows |
**Recommended Agent workflow:**
```
list_connections → test_connection → list_schemas → list_tables
→ describe_table → sample_table / query
```
## Database-specific Notes
### Oracle
- **19c**: In Admin's "Database" field, fill in **Service Name** (e.g., `ORCLPDB1`), use thin mode
- **11g**: Must enable thick mode, requires Oracle Instant Client installation
#### Oracle 11g Instant Client Configuration
Oracle 11g (12 and below) does not support python-oracledb's thin mode; must connect through thick mode.
**Download Instant Client**
Download the corresponding platform's Basic package from [Oracle's official website](https://www.oracle.com/database/technologies/instant-client/downloads.html), and extract it to the project's `lib/` directory:
#### Common errors (Linux)
1. **`DPY-3010: connections to this database server version are not supported by python-oracledb in thin mode`**
Oracle 11g does not support thin mode; thick mode not successfully initialized. Follow the steps to install Instant Client and configure `.env`.
2. Unrecognized files:
```bash
# DPI-1047: Cannot locate a 64-bitOracle Client library:"/home/dqyt/db-mcp-server/lib/instantclient_23_26/libclntsh.so:cannot open shared object file: No such file or directory".
# After extraction, may have version suffix, need to link to suffix-free
cd lib/instantclient_23_26
ln -sf libclntsh.so.23.1 libclntsh.so
ln -sf libocci.so.23.1 libocci.so
# Check if all dependencies are resolved
ldd lib/instantclient_*/libclntsh.so | grep "not found"
# If output libnnz.so => not found, etc., need to configure ldconfig:
echo "/path/to/lib/instantclient_23_26" > /etc/ld.so.conf.d/oracle-instantclient.conf
ldconfig
```
### Trino
- In Admin's "Database" field, fill in **Catalog** (e.g., `iceberg`, `hive`)
- `list_databases()` actually executes `SHOW CATALOGS`
- `default_schemas` first value as default schema
Security and| Item | Description |
| -------- | ----------------------------------------------------------- |
| Read-only by default | `query` defaults to `readonly=true`, only allows `SELECT` / `WITH`, and automatically appends LIMIT |
| LIMIT range | 100 rows, maximum 1000 (adjustable via `.env`) |
| Write operations | Requires `prepare_write` → user must choose oneexecute/deny/modify) → `query(readonly=false, confirm_token` |
| Write confirmation principle | Each `prepare` requires independent confirmation consent after SQL changes; token is bound to `connection_id +` Write confirmation switch | `DB_MCP_WRITE_CONFIRM_REQUIRED=false` can skip confirmation (recommended only for local debugging) |
| Token validity period | Defaults to 600 seconds_MCP_WRITE_CONFIRM_T reprepare_write` expiration |
| Authentication | UI and SSE transmission **have no authentication**, suitable only for private network deployment |
| Sensitive information | `.env` contains Bootstrap PG password, excluded`; connection passwords stored repository |
| Remark field Can write constraints (e.g.,) in connection remarks; Agent can view via `list_connections` |
---
## Production Deployment (system`deploy/` provides two systemd deployment modes, **choose one, do both** (will conflict on port 9999), see [`deploy/READMEdeploy/README.md) for details. Linux server `172.16.60.230`, prerequisites: deploy code, configure `.env`, execute `init.sql`, and build Admin frontend.
### Mode 1: Monolithic Mode (Recommended)
`scripts/start_all.py` starts MCP and Admin together: one command to start both, one command to stop both.
```bash
sudo cp deploy/db-mcp-all.service /etc/systemd/system/
# Edit service file for User, WorkingDirectory, and Python path
sudo systemctl daemon-reload
sudo systemctl enable --now db-mcp```
### Mode 2: SeparateAlternative)
MCP and Admin their own service, with fault isolation (Admin restart does not interrupt MCP long connections), linked by `Requires+PartOf`.
```bash
sudo cp deploy/db-mcp.service deploy/db-mcp-admin.service /etc/systemd/system/
sudo systemctl daemon-resudo systemctl enable --nowcp db-mcp-admin
```
### Firewall and Verification
```bash
sudo firewall-cmd --zone=public --add-port=9999/tcpmanent
sudo firewall-cmd --zone=public --add-port=9998/tcp --permanent
sudo firewall-cmd --reload
curl http://172.16.60.230:9999/sse
```
| Port | Purpose |
| --- | ------------ |
| 9999 | MCP SSE Service |
| 9998 | Admin Management Interface |
--- Extending New Database Types
MCP Config
Below is the configuration for this MCP Server. You can copy it directly to Cursor or other MCP clients.
mcp.json
Connection Info
You Might Also Like
No recommendations available