IndexedDB native file system handler for saving .db files

[stikkireddy]

Hey I would like to be able to open a duckdb read/write duckdb instance from native-file-system-handler is there any examples of doing this. I tried various ways but i was not able to see a option to allow both readwrite from duckdb to a file system adapter. Any examples of this?

[Jonarod]

Would like to get a grasp at it as well, following other documentations, I tried this so far:

// importing duckDB and creating the db...
// ...
const c = await db.connect('test.db'); // HOPING this would init a connection to a file 'test.db' and save everything to it

await c.query(`CREATE TABLE people(id INTEGER, name VARCHAR);`);
await c.query(`INSERT INTO people VALUES (1, 'Mark');`);
const query = await c.query(`SELECT * FROM people;`);

unfortunately I have no trace of test.db file created locally. But just in case the file might have been stored in some virtual file system in the browser, I also tried this AFTER QUITTING MY APP:

// importing duckDB and creating the db...
// ...
const c = await db.connect('test.db');

// ELIMINATED THE INSERTS, trying to check if duckDB could find the data from `test.db` 
const query = await c.query(`SELECT * FROM people;`);

but it returns an error complaining that people is not a valid table etc...

So I wonder if duckdb-wasm is WASI compliant. If not, is there a way to manually hand a file stream (or something else) as a pre-saved database ? Or is duckdb-wasm to be used on memory only ?

Thanks !

[carlopi]

Hi!

I would recommend this section of the launch duckdb-wasm article: https://duckdb.org/2021/10/29/duckdb-wasm.html#how-to-get-data-in

Basically DuckDB-Wasm has it's own file system, BUT there is lots of flexibility in how to get data in or out.

I hope this provide some guidance, but do come back either with more questions!

Also, this is probably a generally interesting snippet of code, a contribution with your finding would be welcome (probably in a sub-folder of exampes folder).

[carlopi]

This I believe would answer both @Jonarod and to some degree the original @stikkireddy question.

[Jonarod]

Thank you very much for your reply, really.

I have read the post, and it indeed provide ways to get data IN and OUT. Thank you. Also, after reviewing a lot of other issues, I found this one:

c.query(`EXPORT DATABASE 'mydb' (format PARQUET);`);

c.query(`IMPORT DATABASE 'mydb';`);

However, in my case (and maybe that was also the case for the initial OP I guess?), the issue is more geared towards read + write operation into an existing db file. All those cases can help to import and export data which is fantastic, but what about importing an existing DB and write to the same existing DB, in other words updating the files?

In fact, EXPORT DATABASE will give an error if we try to overwrite an existing one with the same name, claiming that the file is read-only.

It makes sense when used in the browser since WASM lives in a segregated environment and read+write accesses could be lead to vulnerabilities maybe, but what about other environments? Precisely WASI ones like Wasmedge, Wasmer or even Node itself to name a few., in those environments, WASM could, IMHO, have read+write access in the same stream. What do you think? Is there a way we could see an option to use duckDB-wasm just like we do in Python/Node APIs : db.connect('mydb') and naturally update mydb directly without EXPORT ?

[selwyn-mccracken]

I'd also like to know how to load an existing db and have read/write access on it.

I can load a read only database like this:

1. load the db into the duckdb filesystem - await db.registerFileURL('test.db', /test-data/test.duckdb,DuckDBDataProtocol.HTTP,true)
2. open a read only version of the database - await db.open({ path: 'test.db'})

If instead of step 2 above I try and attach the db (i.e. con.query("ATTACH 'test.db'"), then I get the following error:

Error: Invalid Error: Opening file 'test.db' failed with error: Error: Opening file test.db failed: cannot open file with both read and write flags set
    at sa.fetchQueryResults (duckdb-browser-mvp.worker.js?type=module&worker_file:12:68133)
    at bl.onMessage (duckdb-browser-mvp.worker.js?type=module&worker_file:11:56737)
    at globalThis.onmessage (duckdb-browser-mvp.worker.js?type=module&worker_file:25:9545)  

Any help greatly appreciated.

[carlopi]

Hi @selwyn-mccracken, could you potentially try to use ATTACH 'file.db' (READ_ONLY);?

(see https://duckdb.org/docs/sql/statements/attach.html for reference)

[selwyn-mccracken]

Hi @carlopi - yes con.query(`ATTACH 'test.db' (READ_ONLY)`) works for read-only.

However, I'd also like write access (apologies for being unclear above).

[carlopi]

Hi! What sort of deployment are you using (eg node / hosting a website / ...)?

I believe that keeping things purely SQL side it should emulate the behaviour of the native CLI, so something like:

ATTACH 'https://localhost/test.db';

Should most likely work, and I would expect that on DETACH it should save changes. Could you potentially test whether this works in your set-up?

If this work, then probably there is something to be fixed in the file-system implementation, possibly connected to https://github.com/duckdb/duckdb-wasm/pull/1075, but going through http could potentially be used as a temporary workaround.

[jon-ton]

This seemed like the most similar topic to my question, but has anyone had success running on a non-browser WASM runtime like Wasmtime, Wasmer, WasmEdge, Wazero, etc.?

[rockwotj]

This seemed like the most similar topic to my question, but has anyone had success running on a non-browser WASM runtime like Wasmtime, Wasmer, WasmEdge, Wazero, etc.?

Nom of these support the exception proposal, so you cannot compile using wasi-sdk. emscripten standalone mode might work however

[kesavkolla]

The origin private filesystem is available on all standard browsers. Can the save,load and attach happen from the OPFS?