Use Postgres Enum Type in Rails

Rails provides enum for ActiveRecord which is quite handy but I recently found out that we could add Postgres enum type to guarantee data consistency at database level.

Enum in Rails has few weaknesses:

• If we use number for enum values, SQL queries look meaningless, we have to check the code to know the meaning of each enum value.
• ActiveRecord models will validate enum values, however at database level, we could still insert unsupported values which most of the time is not a problem but in some cases it can introduce data inconsistencies.

Postgres enum type addresses those issues however it has its own problems:

• Rails doesn't support this natively, we have to execute SQL queries regarding create/update/remove enum types.
• Cannot work with db/schema.rb. We have to use SQL schema format in order to use this feature.
• Changing enum types in Postgres is painful.

With those trade offs, Rails enum will be fine most of the time, but in case you need to use Postgres enum, this is the guide for it.

First we need to config ActiveRecord schema format to sql

config.active_record.schema_format = :sql

To create a Postgres enum type:

class AddStatusToProducts < ActiveRecord::Migration[6.1]
  def up
    execute <<-SQL
      CREATE TYPE product_statuses AS ENUM ('on_shelf', 'shipped');
    SQL

    add_column :products, :status, :product_statuses
  end

  def down
    remove_column :products, :status

    execute <<-SQL
      DROP TYPE product_statuses;
    SQL
  end
end

In Product model:

class Product < ApplicationRecord
  enum status: { on_shelf: 'on_shelf', shipped: 'shipped' }
  validates :status, inclusion: { in: statuses.keys }
end

After running the migration, we could see the following lines in db/structure.sql

CREATE TYPE public.product_statuses AS ENUM (
    'on_shelf',
    'shipped'
);

This is simple enough, but what if we want to modify enum values ? You will encounter full of surprises.

Suppose we want to add a new enum value named return. Here is the code that works:

class AddReturnedToProductStatusesEnumType < ActiveRecord::Migration[6.1]
  disable_ddl_transaction!

  def up
    execute <<-SQL
      ALTER TYPE product_statuses ADD VALUE 'returned';
    SQL
  end

  def down
    execute <<-SQL
      ALTER TYPE product_statuses RENAME TO product_statuses_old;
      CREATE TYPE product_statuses AS ENUM ('on_shelf', 'shipped');

      ALTER TABLE products
      ALTER COLUMN status
      TYPE product_statuses
      USING status::text::product_statuses;

      DROP TYPE product_statuses_old;
    SQL
  end
end

The SQL queries for adding/removing enum values doesn't inside transaction hence we need this line in the migration:

disable_ddl_transaction!

And while adding a new enum value is simple, removing a enum value is not. In fact it is not feasible to remove a value from an enum type. Instead we have to rename the current enum, create a new enum type with the current enum original name with all the values except the one we want to remove, then alter related tables to use this new enum and finally drop old enum.

Please note that only Postgres 9.6+ supports enumerated types and the SQL queries depend on Postgres version. You could read more about Postgres enum type from their documentation at https://www.postgresql.org/docs/13/datatype-enum.html