ClickHouse in a JavaScript Data Grid

Reading a table live

clickhouseAdapter({ url, table }) needs no driver and no dependency: it POSTs SQL to {url}/?default_format=JSONEachRow and reads one JSON row per line back. Give table (a plain table, or database.table) or query, any SELECT read as a subquery, when the rows come from a view of your own rather than one table. Your credential travels in headers or a fetch wrapper; the adapter holds none of its own.

import { createGrid, createPushdownSource, clickhouseAdapter } from '@toclocoinc/lattice-grid';
import * as compute from '@toclocoinc/lattice-grid';

const grid = createGrid(el, {
  columns,
  rowKey: 'id',
  source: createPushdownSource({
    adapter: clickhouseAdapter({
      url: 'https://clickhouse.example.com:8443',
      table: 'logs.requests',                 // or database.table, or a query: '...'
      headers: { 'X-ClickHouse-User': 'grid_reader', 'X-ClickHouse-Key': 'your-password' },
    }),
    compute,
    pageSize: 100,
  }),
});
// A filter, sort and page become one SQL statement over HTTP, with every value
// sent as a typed parameter, never spliced into the SQL text.

See it running against ClickHouse's own public playground, with the SQL and its parameters on screen beside the grid: reading ClickHouse live.

What is pushed into ClickHouse

Filtering, sorting, paging, counting, grouping and pivoting all run in ClickHouse rather than in the browser:

eq / ne             case-folded through lowerUTF8() unless caseSensitive is set
gt / gte / lt / lte  a typed comparison
between / notBetween two typed comparisons, either bound inclusive or not
in / notIn           has() over a typed array
contains / startsWith / endsWith   position(), startsWith(), endsWith(), never LIKE
blank / notBlank     an explicit IS NULL check, not left to SQL's own NULL rules
list: hasAny / hasAll / hasNone / listCount

Anything outside that table stays with the grid and is named in source.lastPlan(), the same split every pushdown adapter reports.

No value is ever written into the SQL

Every filter value is a ClickHouse query parameter: the statement names it ({p0:String}, {p1:Float64}, a typed date or an array), and the value itself travels separately in the request. A column name is checked against a plain identifier before it is ever used, and refused by name rather than trusted, so nothing a filter carries can change what the statement does.

-- What the adapter sends for a "path contains admin" filter:
SELECT * FROM `logs`.`requests`
WHERE position(lowerUTF8(toString(`path`)), lowerUTF8({p0:String})) > 0
ORDER BY `id` ASC NULLS LAST LIMIT 2 OFFSET 0
-- param_p0=admin, travelling in the query string, never inside the SQL above.
-- A column name is checked against a plain identifier pattern and refused by
-- name otherwise, so nothing reaches the statement unchecked.

Statistics computed in ClickHouse

source.aggregate() computes sum, average, minimum, maximum, count and an exact distinct count inside ClickHouse, never an estimate. A statistic ClickHouse cannot compute is finished by the grid instead and named in lastPlan().aggregates.client.

const result = await source.aggregate(
  { filters: currentFilters, sort: [], range: null, groupBy: ['service'] },
  [{ id: 'errors', col: 'status', fn: 'count' }, { id: 'p99', col: 'duration', fn: 'max' }],
);
// sum, avg, min, max, count and an exact distinct count (uniqExact, not an
// estimate) all run in ClickHouse, grouped through GROUP BY ROLLUP.

Real groups over the whole table

Grouping a grid over a ClickHouse table gives real subtotals, not a guess from whatever page happened to be loaded: each level of the grouping is one grouped query, its keys, counts and subtotals computed in ClickHouse, and a group's own rows are fetched only once you open it.

// Grouping by county asks ClickHouse for one grouped query: the counties,
// each one's row count and subtotals, and the grand total, computed there.
// Opening a county then fetches its rows, a page at a time, with the county
// added to the filter. A nested grouping asks one more grouped query per
// level you open. Nothing is estimated from whatever page happened to load.

A pivot runs the same way, reshaping ClickHouse's own grouped answer into pivot columns rather than pulling every row into the browser to pivot there:

// A pivot over ClickHouse is answered from grouped aggregates too: no leaf
// row is fetched to build it, and a filter, sort or search the statement
// cannot carry keeps the pivot from running rather than pivoting the wrong
// rows silently.

Opening a column's filter menu asks the same grouped query for that column's values, most frequent first, rather than scanning every row to rediscover them; a column ClickHouse cannot group by that way offers the ordinary text condition editor instead, with a note saying so.

A statistic never collides with a column of the same name

ClickHouse resolves an alias anywhere in a statement, so naming a sum price would let a filter or a second statistic over the column price read the aggregate back instead of the column. Every statistic the adapter asks ClickHouse to compute, whether for aggregate(), a grouped level or a filter menu's value list, is selected under its own prefixed alias and mapped back to what you asked for in the answer, so the two can never be confused:

-- Every statistic is selected under its own alias rather than the column
-- name, because ClickHouse resolves an alias anywhere in a statement:
SELECT sum(`price`) AS `__lattice_a0`, avg(`price`) AS `__lattice_a1` ...
-- so a filter on "price" beside a sum or average of "price" never reads the
-- wrong thing back, and a filter-menu value list over an aggregated column
-- resolves correctly too.

Where the credential lives

Production: a proxy in front of ClickHouse (recommended). Your application authenticates the visitor its own way; a small backend holds the ClickHouse user and password and forwards only the statements this endpoint is meant to answer:

import { createServer } from 'node:http';

const TARGET = 'https://clickhouse.internal:8443';
const USER = 'grid_reader';
const KEY = process.env.CLICKHOUSE_KEY;

createServer(async (req, res) => {
  res.setHeader('Access-Control-Allow-Origin', 'https://app.example.com');
  res.setHeader('Access-Control-Allow-Methods', 'POST');
  const chunks = [];
  for await (const chunk of req) chunks.push(chunk);
  const upstream = await fetch(TARGET + req.url, {
    method: 'POST',
    headers: { 'X-ClickHouse-User': USER, 'X-ClickHouse-Key': KEY },
    body: Buffer.concat(chunks),
  });
  res.writeHead(upstream.status);
  res.end(Buffer.from(await upstream.arrayBuffer()));
}).listen(8080);
// No credential reaches the browser: the proxy holds it and forwards only
// the statements this endpoint is meant to answer.

Trying it out: your own credential in the page. Fine for a developer pointed at a development instance. Never ship a shared credential to visitors. A fixed user and password go in headers; a credential that expires goes in a fetch wrapper, called afresh per request. A public read-only user can also ride in the URL's own query string, the way ClickHouse's playground offers one, and the adapter keeps it as given.

Read-only by default, with room to raise a ceiling

Every statement is sent with readonly=1 and nothing else, so a read-only analyst account works with nothing to configure. On a writable or service account, add a time limit so a filter a stranger can steer cannot hold ClickHouse longer than a page is worth waiting for:

clickhouseAdapter({
  url, table: 'events',
  settings: { max_execution_time: 30 },   // a writable or service account only;
});                                        // a read-only profile refuses this setting
// readonly=1 is sent by default and nothing else, so a read-only analyst
// account, or ClickHouse's own public playground user, needs no configuring
// at all. settings: { readonly: null } drops it if your account forbids it.

A ClickHouse refusal, wrong table name or disallowed setting included, reaches the grid as a named error carrying ClickHouse's own message rather than an empty page.

See it running against ClickHouse's public playground with the generated SQL on screen: reading ClickHouse live. The pushdown data grid covers the same split across every engine, and the connect your data page lists every source and adapter the grid ships.