5 ms·

We now have absurd-sql, wa-sqlite, and the official SQLite project all running in a browser. Their tradeoffs are discussed here https://www.powersync.com/blog/s

We now have absurd-sql, wa-sqlite, and the official SQLite project all running in a browser. Their tradeoffs are discussed here https://www.powersync.com/blog/sqlite-persistence-on-the-web https://www.powersync.com/blog/sqlite-persistence-on-the-web That article was written in July, and in the intervening time, Chrome's announced an intent to ship "Multiple Readers and Writers in File System Access API" aka `readwrite-unsafe` [0] in v121 (stable in 2 weeks) which could help improve SQLite's performance even more. [0] https://chromestatus.com/feature/5172892632875008 https://chromestatus.com/feature/5172892632875008 Edit: It looks like wa-sqlite already has a prototype VFS taking advantage of the above feature https://github.com/rhashimoto/wa-sqlite/discussions/116 https://github.com/rhashimoto/wa-sqlite/discussions/116

Ciantic 3y ago
I couldn't find do these wrappers support read-only SQLite databases with HTTP range requests, like in this famous post [1]. Phiresky's wrapper supports it, but it seems to be rebuilding the whole sql.js [2], I'd rather have it as VFS on top of sqlite.org's own WASM module. I like the idea of HTTP range requests, but I don't want to run a fork, that will be unmaintained in few years. [1]: https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630 [2]: https://github.com/phiresky/sql.js-httpvfs https://github.com/phiresky/sql.js-httpvfs
- samwillis 3y ago
  It would be quite hard to do a HTTP range request VFS for the official build as it's sync only, lots of hoops to jump through. The wa-sqlite build on the other hand is available as both a sync and async build, and so should be quite possible (you can easily build an async VFS). Roy has a great range of example VFSs you can learn from too.
  - sgbeal 3y ago
    > It would be quite hard to do a HTTP range request VFS for the official build as it's sync only, lots of hoops to jump through. As the developer of that build, i wholeheartedly confirm that. wa-sqlite makes use of asyncify, which is a feature we do not want to make use of in the canonical distribution because (to make a long story short) it's third-party voodoo which can be pulled out from under us, or break in incompatible ways, at any time, whereas the sqlite project has a long history of posting only its own code, without third-party dependencies. It's JS/WASM build necessarily depends on Emscripten, but we've also reimplemented all of that glue except for the parts which provide the WASM imports, which are closely tied to the compilation process so cannot simply be swapped out. > Roy has a great range of example VFSs you can learn from too. FWIW, his work has been a tremendous inspiration for the sqlite project's JS code, and the 2nd OPFS VFS is a direct port of one of his VFSes.
    - samwillis 3y ago
      The work that both Roy and yourself have put into SQLite in the browser is awesome, there are so many exciting things coming that are going to be built on top of it. Thank you!
      - Ciantic 3y ago
        It seems indeed very nice, the examples are clear and everything works from async. I tried with Deno, and now experimenting with my own VFS. It does seem to suffer from maintainer problems too though, and I don't blame Roy Hashimoto for that. I wouldn't want to maintain such an obvious wrapper when it should be a task for SQLite's team to upstream the changes. Roy Hashimoto doesn't want to maintain it as an NPM package for instance, as it is just an experiment: https://github.com/rhashimoto/wa-sqlite/issues/12 https://github.com/rhashimoto/wa-sqlite/issues/12 "Low traffic is a happy place - I don't have any motivation to mess with that."
sgbeal 3y ago
> "Multiple Readers and Writers in File System Access API" aka `readwrite-unsafe` in v121 (stable in 2 weeks) which could help improve SQLite's performance even more. As your EDIT notes, Roy (wa-sqlite) has already experimented with this and reports great results. Experimenting with this in the sqlite project's own OPFS VFS (see https://sqlite.org/wasm https://sqlite.org/wasm) is pending, but the feature is not yet widespread enough to replace the current VFSes. We've no information on how long it will take for the other browsers to catch up with that API. Until then, the sqlite project offers two OPFS VFSes, one of which trades speed for a moderate degree of cross-tab concurrency and another which offers tremendous speed but a complete lack of concurrency.

Ciantic 3y ago

I couldn't find do these wrappers support read-only SQLite databases with HTTP range requests, like in this famous post [1]. Phiresky's wrapper supports it, but it seems to be rebuilding the whole sql.js [2], I'd rather have it as VFS on top of sqlite.org's own WASM module. I like the idea of HTTP range requests, but I don't want to run a fork, that will be unmaintained in few years. [1]: https://news.ycombinator.com/item?id=27016630 https://news.ycombinator.com/item?id=27016630 [2]: https://github.com/phiresky/sql.js-httpvfs https://github.com/phiresky/sql.js-httpvfs

samwillis 3y ago
It would be quite hard to do a HTTP range request VFS for the official build as it's sync only, lots of hoops to jump through. The wa-sqlite build on the other hand is available as both a sync and async build, and so should be quite possible (you can easily build an async VFS). Roy has a great range of example VFSs you can learn from too.
- sgbeal 3y ago
  > It would be quite hard to do a HTTP range request VFS for the official build as it's sync only, lots of hoops to jump through. As the developer of that build, i wholeheartedly confirm that. wa-sqlite makes use of asyncify, which is a feature we do not want to make use of in the canonical distribution because (to make a long story short) it's third-party voodoo which can be pulled out from under us, or break in incompatible ways, at any time, whereas the sqlite project has a long history of posting only its own code, without third-party dependencies. It's JS/WASM build necessarily depends on Emscripten, but we've also reimplemented all of that glue except for the parts which provide the WASM imports, which are closely tied to the compilation process so cannot simply be swapped out. > Roy has a great range of example VFSs you can learn from too. FWIW, his work has been a tremendous inspiration for the sqlite project's JS code, and the 2nd OPFS VFS is a direct port of one of his VFSes.
  - samwillis 3y ago
    The work that both Roy and yourself have put into SQLite in the browser is awesome, there are so many exciting things coming that are going to be built on top of it. Thank you!
    - Ciantic 3y ago
      It seems indeed very nice, the examples are clear and everything works from async. I tried with Deno, and now experimenting with my own VFS. It does seem to suffer from maintainer problems too though, and I don't blame Roy Hashimoto for that. I wouldn't want to maintain such an obvious wrapper when it should be a task for SQLite's team to upstream the changes. Roy Hashimoto doesn't want to maintain it as an NPM package for instance, as it is just an experiment: https://github.com/rhashimoto/wa-sqlite/issues/12 https://github.com/rhashimoto/wa-sqlite/issues/12 "Low traffic is a happy place - I don't have any motivation to mess with that."

sgbeal 3y ago

> "Multiple Readers and Writers in File System Access API" aka `readwrite-unsafe` in v121 (stable in 2 weeks) which could help improve SQLite's performance even more. As your EDIT notes, Roy (wa-sqlite) has already experimented with this and reports great results. Experimenting with this in the sqlite project's own OPFS VFS (see https://sqlite.org/wasm https://sqlite.org/wasm) is pending, but the feature is not yet widespread enough to replace the current VFSes. We've no information on how long it will take for the other browsers to catch up with that API. Until then, the sqlite project offers two OPFS VFSes, one of which trades speed for a moderate degree of cross-tab concurrency and another which offers tremendous speed but a complete lack of concurrency.