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.
Install
Section titled “Install”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:
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.
Connect
Section titled “Connect”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" } }}| Field | Meaning |
|---|---|
host | Required. Host name or IP address, without scheme or port. |
port | HTTP interface port; 8123, or 8443 when secure is true. |
secure | Use HTTPS. Defaults to false. |
database | Database for unqualified table names; default when omitted. |
user | ClickHouse user; default when omitted. |
password | The user’s password. |
passwordFile | Instead of password: the absolute path of a private file holding only the password. |
allowWrites | Allow 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.
chmod 600 /absolute/path/to/bindings.jsonreflow target --group database-tests --collection <collection-id> --bindings /absolute/path/to/bindings.jsonWrite a flow
Section titled “Write a flow”---name: Check session eventsproviders: ch: ClickHouse---
### The test user's latest session recorded its tool calls
```rflch.value sql="SELECT session_id FROM events WHERE user_id = 'zz-e2e-user' ORDER BY time DESC, session_id LIMIT 1" -> session_idch.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.
Query arguments and stable rows
Section titled “Query arguments and stable rows”| Argument | Type | Meaning |
|---|---|---|
sql | string, required | One SQL statement, run with the server’s readonly=1. |
key | string, optional | A unique, non-null returned column used to identify and sort rows. |
expect | JSON text, optional | What 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.
Values
Section titled “Values”ClickHouse has integer and decimal types a JavaScript number cannot always hold, so the plugin keeps every value exact:
Decimalvalues are strings with their scale:"12.50".- Integers within ±2^53-1 are numbers whatever their type, so
count()is a number.UInt64,Int64,Int128and wider values beyond that range are strings, such as"18446744073709551615". - Floats a JavaScript number holds exactly are numbers; others, and
nan,infand-inf, are strings. NULLisnullandBoolistrueorfalse. Dates, times, UUIDs, IP addresses and enums are strings. Arrays and tuples are arrays; maps, named tuples andJSONvalues are objects, with the same number rule inside.
Compare values that may exceed 2^53 as strings.
Check the rows
Section titled “Check the rows”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 ….
Return a value with ch.value
Section titled “Return a value with ch.value”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| Argument | Type | Meaning |
|---|---|---|
sql | string, required | One SQL statement, run with readonly=1. |
column | string, optional | The column to return; the first column when omitted. |
raw | "true", optional | Return 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.
Setup statements
Section titled “Setup statements”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
```rflcapture sfx = random.string length=8ch.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}'"```Replace clickhouse-client in a script
Section titled “Replace clickhouse-client in a script”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.