How to insert data into multiple tables in Supabase upon user registration?

Data and Databases
1

When using Supabase, I need to add the identifier of a newly registered user to the users table. Here’s a basic function I’m currently using:

create function public.add_user()
returns trigger
language plpgsql
security definer set search_path = public

as $$
begin
  insert into public.users (id)
  values (new.id);
  return new;
end;
$$;

create trigger after_user_created
  after insert on auth.users
  for each row execute procedure public.add_user();

Additionally, I want to insert entries into two other tables: the organisations table, which should have a unique id and default name as My first org, and the org_users table, which will hold a unique id, user_id from the users table, org_id from the organisations table, and role_id from the org_roles table that corresponds to the role of an owner.

I acknowledge that there may be similar questions out there, yet the responses haven’t aligned with what I’m attempting to achieve using my understanding of SQL or Postgres.

While it’s possible to handle this client-side without issues, I believe that figuring out how to accomplish this in SQL is beneficial for others as well.

What steps do I need to take to create a single function that performs the following tasks?

  • Create a new entry in the users table using the auth.users.id,
  • Create a new entry in the organisations table with a unique id and name set to My first org,
  • Lastly, create an entry in the org_users table with a unique id, the user_id from the users table, and the org_id from the organisations table.

Here’s a SQL snippet illustrating how I envision this working:

WITH inserted_user AS (
   insert into public.users (id) values (new.id) returning new
),
new_org AS (
   insert into public.organisations (id, name) values (uuid_generate_v4(), 'My first org') returning id
)
insert into public.org_users (user_id, org_id)
   select inserted_user.id, new_org.id from inserted_user, new_org;
return NULL;
2

hey, have you considerd exploring supabase’s serverless functions or stored procedures? I’m curious if you’ve tried using those to manage more complex data insertion processes. Also, do you envisage any potential conflicts or errors when handling multple transactions in sql? Let’s chat about possible pitfalls!

3

Utilizing serverless functions in Supabase can offer a more flexible way to manage these operations involving multiple tables. For similar tasks in the past, I’ve implemented a single stored procedure that combines the steps into a transaction block. This approach ensures atomicity. You can use BEGIN and END to group the SQL commands. By using a transaction, if one operation fails, the changes are rolled back, maintaining data consistency. Supabase functions facilitate this process, reducing the risk of partial updates and minimizing potential inconsistencies.

4

consider looking into using CTEs (Common Table Expressions) for chaining your insertions. With CTEs, you can handle multiple operations in one seamless flow without switching contexts. By having CTEs for each table insert, you ensure all your data ops are in sync. avoid complications with nested transactions by staying atomic!