Custom Database Type in ActiveRecord

Another case where someone might run into this is when inserting your custom types (enums) into PostgreSQL. If one does this and still wants to dump your schema.rb without using the SQL dumper, you can add custom database types to the adapter's list of valid types.


Update 2021-08-13: Commenter points out there might be an easier way to do this than my suggestion below.

ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:my_custom_type] = { name: "my_custom_type_name" } 

Original Suggestion

For example, let's say you have a Customer with a status and priority and you want to use postgres's native enum types. You will need to do some setup manually in your migration and you'll need an initializer to help Rails generate your schema.

The Migration

class CreateCustomers < ActiveRecord::Migration[5.2]

  def up
    execute <<-SQL
      CREATE TYPE type_status AS ENUM ('active', 'inactive');
      CREATE TYPE type_priority AS ENUM ('high', 'medium','low');
    SQL

    create_table :customers do |t|
      t.string :name
      t.column :status, :type_status
      t.column :priority, :type_priority
    end
  end

  def down
    drop_table :customer_addresses
    execute <<-SQL
      DROP TYPE status;
      DROP TYPE priority;
    SQL
  end
end

The initializer.

# config/initializers/postres_custom_enum_types.rb

# Following only needed if the adapater isn't loaded for some reason - e.g. 
# if you have no models in your app. 
require "active_record/connection_adapters/postgresql_adapter"

module ActiveRecord
  module ConnectionAdapters
    if const_defined?(:PostgreSQLAdapter)
      class PostgreSQLAdapter
        NATIVE_DATABASE_TYPES.merge!(
          enum:    { name: 'enum' },
          # there is a chance the above causes conflicts in some cases, you can
          # always be more explicit and actually use your type or names (I am not looking up which)
          # type_priority:    { name: 'enum' }
        )
      end
    end
  end
end

Validation of this solution

This is what you will see in your DB.

enum_in_db_development=# INSERT INTO customers VALUES (1, 'Mario','active','high');
INSERT 0 1
enum_in_db_development=# INSERT INTO customers VALUES (1, 'Mario','active','notthere');
ERROR:  invalid input value for enum type_priority: "nothere"
LINE 1: INSERT INTO customers VALUES (1, 'Mario','active','notthere')
                                                          ^
enum_in_db_development=# SELECT enum_range(NULL::type_priority);
    enum_range     
-------------------
 {high,medium,low}
(1 row)

Notes about my solution:

  • I am checking the existence of the PostgreSQLAdpater because of the way a static analysis gem I'm using partially loads some AR dependencies - specifically the gem annotate.
  • The source for the original definition of NATIVE_DATABASE_TYPES is here for rails 6.

Background on my running into this: When this happened to me in rails 5.0.x, the migrations ran fine, but then my test environment would fail when trying to run db:test:prepare or db:reset. It took me quite a while to track this down to the schema dump issue.


For anyones who comes to this page, someone has implemented this in a gem easy to use.

This is how your migration and model files will look like, respectively

# migration
class CreatePerson < ActiveRecord::Migration
  def change
    create_enum :mood, %w(happy great never_better)

    create_table :person do |t|
      t.enum :person_mood, enum_name: :mood
    end
  end
end

# model
class Person < ActiveRecord::Base
  enum mood: { happy: 'happy', great: 'great', never_better: 'never_better' }
end

https://github.com/bibendi/activerecord-postgres_enum


Try this:

Change you config/application.rb

config.active_record.schema_format = :sql

This will change the output format to the native PostgreSQL SQL format, schema.rb will be disabled and a new file will be generated /db/config/structure.sql