Mastering database enums in Rails with Postgres - Guide

Database enums are powerful yet often overlooked features that can significantly improve data integrity and query performance. In this guide, we'll explore how to effectively implement and leverage enums in PostgreSQL with Ruby on Rails, comparing different approaches and highlighting best practices.

Understanding PostgreSQL enums

PostgreSQL enums are ordered data types that allow you to define a static set of values. Unlike regular string columns, enums provide type safety and improved performance. What makes them particularly interesting is their implicit ordering based on the declaration sequence.

Single-value enum implementation

Let's start with a basic example using a Ticket system:

class Ticket < ApplicationRecord
  enum :criticality, {
    low: 'low',
    medium: 'medium',
    critical: 'critical'
  }, validate: true
end

The corresponding migration would look like this:

class CreateCriticalityEnum < ActiveRecord::Migration[7.2]
  def change
    create_enum :criticality, %w[low medium critical]
    # or in SQL: CREATE TYPE criticality AS ENUM ('low', 'medium', 'critical');
    add_column :ticket, :criticality, :enum, enum_type: :criticality
    add_index :ticket, :criticality
  end
end

Multi-select enum arrays

For more complex scenarios, PostgreSQL supports enum arrays, enabling multiple values per record. Enum arrays with GIN indexing offer superior performance compared to string arrays.

# migration
class AddCategoriesToPosts < ActiveRecord::Migration[7.2]
  def change
    create_enum :categories_enum, %w[text code snippet]
    add_column :posts, :categories, :enum, 
      enum_type: :categories_enum, 
      array: true, 
      default: []
    add_index :posts, :categories, using: 'gin'
  end
end

# class
class Post < ApplicationRecord
  validates_inclusion_of :categories, in: [%w[text code snippet]]
end

# post = Post.new
# post.categories << 'text'

Dynamic enum management

PostgreSQL allows for enum value additions with specific positioning:

ALTER TYPE criticality ADD VALUE 'high' AFTER 'medium';

When you have some enums in your database, then you can easily compare them:

-- In Ruby: Ticket.all.order(criticality: :desc)

SELECT 'medium' > 'low'; --true
SELECT 'medium' > 'critical'; --true
SELECT 'critical' > 'low'; --false

-- print all enum with order
SELECT * FROM pg_enum ORDER BY oid;

Summary

PostgreSQL enums provide a robust solution for handling predefined values in Rails applications, offering benefits like type safety, improved performance, and ordered sorting. Whether using single-value enums or enum arrays, proper implementation can significantly enhance your application's data integrity and query efficiency.

Happy enuming!