# clickhouse-mcp

A **read-only** [Model Context Protocol](https://modelcontextprotocol.io) server for
[ClickHouse](https://clickhouse.com). It gives an AI agent a small set of tools to
explore a ClickHouse instance and **investigate observability data** (logs, metrics,
traces) without being able to write or mutate anything.

Built as a reference for wiring agents onto a petabyte-scale analytics platform: the
agent discovers the schema, samples data, and runs guarded read-only SQL to answer
questions like "why did p99 latency spike at 02:14?".

## Tools

| Tool | What it does |
|------|--------------|
| `list_databases` | `SHOW DATABASES` |
| `list_tables` | `SHOW TABLES FROM <db>` |
| `describe_table` | `DESCRIBE TABLE <db>.<table>` |
| `show_create_table` | `SHOW CREATE TABLE` (schema + engine + settings) |
| `sample_rows` | `SELECT * FROM <db>.<table> LIMIT n` (peek at the shape) |
| `query` | Arbitrary **read-only** SQL (SELECT/WITH/SHOW/DESCRIBE/EXPLAIN) |

## Safety

- **Verb guard**: only `SELECT / WITH / SHOW / DESCRIBE / EXPLAIN / EXISTS` are allowed;
  write/DDL verbs (`INSERT`, `CREATE`, `DROP`, `ALTER`, `TRUNCATE`, …) are rejected.
- **Limits**: every query sets `max_result_rows` and `max_execution_time`, and the rows
  returned to the model are capped (with a truncation note) to protect its context window.
- **Defence in depth**: still point this at a **read-only ClickHouse user**. The app-level
  guard is a first line, not the only one.

## Prerequisites

- Node.js 20+
- Any ClickHouse instance. Easiest is local docker (no setup, includes `system.*`
  observability tables to play with):

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

## Install

```bash
git clone <your-fork-url> clickhouse-mcp && cd clickhouse-mcp
npm install
cp .env.example .env        # then edit if your ClickHouse needs auth
```

## Verify it talks to ClickHouse

```bash
npm run self-test
# Connecting to http://localhost:8123
# databases: [ 'default', 'system', 'INFORMATION_SCHEMA', ... ]
# version: [ { v: '25.x.x.x' } ]
# OK
```

## Run as an MCP server (stdio)

```bash
npm start
```

## Use it from a client

**Claude Desktop** (`claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "clickhouse": {
      "command": "npx",
      "args": ["tsx", "/absolute/path/to/clickhouse-mcp/src/index.ts"],
      "env": {
        "CLICKHOUSE_URL": "http://localhost:8123",
        "CLICKHOUSE_USERNAME": "default",
        "CLICKHOUSE_PASSWORD": ""
      }
    }
  }
}
```

**Claude Code**:

```bash
claude mcp add clickhouse -e CLICKHOUSE_URL=http://localhost:8123 -- npx tsx /absolute/path/to/clickhouse-mcp/src/index.ts
```

**MCP Inspector** (great for debugging the tools directly):

```bash
npx @modelcontextprotocol/inspector npx tsx src/index.ts
```

Then ask an agent: *"What tables are in the system database? Show recent slow queries
from system.query_log grouped by minute."*

## License

MIT