Persist filesystem changes after page refresh

[adamziel]

What problem is this issue trying to solve?

At the moment, all database changes and uploads are gone once the page is refreshed. Preserving them would be useful for courses, technical demos, even sharing a link to your changes.

What solution does this issue propose?

A few ideas:

• OPFS. Related Emscripten PR: Add support for read/seek on files in the native file system
• localStorage could store the binary contents of the .sqlite file every few seconds
• An "audit trail" of all SQL writes could be stored and re-applied on refresh
• (in the future) The sqlite file could reside in the persistent Filesystem API
• https://github.com/jlongster/absurd-sql can persist SQLite database in IndexedDB See https://sqlite.org/wasm/doc/tip/about.md

[adamziel]

Relevant: https://sqlite.org/wasm/doc/tip/about.md

Specifically:

James Long's aptly-named absurd-sql demonstrates persistent browser-side sqlite3 by storing the databases in IndexedDB storage. We experimented with that approach but it's... well, absurd 😉. It's too slow for anything beyond trivial databases and it's easy to get a database in an inconsistent state by visiting the page from two tabs.

[eliot-akira]

About persisting changes to the database, I recently learned about SQLite's Session extension.

The session extension provide a mechanism for recording changes to some or all of the rowid tables in an SQLite database, and packaging those changes into a "changeset" or "patchset" file that can later be used to apply the same set of changes to another database with the same schema and compatible starting data. A "changeset" may also be inverted and used to "undo" a session.

I read about it in an article about someone developing an iOS app with a local SQLite database (full copy of the user data in the app itself).

The session extension lets you start a session on an SQLite connection. All changes made to the database through that connection are bundled into a patchset blob. The extension also provides method for applying the generated patchset to a table.

This can be used to build a very simple client-sync system. Collect the changes made in a client, periodically bundle them up into a changeset and upload it to the server where it is applied to a backup copy of the database.

Perhaps this could be a lighter-weight approach to saving SQLite state in browser local storage, by storing (and restoring) the session history instead of a snapshot of the whole database.

[swissspidy]

Related: https://developer.chrome.com/blog/sqlite-wasm-in-the-browser-backed-by-the-origin-private-file-system/