mcp-clickhouse

mcp-clickhouse

A Model Context Protocol server for ClickHouse. It lets an MCP-capable client (Claude Desktop, Claude Code, etc.) explore schemas, run analytical queries, and manage the database — with behaviour controlled entirely by flags.

The security model is statement-aware: every SQL statement is classified as read, write, or destructive, and gated against the current access mode. Read-only mode additionally runs queries under ClickHouse's own readonly=1 setting.

Features

• Exploration & monitoring — databases, tables, columns, SHOW CREATE, table stats (parts/rows/bytes), running queries, server metrics, cluster topology.
• Read queries — a query tool that only accepts read statements, capped at CLICKHOUSE_MAX_ROWS.
• Management — an execute tool for INSERT/CREATE/ALTER (read-write) and DROP/TRUNCATE/DELETE (admin), each gated by classification.
• Access modes — read-only → read-write → admin, layered so a mode never exposes statements above its level.
• Security flags — database allowlist, protected databases, destructive gating, row cap, dry-run, and JSON audit logging (see below).

Security model

Concern	Flag	Default	Effect
What can the server do?	CLICKHOUSE_MODE	read-only	read-only exposes read tools only (and refuses non-SELECT in query); read-write adds execute for writes; admin allows destructive statements.
Which databases are in scope?	CLICKHOUSE_DATABASE_ALLOWLIST	(all)	When set, operations on other databases are refused.
Which databases are read-only forever?	CLICKHOUSE_PROTECTED_DATABASES	system,information_schema	Readable, never mutable.
Can it run destructive SQL?	CLICKHOUSE_ALLOW_DELETE	false	DROP/TRUNCATE/DELETE/… need this and admin mode.
Result size cap	CLICKHOUSE_MAX_ROWS	1000	Hard cap on rows returned to the model.
Preview without executing	CLICKHOUSE_DRY_RUN	false	Write/destructive statements validate + log intent, then return.
Audit trail	CLICKHOUSE_AUDIT_LOG	true	Emits a JSON line to stderr per guarded operation.
Interactive confirmation	(automatic)	—	Destructive & high-impact actions prompt the human to approve via MCP elicitation before running; clients without elicitation fall back to the *_ALLOW_* gate.

Statement classification lives in src/sql.ts and is fail-safe: ALTER … DELETE/UPDATE counts as destructive, and anything unparseable is treated as destructive.

Tools

Read (read-only+): list_databases, list_tables, describe_table, show_create_table, table_stats, running_queries, server_metrics, cluster_info, query

Write/Admin (read-write+): execute — runs a single statement after classifying it; writes need read-write mode, destructive statements need admin mode + CLICKHOUSE_ALLOW_DELETE.

Quickstart — add to your agent

Published on npm as @dockndevai/mcp-clickhouse. No clone or build needed — your MCP client runs it on demand with npx. Start in read-only mode; see .env.example for every variable and docs/CLIENTS.md for the full per-client guide.

Claude Code (CLI)

claude mcp add clickhouse -e CLICKHOUSE_URL="http://localhost:8123" -e CLICKHOUSE_USER="default" -e CLICKHOUSE_MODE="read-only" -- npx -y @dockndevai/mcp-clickhouse

Claude Desktop · Cursor · Windsurf — same block in claude_desktop_config.json, .cursor/mcp.json, or ~/.codeium/windsurf/mcp_config.json:

{
  "mcpServers": {
    "clickhouse": {
      "command": "npx",
      "args": [
        "-y",
        "@dockndevai/mcp-clickhouse"
      ],
      "env": {
        "CLICKHOUSE_URL": "http://localhost:8123",
        "CLICKHOUSE_USER": "default",
        "CLICKHOUSE_MODE": "read-only"
      }
    }
  }
}

OpenAI Codex CLI — in ~/.codex/config.toml:

[mcp_servers.clickhouse]
command = "npx"
args = ["-y", "@dockndevai/mcp-clickhouse"]
env = { CLICKHOUSE_URL = "http://localhost:8123", CLICKHOUSE_USER = "default", CLICKHOUSE_MODE = "read-only" }

VS Code (GitHub Copilot, Agent mode) — in .vscode/mcp.json:

{
  "servers": {
    "clickhouse": {
      "type": "stdio",
      "command": "npx",
      "args": [
        "-y",
        "@dockndevai/mcp-clickhouse"
      ],
      "env": {
        "CLICKHOUSE_URL": "http://localhost:8123",
        "CLICKHOUSE_USER": "default",
        "CLICKHOUSE_MODE": "read-only"
      }
    }
  }
}

Example prompts

• "What are the biggest tables in the analytics database?"
• "Show me the schema for events and run a query for daily counts this week."
• "Which queries are currently running and using the most memory?"

Run from source (development)

Prefer the published package above. To run from a clone:

npm install
npm run build
node dist/index.js   # with the environment variables set

Develop

npm run dev
npm test          # SQL classification + security policy (30 tests)
npm run typecheck

Publishing

This server ships a server.json for the official MCP registry and an mcpName for npm ownership validation. See PUBLISHING.md for publishing to npm and listing on the MCP registry, Smithery, Glama, Cursor, and PulseMCP.

License

MIT