# mcp-data-source

> Multi-datasource MCP Server — connect AI assistants to 6 database types with dynamic configuration and secure credential storage.

[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](LICENSE)

📖 **完整中文文档**：[docs/user-guide.md](docs/user-guide.md)

---

## Features

| Capability | Description |
|------------|-------------|
| **6 Database Types** | MySQL, PostgreSQL, ClickHouse, MongoDB, SQLite, Huawei DWS |
| **Dynamic Configuration** | Add/remove data sources at runtime via MCP tools — no restart needed |
| **Secure Storage** | Passwords encrypted with AES-256-GCM, stored in local SQLite |
| **Read-Only by Default** | Write operations require explicit `readOnly: false` |
| **Schema Discovery** | Auto-detect table structure to assist natural-language-to-SQL |
| **Pagination** | Built-in `limit`/`offset` pagination with `hasMore` indicator |
| **Query Timeout** | Per-database timeouts prevent long-running queries from blocking |
| **SSL/TLS** | Encrypted connections with client certificate support |
| **Offline CLI** | `mcp-ds` for managing data sources — passwords never leave your machine |

## Architecture

```
MCP Client (Claude Code / Claude Desktop / OpenCode)
    │ stdio
    ▼
┌─────────────────────────────────────┐
│         mcp-data-source             │
│  ┌──────────┐  ┌──────────────────┐ │
│  │ 9 Tools  │  │  Connectors (6)  │ │
│  │ config/* │  │ mysql / pg / dws │ │
│  │ query/*  │  │ ch / mongo / sql │ │
│  └──────────┘  └──────────────────┘ │
│         │              │             │
│    ~/.mcp-data-source/   Remote DBs  │
│    config.db (encrypted)            │
└─────────────────────────────────────┘
```

## Installation

```bash
cd mcp-data-source
npm install
npm run build
npm link            # makes mcp-ds globally available
```

Or for distribution:

```bash
npm pack            # → mcp-data-source-1.0.0.tgz
npm install -g mcp-data-source-1.0.0.tgz
```

**Requirements:** Node.js ≥ 18.x, npm ≥ 9.x.

## MCP Client Configuration

Configuration is a two-step process: **① Set encryption key** (system env var) → **② Register the server** (MCP client config).

> ⚠️ `MCP_DATA_SOURCE_KEY` is a **system environment variable** read via `process.env`. It does NOT go in the MCP config file. The config only carries `MCP_LOG_LEVEL` (optional).

### Step 1: Set Encryption Key (system env var)

First, generate and set `MCP_DATA_SOURCE_KEY` as a system environment variable (see [Generate Encryption Key](#generate-encryption-key) below).

**macOS / Linux** — add to `~/.zshrc` or `~/.bashrc`:
```bash
export MCP_DATA_SOURCE_KEY=your-64-char-hex-key
```

**Windows** — set in System Environment Variables, or PowerShell:
```powershell
[Environment]::SetEnvironmentVariable("MCP_DATA_SOURCE_KEY", "your-64-char-hex-key", "User")
```

Restart your terminal / MCP client after setting.

### Step 2: MCP Client Config

> ⚠️ **Configuration format differs between MCP clients.** Copy-pasting between them won't work.

### Format Comparison

| Setting | Claude Code | Claude Desktop | OpenCode |
|---------|-------------|----------------|----------|
| Config file | `.mcp.json` or `settings.json` | `claude_desktop_config.json` | `opencode.json` |
| Top-level key | `mcpServers` | `mcpServers` | **`mcp`** |
| Env var key | `env` | `env` | **`environment`** |
| Extra fields | — | — | `"type": "local"`, `"enabled": true` |

### Claude Code

```json
{
  "mcpServers": {
    "data-source": {
      "command": "mcp-data-source",
      "env": {
        "MCP_LOG_LEVEL": "info"
      }
    }
  }
}
```

### Claude Desktop

Config file: `~/Library/Application Support/Claude/claude_desktop_config.json` (macOS) or `%APPDATA%\Claude\claude_desktop_config.json` (Windows).

```json
{
  "mcpServers": {
    "data-source": {
      "command": "node",
      "args": ["/absolute/path/to/dist/server.js"],
      "env": {
        "MCP_LOG_LEVEL": "info"
      }
    }
  }
}
```

### OpenCode

Config file: `opencode.json` in project root (**not** `.mcp.json`).

```jsonc
{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "data-source": {
      "type": "local",
      "command": ["mcp-data-source"],
      "environment": {
        "MCP_LOG_LEVEL": "info"
      },
      "enabled": true
    }
  }
}
```

> Common OpenCode pitfalls: using `mcpServers` instead of `mcp`, `env` instead of `environment`, or forgetting `"enabled": true`.

## CLI Tool (`mcp-ds`)

Manage data sources offline — passwords never go through the LLM.

```bash
# Add a data source (interactive password prompt — secure)
mcp-ds add mysql --name prod --host 10.0.0.1 --port 3306 --username admin --password

# Add via environment variable (for scripting)
DB_PASS=xxx mcp-ds add mysql --name prod --host 10.0.0.1 --username admin --password-env DB_PASS

# SQLite
mcp-ds add sqlite --name local --file-path /path/to/database.db

# ClickHouse with HTTPS
mcp-ds add clickhouse --name ch --host 10.0.0.1 --username admin --password --protocol https

# List, test, delete
mcp-ds list
mcp-ds test prod
mcp-ds delete prod
```

### Full `add` Options

| Option | Required | Description |
|--------|:--------:|-------------|
| `--name` | ✅ | Unique data source name |
| `--host` | * | Database host (not for SQLite) |
| `--port` | | Port (uses default for type) |
| `--database` | | Database name |
| `--username` | * | Username (not for SQLite) |
| `--password` | | Interactive prompt (secure, recommended) |
| `--password-env <var>` | | Read password from env variable |
| `--file-path` | * | SQLite file path (SQLite only) |
| `--protocol` | | ClickHouse: `http` or `https` |
| `--ssl` | | Enable SSL/TLS |
| `--ssl-ca` | | CA certificate content |
| `--ssl-cert` | | Client certificate content |
| `--ssl-key` | | Client private key content |

## Generate Encryption Key

```bash
node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"
# Or: openssl rand -hex 32
```

Set the generated key as a **system environment variable** (not in MCP config):

**macOS/Linux:** `export MCP_DATA_SOURCE_KEY=...` in `~/.zshrc` or `~/.bashrc`
**Windows:** System Environment Variables, or `[Environment]::SetEnvironmentVariable("MCP_DATA_SOURCE_KEY", "...", "User")` in PowerShell

**Keep it safe** — if lost, stored passwords cannot be decrypted. Restart your MCP client after setting.

## Supported Databases

| Database | `type` | Required Fields | Default Port |
|----------|--------|----------------|-------------|
| MySQL | `mysql` | host, username, password | 3306 |
| PostgreSQL | `postgresql` | host, username, password | 5432 |
| Huawei DWS | `dws` | host, username, password | 5432 |
| ClickHouse | `clickhouse` | host, username, password | 8123 |
| MongoDB | `mongodb` | host, username, password | 27017 |
| SQLite | `sqlite` | filePath | — |

## MCP Tools Overview

### Configuration (6 tools)

| Tool | Description |
|------|-------------|
| `get_connection_params` | Get required fields for a database type |
| `add_data_source` | Add new data source (credentials encrypted) |
| `list_data_sources` | List all configured sources (passwords hidden) |
| `update_data_source` | Modify an existing data source |
| `delete_data_source` | Remove a data source |
| `test_connection` | Verify connectivity to a data source |

### Query (3 tools)

| Tool | Description |
|------|-------------|
| `query_execute` | Execute SQL or MongoDB JSON query |
| `get_schema` | Discover table structure |
| `prepare_query` | Get schema context for natural language questions |

## Quick Usage Examples

```
# Add and explore
User: Add a MySQL data source named "prod" at 10.0.0.1:3306, user admin, password secret123
User: Test the connection
User: Show me the tables in prod

# Natural language query
User: How many orders were placed last month in the analytics database?
→ AI auto-discovers schema, constructs SQL, and returns results

# Pagination
User: Query users table, 50 per page
User: Next page

# Write operations (requires explicit authorization)
User: Insert a new user record, allow writes
```

## Environment Variables

| Variable | Required | Default | How to set | Description |
|----------|:--------:|---------|------------|-------------|
| `MCP_DATA_SOURCE_KEY` | **Yes** | — | **System env var** | AES-256-GCM encryption key (64 hex chars) |
| `MCP_LOG_LEVEL` | No | `info` | MCP config `env`/`environment` | Log level: `debug` / `info` / `warn` / `error` |

> `MCP_DATA_SOURCE_KEY` is read from the system environment by `process.env`. `MCP_LOG_LEVEL` can be injected via the MCP client config.

## Security

- **AES-256-GCM** authenticated encryption with random IV per operation
- Passwords stored encrypted in `~/.mcp-data-source/config.db`
- `list_data_sources` never returns password fields
- Default `readOnly: true` blocks all write operations
- Each query uses an isolated connection, closed immediately after use
- Connection pooling (max 10) with automatic cleanup for MySQL/PostgreSQL

## Troubleshooting

| Issue | Check |
|-------|-------|
| Server won't start | Run `npm install && npm run build`, verify `dist/server.js` exists |
| Encryption key not configured | Set `MCP_DATA_SOURCE_KEY` as a system environment variable (not in MCP config). Verify with `echo $MCP_DATA_SOURCE_KEY` |
| OpenCode `/mcp` shows empty | Use `opencode.json` (not `.mcp.json`), key `mcp` (not `mcpServers`), `environment` (not `env`), and `"enabled": true` |
| Connection test fails | Check network/VPN, port, credentials, remote access enabled |
| Write operation rejected | Set `readOnly: false` in `query_execute` |
| Query timeout | Optimize SQL, reduce `limit`, or add indexes |

For detailed troubleshooting and advanced usage, see [docs/user-guide.md](docs/user-guide.md).

## License

MIT