Use Supabase to Subscribe to Database Events with Postgres Triggers

Jon Meyers
InstructorJon Meyers

Share this video with your friends

Send Tweet
Published 3 months ago
Updated 2 weeks ago

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.

Joshua Michaels
Joshua Michaels
~ a month 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
Jon Meyersinstructor
~ a month 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
Joshua Michaels
~ a month 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.

J