How well does this library play with multiple connections?

[Vanilagy]

First of all, awesome job with this library. I also apologize if the answer to this question is already in here somewhere, I couldn't find it easily.

I'm using it in the "IDBBatchAtomic relaxed" mode, and I was wondering how well this library behaves in the presence of multiple connections. I am talking both:

• Multiple connections within the same tab
• Multiple tabs, each with a single connection

Now, given that IndexedDB is scoped per origin, there shouldn't be much of a difference between these two cases. For the OPFS, for example, I've heard that multiple connections do not behave nicely (like they do on a real file system), and only one handle is permitted. How is it with IndexedDB? Can I expect transactions from multiple connections to behave as expected?

[rhashimoto]

Now, given that IndexedDB is scoped per origin, there shouldn't be much of a difference between [multiple connections in one context and multiple tabs].

These are actually pretty different cases. IDBBatchAtomicVFS requires an asynchronous WebAssembly build, currently with Asyncify, and that doesn't allow multiple simultaneous asynchronous calls into a WebAssembly instance. In theory you could still have multiple SQLite connections in the same WebAssembly instance but you would have to be careful to query only one of them at a time. So it's not very practical and I'm not sure anyone has actually tried it.

You could create multiple SQLite WebAssembly instances in the same context, creating only one connection in each, and that would have a much better chance of working. I'm not sure if anyone does this either, though.

IDBBatchAtomicVFS does work nicely from multiple contexts - i.e. multiple Windows, dedicated workers, service worker, or shared worker. You can try it yourself by opening the demo on multiple tabs (and selecting the appropriate VFS and runtime). IndexedDB is the only persistent browser storage that works on every kind of context.

It isn't easy to use OPFS concurrently on multiple contexts, but OriginPrivateFileSystemVFS does work...as long as each context is a dedicated Worker. This VFS works around that one open handle per file limitation by closing its handle when another context needs access. Chromium browsers have removed this restriction but we're still waiting to see what the others will do.

There is a table comparing the example VFS classes in the upcoming 1.0 release here.

[Vanilagy]

I have a question regarding how to properly set up multiple connections within the same tab. Right now, my code for obtaining a single connection is as follows:

// 1
let module = await SQLiteAsyncESMFactory();

// 2
let sqlite = SQLite.Factory(module);

// 3
let vfs = new IDBBatchAtomicVFS(dbName, {
    durability: 'relaxed',
    onUpgradeNeeded(idb) {
        idb.createObjectStore('blobs');
    }
});

// 4
sqlite.vfs_register(vfs);

// 5
let db = await sqlite.open_v2(dbName, undefined, dbName);

Which of these steps do I need to repeat to create a second connection? Obviously, I can repeat all of them, but is there a smaller subset I could repeat? Can I drop step 1 for the second connection and reuse the existing module? Or do I also need to recreate the module, as that's where the Asyncify stuff happens?

[rhashimoto]

I believe repeating all these steps is needed for independent Asyncify connections.

The stock IDBBatchAtomicVFS doesn't take an onUpgradeNeeded option unless you have modified it for your own use.

[Vanilagy]

Oh yeah that's my own modification. And thanks, I'll just repeat all the steps then. Doesn't even take that long, around ~10 ms.

[Vanilagy]

Thanks for your response! 😊

You could create multiple SQLite WebAssembly instances in the same context, creating only one connection in each, and that would have a much better chance of working.

This is what I'm planning to do. In my mind, this should be almost equivalent in behavior to having multiple tabs, each with one connection. Correct?

IDBBatchAtomicVFS does work nicely from multiple contexts - i.e. multiple Windows, dedicated workers, service worker, or shared worker. You can try it yourself by opening the demo on multiple tabs (and selecting the appropriate VFS and runtime). IndexedDB is the only persistent browser storage that works on every kind of context.

This is awesome to hear. Is this because IndexedDB offers transactions? Can I expect it to work like SQLite does on a normal file system? To be specific, that means:

• n concurrent readers OR
• 1 writer

I'm going to assume WAL mode won't be much of a possibility, given what you write in your table. Reading while another connection writes would be awesome, though...

---

I was originally planning on switching over from this library to the official SQLite WASM NPM library, but given the multi-tab limitations of that version, I don't find it that attractive anymore. The question that's still on the table for me is whether I should switch from an IDB-based solution to an OPFS-based solution.

In the benchmarks, the OPFS solutions seem to have very strange performance characaters. My intuition would be that they would easily outperform IDB in every metric because of the synchronous SQLite build, but they don't. How do you explain this? Seems like you dive into this here, I'll read up on it sometime.

With the current state of how browsers are, do you see any advantage over the OPFS solution compared to the IDB solution?

[rhashimoto]

[multiple SQLite WebAssembly instances in the same context] is what I'm planning to do. In my mind, this should be almost equivalent in behavior to having multiple tabs, each with one connection. Correct?

I guess so, except that everything will be happening on one thread.

Can I expect it to work like SQLite does on a normal file system? To be specific, that means:

• n concurrent readers OR
• 1 writer

The example VFS classes all do exclusive locking by default, so only one transaction (read or write) at a time. You can change this for IDBBatchAtomicVFS to shared locking for multiple readers with a 1-line change, instructions here (this will change in 1.0). Note that if you introduce shared locking your application must handle SQLITE_BUSY properly, some discussion here.

I'm going to assume WAL mode won't be much of a possibility, given what you write in your table. Reading while another connection writes would be awesome, though...

FLOOR in that table is an experimental write-ahead logging VFS that allows multiple readers and a writer simultaneously. Right now it only works on Chromium browsers and isn't open source.

With the current state of how browsers are, do you see any advantage over the OPFS solution compared to the IDB solution?

If you mostly read from your database on a single connection and don't hammer it with small write transactions or multiple connection reads, then OPFS will likely be faster. It is also a much simpler API than IndexedDB which might lead to fewer bugs, all other things being equal.

[Vanilagy]

Thank you for your advice and detailed instructions, I'll take them to heart!

It is still crazy to me how hard it is to have an SQLite setup on the web that's simple and directly analogous in behavior to the native variant. It's sad that these layers of hacks and abstractions need to be applied, but I'm very glad you're investing your time into this, know I truly appreciate this!

My theory/hope would be that OPFS will, as browsers optimize it more and add more feature, become the obvious best choice. After all, allow SQLite to run on it is kind of one of its main goals. (It replaces WebSQL)

[rhashimoto]

It is still crazy to me how hard it is to have an SQLite setup on the web that's simple and directly analogous in behavior to the native variant. It's sad that these layers of hacks and abstractions need to be applied, but I'm very glad you're investing your time into this, know I truly appreciate this!

Thanks, but it's totally normal for there to be challenges in porting software to an unsupported platform (and language), especially for software as close to the metal as SQLite and for a platform as new as the web, which hasn't even been considered an actual "platform" for very long. Consider that the SQLite VFS for UNIX/Linux is over 8000 lines of code. The entire wa-sqlite code base, including its 7 VFS classes, is about 6000 LoC. The SQLite folks write more comments than I do and JavaScript is more expressive than C, so I think one could argue that the complexity is roughly comparable.