Using Supabase Triggers To Effortlessly Update Your Database

Intro

I originally come from a frontend background, and databases in general used to scare me a lot - until I discovered Supabase! As a platform, it acts as a frictionless way to get stuck into using postgres (and comes with a ton more useful features such as auth, storage and realtime!) I'm a firm believer that the best way to learn is to build something people would actually want to use, so you start to encounter problems naturally.

I wrote this blog post a while ago and didn't get round to publishing it. Then I joined Supabase as a support engineer. Perfect excuse!

The Problem

My latest project is a fun platform called Stadiarank - a tool to discover, rate and review the greatest sports experiences in the world.

As part of the experience, you can see the average review of a stadium, which is great! However, when a user submits a new review we need two things to happen:

1) The total review count of that stadium needs to be incremented

2) The average review of that stadium should be updated to take into account the score the latest review has added.

Enter Trigger Functions!

Creating the Function

First things first, we need to write the function that will fire to update the review count and calculate the new average review. It's not too complicated, even if you're a little scared of postgres.

BEGIN
    -- Force error logging if anything fails
    BEGIN
        -- Log initial state
        RAISE LOG 'Trigger fired for Stadium ID: %', NEW.stadium_id;
        
        -- Log stadium details BEFORE the update
        RAISE LOG 'Before UPDATE -> review_count: %, average_review: %',
            (SELECT review_count FROM stadiums WHERE id = NEW.stadium_id),
            (SELECT average_review FROM stadiums WHERE id = NEW.stadium_id);

        -- Attempt the UPDATE
        UPDATE stadiums
        SET review_count = review_count + 1,
            average_review = 
                CASE 
                    WHEN review_count = 0 THEN NEW.total_rating
                    ELSE (average_review * review_count + NEW.total_rating) / (review_count + 1)
                END
        WHERE id = NEW.stadium_id;

        -- Log stadium details AFTER the update
        RAISE LOG 'After UPDATE -> review_count: %, average_review: %',
            (SELECT review_count FROM stadiums WHERE id = NEW.stadium_id),
            (SELECT average_review FROM stadiums WHERE id = NEW.stadium_id);

    EXCEPTION WHEN OTHERS THEN
        -- Log any error that occurs
        RAISE LOG 'Error in trigger: %', SQLERRM;
    END;

    RETURN NEW;
END;

First things first, I've set up extensive logging for debugging purposes.

After that, the first logical step is to increment the stadium review count by one - regardless.

If it is that stadium's first review, then it's super easy - because that review value is now the total cumilative review value. If it isn't the first review, then all we have to do is calculate the new cumilative review value and divide that by the number of reviews to create the new average rating and assign it to that stadium.

Setting Up The Trigger

Our function is set up - great! However, it's no good defining it without using it! That's where triggers come in, where we monitor for events and fire a function based on that event.

In the Supabase dashboard, navigate to database > triggers. We need to find the table we're looking to watch for changes on. In this case, it's "stadium_reviews".

Fill out the form as follows to create the trigger - we want to fire it on insert events (a new review being created), and after the event completes, meaning that the new review is in the table.

And just like that, your function runs when a new row is inserted into a table you specifiy. How good is that?!

Some Caveats

• This is not a very sensitive project in terms of small number fluctuations.

• This is currently not a high traffic project so I have not implemented any locks to prevent concurrency issues!

• The logging is a bit extensive, but in my view more logging is better than less logging if you're newer to a concept - just make sure the logs are clear!