There are a lot of times in our applications where a field in our database can only have one of a few values. The most common are:
- Something that acts like a state machine - often used for an object that goes through several states - like
pending -> under_review -> accepted.
typecolumn in single table inheritance
_typecolumn(s) in polymorphic relationships
The most commontly used type for these columns is
varchar, and a lot of projects I’ve seen don’t even make use of
limits. It would be nice if we could store them in a type that didn’t require so much performance and storage overhead.
Most major databases have support for enumerable types (read: PostgreSQL,MySQL), and at Patch we’ve switched out a lot of our columns like the above for MySQL ENUMs. The best part from the Rails side, is that you don’t have to change anything at all in your code to swap a varchar out for an ENUM. When we made the switch, we saw a nice boost in performance (numbers coming soon) - particularly on joins that involved those columns.
- It may make your life a bit harder if you're switching databases, but worst case you can just not run the migration in databases that don't support it.
- If you use ENUMs in a field, when its used to represent a new
type, or mixed into new models - you need to modify the ENUM in a migration to contain the new value. You need to be particularly careful in your test environment, since schema.rb will load these ENUMs as strings and thus they won't fail on trying to insert a value not in the ENUM.
This sample migration should give you a good idea on how to approach the move in MySQL. Note that MySQL blocks while running
ALTER statements, so I combine multiple changes into single calls.