Optimize Rails enums with Postgres: A guide

Some people say “e-noom”. Others say “e-numb”. But for some reason, Rails calls its enums “integer”.

That decision—dating back to the feature’s introduction in Rails 4.1—has caused no end of problems for developers and teams that aren’t hyper-vigilant about managing the impedance mismatch between ActiveRecord objects that marshal enum attributes as strings and the underlying database schema that represents enum columns as integers.

If you’ve been using Rails enum attributes and have never had an issue with them, then feel free to stop reading and go about your day. But if you’ve ever run into a tricky gotcha when trying to populate an enum field in a test fixture or when submitting a form, then this post is for you. (Oh, and if you’ve heard horror stories about enums and thus avoided them until now, this post can be for you too.)

Postgres, the best database

As has been the case for several years now, the prescription to today’s ActiveRecord frustration is, yet again, a Postgres feature most people don’t know about.

Going back until at least version 8, Postgres has offered its own mechanism for declaring enumerated types as string constants:

create type rate_type as enum ('hourly', 'weekly');

When you set or read an enum column over a database adapter like ActiveRecord, "hourly" and "weekly" values are represented as strings. As luck would have it, shortly after enum’s initial release, Rails added support for enum attributes backed by strings. Therefore, there’s nothing preventing us from defining a Postgres enum column and declaring a matching ActiveRecord enum attribute by explicitly setting its string value counterparts.

The approach outlined below has several best-of-both-worlds-y benefits:

• Postgres will store the values internally as constants, requiring just 4 bytes each
• Postgres will validate that only valid enum values are set, and correctly error if you try to send it a string that doesn’t match a declared enum value
• Postgres can compare values held in columns of the same enum type across multiple tables, which is quite neat
• ActiveRecord will still provide convenience methods for enum attributes, like rate_types, weekly? and hourly!
• Best of all, there will no longer be any awkward translation between strings and ordinal integers, so an entire category of gotchas can be safely erased from our collective memories

So, let’s dive into how to set up ActiveRecord enum attributes backed by Postgres enum columns, for each of a few different scenarios.

Adding an enum to an app using schema.rb

If your app’s migration tasks generate a db/schema.rb file (as is the default), you probably want them to continue to do so. There’s just one problem to deal with first.

See, Rails migrations don’t offer native support for creating enum types, so any enum declarations and any tables containing enum type columns present in your migrations will not be dumped without so much as an error or a warning. (That seemed suboptimal, so I opened this Rails issue for discussion.)

In fact, if you start using PG enums in your migrations, you’ll see entire tables in your schema.rb file replaced with:

# Could not dump table "projects" because of following StandardError
#   Unknown type 'rate_type' for column 'rate_type'

This is not ideal. And it might be weeks or months before anyone notices that the schema.rb is now broken.

Fear not! There is a solution! The activerecord-postgres_enum gem adds awareness of PG enums to Rails migrations, and therefore allows you to write tidier migrations while continuing to be able to rely on your schema.rb file.

With that gem installed, you can write a pretty straightforward migration like this:

class AddDefaultRateTypeToClients < ActiveRecord::Migration[5.2]
  def change
    create_enum :rate_type, ["hourly", "weekly"]

    change_table :clients do |t|
      t.enum :default_rate_type, enum_name: :rate_type, null: false, default: "hourly"
    end
  end
end

And a model attribute like this:

class Client < ActiveRecord::Base
  enum default_rate_type: {
    hourly: "hourly",
    weekly: "weekly",
  }
end

And you’re off to the races! As best as I can tell from a few days of working with them, everything basically just works, and all the edge cases I’d spent the last few years dancing around seem to have gone away.

Adding an enum to an app using structure.sql

Of course, not every app uses the default schema.rb file. If your app’s migrations do anything that ActiveRecord migrations don’t support, someone will have likely added a configuration like this to your config/application.rb at some point:

config.active_record.schema_format = :sql

Which tells Rails to persist a raw db/structure.sql dump instead of the more readable and portable db/schema.rb file.

If you’re already using structure.sql, adding an extra gem just for cuter migration semantics is probably not worth the expense. But that’s okay, because you can write a migration like this without it:

class AddDefaultRateTypeToClients < ActiveRecord::Migration[5.2]
  def change
    reversible do |migrate|
      migrate.up do
        execute "create type rate_type as enum ('hourly', 'weekly')"
      end
      migrate.down do
        execute "drop type rate_type"
      end
    end

    change_table :clients do |t|
      t.column :default_rate_type, :rate_type, null: false, default: "weekly"
    end
  end
end

(If you’re not familiar with reversible, check out the Rails guide on it.)

With this approach, apart from losing the convenience methods create_enum and t.enum, everything else is the same as if we’d used the activerecord-postgres_enum gem.

Converting existing columns from integers to enums

Now, the real fun comes when attempting to migrate your existing ActiveRecord enum attributes that are backed by integer columns to a Postgres enum type.

The first step is understanding which numeric values need to be converted to which strings. If your enum attribute is already defined by setting explicit integers like this:

class Project < ApplicationRecord
  enum rate_type: {
    hourly: 0,
    weekly: 1,
  }
end

Then this will be easy. You know that hourly and weekly will have been stored as 0 and 1, respectively, across every environment.

However, if the enum is defined using a simple array, the ordinal values will be derived implicitly by ActiveRecord:

class Project < ApplicationRecord
  enum rate_type: [:hourly, :weekly]
end

And you may want to be a little more careful. Though I’m 99% sure there aren’t any versions of Rails or any Postgres adapters that would transliterate this to anything other than the same 0 and 1, it’s easy enough to ask Rails for the value when converting the column that we can just as well do that in our migration.

In either case, your migration will need to drop down into executing SQL, even if you’ve adopted the aforementioned enum gem. Below, we’ll also conservatively ask ActiveRecord for the enum’s integer values rather than hard-code any magic numbers. Finally, we’ll locally redefine the Project model in order to future-proof the migration against changes in app/ later. (If you haven’t added good_migrations to your Gemfile yet, you really should!)

class EnumerateProjectRateType < ActiveRecord::Migration[5.2]
  # 1.) locally define the model you're working with
  class Project < ActiveRecord::Base
    enum rate_type: [:hourly, :weekly]
  end
  def change
    # 2.) create the enum type if it hasn't been created
    reversible do |migrate|
      migrate.up do
        execute "create type rate_type as enum ('hourly', 'weekly')"
      end
      migrate.down do
        execute "drop type rate_type"
      end
    end

    # 3.) ask Rails for the integer equivalents of our two enum values
    hourly_int = Project.rate_types[:hourly]
    weekly_int = Project.rate_types[:weekly]

    # 4.) change the column type and migrate its data up and down
    reversible do |migrate|
      migrate.up do
        execute <<~SQL
          alter table projects
            alter column rate_type drop default,
            alter column rate_type set data type rate_type using case
              when rate_type = #{hourly_int} then 'hourly'::rate_type
              when rate_type = #{weekly_int} then 'weekly'::rate_type
            end,
            alter column rate_type set default 'hourly';
        SQL
      end
      migrate.down do
        execute <<~SQL
          alter table projects
            alter column rate_type drop default,
            alter column rate_type set data type integer using case
              when rate_type = 'hourly' then #{hourly_int}
              when rate_type = 'weekly' then #{weekly_int}
            end,
            alter column rate_type set default #{hourly_int};
        SQL
      end
    end
  end
end

And then, finally, update the model:

class Project < ActiveRecord::Base
  enum rate_type: {
    hourly: "hourly",
    weekly: "weekly",
  }
end

And that’s pretty much all you should need to do, unless you’re hard-coding integer values somewhere or otherwise have application code that depended on the old integer values. (A good idea might be to grep for any methods that would have returned those integers, like Project.rate_types).

Conclusion

This post didn’t cover what enums are, whether they’re worth using, or why you should care, but hopefully it presents a useful path forward for anyone on Postgres already using (or hoping to use) ActiveRecord enum attributes, but not yet familiar with Postgres enum types.

Happy migrating!