Skip to content

ClickHouse plugin

Use ClickHouse to check the analytical data behind a user journey: telemetry, events, logs or usage records your application writes to ClickHouse. Queries retain column names and row values, so a reviewer sees the rows a step checked beside screenshots and command output. No clickhouse-client and no docker exec is needed: the plugin speaks the server’s HTTP interface.

ClickHouse is an optional plugin: it is not in the CLI download and not in the default selection. reflow init downloads it once for your platform from the release your CLI was installed from, checks it against that release’s manifest and installs it. Plan and apply never download.

Add it to an application that already has a plugin selection:

Terminal window
reflow init --upgrade ClickHouse --collection <collection-id>

The other selected plugins stay exactly as locked. A teammate’s device, or CI, installs it with plain reflow init, because the application’s lock names it.

Your ClickHouse server must be running and its HTTP interface (port 8123, or 8443 over HTTPS) reachable from the machine that runs Reflow. Save a private bindings.json:

{
"ch": {
"provider": "ClickHouse",
"configuration": {
"host": "127.0.0.1",
"port": 8123,
"database": "default",
"user": "test_user",
"password": "replace-me"
}
}
}
FieldMeaning
hostRequired. Host name or IP address, without scheme or port.
portHTTP interface port; 8123, or 8443 when secure is true.
secureUse HTTPS. Defaults to false.
databaseDatabase for unqualified table names; default when omitted.
userClickHouse user; default when omitted.
passwordThe user’s password.
passwordFileInstead of password: the absolute path of a private file holding only the password.
allowWritesAllow ch.execute. Defaults to false; query stays read-only.

Keep this file out of Git; credentials belong in the private connection. The user, password and database travel in request headers, never in a URL. Binding runs SELECT 1, so a wrong password or a missing database fails at once. A read-only user works for query and value when its profile sets readonly=2; a readonly=1 profile refuses the settings the plugin needs for exact values, so binding it fails with that reason. Redirects are never followed, so the password cannot reach another host.

Terminal window
chmod 600 /absolute/path/to/bindings.json
reflow target --group database-tests --collection <collection-id> --bindings /absolute/path/to/bindings.json
---
name: Check session events
providers:
ch: ClickHouse
---
### The test user's latest session recorded its tool calls
```rfl
ch.value sql="SELECT session_id FROM events WHERE user_id = 'zz-e2e-user' ORDER BY time DESC, session_id LIMIT 1" -> session_id
ch.query (
sql="SELECT count() AS n FROM events WHERE session_id = '${session_id}' AND name = 'tool_call'"
expect=json(
{"rows": 1, "json": [{"pointer": "/0/n", "greaterThanOrEqual": 1}]}
)
checkpoint="session-tool-calls"
)
```

The alias ch selects the private connection with the same name. ch.value returns the session id only if it is a safe token (letters, digits and . _ : / @ + -), so it can be single-quoted into the next statement; never splice free text, such as an input, into SQL.

ArgumentTypeMeaning
sqlstring, requiredOne SQL statement, run with the server’s readonly=1.
keystring, optionalA unique, non-null returned column used to identify and sort rows.
expectJSON text, optionalWhat the rows must hold; see Check the rows.

Every query and value statement runs with ClickHouse’s readonly=1 setting: the server itself refuses an INSERT, DDL, or a SETTINGS clause in the statement. Write one statement per step, and do not add FORMAT: the plugin reads the result as JSONCompact.

Use a key or an explicit ORDER BY to make ordering repeatable. A missing, null or duplicate key fails the step; the step keeps its rows, and its error names the key values. Returned column names must be unique. A capture is limited to 10,000 rows and 7 MiB of serialized snapshot state; aggregate in SQL instead of relying on a partial result.

A statement the server refuses fails the step with ClickHouse error: <message> (code <n> <NAME>), for example ClickHouse error: Unknown table expression identifier 'evnts' in scope SELECT * FROM evnts (code 60 UNKNOWN_TABLE). The step keeps that error, with no rows, as evidence. The connection’s password, and its user, database and host where they appear as whole words, appear as [withheld]. Other failures, such as a lost connection, report fixed text. Do not concatenate untrusted text into SQL; query parameters are not supported.

ClickHouse has integer and decimal types a JavaScript number cannot always hold, so the plugin keeps every value exact:

  • Decimal values are strings with their scale: "12.50".
  • Integers within ±2^53-1 are numbers whatever their type, so count() is a number. UInt64, Int64, Int128 and wider values beyond that range are strings, such as "18446744073709551615".
  • Floats a JavaScript number holds exactly are numbers; others, and nan, inf and -inf, are strings.
  • NULL is null and Bool is true or false. Dates, times, UUIDs, IP addresses and enums are strings. Arrays and tuples are arrays; maps, named tuples and JSON values are objects, with the same number rule inside.

Compare values that may exceed 2^53 as strings.

Give expect to state in the flow what the rows must hold, as a json( block in a grouped call. rows is the exact number of returned rows; json holds assertions over the rows as a JSON array of row objects, each with an RFC 6901 pointer and one operator: exists, equals, type, count, includes, lessThan, lessThanOrEqual, greaterThan or greaterThanOrEqual. /0/n is the first row’s n column and "" the whole array.

ch.query (
sql="SELECT count() AS n FROM spans WHERE session_id = '${session_id}' AND status_code = 'ERROR'"
expect=json(
{"rows": 1, "json": [{"pointer": "/0/n", "equals": 0}]}
)
)

Every check must hold. Otherwise the step fails, keeps its rows, and the error says what was expected and what was received:

expect.json[0] "/0/n": expected equals 0, received 3 (value_differs)

An invalid expectation is refused before any SQL is sent. A refused statement or a key violation fails with its own error and is not judged.

Telemetry often arrives a moment after the action that produced it. Wait for it with within=: the query runs again, every second unless every= says otherwise, until its rows satisfy expect, and ends at once on a refused statement or a lost connection:

ch.query (
sql="SELECT count() AS n FROM events WHERE session_id = '${session_id}' AND name = 'tool_call'"
expect=json(
{"json": [{"pointer": "/0/n", "greaterThanOrEqual": 1}]}
)
within=30s
every=2s
)

The same query can decide an if or unless. A single row with one column named satisfied sets a prerequisite’s predicate when its value is a Bool or a UInt8 0 or 1, which is what comparisons and EXISTS return: SELECT count() > 0 AS satisfied FROM events WHERE ….

ch.value runs one read-only statement like query and returns one cell of the first row for later steps: the column named by column, or the first column.

ch.value sql="SELECT trace_id FROM spans WHERE name = 'zz-e2e-run' ORDER BY start_time DESC, trace_id LIMIT 1" -> trace_id
ArgumentTypeMeaning
sqlstring, requiredOne SQL statement, run with readonly=1.
columnstring, optionalThe column to return; the first column when omitted.
raw"true", optionalReturn any printable text, not only a safe token.

No rows, a missing column, NULL, an array, map or tuple, or a value over 4 KiB fail the step. The value must be a safe token of letters, digits and . _ : / @ + - unless raw="true", so it can be single-quoted into a later statement. Never interpolate raw text into SQL. The value and its rows are kept as evidence, so never select a secret.

ch.execute runs one statement only when the private connection sets "allowWrites": true, and retains the number of rows it wrote. Mutations (ALTER TABLE … UPDATE or DELETE) wait until they finish, so the next step sees their effect. When the server refuses a statement, the step ends with its completion unknown, not failed: an INSERT may have written some of its rows. Check the database, then taint the step to run it again. Make setup safe to repeat, name what it writes with a run-specific key, and undo exactly that in a cleanup step:

### Record this run's test events
```rfl
capture sfx = random.string length=8
ch.execute sql="INSERT INTO events (session_id, name) VALUES ('zz-e2e-${sfx}', 'tool_call')"
```
```rfl cleanup step="Delete this run's test events"
ch.execute sql="ALTER TABLE events DELETE WHERE session_id = 'zz-e2e-${sfx}'"
```

A LocalBash step that runs docker exec … clickhouse-client --query … hides the check in a script’s exit code. Saving such a flow warns (wrong-tool-db). Write the query as a ch.query step with expect instead: the rows and the check are both visible in the flow and in its evidence.

Flows using this plugin can be saved in Reflow or in your repository. Read test storage for the team preference, private bindings and the shared plan/apply workflow.