Stop Crippling Your Database (2/6): Meet Triggers, SQL’s Callbacks

This is the second of a series of six articles on how to improve the way you interact with your DBMS, especially in the context of Rails development. The previous one was entitled “Notes on Sqlphobia and Database Agnosticism”. Enjoy!

Have you ever used ActiveRecord’s counter cache? It’s a feature of ActiveRecord associations that allows you to keep a count of the number of children in a column of the parent’s table.

For instance, suppose you have a Book model and a Review model. You can declare your associations like this:

When you specify the counter_cache option, ActiveRecord will save the number of reviews associated to a book in the reviews_count column of the books table. It will not only increment it when you a review is created, but also decrement it when it’s destroyed, or “move” it if you change the book to which a review belongs.

Don’t believe me? Let’s write some integration tests!

You can then order by reviews_count to get the ten most reviewed books:

This is dead-simple to implement, but it has some disadvantages:

• For every review you create or destroy, a minimum of 2 SQL queries will be executed: one to perform the operation, one to update the counter. If you change the book associated to a review, 3 SQL queries will be executed.
• It will not work if you use update_column or update your database from outside your Rails application, which is not that uncommon in complex scenarios.

So, we’re compromising performance and data integrity for the sake of simplicity.

A Step Down the Ladder

What if we could delegate all of this work to our database? Well, as it turns out, DBMS have had their own callbacks for a while. In PostgreSQL, they’re called triggers and they are supported as of 9.1.

There is a gem for Rails, called hair_trigger, which allows us to write triggers in a (mostly) database-independent way, for those of you still concerned about DB agnosticism. Most importantly, it also takes care of maintaining migrations and updating our triggers as we tweak them.

Let’s rewrite our counter cache with triggers:

The code’s fairly straightforward:

• when a review is INSERTed, we increment the reviews_count column of the related book;
• when the book_id column of a review is UPDATEd, we decrement the reviews_count of the old book and increment that of the new one;
• when a review is DELETEd, we decrement the reviews_count of the related book.

Now, we need to actually create the triggers in our DB:

$ rake db:generate_trigger_migration db:migrate

This will generate a migration for the new triggers and execute it.

What about the tests? Well, our previous tests were pretty high-level, so we won’t need to change anything in order to make them pass.

Some Concluding Notes

• hair_trigger has a very complete DSL for managing triggers, so I suggest you read its documentation and get a good grasp of it.
• You should go through the generated migrations in order to understand the full SQL code behind your triggers — who knows, one day you might have to write them by hand!
• When you use triggers, ActiveRecord has no way to know your records have changed. That means AR objects will contain old data until they are reloaded (hence the my usage of #reload in tests).
• Don’t overdo it: if you’re struggling with implementing your triggers, the reason might be you’re trying to delegate too much business logic to the DBMS. Callbacks do have a place — it’s just not where people usually put them.
• Though smaller than callbacks’ impact, triggers do have a performance overhead too. Try to avoid them in the first place. If you can’t, wield them fiercely but carefully.

This was the second of a series of six articles on how to improve the way you interact with your DBMS, especially in the context of Rails development. The next one will be about SQL types and constraints. Stay tuned!