Use Triggers to Automatically Update Your Supabase Tables

Share this video with your friends

Send Tweet
Published 5 months ago
Updated a month ago

Documentation on creating public user tables has been moved here. Supabase has also removed triggers from this doc but you can still use them.

In this lesson, we'll use Supabase's triggers functionality to automatically update data in our application when a user creates an account. Triggers are a powerful functionality of relational databases that allow us to minimize the amount of manual development work we need to do to build applications.

Kristian Freeman: [0:00] Before we begin creating something with the user interface, we have one more SQL thing that we need to run inside a Supabase to make our application work like we'd expect.

[0:08] In our database section, we have this users' auth table, and this is going to store user login data, like it says, within a secure schema. Anytime that I sign in, whether that's via GitHub or via email, all of my authentication data gets stored inside of this users' table. That is useful for managing all of that for us.

[0:30] When it comes time to do stuff in our application, say, for instance, create a user and be able to send messages, you'll see that the user table, which we created earlier, is inside of a different schema. The schema here is auth for users, and then this user table as well as the message table are both inside of the public schema.

[0:50] These two different schemas aren't accessible to each other, so the user table can access the users' table inside of the auth schema.

[1:01] What we need to do is set up something called a trigger which will run each time that something changes inside of the users' table, to add the corresponding data inside of our user table. This pattern is well documented in the Supabase documentation here in the auth guide, which is just

[1:21] There is a recommendation here to create a public.userstable, ours is just called user. That says, "Even though Supabase provides an auth.userstable, it is helpful to create a users table in the public schema which uses the same UUID primary key as auth.users."

[1:37] For security purposes, the auth schema is not exposed on the auto generated API. Creating a public.users table allows you to interact via the Supabase client, which is especially useful for cross table queries.

[1:50] It says, "Pro tip. If you want to add a row to your public users table every time a user signs up, you can use triggers," for example. Then we have this big SQL statement here which says, "Each time something is inserted on auth.users." It says, "Create trigger on auth.user created after insert on auth.users for each row execute procedure public.HandleNewUser."

[2:15] Every time something is inserted into the auth.users table, we want to call this function. What does this function do? It says, "Begin here." This is the bulk of our code. It says, "INSERT INTO public.users (id) the value new.ID and then just return new."

[2:34] What's happening here is that we have this new variable here. This is going to be our new user that gets inserted into auth.users. Every time that something is inserted there, we want to also insert into public.users, a new row setting the ID to the value

[2:53] What we're going to do here is we're going to copy this code. Back in the SQL area, let's come into this open query and let's give it a little bit better of a name. I'm going to right-click and rename here and say, newUserTrigger. The description is add a user row when auth.users has a new row. I'll save that. Then I'm going to paste in my trigger here.

[3:22] There's only one thing that we need to change. Like I mentioned earlier, instead of public.users, I have public.user. I'll change that and change that. We'll say, insert into public.user, the valueID. That's a column here, if I come back into my user table, that's this ID column right here. We know that that is the right name for that.

[3:46] Now, if I come back to SQL here, newUserTrigger, I can execute the statement. It should say, "Success, no rows returned." The trigger hasn't run yet. That's OK. When we begin working with authentication, and add a new user, whether that's login via GitHub or via email, we will have a corresponding user created inside of public.user.

[4:10] Again, the reason that we do that is that this auth.users table will not be accessible via the API. Supabase doesn't expose it as part of the auto-generated SDK API that you have whether you want to access in JavaScript or wherever else.

[4:27] By sinking these tables together, auth.users and public.user, we can do things like say, "Hey, this user sent this message. Here's the ID for that," without having to work inside of the auth.users at all. This is a common pattern. It shows off why triggers are super useful and pretty easy to understand all things considered.

[4:51] The syntax is a little strange in some ways, if you haven't worked with SQL before. Overall, the syntax and understanding how this trigger procedure works, is pretty straightforward.

~ a month ago

Now that Supabase has removed triggers from their documentation as of 08/24/2021 (I coudn't find any information related to the code that's been showed on the video as they have a bunch of broken links in their docs). Is this still something recommended to do?


Lauro Silva
Lauro Silva
~ a month ago

Here's the documentation. The Supabase team mentioned that they removed the triggers because they were a barrier of entry for devs new to SQL, but triggers are still a great way to do it.