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.
I'd love to hear what you think of the idea. I really like what electric is building. I'm actually hacking on getting pglite to run on react-native right now =)
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.
I must be a bit dense on this little Sunday morning but I don't quite get the problem solved.
Is it an issue with reactivity between browser tabs? Hence the use of the Broadcast Channel API?
The reactivity being implemented in svelte, what is different from any normal app? could have been react with mobx, zustand, recoil, etc but that's not too relevant. This is just how frontend frameworks are nowadays.
Especially since it is single-user (player? sic)?
Syncing between the backend server (remote state) and the local sqlite instance (local state) shouldn't really require much work? A simple websocket connection could do the trick to push from the remote server to the local device and then push the changes to sqlite again.
But that's not too surprising to me.
There’s a whole world of syncing databases when they’re offline and then reconnect. Local first. If your websocket connection is offline you have to store the incremental changes to your data and send them later on reconnect. This gets complex fast.
A lot of these tools (and what the author wrote) offer toolkits to do this well, not having to implement and track all these changes in state manually.
In this case the author is running SQLite in the browser, and that syncs to SQLite on the server.
Among other things, but even with a “latest change wins” method you still have to make sure all transactions arrive, data doesn’t stay out of synch forever, and other challenges.
ElectricSql, instantdb, rxdb, jazz.tools, are some of the things I’ve been looking into to make these tasks easier.
Nice write-up! Would love to read more about practical experience on sync engines. I am also currently building one for a niche use case: a CRDT based smart home system w/o central component: syncing config and state across several embedded/MCU + mobile via CAN bus and BLE, no Internet. It features a simple ORM and persistence and for dev/debug it runs on macos/Linux. Written in C++ and Dart. Due to memory and other platform constraints I chose to build most of it from scratch. Thought about open sourcing or writing about it, but wondered whether anybody cares given the relative obscure tech stack.
I personally don't have use for the insight to this "obscure" tech stack but typically the more obscure the tech stack the more people can benefit from you sharing your perspective.
If someone shares their additional take on rewriting in rust (I say this with love as the type of person to do that) it doesn't add as much value because many of those experiences are shared regularly. If you share your unique tech stack, the people in the woodwork who might be struggling to do something along those lines but can't find the resources will benefit way more.
So you should!
Also can you elaborate on the CAN bus part of your smart home? Is your car wired into your home network?
Amazing write-up! I’m thrilled to see your exploration of a reactive SQLite sync engine; it resonates deeply with my own journey toward Local‑First, Offline‑Resilient tools aimed at relieving pressure, not just impressing. Keeping all user data local in SQLite while enabling seamless, encrypted syncing and reactivity. A nice segue in pragmatic autonomy.
It mirrors the moment I embraced tools like Obsidian; designing for real-world constraints; valuing simplicity, privacy, and functionality; especially in low‑connectivity environments.
The whole line of Tanstack products is very well made yet poorly designed at the same time. I don't know if it's inherited from React by trying (and succeeding) to be a seamless React experience or if it's part of their own philosophy, but most of the tools make it very easy to mix state everywhere in your app which is just bad.
Really people should just move to Gleam and Lustre or Elm, even if it means I'll have much fewer clients paying good money to untangle state issues...
Ok is this about a reactive app with with a local database automatically synced to a remote db? All fully encrypted (at rest and in transit)?
I thought this is what everyone does nowadays!
We built an app like this in 2019 - yes - it was a bit of a challenge with the encryption but the „syncing data“-part is what every litte multiplayer-game has to deal with like forever now. Seems i‘m out of touch with the current state of affairs.
Nice article though!
The value is in the author’s experience with other tools that caused problems as his database grew, and in learning from his reasoning about the appropriate solution for his particular problem.
This is a really great write up!
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.
I built my own offline capable, multiplayer capable sync engine with pglite and electric https://github.com/evelant/synchrotron
It is opinionated and not for every use case, also very experimental, but you might find some of the ideas interesting.
Oh cool! I'll absolutely take a look.
I'd love to hear what you think of the idea. I really like what electric is building. I'm actually hacking on getting pglite to run on react-native right now =)
Do drop into the discord and let me know who you are there. Would love to hear your take on react-native support.
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.
Nice article!
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
Oh wow, thanks for linking evolu! To bad it's typescript only... I'm looking for an e2ee sqlite sync for kotlin. Are you aware of any solutions?
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.
Great write-up! You should check out SQLite-Sync (https://github.com/sqliteai/sqlite-sync) - it might be exactly what you're looking for. There's also a WASM build (https://www.npmjs.com/package/@sqliteai/sqlite-sync-wasm)
I love the timing of how, over the past week or so I've been digging into the ecosystem and options around this, and now here's another article!
> 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.
I would say there is no certainty with eventual consistency, only hope.
> Selecting a sync engine is a lot about knowing the constraints of your application.
Not so sure about this. These seem more like fundamentals than sliding scales.
> How many people will concurrently edit the same resources?
More than 1.
> How write-heavy is it?
Write-heavy enough that you'll encounter an unexpected write between two reads.
> Can you expect unreliable connections or offline usage?
Yes.
CAP with Electron and SQLite is no different from CAP with Tauri and MySQL.
It feels like everyone is solving the sync problem from a slightly different angle. Has anyone here used a few of these in production?
We use very similar solution in https://reflect.app
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
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.
I must be a bit dense on this little Sunday morning but I don't quite get the problem solved.
Is it an issue with reactivity between browser tabs? Hence the use of the Broadcast Channel API?
The reactivity being implemented in svelte, what is different from any normal app? could have been react with mobx, zustand, recoil, etc but that's not too relevant. This is just how frontend frameworks are nowadays.
Especially since it is single-user (player? sic)?
Syncing between the backend server (remote state) and the local sqlite instance (local state) shouldn't really require much work? A simple websocket connection could do the trick to push from the remote server to the local device and then push the changes to sqlite again. But that's not too surprising to me.
I must not have understood something...
There’s a whole world of syncing databases when they’re offline and then reconnect. Local first. If your websocket connection is offline you have to store the incremental changes to your data and send them later on reconnect. This gets complex fast.
A lot of these tools (and what the author wrote) offer toolkits to do this well, not having to implement and track all these changes in state manually.
In this case the author is running SQLite in the browser, and that syncs to SQLite on the server.
oh ok. What does it require? CRDTs?
Among other things, but even with a “latest change wins” method you still have to make sure all transactions arrive, data doesn’t stay out of synch forever, and other challenges.
ElectricSql, instantdb, rxdb, jazz.tools, are some of the things I’ve been looking into to make these tasks easier.
Nice write-up! Would love to read more about practical experience on sync engines. I am also currently building one for a niche use case: a CRDT based smart home system w/o central component: syncing config and state across several embedded/MCU + mobile via CAN bus and BLE, no Internet. It features a simple ORM and persistence and for dev/debug it runs on macos/Linux. Written in C++ and Dart. Due to memory and other platform constraints I chose to build most of it from scratch. Thought about open sourcing or writing about it, but wondered whether anybody cares given the relative obscure tech stack.
I personally don't have use for the insight to this "obscure" tech stack but typically the more obscure the tech stack the more people can benefit from you sharing your perspective.
If someone shares their additional take on rewriting in rust (I say this with love as the type of person to do that) it doesn't add as much value because many of those experiences are shared regularly. If you share your unique tech stack, the people in the woodwork who might be struggling to do something along those lines but can't find the resources will benefit way more.
So you should!
Also can you elaborate on the CAN bus part of your smart home? Is your car wired into your home network?
Amazing write-up! I’m thrilled to see your exploration of a reactive SQLite sync engine; it resonates deeply with my own journey toward Local‑First, Offline‑Resilient tools aimed at relieving pressure, not just impressing. Keeping all user data local in SQLite while enabling seamless, encrypted syncing and reactivity. A nice segue in pragmatic autonomy.
It mirrors the moment I embraced tools like Obsidian; designing for real-world constraints; valuing simplicity, privacy, and functionality; especially in low‑connectivity environments.
PS: I was hooked on ...SQLite
Bravo!
performance degradation observed using the first approach at high concurrency recently discussed here: https://news.ycombinator.com/item?id=44490510
very cool! have you tried Tanstack DB and ElectricSQL?
https://x.com/marcelohbairros/status/1956892684859158892
The whole line of Tanstack products is very well made yet poorly designed at the same time. I don't know if it's inherited from React by trying (and succeeding) to be a seamless React experience or if it's part of their own philosophy, but most of the tools make it very easy to mix state everywhere in your app which is just bad.
Really people should just move to Gleam and Lustre or Elm, even if it means I'll have much fewer clients paying good money to untangle state issues...
Not sure about Tanstack DB, but from the second paragraph in the article:
> First Try: PGlite and Electric
Pretty cool! SQLite fits so well for local-first apps that feels like cheating.
Out of curiosity, apart from the lack of LISTEN support what more did you miss in SQLite itself and its ecosystem?
Thanks! In my case, the only thing "missing" was vector search but there's a great extension for that: https://github.com/asg017/sqlite-vec
Out of curiosity, would CouchDB not be a contender for this type of application?
very cool stuff!
[dead]
Ok is this about a reactive app with with a local database automatically synced to a remote db? All fully encrypted (at rest and in transit)? I thought this is what everyone does nowadays! We built an app like this in 2019 - yes - it was a bit of a challenge with the encryption but the „syncing data“-part is what every litte multiplayer-game has to deal with like forever now. Seems i‘m out of touch with the current state of affairs. Nice article though!
The value is in the author’s experience with other tools that caused problems as his database grew, and in learning from his reasoning about the appropriate solution for his particular problem.