Indeed it's a very hot space! So exciting to see all the different approaches.
ElectricSQL and PowerSync are both tackling the very hard problem of partial replication. The idea is to build a general solution which allows a traditional centralized db to bidirectionally sync only what's needed on the client side - while still supporting optimistic mutations (and all the consistency/conflict stuff that goes along with that).
The downside is implementation complexity. Both require the ability to keep track of precisely the set of data on each client in order to push out changes to only that subset of the overall database. In addition, specifying which subsets of the database state to pull down requires a new DSL and is a new thing to learn (and optimize). That said, I'm stoked they are taking on this extremely hard problem so when SQLSync is ready for partial replication someone will have already figured out the best practices.
SQLSync, on the other hand, only supports full db sync. So every client will see a consistent view of the entire database. You might immediately wonder if this is a good idea - and for some apps, it's not. But consider a personal finance app. The main goal is cross device sync, cloud backup, offline capable, etc. In this case having the entire db stored on every device is probably what you want. Another example is a document oriented data model, such as Airtable. Each Airtable could be a distinct database, thus leaving it up to the client to manage which tables they care about.
(added in edit:) By focusing on full db sync, the sync engine is much simpler than solutions that support partial replication. One benefit of this is that the backend is very lightweight. Currently the demo (https://sqlsync-todo.pages.dev) runs entirely within Cloudflare Durable Objects using very little storage and CPU time.
SQLSync has a ton of work to do to make these use cases possible (still very much a prototype), but my initial tests have been extremely promising. Hope this helps!
(edit: clarified language regarding centralized dbs and full db sync. Also added paragraph regarding full db sync)
Phillip from PowerSync here, always good to see more people working on problems in this space.
A few things to clarify:
>one multi-tenant centralized db to bidirectionally sync
PowerSync supports syncing from multiple databases.
>The downside is complexity.
I'd say this is true if you're building a partial replication system yourself. PowerSync gives you a ready-built system that's been proven at scale and therefore lets you avoid most of that complexity.
>SQLSync, on the other hand, is full db sync.
It's just as easy to sync the full db with PowerSync as do partial sync.
>But consider a personal finance app. The main goal is cross device sync, cloud backup, offline capable, etc. In this case having the entire db stored on every device is probably what you want.
A bit confused by this. If I'm a developer of a PFM, I don't want anything but a single user's financial data synced to their device. This sounds like partial replication to me.
Precisely. In the SQLSync model - every user would have a private database just for their data. For example, this is how the todo list demo works: https://sqlsync-todo.pages.dev
(Note: currently SQLSync's server tier doesn't support auth, just random 128bit ids. Auth will come as it matures - but don't use this for anything super secure at the moment).
Aside from Electric being open source and PowerSync a proprietary service, the primary difference is in the programming model on the write path.
Electric provides finality of local writes. So once a non-malicious write is accepted locally, it is final and won’t be rejected by the server. This simplifies the programming model and means you don’t have to code for rollbacks.
PowerSync is a server authoritative system. Local writes are tentative and can be rejected at the server. You run an API on the write path and write logic to handle conflicts and rollbacks.
The different approaches come with different trade offs, both operationally and in terms of the programming model.
I looked at these two systems recently and noted another crucial difference for anyone using them for a use case that included access control. ElectricSQL doesn't seem to (yet) support table joins in a way that would support standard web app access control design patterns. If you're replicating a table to a device, you're replicating the entire table, not a user-limited selection.
Supporting joins is in development, but I'm not yet clear on whether the current dev branch on it goes far enough to support access control use cases.
There's a hack in place that's supposed to help - you can define an electric_user_id on the table - but that isn't actually usable in the majority of use cases, because most ACL cases include records where multiple users can access it. I did explore using views, but electricsql doesn't currently support postgres views.
(if I'm wrong or missed something in electricsql, I'd love to be corrected, as it looks like an exciting project otherwise)
These are both in development and due soon. From your comment, I think you’ve seen the shapes branch with where clauses and include trees already working, for example.
In the meantime, the shapes API over syncs the full table. This is temporary and obviously suboptimal but it means you can develop today using the shape APIs and still filter data you display using local queries. Then when the proper functionality lands, the sync will become more fine grained and optimal without your app code needing to change.
Hope that makes sense. We’re very much not a full table sync system. Our role is to provide the best possible model for controlling dynamic partial replication (and to maintain integrity across replication boundaries).
I cannot imagine advocating for using shapes as they currently exist for an access control use case. Shapes may limit what the user can see in the app, but I've now put all of my other users data on this user's device! No way can I pass a security audit with that.
Thanks for chiming in! ElectricSQL is great - we see it come up a lot in discussions since on the surface it solves the same problems (syncing between Postgres and SQLite), despite the details being very different. I love seeing all the innovation in the offline-first space!
