A future for SQL on the web (2021) | Hacker News Reader

A future for SQL on the web (2021)

jlongster.com

80 pointsthunderbong25 comments

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

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

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

[2]: https://github.com/phiresky/sql.js-httpvfs

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.

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

"Low traffic is a happy place - I don't have any motivation to mess with that."

> "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.

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.

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.

Personally I think vector lookups make more sense as a new kind of index for existing databases, as opposed to an entirely new class of database.

Completely agree.

Have you my chance come across a good SQLite vector extension that works in the browser? I haven't found one yet.

sqlite-vss mentions WASM in a few places - https://github.com/search?q=repo%3Aasg017%2Fsqlite-vss%20was... - I'm not sure how easy it is to get that running in the browser though.

> 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.

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 :)

Have you checked out Anytype[0]. Was recently posted/discussed here on HN a few days ago[1].

No affiliation, just interesting project that aligns with your description.

[0]: https://anytype.io/ [1]:https://news.ycombinator.com/item?id=38794733

If you're looking for a very real use case of SQLite in the browser, there's SQLSync[1]. It was recently discussed on HN[2].

[1]: https://sqlsync.dev/posts/stop-building-databases/, https://sqlsync.dev/

[2]: https://news.ycombinator.com/item?id=38489307

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?

All data are local; this allows you to use sql.js in the browser with persistence.

Unless the developer adds synchronisation primitive to sync data between multiple clients all data are local.

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.

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.

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?

Discussed at the time:

A future for SQL on the web - https://news.ycombinator.com/item?id=28156831 - Aug 2021 (218 comments)

Reply on news.ycombinator.com