mcp-clickhouse: giving an agent a read-only window onto a ClickHouse cluster

Integers above 2^53 come back as strings, and that is the point

The first thing the documentation explains, before listing a single tool, is how this server encodes results. ClickHouse tool responses are JSON-encoded strings, and integers outside the range from -9007199254740991 to 9007199254740991 are returned as decimal strings to preserve exact values in JavaScript clients. This applies to query rows and to integer table metadata. Safe-range integers and booleans keep their JSON types.

That single decision tells you what kind of project this is. A generic MCP SQL tool would return a number and let the client library parse it, and in JavaScript the parse would silently round. If you have a table of identifiers stored as UInt64, and the agent asks for the row with a specific identifier, the tool would report a value that does not exist in the table, or the agent would query for a different row than the one it just read. The rounding is invisible, and it propagates into whatever the agent concludes.

So the server returns the exact value as a string, and the client has to deal with it. That is a real cost, and it is the right trade: a string that a client must parse correctly is better than a number that a client cannot represent.

The same reasoning shows up in the parameter direction. Exact integers outside JavaScript's safe range are passed as decimal strings, for example "18446744073709551615" with a UInt64 placeholder, and dates, timestamps and exact decimals can also be passed as strings with the corresponding ClickHouse type. The documentation also notes that parameter binding is not a privacy feature and does not reduce the number of vector values sent in a tool call.

run_query is read-only unless you decide otherwise

The query tool is called run_query, and it takes a query string plus an optional params object. The read-only default is set through an environment variable, CLICKHOUSE_ALLOW_WRITE_ACCESS, whose default value is false. Writes can be enabled explicitly if needed.

The framing matters. An MCP server that an agent can point at a production cluster is a server that will eventually be asked to run something destructive, either because the agent decided to or because a prompt injection in a table cell told it to. Defaulting to read-only means the destructive case requires a human to change a configuration value, which is a moment where somebody can think about it.

The tool also accepts DESCRIBE (<query>) and EXPLAIN ESTIMATE <query>, and the documentation describes both as optional ways to inspect a query's result schema or its estimated reads.

DESCRIBE is the one worth understanding properly. It returns the same output-column metadata as DESCRIBE TABLE:

DESCRIBE (SELECT user, sum(amt) FROM events WHERE ts > now() - INTERVAL 30 DAY GROUP BY user)

and the point of running it first is that ClickHouse has to analyse the query to answer, so analysis errors surface there, with ClickHouse's own message, instead of part way through execution. The documentation gives the two failure cases as examples, an unknown expression identifier and an unknown table expression identifier.

EXPLAIN ESTIMATE returns the parts, rows and marks the query would read, one row per table, and the documentation says this is what separates a primary key lookup from a full scan. The example is a count against a single id.

The documentation is careful to add the limits of these checks. A query that describes cleanly can still fail when it runs, on a memory limit or a remote server error, and it says nothing about cost.

Parameter binding avoids SQL injection, with a list of exceptions

The params object exists so values do not have to be interpolated into SQL text, and the documentation is precise about how it works and where it breaks.

The placeholders use ClickHouse's own {name:Type} syntax, and the instruction is to use them without quoting, keeping the opening brace, name and colon adjacent as in {id:UInt32}. Spaces after the colon and within the type are supported, as in {id: UInt32} and {amount:Decimal(18, 4)}. For compatibility across supported driver versions, names should start with a letter and may contain only letters, digits or the _ character.

A worked example:

{
  "query": "SELECT {id:UInt32} AS id, {name:String} AS name",
  "params": {"id": 13, "name": "O'Reilly"}
}

The O'Reilly in that example is the point. A name with an apostrophe is exactly what breaks naive string interpolation, and the placeholder form handles it without escaping.

What is not supported is worth listing too. Python-style %s or %(name)s formatting is not supported, and neither is the driver's $name$ raw binary parameters. Calls with only query still work, and omitting params, passing null, or passing an empty object leaves the query unbound.

Parameter values can be JSON strings, numbers, booleans, null or arrays, provided they match the declared ClickHouse type. A null goes with a Nullable(...) type. Vectors bind as one array, for example a vector of Float32 as an array of three values.

Then come the exceptions, and they are the kind that only show up in production. Nulls inside arrays depend on the installed driver: they work with clickhouse-connect 1.8.0 but fail with the supported minimum of 1.0.0. And JSON lists and objects cannot bind to ClickHouse Tuple and Map types at all.

There is one more rule that is stricter than it looks. With non-empty params, a query carrying many unterminated {name: placeholder starts is rejected, including placeholder-like text in comments or string literals. That is a deliberate trade, because a naive placeholder scan would mangle a query that merely mentions the syntax in a comment.

list_tables is paginated with single-use tokens

The schema discovery tool is list_tables, and it takes a required database plus a set of optional filters.

The filters are like and not_like, which apply LIKE or NOT LIKE to table names. The pagination parameters are page_token, a single-use token returned by a previous call and retained for up to one hour, and page_size, an integer defaulting to 50 that must be greater than 0. There is also include_detailed_columns, a boolean defaulting to true, which when false omits column metadata for lighter responses while keeping the full create_table_query.

That last option is a thoughtful piece of API design. A table list with full column metadata for every table in a large database is a lot of tokens, and the tool gives the caller a way to ask for names and DDL without the column dump. Keeping create_table_query even in the light mode means the caller can still learn the schema by reading the DDL if it wants to.

The response shape has three fields. The tables array holds the table objects for the current page. The next_page_token is a single-use value to pass back before it expires to fetch the next page, or null when there are no more tables. The total_tables field is the count of tables matching the supplied filters.

The token being single-use and expiring after an hour is the right shape for a cursor. It means a client that crashes mid-pagination cannot resume at the same point with a stale token, and it means the server is not keeping unbounded state on behalf of the client.

The third tool is list_databases, which lists all databases on the cluster. That is the entry point, and it connects to a feature in the next section: the server instructions tell the agent to look for an AGENTS database and, if it finds one, read the canonical AGENTS.ROOT through run_query before writing analytical SQL.

The AGENTS database is guidance for the agent, not an automatic fetch

This is a small feature with a large idea behind it, and the documentation is careful about its limits.

The server instructions suggest reading the Agents Schema project's canonical AGENTS.ROOT through run_query when list_databases reveals an AGENTS database, before writing analytical SQL. The Agents Schema is a dbt Labs project whose canonical AGENTS.ROOT the server instructions point the agent at.

What the documentation insists on is the boundary. This is guidance for the agent: no metadata is fetched automatically, tool results are unchanged, and existing ClickHouse permissions still apply. If AGENTS.ROOT is missing or inaccessible, normal discovery and queries remain available.

That is a well-judged design. Automatically fetching and injecting a schema document into every conversation would cost tokens on every call and would create a hard dependency on a table that may not exist. Making it a hint in the server instructions means the agent decides, per task, whether the extra query is worth it.

The same reasoning explains the other two checks the server offers. DESCRIBE gives the agent the columns and types of a result without running it. EXPLAIN ESTIMATE gives it the parts, rows and marks it would read. Neither changes what run_query does, and the documentation says parameterised queries use the same write protection, timeouts, cancellation and JSON result encoding as other queries.

The protocol layer handles two generations of client at once

The server implements the MCP specification revision 2026-07-28, and it also supports legacy initialize handshakes from 2024-11-05 through 2025-11-25. Modern clients use sessionless requests and server/discover, and existing clients can continue to negotiate the legacy protocol.

The note underneath explains how that works in practice. HTTP requests without an MCP-Protocol-Version header are routed through legacy handling so clients from before 2025-06-18 can continue to connect, and the 2026-07-28 specification permits this behavior on servers that support those clients. Modern clients should send the header on every POST request.

This is a deliberate choice, and it has a cost. Carrying two protocol generations means the server has two code paths to maintain and two sets of client behaviours to test, and the spec only allows it as a courtesy. The benefit is that nobody has to upgrade their assistant at the same moment the server does, which for an MCP server published by the database vendor is a real consideration, since the clients are third-party applications the user may not control.

The repository contents reflect a real project. There is a fastmcp.json and a server.json, which are the configuration files for the two main client ecosystems, an example_middleware.py, a Dockerfile, an AGENTS.md and a CLAUDE.md for coding assistants, a CHANGELOG.md, a test-services directory and a tests directory, and a .python-version file.

The package metadata names the Python floor and the dependency set. It requires Python 3.10 or newer, and the classifiers list 3.10 through 3.14. The dependencies are fastmcp pinned to a narrow range, clickhouse-connect for the driver, python-dotenv, truststore for TLS, cachetools, uvicorn and simplejson. There is an optional chdb extra and a dev extra with ruff, pytest and pytest-asyncio, and the build backend is hatchling.

The Docker image is a two stage build with a pinned licence label

The Dockerfile is worth reading because it shows the deployment shape the project expects, which is a stateless server in front of a ClickHouse cluster.

The build stage uses a uv pre-installed Python image on bookworm slim, sets the working directory to /app, enables bytecode compilation and sets the link mode to copy rather than symlink, with the comment explaining that it is copying from the cache because the cache is a mounted volume. It installs git and build-essential through apt with cache mounts, then runs uv sync with the lockfile bound in as a read-only mount, using the --locked flag so the dependency set matches uv.lock exactly.

The layering is deliberate. The dependency install happens before the source is copied, with the comment explaining that installing separately from dependencies allows optimal layer caching. The second sync adds the project itself.

The production stage then uses a plain python:3.13-slim-bookworm image and copies the virtual environment across from the builder. The path is adjusted so the executables in the virtual environment come first, which is what makes the mcp-clickhouse entry point available on the PATH.

The image carries OCI labels, and three of them are worth naming. There is the source label pointing at the repository, a description label reading MCP server for ClickHouse, and a licence label of Apache-2.0. There is also a label with the io.modelcontextprotocol.server.name key, whose value is io.github.ClickHouse/mcp-clickhouse, and that same identifier appears in the README as the mcp-name comment, which is how the server identifies itself in the registry that MCP clients browse.

The package metadata matches the same licence, Apache-2.0 with the licence file declared in the manifest, and the entry point is mcp_clickhouse.main:main, exposed as a console script named mcp-clickhouse.

Where an agent over your cluster is the wrong idea

Three boundaries are worth naming.

The first is the write path. Read-only mode is the default, and enabling writes is a single environment variable change. An agent with write access to a ClickHouse cluster can run ALTER TABLE, DROP TABLE and INSERT, and in a cluster where data is replicated those operations are not local. If your workflow does not need the agent to modify data, leaving CLICKHOUSE_ALLOW_WRITE_ACCESS at its default is the cheapest and most durable protection available, and it is better than a prompt telling the agent not to.

The second is the parameter binding and driver version coupling. Nulls inside arrays work with clickhouse-connect 1.8.0 and fail with the supported minimum of 1.0.0, and JSON lists and objects cannot bind to Tuple and Map types at all. If your queries use tuple columns, the parameter path will not carry them, and you will be back to string interpolation with its quoting problems. That is a case where the safe path is simply unavailable and you need to know that before an agent hits it.

The third is that this is a ClickHouse server, not a database-agnostic one. The tools are list_databases, list_tables and run_query against a ClickHouse cluster, the parameter syntax is ClickHouse's {name:Type} rather than the ANSI or the driver's native form, and the query guidance leans on ClickHouse's own DESCRIBE and EXPLAIN ESTIMATE output. If your data lives in more than one engine, you will need a server per engine, and the agent will be reasoning about two different SQL dialects and two sets of tool descriptions.

There is also a privacy point that the documentation makes explicitly rather than by implication. Parameter values stay out of the MCP server's normal SQL log messages, but they remain in MCP tool arguments and may appear in backend errors, and ClickHouse 26.3.20.7 substitutes values into the query text in system.query_log, system.processes and system.text_log. The documentation says parameter binding is not a privacy feature. If a value must not be logged by ClickHouse, the way to achieve that is a ClickHouse setting, not a different parameter style.

Editorial conclusion

mcp-clickhouse fits a team that has a ClickHouse cluster and an agent-based workflow where an analyst or a bot needs to explore the schema and answer questions about the data, and it fits them best if they use the DESCRIBE and EXPLAIN ESTIMATE checks the server makes available rather than letting the agent guess. It is the wrong choice if you want a general purpose SQL MCP server that works against any database, and it is the wrong choice if you need the agent to write to the cluster, since read-only mode is the default and enabling writes is an explicit decision you have to make with the environment variable. Before adopting it, read the integer encoding rules, because a client that parses the JSON responses naively will silently corrupt any value above 9007199254740991, and check the parameter binding caveats for your driver version before you trust the placeholders.

Frequently asked questions

Official sources

1. ClickHouse/mcp-clickhouse on GitHub
2. Issues
3. License: Apache-2.0
4. README
5. Releases