3 ms·

Using SQLite in the browser is great until you have users that open your app in more then one browser tab.

samwillis 4y ago
See here: https://sqlite.org/wasm/doc/trunk/persistence.md#vfs-locking https://sqlite.org/wasm/doc/trunk/persistence.md#vfs-locking It's mostly works with OPFS but with a few edge cases. They are working with the browser vendors on improving this in OPFS. Alternatively run it in a SharedWorker (https://developer.mozilla.org/en-US/docs/Web/API/SharedWorker https://developer.mozilla.org/en-US/docs/Web/API/SharedWorke...), which seems to have made a comeback after being dropped for security concerns. Or do some sort of leader election with browser tabs and use a BroadcastChannel.
- tantaman 4y ago
  SQLite WASM uses SharedArrayBuffers and Atomics to turn async OPFS apis into blocking APIs (really just _one_ api -- getSyncAccessHandle) Given the use of SharedArrayBuffers and that SharedArrayBuffers can't be used in a shared worker (funny), SQLite WASM doesn't work in shared workers. If getSyncAccessHandle was made synchronous then maybe everything would just work. It'd also improve SQLite WASM perf by 30% according to their measurements [1] [1] https://sqlite.org/forum/forumpost/af64f73911e5410cf9a640c9e3713df2eb7963c7f08d954738a5f456a5d8a4f1 https://sqlite.org/forum/forumpost/af64f73911e5410cf9a640c9e...
tantaman 4y ago
What's wrong with many tabs? The DB handles getting hit by many tabs correctly -- it's just as if many processes were hitting the db in a non-browser environment. Example of syncing your app across tabs with SQLite here: https://vlcn.io/docs/guide-solving-tabs https://vlcn.io/docs/guide-solving-tabs
omniglottal 4y ago
If your app cannot handle concurrency while your back-end i/o is via a machine-local singleton, that sounds like a a problem which will come up for more than just multiple tabs. If architecting around this known-fact is a challenge, an engineer may have bigger problems.
Existenceblinks 4y ago
Or many devices while data is consistent. Where is source of truth. Per user or per resource or per service. A lot of questions.
deleted 4y ago
[deleted]
littlecranky67 4y ago
You can't write to SQLite from multiple processes either, so you mitigate that exactly the same way you do for a desktop app (i.e. show "Can't access DB for writing because other instance is running")
- zackees 4y ago
  [dead]
- bourgeoismedia 4y ago
  You can write from multiple processes and this is enabled by default (see PRAGMA locking_mode [1]). If you've got multiple writers you might have to occasionally handle SQLITE_BUSY and retry. [1] https://www.sqlite.org/pragma.html#pragma_locking_mode https://www.sqlite.org/pragma.html#pragma_locking_mode

See here: https://sqlite.org/wasm/doc/trunk/persistence.md#vfs-locking https://sqlite.org/wasm/doc/trunk/persistence.md#vfs-locking It's mostly works with OPFS but with a few edge cases. They are working with the browser vendors on improving this in OPFS. Alternatively run it in a SharedWorker (https://developer.mozilla.org/en-US/docs/Web/API/SharedWorker https://developer.mozilla.org/en-US/docs/Web/API/SharedWorke...), which seems to have made a comeback after being dropped for security concerns. Or do some sort of leader election with browser tabs and use a BroadcastChannel.

tantaman 4y ago
SQLite WASM uses SharedArrayBuffers and Atomics to turn async OPFS apis into blocking APIs (really just _one_ api -- getSyncAccessHandle) Given the use of SharedArrayBuffers and that SharedArrayBuffers can't be used in a shared worker (funny), SQLite WASM doesn't work in shared workers. If getSyncAccessHandle was made synchronous then maybe everything would just work. It'd also improve SQLite WASM perf by 30% according to their measurements [1] [1] https://sqlite.org/forum/forumpost/af64f73911e5410cf9a640c9e3713df2eb7963c7f08d954738a5f456a5d8a4f1 https://sqlite.org/forum/forumpost/af64f73911e5410cf9a640c9e...

tantaman 4y ago

What's wrong with many tabs? The DB handles getting hit by many tabs correctly -- it's just as if many processes were hitting the db in a non-browser environment. Example of syncing your app across tabs with SQLite here: https://vlcn.io/docs/guide-solving-tabs https://vlcn.io/docs/guide-solving-tabs

omniglottal 4y ago

If your app cannot handle concurrency while your back-end i/o is via a machine-local singleton, that sounds like a a problem which will come up for more than just multiple tabs. If architecting around this known-fact is a challenge, an engineer may have bigger problems.

Existenceblinks 4y ago

Or many devices while data is consistent. Where is source of truth. Per user or per resource or per service. A lot of questions.

deleted 4y ago

[deleted]

littlecranky67 4y ago

You can't write to SQLite from multiple processes either, so you mitigate that exactly the same way you do for a desktop app (i.e. show "Can't access DB for writing because other instance is running")

zackees 4y ago
[dead]
bourgeoismedia 4y ago
You can write from multiple processes and this is enabled by default (see PRAGMA locking_mode [1]). If you've got multiple writers you might have to occasionally handle SQLITE_BUSY and retry. [1] https://www.sqlite.org/pragma.html#pragma_locking_mode https://www.sqlite.org/pragma.html#pragma_locking_mode