Supabase Functions

Create the first Supabase functions and triggers, to smoothen onboarding.

Within PostgreSQL you can create functions (stored procedures) and triggers. These can be used to execute a query when a row is inserted, updated or deleted. Supaboost uses these triggers and functions to update records based on an action.

Functions

As stated above, functions are also called stored procedures. This can either be a query or an update statement to a specific table. For instance, one of the functions we use is checking if there is already a user existing for a specific company.

  • If there is not, this means that this user is the owner of that project.
  • If there is, this means this user got added later

Based on this information, the function is called differently.

Triggers

The function is executed at a specific time for a specific reason. You can manually execute this stored procedure, but that would not be very automated. That is why triggers are used.

A trigger calls a function if a specified event happens. Events are:

  • Insert
  • Update
  • Delete

Example: If a user is deleted from the auth.users list, you want to make sure this user is also not visible throughout your application. The deletion is the trigger, which then calls a stored procedure which executes a specific outcome.

Adding Supaboost Triggers and functions

Just like we have a table creation statement, for Supaboost we also have a Functions and Triggers creation statement.

In the Supaboost-V1.0-SQL-Scripts ZIP, open the file 2. Create functions and triggers, then copy the content.

Creating the Supabase Functions and Triggers

  1. Log in to your project
  2. Open the SQL Editor
  3. Then select + New query Create a new Supabase SQL query
  4. Select New blank query
  5. Open the Untitled query dropdown
  6. Select Rename query Rename the query
  7. Enter the name of the text document: 2. Create functions and triggers
  8. Paste the SQL statement of 2. Create functions and triggersin the Text Editor Enter the code into the Supabase SQL editor
  9. Then select Run CTRL
  10. If everything went well you should see the result Success. No rows returned

Checking the created Functions and Triggers

Now that the Functions and Triggers have been added to the application, we need to make sure that they are actually created.

If you go to Database > Functions, there should be 9 functions. In Database > Triggers, there should be a total of 12 triggers.

Triggers and Functions in Supabase

Working application

At this point you will have a working environment. There is only one small issue — there is no Row Level Security.

This means that any user will be able to see any other user's and other companies information.

Plus they will be able to update this information — let's fix that

Tables and RolesRow Level Security