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.
So does this mean you should stay away from Electric? No. Electric is clearly great if you have lots of concurrent users actively changing the data. But this power (understandably) comes at a cost that didn't make sense for my app.
Taking a Step Back to Figure Out What My Application Really Needs
So I ended up taking a step back and realized three things:
- SQLite in the browser using WASM has also made a ton of progress, and SQLite feels a lot more relevant for this type of challenge.
- Electric is fantastic for fine-grained and complex multiplayer systems... but I'm building a single player notes app so a much simpler solution was possible. A solution so simple, I could actually build it myself.
- Reactivity can be implemented with SQLite and some modern Javascript APIs.
My Current Approach: The Sync Engine
Selecting a sync engine is a lot about knowing the constraints of your application. How many people will concurrently edit the same resources? How write-heavy is it? Can you expect unreliable connections or offline usage?
In my case, the notes app is single-player, so you would not expect concurrent usage, and users will almost always be connected to the internet. Therefore, I could set up a very minimal solution where (to simplify):
- The user's data is fetched initially from the server: We iterate over all the content using the updated_at attribute until we are all caught up. All the records are sent over as JSON and upserted into the database.
- Every couple of seconds, we fetch records with a newer updated_at timestamp and upsert all the returned records.
- Every time a record is updated, a boolean keeps track of the change to ensure it is synced with the server at most a couple of seconds later.
- As an extra safety, I can set up a CRDT like Yjs on text columns to limit the potential for overridden data.
All of this system works with simple JSON requests and frequent polling. In practice, a small amount of code might be necessary to ensure types are correctly inserted in the database (especially in the case of JSONB or arrays, for example), but it only takes a few lines to do it properly.
For the change tracking, I've modified the approach the Electric team recommends when syncing to PGlite: Set up a local-only sync_control table and set an is_syncing boolean to true before any sync transaction. Then use this type of trigger:
UPDATE entries
SET modified = 1
WHERE id = NEW.id
AND (SELECT bypass_triggers FROM sync_control WHERE id = 1) = 0
AND (SELECT is_syncing FROM sync_control WHERE id = 1) = 0
AND OLD.rowid IS NOT NULL;
lang-jsMy Current Approach: Reactivity with SQLite
SQLite in the browser is increasingly common and works fantastically well. I went with wa-sqlite and have found the implementation incredibly stable (actually since setup, I haven't experienced a single problem). But even though SQLite might be super fast and stable... it doesn't have any kind of reactivity system built in; you can't LISTEN to changes.
The good news is that it's easy to work around this constraint. Here is my approach:
- Have a trigger create a log of all the changes made to the data in a separate table. Basically, I'm only storing the table changed and the ID concerned.
- Use the Broadcast Channel API to notify the reactivity script that there have been changes. It then fetches them and uses a broadcast channel again to share which rows have been created/updated/destroyed.
- Svelte stores receive the notification if it applies to them. They then redo the SQL query to fetch the latest version of the data.
In practice, I've found this approach perfect. It's simple to understand, stable, and super fast. The entire app feels perfectly reactive as if everything was in memory.
Conclusion
This setup has been working very well for my needs. There's a lot of potential for better tooling around offline-first applications and SQLite in the browser. I look forward to trying out those solutions!
💬 Comments