Helpers to work with Enum fields in Postgres and ActiveRecord. Companion code to http://blog.theamazingrando.com/posts/postgres-enums-in-rails.html

1.migration_utils.rb

# frozen_string_literal: true

module MigrationUtils
  module CreateEnum
    # :reek:TooManyStatements :reek:NestedIterators
    def create_enum(name, values)
      reversible do |dir|
        dir.up do
          say_with_time "create_enum(:#{name})" do
            suppress_messages do
              execute "CREATE TYPE #{name} AS ENUM (#{values.map{ |v| quote(v) }.join(', ')})"
            end
          end
        end

        dir.down do
          say_with_time "drop_enum(:#{name})" do
            execute "DROP TYPE #{name}"
          end
        end
      end
    end
  end

  module UpdateEnum
    def update_enum(table:, column:, enum_type:, old_value:, new_value:)
      new_enumlabels = new_enumlabels(enum_type, old_value, new_value)
      ActiveRecord::Base.connection.execute <<-SQL.squish
        ALTER TYPE #{enum_type} ADD VALUE IF NOT EXISTS '#{new_value}';
      SQL
      ActiveRecord::Base.connection.execute <<-SQL.squish
        ALTER TYPE #{enum_type} RENAME TO old_#{enum_type};
        CREATE TYPE #{enum_type} AS ENUM (#{new_enumlabels});
        UPDATE #{table} SET #{column} = '#{new_value}' WHERE #{table}.#{column} = '#{old_value}';
        ALTER TABLE #{table} ALTER COLUMN #{column} TYPE #{enum_type} USING #{column}::text::#{enum_type};
        DROP TYPE old_#{enum_type};
      SQL
    end

    # Fetch the enum labels from the data base
    def new_enumlabels(enum_type, old_value, new_value)
      enumlabels = ActiveRecord::Base.connection.execute <<-SQL.squish
        SELECT enumlabel from pg_enum WHERE enumtypid=(
          SELECT oid FROM pg_type WHERE typname='#{enum_type}'
        ) ORDER BY enumsortorder;
      SQL
      _enumlabels = enumlabels.map { |e| "'#{e['enumlabel']}'" }
      new_labels = _enumlabels.to_a - ["'#{old_value}'"] + ["'#{new_value}'"]
      new_labels.uniq.join(", ").chomp(", ")
    end
  end

  module RemoveEnumValue
    def remove_enum_value(table:, column:, enum_type:, value:)
      reversible do |dir|
        dir.up do
          say_with_time "remove_enum_value(#{value})" do
            suppress_messages do
              updated_enum_labels = update_enum_labels(enum_type, value)
              ActiveRecord::Base.connection.execute <<-SQL.squish
                DELETE FROM  #{table} WHERE #{column} = '#{value}';
                ALTER TYPE #{enum_type} RENAME TO old_#{enum_type};
                CREATE TYPE #{enum_type} AS ENUM (#{updated_enum_labels});
                ALTER TABLE #{table} ALTER COLUMN #{column} TYPE #{enum_type} USING #{column}::text::#{enum_type};
                DROP TYPE old_#{enum_type};
              SQL
            end
          end
        end

        dir.down do
          say_with_time "add_enum_value(#{value})" do
            execute "ALTER TYPE #{enum_type} ADD VALUE '#{value}'"
          end
        end
      end
    end

    # Fetch the enum labels from the data base
    def update_enum_labels(enum_type, value)
      enumlabels = ActiveRecord::Base.connection.select_values <<-SQL.squish
        SELECT enumlabel from pg_enum WHERE enumtypid=(
          SELECT oid FROM pg_type WHERE typname='#{enum_type}'
        ) ORDER BY enumsortorder;
      SQL
      enumlabels.without(value.to_s).map{ |enum| "'#{enum}'" }.join(", ")
    end
  end
end

2.example_migration.rb

class CreateMessages < ActiveRecord::Migration[6.0]
  include MigrationUtils::CreateEnum
  
  change do
    create_enum :message_state_type, %w[queued dispatched delivered]

    create_table :messages do |t|
      t.column :state, :message_state_type, null: false
    end
  end
end

3.application_record.rb

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  # Provides a bit of syntactic sugar around Rails' built-in enums to map
  # them to postgres enums which expect string values instead of integer
  # values. Basically this saves you from having to pass in:
  # {
  #   foo: "foo",
  #   bar: "bar",
  #   baz: "baz"
  # }
  # to the Rails enum DSL method.
  def self.pg_enum(attribute, values, options = {})
    enum({ attribute => Hash[values.map{ |value| [value.to_sym, value.to_s] }] }.merge(options))
  end
end

4.example_model.rb

class Message
  enum state: %i[ queued dispatched delivered ]
end