clickhouse-mcp-server

ClickHouse MCP Server

English | 日本語

A remote MCP (Model Context Protocol) server that enables Claude to interact with ClickHouse databases through a secure web interface.

Overview

This project implements an MCP server that:

• Provides read-only access to ClickHouse databases
• Authenticates users via Google OAuth 2.0
• Implements secure communication using Server-Sent Events (SSE)
• Restricts access to whitelisted email addresses
• Works seamlessly with Claude's web interface

Features

• Secure Authentication: Google OAuth 2.0 with email whitelist
• Read-Only Access: Safe database queries without modification risks
• Session Management: UUID-based sessions with automatic cleanup
• CORS Protection: Restricted to Claude.ai origins
• Comprehensive Logging: Debug capabilities with optional protocol logging

Prerequisites

• Node.js 18+ and npm
• ClickHouse server instance
• Google Cloud Console account (for OAuth)
• Domain with HTTPS (Let's Encrypt recommended)
• Apache2 or similar reverse proxy

Installation

1. Clone the repository

   git clone https://github.com/yourusername/clickhouse-mcp-server.git
   cd clickhouse-mcp-server
   

2. Install dependencies

   npm install
   

3. Configure environment variables

   cp .env.example .env
   

   Edit .env with your configuration:

   • ClickHouse connection details
   • Google OAuth credentials
   • Security keys (generate secure random strings)
   • Allowed email addresses

4. Build the project

   npm run build
   

Google OAuth Setup

1. Go to Google Cloud Console
2. Create a new project or select existing
3. Enable Google+ API
4. Create OAuth 2.0 credentials:
   • Application type: Web application
   • Authorized redirect URIs: https://your-domain.com/mcp-clickhouse/oauth/callback
5. Copy Client ID and Client Secret to .env

Apache Configuration

1. Enable required modules

   sudo a2enmod proxy proxy_http proxy_wstunnel ssl
   

2. Add proxy configuration

   # MCP ClickHouse Server
   ProxyPass /mcp-clickhouse http://localhost:3001
   ProxyPassReverse /mcp-clickhouse http://localhost:3001
   
   # SSE Support
   <Location /mcp-clickhouse/sse>
       ProxyPass http://localhost:3001/mcp-clickhouse/sse
       ProxyPassReverse http://localhost:3001/mcp-clickhouse/sse
       Header set Cache-Control "no-cache"
       Header set X-Accel-Buffering "no"
   </Location>
   

3. Restart Apache

   sudo systemctl restart apache2
   

Running the Server

Development Mode

npm run dev

Production Mode

1. Using systemd (recommended)

   # Copy service file
   sudo cp config/mcp-clickhouse.service /etc/systemd/system/
   
   # Edit service file with your paths
   sudo nano /etc/systemd/system/mcp-clickhouse.service
   
   # Enable and start service
   sudo systemctl enable mcp-clickhouse
   sudo systemctl start mcp-clickhouse
   

2. Using PM2

   npm install -g pm2
   pm2 start dist/index.js --name mcp-clickhouse
   pm2 save
   pm2 startup
   

Configuration Details

Environment Variables

Variable	Description	Example
PORT	Server port	3001
CLICKHOUSE_HOST	ClickHouse server address	localhost
CLICKHOUSE_USER	Database username	default
CLICKHOUSE_PASSWORD	Database password	your-password
GOOGLE_CLIENT_ID	OAuth client ID	123456789.apps.googleusercontent.com
GOOGLE_CLIENT_SECRET	OAuth client secret	GOCSPX-...
JWT_SECRET	JWT signing key	Random 64+ character string
MCP_API_KEY	API authentication key	Random 64+ character string
ALLOWED_EMAILS	Comma-separated whitelist	user1@gmail.com,user2@gmail.com

Security Keys Generation

Generate secure random strings:

# Generate JWT_SECRET
openssl rand -hex 64

# Generate MCP_API_KEY
openssl rand -hex 32

Usage with Claude

1. Navigate to https://claude.ai
2. Add the MCP server:
   • URL: https://your-domain.com/mcp-clickhouse
3. Authenticate with Google when prompted
4. Use Claude to query your ClickHouse database

Example queries:

• "Show me the tables in the database"
• "Get the row count for the users table"
• "Analyze the data distribution in the events table"

Troubleshooting

Common Issues

1. Authentication fails

   • Verify Google OAuth credentials
   • Check redirect URI matches exactly
   • Ensure email is in ALLOWED_EMAILS

2. Connection refused

   • Check if service is running: sudo systemctl status mcp-clickhouse
   • Verify Apache proxy configuration
   • Check firewall settings

3. ClickHouse connection errors

   • Verify ClickHouse is running
   • Check connection credentials
   • Test with clickhouse-client

Logs

View logs:

# systemd logs
sudo journalctl -u mcp-clickhouse -f

# Application logs
tail -f /path/to/app/logs/*.log

Documentation

• MCP Tools Reference - Available MCP tools and ClickHouse query examples
• Protocol Communication Flow - Detailed OAuth and SSE communication flow with diagrams

Development

Project Structure

├── src/
│   ├── index.ts           # Main server & SSE handler
│   ├── auth/
│   │   └── oauth.ts       # OAuth implementation
│   └── server/
│       ├── config.ts      # Configuration management
│       ├── logger.ts      # Logging utilities
│       └── mcp-handler.ts # MCP protocol handler
├── config/                # Deployment configurations
├── docs/                  # Documentation
├── tests/                 # Test files
└── package.json

Testing

# Run tests
npm test

# Test SSE connection
node test-sse.cjs

Contributing

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

• Anthropic for the MCP specification
• ClickHouse for the amazing database
• The Node.js and TypeScript communities