ST_Read in @duckdb/duckdb-wasm with user-uploaded Files

[parkerziegler]

I'm currently trying to integrate @duckdb/duckdb-wasm in a system I'm working on, but I'm struggling a bit to understand how I might support user-uploaded files (i.e., File objects) with ST_Read. My initial attempt involved using the registerFileHandle API with a GeoJSON file like so:

async function createTable(file: File, db: AsyncDuckDB, tableName: string): Promise<void> {
  const conn = await db.connect();
  await $db.registerFileHandle(
    file.name,
    file,
    DuckDBDataProtocol.BROWSER_FILEREADER,
    true
  );
  await conn.query(
    `INSTALL spatial;
    LOAD spatial;
    CREATE TABLE ${table_name} AS SELECT * FROM ST_Read('${file.name}');`
  );
}

My understanding (which is very cursory, just getting going here), is that registerFileHandle would create a file named file.name in DuckDB's virtual file system, which ST_Read could then access. Unfortunately, executing this code yields the following error:

Error: IO Error: Unknown file type
    at pa.runQuery (duckdb-browser-eh.worker.js:1:741917)
    at Lo.onMessage (duckdb-browser-eh.worker.js:1:729928)
    at Bc.globalThis.onmessage (duckdb-browser-eh.worker.js:1:763450)

I wasn't able to track down this exception in either the @duckdb/duckdb-wasm repo or in this repo, so I'm curious if it's thrown by GDAL. I'd also expect files ending with .json or .geojson to be picked up, so the "Unknown file type" error came as a bit of a surpise. I've attached the GeoJSON file I'm using below.

In any case, does anyone have experience getting ST_Read to work with File objects in @duckdb/duckdb-wasm? Using the above query (i.e., the CREATE TABLE ${table_name} AS SELECT * FROM ST_Read('...')) works like a charm with urls, and I'd love to get it working with Files as well (since lots of my users are bringing in their own data not hosted on any remote server / storage). Thanks!

powerplants.json

[carlopi]

I think you should use BROWSER_FSACCESS instead of BROWSER_FILEREADER.

BROWSER_FILEREADER expect basically a blob as input, that can also work for files of limited files if you do the conversion to Uint8Array yourself and then pass that to registerFileHandle.

If you manage with BROWSER_FSACCESS great, otherwise I will later set up an example of this. This feature is used by https://shell.duckdb.org via .open instruction, but following that code is probably a bit too detailed / involved, and this qualify as an example that should be present in the duckdb-wasm readme somehow.

[parkerziegler]

Thanks @carlopi! That worked great 😄 I'd be happy to PR an example to this repo or @duckdb/duckdb-wasm depending on which you think is more appropriate. And thanks for such a speedy reply!

[carlopi]

I'd say either to duckdb/duckdb-wasm, as a test (somewhere here: https://github.com/duckdb/duckdb-wasm/tree/main/packages/duckdb-wasm/test) or as an improvement to this page: https://duckdb.org/docs/api/wasm/data_ingestion (top right there is a button to improve the page).

Thanks!