[Awesome Ruby Gem] Use hairtrigger gem to create and manage database triggers in Rails

Posted on Edited on In , , Views: Word count in article: 7.8k Reading time ≈ 7 mins.

HairTrigger

HairTrigger lets you create and manage database triggers in a concise, db-agnostic, Rails-y way. You declare triggers right in your models in Ruby, and a simple rake task does all the dirty work for you.

Installation

You can install it as a gem:

1
$ gem install hairtrigger

or add it into a Gemfile (Bundler):

1
2
3
# Gemfile

gem 'hairtrigger', `0.2.24'

Then, run bundle install.

1
$ bundle install

Usage

Models

Declare triggers in your models and use a rake task to auto-generate the appropriate migration. For example:

1
2
3
4
5
6
7
8
9
class AccountUser < ActiveRecord::Base
  trigger.after(:insert) do
    "UPDATE accounts SET user_count = user_count + 1 WHERE id = NEW.account_id;"
  end

  trigger.after(:update).of(:name) do
    "INSERT INTO user_changes(id, name) VALUES(NEW.id, NEW.name);"
  end
end

and then:

1
$ rake db:generate_trigger_migration

This will create a db-agnostic migration for the trigger that mirrors the model declaration. The end result in MySQL will be something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER account_users_after_insert_row_tr AFTER INSERT ON account_users
FOR EACH ROW
BEGIN
    UPDATE accounts SET user_count = user_count + 1 WHERE id = NEW.account_id;
END;

CREATE TRIGGER account_users_after_update_on_name_row_tr AFTER UPDATE ON account_users
FOR EACH ROW
BEGIN
    IF NEW.name <> OLD.name OR (NEW.name IS NULL) <> (OLD.name IS NULL) THEN
        INSERT INTO user_changes(id, name) VALUES(NEW.id, NEW.name);
    END IF;
END;

Note that these auto-generated create_trigger statements in the migration contain the :generated => true option, indicating that they were created from the model definition. This is important, as the rake task will also generate appropriate drop/create statements for any model triggers that get removed or updated. It does this by diffing the current model trigger declarations and any auto-generated triggers in schema.rb (and subsequent migrations).

Chainable Methods

Triggers are built by chaining several methods together, ending in a block that specifies the SQL to be run when the trigger fires. Supported methods include:

name(trigger_name)

Optional, inferred from other calls.

on(table_name)

Ignored in models, required in migrations.

for_each(item)

Defaults to :row, PostgreSQL allows :statement.

before(*events)

Shorthand for timing(:before).events(*events).

after(*events)

Shorthand for timing(:after).events(*events).

where(conditions)

Optional, SQL snippet limiting when the trigger will fire. Supports delayed interpolation of variables.

of(*columns)

Only fire the update trigger if at least one of the columns is specified in the statement. Platforms that support it use a native OF clause, others will have an inferred IF … statement in the trigger body. Note the former will fire even if the column’s value hasn’t changed; the latter will not.

security(user)

Permissions/role to check when calling trigger. PostgreSQL supports :invoker (default) and :definer, MySQL supports :definer (default) and arbitrary users (syntax: ‘user’@‘host’).

timing(timing)

Required (but may be satisified by before/after). Possible values are :before/:after.

events(*events)

Required (but may be satisified by before/after). Possible values are :insert/:update/:delete/:truncate. MySQL/SQLite only support one action per trigger, and don’t support :truncate.

nowrap(flag = true)

PostgreSQL-specific option to prevent the trigger action from being wrapped in a CREATE FUNCTION. This is useful for executing existing triggers/functions directly, but is not compatible with the security setting nor can it be used with pre-9.0 PostgreSQL when supplying a where condition.

Example: trigger.after(:update).nowrap { "tsvector_update_trigger(...)" }

declare

PostgreSQL-specific option for declaring variables for use in the trigger function. Declarations should be separated by semicolons, e.g.

1
2
3
4
5
6
7
trigger.after(:insert).declare("user_type text; status text") do
  <<-SQL
    IF (NEW.account_id = 1 OR NEW.email LIKE '%company.com') THEN
      user_type := 'employee';
    ELSIF ...
  SQL
end

all

Noop, useful for trigger groups (see below).

Trigger Groups

Trigger groups allow you to use a slightly more concise notation if you have several triggers that fire on a given model. This is also important for MySQL, since it does not support multiple triggers on a table for the same action and timing. For example:

1
2
3
4
5
6
7
8
9
10
11
trigger.after(:update) do |t|
  t.all do # every row
    # some sql
  end
  t.of("foo") do
    # some more sql
  end
  t.where("OLD.bar != NEW.bar AND NEW.bar != 'lol'") do
    # some other sql
  end
end

For MySQL, this will just create a single trigger with conditional logic (since it doesn’t support multiple triggers). PostgreSQL and SQLite will have distinct triggers. This same notation is also used within trigger migrations. MySQL does not currently support nested trigger groups.

Because of these differences in how the triggers are created, take care when setting the name for triggers or groups. In other words, PostgreSQL/SQLite will use the names specified on the individual triggers; MySQL will use the name specified on the group.

Database-specific trigger bodies

Although HairTrigger aims to be totally db-agnostic, at times you do need a little more control over the body of the trigger. You can tailor it for specific databases by returning a hash rather than a string. Make sure to set a :default value if you aren’t explicitly specifying all of them.

For example, MySQL generally performs poorly with subselects in UPDATE statements, and it has its own proprietary syntax for multi-table UPDATEs. So you might do something like the following:

1
2
3
4
5
6
7
8
9
10
11
trigger.after(:insert) do
  {:default => <<-DEFAULT_SQL, :mysql => <<-MYSQL}

  UPDATE users SET item_count = item_count + 1
  WHERE id IN (SELECT user_id FROM buckets WHERE id = NEW.bucket_id)
  DEFAULT_SQL

  UPDATE users, buckets SET item_count = item_count + 1
  WHERE users.id = user_id AND buckets.id = NEW.bucket_id
  MYSQL
end

Manual Migrations

You can also manage triggers manually in your migrations via create_trigger and drop_trigger. They are a little more verbose than model triggers, and they can be more work since you need to figure out the up/down create/drop logic when you change things. A sample trigger:

1
2
3
create_trigger(:compatibility => 1).on(:users).after(:insert) do
  "UPDATE accounts SET user_count = user_count + 1 WHERE id = NEW.account_id;"
end

Because create_trigger may drop an existing trigger of the same name, you need to actually implement up/down methods in your migration (rather than change) so that it does the right thing when rolling back.

Manual triggers and :compatibility

As bugs are fixed and features are implemented in HairTrigger, it’s possible that the generated trigger SQL will change (this has only happened once so far). If you upgrade to a newer version of HairTrigger, it needs a way of knowing which previous version generated the original trigger. You only need to worry about this for manual trigger migrations, as the model ones automatically take care of this. For your manual triggers you can either:

  • pass :compatibility => x to your create_trigger statement, where x is whatever HairTrigger::Builder.compatibility is (1 for this version).

  • set HairTrigger::Builder.base_compatibility = x in an initializer, where x is whatever HairTrigger::Builder.compatibility is. This is like doing the first option on every create_trigger. Note that once the compatibility changes, you’ll need to set :compatibility on new triggers (unless you just redo all your triggers and bump the base_compatibility).

If you upgrade to a newer version of HairTrigger and see that the SQL compatibility has changed, you’ll need to set the appropriate compatibility on any new triggers that you create.

rake db:schema:dump

HairTrigger hooks into rake db:schema:dump (and rake tasks that call it) to make it trigger-aware. A newly generated schema.rb will contain:

create_trigger statements for any database triggers that exactly match a create_trigger statement in an applied migration or in the previous schema.rb file. this includes both generated and manual create_trigger calls.
adapter-specific execute(‘CREATE TRIGGER…’) statements for any unmatched database triggers.

As long as you don’t delete old migrations and schema.rb prior to running rake db:schema:dump, the result should be what you expect (and portable). If you have deleted all trigger migrations, you can regenerate a new baseline for model triggers via rake db:generate_trigger_migration.

References

[1] GitHub - jenseng/hair_trigger: Happy database triggers for ActiveRecord - https://github.com/jenseng/hair_trigger

[2] hairtrigger | RubyGems.org | your community gem host - https://rubygems.org/gems/hairtrigger

[3] Active Record Migrations — Ruby on Rails Guides - https://guides.rubyonrails.org/active_record_migrations.html#types-of-schema-dumps