I think there is a large overlap with projects that market/focus on offline-first experiences.
AFAIK this problem can be solved by:
1) Considering a client-side copy of the database that gets synced with the remote DB. This is an approach [PowerSync](https://www.powersync.com/) and [ElectricSql](https://electric-sql.com/) and [rxdb](https://rxdb.info/) take!
For the first two, they effectively use postgres CDC and then have a sync process that is provided for you. For rxdb I think you have to roll your own syncing but there is an interface for you to implement.
One difference between ElectricSQL and PowerSync (copied this from another [HN comment](https://news.ycombinator.com/item?id=37600917)) -> `ElectricSQL uses “shapes” (currently a WIP feature) defined on the client to partition data, while PowerSync uses server-side “sync rules”. The sync rules approach allows data to be transformed and filtered using basic SQL queries, which [PowerSync] expect to scale well (benchmarks pending). PowerSync guarantees consistency over all synced data, while it appears like ElectricSQL only guarantees consistency within each shape. PowerSync also automatically adds and removes sync buckets when changes are made to either the sync rules or data, while ElectricSQL likely requires you to subscribe and remove shapes yourself.`
^ Please note that comment faced rebuttal from ElectricSQL.
On the same thread (6 months ago) , someone from ElectricSQL said this `Electric is designed to support partial sync, and so you don’t have to sync your whole dataset. (Note that this is feature is under development and not yet public)`
2) Another exciting entry is [Convex](https://www.convex.dev/) which supports reactive queries but it is not based on a well known existing DB like postgres, it's fully custom and new - but looks easy to get started with! They recently open-sourced their backend so you can give that a try.
4) [Triplit](https://www.triplit.dev/) Another option! Triplit is an open-source database that syncs data between server and browser in real-time but again, this not based on a well-known db like postgres, instead it uses another new solution. It is open-source, but I haven't tried it yet. Looks quick to set uo.
4) Yes, you can use supabase realtime/ postgres events to listen to changes in your db and refetch all your data as you said - not ideal but yep.
Other projects/technologies to look into include Debezium for CDC and also potentially using GraphQL subscriptions to keep your frontend up to date
See also: [Prisma Pulse](https://www.prisma.io/data-platform/pulse) which I looks something similar to Supabase Realtime. Supabase are in the midst of their announcement week. I have quietly hoping that they will launch an offline-first / realtime query feature soon. [This github issue](https://github.com/orgs/supabase/discussions/357) seems to tease it.
Best of luck with this! It's a super interesting problem and I'd like to know what you end up doing! :)
Just to expand on this - ElectricSQL and PowerSync (and some others like Evolu) use a simple but quite effective approach to get reactive queries:
1. Listen for updates to the table(s) used by the query, using SQLite's update hook or similar approach.
2. When a table has changed, rerun the query.
There is no incremental updating of row results, no smart detection of whether the updated rows have an effect on the query. But it works with practically any query, including joins, aggregates, and even views and CTEs in the case of PowerSync and Evolu. And in most cases, SQLite is fast enough that performance is not an issue.
Yup, our reactivity is quite simple at the moment, as described, and works quite well because SQLite is so fast. There are other projects doing more sophisticated reactivity algorithms, like Riffle/LiveStore, which has a reactive DAG optimised to avoid re-rendering and https://github.com/vlcn-io/materialite, which is like differential dataflow for live queries.
They're quite bleeding edge at the moment, but you can expect more efficient primitives for incremental view maintenance / efficient subscriptions to land in the embedded database layer quite soon.
Thanks!
Electric looks great! though I'm wondering how it works in scale.
I'm using Postgres and have some heavy queries, would be great to cache\materizlized the results instead of re-run them every time for every user, or for returning users without any change in the data.
Thank you for the detailed answer, you're awesome.
Electric-SQL, Convex, and Triplit are definitely relevant, and I'll dive deep into understanding their pros and cons.
In the meantime, I found another relevant one: https://squid.cloud/, which has a slightly different approach, more like full BAAS.
On one end, it can save me some time; on another, it's another stack my R&D needs to be familiar with.
