Basic questions about storing thousands of JSONs

[hyperknot]

Thank you for your work on wa-sqlite.

I've been reading up on various approaches, including Notion's blog post about concurrency issues, and wanted to ask a few basic questions to help guide my implementation.

My use case is fairly simple: I’d like to build a key-value store with around 10,000 JSON documents, each identified by a UUID. Each entry might also have associated binary attachments (like JPEGs), which I can store in base64 or as binary.

A few questions I'm hoping to get your thoughts on:

On modern browsers, what would you recommend for reliable, concurrent-friendly storage, especially with wa-sqlite?
I don’t expect concurrent edits to the same document, but I do want to ensure data integrity if users have multiple browser tabs open. I mean if there are concurrent writes to different documents from different browser tabs at the same time, I want to make sure the full DB is not corrupted.

Can wa-sqlite fully handle that scenario with one of the plugins/backends? Or I still need to implement some locking mechanism as in the Notion blog?

Would it be reasonable (or better) to skip SQLite entirely and store JSON files directly in OPFS? I don’t need advanced SQL features, I'm mainly looking for reliability and simplicity over time, with total storage potentially growing to a few hundred MB. If I understand correctly, the individual JSON files on OPFS would allow me not to care about corruption at all.

Thanks in advance for any insights you can provide.

[rhashimoto]

My use case is fairly simple: I’d like to build a key-value store with around 10,000 JSON documents, each identified by a UUID. Each entry might also have associated binary attachments (like JPEGs), which I can store in base64 or as binary.

For your use case I would probably use plain IndexedDB and not SQLite. IndexedDB is scorned by many developers and its API is annoying (a wrapper like idb makes it more usable). But as a local key-value store for Javascript objects, ACID and persistent, where you don't need to expose SQL as the query language, it's a great match.

The advantages of plain IndexedDB over SQLite would be:

• Smaller app size (no WebAssembly)
• Easier bundling.
• Direct visibility in browser dev tools.

Storing each JSON file as an OPFS file is a possible alternative, but the OPFS API doesn't give you atomicity so a file could be only partially written if interrupted by tab closure or crash. If that is important then you would need to add that yourself, e.g. by including some sort of checksum. Also OPFS access handles are only available in Worker contexts and can't be used for simultaneous access to the same file except on Chromium browsers. OPFS access handle reads and writes are very fast, faster than IndexedDB fetches, but opening and closing access handles is not fast so I would actually expect IndexedDB to be more performant than having many individual OPFS files.

[hyperknot]

Thank you for your detailed answer!

For me, the biggest reason to go with OPFS is that I could use an identical directory structure on a web app and in an Electron based desktop app. Same directories, same files, etc.

Also, I found IndexedDB performance to be quite bad on Firefox for some reason. I was playing around on https://demo.agektmr.com/storage/ and Firefox was frequently breaking/stalling when I was testing these IndexedDB operations on it. For example Delete All could stall it many times.

But this might be something related to https://demo.agektmr.com/storage/, as you've have probably run into the same issues with the ID backends on Firefox already.

Also, I've been reading some of the OPFS code and it really looks like you need to work around browser quirks a lot. For simplicity I might just take you advice and choose IndexedDB.

[hyperknot]

Down the rabbithole I went. Started benchmarking IndexedDB performance, with batched operations. 500 files per batch this time. I made a benchmark for this.

What's interesting is that Safari has an absolutely terrible performance when it comes to overwriting keys.

Outside of the benchmark performance, what's concerning with Safari is that if I understand correctly, it empties the IndexedDB after 7 days of not visiting a site. So basically a single holiday is enough to delete all data.

https://webkit.org/blog/10218/full-third-party-cookie-blocking-and-more/

[rhashimoto]

Outside of the benchmark performance, what's concerning with Safari is that if I understand correctly, it empties the IndexedDB after 7 days of not visiting a site.

That is concerning. I didn't know about that, and I don't remember encountering it. Safari is supposed to support navigator.storage.persist(), so if that call succeeds and it deletes on its own that would violate the spec.

[hyperknot]

The article says that it's only if "Prevent Cross-site tracking" is enabled, which it is by default on modern versions.

Very relevant SO: https://stackoverflow.com/questions/50795409/is-indexeddb-on-safari-guaranteed-to-be-persistent

[hyperknot]

I looked into persist, but I couldn't trigger it on Safari, with or without Cross-site tracking enabled.
On Chrome sometimes asking for a Notification help. I've pushed my tests here:
https://idb-opfs.hyperknot.com/