Add support for relationship naming to avoid postgres from throwing multiple relationships error

[dukesx]

Feature request

Is your feature request related to a problem? Please describe.

I am a hasura user. I migrated from that to supabase. My biggest gripe yet with supabase is that there is no way that i can "explicitly" set what kind of relationship two tables have. In Hasura console, i not only could name the relationship but also decide individually, which one was which. This way, although complex, i still had the ability to fetch things like this :

articles {
 authors_to_articles {
 name 
}
bookmarks_to_articles {
  author {
  name
 }
}

"bookmarks_to_articles" and "authors_to_articles" are relationship names, not table names. bookmarks and articles, both are linked to authors through these relationships. This confuses postgres since there is multiple relationship in same query , by default and i have witnessed it in supabase. there is a workround mentioned in documentation but i bet this would be a killer feature for enthusiasts.

Describe the solution you'd like

allow us to specify name for relationship and its type (one to many, etc ) when we mention the table and column to link for foreign key.

Describe alternatives you've considered

Hasura, Directus both offer this in their admin panels respectively.

[steve-chavez]

allow us to specify name for relationship and its type (one to many, etc ) when we mention the table and column to link for foreign key.

This is possible with https://postgrest.org/en/stable/api.html#computed-relationships. You can specify the cardinality, join conditions and the name of the relationship with plain SQL functions.

(The above feature is not yet deployed in Supabase)

[steve-chavez]

"bookmarks_to_articles" and "authors_to_articles" are relationship names, not table names. bookmarks and articles, both are linked to authors through these relationships. This confuses postgres since there is multiple relationship in same query

Additionally, right now you can rename a foreign key and use it as a relationship name, this also avoids errors as the relationship is unambiguous. See https://postgrest.org/en/stable/api.html#target-disambiguation

[dukesx]

allow us to specify name for relationship and its type (one to many, etc ) when we mention the table and column to link for foreign key.

This is possible with https://postgrest.org/en/stable/api.html#computed-relationships. You can specify the cardinality, join conditions and the name of the relationship with plain SQL functions.

(The above feature is not yet deployed in Supabase)

yes that is an option but thats a real toll on development when the number of tables and features for your apps grow. I was hoping that you guys would have an ace up your sleeve, maybe consider checking out with how Hasura did this. I am building a blog like dev.to and i have just added a bookmarks table and this has now become a menace to handle postgres's confusions. Consider, that this is a very small scale i am working on, imagine if you guys could come up with an easier, friendlier, GUI based way like Hasura has, maybe this will save alot of people, alot of work.

[dukesx]

"bookmarks_to_articles" and "authors_to_articles" are relationship names, not table names. bookmarks and articles, both are linked to authors through these relationships. This confuses postgres since there is multiple relationship in same query

Additionally, right now you can rename a foreign key and use it as a relationship name, this also avoids errors as the relationship is unambiguous. See https://postgrest.org/en/stable/api.html#target-disambiguation

Yes, i have tried this and it doesn't help. Postgres is too intelligent to still figure out this.

[steve-chavez]

Yes, i have tried this and it doesn't help. Postgres is too intelligent to still figure out this.

Here you mean that the SQL interface for renaming the foreign key is complex? Would a UI that allows renaming the foreign key relationships help?

[dukesx]

Yes, i have tried this and it doesn't help. Postgres is too intelligent to still figure out this.

Here you mean that the SQL interface for renaming the foreign key is complex? Would a UI that allows renaming the foreign key relationships help?

Not at all, what i mean is, that i have tried this technique and it still threw me the same error.

[steve-chavez]

Not at all, what i mean is, that i have tried this technique and it still threw me the same error.

Could you share the error and the snippet you used?

[dukesx]

"bookmarks_to_articles" and "authors_to_articles" are relationship names, not table names. bookmarks and articles, both are linked to authors through these relationships. This confuses postgres since there is multiple relationship in same query

Additionally, right now you can rename a foreign key and use it as a relationship name, this also avoids errors as the relationship is unambiguous. See https://postgrest.org/en/stable/api.html#target-disambiguation

Pardon if I am asking the wrong question, but when I say relationship name, you do understand I meant constraint name? If not then I do apologise for the confusion. I mean we need raw sql to rename but in hasura we could do in GUI.

[steve-chavez]

Sorry for the late reply. Yes, here I meant you can rename the constraint

ALTER TABLE orders
   RENAME CONSTRAINT orders_billing_address_id_fkey  TO billing_address;

And then use it as

const { data, error } = await supabase.from('orders').select(`
    name,
    billing_address (
      name
    )
  `)