Should I Use PostgreSQL Triggers in my Rails App?

Noah Over, Application Developer

Article Categories: #Code, #Back-end Engineering

Posted on

A brief overview of PostgreSQL Triggers, how you can use them in your Rails application, and whether or not that is a good idea.

If you are reading this article, you are probably hoping to learn something about PostgreSQL triggers and how they interact with Rails. Rest assured, I will get to that, but first a little about me. I come from a background in which we used PostgreSQL triggers on a regular basis. Then, I started working with Viget and was working regularly with Rails for the first time in a professional setting. That led me to slowly noticing the tendency to avoid SQL that often comes with being a Rails developer. It is not that we never use it. I personally have written raw SQL for several database migrations in my short time here. It is more so that we tend to keep functionality in the app rather than in the database. If you so desire however, there is one way to move some of that functionality over to the database and that is PostgreSQL triggers.

Introduction to PostgreSQL Triggers #

There does seem to be a decent number of people in the Rails community that are either completely unaware of PostgreSQL triggers or have limited experience with them. If you are already well-acquainted with PostgreSQL triggers, I recommend jumping ahead to here. If you are not, please read on.

What are PostgreSQL Triggers? #

PostgreSQL triggers are functions that are caused to run by an INSERT, UPDATE, DELETE, or TRUNCATE statement. There are two types of triggers to be familiar with in PostgreSQL:

  • Row-level triggers
  • Statement-level triggers

Row-level triggers run the function for every row affected by the statement while statement-level triggers run the function just once per statement. I am mainly going to focus on row-level triggers for the rest of this article since that is the one I have the most experience in using. In order to add a trigger to your table in raw SQL land, you must first write a function for the trigger to call and then create the trigger like in the below example:

CREATE OR REPLACE FUNCTION example_trigger_function()
    RETURNS trigger AS 
$$
BEGIN

    /* trigger functionality goes here */

RETURN NEW;
END
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER example_trigger
BEFORE INSERT OR UPDATE
ON example_table
FOR EACH ROW
EXECUTE PROCEDURE example_trigger_function();

In the example, the trigger function will run before an INSERT or an UPDATE statement that is called on the example_table. You can mix this up by replacing BEFORE with AFTER or INSTEAD OF or you change the statement types being called. You can also make this a statement-level trigger by replacing ROW with STATEMENT.

Common Uses for PostgreSQL Triggers #

There are numerous things you can do with PostgreSQL triggers as I am sure you can imagine. These are just a couple of the more common techniques I have used in my past experience.

Auditing

Basically, if you ever want to store the history of a table for whatever reason, a PostgreSQL trigger might be the way to go. You just have a row-level trigger that runs after any statements that change the rows on the table and insert a row into an audit table with all the same columns from your original table and a timestamp as to when this change was made and you will now be able to see any time any data in that table changes. Here's an example of this technique in action using a hypothetical Marvel superhero database where Marvel writers can go to check and update what superheroes are currently on what team and who is currently behind each mask:

CREATE OR REPLACE FUNCTION superhero_audit_trigger_function()
    RETURNS trigger AS 
$$
BEGIN

    INSERT INTO superheroes_audit (superhero_id, name, secret_identity, team_id, updated_by, updated_timestamp)
    VALUES (NEW.id, NEW.name, NEW.secret_identity, NEW.team_id, NEW.updated_by, NEW.updated_timestamp);

RETURN NEW;
END
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER superhero_audit_trigger
AFTER INSERT OR UPDATE
ON superheroes
FOR EACH ROW
EXECUTE PROCEDURE superhero_audit_trigger_function();

This example will keep track of all the data on the superheroes table so that way if a writer ever needed to check to see when a certain change was made to it or even who made that change, the writer can now do so. For example, if a writer went in to check who had decided to move Wolverine from the X-Men to the Avengers, they could just check the superheroes_audit table.

Populating Data

Another common technique for using triggers is to populate data. You can either do this by running INSERT or UPDATE statements on other tables or by setting the columns manually in the NEW row if the data you are populating is just a different column in that row and you are using a BEFORE trigger. Here is another example in the hypothetical Marvel superheroes database of both of these ways of populating data.

CREATE OR REPLACE FUNCTION superhero_data_population_trigger_function()
    RETURNS trigger AS 
$$
BEGIN

    NEW.updated_timestamp = current_timestamp;

    IF (TG_OP = "INSERT" OR (TG_OP = "UPDATE" AND NEW.team_id != OLD.team_id))
    THEN
        UPDATE teams
            SET superhero_count = superhero_count + 1
        WHERE id = NEW.team_id;
    END IF;

    IF (TG_OP = "DELETE" OR (TG_OP = "UPDATE" AND NEW.team_id != OLD.team_id))
    THEN
        UPDATE teams
            SET superhero_count = superhero_count - 1
        WHERE id = OLD.team_id;
    END IF;

RETURN NEW;
END
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER superhero_data_population_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON superheroes
FOR EACH ROW
EXECUTE PROCEDURE superhero_data_population_trigger_function();

In this example, we are automatically setting the updated_timestamp to the current time and updating the superhero_count on the teams table appropriately. That way the writers can keep track of how many superheroes are on the Avengers and X-Men at any given time. This was not meant to be a comprehensive lesson on PostgreSQL triggers but rather just a basic introduction of sorts, so if you would like more information, I would recommend checking out some of the resources I found helpful, which can be found here and here.

How to Use PostgreSQL Triggers with Rails #

It is now time to get into what we all came here for: the intersection between Rails and PostgreSQL triggers. If you read through that entire intro to PostgreSQL triggers, I applaud you. If you decided to skip it, welcome back! Time for me to teach you the how to add PostgreSQL triggers into your Rails code. In all honestly though, it is really not that complicated to pick up because there is already a gem that takes care of most of the heavy lifting for you. It is called hair_trigger. With this gem there are basically just two simple steps to adding PostgreSQL triggers to your Rails application. First, you need to add the trigger(s) to your model.

class Superhero < ApplicationRecord
    trigger.before(:insert) do
        "UPDATE teams SET superhero_count = superhero_count + 1 WHERE id = NEW.team_id;"
    end
    trigger.before(:update).of(:team_id) do
        <<-SQL
            UPDATE teams
                SET superhero_count = superhero_count + 1
            WHERE id = NEW.team_id;
            UPDATE teams
                SET superhero_count = superhero_count - 1
            WHERE id = OLD.team_id;
        SQL
    end 
    trigger.before(:delete) do 
        "UPDATE teams SET superhero_count = superhero_count - 1 WHERE id = OLD.team_id;"
    end
end

These triggers in the Superhero model do the same thing, except for setting the updated_timestamp, as the example for the Populating Data use case discussed earlier. Then, you just need to run 

rake db:generate_trigger_migration

This will generate your trigger migrations for you and you can now just treat it like you would any other migration. That's it. Pretty simple, right? You now have the full ability to include PostgreSQL triggers in your Rails application, but that still does not answer the question of whether or not you should.

Pros and Cons of PostgreSQL Triggers in Rails #

Let's start with the cons and then we can end on a positive note with the pros. I would say the biggest con I can think of for having PostgreSQL triggers in your Rails app is that most of the stuff you would typically use triggers for can already be done through ActiveRecord fairly simply. With ActiveRecord, when looking at the app code, it will be more clear where the changes in the data are coming from as well. With triggers, when you are searching the app for the code that is updating the data, you are going to forget they are there without extensive documentation. And if you are new to the project, you probably will not even realize they are there until you hit a problem with that data and need to ask someone where it is coming from. I know, in my experience with triggers in non-Rails applications, I would often be stuck looking through the code for where data was coming from for much too long before finally realizing it was coming from a database trigger. Another big downside with PostgreSQL triggers and Rails is that if you are updating data with the trigger, ActiveRecord objects will not automatically recognize the change and update, so you will have to manually reload them. That just adds an extra step that you would not have to worry about if you just handled this functionality on the app side to begin with.

Finally, let's discuss the pros. The really big positive is that it can help with performance overhead. In any sort of situation in which you need to update one table and then immediately update another table because of that initial update, will involve you making multiple calls to the database. If you are consistently making that second update after the first one, you can just put that functionality in the trigger and now you only have to make one call to the database. The only other pro that I could come up with is if you have multiple Rails apps all pointing to the same database. This probably will almost never come up for you because it is a bad idea that could be difficult to maintain if you are doing anything complex and would work better if you just had the multiple apps pointed at an API that accesses the singular database. But if you do decide to go down this terrible path, you could put some logic that would be consistent across the apps in a PostgreSQL trigger in that database. That was not a great positive but I am going to count it.

Conclusion #

Let me start by saying that I really enjoy working with PostgreSQL triggers and database stuff in general. I believe it is all fun to play around with and I honestly began my research hoping to come to a different conclusion that what I am about to present to you. So without further ado, I would say it is probably best to avoid PostgreSQL triggers where you can in your Rails app. I do not believe the slight improvement in performance is worth the multiple downsides that come with it and most other situations where it would be a good idea would probably be best to just avoid putting yourself in at all. However, if there is ever a situation in which you feel like you are duplicating code too much, you could use that slight performance bump, or there is just a really complex data problem that you believe a PostgreSQL trigger could solve, go ahead and give it a try. I hope that you learned something here that would at least make that process easier for you.

Noah Over

Noah is a Developer in our Durham, NC office. He’s passionate about writing Ruby and working with databases to overcome problems.

More articles by Noah

Related Articles