DuckDB in Astro

Fri Jan 03 2025

Wanted to play with the new DuckDB Node Neo library and thought it would be interesting to see if I could make it work in Astro. Astro supports MDX, so I thought it would be a good fit.

The query will be executed at build time and data rendered into a plain HTML file (no JS). Very useful to reference remote datasets (e.g: doing dashboards) while keeping things minimal. Let’s see how it works!

The first thing I did was to install the DuckDB Node Neo library.

npm install @duckdb/node-api

Then I created a new component called DuckDBQuery.astro that would take a query as a prop and execute it using the DuckDB Node Neo library.

---
const { query } = Astro.props;
import { DuckDBInstance } from "@duckdb/node-api";
const instance = await DuckDBInstance.create();
const connection = await instance.connect();
const reader = await connection.runAndReadAll(query);
const rows = reader.getRows();
const columns = reader.columnNames();
---

<div class="table-container">
  <table>
    <thead>
      <tr>
        {columns.map((column) => <th>{column}</th>)}
      </tr>
    </thead>
    <tbody>
      {
        rows.map((row) => (
          <tr>
            {Object.values(row).map((value) => (
              <td>{value}</td>
            ))}
          </tr>
        ))
      }
    </tbody>
  </table>
</div>

<style>
  .table-container {
    overflow-x: auto;
    margin: 1rem 0;
  }
</style>

The component creates a DuckDB instance, connects to it, runs the query, and then displays the results in a poorly formatted table.

Now, I can use the component in an MDX file.

import DuckDBQuery from "../../components/DuckDBQuery.astro";

<DuckDBQuery query="SELECT....;" />

That’s it!

For example, let’s run a query to get the total price for each customer for the orders.parquet file that DuckDB provides.

select
o_custkey as customer_id,
sum(o_totalprice) as total_price
from 'https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parquet'
group by o_custkey
limit 10;

Rudimentary, but it works and is quite minimal.

A similar pattern can be done at the Astro Content Collection level to generate a page for each row of the dataset. E.g: get a list of countries, and build a page for each country with their stats from datasets like the World Development Indicators.

Thoughs, ideas, feedback? Reach out!