observablehq
Document how to support sql blocks from a customized DuckDB client
If you want to manually configure your DuckDB instance, you can create a custom sql definition using the DuckDBClient.sql function. This returns a callback that abstracts and hides access to the underlying database. If you want to manually configure it and pass the DB instance to something like Mosaic, you can instead use the DuckDBClient.of function which returns a wrapper class around the underlying WASM instance. Both of these are documented.
What's slightly unclear is that if you want to both enable Mosaic and have access to SQL literals, you need to build your own const sql definition using the db value returned from the of function. This is easy, but as far as I could find, undocumented. In particular, it's important to note that (a) the db.sql function is what you want to define const sql to be and (b) that it's a method so you need to bind the database instance.
The following code snippet is a tiny modification of the code available on the Mosaic documentation page.
import {DuckDBClient} from "npm:@observablehq/duckdb";
import * as vgplot from "npm:@uwdata/vgplot";
const db = await DuckDBClient.of({ ... });
const vgCoordinator = new vgplot.Coordinator();
vgCoordinator.databaseConnector(vgplot.wasmConnector({duckdb: db._db}));
const sql = db.sql.bind(db); // this line is new
const vg = vgplot.createAPIContext({coordinator: vgCoordinator});
I looked for this information on the SQL documentation page, but it only references the DuckDBClient.sql function. Finally, on the DuckDB documentation page both DuckDBClient.of and DuckDBClient.sql are mentioned, but it's not described how to create the sql function from a database created using of. Ultimately, it was just a matter of looking at the source code of DuckDBClient.sql, but ideally this would be documented.
Thanks! Do you want to make a pull-request with this change? I think it should have a sentence to say "optionally, create a sql symbol to use elsewhere in the page" (or something clearer than this ;-) )…
But this makes me think we should perhaps attach the database to the sql symbol too, and make this simpler.
Say, if we had:
return this.of.apply(this, arguments).then((db) => Object.assign(db.sql.bind(db), {_db: db}));
then in the documentation above you could use
const sql = await DuckDBClient.of({ ... });
const vgCoordinator = new vgplot.Coordinator();
vgCoordinator.databaseConnector(vgplot.wasmConnector({duckdb: sql._db}));
@Fil I don’t think we should muddle the DuckDBClient interface (DatabaseClient implementation) with the sql template literal; those are two separate things.
