DuckDB In Vanilla JS With Tabulator

If all worked as planned, you'll see a scrolling table at the end of page. Said table was made from a query against four remote parquet files. Let's see how it came together.

NOTE: Any bulleted line number references refer to the code block above the numbered bullets.

We'll start with…

Bits that go in the HTML tag

<link href="https://cdn.jsdelivr.net/npm/tabulator-tables@5.6.1/dist/css/tabulator_midnight.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/luxon@3.4.4/build/global/luxon.min.js"></script>

This gets of the Tabulator "midnight" theme for the table. There are other ones we can use, or we can make our own.
This pull in luxon so we can have Tabulator let us sort the day column in the table.

We need a place to render the table, so we set up a <div> for that:

A place to render our table

<div class="tabulator" id="tbl"></div>

We'll target this <div> by the tbl id attribute value. We use the tabulator class so it formats nicely.

Now, everything else is in a <script type="module"> tag.

JavaScript ES6 imports

import * as duckdb from "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.1-dev106.0/+esm";
import { TabulatorFull as Tabulator } from 'https://cdn.jsdelivr.net/npm/tabulator-tables@5.6.1/+esm';

This imports DuckDB-WASM from CDN
This imports all of Tabulator from CDN

Now, let's make a helper function to wrap up loading and instantiating DuckDB (this is just a wrapper around DuckDB's recommended steps):

DuckDB loader helper function

async function instantiate(duckdb) {
  const CDN_BUNDLES = duckdb.getJsDelivrBundles(),
  bundle = await duckdb.selectBundle(CDN_BUNDLES), ^{// Select a bundle based on browser checks}
  worker_url = URL.createObjectURL(
    new Blob([ `importScripts("${bundle.mainWorker}");` ], {
      type: "text/javascript"
    })
  );

  ^{// Instantiate the async version of DuckDB-WASM}
  const worker = new Worker(worker_url),
  logger = new duckdb.ConsoleLogger("DEBUG"),
  db = new duckdb.AsyncDuckDB(logger, worker);

  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
  URL.revokeObjectURL(worker_url);

  return db;
}

Now, we can start using DuckDB!

Instantiate and connect to our browser's DuckDB

const DuckDB = await instantiate(duckdb)
const db = await DuckDB.connect();

This gets us a DuckDB object we can use to connect to and perform operations on.
This wires up the database to the db JavaScript variable

There's a decent amount of SQL inside one await db.query() call block, so we'll just focus on the SQL in two phases.

Wire up remote parquet files

create table activity as select * from "https://data.hrbrmstr.dev/tag-activity.parquet";
create table     tags as select * from "https://data.hrbrmstr.dev/tags.parquet";
create table      kev as select * from "https://data.hrbrmstr.dev/kev.parquet";
create table      ips as select * from "https://data.hrbrmstr.dev/daily-ips.parquet";

Each create table line tells DuckDB where the remote databases are and gives them local table names.

Do some joins

with tagtivity as (
  select 
    day, count(day) as active_tags, 
    cast(sum(total_tag_activity) as double) as volume
  from 
    activity 
  group by 
    day
) 

select 
  a.day, 
  a.active_ips, 
  b.active_tags, 
  b.volume
from 
  ips a, 
  tagtivity b 
where
  a.day = b.day
order by
  a.day

Instantiate and connect to our browser's DuckDB

const tagsAndIps = await db.query(`…the SQL above`);

const tip = tagsAndIps.toArray().map(d => {
  const tmp = d.toJSON()
  tmp.day = tmp.day.toISOString().substring(0, 10)
  return tmp
})

This evalutes the query and returns an array of Arrow proxy objects
This turns the array of proxy objects into a regular JavaScript array of objects

Now for the Tabulator part!

Number formatter

function numeric(cell, formatterParams, onRendered) {
  return cell.getValue().toLocaleString()
}

We define a Tabulator custom formatter function which we'll use when we configure the table.

Table setup

var table = new Tabulator("#tbl", {
  height: "500px",
  data: tip,
  layout: "fitColumns",
  columns: [
    { title: "Day", field: "day", sorter: "date", sorterParams: { format: "yyyy-MM-dd" } },
    { title: "Active IPs", field: "active_ips", hozAlign: "right", sorter: "number", formatter: numeric },
    { title: "Active Tags", field: "active_tags", hozAlign: "right", sorter: "number", formatter: numeric },
    { title: "Volume", field: "volume", hozAlign: "right", sorter: "number", formatter: numeric },
  ]
})

This makes a new Tabulator object and tells it the element id we're targeting
This is the height of the table
This is the array we just made
This will make it fit to the width of the container and hide columns, if needed
This is rendering metadata information
Each of these lines configures on columns. For the day field, we make sure to use a data sorter
For these number value columns, we right align them and use our fancy formatter function. I also set font-variant-numeric: tabular-nums; in CSS for all .tabulator-cell classed elements.

And, here is the result!

You can view this example standalone, as well.

NOTE: since I'm still learning Nue, you may need to hard refresh this page to see the table, as Nue does fancy rendering tricks that may not trigger the load. The parquet files may take multiple seconds to load, too.

⌛️ Loading…