ClickHouse MCP Server

An MCP server for ClickHouse.

The server implements MCP 2026-07-28 and supports legacy initialize handshakes from 2024-11-05 through 2025-11-25. Modern clients use sessionless requests and server/discover. Existing clients can continue to negotiate the legacy protocol.

[!NOTE] HTTP requests without MCP-Protocol-Version are routed through legacy handling so clients from before 2025-06-18 can continue to connect. MCP 2026-07-28 permits this behavior on servers that support those clients. Modern clients should send the header on every POST request.

Features

ClickHouse Tools

ClickHouse tool responses are JSON-encoded strings. Integers outside [-9007199254740991, 9007199254740991] are returned as decimal strings to preserve exact values in JavaScript clients. This applies to query rows and integer table metadata. Safe-range integers and booleans keep their JSON types.

• run_query

  • Execute SQL queries on your ClickHouse cluster.
  • Input: query (string): The SQL query to execute.
  • Queries run in read-only mode by default (CLICKHOUSE_ALLOW_WRITE_ACCESS=false), but writes can be enabled explicitly if needed.
  • DESCRIBE (<query>) and EXPLAIN ESTIMATE <query> run here too and are optional ways to inspect a query's result schema or its estimated reads. See Checking a query before running it.

• list_databases

  • List all databases on your ClickHouse cluster.

• list_tables

  • List tables in a database with pagination.
  • Required input: database (string).
  • Optional inputs:
    • like / not_like (string): Apply LIKE or NOT LIKE filters to table names.
    • page_token (string): Single-use token returned by a previous call. It is retained for up to one hour.
    • page_size (int, default 50): Number of tables returned per page; must be greater than 0.
    • include_detailed_columns (bool, default true): When false, omits column metadata for lighter responses while keeping the full create_table_query.
  • Response shape:
    • tables: Array of table objects for the current page.
    • next_page_token: Pass this single-use value back before it expires to fetch the next page, or null when there are no more tables.
    • total_tables: Total count of tables that match the supplied filters.

Checking a query before running it

run_query also runs DESCRIBE and EXPLAIN ESTIMATE. Both are optional checks: reach for DESCRIBE when you need a query's output columns and types, and for EXPLAIN ESTIMATE before a SELECT that could be expensive.

DESCRIBE (<query>) inspects the result schema and returns the same output-column metadata as DESCRIBE TABLE:

DESCRIBE (SELECT user, sum(amt) FROM events WHERE ts > now() - INTERVAL 30 DAY GROUP BY user)

user      String
sum(amt)  Decimal(38, 2)

ClickHouse has to analyze the query to answer, so analysis errors surface here, with ClickHouse's own message, instead of part way through execution:

DESCRIBE (SELECT usr FROM events)  -> Code: 47. Unknown expression identifier `usr` ... Maybe you meant: ['user']
DESCRIBE (SELECT * FROM nosuch)    -> Code: 60. Unknown table expression identifier 'nosuch'

A query that describes cleanly can still fail when it runs, on a memory limit or a remote server error, and it says nothing about cost.

EXPLAIN ESTIMATE <query> returns the parts, rows and marks the query would read, one row per table, which is what separates a primary key lookup from a full scan:

EXPLAIN ESTIMATE SELECT count() FROM events WHERE id = 42

database  table   parts  rows   marks
default   events  1      8192   1

Those are estimated reads from MergeTree family tables, after primary key and partition pruning. They are not run time and not result size, and other table engines are not covered.

Neither statement runs the query body, but analysis is not always free: DESCRIBE (SELECT (SELECT sleep(1))) executes the scalar subquery while analyzing. Both are read-only and work under the default CLICKHOUSE_ALLOW_WRITE_ACCESS=false. See the ClickHouse documentation for EXPLAIN ESTIMATE and DESCRIBE.

chDB Tools

• run_chdb_select_query
  • Execute SQL queries using chDB's embedded ClickHouse engine.
  • Input: query (string): The SQL query to execute.
  • Integers outside [-9007199254740991, 9007199254740991] are returned as decimal strings.
  • Query data directly from various sources (files, URLs, databases) without ETL processes.
  • Requires the optional chdb extra: pip install 'mcp-clickhouse[chdb]'

Health Check Endpoint

When running with HTTP or SSE transport, a health check endpoint is available at /health. This endpoint:

• Returns 200 OK (body: OK) if the server is healthy and can connect to ClickHouse
• Returns 503 Service Unavailable with a generic error message if the server cannot connect to ClickHouse
• Returns 503 if a ClickHouse probe does not finish within two seconds. Concurrent requests share one in-flight probe
• Reuses a completed probe result for one second, so probes that arrive in quick succession do not each connect to ClickHouse. A failure or a recovery can therefore be reported up to a second late

GET and HEAD requests to the endpoint are intentionally unauthenticated and exempt from Host and Origin validation so orchestrator probes (e.g. Kubernetes liveness/readiness, load balancers) can use runtime-assigned pod or target IPs without extra configuration. /health is reserved and cannot be used as the MCP transport path. The response body is deliberately minimal to avoid leaking backend version strings or error details; debug failures via the server logs.

Example:

curl http://localhost:8000/health
# Response: OK

Security

Authentication for HTTP/SSE Transports

When using HTTP or SSE transport, authentication is required by default. The stdio transport (default) does not require authentication as it only communicates via standard input/output.

Three authentication modes are supported. Pick one:

Mode	When to use	Env var
Static bearer token	Simple deployments, internal services	CLICKHOUSE_MCP_AUTH_TOKEN
OAuth / OIDC (via FastMCP)	Azure Entra, Google, GitHub, WorkOS, etc.	FASTMCP_SERVER_AUTH=<provider-class-path> (+ provider-specific FASTMCP_SERVER_AUTH_* vars)
Disabled	Local development only	CLICKHOUSE_MCP_AUTH_DISABLED=true

Startup fails if none of these are configured for HTTP/SSE transports.

Setting Up Authentication

1. Generate a secure token (can be any random string):

   # Using uuidgen (macOS/Linux)
   uuidgen
   
   # Using openssl
   openssl rand -hex 32
   

2. Configure the server with the token:

   export CLICKHOUSE_MCP_AUTH_TOKEN="your-generated-token"
   

3. Configure your MCP client to include the token in requests:

   For Claude Desktop with HTTP/SSE transport:

   {
     "mcpServers": {
       "mcp-clickhouse": {
         "url": "http://127.0.0.1:8000",
         "headers": {
           "Authorization": "Bearer your-generated-token"
         }
       }
     }
   }
   

   Note: the /health endpoint is intentionally unauthenticated (see Health Check Endpoint above). To verify that bearer-token auth is actually rejecting unauthenticated requests, hit the MCP endpoint itself e.g. with the MCP Inspector, or by POSTing a JSON-RPC request to /mcp with and without the Authorization header and confirming the unauthenticated call returns 401.

OAuth / OIDC via FastMCP

For production deployments with identity providers (Azure Entra, Google, GitHub, WorkOS, etc.), delegate authentication to FastMCP's built-in auth providers instead of using a static token. Set FASTMCP_SERVER_AUTH to the full class path of a FastMCP auth provider, along with the provider-specific FASTMCP_SERVER_AUTH_* variables, and leave CLICKHOUSE_MCP_AUTH_TOKEN unset.

Example (Azure Entra):

export FASTMCP_SERVER_AUTH=fastmcp.server.auth.providers.azure.AzureProvider
export FASTMCP_SERVER_AUTH_AZURE_TENANT_ID="<tenant-id>"
export FASTMCP_SERVER_AUTH_AZURE_CLIENT_ID="<client-id>"
export FASTMCP_SERVER_AUTH_AZURE_CLIENT_SECRET="<client-secret>"
export FASTMCP_SERVER_AUTH_AZURE_BASE_URL="https://mcp.example.com"
export FASTMCP_SERVER_AUTH_AZURE_REQUIRED_SCOPES="read access_as_user"

mcp-clickhouse retains these FastMCP 2.14.7 environment prefixes for the FastMCP 4.0.0 built-in providers:

Provider class path	Provider variable prefix
fastmcp.server.auth.providers.auth0.Auth0Provider	FASTMCP_SERVER_AUTH_AUTH0_
fastmcp.server.auth.providers.aws.AWSCognitoProvider	FASTMCP_SERVER_AUTH_AWS_COGNITO_
fastmcp.server.auth.providers.azure.AzureProvider	FASTMCP_SERVER_AUTH_AZURE_
fastmcp.server.auth.providers.descope.DescopeProvider	FASTMCP_SERVER_AUTH_DESCOPEPROVIDER_
fastmcp.server.auth.providers.discord.DiscordProvider	FASTMCP_SERVER_AUTH_DISCORD_
fastmcp.server.auth.providers.github.GitHubProvider	FASTMCP_SERVER_AUTH_GITHUB_
fastmcp.server.auth.providers.google.GoogleProvider	FASTMCP_SERVER_AUTH_GOOGLE_
fastmcp.server.auth.providers.introspection.IntrospectionTokenVerifier	FASTMCP_SERVER_AUTH_INTROSPECTION_
fastmcp.server.auth.providers.jwt.JWTVerifier	FASTMCP_SERVER_AUTH_JWT_
fastmcp.server.auth.providers.oci.OCIProvider	FASTMCP_SERVER_AUTH_OCI_
fastmcp.server.auth.providers.scalekit.ScalekitProvider	FASTMCP_SERVER_AUTH_SCALEKITPROVIDER_
fastmcp.server.auth.providers.supabase.SupabaseProvider	FASTMCP_SERVER_AUTH_SUPABASE_
fastmcp.server.auth.providers.workos.WorkOSProvider	FASTMCP_SERVER_AUTH_WORKOS_
fastmcp.server.auth.providers.workos.AuthKitProvider	FASTMCP_SERVER_AUTH_AUTHKITPROVIDER_

Append the uppercase provider field name to the prefix. See the FastMCP docs for each provider's configuration requirements.

Auth values set directly in the process environment take precedence case-insensitively. The default .env load starts at the installed mcp_clickhouse package directory, resolves symlinks first, and walks upward to the filesystem root. It loads the first .env it finds and loads nothing if there is none. It never reads the working directory, regardless of how the server is launched. A source checkout normally finds the repository root .env. That file may also provide FASTMCP_SERVER_AUTH and its provider fields. Its values take precedence over the explicit or compatibility auth file. For FastMCP 2 compatibility, mcp-clickhouse reads missing provider fields from .env in the working directory, but that compatibility fallback cannot select FASTMCP_SERVER_AUTH. A process-set FASTMCP_ENV_FILE replaces that compatibility fallback and may provide both the selector and provider fields. Set it before startup. The mcp-clickhouse compatibility loader reads only FASTMCP_SERVER_AUTH and FASTMCP_SERVER_AUTH_* from that file, so it cannot inject CLICKHOUSE_* settings. FastMCP 4 may use the same file for its own broader settings. A custom provider receives no environment-derived constructor arguments and must support no-argument construction.

Treat both discovered and working-directory .env files as trusted authentication configuration. Anyone who can create or write a .env in any directory from the package directory up to the filesystem root can control which file is discovered, select the provider, and set its fields. Anyone who can write the working-directory file controls every provider field absent from the process and discovered configuration, including signing keys, issuers and endpoints, and client secrets. A process-set FASTMCP_ENV_FILE that points to an operator-owned file disables the working-directory fallback.

FastMCP 4 changed the default OAuth proxy client store. Deployments that relied on FastMCP 2's default OAuth proxy storage must have clients register and authorize again. Compatible custom storage, static bearer tokens, and JWT verification are unaffected.

Development Mode (Disabling Authentication)

For local development and testing only, you can disable authentication by setting:

export CLICKHOUSE_MCP_AUTH_DISABLED=true
export CLICKHOUSE_MCP_ALLOWED_HOSTS=127.0.0.1:8000,localhost:8000

WARNING: Only use this for local development. Do not disable authentication when the server is exposed to any network.

Configuration

This MCP server supports both ClickHouse and chDB. You can enable either or both depending on your needs. Python 3.10 through 3.14 are supported. Python 3.12 is recommended for local launches.

1. Open the Claude Desktop configuration file located at:

   • On macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
   • On Windows: %APPDATA%/Claude/claude_desktop_config.json

2. Add the following:

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "uv",
      "args": [
        "run",
        "--with",
        "mcp-clickhouse",
        "--python",
        "3.12",
        "mcp-clickhouse"
      ],
      "env": {
        "CLICKHOUSE_HOST": "<clickhouse-host>",
        "CLICKHOUSE_PORT": "<clickhouse-port>",
        "CLICKHOUSE_USER": "<clickhouse-user>",
        "CLICKHOUSE_PASSWORD": "<clickhouse-password>",
        "CLICKHOUSE_ROLE": "<clickhouse-role>",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_VERIFY": "true",
        "CLICKHOUSE_CONNECT_TIMEOUT": "30"
      }
    }
  }
}

Update the environment variables to point to your own ClickHouse service.

Or, if you'd like to try it out with the ClickHouse SQL Playground, you can use the following config:

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "uv",
      "args": [
        "run",
        "--with",
        "mcp-clickhouse",
        "--python",
        "3.12",
        "mcp-clickhouse"
      ],
      "env": {
        "CLICKHOUSE_HOST": "sql-clickhouse.clickhouse.com",
        "CLICKHOUSE_PORT": "8443",
        "CLICKHOUSE_USER": "demo",
        "CLICKHOUSE_PASSWORD": "",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_VERIFY": "true",
        "CLICKHOUSE_CONNECT_TIMEOUT": "30"
      }
    }
  }
}

For chDB (embedded ClickHouse engine), add the following configuration:

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "uv",
      "args": [
        "run",
        "--with",
        "mcp-clickhouse[chdb]",
        "--python",
        "3.12",
        "mcp-clickhouse"
      ],
      "env": {
        "CHDB_ENABLED": "true",
        "CLICKHOUSE_ENABLED": "false",
        "CHDB_DATA_PATH": "/path/to/chdb/data"
      }
    }
  }
}

You can also enable both ClickHouse and chDB simultaneously:

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "uv",
      "args": [
        "run",
        "--with",
        "mcp-clickhouse[chdb]",
        "--python",
        "3.12",
        "mcp-clickhouse"
      ],
      "env": {
        "CLICKHOUSE_HOST": "<clickhouse-host>",
        "CLICKHOUSE_PORT": "<clickhouse-port>",
        "CLICKHOUSE_USER": "<clickhouse-user>",
        "CLICKHOUSE_PASSWORD": "<clickhouse-password>",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_VERIFY": "true",
        "CLICKHOUSE_CONNECT_TIMEOUT": "30",
        "CHDB_ENABLED": "true",
        "CHDB_DATA_PATH": "/path/to/chdb/data"
      }
    }
  }
}

3. Locate the command entry for uv and replace it with the absolute path to the uv executable. This ensures that the correct version of uv is used when starting the server. On a mac, you can find this path using which uv.

4. Restart Claude Desktop to apply the changes.

Optional Write Access

By default, this MCP enforces read-only queries so that accidental mutations cannot happen during exploration. To allow DDL or INSERT statements, set the CLICKHOUSE_ALLOW_WRITE_ACCESS environment variable to true. The server keeps enforcing read-only mode if the ClickHouse instance itself disallows writes.

Destructive Operation Protection

Even when write access is enabled (CLICKHOUSE_ALLOW_WRITE_ACCESS=true), destructive operations require an additional opt-in flag for safety. The check covers any DROP statement (including the ALTER TABLE ... DROP PARTITION / DROP PART / DROP COLUMN clauses), any TRUNCATE, DELETE and UPDATE (both the lightweight statements and the ALTER TABLE ... DELETE / ALTER TABLE ... UPDATE mutations), REPLACE TABLE, CREATE OR REPLACE, ALTER TABLE ... REPLACE PARTITION, ALTER TABLE ... CLEAR COLUMN / CLEAR INDEX / CLEAR PROJECTION, and DETACH ... PERMANENTLY. Keywords inside string literals, quoted identifiers, and SQL comments are ignored, so they neither trigger the check nor hide a statement from it.

This check runs in the MCP server and is a best-effort guard against accidents. It is not a security boundary. The security boundary is the ClickHouse user's grants. Read-only mode (the default) is enforced server-side via readonly=1. The destructive-operation gate is not server-enforced.

For write mode, give the MCP server a dedicated ClickHouse user with only the privileges it needs:

CREATE USER mcp_agent IDENTIFIED BY '...';
GRANT SELECT, INSERT, CREATE TABLE, ALTER ADD COLUMN ON mydb.* TO mcp_agent;

Every statement outside these grants then fails server-side with ACCESS_DENIED, regardless of MCP flags. The server settings max_table_size_to_drop and max_partition_size_to_drop can also cap blast radius if pinned with settings constraints.

To enable destructive operations, set both flags:

"env": {
  "CLICKHOUSE_ALLOW_WRITE_ACCESS": "true",
  "CLICKHOUSE_ALLOW_DROP": "true"
}

This two-tier approach makes accidental deletion difficult:

• Write operations (INSERT, CREATE, ALTER ADD COLUMN) require CLICKHOUSE_ALLOW_WRITE_ACCESS=true
• Destructive operations (DROP, TRUNCATE, DELETE, UPDATE, and the rest of the list above) additionally require CLICKHOUSE_ALLOW_DROP=true

Running Without uv (Using System Python)

If you prefer to use the system Python installation instead of uv, you can install the package from PyPI and run it directly:

1. Install the package using pip:

   python3 -m pip install mcp-clickhouse
   

   To install chDB support as well:

   python3 -m pip install 'mcp-clickhouse[chdb]'
   

   To upgrade to the latest version:

   python3 -m pip install --upgrade mcp-clickhouse
   

2. Update your Claude Desktop configuration to use Python directly:

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "python3",
      "args": [
        "-m",
        "mcp_clickhouse.main"
      ],
      "env": {
        "CLICKHOUSE_HOST": "<clickhouse-host>",
        "CLICKHOUSE_PORT": "<clickhouse-port>",
        "CLICKHOUSE_USER": "<clickhouse-user>",
        "CLICKHOUSE_PASSWORD": "<clickhouse-password>",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_VERIFY": "true",
        "CLICKHOUSE_CONNECT_TIMEOUT": "30"
      }
    }
  }
}

Alternatively, you can use the installed script directly:

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "mcp-clickhouse",
      "env": {
        "CLICKHOUSE_HOST": "<clickhouse-host>",
        "CLICKHOUSE_PORT": "<clickhouse-port>",
        "CLICKHOUSE_USER": "<clickhouse-user>",
        "CLICKHOUSE_PASSWORD": "<clickhouse-password>",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_VERIFY": "true",
        "CLICKHOUSE_CONNECT_TIMEOUT": "30"
      }
    }
  }
}

Note: Make sure to use the full path to the Python executable or the mcp-clickhouse script if they are not in your system PATH. You can find the paths using:

• which python3 for the Python executable
• which mcp-clickhouse for the installed script

Custom Middleware

You can add custom middleware to the MCP server without modifying the source code. FastMCP provides a middleware system that allows you to intercept and process MCP protocol messages (tool calls, resource reads, prompts, etc.).

How to Use

1. Create a Python module with middleware classes extending Middleware and a setup_middleware(mcp) function:

# my_middleware.py
import logging
from fastmcp.server.middleware import Middleware, MiddlewareContext, CallNext

logger = logging.getLogger("my-middleware")

class LoggingMiddleware(Middleware):
    """Log all tool calls."""
    
    async def on_call_tool(self, context: MiddlewareContext, call_next: CallNext):
        tool_name = context.message.name if hasattr(context.message, 'name') else 'unknown'
        logger.info(f"Calling tool: {tool_name}")
        result = await call_next(context)
        logger.info(f"Tool {tool_name} completed")
        return result

def setup_middleware(mcp):
    """Register middleware with the MCP server."""
    mcp.add_middleware(LoggingMiddleware())

2. Set the MCP_MIDDLEWARE_MODULE environment variable to the module name (without .py extension):

{
  "mcpServers": {
    "mcp-clickhouse": {
      "command": "uv",
      "args": ["run", "--with", "mcp-clickhouse", "--python", "3.12", "mcp-clickhouse"],
      "env": {
        "CLICKHOUSE_HOST": "<clickhouse-host>",
        "CLICKHOUSE_USER": "<clickhouse-user>",
        "CLICKHOUSE_PASSWORD": "<clickhouse-password>",
        "MCP_MIDDLEWARE_MODULE": "my_middleware"
      }
    }
  }
}

3. Ensure your middleware module is in Python's import path (e.g., in the same directory where the MCP server runs, or installed as a package).

Example Middleware

An example middleware module is provided in example_middleware.py showing common patterns:

• Logging all MCP requests
• Logging tool calls specifically
• Measuring request processing time

To use the example:

"env": {
  "MCP_MIDDLEWARE_MODULE": "example_middleware"
}

Middleware Capabilities

The Middleware base class provides hooks for different MCP operations: