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.
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.