# how to use duckdb \*and\* DuckDBClient **URL:** \ **Category:** Help\ **Created:** [July 25, 2024, 10:04am UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549 "2024-07-25T10:04:01Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![undef](https://avatars.discourse-cdn.com/v4/letter/u/7feea3/32.png) [@undef](https://talk.observablehq.com/u/undef)\ **Post date:** [July 25, 2024, 10:04am UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/1 "2024-07-25T10:04:01Z")
Hi, I’m trying to access the duckdb instance created by DuckDBClient (so I can use the registerFileXYZ apis from [Data Ingestion – DuckDB](https://duckdb.org/docs/api/wasm/data_ingestion.html)) I naively tried the below snippet which doesn’t work.(i’ve replace the js/sql blocks with triple` as it messes with the forum formatting) ```auto triple`js --echo const db = await DuckDBClient.of({ lut: FileAttachment("data/lut.parquet") }) const sql = db.sql.bind(db); triple` triple`sql SHOW TABLES; -- shows lut triple` triple`js // DuckDB-Wasm is available by default as duckdb in Markdown console.log(duckdb.PACKAGE_VERSION); // 1.28.0 -- we have duck // https://observablehq.com/@cmudig/duckdb-client // db() returns the DuckDB database const fdb = await DuckDBClient.db(); // TypeError: DuckDBClient.db is not a function const c = await fdb.connect(); await c.query(` SHOW TABLES; `); triple` ``` What is the right way of accessing the underlying APIs of duckdb , either via DuckDBClient or via duckb - I do not want to instantiate an entirely separate instance obviously. thanks! ---
**Author:** ![mootari](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/mootari/32/581_2.png) [@mootari](https://talk.observablehq.com/u/mootari)\ **Post date:** [July 25, 2024, 11:11am UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/2 "2024-07-25T11:11:47Z")
You can access the underlying DuckDB instance via `._db`: ![](https://canada1.discourse-cdn.com/flex030/uploads/observablehq/original/2X/a/a3f544d4350e77701a50a72f87959ab1398f7e3a.png) You can find the implementation here: [framework/src/client/stdlib/duckdb.js at 260f8f1bd6d524d01d3dd82400293b318da1aa06 · observablehq/framework · GitHub](https://github.com/observablehq/framework/blob/260f8f1bd6d524d01d3dd82400293b318da1aa06/src/client/stdlib/duckdb.js#L71-L75) * * * > [@undef](#): > > i’ve replace the js/sql blocks with triple` as it messes with the forum formatting Markdown lets you wrap code blocks both in ````` and ` ~~~`. To share your code verbatim, use `~~~ `. ---
**Author:** ![undef](https://avatars.discourse-cdn.com/v4/letter/u/7feea3/32.png) [@undef](https://talk.observablehq.com/u/undef)\ **Post date:** [July 25, 2024, 11:41am UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/3 "2024-07-25T11:41:25Z")
Hi and thanks for the fastreply, but I’m sorry to say that’s a little too cryptic for me - could you maybe give an example of how I can still use the sql code blocks in markdown and the duckdb api. I tried this, but no joy. ````auto ```js const fdb = db._db; //TypeError: fdb.query is not a function await fdb.query(` SHOW TABLES; `); ``` ```` ---
**Author:** ![mootari](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/mootari/32/581_2.png) [@mootari](https://talk.observablehq.com/u/mootari)\ **Post date:** [July 25, 2024, 12:57pm UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/4 "2024-07-25T12:57:49Z")
You skipped creating the connection in your second attempt. 🙂 (Also keep in mind that you can still continue to use the DuckDBClient abstractions like `db.sql`SHOW TABLES``.) Can you say more about the additional types of files that you want to register? `DuckDBClient.of({})` lets you pass in multiple data sources. ---
**Author:** ![undef](https://avatars.discourse-cdn.com/v4/letter/u/7feea3/32.png) [@undef](https://talk.observablehq.com/u/undef)\ **Post date:** [July 25, 2024, 1:32pm UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/5 "2024-07-25T13:32:00Z")
> You skipped creating the connection in your second attempt. 🙂 > :face\_palm: my use case isn’t not really about additional types of files per se; they’re (particularly malformed) csv’s. I already have sql available to deal with that, relying on read\_text - i.e. not using the duckdb builtin parser. Also, the existing sql relies on the path (/lookups, /data… ) and then globs the lot into the correct view - i.e. combines the main data set with user data added. I,m trying/hoping for a straightforward path where I could just upload the usrfile in their correct locations in the wasm filesystem and keep the sql unchanged. thanks for the help, and love to hear other ideas about the approach. ---
**Author:** ![mootari](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/mootari/32/581_2.png) [@mootari](https://talk.observablehq.com/u/mootari)\ **Post date:** [July 25, 2024, 10:32pm UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/6 "2024-07-25T22:32:15Z")
To me that sounds like the sort of data wrangling that you’d normally do in a dataloader, so that it wouldn’t need to run in the user’s browser. Can you say more about what you want to accomplish in general, e.g. why you picked Framework for this task? ---
**Author:** ![undef](https://avatars.discourse-cdn.com/v4/letter/u/7feea3/32.png) [@undef](https://talk.observablehq.com/u/undef)\ **Post date:** [July 26, 2024, 8:45am UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/7 "2024-07-26T08:45:02Z")
it’s complicated… and there have been various experiments over time. Including a data wrangling pipeline or two. duckdb (ideally duckdb-wasm) stuck. Other experiments involved perspective, mosaic, vega/lite, evidence … the core of the use case is a large central data set (the static part) combined with a small user generated data set (the unpredictably messy part) visualized as a static dashboard. so it’s the old 80/20 rule … i guess. reasons for trying Framework - duckdb - local first (duckdb-wasm) - static (nothing to manage besides a dumb webserver) - batteries included (just works/looks fine out of the box) - libraries ---
**Author:** ![mootari](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/mootari/32/581_2.png) [@mootari](https://talk.observablehq.com/u/mootari)\ **Post date:** [July 26, 2024, 10:56am UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/8 "2024-07-26T10:56:45Z")
That still sounds to me like you’d want to do the messy wrangling part in server-side code via a data loader and then have it output a cleaned up file (e.g. parquet, arrow, JSON …, depending on how you want to consume it in your dashboard)? In which case DuckDBClient or FileAttachments wouldn’t come into play yet. [https://observablehq.com/framework/loaders](https://observablehq.com/framework/loaders) ---
**Author:** ![mbostock](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/mbostock/32/9_2.png) [@mbostock](https://talk.observablehq.com/u/mbostock)\ **Post date:** [July 26, 2024, 12:36pm UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/9 "2024-07-26T12:36:42Z")
It sounds to me like you’re trying to create a `DuckDBClient` with some tables coming from a `FileAttachment` (either static files or created by a data loader) and other tables coming from the user. For the latter you can use [`Inputs.file`](https://observablehq.com/framework/inputs/file) to let the user select a file from their local file system. Observable Framework’s SQL front matter doesn’t support user-specified files (since you need to render a file input somewhere), but you can do the same thing by using the underlying `DuckDBClient.of` method in JavaScript. For example, here the `widgets` table comes from the `widgets.csv` file, while the `others` table comes from a user-specified file: ````md ```js const db = await DuckDBClient.of({ widgets: FileAttachment("widgets.csv"), ...otherFile && {others: otherFile.csv()} }); ``` ```` The user input is declared as: ````md ```js const otherFile = view(Inputs.file({label: "File", accept: ".csv"})); ``` ```` If you want to use this database for your SQL cells, you can override the `sql` built-in like so: ```js const sql = db.sql.bind(db); ``` I’m using JavaScript to parse the file here (`file.csv`, which is implemented by d3-dsv), but you could load it as text and use a different technique if you prefer. You can also create tables using SQL commands. ```js const url = await file.url(); const buffer = await file.arrayBuffer(); await db._db.registerFileBuffer("other.csv", new Uint8Array(buffer)); ``` I’m not sure if you can use `read_text`, though. I get `Table Function with name read_text does not exist!` if I try this: ```sql SELECT * FROM read_text('other.csv'); ``` ---
**Author:** ![undef](https://avatars.discourse-cdn.com/v4/letter/u/7feea3/32.png) [@undef](https://talk.observablehq.com/u/undef)\ **Post date:** [July 26, 2024, 1:19pm UTC](https://talk.observablehq.com/t/how-to-use-duckdb-and-duckdbclient/9549/10 "2024-07-26T13:19:19Z")
thanks for the examples, that’ll help. including a file in the front matter like that … is indistinguishable from magic ( to me, guess I’m not sufficiently advanced 😉 > [@mbostock](#): > > I’m not sure if you can use `read_text`, read\_text (and read\_blob) are additions in more recent versions (0.10.0) of duckdb, combined with regexp\_split\_to\_table on \n it’s pretty much the same as using read\_csv with \b \v as seperator (but that functionality is now removed from duckdb)