DuckDB adapter

A grid over a real analytical engine

DuckDB is a full analytical engine, and this adapter lets the grid drive one without carrying a byte of it. You create the connection and hand it over; the adapter translates each sort, filter and scroll into SQL and reads the result back. The pay-off is that a large analytical dataset stays fast: the filter, the sort and the paging are answered by an engine built for exactly that, over data that never has to be loaded into the grid row by row.

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

// You create the DuckDB connection; the grid imports no engine.
const db = await makeDuckDb();                 // your duckdb-wasm bootstrap
const connection = await db.connect();

createGrid(el, {
  columns,
  rowKey: 'id',
  source: createPushdownSource({
    adapter: duckdbAdapter({
      connection,                              // the live connection you made
      from: "read_parquet('data/readings.parquet')",
    }),
    compute,
    pageSize: 100,
  }),
});
// A million rows, queried in the tab, with no back end. The adapter answers
// the filter tree, a multi-column sort and the page in one query.

In the browser, or on a server

The adapter takes a connection you made, and does not care where it came from. In the browser that is @duckdb/duckdb-wasm, which runs DuckDB compiled to WebAssembly in the tab, so a million-row Parquet file can be queried with no back end at all. On a server it is any DuckDB client, driven through your own endpoint. Either way the adapter is the same code and imports nothing: DuckDB returns Arrow tables and they are read through their own accessors, so the Arrow library never enters the bundle.

What you query is up to the from expression. A table name, a view, or any FROM expression works, which is most of why the engine is worth reaching for: read_parquet('s3://…') is as valid as a table, so the grid can sit over a Parquet scan directly.

duckdbAdapter({ connection, from: 'orders' })                       // a table
duckdbAdapter({ connection, from: 'analytics.daily_totals' })       // a view
duckdbAdapter({ connection, from: "read_parquet('s3://bucket/*.parquet')" }) // a scan
duckdbAdapter({ connection, from: 'orders', fields: ['id', 'total', 'placed'] }) // a projection

What pushes down to DuckDB

The DuckDB adapter answers most of the query in the engine:

-- The statement the adapter builds for a filtered, sorted page.
-- count(*) OVER () returns the whole match count in the same round trip.
SELECT *, count(*) OVER () AS "__lattice_total"
FROM read_parquet('data/readings.parquet')
WHERE ("region" = ? AND "value" > ?)
ORDER BY "value" DESC
LIMIT 100 OFFSET 200;

The operators it writes are equals and not-equals, the four inequalities, contains, startsWith and endsWith as ILIKE, blank and not-blank, and in for a set. Two details are worth knowing. The count comes back with the rows through a window computed over the whole matching set before the limit, so one query returns both the page and the number of rows it was cut from, with no second round trip. And DuckDB returns BIGINT and HUGEINT as JavaScript BigInt: every value is normalised on the way out, and a magnitude past the safe integer range is kept as a string rather than silently rounded, because a wrong identifier is worse than an awkward one.

What runs in the browser

Aggregation does not push down. The adapter does not declare grouping, so when the grid is grouped or totalled the grouping and the totals are computed in the browser over the rows DuckDB returns. When that residual work exists the grid asks DuckDB for the whole matching result rather than a window, because grouping a single page locally would give the wrong answer, and it warns once naming what it could not push. If it is ever handed fewer rows than it asked for while residual work is pending, it refuses the result and raises an error rather than grouping a fraction and presenting it as the whole. You can watch the split after every query with source.lastPlan().

Filter values are bound, not interpolated

Filter values come from the user, which is the one part of a grid a stranger can steer, so they are sent through prepared statements rather than pasted into SQL. Column identifiers, which cannot be parameterised, are refused unless they are plain identifiers. If a connection has no prepare method, the adapter declines to send the filter rather than interpolate it, and says so once: filters are dropped, never inlined.

See it running: a million rows in DuckDB, no server, which queries a Parquet file in the browser. The pushdown guide covers the split rules shared across every adapter, and connect your data lists the other options.