Listen to your to PostgreSQL database in realtime via WebSockets

[JakaJancar]

This works until you have an A->B->C hierarchy of entities and want to monitor the subtree rooted at a specific A. Then the WAL for table C, even with REPLICA IDENTITY FULL, will not tell you whether a change is relevant. At that point you need to do a JOIN anyways, so might as well just use a trigger and NOTIFY instead.

Two of the other reasons for this over triggers are also misleading:

- Setting up triggers can be automated easily.

- True, you only use 1 connection to the database, but you now operate this app. You could also run pgbouncer.

[skyde]

if C is a "child entity" of C then it's primary key should have the primary key of B as a prefix.

This is how I design all my table schema. and it make database partitioning easier too.

[karambahh]

For some reason I understood the title as an audio rendering of changes on your pg db

The actual purpose is actually way cooler, looks like a great tool

[yokto]

This reminds me of a project I did a few years ago:

"Stockify is a live music service that follows the mood of the stock markets. It plays happy music only when the stock is going up and sad music when it’s going down." https://vimeo.com/310372406

(it's of course a parody, but I made a functional prototype)

[jmull]

That's what I thought too.

I guess someone needs to do it now, hm...

(But: I think live db-activity rendered to music as a kind of monitoring mechanism is potentially way cooler than just getting db updates over a web socket.)

[rzzzt]

You can listen to GitHub commits: https://github.audio/

[m_mueller]

Same here. App idea geared at DevOps: ambient sound scape generator based on monitoring events, e.g. from Datadog.

[mark-wagner]

Similar: Solaris's snoop (tcpdump equivalent) with the -a option:

Listen to packets on /dev/audio (warning: can be noisy).

https://docs.oracle.com/cd/E23823_01/html/816-5166/snoop-1m....

[domoritz]

Same here and I am slightly disappointed it's not what I thought it was.

[brap]

I really want Supabase to take off. For me, the #1 reason not to go with Firebase was lack of relational DB support, and #2 vendor lock-in.

[pier25]

For #1 you could check out Fauna. Much more powerful than Firebase regarding querying and data modeling.

With Fauna you still get vendor lock-in though.

[kabes]

For 2 you could look at meteor, which does a similar thing with mongo. It's not relational, but with supabasr you also don't really get the advantages of a relational db, since the update events are about single tables.

[endisneigh]

I'm really curious to see how this ends up doing vs. Hasura.

Postgrest (which is basically what Supabase is, obviously with other value-adds) or Hasura which basically exposes a GraphQL server that interfaces with Postgres.

Personally I prefer GraphQL as there's more tooling around that compared to Postgrest but it's interesting to see. In this case if supabase was GraphQL you could just use a subscription.

I'd be curious to know why supabase didn't go with GraphQL.

[michael_j_ward]

I believe the graphql equivalent of postgrest is postgraphile [0].

[0] https://www.graphile.org/postgraphile/

[kiwicopple]

(supabase cofounder)

We're not opposed to GraphQL at all. We use PostgREST because it's very low-level. It's a thin wrapper around Postgres. It uses Row Level Security, works nicely with Stored Procedures, and postgres extensions (like PostGIS).

Philosophically we want to focus more on the database-centric solutions, rather than than middleware-centric solutions. This means a slower time-to-market, but it's beneficial because you have a "source" right at the bottom of your stack, and you can connect whatever tools you want to it (including Hasura!)

[pier25]

> I'd be curious to know why supabase didn't go with GraphQL.

I'm guessing because GraphQL is very limited compared to SQL.

From reading their docs [1] it seems they have a JS API for SQL with support for nested data a la GraphQL. Not sure if its their own or comes from some other library.

[1] https://supabase.io/docs/reference/javascript/select

[iooi]

For Python folks, this library builds a GraphQL schema from your SQLAlchemy models: https://github.com/gzzo/graphql-sqlalchemy

[eloff]

So one big difference is this tails the bin log (as a replication slave), while Hasura polls. Hasura makes that polling efficient by multiplexing the polling queries transparently so they send one query to poll N queries on the same table with the same shape.

But once you're polling a large percentage of your whole data set, the bin log approach has a clear advantage.

[tablatom]

Nhost is a very similar offering, built on Hasura

https://nhost.io/

[thom]

Have you had positive experiences using Hasura in production?

[skyde]

I love it! Only issue is if you can't consume the history of changes. But this is good for caching materialized view in the client

1- subscribe to event and buffer event

2- run SQL query for your materialized view

3- apply all buffered event and new event to incrementally update the materialized view

this way the (slow/expensive) query of the materialized view don't need to be run periodically and your cache always is always fresh without need to set TTL.

If you websocket connection get disconnected, drop the materialized view and repeat step #1.

[raarts]

So basically Apollo GraphQL server could use the same mechanism for enabling GraphQL subscriptions right? Any idea what they currently use?

[speedgoose]

They use websockets and a set of plug-ins to support redis, rabbitmq, or many other things. However, Apollo is planning to remove subscriptions for the next major release.

[endisneigh]

GraphQL severs handle subscriptions websockets as well usually.

[bcoates]

Neat! Is the idea to subscribe to the websocket directly from frontend code or from an application server?

It sounds like it's the latter, which seems like an unusual use case for websockets. (or at least I can't think of anything else that does ws for a server-to-server API)

[cookguyruffles]

As far as getting cheap framing and bidirectional communication over an HTTP port goes, there's nothing to stop you using websocket server->server. For example users, many crypto exchanges publish their feed this way

[throwdbaaway]

For me, this looks like the perfect implementation of a very scalable config service, where the read RPS can get very high, but the config values are rarely changed. The consumers of the config service can be either frontend applications or backend application, thus websocket is a great choice.

[jordan801]

Yeah! Front end apps can skip the server and get data from the db directly. There's OAuth2 setup and you can authorize users to have crud access to tables, or even a granular as rows.

[kitd]

Nice!

Can you go in the other direction, ie push via WS into a table? Obviously you'd need the correct auth.

I had an idea of doing something similar with MQTT. Data at rest and data in motion in 1 (or maybe 1.5) components.

[jordan801]

Sure can! I contributed on this project a few months ago. Hoping to use it soon on a project.

I implemented a portion of crud with a vuejs app. It's a pretty neat setup. Just authorize with OAuth2 and then if you have permissions youre good to go!

[edoceo]

yea, thats pretty easy. a small Go/Rust/whatever program to do WS and write (well formed, authenticated) message data via INSERT

[tyre]

Do you know if there is something similar for SQLite? I've been looking for a while and wonder if maybe listening to the WAL is the best option. There are hooks to tie into but the database I'm looking at isn't mine (it's local on my machine for another application) so idk if I should be futzing around with it.

[WJW]

https://litestream.io/ ?

[pihentagy]

How does this relates to Debezium?

[simonw]

Looks to me like it uses the same trick as Debezium (and similar projects) - listening to the replication stream and turning it into usable information - but it's a totally separate implementation built on top of Elixir: https://github.com/supabase/realtime/blob/master/server/lib/...

[yawnxyz]

How is this similar/different from using CouchDB as a live database? I've been toying around with both (hosting CouchDB on DO)

Which one would you recommend for small-ish (~100 user, <10k records) projects?

[eloff]

For a project that small you can use NoSQL without issue because you can always scan the whole thing when needed. For larger data sets you want the relational model unless you're very sure you don't need it (and most people who think they're in this category are just wrong and will find out later the hard way.)

[gregw2]

Anyone know of any open source or cheap web/browser apps that let you edit data in postgres tables?

Using this tech or something more primitive?

I want to get some end user feedback on predictive model outputs...

[anaganisk]

https://github.com/dbeaver/cloudbeaver

[tarun_anand]

How does this work if I also need to replicate the database for disaster recovery using replication methods other than logical?

[hmsimha]

This is something I have an immediate need for (and was about to build myself) to use with a PostgreSQL/TimescaleDB instance. Is it possible to have new subscriptions get up to an hour of historical data before streaming starts, or even better, to supply a parameter to receive historical data from a timestamp?

[sk5t]

Stream history is kind of thorny; oughtn't one consider Kafka, Kinesis, etc., for that instead?

[sscarduzio]

Will this work on AWS RDS?

[cglace]

So this is similar to how you could subscribe to queries in meteorjs.