One API call to insert in multiple tables Supabase

Supabase is super awesome! One constraint — there is no way to manage join tables easily. For example, let's say we have a bookstable in Supabase and another table book_usersto manage user access to books. If a new user adds a book, it would be great to make one call to Supabase to insert into both tables.

schema:
users
id uuid
books
id uuid 
title text
book_users
id uuid
book_id uuid -- foreign key relationship
user_id uuid -- foreign key relationship

Note: I prefer uuid but other options are fine.

Here are three options, 1) Two calls 2) One call with RPC or 3) One call with Trigger

Option 1: Two calls

We create the books wait for the response, then create the book_user . Totally fine, but if you’re calling the Supabase API from the client, this could be a bit slow, particularly if you want to insert in lots of tables.

const title = 'New Book'
const user_id = <user id>
const { data } = await supabase
   .from('books)
   .insert({ title })
const book = data[0] // ignoring error handling for simplicity
const { data, error } = await supabase
   .from('book_users)
   .insert({ user_id, book_id: book.id })

Option 2: RPC

We create an RPC in Postgres to insert values into both tables with one API call. You can call Postgres functions as a “Remote Procedure Call”. more details. https://supabase.com/docs/reference/javascript/rpc

CREATE OR REPLACE FUNCTION public.insert_books(
  title text,
  user_id uuid
)
RETURNS setof public.books
AS $$
  declare
  book_id uuid;
begin
  INSERT into books 
    (title) 
    values (title) 
    returning id 
    into book_id;
  INSERT into book_users 
    (book_id, user_id)
     values (book_id, user_id);
RETURN query select * from boards where boards.id = board_id;
END $$ language plpgsql;

To call this RPC we just need to do

supabase.rpc('insert_books', { title: 'New book', user_id })

So good notes here too! https://stackoverflow.com/questions/69595895/how-to-insert-in-multiple-tables-with-a-single-api-call-in-supabase

Option 3: Trigger

Here we just make one API call to booksand set up a trigger in Postgres that listens to the booksinsert and then insert to the join table thereafter.

CREATE OR REPLACE FUNCTION public.insert_book_user()
RETURNS TRIGGER
AS $$
begin
  INSERT into book_users
     (book_id, user_id)
      values (new.id, new.user_id);
  RETURN NEW;
END $$ language plpgsql;
DROP TRIGGER IF EXISTS insert_book_user_after_book_inserted on public.book;
CREATE TRIGGER insert_book_user_after_book_inserted
   after INSERT ON public.books
FOR EACH ROW EXECUTE PROCEDURE insert_book_user();

To execute this we just do the standard Supabase insert and let the trigger take care of the rest.

supabase.from('books).insert({ title: 'New book', user_id })