OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
import { describe, expect, it } from 'bun:test';
import { sql } from './sql';
/**
* Every param type the codebase uses, bound through `chQuery` (so the
* `query_params` path is exercised through `withRetry` / round-robin) and
* read back off a real local ClickHouse.
*
* Payloads are hostile on purpose — single quote, double quote, backslash,
* newline, tab, and a literal `{p1:String}` — because the escaping lives in the
* driver and the parsing lives in the server, and neither is ours.
*
* This suite runs against its own `openpanel_test` database, which it creates.
* It must never touch the local `openpanel` prod-copy: those tables hold
* hundreds of millions of rows, and a `FINAL` / `ARRAY JOIN` scan over them
* times the HTTP client out while the rest of the suite runs in parallel
* against the same node.
*/
const CLICKHOUSE_TEST_DATABASE = 'openpanel_test';
const DEFAULT_CLICKHOUSE_BASE_URL = 'http://localhost:23123';
/**
* Well above the app default (30s): this suite shares one local node with
* every other test file in the run, and a queued request should wait rather
* than fail.
*/
const CLICKHOUSE_TEST_REQUEST_TIMEOUT_MS = 120_000;
function testDatabaseUrl(): { base: string; withDatabase: string } {
const configured = (process.env.CLICKHOUSE_URL ?? '').split(',')[0]?.trim();
const url = new URL(configured || DEFAULT_CLICKHOUSE_BASE_URL);
url.pathname = '/';
const base = url.toString();
url.pathname = `/${CLICKHOUSE_TEST_DATABASE}`;
return { base, withDatabase: url.toString() };
}
const { base: CLICKHOUSE_BASE_URL, withDatabase: CLICKHOUSE_TEST_URL } =
testDatabaseUrl();
async function execute(statement: string): Promise<void> {
const response = await fetch(CLICKHOUSE_BASE_URL, {
method: 'POST',
body: statement,
});
if (!response.ok) {
throw new Error(`${response.status}: ${await response.text()}`);
}
}
/**
* Minimal stand-ins for the three tables this suite names — same engine and
* sort key as production, only the columns the assertions touch. They stay
* empty; every query here asserts on shape, not on data.
*/
const FIXTURE_TABLES = [
`CREATE TABLE IF NOT EXISTS ${CLICKHOUSE_TEST_DATABASE}.events (
id UUID DEFAULT generateUUIDv4(),
project_id String,
profile_id String,
groups Array(String) DEFAULT [],
created_at DateTime64(3)
) ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (project_id, toDate(created_at), created_at)`,
`CREATE TABLE IF NOT EXISTS ${CLICKHOUSE_TEST_DATABASE}.profiles (
id String,
project_id String,
first_name String,
last_name String,
last_seen_at DateTime64(3)
) ENGINE = ReplacingMergeTree(last_seen_at)
ORDER BY (project_id, id)`,
`CREATE TABLE IF NOT EXISTS ${CLICKHOUSE_TEST_DATABASE}.sessions (
id String,
project_id String,
created_at DateTime64(3),
sign Int8,
version UInt64
) ENGINE = VersionedCollapsingMergeTree(sign, version)
PARTITION BY toYYYYMM(created_at)
ORDER BY (project_id, toDate(created_at), created_at)`,
];
async function bootstrapTestDatabase(): Promise<boolean> {
try {
await execute(`CREATE DATABASE IF NOT EXISTS ${CLICKHOUSE_TEST_DATABASE}`);
for (const table of FIXTURE_TABLES) {
await execute(table);
}
return true;
} catch {
return false;
}
}
const clickhouseReachable = await bootstrapTestDatabase();
// ./client reads both of these once, at module evaluation, so they have to be
// set before it is imported — hence the dynamic import.
process.env.CLICKHOUSE_URL = CLICKHOUSE_TEST_URL;
process.env.CLICKHOUSE_REQUEST_TIMEOUT_MS = String(
CLICKHOUSE_TEST_REQUEST_TIMEOUT_MS
);
const { chQuery } = await import('./client');
const HOSTILE = `it's a "quoted" \\ backslash {p1:String} }brace{ \n newline \t tab`;
const BIG_UINT64 = 1_234_567_890_123;
const describeAgainstClickhouse = describe.skipIf(!clickhouseReachable);
/** ClickHouse type names carry `(` and `)`; they are matched literally. */
const escapeRegExp = (literal: string) =>
literal.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
/**
* Returns `unknown` rather than a generic `T` on purpose. bun:test's `expect`
* has an `(actual?: never)` overload, which makes the argument position a
* contextual inference site: a generic helper called bare there resolves its
* parameter to `never`, and every `toBe(...)` below then fails to compile
* against `Matchers<undefined>`. vitest's `expect(actual: any)` applied no
* such pressure. Each assertion states the expected value itself, so nothing
* is lost by widening here.
*/
const value = async (
query: Parameters<typeof chQuery>[0],
settings?: Parameters<typeof chQuery>[1]
): Promise<unknown> => {
const rows = await chQuery<{ value: unknown }>(query, settings);
return rows[0]?.value;
};
describeAgainstClickhouse('param round-trip against local ClickHouse', () => {
it('String survives quotes, backslashes, braces and whitespace', async () => {
expect(await value(sql`SELECT ${sql.string(HOSTILE)} AS value`)).toBe(
HOSTILE
);
});
it('UInt64', async () => {
// chQueryWithMeta parseFloats anything whose meta type contains 'Int',
// so this asserts the value that reaches a caller, not the wire form.
expect(await value(sql`SELECT ${sql.uint64(BIG_UINT64)} AS value`)).toBe(
BIG_UINT64
);
expect(await value(sql`SELECT ${sql.uint64(42n)} AS value`)).toBe(42);
});
it('Float64', async () => {
expect(await value(sql`SELECT ${sql.float64(-1.5)} AS value`)).toBe(-1.5);
});
it('DateTime64(3) keeps millisecond precision', async () => {
const date = new Date('2026-04-29T13:45:07.123Z');
expect(
await value(
sql`SELECT toUnixTimestamp64Milli(${sql.dateTime64(date)}) AS value`
)
).toBe(date.getTime());
expect(
await value(
sql`SELECT toString(${sql.dateTime64('2026-04-29 13:45:07.123')}) AS value`
)
).toBe('2026-04-29 13:45:07.123');
});
it('Date takes the calendar-day text form, not a timestamp', async () => {
expect(
await value(
sql`SELECT toString(${sql.date(new Date('2026-04-29T13:45:00.000Z'))}) AS value`
)
).toBe('2026-04-29');
// Why sql.date() formats: the driver serializes a JS Date as a unix
// timestamp, and ClickHouse refuses that for the Date type.
await expect(
chQuery(sql`SELECT ${sql.param('Date', new Date())} AS value`)
).rejects.toThrow(/cannot be parsed as Date/);
});
it('Array(String) with hostile items', async () => {
const items = [HOSTILE, "', 'injected", '{p1:String}'];
expect(
await value(sql`SELECT ${sql.array('String', items)} AS value`)
).toEqual(items);
});
it('Map(String,String) with hostile keys and values', async () => {
const map = { [HOSTILE]: HOSTILE, "key'": '}value{' };
expect(
await value(sql`SELECT ${sql.map('String', 'String', map)} AS value`)
).toEqual(map);
});
it('Nullable(String) carries null and a hostile value', async () => {
expect(
await value(sql`SELECT isNull(${sql.nullable('String', null)}) AS value`)
).toBe(1);
expect(
await value(sql`SELECT ${sql.nullable('String', HOSTILE)} AS value`)
).toBe(HOSTILE);
});
it('UUID', async () => {
const id = '11111111-2222-3333-4444-555555555555';
expect(await value(sql`SELECT toString(${sql.uuid(id)}) AS value`)).toBe(
id
);
});
it('Bool', async () => {
expect(await value(sql`SELECT ${sql.bool(true)} AS value`)).toBe(true);
expect(await value(sql`SELECT ${sql.bool(false)} AS value`)).toBe(false);
});
it('a payload that looks like SQL stays data', async () => {
// Interpolated, this returns 1 row. Bound, it returns 0.
const payload = "nope' OR '1'='1";
expect(
await value(
sql`SELECT count() AS value FROM (SELECT 'safe' AS path) WHERE path = ${sql.string(payload)}`
)
).toBe(0);
});
it('rejects a hostile payload on a non-text type instead of inlining it', async () => {
// The types above cannot carry quotes or braces, so the round-trip for
// them is the negative one: the server refuses the value as data. The
// payload never becomes SQL text, which is the property under test.
const hostileOn: [string, ReturnType<typeof sql.param>][] = [
['UInt64', sql.param('UInt64', HOSTILE)],
['Float64', sql.param('Float64', HOSTILE)],
['DateTime64(3)', sql.dateTime64(HOSTILE)],
['Date', sql.date(HOSTILE)],
['Bool', sql.param('Bool', HOSTILE)],
];
for (const [type, hostileParam] of hostileOn) {
await expect(
chQuery(sql`SELECT ${hostileParam} AS value`),
type
).rejects.toThrow(
new RegExp(`cannot be parsed as ${escapeRegExp(type)}`)
);
}
});
});
/**
* Identifier positional matrix: where `{x:Identifier}` works, and where a
* call site must use `sql.id()` (validated inline text) instead.
*
* | Position | `{x:Identifier}` |
* |--------------------------------------------|------------------|
* | SELECT column | works |
* | SELECT alias (`1 AS {c:Identifier}`) | works |
* | Aggregate/function argument | works |
* | WHERE column | works |
* | GROUP BY column | works |
* | ORDER BY column | works |
* | FROM table | works |
* | JOIN table | works |
* | FROM table inside a CTE | works |
* | database.table as two separate params | works |
* | `'db.table'` in ONE param | FAILS — quoted whole |
* | `'alias.column'` in ONE param | FAILS — quoted whole |
* | SETTINGS value (`session_timezone = {…}`) | FAILS — syntax error, any type |
*
* Consequences: a qualified name is two params, or one
* `sql.id('profiles.name')`; a per-query `session_timezone` goes through
* `chQuery`'s `clickhouse_settings` argument, not through the SQL text.
*/
describeAgainstClickhouse('Identifier positional matrix', () => {
const table = sql.identifier('events');
it('works in SELECT, alias, function, WHERE, GROUP BY and ORDER BY', async () => {
const column = sql.identifier('a');
expect(
await value(sql`SELECT ${column} AS value FROM (SELECT 1 AS a)`)
).toBe(1);
expect(
await value(sql`SELECT sum(${column}) AS value FROM (SELECT 1 AS a)`)
).toBe(1);
expect(
await value(
sql`SELECT a AS value FROM (SELECT 1 AS a) WHERE ${column} = 1`
)
).toBe(1);
expect(
await value(
sql`SELECT count() AS value FROM (SELECT 1 AS a) GROUP BY ${column} ORDER BY ${column}`
)
).toBe(1);
const aliased = await chQuery<Record<string, number>>(
sql`SELECT 1 AS ${sql.identifier('value')}`
);
expect(aliased[0]?.value).toBe(1);
});
it('works for a table name in FROM, JOIN and a CTE', async () => {
expect(
await value(sql`SELECT count() AS value FROM ${table} WHERE 0`)
).toBe(0);
expect(
await value(
sql`SELECT count() AS value FROM (SELECT 1 AS a) AS x JOIN ${table} AS y ON 0 WHERE 0`
)
).toBe(0);
expect(
await value(
sql`WITH c AS (SELECT count() AS value FROM ${table} WHERE 0) SELECT value FROM c`
)
).toBe(0);
});
it('works for database.table as two params', async () => {
expect(
await value(
sql`SELECT count() AS value FROM ${sql.identifier(CLICKHOUSE_TEST_DATABASE)}.${table} WHERE 0`
)
).toBe(0);
});
it('fails for a dotted name in a single param — use sql.id or two params', async () => {
await expect(
chQuery(
sql`SELECT count() AS value FROM ${sql.identifier(`${CLICKHOUSE_TEST_DATABASE}.events`)} WHERE 0`
)
).rejects.toThrow(/Unknown table expression identifier/);
await expect(
chQuery(
sql`SELECT ${sql.identifier('t.a')} AS value FROM (SELECT 1 AS a) AS t`
)
).rejects.toThrow(/Unknown expression identifier/);
// sql.id() is the supported form for a qualified reference.
expect(
await value(
sql`SELECT ${sql.id('t.a')} AS value FROM (SELECT 1 AS a) AS t`
)
).toBe(1);
});
it('fails inside a SETTINGS clause, for any param type', async () => {
await expect(
chQuery(sql`SELECT 1 SETTINGS session_timezone = ${sql.string('UTC')}`)
).rejects.toThrow(/Syntax error/);
await expect(
chQuery(
sql`SELECT 1 SETTINGS session_timezone = ${sql.identifier('UTC')}`
)
).rejects.toThrow(/Syntax error/);
// The supported route: settings travel beside the query.
expect(
await value(
sql`SELECT toString(toTimeZone(toDateTime(0), timezone())) AS value`,
{ session_timezone: 'Europe/Stockholm' }
)
).toBe('1970-01-01 01:00:00');
});
it('neutralises an injection payload in an Identifier param', async () => {
await expect(
chQuery(
sql`SELECT ${sql.identifier('a) UNION ALL (SELECT 2')} AS value FROM (SELECT 1 AS a)`
)
).rejects.toThrow(/Unknown expression identifier/);
});
});
describeAgainstClickhouse('local builder fallback executes (R5)', () => {
const PROFILE_COLUMNS = ['first_name', 'last_name'] as const;
it('binds every value the imperative assembly produced', async () => {
// Local assembly is permitted; unbound text is not. Hostile values go in
// as data and the server matches nothing.
const filters = [
{ column: 'first_name', value: HOSTILE },
{ column: 'last_name', value: "' OR 1=1 --" },
];
const conditions = filters.map(
({ column, value }) =>
sql`${sql.id(column, PROFILE_COLUMNS)} = ${sql.string(value)}`
);
expect(
await value(
sql`SELECT count() AS value FROM profiles WHERE ${sql.join(conditions, ' AND ')}`
)
).toBe(0);
});
});
describeAgainstClickhouse('cluster constructs execute unchanged (R4)', () => {
it('accepts GLOBAL IN, PREWHERE, ARRAY JOIN, LIMIT BY and a bound LIMIT', async () => {
const projectId = sql.string('__no_such_project__');
const rows = await chQuery(sql`
SELECT e.id AS id, g AS grp
FROM events AS e
ARRAY JOIN e.groups AS g
PREWHERE e.project_id = ${projectId}
WHERE e.profile_id GLOBAL IN (
SELECT id FROM profiles WHERE project_id = ${projectId}
)
ORDER BY e.created_at
LIMIT 1 BY e.id
LIMIT ${sql.param('UInt32', 10)}
`);
expect(rows).toEqual([]);
});
it('accepts GLOBAL JOIN alongside a trailing literal SETTINGS clause', async () => {
// The SETTINGS *value* cannot be a param (see the matrix above), but a
// literal SETTINGS tail must still survive the tag untouched.
const projectId = sql.string('__no_such_project__');
expect(
await value(sql`
SELECT count() AS value
FROM events AS e
GLOBAL JOIN (
SELECT id FROM profiles WHERE project_id = ${projectId}
) AS p ON e.profile_id = p.id
WHERE e.project_id = ${projectId}
SETTINGS max_execution_time = 30
`)
).toBe(0);
});
it('accepts FINAL and WITH FILL with bound bounds', async () => {
const from = sql.dateTime64('2026-01-01 00:00:00.000');
const to = sql.dateTime64('2026-01-04 00:00:00.000');
const rows = await chQuery<{ d: string; c: number }>(sql`
SELECT toStartOfDay(created_at) AS d, count() AS c
FROM sessions FINAL
WHERE project_id = ${sql.string('__no_such_project__')}
AND created_at >= ${from} AND created_at < ${to}
GROUP BY d
ORDER BY d WITH FILL FROM toStartOfDay(${from}) TO toStartOfDay(${to}) STEP toIntervalDay(1)
`);
expect(rows.map((row) => row.c)).toEqual([0, 0, 0]);
});
});