demo D232

Live pushdown to DuckDB, query by query in htmx

Every sort, filter and page becomes one SQL statement DuckDB answers, with only the window coming back and the generated SQL and timing on screen

createPushdownSource · duckdbAdapter · lastPlan()

This grid turns every sort, filter and page into a single SQL statement DuckDB answers, with only the visible window coming back, and shows the generated SQL and its timing. It makes the query-per-interaction model visible, so you see exactly what the grid asks the database for.

Building…

Loading a live grid…

This is the htmx version. A server-rendered table is upgraded in place, so the page is readable before any script runs and gains the full grid when the module loads, which suits a progressively enhanced app.

The configuration

<link rel="stylesheet" href="https://cdn.latticegrid.dev/lattice-grid@1.108.0/lattice-grid.min.css">

<div id="grid" style="height: 560px"></div>
<pre id="sql" style="margin-top: 12px"></pre>

<script type="module">
  import * as LatticeGrid from 'https://cdn.latticegrid.dev/lattice-grid@1.108.0/modules/htmx.esm.min.js';

  // The grid ships no query engine. The page starts DuckDB-Wasm itself and hands
  // the adapter a live connection, so reaching for pushdown never grows the grid
  // you load.
  import * as duckdb from 'https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.29.0/+esm';
  const bundle = await duckdb.selectBundle(duckdb.getJsDelivrBundles());
  const worker = await duckdb.createWorker(bundle.mainWorker);
  const db = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(duckdb.LogLevel.ERROR), worker);
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
  const connection = await db.connect();

  // from is any SQL table expression. read_parquet streams the file, so a host
  // that honours range requests lets DuckDB read only the bytes a query touches.
  const adapter = LatticeGrid.duckdbAdapter({
    connection,
    from: "read_parquet('/demo-data/readings.parquet')",
  });
  const source = LatticeGrid.createPushdownSource({
    adapter,
    compute: LatticeGrid,   // the grid finishes anything SQL could not express
    pageSize: 200,          // only the visible window comes back, per interaction
  });

  // Wrap execute to show the statement DuckDB ran and how long it took.
  const sql = document.getElementById('sql');
  const inner = adapter.execute.bind(adapter);
  adapter.execute = async (query, request) => {
    const started = performance.now();
    const result = await inner(query, request);
    const ms = Math.round(performance.now() - started);
    const f = adapter.sqlFor(query);
    sql.textContent = f.sql + '\n-- ' + ms + ' ms, ' + result.rows.length + ' of ' + (result.total ?? '?') + ' rows';
    return result;
  };

  LatticeGrid.createGrid(document.getElementById('grid'), {
    rowKey: 'reading_id',
    toolPanel: { side: 'left', panels: ['filters', 'columns'] },
    columns: [
      { field: 'reading_id', title: '#', type: 'number', layout: { width: 110 } },
      { field: 'plant', title: 'Plant', filter: { type: 'set' } },
      { field: 'line', title: 'Line', filter: { type: 'set' } },
      { field: 'product', title: 'Product' },
      { field: 'bore_mm', title: 'Bore', type: 'millimetres',
        spec: { lower: 9.95, upper: 10.05, target: 10 } },
      { field: 'cycle_seconds', title: 'Cycle', type: 'seconds' },
      { field: 'in_spec', title: 'In spec', type: 'boolean' },
      { field: 'taken_at', title: 'Taken', type: 'dateString' },
    ],
    source,   // a hundred thousand rows; DuckDB answers each filter and sort in SQL
  });
</script>