MCP ClickHouse Tool

A read-only-by-default Model Context Protocol (MCP) server for ClickHouse that provides schema discovery, read-only queries, execution-plan analysis, opt-in writes, and profile-based access to multiple servers from a single toolset deployment.

Read-only is enforced by the engine, not by SQL text matching: the query tools' clients carry ClickHouse's readonly=1, so writes, external table functions and query-level SETTINGS are refused by the server being queried. Writes live behind a separate tool that is not registered at all until a profile asks for it.

Requirements: Python 3.13+, a running ClickHouse instance, and connection details via environment variables or a config file.

Quick start

Set a DSN and run the server with MCP Inspector:

# Option 1: Run directly with uvx (no clone needed)
export MCP_CLICKHOUSE_DSN="http://default:@localhost:8123/default"
npx -y @modelcontextprotocol/inspector@latest uvx mcp-clickhousex

# Option 2: Run from source (clone repo, then)
export MCP_CLICKHOUSE_DSN="http://default:@localhost:8123/default"
npx -y @modelcontextprotocol/inspector@latest uv run mcp-clickhousex

Configuration

A profile is one ClickHouse connection: a DSN plus the row and timeout caps that apply to it. A profile named default always exists; every tool takes an optional profile to reach another, and list_profiles reports what is configured.

Settings come from three sources, merged field by field, later winning:

1. the user-scoped config.json — any number of profiles;
2. MCP_CLICKHOUSE_PROFILES_<NAME>_<FIELD> environment variables — any number of profiles;
3. flat MCP_CLICKHOUSE_<FIELD> environment variables — the default profile only.

Because the merge is per field rather than per profile, a config.json can carry the full set while a flat MCP_CLICKHOUSE_DSN repoints the default profile at a local server, leaving its other fields intact. With none of the three present, default falls back to http://default:@localhost:8123/default.

Each setting has one field name, spelled three ways — MCP_CLICKHOUSE_<FIELD>, MCP_CLICKHOUSE_PROFILES_<NAME>_<FIELD>, or the field lowercased as a JSON key:

Caps are per profile. A value above its ceiling is clamped at startup; a value that is not an integer falls back to the default, and a value that is not a boolean leaves ALLOW_WRITE off.

Single connection: flat environment variables are the shortest path.

# Connection DSN.
export MCP_CLICKHOUSE_DSN="http://user:password@host:8123/database"

# Optional description for the default profile (tooling/AI discovery).
export MCP_CLICKHOUSE_DESCRIPTION="Primary cluster"

# Optional caps, defaults shown.
export MCP_CLICKHOUSE_QUERY_MAX_ROWS="500"
export MCP_CLICKHOUSE_QUERY_COMMAND_TIMEOUT_SECONDS="30"
export MCP_CLICKHOUSE_SNAPSHOT_MAX_ROWS="10000"
export MCP_CLICKHOUSE_SNAPSHOT_COMMAND_TIMEOUT_SECONDS="120"

# Optional write access, off by default; also controls whether run_command
# is advertised at all.
export MCP_CLICKHOUSE_ALLOW_WRITE="false"
export MCP_CLICKHOUSE_WRITE_COMMAND_TIMEOUT_SECONDS="60"

Multiple connections: use the user-scoped config.json, which keeps credentials out of the host's process environment.

• Unix-like: ~/.config/mcp-clickhousex/config.json
• Windows: %USERPROFILE%\.config\mcp-clickhousex\config.json

{
  "profiles": {
    "default": {
      "dsn": "http://default:@localhost:8123/default",
      "description": "Primary",
      "query_max_rows": 500,
      "query_command_timeout_seconds": 60,
      "snapshot_max_rows": 10000,
      "snapshot_command_timeout_seconds": 120
    },
    "warehouse": {
      "dsn": "http://user:pass@warehouse:8123/analytics",
      "description": "Warehouse"
    },
    "writer": {
      "dsn": "http://etl:pass@warehouse:8123/analytics",
      "description": "Warehouse, write-enabled",
      "allow_write": true,
      "write_command_timeout_seconds": 120
    }
  }
}

Nothing stops one profile from both reading and writing, but a separate write-enabled profile is the shape worth copying: it gives the writes their own DSN, so the credentials behind them can be scoped to what they actually need while the read profiles stay on a login whose grants stop at SELECT.

Profile names are case-insensitive and must be alphanumeric — no underscores or hyphens, since the structured env form splits on _ (MCP_CLICKHOUSE_PROFILES_WAREHOUSE_DSN is profile warehouse, field DSN). A name that breaks the rule is skipped, as is a config.json that is missing, unreadable, or not shaped {"profiles": {…}}; the server starts on whatever sources remain rather than failing.

DSN syntax: scheme://user:password@host:port/database. An https:// or clickhouses:// scheme enables TLS, and query-string parameters reach the driver (?connect_timeout=10) — except readonly, which the server always applies last, from the profile's ALLOW_WRITE.

URL-reserved characters in the username or password must be percent-encoded — # → %23, ? → %3F, / → %2F, @ → %40, % → %25. Username admin@org with password p#ss? becomes http://admin%40org:p%23ss%3F@host:8123/database.

Tools and resources

All tools accept an optional profile; when omitted, the default profile is used.

Tools

• types — EXPLAIN variants: plan (indexes), pipeline, syntax. Defaults to plan and pipeline.
• parameters — Named parameters for driver placeholders, %(name)s or {name:Type}.
• database — Session default database for unqualified names; otherwise qualify as db.table.

Catalog discovery has no dedicated tool: list databases, tables and columns — and read sizes (total_rows, total_bytes) and keys (primary_key, sorting_key, partition_key) — with run_query over system.databases, system.tables and system.columns, which take ordinary WHERE predicates where SHOW takes only LIKE. SHOW earns its place for DDL a listing cannot give you — SHOW CREATE TABLE/VIEW/DICTIONARY for codecs, TTLs and the full column list.

Results are RFC 4180 CSV: the first row is the header, the rest are data. NULL is written as \N, ClickHouse's own CSV null representation, so it stays distinct from the empty string.

A plan's Indexes section is not authoritative about a table's keys: it names only the key columns the query used, so a query that skips the leading key column reports a shorter key than the table has. Confirm from system.tables, which answers in a few dozen tokens where SHOW CREATE TABLE spends several hundred to say the same thing.

The row caps that applied to a call arrive with its result as truncated and row_limit.

Resources

Security

Every client this server opens carries ClickHouse's own readonly=1, so the engine — not just the server's SQL checks — refuses:

• writes of any kind (INSERT, DDL, ALTER … UPDATE, SYSTEM, GRANT);
• the external table functions url(), s3(), remote(), mysql() and friends, so a query cannot reach a host outside the configured profile;
• query-level SETTINGS, so the row and time caps cannot be raised by the SQL an agent supplies, and INTO OUTFILE is refused.

readonly=2 is deliberately not used: it permits SETTINGS changes, which would make those caps advisory. The tradeoff is that benign per-query tuning (SETTINGS max_threads = …) is refused too.

On top of that, run_query accepts SELECT / WITH … SELECT / SHOW and analyze_query only the first two, one statement per call. Interactive queries enforce a tight row cap (default 500, hard ceiling 1 000); for larger extracts use snapshot=true (default 10 000, hard ceiling 50 000).

Writes are opt-in, and invisible until then. run_command runs on a client carrying readonly=0, so it executes arbitrary DDL and DML — and, with readonly lifted, the external table functions come back too. Unless at least one configured profile sets ALLOW_WRITE (default false), the tool is not registered at all: it never appears in tools/list, so a read-only deployment spends no context on it and offers no write surface an agent could be talked into. Once any profile opts in, the tool is advertised server-wide and is still refused at call time on profiles that remain locked; list_profiles reports allow_write per profile so an agent can pick a writable one.

ALLOW_WRITE is a soft, application-level guard, not a security boundary — it constrains this server, not the database. For a genuine read-only guarantee, connect with a login whose ClickHouse grants stop at SELECT, and keep write-enabled profiles pointed at credentials scoped to only what they need. run_command carries destructive and openWorld tool annotations so hosts can gate it behind confirmation, but honoring those annotations is the host's choice. ClickHouse has no transaction to roll back in here: a statement that lands, stays.

Use environment variables or the config file for connection credentials — never commit secrets.

MCP host examples

Snippets use uvx mcp-clickhousex (no clone required; ensure uv is on your PATH). Replace connection details as needed; the env block is unnecessary when the DSN already comes from config.json or the environment.

Claude Code and Cursor read the same mcpServers shape:

{
  "mcpServers": {
    "clickhouse": {
      "command": "uvx",
      "args": ["mcp-clickhousex"],
      "env": {
        "MCP_CLICKHOUSE_DSN": "http://default:@localhost:8123/default"
      }
    }
  }
}

[mcp_servers.clickhouse]
command = "uvx"
args = ["mcp-clickhousex"]

[mcp_servers.clickhouse.env]
MCP_CLICKHOUSE_DSN = "http://default:@localhost:8123/default"

{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "clickhouse": {
      "type": "local",
      "enabled": true,
      "command": ["uvx", "mcp-clickhousex"],
      "environment": {
        "MCP_CLICKHOUSE_DSN": "http://default:@localhost:8123/default"
      }
    }
  }
}

{
  "inputs": [],
  "servers": {
    "clickhouse": {
      "type": "stdio",
      "command": "uvx",
      "args": ["mcp-clickhousex"],
      "env": {
        "MCP_CLICKHOUSE_DSN": "http://default:@localhost:8123/default"
      }
    }
  }
}

Tests

Tests require a running ClickHouse instance; the suite creates a sample table in the default database, seeds it, and drops it after.

uv run pytest tests/ -v

The harness locates the instance through MCP_TEST_CLICKHOUSE_DSN, falling back to http://admin:password123@localhost:8123/default. Set it to point tests at another server without touching your production MCP_CLICKHOUSE_DSN.

The suite configures two profiles on that one instance — a read-only default and a write-enabled writable — so both halves of the write gate are exercised: run_command is advertised because a profile opts in, and is still refused against the profile that does not.

Contributing

Open issues or PRs; follow existing style and add tests where appropriate.

License

MIT