Postgresql Enum Migrator - from integers to PG enum values

1_pg_enum_migrator.rb

# frozen_string_literal: true

class PgEnumMigrator
  def initialize(migration:, table:, column:, enum_name:, mapping:, new_default: nil, old_default: nil)
    @migration = migration
    @table = table
    @column = column
    @enum_name = enum_name
    @mapping = mapping
    @new_default = new_default
    @old_default = old_default
  end

  def call
    migration.reversible do |migrate|
      migrate.up do
        migration.execute "CREATE TYPE #{enum_name} AS ENUM (#{values_list})"
      end

      migrate.down do
        migration.execute "DROP TYPE #{enum_name}"
      end
    end

    migration.reversible do |migrate|
      migrate.up do
        migration.execute <<~SQL
          ALTER TABLE #{table}
            ALTER COLUMN #{column} DROP DEFAULT,
            ALTER COLUMN #{column} SET DATA TYPE #{enum_name} USING CASE
              #{case_int_to_type}
            END;
        SQL

        if new_default.present?
          migration.execute <<~SQL
            ALTER TABLE #{table}
              ALTER COLUMN #{column} SET DEFAULT #{quote(new_default)};
          SQL
        end
      end

      migrate.down do
        migration.execute <<~SQL
          ALTER table #{table}
            ALTER COLUMN #{column} DROP DEFAULT,
            ALTER COLUMN #{column} SET DATA TYPE integer USING CASE
              #{case_type_to_int}
            END;
        SQL

        if old_default.present?
          migration.execute <<~SQL
            ALTER TABLE #{table}
              ALTER COLUMN #{column} SET DEFAULT #{old_default};
          SQL
        end
      end
    end
  end

  private

  attr_reader :migration, :table, :column, :enum_name, :mapping,
              :new_default, :old_default

  def values_list
    mapping.keys.map { |value| quote(value) }.join(", ")
  end

  def case_int_to_type
    mapping.map do |value, int|
      "when #{column} = #{int} then #{quote(value)}::#{enum_name}"
    end.join("\n")
  end

  def case_type_to_int
    mapping.map do |value, int|
      "when #{column} = #{quote(value)} then #{int}"
    end.join("\n")
  end

  def quote(value)
    ActiveRecord::Base.connection.quote(value)
  end
end

2_migration.rb

require_relative "../../lib/pg_enum_migrator"

class SwitchToPgEnums < ActiveRecord::Migration[6.0]
  def change
    PgEnumMigrator.new(
      migration: self,
      table: "clients",
      column: "rate_type",
      enum_name: "client_rate_type",
      mapping: { hourly: 0, weekly: 1 } # or via model: InlinedClients.rate_types
    ).call
  end
end

3_credit.md

Riffing on this awesome post by Searls @ Test Double: https://blog.testdouble.com/posts/2019-06-03-enumerate-your-enums

Dear Reader, you may also like HasStringEnum 👀