postgrest-js Transactional Update / Upsert / Insert with relationships

Preamble

In the referenced discussion it was mentioned that currently it is not possible to do transactional changes to the database, which is a common case in a lot of DB systems out there.

Obvioulsy, one could workaround it via RPC / Functions but workarounds shouldn't be considered a good solution.

Problem Description

If you are selecting data from related tables you can do so via select('column_name, other_table (other_table_column_name) . This behaviour of automatic relation detection implies that you'd be equally be able to perform such an action on mutation e.g.

// not working:
supabase.from('table').upsert([{ id: 123, name: 'foo', foreign_table: [{foreign_name: 'foobar'}})

What this should do is: Go to the database, find the relation via foreign_table (if easier, allow to provide additional arguments to specify that) and then update/insert all entries within the foreign_table property to have the id from its "parent" related table.

So the outcome here would be

Table 1: [id=123, name='foo']
Table 2: [table1_id=123, foreign_name='foobar']

Why is this crucial and not just "syntactical sugar" enhancement?

From an architectural viewpoint TRANSACTIONS are meant to succeed completely or fail completely. What you get with supabase instead is having sequential inserts of which one can succeed and the next one fail hence leading to corrupt data.

Discussed in https://github.com/supabase/supabase/discussions/710

^{Originally posted by gaurangrshah February 12, 2021} I must be missing something, but I'm currently experimenting with supabase, and I can't seem to figure out how to create many-to-many or one-to-many relationships using the UI. Are there any blog posts, that discuss this? I am more than sure that I'm missing something. I've read through the docs pretty much cover -to-cover and can't seem to find anything other than this: https://supabase.io/docs/guides/database#relationships

Any help/suggestions/guidance would be greatly appreciated! Thanks in advance for the help!