Skip to main content
Glama
sepfazeli

clickhouse-mcp-server

by sepfazeli

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

list_tables

Lists all tables in the database with engine type, row count, and size.

run_query

Executes a read-only SQL query with validation, caching, and a 1000-row limit.

describe_table

Returns column names, types, defaults, and a 3-row sample for a table.

aggregate

Builds and runs a time-windowed aggregation query without writing raw SQL.

cache_stats

Shows query cache statistics.

clear_cache

Clears all cached query results.

Related MCP server: clickhouse

Setup

Prerequisites

Install

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

Configure

Copy .env.example to .env and fill in your connection details:

cp .env.example .env
CLICKHOUSE_URL=http://localhost:8123
CLICKHOUSE_USER=default
CLICKHOUSE_PASSWORD=
CLICKHOUSE_DATABASE=default

For a local instance via Docker:

docker run -d -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server

Run

Stdio transport (default — for Claude Desktop and local MCP clients):

npm run build && npm start

# Development
npm run dev

HTTP 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:http

The HTTP server exposes:

  • POST /mcp — MCP Streamable HTTP endpoint (supports SSE streaming)

  • GET /mcp — SSE stream for server-initiated notifications

  • GET /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.js

Tests

npm test

51 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 whitelist

  • maxRowLimit — 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, event

  • tool name, query text (truncated to 500 chars)

  • durationMs, rowCount, cacheHit

  • error message 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 aggregate tool 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 tools
aggregateAggregateA
Read-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.

ParametersJSON Schema
NameRequiredDescriptionDefault
tableYesTable name to aggregate from.
metricYesColumn to aggregate (e.g. 'revenue', 'count').
funcYesAggregation function.
timeColumnNoDate/DateTime column to group by time window. Omit for a single aggregate.
intervalNoTime bucketing interval. Required if timeColumn is set.
filterNoOptional WHERE clause condition (without the WHERE keyword).
groupByNoOptional additional column to group by.

TDQS

A4.3/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint=true and destructiveHint=false. Description adds context that it runs a query without writing SQL, which aligns with safety profile. No contradictions.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Two sentences with front-loaded core purpose and no excess. Every sentence adds meaningful information.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness3/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Covers main parameters (table, metric, func, timeColumn, interval) but omits filter and groupBy in description despite schema having descriptions. No output schema, so return format is unaddressed, which is a minor gap for a moderately complex tool.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema coverage is 100%, so baseline is 3. Description adds value by explaining the usage pattern (specify table, metric, func, optional time column) and aggregation function enum clarity, though it mostly reiterates schema.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

Description clearly states it builds and runs a time-windowed aggregation query without raw SQL, with a specific verb and resource. It distinguishes from sibling tools like run_query which handles raw SQL.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

Explicitly states when to use (in-place aggregation) and explains required parameters. Does not explicitly mention when not to use or alternatives, but context (sibling tools) implies partial guidance.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

cache_statsCache StatsA
Read-only

Show query cache statistics: entry count, max entries, and TTL.

ParametersJSON Schema
NameRequiredDescriptionDefault

No parameters

TDQS

A4/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint=true and destructiveHint=false, so the agent knows this is a safe read operation. The description adds minor detail about the statistics shown but does not disclose additional behavioral traits such as performance impact or data freshness.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single concise sentence that front-loads the purpose (Show query cache statistics) and lists the outputs. No redundant or unnecessary words.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the lack of parameters, presence of read-only annotations, and no output schema, the description is largely sufficient. However, it could briefly mention that the cache statistics are from the current session or indicate data sources.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema has zero parameters with 100% schema description coverage, so no parameter documentation is needed. The description adds value by specifying what the tool returns, but since there are no parameters, a high score is appropriate.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the verb 'Show' and the resource 'query cache statistics', listing specific metrics (entry count, max entries, TTL). This distinguishes it from siblings like clear_cache (destructive) and run_query (executes queries).

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description implies usage for viewing cache state but does not explicitly state when to use this tool versus siblings like clear_cache or describe_table. No alternatives or exclusions are provided.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

clear_cacheClear CacheA
Destructive

Clear all cached query results.

ParametersJSON Schema
NameRequiredDescriptionDefault

No parameters

TDQS

A3.7/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare destructiveHint=true, and the description aligns by stating 'clear all'. However, it does not elaborate on side effects or what exactly is destroyed beyond cached query results, providing minimal additional context.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

A single sentence of five words efficiently conveys the tool's purpose with no unnecessary information, earning the highest score for conciseness.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a zero-parameter, destructive tool with clear annotations, the description sufficiently explains the action. No output schema exists, but success or failure is implicit. The completeness is adequate for the tool's simplicity.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema has no parameters, and schema description coverage is 100%. The description adds no parameter information, as none exists, resulting in a baseline score of 3.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description 'Clear all cached query results' uses a specific verb (Clear) and resource (cached query results), clearly indicating the action. It distinguishes from sibling tools like cache_stats and run_query, which have different purposes.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

No guidance on when to use this tool versus alternatives like cache_stats for viewing cache or run_query for executing queries. The description does not state prerequisites or conditions for clearing cache.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

describe_tableDescribe TableA
Read-only

Get the schema of a specific ClickHouse table: column names, types, default expressions, and a sample of up to 3 rows.

ParametersJSON Schema
NameRequiredDescriptionDefault
tableYesThe table name to describe.

TDQS

A4.1/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Adds behavioral context beyond annotations by describing the exact return content (columns, types, defaults, sample rows). Annotations already indicate read-only, non-destructive nature, so description complements well.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Single sentence, information-dense, front-loaded with key details. No unnecessary words.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a simple tool with 1 parameter and no output schema, the description sufficiently covers inputs and outputs. Annotations and schema handle the rest.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, so baseline 3. Description restates 'specific ClickHouse table' but adds no extra meaning beyond schema's 'The table name to describe.'

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

Clearly states verb ('get'), resource ('schema of a specific ClickHouse table'), and specifics (column names, types, default expressions, sample rows). Distinguishes from siblings like list_tables and run_query.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

Implies usage for getting table schema but provides no explicit when-to-use or alternatives, leaving room for ambiguity with sibling tools.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

list_tablesList TablesA
Read-only

List all tables in the connected ClickHouse database with their engine type and row count.

ParametersJSON Schema
NameRequiredDescriptionDefault

No parameters

TDQS

A4.1/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint=true and destructiveHint=false. The description adds the specific return fields (engine type, row count) but lacks additional behavioral context like performance implications or ordering.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single, front-loaded sentence that conveys the purpose and output without any superfluous content.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a simple list tool with no parameters and no output schema, the description sufficiently covers what the tool does and what it returns. No additional information is needed.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The tool has zero parameters, so the description naturally adds no parameter details. Schema coverage is 100%, and the baseline for zero parameters is 4.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the action (list), resource (tables), and returned information (engine type and row count), distinguishing it from sibling tools like describe_table or run_query.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description does not provide explicit guidance on when to use this tool versus alternatives, such as when a specific table's details are needed via describe_table. It only states what it does.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

run_queryRun QueryA
Read-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).

ParametersJSON Schema
NameRequiredDescriptionDefault
queryYesThe SQL query to execute. Must be a read-only statement.

TDQS

A4.2/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint=true and destructiveHint=false. The description adds important behavioral details: a 60-second cache and a 1000-row limit, which go beyond the annotations and provide useful constraints.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is two sentences long, with the main action front-loaded. Every sentence provides essential information without redundancy.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the tool has a single string parameter and no output schema, the description covers constraints (allowed statements, caching, row limits) adequately. It could optionally mention the return format, but the current level is sufficient for typical use.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema covers the only parameter 'query' with a description stating it must be read-only. The tool description adds further specificity by enumerating which statement types are allowed (SELECT, WITH, SHOW, etc.), thus adding meaning beyond the schema.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the tool executes read-only SQL queries against ClickHouse and lists specific allowed statement types (SELECT, WITH, SHOW, etc.), which is a specific verb+resource combination that distinguishes it from siblings like describe_table or list_tables.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description lists allowed statement types but does not explicitly guide when to use this tool versus its siblings (e.g., describe_table). The guidance is implicit rather than explicit.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

  1. 6 tool updatesv1.0.0
    • First observedaggregate
    • First observedcache_stats
    • First observedclear_cache
    • First observeddescribe_table
    • First observedlist_tables
    • First observedrun_query

TDQS

A4.2/5.0

Scored across 6 tools

Disambiguation5/5

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.

Naming Consistency5/5

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.

Tool Count5/5

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.

Completeness5/5

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

ActivityInactive
ResponsivenessNo issues

Related MCP Connectors

Related MCP Servers

  • A
    license
    A
    quality
    D
    maintenance
    Enables AI assistants to query and manage ClickHouse databases, supporting SELECT queries, DDL/DML statements, and metadata listing.
    5
    5 npm
    MIT
  • A
    license
    Not graded
    quality
    D
    maintenance
    Enables executing SQL queries, listing databases, and listing tables on a ClickHouse cluster through natural language.
    Apache 2.0