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.
> "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) 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.
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.
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.
> 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.
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!
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.
Whenever WASM SQLite comes up there is discussion as to if it would have been better for WebSQL to have become a cross browser standard.
I believe this route with WASM is the correct one. WebSQL would have been tied to one single version of SQLite, with no alternative implementation.
With browsers adopting safe low level APIs like WASM and OPFS it enables a much broader range of databases to be available in the browser. We already have SQLite, DuckDB and various vector dbs.
OPFS is still under active development, but with some of the changes coming to it in 2024 it's going to become significantly better to use.
All of this is part of the enabling tech behind "local-first" apps. I'm somewhat biased as I work on ElectricSQL (we sync Postgres on a server to SQLite in the browser), but 2024 is going to be a supper exciting time for local-first software.
> Whenever WASM SQLite comes up there is discussion as to if it would have been better for WebSQL to have become a cross browser standard.
An oft-neglected detail in such discussion is that WebSQL was main-thread-only. When WebSQL was designed that was not a serious issue, but it would have been in conflict with the directions web design has since taken, making WebSQL a non-starter for many modern apps.
> WebSQL would have been tied to one single version of SQLite, with no alternative implementation.
Not only that, but with a castrated feature set (e.g. only implicit transactions and lack of many of sqlite's SQL functions). When trying to benchmark WebSQL vs the sqlite project's WASM build, that castration makes it difficult to get apples-to-apples comparisons.
What are your thoughts around the various vector dbs (pinecone, etc)?
DuckDB or pgVector or with all of the work going forward in Arrow being able to already support vectors/arrays, it seems that the specific “vector” class of db is hype/marketing.
Just leaving a short mention for ElectricSQL, which is explicitly built by the co-inventors of CRDTs to solve the exact issues discussed here. The docs use wa-SQLite by default, but AFAIK it’s flexible. It’s one of those projects where you can just tell how smart the design is based on how helpful and clear the docs are
I have a pet idea to write a Notion-like tool that works like an old-school desktop app - meaning you should be able to export your pages or the entire database as a single sql file and it should be open-able from somewhere else.
I also want to leverage the web platform (mainly because it's the right thing to do and partly because the web is the only one that offers a decent rich-editor environment without as much plumbing needed if I build natively). So basically a browser app that behaves fully like a desktop app without any needing internet connection.
And for that idea to become reality james long's absurd sql is basically the key. Without such persistent sql based db to work with, that ideas is never going to materialize :)
The problem it's trying to solve is better addressed by removing the need to cache so much data. It's hard enough to manage one database without then having to deal with replication, and cache invalidation on top of it all.
Treating a browser like a real client application is putting lipstick on a pig. The harder you try, the uglier it gets.
How does this handle permissions over data? I wouldn't want a user accessing private information of another. How would you "hide parts of a table" to a client-side reader?
You really have to shift thinking away from the centralized-database model in order to fully understand why this question seems ill-informed.
If you recall from the SQLite story, it was created, in-part, to provide a local cache to an offline device yet still have the same query interface that developers enjoy. Because the model of access is shifted to the device, anything you populate a client-side SQL database with is already data that is accessible by the client.
Synchronization in this space being covered under the term “Local First” Web Applications, if anyone wants to look into it more. Almost all the frameworks I saw in that space have access controls built in - both between clients and for privileged server-only data.
I agree with the other commenter though that by putting a DB on the client, you’re implicitly trusting that client more than usual to only make acceptable changes to the DB. If you’re working in a sensitive context, I think Local First means you’ll have to spend some extra time planning out data security
which is a big limitation given that Safari will destroy all the data after one week of no usage. The idea is great, but these limitations make it basically useless on all iPhones.
the problem with these local approaches is that Safari would destroy all the data after one week of no usage. So we need some way to backup the data either on file locally, or on a server, which is not easy and not part of these approaches.
Disgraceful that Apple does this in the name of privacy, when this implies less privacy and pushes people to either build apps (with much of Apple's economic gain) or some centralised database (at the expense of data protection).
Is there any browser based databases with backend synchronisation and E2E encryption included?
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
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