I work at Electric and started the PGlite and now Tanstack DB projects. The issues mentioned with PGlite are one of the major motivating factors behind Tanstack DB. We are taking those learnings and building, what we believe, is the missing client side datastore that is "sync native" and completely backend agnostic. Also being JS, rather than WASM, solves many of the slower than ideal query semantics, and has enabled us to build an incremental query engine for it.
It's also important to note that Electric doesn't require PGlite on the client, far from it - it's essentially a "protocol first" sync engine, you can use it to write into and maintain any client side store.
This solution by the OP, diffing based of modified data is ideal for a huge number of apps, and something that we intend to built into Tanstack DB so you can easily sync with no additional infrastructure.
SQLite (or PGlite) in the browser is awesome, and has the advantage over Tanstack DB at the moment of having persistence (it's on our roadmap), but they are also somewhat chunky downloads. For many local-first apps that's not a problem though.
FWIW, I think you might be better off with immutable rows and lamport clocks.
Everything is a full new row because it is “a message” including read receipts. Some messages like read receipts just don’t render in the chat.
Edits can work the same way by rendering over a previous message, even though the local and remote DB have multiple rows for the original and edited messages.
A month of development can easily save a day of research.
I was very surprised (or perhaps disappointed is a better word) when I didn’t see Lamport, paxos or raft mentioned at all. At least crdts made an appearance, although almost in the post scriptum.
An interesting tool that matches the requirements mentioned in the article is Evolu[0]
It's a sync engine with e2e encryption based on SQLite.
The local-first landscape is quite wide now, and there is probably a solution ready for all kind of needs[1]
Building a sync engine can be a nice learning experience, but for production software it's better to pick something that has already faced and resolved all the weird edge cases you get when building a sync engine and persistent storage on the browser.
We've spend the last couple of years building a local-first end-to-end encrypted multiplayer notes app (Thymer). We also explored running sqlite in the browser but ultimately concluded that we didn't really sqlite or a real database at all in the browser.
What does a document system need? A few simple indexes that track links and backlinks, mentions, and hashtags. You’re broadcasting change notifications anyway (for reactive DOM updates), so updating the indexes for those manually isn’t much extra work. You apply updates eagerly and then notify other users so they can apply the same updates, with some rules to guarantee that all users end up with the same state regardless of the order in which they receive the updates. But a relational database doesn’t help with any of this. Document systems tend to be versioned, so every user action turns into another entry in a transaction log. Even queries like “last Monday’s version of my document” don’t map naturally to SQL. You can query for transactions in a given time period, but unless you have snapshots, you’re still forced to re-apply transactions all the way from t=0 if you want to re-create the document state at a given date.
> And because Electric syncs every change granularly, you are certain that the state of your local database is exactly the same as the server's.
I don't see how this certainty follows from "granularity" (whatever that means in this context). I believe to have such a certainty one would need the synchronization to happen within a single transaction that spans both client and server databases.
Correct - granular syncing alone doesn't guarantee consistency; you'd need either a distributed transaction protocol or a conflict resolution strategy with eventual consistency semantics.
Thanks for sharing this talk. I didn’t know about MobX, but I did use your fork of wa-sqlite (https://github.com/team-reflect/wa-sqlite/) since I also needed the sqlite-vec extension :)
By the way, Reflect is an awesome app; I'm actually a paid subscriber.
Readit News
I work at Electric and started the PGlite and now Tanstack DB projects. The issues mentioned with PGlite are one of the major motivating factors behind Tanstack DB. We are taking those learnings and building, what we believe, is the missing client side datastore that is "sync native" and completely backend agnostic. Also being JS, rather than WASM, solves many of the slower than ideal query semantics, and has enabled us to build an incremental query engine for it.
It's also important to note that Electric doesn't require PGlite on the client, far from it - it's essentially a "protocol first" sync engine, you can use it to write into and maintain any client side store.
This solution by the OP, diffing based of modified data is ideal for a huge number of apps, and something that we intend to built into Tanstack DB so you can easily sync with no additional infrastructure.
SQLite (or PGlite) in the browser is awesome, and has the advantage over Tanstack DB at the moment of having persistence (it's on our roadmap), but they are also somewhat chunky downloads. For many local-first apps that's not a problem though.
It is opinionated and not for every use case, also very experimental, but you might find some of the ideas interesting.
Everything is a full new row because it is “a message” including read receipts. Some messages like read receipts just don’t render in the chat.
Edits can work the same way by rendering over a previous message, even though the local and remote DB have multiple rows for the original and edited messages.
I was very surprised (or perhaps disappointed is a better word) when I didn’t see Lamport, paxos or raft mentioned at all. At least crdts made an appearance, although almost in the post scriptum.
An interesting tool that matches the requirements mentioned in the article is Evolu[0]
It's a sync engine with e2e encryption based on SQLite.
The local-first landscape is quite wide now, and there is probably a solution ready for all kind of needs[1]
Building a sync engine can be a nice learning experience, but for production software it's better to pick something that has already faced and resolved all the weird edge cases you get when building a sync engine and persistent storage on the browser.
[0] https://www.evolu.dev/
[1] https://www.localfirst.fm/landscape
What does a document system need? A few simple indexes that track links and backlinks, mentions, and hashtags. You’re broadcasting change notifications anyway (for reactive DOM updates), so updating the indexes for those manually isn’t much extra work. You apply updates eagerly and then notify other users so they can apply the same updates, with some rules to guarantee that all users end up with the same state regardless of the order in which they receive the updates. But a relational database doesn’t help with any of this. Document systems tend to be versioned, so every user action turns into another entry in a transaction log. Even queries like “last Monday’s version of my document” don’t map naturally to SQL. You can query for transactions in a given time period, but unless you have snapshots, you’re still forced to re-apply transactions all the way from t=0 if you want to re-create the document state at a given date.
I don't see how this certainty follows from "granularity" (whatever that means in this context). I believe to have such a certainty one would need the synchronization to happen within a single transaction that spans both client and server databases.
Using the wa-sqlite library, and our own Mobx-based model layer similar to what Linear does.
I gave a short talk about it a few months ago https://www.youtube.com/watch?v=b0GKWzeyZko