I wrote postgresql-replicant [0] in order to do this. It’s not a novel idea. It lets you read the logical replication stream.

On top of this library you can do all sorts of things: replicate data to Kafka or SQS, write a web socket server to publish changes to clients, etc.

[0] https://github.com/agentultra/postgresql-replicant

> Next you need push and pub/sub semantics in PostgreSQL

It's fairly basic (then again, does it need to be more?), but NOTIFY exists: https://www.postgresql.org/docs/current/sql-notify.html

If you considered PostgreSQL as a cloud platform it has some useful properties. A well understood API that is consistent across different products. Built in authorisation and authentication. Reliable data storage without having to think to much about the underlying hardware. Triggers that allow activity in one product to initiate a process in another. Support for running sandboxed code in several languages without needing a "server". And you even have some benefits. Your entire infrastructure can be dumped to a file. The infrastructure can be altered in a transaction that can be rolled back. And external services can be integrated as first class citizens (more or less).

That's interesting - have a look at https://colossal.gitbook.io/microprocess/a-totally-new-conce...

All you need to do is map JWT to a Phantom DB User in PostgreSQL, then you can query SQL directly from the browser.

"Reversible SQL" is such a great idea. PostgreSQL does have pub/sub semantics via LISTEN and NOTIFY. At some level, we should think about removing the centralized database completely, and let all of user's devices synchronize with each other somehow. Perhaps SQLite database files synchronized with syncthing? That would really be fantastic!

CouchDB has a javascript client called PouchDB that can do multi-"master" replication and streaming. I wouldn't use it as a primary database, but the syncing story is pretty much top notch.

Postgres has logical replication[0], which the Supabase client uses for real-time data subscriptions.

[0]: https://www.postgresql.org/docs/10/logical-replication.html

Yep, I actually pulled out the Supabase realtime code into an individual library so that you can listen to Postgres changes right in Elixir:

https://github.com/cpursley/walex