Live pushdown to DuckDB, query by query

<script>
  import * as LatticeGrid from 'https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.28.0/lattice-grid.esm.min.js';
  import createLatticeAction from 'https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.28.0/modules/svelte.esm.min.js';
  import * as duckdb from 'https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.29.0/+esm';

  const { createGrid, duckdbAdapter, createPushdownSource } = LatticeGrid;
  const lattice = createLatticeAction({ createGrid });

  // Lattice ships no engine. The component starts DuckDB-Wasm itself and hands
  // the adapter a live connection; the grid loads none of it, so the bundle is
  // the same size whether this is used or not. The source is ready once the
  // connection opens, so the grid mounts behind an {#if}.
  let config;
  let sqlEl;

  (async () => {
    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 = duckdbAdapter({
      connection,
      from: "read_parquet('/demo-data/readings.parquet')",
    });
    const source = 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 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);
      sqlEl.textContent = f.sql + '\n-- ' + ms + ' ms, ' + result.rows.length + ' of ' + (result.total ?? '?') + ' rows';
      return result;
    };

    config = {
      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>

<svelte:head>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@toclocoinc/lattice-grid@1.28.0/lattice-grid.min.css">
</svelte:head>

{#if config}
  <div use:lattice={config} style="height: 560px"></div>
{/if}
<pre bind:this={sqlEl} style="margin-top: 12px"></pre>