Hacker News Reader

> What I really want to see next is an eventually consistent sync system between browser and server

We’ve made one: https://electric-sql.com

Active-active SQLite to Postgres with transactional causal+ consistency based on CRDTs.

Disclaimer: founder.

That is awesome! I’m going to have to play with that.

I previously experimented with combining Yjs (CRDT toolkit) with Pouch/CouchDB to create an eventually consistent db, but decided that CouchDB was the wrong backend.

It looks like you have built exactly what I wanted to do if I had had time.

Very cool! Would love to swap ideas if you're up for a chat :)

I clicked through to the technical intro, and it says you’re using AntidoteDB for the top-level replication

https://electric-sql.com/docs/overview/technical-intro

I happen across AntidoteDB just the other day, and my take-away was that it was still under development but not really ready for production use. Curious your opinion / experience with it?

Yup, it’s a very rigorous system developed over ~10 years with solid testing, benchmarking and formal proofs. However it has some known issues, such as behaviour under high load and not currently handling node failure within a DC.

It’s not production ready and neither are we (we’re in developer preview mode, which is like a public alpha [0]).

There are also other aspects on the Antidote roadmap such as efficiently materialising consistent secondary indexes that are ongoing challenges but aren’t so relevant to how we’re using it as a replication layer.

We are working, alongside others, with the Antidote developers to help fix these issues and generally improve reliability / engineer correct behaviour under load. (Professor Annette Bieniusa, who leads the development, is our Chief Architect). We have a fork at electric-sql/vaxine [1].

We are also taking advantage of some simplifications which mitigate the known issues. We have an #antidote channel in the ElectricSQL discord [2] if you’d be interested in chatting more.

[0]: https://electric-sql.com/docs/overview/faqs

[1]: https://github.com/electric-sql/vaxine

[2]: https://discord.gg/B7kHGwDcbj

I’m just going to put the idea here… C# and Blazor, with linq to sql, in the browser… automatically synced to the backend server… that just sounds like magic, but magic I would throw money at.

This looks good. I’m currently exploring Firebase for offline support in Ionic/Angular app, but am much more familiar with SQL. Is capacitor support on the cards?

Hey, yup, definitely. It should be very similar to the Cordova integration.

You can also follow the generic driver integration instructions [0]. Basically need to implement an adapter interface and compose the various utilities.

Happy to help with this — shout on Discord [1] if you’d be interested in collaborating on it.

[0]: https://electric-sql.com/docs/usage/drivers#generic

[1]: https://discord.gg/B7kHGwDcbj

Very interesting! It looks like an Open Source core + Premium features business model (just guessing here).

How heavy is the download? What's the minified client size at the moment?

Yup, open source (https://github.com/electric-sql) + managed replication-as-service. Some of our service code (eg: infra, control plane) is proprietary.

Download size depends on the driver (we support different SQLite drivers for different environments). The web is quite heavy as it loads the SQL.js WASM (as a separate file, it’s not bundled). I want to say ~350-400kb total but I need to check and it depends a bit on how you build/bundle and serve it.

One approach would be to look at the network tab of the browser console when running the web example: https://github.com/electric-sql/examples

> What I really want to see next is an eventually consistent sync system between browser and server

Another CRDT + SQLite project as a native extension: https://github.com/aphrodite-sh/cr-sqlite

Should have a release out here shortly but feel free to browse the code, tests and readme till then to see how it works.

> WASM SQLite with the OPFS is the future of offline first web app development.

is that basically a desktop app that doesn't need to be installed? You still download it using a browser but then you don't install it on your operating system you just run it in your browser?

yea exactly. seems pretty sweet

I find it kinda sad that browsers get to remove Web SQL on shaky grounds (which was just sqlite available to JS) yet here we are, back to that same spot, but with less performance and more complexity

This is somewhat more complex for the developers of websites but dramatically less complex for the developers of web browsers, and that is where we really should be caring: the complexity in the browser leads to both increased attack surface and nigh unto guarantees there will only ever be a single good implementation of the browser. Both standards and security critical systems should be easy to build and easy to reason about.

> I find it kinda sad that browsers get to remove Web SQL on shaky grounds (which was just sqlite available to JS)

i recently had to work with WebSQL in the context of comparing it to sqlite's new WASM support (of which i'm the developer). WebSQL, quite frankly, is a toy. Its execution model is far too limited and excludes all sorts of functionality, not the least of which is that it's impossible to delete a WebSQL db.

> yet here we are, back to that same spot, but with less performance and more complexity

Wrong. We have benchmarked the two in apples-to-apples comparisons, taking into account WebSQL's limitations. The two approaches are roughly equivalent, with both winning out under certain loads, despite WebSQL being implemented in native code.

Who is "we" and where I can see those benchmarks ?

Also even at similar performance you still need to download a bunch of extra stuff to even run the WASM version. My whole webpage takes less than it...

> Who is "we"

We is the sqlite team. i'm the "JS/WASM Guy" for the project.

> and where I can see those benchmarks ?

You can't currently because we don't have them in a user-consumable form. We've done a tremendous amount of benchmarking during the development because All The Speed was one of our design goals. However, all such records were in transient spreadsheets intended for one-shot note-taking use, not publication.

Once our documentation effort settles down, and responding to user feedback from the initial announcement slows down, i hope to implement a benchmarking application similar to:

<https://rhashimoto.github.io/wa-sqlite/demo/benchmarks.html>

Until then, however, you'll simply have to (A) take my word for it, (B) try it out yourself, or (C) none of the above, as you wish. Edit: or (D): we have a WASM port of sqlite's standard benchmarking tool, known as "speedtest1", in the sqlite source tree, but getting it up and running requires reading a good deal of documentation:

<https://sqlite.org/src/dir/ext/wasm&ci=trunk>

That tool is how we've benchmarked it so far, with the exception of comparing it to WebSQL, which required a custom application which is also in that directory (batch-runner.*). batch-runner, however, is in no way user friendly.

I'm a bit confused as to why it supports OPFS, but not the much more widely supported regular FileSystem API. As far as I understand, this is also capable of reading and writing to files directly, and also allows seeking. All the user needs to do is select a file in a file picker first.

Is there a required feature that only OPFS provides, other than file locking (which seems like a pretty non-essential feature tbh)?

> persistent

Question: is it really persisted indefinitely? And will this SQLite file be portable (import and export to somewhere else) ?

It’s not persistent everywhere. On iOS the data is deleted after 7 days of inactivity on a website. It’s treated like a cache.

https://webkit.org/blog/12257/the-file-system-access-api-wit...

https://web.dev/storage-for-the-web/#:~:text=Starting%20in%2....

Missed one part from web.dev

> ...This eviction policy does not apply to installed PWAs that have been added to the home screen.

> And will this SQLite file be portable (import and export to somewhere else) ?

sqlite's storage format is independent of the underlying device. OPFS is "just another backend" and the only tricky part of implementing it was that OPFS's API is largely asynchronous and sqlite3 requires synchronous I/O. The db that's stored in OPFS is exactly what would be stored on your hard drive if you were operating outside of the browser.

I can already imagine the youtube video titles:

"Mozilla and Microsoft TRIED to block it, but COULDN'T stop it!"

Seriously though, I can't wait to use this for my website. This is going to be ridiculously awesome combined with htmx.

What features will this unlock for you?

Serving my htmx app as a WASM app, so interaction by users will be done on their computer. Then only the DB changes will be have to be synced with the backend, saving a lot of traffic / server capacity. This was already possible, but then the user's changes would be lost if they don't sync in time. If I understand things correctly this is the missing piece: persistence. So if the user goes offline and makes changes, those will persist so they can be synced when the user goes online again.

(All of this is as far as I understand things, I could be completely wrong.)

Isn't that model (without the wasm part) alread possible with pouchdb/couchdb?

I haven't looked into those, but I'm happily using SQLite already, and this would mean I won't have to swap it for something else to achieve the same thing.

> If I understand things correctly this is the missing piece: persistence. So if the user goes offline and makes changes, those will persist so they can be synced when the user goes online again.

1. You can persist data with localStorage/IndexDB, can't you? 2. There is nothing in the article about syncing. There is no out-of-the-box solution, as I can see.

> You can persist data with localStorage/IndexDB, can't you?

I guess so, but given the situation that I'd already be using WASM SQLite in the client's browser, then I'd have to implement that part myself, or use something like Absurd SQL. I'd rather use the implementation made by the creator of SQLite.

> There is nothing in the article about syncing.

Correct, that's something I'll have to do myself. I don't see any issues there though, I'd just need to verify the user is authorised to make those changes, which in my specific use case seems pretty trivial.

Querying already cached data without reimplementing the query logic in javascript. How… I mean this is so obvious to have a RDBMS local to any non-basic app, why are you even asking? No offense, I just don’t understand how to create webapps and be not bothered by a lack of fundamental things. Some e-stores and sites literally reach server on each tick on a sidebar, when it could be a fragmentarily replicated local database with a background periodic or live sync. Imagine adjusting a filter and not waiting for reload and rescroll in a “web 2.0” app, when an entire dataset is comparable to a bundle.min.js and a roundtrip to it is 1000x+ slower than a query itself. E.g. a page where I buy t-shirts is barely a megabyte of json (sans images) for all items, but it spends probably half an hour in awaiting responses when I’m shopping around. An app like that shouldn’t even make requests, apart from downloading new jpegs and one narrow replication stream. Even for open-in-new-tab, because resources are cached and a dataset is in sync.

Why do you need RDBMS for it? localStorage/IndexDB isn't enough?

Check out “SQLite as an application file format”, https://www.sqlite.org/appfileformat.html

And how does it help in case of frontend development? A half of those points don't apply to web dev. Do you really need joins? Do you really need atomic transactions? Do you really need SQL? Most of API's that used by web apps operate with objects or arrays of objects. Why should we add complexity of SQL if we already can store those objects in a plain array?

This logic is flawed. X is not in Y, so we shouldn’t add it, because currently in Y no one is using neither X nor some crippled parody of it.

You haven't answered the questions. I don't see a reason to use RDBMS in a browser, if it's not, for example, an SQL tutorial site. You gave an example of a t-shirt store. A plain array of JS objects is enough to store items in a browser. Why do you need a fully backed RDBMS for it?

Because it’s a better programming model.

> better

Sorry, but it's just you opinion. Do you have proofs of how t-shirt store will benefit of putting RDBMS on a client side (how much space does it take BTW) instead of using simple list of objects?

And you still haven't answered the questions.

It helps because apps are often used to view or edit files. By “files” I mean things like word doc files, photoshop files, Apple Keynote files. Many people like working with files and saving them locally, rather than on a server. With SQLite running in the browser, a file that is an SQLite database can be very easily read into memory, worked with, then saved back to disk. The page I linked explains this in detail. There are some examples of companies taking this approach at https://www.sqlite.org/appfileformat.html. Hope this extra detail is helpful.

Edit: Additionally, I encourage you to experiment with this. If you haven’t already, you may be surprised at how efficient/compact SQLite files are. They’re significantly more compact that JSON or XML documents and can include binary files like images. It’s much simpler to use SQLite as a file format than to create a custom binary file format.

If your app lets users sort, search, group, join or transform non-trivial data then SQL requires far fewer lines of code than procedural JavaScript, especially if you don't know ahead of time all the different ways in which users might want to query the data. It's definitely not useful for every kind of app though.

Think of something like personal finance, accounting, payroll, project management, CRM, zettelkasten style note taking, health and fitness tracking, etc, where users may want flexible analysis and reporting tools.

Much of this is now done on the backend, which makes sense if the data is accessed by many users in a transactional manner. But if it's a single user app or something used by tiny teams then cutting latency down to zero could greatly improve usability while still benefitting from everything a real query engine has to offer.

I agree that it could work for a single user web app that works locally, but it's a pretty rare case.

It has indeed been a pretty rare case for web apps, but it's a not completely insignificant niche for desktop and mobile apps. Some of those (e.g many Electron apps) could become pure web apps if more of their enabling technologies were available as WASM modules. I think this SQLite initiative as well as the underlying file system access API is part of that trend.