Suggested way to persist a database across browser refreshes

[david542542]

Take the following scenario within a browser:

1. A user creates a table, such as from a csv file (CREATE TABLE tbl AS SELECT * FROM 'myfile').
2. A user performs a modification to the table (ex DELETE FROM tbl WHERE id < 10).
3. A user now refreshes the page.

We want to be able to reload the table that the user had just been prior to refreshing the page. We can write a parquet file using something like https://wicg.github.io/file-system-access/. However, what should be done about the additional data modifications after the first initial import? Ideally we'd like to have a concept similar to an initial base db + a WAL or something (rather than having to re-save the parquet file after each modification, which, depending on the size of the table, could take a long time).

What would you suggest to do here to accomplish this?

[sgilligan]

We're experimenting with writing the database as an arrow IPC file to indexedDB using idb-keyval, and then reading back in to duckdb on reload. It works well so far, but we haven't pushed file sizes yet.

[david542542]

@sgilligan thanks for the response. What happens when there is an update (say, a row is inserted or deleted)? Do you re-save the entire database, or how do you deal with any changes? Also, what do you mean by we haven't pushed file sizes yet. ?

[sgilligan]

At this point, we don't have that need (syncing with inserts/deletes). Our use-case is customers importing their own data for analysis against existing system data (with all imports retained client-side to support privacy concerns of health data). As to file sizes, our typical size is not much more than 500mb. If you had a 200gig file to persist and restore with indexedDB you'll surely hit bottlenecks to explore, but indexeddb is only constrained by disc space so it still may be an option. The limit I guess will be what size duckdb instance the browser can cope with (we only test to 1 gig).

[ravwojdyla]

@sgilligan thanks for the response here, do you happen to have any reference code?

Also btw @sgilligan, in case you have not seen it: #1230 and #1490