MCP ClickHouse Tool

A read-only Model Context Protocol (MCP) server for ClickHouse that supports metadata discovery, resources, parameterized `SELECT` queries, `SHOW` introspection, query analysis, and snapshot mode for large result sets, with profile-based configuration and strict no-DML/DDL enforcement.

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 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

All settings use the MCP_CLICKHOUSE prefix. Flat environment variables (e.g. `MCP_CLICKHOUSE_DSN`) are the straightforward way to configure the default profile when you have a single connection. For multiple profiles, the user-scoped `config.json` file is recommended.

Single connection: Configure via environment variables.

# Connection DSN (required).
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 max rows per interactive query (default 500; hard ceiling 1000).
export MCP_CLICKHOUSE_QUERY_MAX_ROWS="500"

# Optional interactive query timeout in seconds (default 30; hard ceiling 300).
export MCP_CLICKHOUSE_QUERY_COMMAND_TIMEOUT_SECONDS="30"

# Optional max rows for snapshot queries (default 10000; hard ceiling 50000).
export MCP_CLICKHOUSE_SNAPSHOT_MAX_ROWS="10000"

# Optional snapshot query timeout in seconds (default 120; hard ceiling 300).
export MCP_CLICKHOUSE_SNAPSHOT_COMMAND_TIMEOUT_SECONDS="120"

Multiple connections: Use the user-scoped `config.json` file (recommended). Env vars also work via the `MCP_CLICKHOUSE_PROFILES__` prefix (e.g. `MCP_CLICKHOUSE_PROFILES_WAREHOUSE_DSN`).

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

Example (`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"
    }
  }
}

Special characters in credentials: If the username or password contains URL-reserved characters, percent-encode them in the DSN:

For example, username `admin@org` and password `p#ss?` become `admin%40org:p%23ss%3F` in the DSN: `http://admin%40org:p%23ss%3F@host:8123/database`.

Tools

Tool descriptions match `server.py` tool docstrings except the `[ClickHouse]` prefix is omitted here (it remains in MCP-exposed metadata). Parameter text matches each `Field(description=…)` on the same tool.

Resources

The server exposes the same discovery and metadata as the tools above via URI-addressable resources (profile-first hierarchy). Each resource’s `description` matches the corresponding tool (`list_profiles`, `get_cluster_properties`, `list_databases`, `list_tables`, `list_columns`), plus `Src:` tags for URI path parameters. All resource content is JSON (`application/json`) except snapshots which return CSV (`text/csv`). Use path segment `default` for the default profile or database.

Resource descriptions match `description=…` on `@mcp.resource` in `server.py` (same prefix omission as above).

Security

Read-only SQL only: `run_query` allows `SELECT` / `WITH … SELECT`; `run_show` allows a single `SHOW` statement per call. `INTO OUTFILE` is not allowed on `run_show`. 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). Parameterized queries are supported where the driver allows (`%(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"
      }
    }
  }
}

Claude Code

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

Copilot

{
  "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.