Creating trigger functions with Supabase

One of the really interesting products, I've tried recently is supabase. It allows to create a backend for a react app with Postgresql as a database in a few minutes.

One of the most powerful features of Supabase is that it allows to use advanced Postgresql capabilities in a very easy way.

For instance, I needed to produce a table with aggregated statistics from another table, and update the aggregated stats on every data change. We often need this aggregated state of the data for analytics and charts.

To do this, we will use postgresql triggers in order to subscribe for particular events, like records update, insert and delete.

This capability is available in a Supabase -> Database section.

First, we need to create a function, that will fire off on any changes in the table. To do so, go to Database -> Functions -> Create a new function.

The return type of the function would be trigger.

The function I use creates an aggregated statistics from the funding table, resulting in amount of startup funding generated by country and industry type.

begin
  insert into public.investment_industry_country(industry_id, country_id, total_usd)
  SELECT 
  s.industry, 
  s.startup_hq, 
  SUM(f.amount_usd) AS ku
FROM public.startups s 
LEFT JOIN public.funding f 
  ON s.id = f.startup_id
GROUP BY 
  s.industry, 
  s.startup_hq

on conflict(industry_id, country_id) 
do update set total_usd = EXCLUDED.total_usd;
return null;
end;

Also, as this function should have access to our tables, we need to set it up as a security definer.

To make use of this function, we need to set up an actual trigger via Database -> Triggers -> Create a new trigger.

After we gave a name to the trigger, and assigned a table it will keep track of, we can select what types of events we are interested in. As I want an aggregated view, I selected all types of data changes - create, update and delete.

Then, we could select a function we've created earlier, which would run after table changes.

Once we click "confirm" the trigger is ready and the work of creating table for analytics is automated.

Here is how using extended supabase backend, we've created an aggregated table, that allows to produce some charts and statistics. Supabase makes it quite easy to utilise advanced Supabase capabilities and extend the database functionality.