I like the implications of this to a "local first" architecture

postgres has some capability to do that, but does need a server.

Yeah you can subscribe to overall changes to the data on a row by row basis but can't subscribe to an actual query. Many apps and libraries imitate reactive queries by just refetching all queries from Postgres when any data changes or just repeatedly polling the query every 10 seconds or so but this puts a lot of strain on the database. You can just subscribe to the replication stream but then you're left trying to reconstruct your queries in your application code which is extremely error prone and painful

Could you explain in more detail the use case for subscribing to the actual queries (instead of the change events)?

> It doesn't easily map to the data-structures you want in your client code

I disagree. Normalizing data is critical for FE reactive applications, keeping data up-to-date basically requires it; all CRUD operations are much easier to handle.

Realm Sync, Mongo + Kotlin MP will cover basically all platforms (server, web, mobile, desktop)... at a cost. Actually interested by alternatives. Will this be part of your article?

SQLite does actually provide the mechanisms required to listen for changes via update hooks. It's unfortunate that many SQLite bindings don't expose that. I'm using it in a very simple way - automatically rerun the query if data in the underlying table changes. It's perhaps not as efficient as incrementally updating the results, but with how fast queries in SQLite usually are, I find that doesn't really matter.

Actually, Postgres provides a great way to subscribe to real time changes via the WAL. I even maintain an open source library for this:

https://github.com/cpursley/walex