Hey HN, this is an Elixir server (Phoenix) that allows you to listen to changes in your database via websockets.
Basically the Phoenix server
1. listens to PostgreSQL's native replication functionality (WAL stream)
2. converts the byte stream into JSON
3. it then broadcasts over websockets
I wrote this originally to replace Firebase's firestore database, which I wasn't too pleased with. I needed the realtime functionality for messaging inside my apps.
Thought the community here might like it. Postgres is an amazing database - with realtime functionality I was able to consolidate everything into one database.
Would love feedback. There are a few (many?) bugs and a lot of things to iron out, but I'm working with some close friends to make it awesome.
I know I’m late to the party, but I’ve recently come around to the idea of using apps that sit in front of Postgres to provide an API for free. This seems like an amazing project! I mean, I knew PostgREST existed, but I didn’t realise think it would be possible to make something similar that worked for the real-time use case. Brilliant stuff!
That's quite clever, I never actually thought of that. Guessing from your username, this is your package? It's very cool
Yes, we will allow filtering on the listener, just shipping early. Next steps, we will only filter on the Primary keys - something like supabase.from('users:id.eq.1') - but eventually we will support advanced filtering like this:
https://supabase.io/docs/library/get#filtering
Just a quick precision, if I'm not mistaken: this project is not decoding the (version, architecture dependent) WAL stream as it seems to be implied by https://news.ycombinator.com/item?id=22114603 But it rather uses logical decoding with the pgoutput plugin (which was introduced in Postgres 10, so I'm guessing it doesn't support out of the box earlier versions). This approach makes total sense, though.
Apart from that, reading the documentation I don't see how app.supabase will interface with my source database. Does it need to be open for connections from supabase, or is supabase also a database hosting service, or an agent is installed alongside the source database?
Yes you're right about the logical decoding, thanks for clarifying and I'll make sure I update the docs.
For Supabase (when we finally have sign ups), the idea is that you can BYO postgres, or we will host. If BYO, you would have to set wal_level = logical, and create a publication for us to listen to (eg: CREATE PUBLICATION supabase_realtime FOR ALL TABLES).
We will host the middleware which will give realtime and restful api. The idea is that you focus on your database schema and we handle the rest.
Still early days though. We are just trying to find our feet, including figuring out whether this is even something people would want
May you ellaborate on the BYO concept? How do you bring it? Or you will just ask Postgres owners to install an agent which will then connect from customers' premises to your middleware?
BTW hosting your own Postgres service is a huge project on its own. Take this into consideration.
For the BYO - all we need is the database connection string. We will then host an elixir server + nginx. This doesn’t need any agent on the database itself. I’ll add a diagram to to website, I think I can make this a lot clearer.
> hosting your own Postgres service is a huge project
Yes, you’re right. We will only do it if it looks like our potential customers are leaving because they don’t know how to do it themselves. Even then, we will just wrap an existing managed service to start with. We’d like to make Postgres as simple to use as Firebase (for newbie database users)
> For the BYO - all we need is the database connection string.
Then this means that the database should be publicly accessible. Many would not accept this from a security perspective (regardless of the use of SSL and even certificate authentication only).
We will also provide an IP address that the user can whitelist so they don’t have to make it completely public. But I would understand if even that is not enough for some.
That’s the beauty of opensource though. If we end up just building a useful product that everyone hosts themselves, I’m happy with that.
Btw, these are great questions. Thanks - they will really help me to improve how I have explained it on the website
Good question. We will be adding a static API key which is set as an ENV variable.
For our hosted service we put this behind an nginx instance that has more robust access control. We’d prefer to keep it this way so the realtime server can remain as simple as possible (and completely stateless)
Yea, I'd be really interested in this.
Part of using Firebase Realtime DB was that you can use Firebase Auth with it for access control and set rules on the database based on it.
This is pretty good. I built an open source project on GitHub that listened to table changes in Postgres a few years back and could relay them back to the UI
As I mentioned on a comment above (https://news.ycombinator.com/item?id=22115752) it doesn't really require the WAL but rather the logical decoding stream. This is offered by some of the PostgreSQL-aaS.
Basically the Phoenix server
1. listens to PostgreSQL's native replication functionality (WAL stream)
2. converts the byte stream into JSON
3. it then broadcasts over websockets
I wrote this originally to replace Firebase's firestore database, which I wasn't too pleased with. I needed the realtime functionality for messaging inside my apps.
Thought the community here might like it. Postgres is an amazing database - with realtime functionality I was able to consolidate everything into one database.
Would love feedback. There are a few (many?) bugs and a lot of things to iron out, but I'm working with some close friends to make it awesome.
Not much is different now except I don't use NOTIFY anymore due to the 8000 byte payload limitation (and the reason why I made the elixir server)
https://github.com/numtel/pg-live-select/blob/master/lib/tri...
Reading the replication log is probably faster though.
MySQL has row-based replication too and there's clients in most languages if you want to support it on your saas.
Do you have plans to support changes to specific SELECT queries or keep it at the row level?
Yes, we will allow filtering on the listener, just shipping early. Next steps, we will only filter on the Primary keys - something like supabase.from('users:id.eq.1') - but eventually we will support advanced filtering like this: https://supabase.io/docs/library/get#filtering
Apart from that, reading the documentation I don't see how app.supabase will interface with my source database. Does it need to be open for connections from supabase, or is supabase also a database hosting service, or an agent is installed alongside the source database?
For Supabase (when we finally have sign ups), the idea is that you can BYO postgres, or we will host. If BYO, you would have to set wal_level = logical, and create a publication for us to listen to (eg: CREATE PUBLICATION supabase_realtime FOR ALL TABLES).
We will host the middleware which will give realtime and restful api. The idea is that you focus on your database schema and we handle the rest.
Still early days though. We are just trying to find our feet, including figuring out whether this is even something people would want
May you ellaborate on the BYO concept? How do you bring it? Or you will just ask Postgres owners to install an agent which will then connect from customers' premises to your middleware?
BTW hosting your own Postgres service is a huge project on its own. Take this into consideration.
> hosting your own Postgres service is a huge project
Yes, you’re right. We will only do it if it looks like our potential customers are leaving because they don’t know how to do it themselves. Even then, we will just wrap an existing managed service to start with. We’d like to make Postgres as simple to use as Firebase (for newbie database users)
Then this means that the database should be publicly accessible. Many would not accept this from a security perspective (regardless of the use of SSL and even certificate authentication only).
That’s the beauty of opensource though. If we end up just building a useful product that everyone hosts themselves, I’m happy with that.
Btw, these are great questions. Thanks - they will really help me to improve how I have explained it on the website
One question: Does this have any access control built in? Otherwise, anyone could change anything in database from browser.
For our hosted service we put this behind an nginx instance that has more robust access control. We’d prefer to keep it this way so the realtime server can remain as simple as possible (and completely stateless)
My question more on the lines of application level access control. As in how to ensure one user can only modify certain data in a table?
For example assuming a social networking site, if entire database is exposed one user could update the profile of other user knowing their user ID.
I think firebase used to have a concept of security rules for such things
And yes, Firebase still has that concept. You can achieve the same in postgres with Row Level Security (https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html) although admittedly it's a bit easier on Firebase.