Use Postgres Functions to Implement Database Logic with Supabase
Each time a new user signs in, we want to create an associated profile. In PostgreSQL, we can use triggers to listen to events - such as INSERT - on specific tables - such as auth.users - and execute a Postgres function.
In this video, we use the Supabase UI to create a Postgres function that returns a trigger. This is a special kind of function that can be invoked when particular events occur in the database - covered in the next lesson. This function will be responsible for creating a new row in the profile table, using the ID from our auth.users record.
Instructor: [0:00] Each time our application creates a new user, we want to go and create an associated row in the Profile table. We can do this by creating a Postgres function, and a quick mention here that Postgres functions themselves are not in Alpha preview, just Supabase's UI for creating them.
[0:15] I'm going to name my function Create Profile For User, and this is going to be on the public schema. The return type for this is going to be trigger. Then, down in the definition section, we can wrap the body of our function in a begin and end.
[0:27] We want to say, "Insert into," public.profile. We just care about the ID column in this case, and we want to insert the VALUES(NEW.id). This function is automatically going to be passed this new parameter, and that's going to represent our new user that's being added to the Auth.users table. We also need to return that new variable from our function. Lastly, we need to scroll down and enable Advanced Settings and set the type of security to be Security Definer and click Confirm. Now, we have a function in the database itself that we can call anytime we want to create a profile for a new user.
I learned a lot by watching this. It would be really awesome if you could explain how to create a local version of this exact DB after learning how to deploy to prod. In a real world situation we'd likely have the production version, and a local version that can be ran without disrupting the production DB. Fingers crossed for that.