soundcloud/lhm — pull request #81 by orien (2014-11-24 07:20:36+00:00)
Title: Migrator support for `add_trigger` and `remove_trigger`

Here lies a proposal to support adding and removing triggers via LHM.

This will support complex migrations/backfills with less table copies. Here's my use case:

I have a table `users` with many millions of rows upon which I need to introduce the datetime column `grokked_at`. This column should be constrained not null and for legacy rows (before we populate the column via the application) the value should be that of the `created_at` column.

Currently, to perform this online I would need to:

1. Add the column _without_ `NOT NULL` constraint via LHM:

```ruby
Lhm.change_table :users do |users|
users.add_column :grokked_at, "datetime"
end
```

2. Deploy code that populates this new column.

3. Backfill legacy rows with values from `created_at`.

4. Change the column to introduce constraint:

```ruby
Lhm.change_table :users do |users|
users.change_column :grokked_at, "datetime NOT NULL"
end
```

This is great, but we can do better. Compare to performing the backfill with a trigger:

1. Add the column _with_ `NOT NULL` constraint, and the backfilling trigger via LHM:

```ruby
Lhm.change_table :users do |users|
users.add_column :grokked_at, "datetime NOT NULL"
users.add_trigger :grokked_at_backfill_trigger, :before, :insert, "SET NEW.grokked_at = NEW.created_at;"
end
```

2. Deploy code that populates this new column.

3. Remove the trigger from database via normal migration.

Only one table copy compared to two. Less development effort. Less production deploys.

Triggers would be a great tool to work on our zero-downtime system migrations! :grinning: