Issues with :tinyint in Rails Table Definitions

This issue applies to Rails v 1.1.0 - don't know about other versions...

One option available in defining a column in a table definition is to use :tinyint instead of :integer if you want to store a small number.

With a MySQL database as a back end, :tinyint get translated into a tinyint(4) type (using 4 bytes) instead of an int(11) type, which is what :integer get translated into.

You can use :tinyint in a create_table block thus:

     create_table :users do |table|
      table.column :active, :tinyint, :default => 1
   end

The problem that I've run into is when I want to extend an existing table by adding a column of type :tinyint, using an add_column definition in a migration, like this:

add_column :disclosures, :seqs_frozen, :tinyint, :default => 0

When I run rake migrate on this I get the following error message:

$ rake migrate
(in /Users/jones/Documents/Craic/Code/RubyOnRails/portfolio)
== AddFreezeSeqsToDisclosures: migrating ======================================
-- add_column(:disclosures, :seqs_frozen, :tinyint, {:default=>0})
rake aborted!
You have a nil object when you didn't expect it!
You might have expected an instance of Array.
The error occured while evaluating nil.[]

(See full trace by running task with --trace)

Not very informative and the --trace option doesn't tell me any more.

The solution is to change the :tinyint to :integer. Do that and it will work fine. If you want to save space you can specify the size of the integer by adding :limit => 4 to the add_column definition.

add_column :disclosures, :seqs_frozen, :integer, :limit => 4, :default => 0

This will translate into a MySQL type of int(4) - not tinyint(4).

It looks like :tinyint may be deprecated in Rails 1.2 and it may be good practice to avoid it anyway. Using only :integer with a suitable :limit is probably the more mainstream way to do it.

Update: 01/29/2007

Another reason to avoid :tinyint is that Oracle will throw an error if you try and load a migration that includes :tinyint.