Use Supabase to Subscribe to Database Events with Postgres Triggers
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.
Instructor: [0:00] In Postgres, we can create something called a trigger. This is a way for us to subscribe to particular events in the database, such as INSERT, UPDATE or DELETE. When these events take place, we can automatically call a function, like our create_profile_for_user() function.
[0:14] Supabase does contain a nice UI for creating triggers, but unfortunately, it only has access to the public schema, not the auth schema, where our Users table is. For this, we're going to write some SQL.
[0:25] Again, we're going to create a new query. We're going to say CREATE TRIGGER, and we're going to call this one create_new_profile_for_user. We want this to happen after our user has been inserted into the auth.userstable. We want it to happen for each row, and we want it to execute our procedure, create_profile_for_user.
[0:47] Remember, that was the name of our function that we created in the previous video. When we run that snippet, we see success, no rows returned, which means everything is all good. We can now test this is working correctly by coming back over to Authentication and Users and deleting our currently logged in user.
[1:03] Now, back in our application, we can navigate to the log in route. If we come back to Supabase and Refresh, we should see that our new user has been created. Now, when we navigate to our Profile table, we'll see a new row has automatically been created for us with its ID matching our new user.
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 👍
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.
J
Ya I am having the same issue. Did anyone find a solution to this. I've tried the github link but still no luck.
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.)
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();
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 👍
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+userSeems 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!