Use Triggers to Automatically Update Supabase Tables

Christian Freeman: ...and the last thing we need to do before getting started building our user interface and working with Supabase inside of our JavaScript project is one more set of SQL configurations that will allow us to keep a real-time update between this public user table and our off.user table, which is managed by Supabase and is updated any time that someone signs into our project.

In order to do this, we need to basically look for any new users that are created here. For instance, Christian at christianfreeman.com. When that user is created, we need to create a corresponding user inside of our user table.

The reason for this is that this public.user table is what is accessible and modifiable by our application. The auth.user table, which is represented here, is under a off schema, which is protected by Supabase and you can't actually update or modify it in any way.

To do this, what we're going to do is set up a SQL snippet here in the SQL snippet section. I'll just click new query and I'm going to rename it to update public users table. Now, the process for doing this is based specifically on a guide in the Supabase documentation called managing user data, which explains for security purposes, the off schema is not exposed on the auto-generated API.

Even though Supabase provides an opt-out users table, it can be helpful to create tables in the public schema for storing user data that you want to access via the API. If we come down to this advanced technique section and find this using triggers, this will provide the code that we need.

We need to execute this inside of our Supabase database in order to automatically create a new public.users row for every associated opt-out users row that is created. This will automatically do it for us, as long as we run this in our SQL database.

Let's begin by copying this code which creates a function called handle new user. It will paste our function into the SQL editor and now we can walk through how it actually works. We're creating a new function called handle new user.

We have some configuration setup here, which you can ignore for now. Then inside of our begin and end block is where our code actually runs. We're going to insert into public.users. Now, click Note.

Depending on how you set this up, public.users may be the name of your table or if you've been going through this course in the same way that I've set it up, you'll notice it's actually user, not users. This is slightly different than how the documentation does it. Your mileage may vary there.

Let's change this to public.user to match the name of our table. Basically, every time that this function is called, we're going to insert a row into our user table which has an ID here which we're going to be setting based on the values of new.ID, which is going to be a record we'll pass in just a second.

Once that's done, we'll return that new record back from this function. Now, the second part of this is the trigger. Let's copy this second part of the code here and paste it in below the function.

Every time that there is an insert on auth.users, we're going to create this trigger called on auth user-created, which is going to execute the procedure public.handle new user. This is our function up here.

Every time that a new user is created on the auth.users table, we're going to call this function handle new user, which is going to add a associated public.user row with the same ID from our auth.users table, that matches any user that's signed in or created an account.

Let's run this function. You can see it says success. No rows returned. Then to test that, let's come into our authentication table and I'm going to invite a new user.

I'm just going to send an invite to myself here and you can see that once it's completed, it will have sent an invite email to signalnerve@hey.com. You can see the associated row has been created and it has this user ID 6870CBE8.

If we just copy this first part here, let's come into our table editor. We'll look at the user table and see. We search here 6870CBE8. This is a new user that's been created on this public.user table. It doesn't have a username for now because it hasn't been set, but this matches the user in our auth.users table.

We'll now have a corresponding public user record, which we can use to set fields and information such as username or anything else you might add to your application, which will correspond to the private auth.user table that Supabase manages for us.