Dumping a Rails project Database with enum types

Beware: This post was published 4 years ago and its content may be outdated.
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:

rake db:schema:dump

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

# Could not dump table "activities" because of following StandardError
# Unknown type 'activity_type' for column 'activity_type'

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:

config.active_record.schema_format = :sql

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.