Automate Database Processes: Supabase Triggers

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:

begin
  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');
  return new;
end;

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:

Relations
Functions
Triggers

Relations

Recall in the last article I said I wasn’t going to delve into table relations. For the coming steps, relations are the lynch pin to a lot of what I want to accomplish.

Profiles Table

To start off, here’s the relation between my profiles table and the auth.users one:

profile relation

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.

user data relation

Functions

Triggers, from what I learnt, require functions. Naturally, I started there. The requirements for my function were straightforward:

when a row in the profiles table is created, create a row in the user_data table that is related by id

The settings for functions and triggers can be found in the Database area of my project.

database settings

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

function setting

Definition: This is the core of what our function will be doing. It will be similar to the one before:

begin
    insert into public.user_data(id)
    values (new.id);
	
    return new;
end

function setting

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.

function setting

Triggers

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 setting

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.

trigger setting

Recapping

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 id

Last Update: 30 October 2023

Conclusion

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.

Thank you for reading, let’s connect!

Thank you for visiting this little corner of mine. Let’s connect on Twitter, Discord and LinkedIn