ClickHouse MCP Server

A Model Context Protocol (MCP) server that provides tools for interacting with ClickHouse databases. Built with xmcp and supports both HTTP and stdio transport methods.

Features

• List Databases: Retrieve all databases from your ClickHouse server
• List Tables: Get tables from a specific database
• Run Select Queries: Execute SELECT, SHOW, and DESCRIBE queries safely
• Multiple Transport Modes: Supports stdio, HTTP, and SSE (Server-Sent Events)
• TLS/SSL Support: Configurable certificate handling for secure connections

Installation

From npm (when published)

npm install -g clickhouse-mcp-server

From source

git clone https://github.com/ahmedrowaihi/clickhouse-mcp-server.git
cd clickhouse-mcp-server
bun install
bun run build

Usage

As a CLI tool (after global install)

# Start in stdio mode (for local/subprocess communication)
clickhouse-mcp stdio

# Start in HTTP mode (for remote/network access)
clickhouse-mcp http

# Start in SSE mode (Server-Sent Events over HTTP)
clickhouse-mcp sse

Development mode

# Start development server
bun run dev

# Build for production
bun run build

# Run specific modes
bun run start:stdio
bun run start:http

Direct execution (after building)

# Using the compiled launcher
node dist/launcher.js stdio
node dist/launcher.js http
node dist/launcher.js sse

# Or run server files directly
node dist/stdio.js
node dist/http.js

Configuration

Environment Variables

Set these environment variables before running the server:

export CLICKHOUSE_HOST_URL=https://your-clickhouse-server.com:8443
export CLICKHOUSE_USER=your-username
export CLICKHOUSE_PASSWORD=your-password
export CLICKHOUSE_DATABASE=your-database

# Optional settings with defaults
export CLICKHOUSE_SECURE=true
export CLICKHOUSE_VERIFY=true
export CLICKHOUSE_CONNECT_TIMEOUT_SEC=10
export CLICKHOUSE_SEND_RECEIVE_TIMEOUT_SEC=60

# Optional TLS certificate path
export CLICKHOUSE_CERT_PATH=/path/to/certificate.pem

Or create a .env file in your project root:

# ClickHouse connection settings
CLICKHOUSE_HOST_URL=https://your-clickhouse-server.com:8443
CLICKHOUSE_USER=your-username
CLICKHOUSE_PASSWORD=your-password
CLICKHOUSE_DATABASE=your-database

# Optional settings with defaults
CLICKHOUSE_SECURE=true
CLICKHOUSE_VERIFY=true
CLICKHOUSE_CONNECT_TIMEOUT_SEC=10
CLICKHOUSE_SEND_RECEIVE_TIMEOUT_SEC=60

# Optional TLS certificate path
CLICKHOUSE_CERT_PATH=/path/to/certificate.pem

MCP Client Configuration

Claude Desktop

Add this to your Claude Desktop configuration file:

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

{
  "mcpServers": {
    "clickhouse": {
      "command": "clickhouse-mcp",
      "args": ["stdio"],
      "env": {
        "CLICKHOUSE_HOST_URL": "https://your-clickhouse-server.com:8443",
        "CLICKHOUSE_USER": "your-username",
        "CLICKHOUSE_PASSWORD": "your-password",
        "CLICKHOUSE_DATABASE": "your-database",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_VERIFY": "true",
        "CLICKHOUSE_CONNECT_TIMEOUT_SEC": "10",
        "CLICKHOUSE_SEND_RECEIVE_TIMEOUT_SEC": "60"
      }
    }
  }
}

Using with npx (if not globally installed)

{
  "mcpServers": {
    "clickhouse": {
      "command": "npx",
      "args": ["clickhouse-mcp-server", "stdio"],
      "env": {
        "CLICKHOUSE_HOST_URL": "https://your-clickhouse-server.com:8443",
        "CLICKHOUSE_USER": "your-username",
        "CLICKHOUSE_PASSWORD": "your-password",
        "CLICKHOUSE_DATABASE": "your-database"
      }
    }
  }
}

HTTP/SSE Mode Setup

For HTTP-based MCP clients, you need to run the server in HTTP or SSE mode and configure your client to connect via URL.

Step 1: Create .env file

Create a .env file in your project directory:

CLICKHOUSE_HOST_URL=https://sql-clickhouse.clickhouse.com:8443
CLICKHOUSE_USER=demo
CLICKHOUSE_PASSWORD=
CLICKHOUSE_DATABASE=default
CLICKHOUSE_SECURE=true

Step 2: Start the server

# Start in HTTP mode
clickhouse-mcp http

# Or start in SSE mode
clickhouse-mcp sse

The server will start on http://localhost:3001 by default and accepts mcp requests on the /mcp path.

Step 3: Configure MCP Client

For MCP clients that support HTTP connections, add this to your configuration:

{
  "mcpServers": {
    "clickhouse-playground": {
      "url": "http://127.0.0.1:3001/mcp"
    }
  }
}

Complete Example for Playground Testing:

1. Create .env file:

   CLICKHOUSE_HOST_URL=https://sql-clickhouse.clickhouse.com:8443
   CLICKHOUSE_USER=demo
   CLICKHOUSE_PASSWORD=
   CLICKHOUSE_DATABASE=default
   CLICKHOUSE_SECURE=true
   

2. Start server:

   clickhouse-mcp sse
   

3. Configure your MCP client:

   {
     "mcpServers": {
       "clickhouse-playground": {
         "url": "http://127.0.0.1:3001/mcp"
       }
     }
   }
   

Note: HTTP/SSE mode is ideal for web-based MCP clients, remote access, or when you need multiple clients to connect to the same server instance.

Quick Start with ClickHouse Playground

To quickly test the MCP server, you can use the public ClickHouse SQL Playground:

Environment Variables for Playground

export CLICKHOUSE_HOST_URL=https://sql-clickhouse.clickhouse.com:8443
export CLICKHOUSE_USER=demo
export CLICKHOUSE_PASSWORD=""
export CLICKHOUSE_DATABASE=default
export CLICKHOUSE_SECURE=true

Claude Desktop Configuration for Playground

{
  "mcpServers": {
    "clickhouse-playground": {
      "command": "clickhouse-mcp",
      "args": ["stdio"],
      "env": {
        "CLICKHOUSE_HOST_URL": "https://sql-clickhouse.clickhouse.com:8443",
        "CLICKHOUSE_USER": "demo",
        "CLICKHOUSE_PASSWORD": "",
        "CLICKHOUSE_DATABASE": "default",
        "CLICKHOUSE_SECURE": "true"
      }
    }
  }
}

Test Commands

Once configured, you can test these queries in Claude Desktop:

1. List databases: "Show me all available databases"
2. List tables: "What tables are in the default database?"
3. Sample query: "Show me the first 10 rows from any table"

Note: The ClickHouse Playground is a public demo environment. Don't use it for production data or sensitive information.

Environment Variables

Variable	Description	Required	Default
CLICKHOUSE_HOST_URL	Full URL to ClickHouse server	Yes	-
CLICKHOUSE_USER	Username for authentication	Yes	-
CLICKHOUSE_PASSWORD	Password for authentication	Yes	-
CLICKHOUSE_DATABASE	Default database to connect to	Yes	-
CLICKHOUSE_SECURE	Use HTTPS/TLS connection	No	true
CLICKHOUSE_VERIFY	Verify SSL certificates	No	true
CLICKHOUSE_CONNECT_TIMEOUT_SEC	Connection timeout in seconds	No	10
CLICKHOUSE_SEND_RECEIVE_TIMEOUT_SEC	Request timeout in seconds	No	60
CLICKHOUSE_CERT_PATH	Path to custom SSL certificate	No	-

Available Tools

1. List Databases

Lists all databases available on the ClickHouse server.

Usage in MCP client:

{
  "method": "tools/call",
  "params": {
    "name": "list_databases"
  }
}

2. List Tables

Lists all tables in a specified database.

Parameters:

• database (string): Name of the database

Usage in MCP client:

{
  "method": "tools/call",
  "params": {
    "name": "list_tables",
    "arguments": {
      "database": "my_database"
    }
  }
}

3. Run Select Query

Executes SELECT, SHOW, or DESCRIBE queries on the ClickHouse database.

Parameters:

• query (string): The SQL query to execute (must start with SELECT, SHOW, or DESCRIBE)

Usage in MCP client:

{
  "method": "tools/call",
  "params": {
    "name": "run_select_query",
    "arguments": {
      "query": "SELECT * FROM my_table LIMIT 10"
    }
  }
}

Transport Modes

STDIO Mode

Best for local development and subprocess communication:

clickhouse-mcp stdio

HTTP Mode

Ideal for remote access and web-based clients:

clickhouse-mcp http

SSE Mode

Server-Sent Events over HTTP for real-time applications:

clickhouse-mcp sse

Building

# Build everything (xmcp + launcher)
bun run build

# Build only xmcp components
bun run build:xmcp

Security

• All queries are executed with readonly: "1" setting for safety
• Only SELECT, SHOW, and DESCRIBE queries are allowed
• TLS/SSL support for secure connections

Requirements

• Node.js >= 20.0.0
• ClickHouse server with network access
• Valid ClickHouse credentials

License

MIT

Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

Links

• GitHub Repository