MySQL Data Types and Rails Migrations

If you have ever tried googling for Rails migrations and MySQL data types in order to understand how they map, you may have been disappointed as I had been. There is very little documentation about that on the RoR Wiki. So, it takes a while to understand how they map, what you can do in your create_table functions and what you cannot do. There is a way to get all that information though. And it's right on your computer.

First, it is important to understand that Rails communicates with databases by using Adapters. What in Java are usually known as JDBC Database Connectors. Adapter classes for databases are usually found in classes of the following format:

ActiveRecord::ConnectionAdapters::DatabaseServerNameAdapter. For which DatabaseServerName could be MySQL, PostgreSQL and so on.

Assuming that you're running OS X or any other type of Unix system, you will be able to find the code for your particular adapter by running:

less /usr/local/lib/ruby/gems/1.8/gems/activerecord-1.15.1/lib/active_record/connection_adapters/mysql_adapter.rb

In my case, my database adapter is MySQL. Once the file opens up, look for the function native_database_types. It shows the mapping between Rails names and your particular database. Again, in the case of MySQL the mapping is as follows:


def native_database_types #:nodoc:
{
:primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
:string => { :name => "varchar", :limit => 255 },
:text => { :name => "text" },
:integer => { :name => "int", :limit => 11 },
:float => { :name => "float" },
:decimal => { :name => "decimal" },
:datetime => { :name => "datetime" },
:timestamp => { :name => "datetime" },
:time => { :name => "time" },
:date => { :name => "date" },
:binary => { :name => "blob" },
:boolean => { :name => "tinyint", :limit => 1 }
}
end


It is also interesting to read through the full source code of the adapter. There is a bunch of valuable information in there that one can use to optimize the usage of ActiveRecord models as well as Migrations.

-- JPGeek.