Ulzurrun de Asanza i Sàez

Dumping a Rails project Database with enum types

Dumping table error Rails
One of the things we don’t want to see when dumping our Database

This year I’ve been learning about MySQL, PostgreSQL and Database security and optimization. One of the features I found useful was creating enumerated types. As they are a good solution for both efficiency and data consistency (only allowing one of the values in the enumeration) I’ve found myself using them in almost any case they fit.

But it wouldn’t be as easy (if it was that easy this post wouldn’t exist). I have been working with Ruby on Rails (an awesome framework to which I’ll devote a couple of posts someday) and its capacity to create the Database from scratch impressed me. But this capacity is also possible due to Active Record migrations. With just a simple command like this you can create the migration to rebuild your Database:

[code]rake db:schema:dump[/code]

But if you use enumerated types you’ll get an error message like this in your migration file:

[ruby]
# Could not dump table “activities” because of following StandardError
# Unknown type ‘activity_type’ for column ‘activity_type’
[/ruby]

Well, it doesn’t look good: Rails couldn’t dump an entire table just because one of the fields used an unknown type (an enumeration). One solution could be modifying each table that used an enumeration but it is a lot of work and we would also lose all the advantages of the enumerated values.

There is a better solution. Rails (3.2.12) allows you to retrieve the scheme in SQL format, rather than in Ruby code. To enable this you just have to edit config/application.rb and add the following line:

[ruby]
config.active_record.schema_format = :sql
[/ruby]

Next time you run db:schema:dump you’ll get a file named structure.sql. With this file Rails will rebuild the entire Database, including enumerated values, sequences and constraints.


2 replies on “Dumping a Rails project Database with enum types

  1. Hej,

    I was really pleased when I found someone else having this issue.

    But unfortunately it seems to not work for me. :/

    Plain installation of Rails 4.1, before doing anything else I wanted to restore my scheme from an existing database using enumerations.

    Placed the line of code in config/application.rb and while it did not work it also tried config/environments/development.rb

     

    Any knowledge why it still shows the StandardError message?

     

    Greetings from Berlin, Germany
    – Stephan

  2. I found another way around this – you can add your custom types to activerecords postgres adapter with an initalizer.

    I wrote my answer up here: https://stackoverflow.com/a/45430348/1019660

Leave a Reply

Your email address will not be published.

Required fields are marked *

Your avatar