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.