Save many-to-many relationships via js sdk

[wiesson]

I have three tables

images_image (id, url)
itinerary_image (id, itinerary_id (fk), image_id (fk)) // mapping table
bookings_itinerary (id, ...)

Each itinerary can have multiple images and those images could be linked to other resources as well. Is it possible to save the many-to-many relation via insert of bookings_itinerary? I was only able to achieve this by waiting for the insert of bookings_itinerary and once I have the id, I do a new insert in the mapping table which makes everything more complex.

Is there a shortcut? If I add the image ids as an array to the bookings_itinerary object, I just receive errors that the column does not exist.

[mstade]

Sadly, not as far as I know. You'd have to write a database function that you could call with your inputs, and have that do the inserts and all in a transaction, otherwise you have to do multiple Supabase calls using the results of previous calls. It's unfortunate and not very performant since it creates a waterfall like profile, but it's the way it is.

Writing functions isn't that bad though, and you can even use JavaScript if you enable the v8 extension. Calling them using the Supabase client is also very easy.

[soedirgo]

There has been work on the PostgREST side to enable this, but yes rpc() is the way to go for now.

• A relational insert PostgREST/postgrest#818
• Multiple requests in a transaction PostgREST/postgrest#286

[wiesson]

Thanks! Do you have an example? I find this issue annoying because I'd like to create a lot of relations in one transaction. I am torn between using Hasura with GraphQL or stay with supabase.