Custom Database Type in ActiveRecord

Question

I am using Rails 3.1.1 with PostgreSQL 9.1 and the earthdistance module. To be able to calculate the distance between different locations properly, I have setup a column with the earth type in my branches table.

The problem I am experiencing now is that my Rails application that uses this table does not understand the earth type and thus I am getting this in my db/schema.rb:

# Could not dump table "branches" because of following StandardError
#   Unknown type 'earth' for column 'location'

This is problematic since now I can't create my test database from the schema.rb.

How can I add this type to AR or make it ignore that column?

Answer 1

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.

Answer 2

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