Automate Database Processes: Supabase Triggers

Automate Database Processes: Supabase Triggers

Read 381 times

#supabase

#database

#sql

#automation

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

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:

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.

function setting

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

function setting

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.

trigger setting

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

Back to articles