Over the last couple of months, I've been trying to build the dream: A local-first, end-to-end encrypted and reactive app, with all of the user's data in a local SQL database but continually synced to a remote server. This article summarizes my learning and how I ended up building a minimal sync engine for SQLite with full reactivity.
First Try: PGlite and Electric
My first try was with Electric and a WASM-based version of PostgreSQL called PGlite that can run directly in the browser. I even wrote an article about this setup . In a way, this approach is ideal as you have the same database engine locally as on the server. 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. Additionally, thanks to PostgreSQL, it's possible to use LISTEN which enables total reactivity throughout the application, which is really cool to see.
But as I used it, I experienced two major issues. The first was with Electric itself: It is still a young tool, and even though it works great, a few features are still missing. In particular, I found that as the database grew, since compaction isn't yet enabled (as far as I can tell), it could take more than a minute to start the application which isn't great for the user experience.
At the same time, I started running into bugs with PGlite that came down to how PGlite itself works; typically PostgreSQL is a server process, so how could you have it "run" in the browser? The trick is to use PG's single-user mode. Typically, you would only do that for maintenance tasks, but it turns out that it can go pretty far. Sadly, I found it a bit too unstable as the size of the database and the number of concurrent LISTEN requests increased. I started getting memory leaks and huge slowdowns with live queries and ended up losing faith that this approach would work in the long term.
... continue reading