# DB CLI MCP Server

[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![Node.js](https://img.shields.io/badge/Node.js-18%2B-green.svg)](https://nodejs.org/)
[![MCP](https://img.shields.io/badge/MCP-Compatible-blue.svg)](https://modelcontextprotocol.io/)

> **[繁體中文版 README](./README.zh-TW.md)**

A **Model Context Protocol (MCP) Server** that provides a unified, safe, AI-friendly interface for executing **read-only database queries** across multiple databases via CLI tools.

---

## Table of Contents

- [Features](#features)
- [Supported Databases](#supported-databases)
- [Installation](#installation)
- [Quick Start](#quick-start)
- [AI IDE & CLI Integration](#ai-ide--cli-integration)
  - [Claude Code](#claude-code)
  - [Codex CLI](#codex-cli)
  - [Cursor](#cursor)
  - [VS Code (Copilot)](#vs-code-copilot)
  - [GitHub Copilot CLI](#github-copilot-cli)
  - [Visual Studio 2026](#visual-studio-2026)
- [MCP Tools Reference](#mcp-tools-reference)
- [Connection Modes](#connection-modes)
- [Security](#security)
- [Configuration](#configuration)
- [Development](#development)
- [Architecture](#architecture)
- [License](#license)

---

## Features

- **Multi-database support** — SQL Server, MySQL, MariaDB, PostgreSQL, Oracle
- **Read-only enforcement** — Strict SELECT-only query execution with fail-closed validation
- **Zero-config startup** — Server starts even without config files or CLI tools installed
- **Auto-detection** — Scans project files (.env, appsettings.json, docker-compose.yml, etc.) for DB configs
- **Temporary connections** — Ephemeral, in-memory-only connections that never touch disk
- **CLI tool guidance** — Detects missing CLI tools and provides installation instructions
- **AI-native** — Built for seamless integration with AI IDEs and CLI tools via MCP protocol

---

## Supported Databases

| Database   | CLI Tool  | Default Port |
|------------|-----------|-------------|
| SQL Server | `sqlcmd`  | 1433        |
| MySQL      | `mysql`   | 3306        |
| MariaDB    | `mariadb` | 3306        |
| PostgreSQL | `psql`    | 5432        |
| Oracle     | `sqlplus` | 1521        |

---

## Installation

### Prerequisites

- **Node.js** 18 or later
- At least one database CLI tool installed (optional — server works without them)

### Install via npm

```bash
npm install -g @mcp/dbcli-server
```

### Install from source

```bash
git clone https://github.com/your-org/dbcli-mcp-server.git
cd dbcli-mcp-server
npm install
npm run build
```

---

## Quick Start

### 1. Run the server

```bash
# Via npx (no install needed)
npx @mcp/dbcli-server --stdio

# Or if installed globally
dbcli-mcp-server --stdio
```

### 2. Check system status

Use the `doctor` tool to verify CLI tools and configuration:

```json
{
  "serverVersion": "2.0",
  "cliStatus": [
    { "dbType": "mssql", "cli": "sqlcmd", "installed": true, "version": "17.10" },
    { "dbType": "mysql", "cli": "mysql", "installed": false }
  ],
  "issues": ["mysql not installed"]
}
```

### 3. Run a query

```json
// Input
{ "sql": "SELECT * FROM users LIMIT 10" }

// Output
{
  "success": true,
  "columns": ["id", "name", "email"],
  "rows": [
    { "id": "1", "name": "Alice", "email": "alice@example.com" }
  ],
  "rowCount": 1,
  "truncated": false
}
```

---

## AI IDE & CLI Integration

### Claude Code

[Claude Code](https://docs.anthropic.com/en/docs/claude-code) supports MCP servers natively.

#### Setup

Add the server to your Claude Code MCP configuration:

```bash
claude mcp add dbcli -- npx @mcp/dbcli-server --stdio
```

Or manually edit `~/.claude/claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "dbcli": {
      "command": "npx",
      "args": ["@mcp/dbcli-server", "--stdio"]
    }
  }
}
```

#### Usage

Once configured, you can ask Claude Code directly:

```
> Query the users table to show the first 10 records
> Check which database CLI tools are installed on my system
> Create a temporary connection to my local MySQL database
> Scan this project for database configurations
```

---

### Codex CLI

[Codex CLI](https://github.com/openai/codex) supports MCP protocol integration.

#### Setup

Add to your Codex CLI configuration file `~/.codex/config.json`:

```json
{
  "mcpServers": {
    "dbcli": {
      "command": "npx",
      "args": ["@mcp/dbcli-server", "--stdio"]
    }
  }
}
```

#### Usage

```bash
codex "Query the users table in my local SQL Server database"
codex "What database configurations exist in this project?"
```

---

### Cursor

[Cursor](https://cursor.com) supports MCP servers for AI-assisted development.

#### Setup

1. Open Cursor Settings: `Ctrl+Shift+J` (Windows/Linux) or `Cmd+Shift+J` (macOS)
2. Navigate to **MCP** section
3. Click **"Add new MCP server"**
4. Fill in the configuration:

```json
{
  "mcpServers": {
    "dbcli": {
      "command": "npx",
      "args": ["@mcp/dbcli-server", "--stdio"]
    }
  }
}
```

Or manually edit `.cursor/mcp.json` in your project root:

```json
{
  "mcpServers": {
    "dbcli": {
      "command": "npx",
      "args": ["@mcp/dbcli-server", "--stdio"]
    }
  }
}
```

#### Usage

In Cursor's AI chat or Composer, you can:

```
@dbcli Run SELECT * FROM products WHERE price > 100
@dbcli Show me the database schema for the orders table
@dbcli Create a temporary connection to postgres://localhost:5432/mydb
```

---

### VS Code (Copilot)

[VS Code](https://code.visualstudio.com/) with GitHub Copilot supports MCP servers (requires Copilot Chat).

#### Setup

1. Install the **GitHub Copilot** and **GitHub Copilot Chat** extensions
2. Open VS Code Settings (`Ctrl+,`)
3. Search for `mcp` in settings
4. Edit `settings.json` and add:

```json
{
  "github.copilot.chat.mcp.servers": {
    "dbcli": {
      "command": "npx",
      "args": ["@mcp/dbcli-server", "--stdio"]
    }
  }
}
```

Or create `.vscode/mcp.json` in your project:

```json
{
  "servers": {
    "dbcli": {
      "command": "npx",
      "args": ["@mcp/dbcli-server", "--stdio"]
    }
  }
}
```

#### Usage

In Copilot Chat panel:

```
@dbcli What database connections are configured in this project?
@dbcli Run a health check on my database setup
@dbcli SELECT COUNT(*) FROM orders WHERE status = 'pending'
```

---

### GitHub Copilot CLI

[GitHub Copilot CLI](https://docs.github.com/en/copilot/github-copilot-in-the-cli) can leverage MCP servers.

#### Setup

Configure via `~/.config/github-copilot/config.json`:

```json
{
  "mcpServers": {
    "dbcli": {
      "command": "npx",
      "args": ["@mcp/dbcli-server", "--stdio"]
    }
  }
}
```

#### Usage

```bash
gh copilot explain "How to query my database using the dbcli MCP server"
```

---

### Visual Studio 2026

[Visual Studio 2026](https://visualstudio.microsoft.com/) supports MCP servers through its AI integration.

#### Setup

1. Open **Tools** → **Options** → **GitHub Copilot** → **MCP Servers**
2. Click **"Add Server"**
3. Configure:
   - **Name**: `dbcli`
   - **Command**: `npx`
   - **Arguments**: `@mcp/dbcli-server --stdio`

Or edit `.vs/mcp.json` in your solution root:

```json
{
  "servers": {
    "dbcli": {
      "command": "npx",
      "args": ["@mcp/dbcli-server", "--stdio"]
    }
  }
}
```

#### Usage

In the Copilot Chat window:

```
@dbcli Query the Customers table for records from this month
@dbcli Check if sqlcmd is installed and show install instructions if not
```

---

## MCP Tools Reference

### `doctor()`

Returns system diagnostics including server version, CLI tool installation status, and detected issues.

**Parameters**: None

**Response**:
```json
{
  "serverVersion": "2.0",
  "cliStatus": [
    { "dbType": "mssql", "cli": "sqlcmd", "installed": true, "version": "17.10", "path": "/usr/bin/sqlcmd" }
  ],
  "issues": []
}
```

---

### `scanProjectDbConfigs()`

Scans current project directory for database configuration in common config files.

**Scanned files**: `.env`, `.env.*`, `appsettings.json`, `application.yml`, `application.properties`, `docker-compose.yml`

**Parameters**: None

**Response**:
```json
[
  {
    "dbType": "mssql",
    "host": "localhost",
    "port": 1433,
    "database": "ExampleDB",
    "source": "appsettings.json"
  }
]
```

---

### `initConfig()`

Creates or updates a persistent configuration profile in `.mcp/dbcli.config.json`.

**Parameters**:

| Parameter       | Type   | Required | Description                    |
|----------------|--------|----------|--------------------------------|
| profileName    | string | Yes      | Profile name (e.g., "dev")     |
| dbType         | string | Yes      | Database type                  |
| host           | string | Yes      | Database host                  |
| database       | string | Yes      | Database name                  |
| port           | number | No       | Database port                  |
| username       | string | No       | Database username              |
| password       | string | No       | Database password              |
| timeoutSeconds | number | No       | Query timeout (default: 30)    |
| maxRows        | number | No       | Max rows returned (default: 500) |

---

### `setActiveProfile()`

Switches the active configuration profile.

**Parameters**:

| Parameter    | Type   | Required | Description             |
|-------------|--------|----------|-------------------------|
| profileName | string | Yes      | Profile name to activate |

---

### `getInstallInstructions()`

Returns platform-specific installation instructions for a database CLI tool.

**Parameters**:

| Parameter | Type   | Required | Description   |
|----------|--------|----------|---------------|
| dbType   | string | Yes      | Database type |

**Response**:
```json
{
  "dbType": "mssql",
  "cli": "sqlcmd",
  "installed": false,
  "instructions": {
    "windows": "winget install Microsoft.SQLCMD",
    "linux": "sudo apt install mssql-tools",
    "macos": "brew install mssql-tools"
  }
}
```

---

### `createTemporaryConnection()`

Creates an in-memory-only database connection that is never persisted to disk.

**Parameters**:

| Parameter | Type   | Required | Description       |
|----------|--------|----------|-------------------|
| dbType   | string | Yes      | Database type     |
| host     | string | Yes      | Database host     |
| database | string | Yes      | Database name     |
| port     | number | No       | Database port     |
| username | string | No       | Database username |
| password | string | No       | Database password |

**Response**:
```json
{
  "id": "uuid-string",
  "type": "temporary",
  "dbType": "mssql",
  "cli": "sqlcmd",
  "connection": {
    "host": "localhost",
    "database": "TestDB",
    "password": "***"
  }
}
```

---

### `runSelectQuery()`

Executes a read-only SQL query through the appropriate database CLI tool.

**Parameters**:

| Parameter    | Type   | Required | Description                              |
|-------------|--------|----------|------------------------------------------|
| sql         | string | Yes      | The SELECT SQL query                     |
| connectionId | string | No      | Temporary connection ID (uses active profile if omitted) |

**Allowed SQL**:
- `SELECT ...`
- `WITH ... SELECT ...` (CTE)
- `EXPLAIN SELECT ...`

**Response**:
```json
{
  "success": true,
  "columns": ["id", "name"],
  "rows": [{ "id": "1", "name": "Alice" }],
  "rowCount": 1,
  "truncated": false
}
```

---

## Connection Modes

| Mode                 | Description                                      | Persistence |
|---------------------|--------------------------------------------------|-------------|
| **Project Detection** | Auto-detects connection info from project files  | None        |
| **Config Profile**    | Stored in `.mcp/dbcli.config.json`              | Disk        |
| **Temporary Connection** | Runtime-only, created via `createTemporaryConnection()` | Memory only |

### Config File Structure

```json
{
  "version": 1,
  "activeProfile": "dev",
  "profiles": {
    "dev": {
      "dbType": "mssql",
      "cli": "sqlcmd",
      "connection": {
        "host": "localhost",
        "port": 1433,
        "database": "ExampleDb",
        "username": "app",
        "password": "${DB_PASSWORD}"
      },
      "limits": {
        "timeoutSeconds": 30,
        "maxRows": 500
      }
    }
  }
}
```

---

## Security

### Core Security Rules

1. **SELECT-only queries** — All DML/DDL operations are strictly forbidden
2. **Fail-closed validation** — If the SQL parser cannot determine the query type, it is rejected
3. **No credential exposure** — Passwords are masked in all API responses
4. **Result size limits** — `maxRows` prevents excessive data retrieval
5. **Query timeouts** — `timeoutSeconds` kills long-running queries
6. **No shell injection** — CLI execution uses `execFile` (not `exec`), preventing shell injection
7. **Multi-statement rejection** — Semicolon-separated multi-statement queries are blocked
8. **Temporary connection isolation** — Ephemeral connections are never written to disk

### Forbidden SQL Operations

`INSERT`, `UPDATE`, `DELETE`, `MERGE`, `CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `EXEC`, `EXECUTE`, `CALL`, `GRANT`, `REVOKE`, `SELECT INTO`

---

## Development

```bash
# Install dependencies
npm install

# Build
npm run build

# Run tests
npm test

# Run tests in watch mode
npm run test:watch
```

### Project Structure

```
src/
├── index.ts                  # MCP Server entry point + tool definitions
├── types.ts                  # Core TypeScript types
├── server/
│   └── ConnectionStore.ts    # Temporary connection memory store
├── validator/
│   └── SqlValidator.ts       # SQL security validation
├── cli/
│   ├── CliManager.ts         # CLI detection + install instructions
│   └── CliExecutor.ts        # Safe CLI execution
├── config/
│   └── ConfigManager.ts      # Configuration file management
├── scanner/
│   └── ProjectScanner.ts     # Project DB config scanning
└── formatter/
    └── ResultFormatter.ts    # Query result formatting
```

---

## Architecture

```
AI Client (Claude Code / Cursor / VS Code / Codex CLI / ...)
    │
    │  MCP Protocol (JSON-RPC over stdio)
    ▼
┌──────────────────────────────┐
│       MCP Server Core        │
│   (Tool Routing & Lifecycle) │
└──────────┬───────────────────┘
           │
     ┌─────┼─────┬──────────┬──────────┬──────────┐
     ▼     ▼     ▼          ▼          ▼          ▼
  Project  CLI   Config     SQL       CLI       Result
  Scanner  Mgr   Manager  Validator  Executor  Formatter
```

---

## License

MIT License. See [LICENSE](./LICENSE) for details.