Automate Database Processes: Supabase Triggers
Set up once, rest easy knowing things are working as expected.
This article follows my Row Level Security one ...
As I was doing my tests I came across an issue whereby the
user_data table wasn't created if there was an issue with connectivity. The
upsert that I was trying to run wouldn't fire.
That got me thinking; the profiles table gets created when a user signs up, right? Something, somewhere is being automated to accomplish that. But how?
So, I went on another adventure...
I went searching in my project to see what had been done when I was following the guide that I was using as a starting point. What I discovered was really interesting. The starter that I used from the guide - User Management - created a function. What that function does is create a profiles table row and populates it with certain fields.
That looks like this:
insert into public.profiles (id, full_name, avatar_url)
values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
With this knowledge, I went digging further and found out that functions are the first key to the puzzle I was trying to solve, the other being triggers.
What Are Triggers?
Triggers allow us to watch database events (insert, update, delete) and call a function whenever they occur, allowing us to automate functionality in our database while keeping our data in sync.
Automating Database Events
The automation that I need is comprised of three main aspects:
Recall in the last article I said I wasn't going to delve into table relations. For the coming steps, relations are the lynchpin to a lot of what I want to accomplish.
To start off, here's the relation between my profiles table and the
auth.users table is from the authentication service I talked about in the prior article.
User Data Table
I could choose to link this table directly to the
auth.users one, the same way the profiles one is - but that creates a separate relation. What I want is to relate the
user_data one to the
profiles one. In essence; an authenticated user has related profile data as well as user data.
So instead I have made the relation to the profiles one - that way everything is connected.
Triggers, from what I learnt, require functions. Naturally, I started there. The requirements for my function were straightforward:
- When a row in the
profilestable is created, create a row in the
user_datatable that is related by
The settings for functions and triggers can be found in the Database area of my project.
Creating The Function
Let's go through the steps to create a function and the important aspects to take note of.
- Return Type: Because we are making a function that a trigger will call, we have to set its return type to trigger
- Definition: This is the core of what our function will be doing. It will be similar to the one before:
insert into public.user_data(id)
- Type of Security: It is necessary to change this to SECURITY DEFINER. This is to ensure that the trigger is run with the same privileges as a dashboard user. If left as SECURITY INVOKER, it would fail because the user trying to run does not have the necessary permissions.
With the function ready, let's create the trigger that calls it.
Creating The Trigger
These are the important aspects to take note of when creating a trigger.
Table: We have to set the table that is to be watched for changes. In our case, as we discussed, it's the profiles table.
Events: Here we specify what kind of action will invoke our trigger. We want INSERT events.
Trigger Type: Next is to set when we want the trigger to run. We need it to run after because we require there to be a row in the profiles table to be created first. That way it is correctly related.
Orientation: As we want accompanying data for each user that is created, we set this to Row. Otherwise, it would only run once and fail on subsequent attempts.
Function to trigger: Lastly, we choose the function we need to be triggered.
We have created a function that is run through a trigger, allowing us to have database actions and events automated whenever specific conditions are met. What it does is create a row in the
user_data table whenever an insert event happens in the
profiles table - all this being linked via the
As with most things in Supabase, this also wasn't that hard to get up and running. The beauty also is that I know exactly what is going on and what I have to do if I need to modify or add to it.
This adventure has been very fruitful. The fact that I can ensure that I can ensure that user data and info is created and related fills me with confidence.