MCP ClickHouse Tool

A read-only Model Context Protocol (MCP) server for ClickHouse that supports metadata discovery, resources, parameterized queries, and query analysis, with profile-based configuration and strict no-DML/DDL enforcement.

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

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 uvx mcp-clickhousex

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

Configuration

Connection and behavior are configured via environment variables. The server supports multiple named profiles and a backward-compatible flat layer for single-connection setups.

Single connection (flat env vars)

Flat vars create or override the default profile. This is all you need for a single ClickHouse instance:

export MCP_CLICKHOUSE_DSN="http://user:password@host:8123/database"
export MCP_CLICKHOUSE_DESCRIPTION="Primary cluster"                  # optional
export MCP_CLICKHOUSE_QUERY_MAX_ROWS="5000"                          # default: 5000 (capped at 50000)
export MCP_CLICKHOUSE_QUERY_COMMAND_TIMEOUT_SECONDS="30"             # default: 30 (capped at 300)

Multiple profiles (structured env vars)

To connect to more than one ClickHouse instance, use the MCP_CLICKHOUSE_PROFILES_<NAME>_ prefix. Profile names must be alphanumeric (no underscores) and are case-insensitive.

# Default profile
export MCP_CLICKHOUSE_PROFILES_DEFAULT_DSN="http://user:pass@primary:8123/mydb"
export MCP_CLICKHOUSE_PROFILES_DEFAULT_DESCRIPTION="Primary cluster"
export MCP_CLICKHOUSE_PROFILES_DEFAULT_QUERY_MAX_ROWS="5000"
export MCP_CLICKHOUSE_PROFILES_DEFAULT_QUERY_COMMAND_TIMEOUT_SECONDS="60"

# Named profile
export MCP_CLICKHOUSE_PROFILES_WAREHOUSE_DSN="http://user:pass@warehouse:8123/analytics"
export MCP_CLICKHOUSE_PROFILES_WAREHOUSE_DESCRIPTION="Analytics warehouse"
export MCP_CLICKHOUSE_PROFILES_WAREHOUSE_QUERY_MAX_ROWS="10000"
export MCP_CLICKHOUSE_PROFILES_WAREHOUSE_QUERY_COMMAND_TIMEOUT_SECONDS="120"

Per-profile fields:

Suffix	Description	Default
DSN	Connection DSN (required)	http://default:@localhost:8123/default
DESCRIPTION	Human-readable label	—
QUERY_MAX_ROWS	Row cap per query	5000 (max 50000)
QUERY_COMMAND_TIMEOUT_SECONDS	Query timeout	30 (max 300)

Merge rule: Flat vars always feed into the default profile. If both MCP_CLICKHOUSE_PROFILES_DEFAULT_* and flat vars are set, flat vars win on conflict.

User-level configuration

You can define profiles in a user config file instead of (or in addition to) environment variables. The file is read first; environment variables override file values.

• Path: ~/.config/mcp-clickhousex/config.json (Windows: %USERPROFILE%\.config\mcp-clickhousex\config.json).
• Precedence: user config file → structured env vars → flat env vars (later overrides earlier).
• Format: JSON with a top-level profiles object; each profile supports dsn, description, query_max_rows, query_command_timeout_seconds. Profile names must be alphanumeric (no underscores).

Example config.json:

{
  "profiles": {
    "default": {
      "dsn": "http://default:@localhost:8123/default",
      "description": "Primary",
      "query_max_rows": 5000,
      "query_command_timeout_seconds": 60
    },
    "warehouse": {
      "dsn": "http://user:pass@warehouse:8123/analytics",
      "description": "Warehouse"
    }
  }
}

Keep secrets in environment variables or a secret manager; avoid committing connection strings in the config file.

Max rows is applied to every query (server-side via max_result_rows); results may be truncated with a truncated and row_limit field in the response.

Tools

Tool	Description	Key params
list_profiles	List configured profiles (name and optional description).	—
get_cluster_properties	Get cluster (node) version and execution limits for a profile.	profile (optional)
run_query	Execute a read-only SELECT and return tabular results. Database/table must be specified in the SQL (e.g. db.table). Applies the profile's max rows limit.	sql, parameters (optional), profile (optional)
analyze_query	Analyze a read-only SELECT via EXPLAIN (plan with index usage, pipeline, syntax).	sql, parameters (optional), types (optional), database (optional), profile (optional)
list_databases	List all databases.	profile (optional)
list_tables	List tables and views in a database. Returns name, engine, primary_key, sorting_key, partition_key, total_rows, total_bytes for query analysis.	database (optional), profile (optional)
list_columns	List columns for a table or view.	table, database (optional), profile (optional)

Resources

The server exposes the same discovery and metadata as the tools above via URI-addressable resources (profile-first hierarchy). All resource content is JSON (application/json). Use path segment default for the default profile or database.

Resource	URI	Description
Profiles	clickhouse://profiles	List configured profiles (same as list_profiles)
Cluster properties	clickhouse://profiles/{profile}/cluster-properties	Cluster version and limits for a profile
Databases	clickhouse://profiles/{profile}/databases	List databases for a profile
Tables	clickhouse://profiles/{profile}/databases/{database}/tables	List tables for a profile and database
Table columns	clickhouse://profiles/{profile}/databases/{database}/tables/{table}/columns	List columns for a table

Security

Read-only (SELECT only); parameterized queries supported (%(name)s or {name:Type} syntax). Use environment variables for connection credentials — never commit secrets.

MCP host examples

Snippets for common MCP clients using uvx mcp-clickhousex (no clone required; ensure uv is on your PATH). Replace connection details as needed.

Cursor

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

Codex

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

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

OpenCode

{
  "$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"
      }
    }
  }
}

GitHub Copilot (agent)

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

Config file locations: Cursor .cursor/mcp.json, Codex/Copilot/OpenCode vary by client; see your client's MCP docs.

Tests

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

# Run all tests (unit + functional + e2e)
uv run pytest tests/ -v

The test harness uses MCP_TEST_CLICKHOUSE_DSN to locate the ClickHouse instance. If unset, it falls back to http://admin:password123@localhost:8123/default. Set the variable to point tests at a different server without affecting your production MCP_CLICKHOUSE_DSN:

export MCP_TEST_CLICKHOUSE_DSN="http://user:pass@testhost:8123/default"
uv run pytest tests/ -v

Roadmap

No planned features at this time. Open an issue to suggest improvements.

Contributing

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

License

MIT. See LICENSE.