I'm currently building an application that is offline-first (meaning it is fully usable even without an internet connection) but eventually syncs to a server and is E2E encrypted. If you need to build something similar, this article should help as I'll share the approach I'm currently using.
Before I get into the details, I wanted to highlight the usefulness of having an offline-first application, even if you expect your users to be almost always connected. Due to being able to work offline, the application is expected to perform its database requests locally, which vastly speeds things up. Navigation feels instant, which provides an awesome experience for your users. I'm currently building a productivity app, and for this category, I see these speed benefits as a must-have.
The tools we'll be using: PGlite and Electric
PGlite is a fully functional Postgres database embedded locally in the browser using WASM. Electric is a sync engine that synchronizes your server's Postgres database with the PGlite DB of your users.
I chose these tools because:
- PGlite and Electric are both open source. Electric is still a business, so I expect them to have some kind of offering at some point, but I'm not too dependent on them if their project goes in a direction I do not want to follow.
- PGlite in the browser gives full access to the power of relational SQL queries. There are other solutions out there that deal with syncing, but I worry the querying won't be as powerful. And it's one less thing to learn.
- Postgres is a mainstream, generic building block. That brings in tons of support and resources that more niche and proprietary tools do not have.
A high-level view of how the syncing will work
Before we jump into the code samples, it's important to figure out what we're trying to achieve. We have two PostgreSQL databases: our primary one on the server and another one in the browser that replicates a subset of the data relating to the user.
When we start the application, if the client connects with an empty database, we need to perform a full sync. Then we continually track incoming changes. If the client already has a database, it should only catch up with the latest changes.
Finally, when the client performs writes, those changes should be pushed to the server. Note, however, that this is where things might get very complicated or might not be doable in an offline-first app. I'll share more on how to handle writes later in this article.
This theoretical setup would give us a fully working and updated PostgreSQL database in the browser that also syncs with the server.
Implementing the sync
Syncing a table with Electric and PGlite is initially quite simple. It only takes a few lines to get started:
const shape = await pg.electric.syncShapeToTable({ shape: { url: 'http://localhost:3000/v1/shape', params: { table: 'issues', }, }, table: 'issues', primaryKey: ['id'], }) lang-js
Because the syncing plugin is still in alpha, this approach has two limitations:
- The syncing doesn't support conflict resolution, meaning that updates to an existing row will fail.
- Electric has no way of dealing with writes.
Keeping track of local changes
To solve these two problems, we'll set up our local Postgres table slightly differently than the version on the server. In particular, we'll add columns to keep track of the rows that have been changed, if they have been sent to the server, and which columns have been modified. This way, we'll be able to continuously send the changes to the server. Once the server sends back the data, it will be our confirmation that it has been saved successfully.
For the exact implementation, Electric provides some example applications that implement this pattern, in particular, Linearlite, which is a clone of Linear (this example app is a great resource!).
We do not want to have to manually update those extra columns every time we perform a write, so instead, we'll use triggers to keep track of the changes we make to the data. This implementation has the great advantage of only needing to be set up once. The Linearlite application offers a really good start for this setup.
Syncing local changes
A cool feature of PGlite is live queries, enabling you to subscribe to a query and get updates as they happen. This is useful for most apps, and we'll use it to sync local changes to the server. We need something like this:
pgLite.live.query( ` SELECT count(id) as issues_count FROM issues WHERE synced = false `, [], (res) => { syncToServer() } ) lang-js
syncToServer only needs to fetch all the changes that haven't been synced and send them to an API endpoint that will apply everything to the server database.
Because we already set up the subscription earlier, we can expect Electric to send us back the changes we just synced. That's when we will update the columns we have created to track the changes, as we would now know for a fact that the writes have been saved.
Conflict resolution in Electric
As mentioned earlier, for now, the syncing plugin doesn't deal with existing rows. I imagine this will one day be solved, but for now, we can fork the sync plugin and add the feature ourselves. It's a single file so nothing too complicated to update. Here is the version of the plugin I'm currently using.
Handling failed writes
What happens when a user tries to do something the server later refuses? In an offline-first application, we might only know about this failure much later. So what is the solution? Ideally, your app would be comprised mainly of idempotent actions that can't fail. Of course, this isn't always possible and very much depends on your use case. If writes can fail, it's up to you to decide if you want to take the chance to allow them offline or deny them to limit user frustration when something happens.
E2E encryption
From our current state, it's reasonably easy to add E2E encryption. Indeed, we have one file that sends updates to the server and one file that receives and applies the updates. We just need to modify those two files to encrypt data before it's sent out and decrypt it before we store it in the local database. This approach is great as you never have to worry about dealing with encryption locally... it just happens.
As for the implementation, I recommend using the kiss-crypto package maintained by the Reflect team. It's easy to use and has even been audited.
Conclusion
As the implementation is very dependent on your needs and the tools you'll be using, I haven't shared too much code. Hopefully, this can still be helpful. Overall, I found this setup relatively easy to get working and look forward to seeing the improvements that Electric continues to bring.
š¬ Comments