# ClickHouse MCP Server

An MCP (Model Context Protocol) server that gives AI agents safe, read-only access to a ClickHouse database. Connect it to Claude Desktop, the MCP Inspector, or any MCP-compatible client and let the model explore your tables, run queries, and inspect schemas — without risking writes or mutations.

## Tools

| Tool             | Description |
|------------------|-------------|
| `list_tables`    | Lists all tables in the database with engine type, row count, and size. |
| `run_query`      | Executes a read-only SQL query with validation, caching, and a 1000-row limit. |
| `describe_table` | Returns column names, types, defaults, and a 3-row sample for a table. |
| `aggregate`      | Builds and runs a time-windowed aggregation query without writing raw SQL. |
| `cache_stats`    | Shows query cache statistics. |
| `clear_cache`    | Clears all cached query results. |

## Setup

### Prerequisites

- Node.js 18+
- A ClickHouse instance (local Docker or [ClickHouse Cloud](https://clickhouse.cloud) free tier)

### Install

```bash
git clone https://github.com/sepfazeli/clickhouse-mcp-server.git
cd clickhouse-mcp-server
npm install
npm run build
```

### Configure

Copy `.env.example` to `.env` and fill in your connection details:

```bash
cp .env.example .env
```

```env
CLICKHOUSE_URL=http://localhost:8123
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=
CLICKHOUSE_DATABASE=default
```

For a local instance via Docker:

```bash
docker run -d -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server
```

### Run

**Stdio transport** (default — for Claude Desktop and local MCP clients):

```bash
npm run build && npm start

# Development
npm run dev
```

**HTTP transport** (for remote clients or multi-session use):

```bash
npm run start:http              # default port 3001
npm run start:http -- 8080      # custom port

# Development
npm run dev:http
```

The HTTP server exposes:
- `POST /mcp` — MCP Streamable HTTP endpoint (supports SSE streaming)
- `GET /mcp` — SSE stream for server-initiated notifications
- `GET /health` — health check

### Claude Desktop Configuration

Add to your Claude Desktop config (`~/Library/Application Support/Claude/claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "clickhouse": {
      "command": "node",
      "args": ["/absolute/path/to/clickhouse-mcp-server/dist/index.js"],
      "env": {
        "CLICKHOUSE_URL": "http://localhost:8123",
        "CLICKHOUSE_USER": "default",
        "CLICKHOUSE_PASSWORD": "",
        "CLICKHOUSE_DATABASE": "default"
      }
    }
  }
}
```

### Test with MCP Inspector

```bash
npx @modelcontextprotocol/inspector node dist/index.js
```

### Tests

```bash
npm test
```

51 unit tests covering query validation, auth scoping, and cache behavior.

## Auth Scoping

The server supports per-API-key permission scopes via the `MCP_AUTH_SCOPES` and `MCP_API_KEY` environment variables. This lets you restrict which tables, columns, and row limits are available per caller.

```env
MCP_API_KEY=analyst-key-1
MCP_AUTH_SCOPES={"analyst-key-1":{"allowedTables":["events","pageviews"],"maxRowLimit":500},"intern-key":{"deniedTables":["billing","secrets"],"allowedColumns":{"users":["id","name"]}}}
```

Scope options:
- `allowedTables` — whitelist of accessible tables (deny-by-default)
- `deniedTables` — blacklist of inaccessible tables (allow-by-default)
- `allowedColumns` — per-table column whitelist
- `maxRowLimit` — override the default 1000-row cap (can only go lower)

When no `MCP_AUTH_SCOPES` is set, the server runs in open mode with full read access.

## Query Caching

Identical queries are cached for 60 seconds by default (configurable via `CACHE_TTL_MS`). The cache holds up to 100 entries and uses FIFO eviction. Use the `cache_stats` and `clear_cache` tools to inspect and manage it.

## Observability

All tool calls, query executions, and errors are logged as structured JSON to stderr. Each log entry includes:

- `timestamp`, `level`, `event`
- `tool` name, `query` text (truncated to 500 chars)
- `durationMs`, `rowCount`, `cacheHit`
- `error` message on failures

Set `LOG_LEVEL` to `debug`, `info`, `warn`, or `error` (default: `info`).

Example log line:
```json
{"timestamp":"2025-01-15T10:30:00.000Z","level":"info","event":"query_exec","query":"SELECT count() FROM events","durationMs":42,"rowCount":1,"cacheHit":false}
```

## Design Decisions

**Read-only enforcement.** Queries are validated before execution by stripping comments, rejecting multi-statement queries (semicolons), and checking that the statement starts with `SELECT`, `WITH`, `SHOW`, `DESCRIBE`, `EXISTS`, or `EXPLAIN`. Dangerous DDL/DML keywords like `INSERT`, `DROP`, `ALTER` are caught when paired with their target keywords (e.g., `DROP TABLE`). This is defense-in-depth — ideally the ClickHouse user itself should also have read-only grants.

**Why CTEs are allowed.** `WITH` (Common Table Expressions) are essential for non-trivial analytical queries. Blocking them would cripple the tool for real ClickHouse workloads. The same validation that applies to `SELECT` applies to CTEs.

**Row limit: 1000 max, auto-appended.** If a query omits `LIMIT`, the server appends `LIMIT 1000`. If a query specifies a limit above 1000, it's rejected. This prevents agents from accidentally pulling massive result sets into context. The limit is deliberately conservative — large results aren't useful for an LLM anyway. Auth scopes can lower this per-key.

**Query timeout: 30 seconds.** Applied at both the client level (`request_timeout`) and the ClickHouse engine level (`max_execution_time`). Long-running queries are killed server-side rather than leaving connections hanging.

**Table name sanitization.** `describe_table` and `aggregate` strip non-alphanumeric/underscore characters from identifier parameters. A mismatch between the sanitized and original name is rejected outright rather than silently corrected.

**Cache TTL: 60 seconds.** Short enough that agents see reasonably fresh data, long enough to absorb the repeated identical queries that agents tend to issue (e.g., re-checking a count before and after an explanation).

**HTTP transport uses Streamable HTTP, not legacy SSE.** The MCP SDK's `StreamableHTTPServerTransport` supports both SSE streaming and direct HTTP responses per the latest MCP spec. The deprecated `SSEServerTransport` is not used.

## Known Limitations

- **Validation is pattern-based, not a real SQL parser.** The read-only check uses keyword matching after stripping comments. A sufficiently creative query could theoretically bypass it, which is why the ClickHouse user should also be restricted at the database level.
- **Results are buffered in memory.** For very large result sets (close to the 1000-row limit with wide rows), this could use significant memory. True streaming would require changes to how MCP tool results are structured.
- **Auth scoping is env-var-based.** For production multi-tenant use, a proper auth middleware with JWT or API key lookup would be more appropriate than a single JSON env var.
- **No query plan analysis.** The `aggregate` tool builds queries from parameters but doesn't analyze whether the resulting query will be efficient (e.g., whether the time column is indexed).

## License

MIT