Automate Database Processes: Supabase Triggers
Tags: supabase, database, sql, automation
Published: 2023-10-17 13:12:33

This article follows my [Row Level Security](https://dantedecodes.vercel.app/articles/fortify-your-database-supabases-row-level-security-3fh8) 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](https://supabase.com/docs/guides/getting-started/tutorials/with-sveltekit) 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:  
```sql
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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5xshuhj36rhjfalqsmhd.png)  

> The `auth.users` table is from the authentication service I talked about in the prior article.  

&nbsp;

#### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mczap22mrrn3n2g7qyeg.png)

---

### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dens3cwv0r4aue7fyku7.png)  

&nbsp;&nbsp;

#### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vsxmhz18giwgvqa89xtq.png)

- **Definition**: This is the core of what our function will be doing. It will be similar to the one before:
```sql
begin
    insert into public.user_data(id)
    values (new.id);
	
    return new;
end
```

![function setting](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9tre1xhwhdq28mqc9rqy.png)

- **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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fipd3kzcqym99j0zt48g.png)

---

### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xhvtcaenbj80vb0gig8y.png)

- **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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vrnpintjqm933k5zo55o.png)

---

## 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](https://twitter.com/Psypher1), [Discord](https://discord.com/users/Pharaoh#5441/) and [LinkedIn](https://www.linkedin.com/in/jamesmidzi/)