Use Supabase to Subscribe to Database Events with Postgres Triggers

Supabase

Share this video with your friends

We now have a Postgres function that can handle creating a profile for a user. In this video, we implement a Postgres trigger to listen to INSERT events on the auth.users table and call our create_profile_for_user function. This will ensure that every new user that logs into our application has an associated profile.

Triggers are a super powerful feature of PostgreSQL! When combined with Supabase real-time - covered later in the course - our applications can have multiple users changing data in the db, and UIs automatically updating without refreshing the page.

Joshua Michaels

~ 3 years ago

Hello...great tutorial but stuck at this point. Followed the setup in Supabase exactly but after deleting the user (me) and trying to login again, getting back the query param: ?error=server_error&error_description=Database+error+saving+new+user

Seems to be related to this issue: https://github.com/supabase/supabase/issues/563

Any ideas on how to fix and still use the GUI to create the users table? I've cleared cache + cookies completely as well as resaved GitHub auth settings in Supabase and no joy.

Thanks!

Jon Meyers(instructor)

~ 3 years ago

Hey! Sorry for the late response! The lead up to holidays time was crazy crazy! Can you try deleting the postgres function from the previous video and stepping through its creation again with the video - you may need to delete the trigger first. Make sure you set the "Type of security" to "Security Definer". Failing this you could try doing it with pure SQL - similar to this solution: https://github.com/supabase/supabase/issues/563#issuecomment-772954907

Lastly, if it is still being weird, can you send me your project ref (part of your Supabase URL) and I can look into it 👍

Joshua Michaels

~ 3 years ago

Hey Jon, No worries - thanks for the reply.

I deleted the function and trigger and recreated step-by-step and still got the same query param error.

Then tried creating with SQL as per the solution in the GitHub link and getting an error when trying to invite a user via the UI: Failed to invite new user. Database error saving new user.

So yeah if you don't mind looking into my project setup when you have a chance, here is the ref: dtscczuqxwjopifwcrrg

Thanks so much.

Scott Carlton

~ 3 years ago

Ya I am having the same issue. Did anyone find a solution to this. I've tried the github link but still no luck.

~ 3 years ago

I was getting the same error as mentioned above ("?error=server_error&error_description=Database+error+saving+new+user") but kept deleting the Function and Trigger and trying again, and on the third time it was working. In my case I think I missed one small step each time. The step I missed finally was in creating the Trigger: setting the Orientation to "row" instead of "statement." So make very sure that you're doing each step. (Personally, I can miss little steps like this when it's a lot of GUI action, rather than CLIs etc.)

Jon Meyers(instructor)

~ 3 years ago

Very strange that this is so intermittent!

If anyone is still having this issue, could you try heading over to the SQL Editor in your Supabase dashboard and pasting in each of these three statements and clicking RUN.

drop function if exists public.create_profile_for_user cascade;

create or replace function public.create_profile_for_user()
returns trigger as $$
begin
  insert into public.profile (id)
  values (new.id);
  return new;
end;
$$ language plpgsql security definer;

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

Jon Meyers(instructor)

~ 3 years ago

If you're getting the same error when signing a new user in for the first time, please log a support ticket by emailing: support@supabase.com.

Please include your project ref so someone can look into what is going on 👍