MCP ClickHouse: Database Operations + Cloud Management

A comprehensive Model Context Protocol (MCP) server that provides two distinct capabilities:

1. Database Operations - Connect to and query any ClickHouse database (local, cloud, or self-hosted)

2. Cloud Management - Complete ClickHouse Cloud infrastructure management via API

🚀 Quick Start

Start with our step-by-step tutorial:

👉 **Complete Setup Tutorial** - Transform Claude into a powerful ClickHouse data agent

For experienced users, jump to the Quick Configuration section below.

📚 Table of Contents

• 🚀 Quick Start
• 📚 Table of Contents
• 🎯 Choose Your Use Case
• 🌟 Why This Server?
• ✨ Capabilities Overview
• 🔒 Safety Features
• ⚡ Quick Configuration
• 📦 Installation
• ⚙️ Configuration Guide
• 🛠️ Available Tools
• 💡 Usage Examples
• 🔧 Development
• 🐛 Troubleshooting
• 📄 License

🎯 Choose Your Use Case

This MCP server supports two independent use cases. You can use one or both:

📊 Database Operations Only

For: Data analysis, querying, and exploration of ClickHouse databases

• Connect to any ClickHouse instance (local, self-hosted, or ClickHouse Cloud)
• Execute read-only queries safely
• Explore database schemas and metadata
• Setup: Database connection credentials only

☁️ Cloud Management Only

For: Managing ClickHouse Cloud infrastructure programmatically

• Create, configure, and manage cloud services
• Handle API keys, members, and organizations
• Monitor usage, costs, and performance
• Setup: ClickHouse Cloud API keys only

🔄 Both Combined

For: Complete ClickHouse workflow from infrastructure to data

• Manage cloud services AND query the databases within them
• End-to-end data pipeline management
• Setup: Both database credentials and cloud API keys

🌟 Why This Server?

This repository significantly improves over the original ClickHouse MCP server:

> [!WARNING]

> Security Notice: The original ClickHouse MCP server (v0.1.10) has a critical security flaw where `run_select_query` can execute ANY SQL operation including DROP, DELETE, INSERT, etc., despite its name suggesting it only runs SELECT queries. This server implements proper query filtering and safety controls.

✨ Capabilities Overview

📊 Database Operations (3 Tools)

Connect to and query any ClickHouse database:

• List databases and tables with detailed metadata
• Execute SELECT queries with safety guarantees (read-only mode)
• Explore schemas including column types, row counts, and table structures
• Works with: Local ClickHouse, self-hosted instances, ClickHouse Cloud databases, and the free SQL Playground

☁️ Cloud Management (50+ Tools)

Complete ClickHouse Cloud API integration:

• Organizations (5 tools): Manage settings, metrics, private endpoints
• Services (12 tools): Create, scale, start/stop, configure, delete cloud services
• API Keys (5 tools): Full CRUD operations for programmatic access
• Members & Invitations (8 tools): User management and access control
• Backups (4 tools): Configure and manage automated backups
• ClickPipes (7 tools): Data ingestion pipeline management
• Monitoring (3 tools): Usage analytics, costs, and audit logs
• Network (6 tools): Private endpoints and security configuration

🔒 Safety Features

This MCP server includes comprehensive safety controls to prevent accidental data modification or infrastructure changes:

📊 Database Safety

• Automatic Read-Only Mode: All database queries run with `readonly = 1` by default
• Query Filtering: Only SELECT, SHOW, DESCRIBE, and EXPLAIN queries are allowed
• Manual Override: Set `CLICKHOUSE_READONLY=false` to enable write operations when needed

☁️ Cloud Management Safety

• Protected Operations: Destructive cloud operations (delete, stop) can be enabled
• Safe Mode: Set `CLICKHOUSE_CLOUD_READONLY=false` to allow infrastructure changes
• Audit Trail: All operations are logged for accountability

🛡️ Security Best Practices

• Minimal Privileges: Create dedicated users with limited permissions
• SSL by Default: Secure connections enabled automatically
• Environment Variables: Sensitive credentials never hardcoded
• Timeout Controls: Prevent runaway queries and operations

⚡ Quick Configuration

Claude Desktop Setup

1. Open your Claude Desktop configuration file:

2. Choose your configuration based on your use case:

📊 Database Operations Only (Click to expand)

For Your Own ClickHouse Server

{
  "mcpServers": {
    "chmcp": {
      "command": "/path/to/uv",
      "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"],
      "env": {
        "CLICKHOUSE_HOST": "your-server.com",
        "CLICKHOUSE_PORT": "8443",
        "CLICKHOUSE_USER": "your-username",
        "CLICKHOUSE_PASSWORD": "your-password",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_READONLY": "true"
      }
    }
  }
}

For ClickHouse Cloud Database

{
  "mcpServers": {
    "chmcp": {
      "command": "/path/to/uv",
      "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"],
      "env": {
        "CLICKHOUSE_HOST": "your-instance.clickhouse.cloud",
        "CLICKHOUSE_USER": "default",
        "CLICKHOUSE_PASSWORD": "your-database-password",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_READONLY": "true"
      }
    }
  }
}

For Free Testing (SQL Playground)

{
  "mcpServers": {
    "chmcp": {
      "command": "/path/to/uv",
      "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"],
      "env": {
        "CLICKHOUSE_HOST": "sql-clickhouse.clickhouse.com",
        "CLICKHOUSE_PORT": "8443",
        "CLICKHOUSE_USER": "demo",
        "CLICKHOUSE_PASSWORD": "",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_READONLY": "true"
      }
    }
  }
}

☁️ Cloud Management Only (Click to expand)

{
  "mcpServers": {
    "chmcp": {
      "command": "/path/to/uv",
      "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"],
      "env": {
        "CLICKHOUSE_CLOUD_KEY_ID": "your-cloud-key-id",
        "CLICKHOUSE_CLOUD_KEY_SECRET": "your-cloud-key-secret"
      }
    }
  }
}

> Note: `CLICKHOUSE_CLOUD_READONLY` defaults to `true` (monitoring-only mode). Add `"CLICKHOUSE_CLOUD_READONLY": "false"` for full access.

🔄 Both Database + Cloud Management (Click to expand)

{
  "mcpServers": {
    "chmcp": {
      "command": "/path/to/uv",
      "args": ["run", "--with", "chmcp", "--python", "3.13", "chmcp"],
      "env": {
        "CLICKHOUSE_HOST": "your-instance.clickhouse.cloud",
        "CLICKHOUSE_USER": "default",
        "CLICKHOUSE_PASSWORD": "your-database-password",
        "CLICKHOUSE_SECURE": "true",
        "CLICKHOUSE_READONLY": "true",
        "CLICKHOUSE_CLOUD_KEY_ID": "your-cloud-key-id",
        "CLICKHOUSE_CLOUD_KEY_SECRET": "your-cloud-key-secret"
      }
    }
  }
}

> Note: This enables database analysis (readonly) + full cloud management. Add `"CLICKHOUSE_CLOUD_READONLY": "true"` for monitoring-only mode.

3. Important: Replace `/path/to/uv` with the absolute path to your `uv` executable (find it with `which uv` on macOS/Linux)

4. Restart Claude Desktop to apply the changes

📦 Installation

Option 1: Using uv (Recommended)

# Install via uv (used by Claude Desktop)
uv add chmcp

Option 2: Manual Installation

# Clone the repository
git clone https://github.com/oualib/chmcp.git
cd chmcp

# Install core dependencies
pip install .

# Install with development dependencies
pip install ".[dev]"

# Install with test dependencies
pip install ".[test]"

# Install with documentation dependencies
pip install ".[docs]"

# Install with all optional dependencies
pip install ".[dev,test,docs]"

# Set up environment variables
cp .env.example .env
# Edit .env with your configuration

⚙️ Configuration Guide

📊 Database Configuration

Set these environment variables to enable database operations:

Required Variables

CLICKHOUSE_HOST=your-clickhouse-host.com   # ClickHouse server hostname
CLICKHOUSE_USER=your-username              # Username for authentication
CLICKHOUSE_PASSWORD=your-password          # Password for authentication

Safety & Security Variables

CLICKHOUSE_READONLY=true                   # Enable read-only mode (recommended)
                                           # true: Only SELECT/SHOW/DESCRIBE queries allowed
                                           # false: All SQL operations permitted

Optional Variables (with defaults)

CLICKHOUSE_PORT=8443                        # 8443 for HTTPS, 8123 for HTTP
CLICKHOUSE_SECURE=true                      # Enable HTTPS connection
CLICKHOUSE_VERIFY=true                      # Verify SSL certificates
CLICKHOUSE_CONNECT_TIMEOUT=30               # Connection timeout in seconds
CLICKHOUSE_SEND_RECEIVE_TIMEOUT=300         # Query timeout in seconds
CLICKHOUSE_DATABASE=default                 # Default database to use

> [!CAUTION]

> Security Best Practice: Always use `CLICKHOUSE_READONLY=true` in production environments. Create a dedicated database user with minimal privileges for MCP connections. Avoid using administrative accounts.

☁️ Cloud API Configuration

Set these environment variables to enable cloud management:

Required Variables

CLICKHOUSE_CLOUD_KEY_ID=your-cloud-key-id          # From ClickHouse Cloud Console
CLICKHOUSE_CLOUD_KEY_SECRET=your-cloud-key-secret  # From ClickHouse Cloud Console

Safety & Security Variables

CLICKHOUSE_CLOUD_READONLY=false            # Cloud operation mode (default: false)
                                           # true: Only read operations (list, get, metrics)
                                           # false: All cloud operations permitted (create, update, delete)

Optional Variables (with defaults)

CLICKHOUSE_CLOUD_API_URL=https://api.clickhouse.cloud   # API endpoint
CLICKHOUSE_CLOUD_TIMEOUT=30                             # Request timeout
CLICKHOUSE_CLOUD_SSL_VERIFY=true                        # SSL verification

> [!WARNING]

> Cloud Safety: By default, `CLICKHOUSE_CLOUD_READONLY=false` allows all infrastructure operations. Set to `true` in production to prevent accidental infrastructure changes. When disabled, Claude can create, modify, and delete cloud services, which may incur costs or cause service disruptions.

🔑 Getting ClickHouse Cloud API Keys

1. Log into ClickHouse Cloud Console

2. Navigate to Settings → API Keys

3. Click Create API Key

4. Select appropriate permissions:

5. Copy the Key ID and Key Secret to your configuration

🔒 Safety Configuration Examples

Production Safe Mode (Recommended)

# Database - read-only queries only
CLICKHOUSE_HOST=your-instance.clickhouse.cloud
CLICKHOUSE_USER=readonly_user
CLICKHOUSE_PASSWORD=secure-password
CLICKHOUSE_SECURE=true
CLICKHOUSE_READONLY=true

# Cloud - monitoring and inspection only (explicitly set to true)
CLICKHOUSE_CLOUD_KEY_ID=your-cloud-key-id
CLICKHOUSE_CLOUD_KEY_SECRET=your-cloud-key-secret
CLICKHOUSE_CLOUD_READONLY=true

Development Mode (Full Access)

# Database - all operations allowed
CLICKHOUSE_HOST=localhost
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=clickhouse
CLICKHOUSE_SECURE=false
CLICKHOUSE_READONLY=false

# Cloud - full infrastructure management
CLICKHOUSE_CLOUD_KEY_ID=dev-key-id
CLICKHOUSE_CLOUD_KEY_SECRET=dev-key-secret
CLICKHOUSE_CLOUD_READONLY=false

Analysis Only Mode

# Database - read-only for data analysis
CLICKHOUSE_HOST=analytics.company.com
CLICKHOUSE_USER=analyst
CLICKHOUSE_PASSWORD=analyst-password
CLICKHOUSE_SECURE=true
CLICKHOUSE_READONLY=true

# Cloud - monitoring only, no infrastructure changes
CLICKHOUSE_CLOUD_KEY_ID=monitoring-key-id
CLICKHOUSE_CLOUD_KEY_SECRET=monitoring-key-secret
CLICKHOUSE_CLOUD_READONLY=true

Example Configurations

Local Development with Docker

# Database only - full access for development
CLICKHOUSE_HOST=localhost
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=clickhouse
CLICKHOUSE_SECURE=false
CLICKHOUSE_PORT=8123
CLICKHOUSE_READONLY=false

ClickHouse Cloud (Safe Mode)

# Database connection - read-only
CLICKHOUSE_HOST=your-instance.clickhouse.cloud
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=your-database-password
CLICKHOUSE_SECURE=true
CLICKHOUSE_READONLY=true

# Cloud management - monitoring only (explicitly set to true)
CLICKHOUSE_CLOUD_KEY_ID=your-cloud-key-id
CLICKHOUSE_CLOUD_KEY_SECRET=your-cloud-key-secret
CLICKHOUSE_CLOUD_READONLY=true

SSL Issues Troubleshooting

If you encounter SSL certificate verification issues:

# Disable SSL verification for database
CLICKHOUSE_VERIFY=false
CLICKHOUSE_SECURE=false  # Use HTTP instead of HTTPS
CLICKHOUSE_PORT=8123     # HTTP port instead of 8443

# Disable SSL verification for cloud API
CLICKHOUSE_CLOUD_SSL_VERIFY=false

🛠️ Available Tools

📊 Database Tools (3 tools)

These tools work with any ClickHouse database when database configuration is provided:

• `list_databases()` - List all available databases
• `list_tables(database, like?, not_like?)` - List tables with detailed metadata including schema, row counts, and column information
• `run_query(query)` - Execute queries with safety controls:
  • Read-only mode (`CLICKHOUSE_READONLY=true`): Only SELECT, SHOW, DESCRIBE, EXPLAIN queries
  • Full access mode (`CLICKHOUSE_READONLY=false`): All SQL operations including INSERT, UPDATE, DELETE, CREATE, DROP

> [!NOTE]

> Query Safety: When `CLICKHOUSE_READONLY=true`, all queries automatically run with `readonly = 1` setting and are filtered to prevent data modification operations.

☁️ Cloud Management Tools (50+ tools)

These tools work with ClickHouse Cloud when API credentials are provided. Tool availability depends on the `CLICKHOUSE_CLOUD_READONLY` setting:

🔍 Read-Only Operations (Available when `CLICKHOUSE_CLOUD_READONLY=true`)

Organization Monitoring (3 tools)

• `cloud_list_organizations()` - List available organizations
• `cloud_get_organization(organization_id)` - Get organization details
• `cloud_get_organization_metrics(organization_id, filtered_metrics?)` - Get Prometheus metrics

Service Monitoring (3 tools)

• `cloud_list_services(organization_id)` - List all services in organization
• `cloud_get_service(organization_id, service_id)` - Get detailed service information
• `cloud_get_service_metrics(organization_id, service_id, filtered_metrics?)` - Get service performance metrics

Resource Inspection (8 tools)

• `cloud_list_api_keys(organization_id)` - List all API keys (metadata only)
• `cloud_get_api_key(organization_id, key_id)` - Get API key details
• `cloud_list_members(organization_id)` - List organization members
• `cloud_get_member(organization_id, user_id)` - Get member details
• `cloud_list_invitations(organization_id)` - List pending invitations
• `cloud_get_invitation(organization_id, invitation_id)` - Get invitation details
• `cloud_list_backups(organization_id, service_id)` - List service backups
• `cloud_get_backup(organization_id, service_id, backup_id)` - Get backup details

Configuration Inspection (5 tools)

• `cloud_get_backup_configuration(organization_id, service_id)` - Get backup configuration
• `cloud_get_private_endpoint_config(organization_id, service_id)` - Get private endpoint configuration
• `cloud_list_clickpipes(organization_id, service_id)` - List ClickPipes
• `cloud_get_clickpipe(organization_id, service_id, clickpipe_id)` - Get ClickPipe details
• `cloud_get_available_regions()` - Get supported regions

Analytics & Monitoring (3 tools)

• `cloud_list_activities(organization_id, from_date?, to_date?)` - Get audit logs
• `cloud_get_activity(organization_id, activity_id)` - Get activity details
• `cloud_get_usage_cost(organization_id, from_date, to_date)` - Get usage analytics

⚠️ Write Operations (Available only when `CLICKHOUSE_CLOUD_READONLY=false`)

Organization Management (2 tools)

• `cloud_update_organization(organization_id, name?, private_endpoints?)` - Update organization settings
• `cloud_get_organization_private_endpoint_info(organization_id, cloud_provider, region)` - Get private endpoint info

Service Management (9 tools)

• `cloud_create_service(organization_id, name, provider, region, ...)` - Create new service
• `cloud_update_service(organization_id, service_id, ...)` - Update service settings
• `cloud_update_service_state(organization_id, service_id, command)` - Start/stop services
• `cloud_update_service_scaling(organization_id, service_id, ...)` - Configure scaling (legacy)
• `cloud_update_service_replica_scaling(organization_id, service_id, ...)` - Configure replica scaling
• `cloud_update_service_password(organization_id, service_id, ...)` - Update service password
• `cloud_create_service_private_endpoint(organization_id, service_id, id, description)` - Create private endpoint
• `cloud_delete_service(organization_id, service_id)` - Delete service

API Key Management (3 tools)

• `cloud_create_api_key(organization_id, name, roles, ...)` - Create new API key
• `cloud_update_api_key(organization_id, key_id, ...)` - Update API key properties
• `cloud_delete_api_key(organization_id, key_id)` - Delete API key

User Management (3 tools)

• `cloud_update_member_role(organization_id, user_id, role)` - Update member role
• `cloud_remove_member(organization_id, user_id)` - Remove member
• `cloud_create_invitation(organization_id, email, role)` - Send invitation
• `cloud_delete_invitation(organization_id, invitation_id)` - Cancel invitation

Infrastructure Management (12 tools)

• `cloud_update_backup_configuration(organization_id, service_id, ...)` - Update backup settings
• `cloud_create_clickpipe(organization_id, service_id, name, description, source, destination, field_mappings?)` - Create ClickPipe
• `cloud_update_clickpipe(organization_id, service_id, clickpipe_id, ...)` - Update ClickPipe
• `cloud_update_clickpipe_scaling(organization_id, service_id, clickpipe_id, replicas?)` - Scale ClickPipe
• `cloud_update_clickpipe_state(organization_id, service_id, clickpipe_id, command)` - Control ClickPipe state
• `cloud_delete_clickpipe(organization_id, service_id, clickpipe_id)` - Delete ClickPipe
• `cloud_list_reverse_private_endpoints(organization_id, service_id)` - List reverse private endpoints
• `cloud_create_reverse_private_endpoint(organization_id, service_id, ...)` - Create reverse private endpoint
• `cloud_get_reverse_private_endpoint(organization_id, service_id, reverse_private_endpoint_id)` - Get details
• `cloud_delete_reverse_private_endpoint(organization_id, service_id, reverse_private_endpoint_id)` - Delete endpoint
• `cloud_create_query_endpoint_config(organization_id, service_id, roles, open_api_keys, allowed_origins)` - Create query config
• `cloud_delete_query_endpoint_config(organization_id, service_id)` - Delete query config

> [!CAUTION]

> Production Warning: Write operations can create billable resources, modify running services, or delete infrastructure. Always use `CLICKHOUSE_CLOUD_READONLY=true` in production unless infrastructure changes are specifically required.

💡 Usage Examples

📊 Database Operations Examples

Safe Analysis Mode

# With CLICKHOUSE_READONLY=true (recommended for production)
# Only analytical queries are allowed

# Explore database structure
databases = list_databases()
print(f"Available databases: {[db['name'] for db in databases]}")

# Get detailed table information
tables = list_tables("my_database")
for table in tables:
    print(f"Table: {table['name']}, Rows: {table['total_rows']}")

# Execute analytical queries safely
result = run_query("""
    SELECT 
        date_trunc('day', timestamp) as day,
        count(*) as events,
        avg(value) as avg_value
    FROM my_table 
    WHERE timestamp >= '2024-01-01'
    GROUP BY day
    ORDER BY day
""")

# These queries would be blocked in readonly mode:
# run_query("DROP TABLE my_table")  # ❌ Blocked
# run_query("INSERT INTO my_table VALUES (1)")  # ❌ Blocked
# run_query("UPDATE my_table SET value = 0")  # ❌ Blocked

Full Access Mode

# With CLICKHOUSE_READONLY=false (development only)
# All SQL operations are allowed

# Data modification operations
run_query("""
    CREATE TABLE test_table (
        id UInt32,
        name String,
        created_at DateTime
    ) ENGINE = MergeTree()
    ORDER BY id
""")

run_query("INSERT INTO test_table VALUES (1, 'test', now())")
run_query("UPDATE test_table SET name = 'updated' WHERE id = 1")

☁️ Cloud Management Examples

Monitoring Mode (Safe)

# With CLICKHOUSE_CLOUD_READONLY=true (recommended for production)
# Only monitoring and inspection operations

# Monitor organization resources
orgs = cloud_list_organizations()
for org in orgs:
    services = cloud_list_services(org['id'])
    print(f"Organization: {org['name']}, Services: {len(services)}")
    
    # Get service metrics
    for service in services:
        metrics = cloud_get_service_metrics(org['id'], service['id'])
        print(f"Service {service['name']} metrics: {metrics}")

# Monitor costs and usage
usage = cloud_get_usage_cost(
    organization_id="org-123",
    from_date="2024-01-01",
    to_date="2024-01-31"
)
print(f"Monthly cost: ${usage['total_cost']}")

# Audit recent activities
activities = cloud_list_activities(
    organization_id="org-123",
    from_date="2024-01-01T00:00:00Z"
)
print(f"Recent activities: {len(activities)} events")

# These operations would be blocked in readonly mode:
# cloud_create_service(...)  # ❌ Blocked
# cloud_delete_service(...)  # ❌ Blocked  
# cloud_update_service_state(...)  # ❌ Blocked

Infrastructure Management (Full Access)

# With CLICKHOUSE_CLOUD_READONLY=false (use with caution)
# All infrastructure operations allowed

# Create a production service with full configuration
service = cloud_create_service(
    organization_id="org-123",
    name="analytics-prod",
    provider="aws",
    region="us-east-1",
    tier="production",
    min_replica_memory_gb=32,
    max_replica_memory_gb=256,
    num_replicas=3,
    idle_scaling=True,
    idle_timeout_minutes=10,
    ip_access_list=[
        {"source": "10.0.0.0/8", "description": "Internal network"},
        {"source": "203.0.113.0/24", "description": "Office network"}
    ]
)

# Start the service and monitor status
cloud_update_service_state(
    organization_id="org-123",
    service_id=service['id'],
    command="start"
)

# Set up automated backups
cloud_update_backup_configuration(
    organization_id="org-123",
    service_id=service['id'],
    backup_period_in_hours=24,
    backup_retention_period_in_hours=168,  # 7 days
    backup_start_time="02:00"
)

🔄 Safe Combined Workflow Example

# Production-safe configuration for monitoring and analysis
# CLICKHOUSE_READONLY=true + CLICKHOUSE_CLOUD_READONLY=true

# 1. Monitor existing cloud infrastructure
orgs = cloud_list_organizations()
org_id = orgs[0]['id']

services = cloud_list_services(org_id)
active_services = [s for s in services if s['state'] == 'running']
print(f"Active services: {len(active_services)}")

# 2. Analyze data from running services
for service in active_services:
    # Check service health
    metrics = cloud_get_service_metrics(org_id, service['id'])
    
    # Analyze data (read-only queries)
    if service['endpoints']:
        # Connect to database (would use service endpoint)
        result = run_query("""
            SELECT 
                database,
                table,
                sum(rows) as total_rows,
                sum(bytes_on_disk) as disk_usage
            FROM system.parts
            WHERE active = 1
            GROUP BY database, table
            ORDER BY total_rows DESC
            LIMIT 10
        """)
        
        print(f"Top tables in {service['name']}: {result}")

# 3. Generate usage report
usage = cloud_get_usage_cost(
    organization_id=org_id,
    from_date="2024-01-01",
    to_date="2024-01-31"
)

activities = cloud_list_activities(org_id)
recent_changes = [a for a in activities if 'create' in a.get('action', '').lower()]

print(f"""
Monthly Report:
- Total Cost: ${usage.get('total_cost', 'N/A')}
- Active Services: {len(active_services)}
- Recent Infrastructure Changes: {len(recent_changes)}
""")

🔧 Development

Local Development Setup

1. Start ClickHouse for testing:

cd test-services
   docker compose up -d

2. Create environment file:

cat > .env << EOF
   # Database configuration (development mode)
   CLICKHOUSE_HOST=localhost
   CLICKHOUSE_PORT=8123
   CLICKHOUSE_USER=default
   CLICKHOUSE_PASSWORD=clickhouse
   CLICKHOUSE_SECURE=false
   CLICKHOUSE_READONLY=false
   
   # Cloud configuration (optional, safe mode)
   CLICKHOUSE_CLOUD_KEY_ID=your-key-id
   CLICKHOUSE_CLOUD_KEY_SECRET=your-key-secret
   CLICKHOUSE_CLOUD_READONLY=true
   EOF

3. Install and run:

uv sync                               # Install dependencies
   source .venv/bin/activate            # Activate virtual environment
   mcp dev chmcp/mcp_server.py          # Start for testing
   # OR
   python -m chmcp.main                 # Start normally

Testing Safety Features

# Test read-only database mode
CLICKHOUSE_READONLY=true python -m chmcp.main

# Test cloud monitoring mode  
CLICKHOUSE_CLOUD_READONLY=true python -m chmcp.main

# Test full access mode (development only)
CLICKHOUSE_READONLY=false CLICKHOUSE_CLOUD_READONLY=false python -m chmcp.main

Project Structure

chmcp/
├── __init__.py                 # Package initialization
├── main.py                     # Entry point
├── mcp_env.py                  # Database environment configuration
├── mcp_server.py              # Main server + database tools (3 tools)
├── cloud_config.py            # Cloud API configuration
├── cloud_client.py            # HTTP client for Cloud API
└── cloud_tools.py             # Cloud MCP tools (50+ tools)

Running Tests

uv sync --all-extras --dev              # Install dev dependencies
uv run ruff check .                     # Run linting
docker compose up -d                    # Start test ClickHouse
uv run pytest tests                     # Run tests

🐛 Troubleshooting

📊 Database Connection Issues

Problem: Can't connect to ClickHouse database

• ✅ Verify `CLICKHOUSE_HOST`, `CLICKHOUSE_USER`, and `CLICKHOUSE_PASSWORD`
• ✅ Test network connectivity: `telnet your-host 8443`
• ✅ Check firewall settings allow connections on the specified port
• ✅ For SSL issues, try setting `CLICKHOUSE_VERIFY=false`
• ✅ Ensure database user has appropriate SELECT permissions

Problem: SSL certificate verification fails

# Temporarily disable SSL verification
CLICKHOUSE_VERIFY=false
CLICKHOUSE_SECURE=false  # Use HTTP instead of HTTPS
CLICKHOUSE_PORT=8123     # HTTP port instead of 8443

Problem: Queries are being blocked

• ✅ Check if `CLICKHOUSE_READONLY=true` is preventing write operations
• ✅ For development, temporarily set `CLICKHOUSE_READONLY=false`
• ✅ Review query for prohibited operations (INSERT, UPDATE, DELETE, CREATE, DROP)
• ✅ Use SHOW, DESCRIBE, EXPLAIN, or SELECT queries instead

☁️ Cloud API Issues

Problem: Cloud tools not working

• ✅ Verify `CLICKHOUSE_CLOUD_KEY_ID` and `CLICKHOUSE_CLOUD_KEY_SECRET` are correct
• ✅ Check API key permissions in ClickHouse Cloud Console
• ✅ Ensure API key is active and not expired
• ✅ For SSL issues, try setting `CLICKHOUSE_CLOUD_SSL_VERIFY=false`

Problem: "Operation not permitted" errors

• ✅ Check if `CLICKHOUSE_CLOUD_READONLY=true` is blocking write operations
• ✅ For infrastructure management, set `CLICKHOUSE_CLOUD_READONLY=false`
• ✅ Verify API key has sufficient permissions for the requested operation
• ✅ Review operation type: monitoring operations work in readonly mode, management operations require write access

Problem: "Organization not found" errors

• ✅ List organizations first: `cloud_list_organizations()`
• ✅ Verify your API key has access to the organization
• ✅ Check that you're using the correct organization ID format

🔧 General Issues

Problem: Tools missing in Claude

• ✅ Database tools require database configuration (`CLICKHOUSE_HOST`, etc.)
• ✅ Cloud tools require API configuration (`CLICKHOUSE_CLOUD_KEY_ID`, etc.)
• ✅ Check Claude Desktop configuration file syntax
• ✅ Restart Claude Desktop after configuration changes
• ✅ Verify `uv` path is absolute in configuration

Problem: Safety features not working as expected

• ✅ Confirm environment variables are properly set: `echo $CLICKHOUSE_READONLY`
• ✅ Check boolean values are strings: `"true"` not `true` in JSON config
• ✅ Restart the MCP server after changing readonly settings
• ✅ Test with simple operations first to verify behavior

Problem: Import errors or missing dependencies

# Reinstall with latest dependencies
uv sync --force
# Core dependencies with force reinstall
pip install . --force-reinstall

# With development dependencies
pip install ".[dev]" --force-reinstall

# With all optional dependencies
pip install ".[dev,test,docs]" --force-reinstall

# Editable install with force reinstall
pip install -e ".[dev]" --force-reinstall

🔒 Safety Configuration Troubleshooting

Problem: Want to enable write operations temporarily

# For database operations
export CLICKHOUSE_READONLY=false
# For cloud operations  
export CLICKHOUSE_CLOUD_READONLY=false
# Restart MCP server

Problem: Accidentally enabled write mode in production

# Immediately disable write operations
export CLICKHOUSE_READONLY=true
export CLICKHOUSE_CLOUD_READONLY=true
# Restart MCP server
# Review audit logs: cloud_list_activities()

Problem: Unclear which operations are blocked

• ✅ Database readonly mode blocks: INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, TRUNCATE
• ✅ Database readonly mode allows: SELECT, SHOW, DESCRIBE, EXPLAIN, WITH (read-only)
• ✅ Cloud readonly mode blocks: create_*, update_*, delete_*, start/stop services
• ✅ Cloud readonly mode allows: list_*, get_*, metrics, monitoring, analytics

📄 License

This project is licensed under the Apache License 2.0. See the LICENSE file for details.

**Developed by Badr Ouali**