timestamps_example.rb

Sequel.migration do
  up do
    create_table(:users) do
      primary_key :id

      String :first_name, null: false, limit: 100
      String :last_name, null: false, limit: 100
      String :middle_name, null: true, limit: 100
      String :email, null: false, limit: 500
      String :role, null: true, limit: 50
      String :authentication_token, limit: 200
      String :password_digest, limit: 1000
      String :comment, limit: 5000
      Boolean :active, null: false

      Time :created_at, null: false, default: Sequel::CURRENT_TIMESTAMP
      Time :updated_at, null: false, default: Sequel::CURRENT_TIMESTAMP

      index :email, unique: true, where: 'active'
      index :authentication_token, unique: true, where: 'active'
    end

    run <<~SQL
      CREATE OR REPLACE FUNCTION set_updated_at_column() RETURNS TRIGGER AS $$
        BEGIN
          new.updated_at = CURRENT_TIMESTAMP;
          RETURN new;
        END;
      $$ language 'plpgsql';

      CREATE TRIGGER set_updated_at_on_users
      BEFORE UPDATE ON users FOR EACH ROW
      EXECUTE PROCEDURE set_updated_at_column();
    SQL
  end

  down do
    drop_table(:users)

    run(<<~SQL)
      drop function set_updated_at_column();
    SQL
  end
end