Strategies for handling one-to-many or many-to-many relations

[kapoko]

Maybe not so much a supabase/realtime question but working with realtime stuff in general. I have realtime setup with realtime-js. As in the examples I fetch the initial data from an api, using TypeORM, with which I set op a many-to-one relationship (for example comments to a user).

My api returns something like this

{
  "id": "1",
  "owner": {
    "id": "1",
    "name": "Joe"
  }
}

Under the hood in the table ownerId with just the users' id is stored, so when subscribing to the channel on a change I get just the user ID, not the user object. I could query those separately but this will get messy very fast.

I understand in firebase working with relations is also not super trivial to do. Unless you're storing entire collections under an object, but that's not ideal. I can imagine writing my own backend logic that suits my needs (keeping slices of data in sync in many to many relationships on the object, a valid strategy in firebase). Or is this something that down the road realtime is gonna be able to support?

[kiwicopple]

Hey @kapoko - thanks for the patience. We're still brainstorming on this one. I shared some thoughts in another issue, and here is the current thinking: https://paul.copplest.one/blog/realtime-user-store.html

We are working on our CLI this quarter, and I hope that it will incrementally move us towards a solution where we can generate your schema into a set of "subscription rules". This would mean that you can fetch your data using PostgREST, then the data is constantly kept in sync through our Realtime engine. We re-enginered our supabase-js library earlier to have one socket-connection per "client" (previously it would generate a connection per "subscribe()") and so the parts are slowly falling into place.

If you have some more ideas on this feel free to drop them here! its quite tricky to solve timely dataflow, and we're still figuring out how all the pieces can fit together to make sure the dev experience is seamless

[kapoko]

Thanks for the reply!

I ended up putting some json data from the relations directly on the row so I can access it realtime. This is a little more maintenance-heavy as I need to keep data in sync in two places, but suits my simple use-case. This way I still have true relations in the database and I can keep track of the data I need from them in real time. I wonder if this becomes more of a burden when a project gets bigger, but I'll have to see. Thanks for the great work so far and good luck! If I come up with anything I'll be sure to post here.

[braebo]

I apologize if this is naive, but I always felt like Firebase was in desperate need of a GraphQL layer to greatly simplify more complex relationships / queries. I feel as though the general consensus on GraphQL is that it is a step forward in the right direction. If this is the case, I think the Supabase team should reconsider their support for GraphQL.

[kiwicopple]

Hey @fractalhq - just note that we already have joins/relationships on our client, provided by PostgREST. The question is how to do this in real-time, to keep the data on the client always up to date. Even the GraphQL servers haven't really solved this IMO. "Under the hood" most just poll the database (perhaps every second). We could do this very easily to get the same result, but it's expensive on the database.

GraphQL Subscriptions are the right step forward, but they vary in their implementation on how "efficient" they are. We are aiming to be extremely efficient by using "incrementally updating data flow".

In the meantime, you can always connect your GraphQL server to the database if you prefer to use an existing implementation.