The avatar for @mootari
Fabian Iwand
Web developer and autodidact with an interest in creative coding.
Public
Observable Things
Edited
Mar 31
Paused
Importers
6 stars
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
db = DuckDBClient_example.of({cities})
Insert cell
db
-- Example from https://duckdb.org/docs/stable/sql/statements/pivot.html#pivot-on-and-using
PIVOT cities ON year USING sum(population)
Insert cell
db
-- Example from https://duckdb.org/docs/stable/sql/statements/pivot.html#in-filter-for-on-clause
PIVOT cities ON year IN (2000.0, 2010.0) USING sum(population) GROUP BY country
Insert cell
Insert cell
db
-- Adapted from https://duckdb.org/docs/stable/sql/statements/pivot.html#in-filter-for-on-clause
PIVOT cities ON year IN (2000.0, 2010.0) USING sum(population) + ${0} GROUP BY country
Insert cell
Insert cell
db
-- Show the DuckDB version and ensure that prepared statements work
select version(), ${"foo"}, ${1}
Insert cell
Insert cell
// Note: "duckdb" is only used for display purposes and needs to be kept in sync manually.
versions = ({
latest: {
gitRef: "v1.29.0",
wasmVersion: "1.29.0",
arrowVersion: "17.0.0",
duckdb: "1.1.1"
},
// @next switches back and forth between these two branches. We'll keep both here for now.
next: {
gitRef: "main",
wasmVersion: "1.29.1-dev76.0",
arrowVersion: "17.0.0",
duckdb: "1.2.1"
},
next: {
gitRef: "v1.2-histrionicus",
wasmVersion: "1.29.1-dev126.0",
arrowVersion: "17.0.0",
duckdb: "v1.2.2-dev140"
}
})
Insert cell
DuckDBClient_latest = importPatched(versions.latest)
Insert cell
DuckDBClient_next = importPatched(versions.next)
Insert cell
function importPatched({wasmVersion, arrowVersion}) {
return fetch("https://esm.sh/gh/observablehq/stdlib@v5.8.8/es2022/src/duckdb.bundle.mjs")
.then(response => response.text())
// Patch the module source
.then(source => source
.replace(/("@duckdb\/duckdb-wasm",)"1.24.0"/, `$1"${wasmVersion}"`)
// Match the Arrow version.
// See https://github.com/duckdb/duckdb-wasm/blob/v1.29.0/packages/duckdb-wasm/package.json#L26
.replace(/("apache-arrow",)"11.0.0"/, `$1"${arrowVersion}"`)
)
// Import the patched module
.then(source => import(URL.createObjectURL(new Blob([source], {type: "text/javascript"}))))
// Restore the now minified class name
.then(module => Object.defineProperty(module.DuckDBClient, "name", {value: "DuckDBClient"}))
// Patch the client's internal duckdb-wasm instance to remap .send() to .query() on connection and statement instances
.then(C => {
const of = C.of;
C.of = async(...args) => {
const client = await of(...args);
patchSend(client._db);
return client;
};
return C;
})
}
Insert cell
/**
* PIVOT does not currently work with .send() (see https://github.com/observablehq/framework/issues/1016).
* This function replaces connection.send() and statement.send() with a compatible call to .query().
*/
function patchSend(db) {
const wrap = (fn, map) => function(...args) { return map(fn.apply(this, args)) };
const yieldAsync = async function* (ret) { yield await ret };
db.connect = wrap(db.connect, async ret => {
const conn = await ret;
conn.prepare = wrap(conn.prepare, async ret => {
const stmt = await ret;
stmt.send = wrap(stmt.query, yieldAsync);
return stmt;
});
conn.send = wrap(conn.query, yieldAsync);
return conn;
});
}
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more