Extensions for the migration DSL

[svoop]

I'd like to use the same trigger on most tables to automatically set updated_at when rows are modified. To keep things tidy, a custom addition to the migration DSL like automate_updated_at :mytable might come in handy.

Extending PORO style is the simple approach, but that won't work in change do (can't guess the down action), so I'm wondering whether there's a better way to tackle this?

[jeremyevans]

A loop in up and down seems the simplest way. change only handles trivial migrations, for anything complex it is expected you'll need both up and down.

[svoop]

It looks as follows now:

# lib/sequel/extensions/updated_at.rb

module Sequel
  class Migration
    module UpdatedAt

      def create_updated_at_trigger_function
        create_function :set_updated_at, <<-SQL, language: :plpgsql, returns: :trigger
          BEGIN
            NEW.updated_at := CURRENT_TIMESTAMP;
            RETURN NEW;
          END;
        SQL
      end

      def drop_updated_at_trigger_function
        drop_function :set_updated_at
      end

      def create_updated_at_trigger(table)
        create_trigger(table, "#{table}_updated_at_trigger", :set_updated_at,
          events: :update,
          each_row: true,
          when: { Sequel[:new][:updated_at] => Sequel[:old][:updated_at] }
        )
      end

      def drop_updated_at_trigger(table)
        drop_trigger(table, "#{table}_updated_at_trigger")
      end

    end
  end
end

And then the migrations (ROM which just delegates to Sequel):

# 00000000000000_create_updated_at_trigger_function.rb

Sequel.extension :updated_at

ROM::SQL.migration do
  up do
    extend Sequel::Migration::UpdatedAt
    create_updated_at_trigger_function
  end

  down do
    extend Sequel::Migration::UpdatedAt
    drop_updated_at_trigger_function
  end
end

# 00000000000000_create_users.rb

Sequel.extension :updated_at

ROM::SQL.migration do
  up do
    create_table :users do
      column :id, :uuid, primary_key: true, default: Sequel.lit('uuidv7()')
      column :updated_at, :timestamptz, default: Sequel::CURRENT_TIMESTAMP

      column :email, :citext, null: false, unique: true, index: true
      column :mobile_phone, :text, unique: true
      column :first_name, :text
      column :last_name, :text
    end

    extend Sequel::Migration::UpdatedAt
    create_updated_at_trigger :users
  end

  down do
    drop_table :users
  end
end

I get that for support of change do, a full fledged migration extensions logic would be necessary which includes defining up and down methods in the extension. Probably too rare a use case for that much code.

But what's also bugging me a little is the explicit extend needed. After looking at the code, Sequel.extension only requires, nothing more. So unless I'm missing something here, I guess that's as nice as it gets. 😄

[jeremyevans]

You may want to write it as a Database extension. See https://github.com/jeremyevans/sequel_postgresql_triggers/blob/master/lib/sequel/extensions/pg_triggers.rb for a similar approach. Alternatively, you could use the sequel_postgresql_triggers gem instead of your code, as it implements what you need already.

[svoop]

I try to keep the number of dependencies low, but since you're the author of the gem, that's not an issue. Thanks for the hint!!