How to insert in multiple tables with a single API call in Supabase

Question

This is the simplified structure of my tables - 2 main tables, one relation table.
What's the best way to handle an insert API for this? If I just have a Client and Supabase:

- First API call to insert book and get ID
- Second API call to insert genre and get ID
- Third API call to insert book-genre relation

This is what I can think of, but 3 API calls seems wrong.
Is there a way where I can do insert into these 3 tables with a single API call from my client, like a single postgres function that I can call?
Please share a general example with the API, thanks!

score 14 · Accepted Answer · answered Oct 16 '21 at 14:27

Is there any reason you need to do this with a single call? I'm assuming from your structure that you're not going to create a new genre for every book you create, so most of the time, you're just inserting a book record and a book_gen_rel record. In the real world, you're probably going to have books that fall into multiple genres, so eventually you're going to be changing your function to handle the insert of a single book along with multiple genres in a single call.

That being said, there are two ways too approach this. You can make multiple API calls from the client (and there's really no problem doing this -- it's quite common). Second, you could do it all in a single call if you create a PostgreSQL function and call it with .rpc().

Example using just client calls to insert a record in each table:

    const { data: genre_data, error: genre_error } = await supabase
      .from('genre')
      .insert([
        { name: 'Technology' }
      ]);
    const genre_id = genre_data[0].id;  
    const { data: book_data, error: book_error } = await supabase
      .from('book')
      .insert([
        { name: 'The Joys of PostgreSQL' }
      ]);
    const book_id = book_data[0].id;
    const { data: book_genre_rel_data, error: book_genre_rel_error } = await supabase
      .from('book_genre_rel_data')
      .insert([
        { book_id, genre_id }
      ]);

Here's a single SQL statement to insert into the 3 tables at once:

WITH genre AS (
  insert into genre (name) values ('horror') returning id
),
book AS (
  insert into book (name) values ('my scary book') returning id
)
insert into book_genre_rel (genre_id, book_id) 
  select genre.id, book.id from genre, book

Now here's a PostgreSQL function to do everything in a single function call:

CREATE OR REPLACE FUNCTION public.insert_book_and_genre(book_name text, genre_name text)
    RETURNS void language SQL AS
$$
  WITH genre AS (
    insert into genre (name) values (genre_name) returning id
  ),
  book AS (
    insert into book (name) values (book_name) returning id
  )
  insert into book_genre_rel (genre_id, book_id) 
    select genre.id, book.id from genre, book
$$

Here's an example to test it:

select insert_book_and_genre('how to win friends by writing good sql', 'self-help')

Now, if you've created that function (inside the Supabase Query Editor), then you can call it from the client like this:

const { data, error } = await supabase
  .rpc('insert_book_and_genre', {book_name: 'how I became a millionaire at age 3', genre_name: 'lifestyle'})

Again, I don't recommend this approach, at least not for the genre part. You should insert your genres first (they probably won't change) and simplify this to just insert a book and a book_genre_rel record.

My pleasure. Personally, the simple solution I'd use is the first one -- just make multiple client calls as needed, then you can adjust as necessary (very quickly) in your client code. You're going to end up changing things around a bit, as I said. — Mark Burggraf, Oct 16 '21 at 15:29
I would add that this solution assumes these inserts are not required to happen as a transaction. If you need to guarantee that both inserts must happen atomically, you should be using a function and call it using the `rpc` function of the client. If not, you risk having something fail between client calls to the API and causing partial inserts (this can leave your data in an unexpected intermediary state). — bombillazo, Apr 09 '23 at 02:59

How to insert in multiple tables with a single API call in Supabase

1 Answers1