clickhouse-mcp-server
Provides read-only tools for exploring ClickHouse databases, including listing tables, running queries (with caching, validation, and row limits), describing table schemas, and building time-windowed aggregations.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@clickhouse-mcp-server list all tables in the database"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
ClickHouse MCP Server
An MCP (Model Context Protocol) server that gives AI agents safe, read-only access to a ClickHouse database. Connect it to Claude Desktop, the MCP Inspector, or any MCP-compatible client and let the model explore your tables, run queries, and inspect schemas — without risking writes or mutations.
Tools
Tool | Description |
| Lists all tables in the database with engine type, row count, and size. |
| Executes a read-only SQL query with validation, caching, and a 1000-row limit. |
| Returns column names, types, defaults, and a 3-row sample for a table. |
| Builds and runs a time-windowed aggregation query without writing raw SQL. |
| Shows query cache statistics. |
| Clears all cached query results. |
Related MCP server: shop-mcp
Setup
Prerequisites
Node.js 18+
A ClickHouse instance (local Docker or ClickHouse Cloud free tier)
Install
git clone https://github.com/sepfazeli/clickhouse-mcp-server.git
cd clickhouse-mcp-server
npm install
npm run buildConfigure
Copy .env.example to .env and fill in your connection details:
cp .env.example .envCLICKHOUSE_URL=http://localhost:8123
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=
CLICKHOUSE_DATABASE=defaultFor a local instance via Docker:
docker run -d -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-serverRun
Stdio transport (default — for Claude Desktop and local MCP clients):
npm run build && npm start
# Development
npm run devHTTP transport (for remote clients or multi-session use):
npm run start:http # default port 3001
npm run start:http -- 8080 # custom port
# Development
npm run dev:httpThe HTTP server exposes:
POST /mcp— MCP Streamable HTTP endpoint (supports SSE streaming)GET /mcp— SSE stream for server-initiated notificationsGET /health— health check
Claude Desktop Configuration
Add to your Claude Desktop config (~/Library/Application Support/Claude/claude_desktop_config.json):
{
"mcpServers": {
"clickhouse": {
"command": "node",
"args": ["/absolute/path/to/clickhouse-mcp-server/dist/index.js"],
"env": {
"CLICKHOUSE_URL": "http://localhost:8123",
"CLICKHOUSE_USER": "default",
"CLICKHOUSE_PASSWORD": "",
"CLICKHOUSE_DATABASE": "default"
}
}
}
}Test with MCP Inspector
npx @modelcontextprotocol/inspector node dist/index.jsTests
npm test51 unit tests covering query validation, auth scoping, and cache behavior.
Auth Scoping
The server supports per-API-key permission scopes via the MCP_AUTH_SCOPES and MCP_API_KEY environment variables. This lets you restrict which tables, columns, and row limits are available per caller.
MCP_API_KEY=analyst-key-1
MCP_AUTH_SCOPES={"analyst-key-1":{"allowedTables":["events","pageviews"],"maxRowLimit":500},"intern-key":{"deniedTables":["billing","secrets"],"allowedColumns":{"users":["id","name"]}}}Scope options:
allowedTables— whitelist of accessible tables (deny-by-default)deniedTables— blacklist of inaccessible tables (allow-by-default)allowedColumns— per-table column whitelistmaxRowLimit— override the default 1000-row cap (can only go lower)
When no MCP_AUTH_SCOPES is set, the server runs in open mode with full read access.
Query Caching
Identical queries are cached for 60 seconds by default (configurable via CACHE_TTL_MS). The cache holds up to 100 entries and uses FIFO eviction. Use the cache_stats and clear_cache tools to inspect and manage it.
Observability
All tool calls, query executions, and errors are logged as structured JSON to stderr. Each log entry includes:
timestamp,level,eventtoolname,querytext (truncated to 500 chars)durationMs,rowCount,cacheHiterrormessage on failures
Set LOG_LEVEL to debug, info, warn, or error (default: info).
Example log line:
{"timestamp":"2025-01-15T10:30:00.000Z","level":"info","event":"query_exec","query":"SELECT count() FROM events","durationMs":42,"rowCount":1,"cacheHit":false}Design Decisions
Read-only enforcement. Queries are validated before execution by stripping comments, rejecting multi-statement queries (semicolons), and checking that the statement starts with SELECT, WITH, SHOW, DESCRIBE, EXISTS, or EXPLAIN. Dangerous DDL/DML keywords like INSERT, DROP, ALTER are caught when paired with their target keywords (e.g., DROP TABLE). This is defense-in-depth — ideally the ClickHouse user itself should also have read-only grants.
Why CTEs are allowed. WITH (Common Table Expressions) are essential for non-trivial analytical queries. Blocking them would cripple the tool for real ClickHouse workloads. The same validation that applies to SELECT applies to CTEs.
Row limit: 1000 max, auto-appended. If a query omits LIMIT, the server appends LIMIT 1000. If a query specifies a limit above 1000, it's rejected. This prevents agents from accidentally pulling massive result sets into context. The limit is deliberately conservative — large results aren't useful for an LLM anyway. Auth scopes can lower this per-key.
Query timeout: 30 seconds. Applied at both the client level (request_timeout) and the ClickHouse engine level (max_execution_time). Long-running queries are killed server-side rather than leaving connections hanging.
Table name sanitization. describe_table and aggregate strip non-alphanumeric/underscore characters from identifier parameters. A mismatch between the sanitized and original name is rejected outright rather than silently corrected.
Cache TTL: 60 seconds. Short enough that agents see reasonably fresh data, long enough to absorb the repeated identical queries that agents tend to issue (e.g., re-checking a count before and after an explanation).
HTTP transport uses Streamable HTTP, not legacy SSE. The MCP SDK's StreamableHTTPServerTransport supports both SSE streaming and direct HTTP responses per the latest MCP spec. The deprecated SSEServerTransport is not used.
Known Limitations
Validation is pattern-based, not a real SQL parser. The read-only check uses keyword matching after stripping comments. A sufficiently creative query could theoretically bypass it, which is why the ClickHouse user should also be restricted at the database level.
Results are buffered in memory. For very large result sets (close to the 1000-row limit with wide rows), this could use significant memory. True streaming would require changes to how MCP tool results are structured.
Auth scoping is env-var-based. For production multi-tenant use, a proper auth middleware with JWT or API key lookup would be more appropriate than a single JSON env var.
No query plan analysis. The
aggregatetool builds queries from parameters but doesn't analyze whether the resulting query will be efficient (e.g., whether the time column is indexed).
License
MIT
Available Tools
6 toolsaggregateAggregateARead-only
Build and run a time-windowed aggregation query without writing raw SQL. Specify a table, metric column, aggregation function, and optional time column with interval for grouping.
Inputs: table (required), metric (required), func (required), timeColumn, +3 more.
cache_statsCache StatsARead-only
Show query cache statistics: entry count, max entries, and TTL.
Inputs: no named parameters.
clear_cacheClear CacheADestructive
Clear all cached query results.
Inputs: no named parameters.
describe_tableDescribe TableARead-only
Get the schema of a specific ClickHouse table: column names, types, default expressions, and a sample of up to 3 rows.
Inputs: table (required).
list_tablesList TablesARead-only
List all tables in the connected ClickHouse database with their engine type and row count.
Inputs: no named parameters.
run_queryRun QueryARead-only
Execute a read-only SQL query against ClickHouse. Only SELECT, WITH (CTE), SHOW, DESCRIBE, EXISTS, and EXPLAIN statements are allowed. Results are cached for 60s. A maximum row limit of 1000 is enforced (or lower if scoped).
Inputs: query (required).
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 6 tools
Each tool has a clear, distinct purpose: run_query for arbitrary SQL, aggregate for time-windowed aggregation, describe_table/list_tables for schema/table info, and cache_stats/clear_cache for cache management. No overlap or ambiguity.
All tool names follow a consistent snake_case verb_noun (or action_noun) pattern: 'list_tables', 'describe_table', 'run_query', 'clear_cache', 'aggregate', 'cache_stats'. No mixed conventions.
Six tools cover the core functionality of querying, schema exploration, and cache management without excess. The count is well-scoped for a read-only ClickHouse interface.
The tool set provides everything needed for querying and exploring a ClickHouse database: listing tables, describing schemas, running arbitrary read-only queries, a convenience aggregation function, and cache management. No obvious gaps for its intended use.
Maintenance
Related MCP Connectors
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Governed access to production AI-agent traces in an existing ClickHouse store.
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
Query Postgres, MySQL, SQL Server, Oracle, BigQuery, ClickHouse and Redshift from your AI client.
Related MCP Servers
- AlicenseAqualityDmaintenanceEnables AI agents to explore MySQL database schemas and execute read-only queries through a safe, MCP interface.6ISC
- AlicenseAqualityCmaintenanceEnables AI agents to safely explore and query a SQLite database in read-only mode, allowing them to inspect schema and run analytical SQL queries without risking data modification.3MIT
- FlicenseNot gradedqualityBmaintenanceProvides read-only database access for AI agents, enabling table listing, schema inspection, and filtered row queries through MCP tools.-
- AlicenseAqualityBmaintenanceEnables AI agents to inspect database schemas and run strictly read-only SQL queries with guardrails against destructive statements, multi-statement chaining, and unbounded scans. Supports SQLite natively and PostgreSQL optionally.484 PyPIMIT