Defining Columns in Migrations

When using migrations, bear in mind that a migration is (by design) a database-agnostic representation of a database. It uses generic data types for columns, like :binary and :boolean, to define the kind of data to be stored in a column.

However, different database servers implement the migration column types in different ways. For example, MySQL doesn't have a boolean data type; so any migration columns you define as :boolean are actually converted into TINYINT(1) fields in the resulting MySQL database table (0 = false, 1 = true). Each migration column type also has a range of extra options you can set, which again modify the definition of the resulting field in the MySQL database.

The table below summarizes the migration column types, how they map to MySQL field data types, and the extra options available.

Migration column type...	Converts to MySQL field type...	Available options1
:binary	TINYBLOB, BLOB, MEDIUMBLOB, or LONGBLOB2	:limit => 1 to 4294967296 (default = 65536)2
:boolean	TINYINT(1)	-
:date	DATE	-
:datetime	DATETIME	-
:decimal	DECIMAL	:precision => 1 to 63 (default = 10) :scale => 0 to 30 (default = 0)3
:float	FLOAT	-
:integer	INT	:limit => 1 to 11 (default = 11)
:primary_key	INT(11) AUTO_INCREMENT PRIMARY KEY	-
:string	VARCHAR	:limit => 1 to 255 (default = 255)
:text	TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT2	:limit => 1 to 4294967296 (default = 65536)2
:time	TIME	-
:timestamp	DATETIME	-

All column types accept a :null or :default option:

• :null
  The default value for this is true (i.e. the field's value can be null in thedatabase). Set :null => false if you don't want to allow nulls in thedatabase field, e.g.
  
  • t.column :first_name, :string, :null => false
    
  Note that if you allow nulls in a field (:null => true or not specified), youdon't need to specify :default => NULL: NULL is already the default for a field, which allows null values.
  
• :default
  Specify the default value for the database field when new records are addedto the table. The value you specify should be of the correct data type for the column, e.g.
  
  1. t.column :completed, :default => true (for a :boolean column)
  2. t.column :size, :default => 1 (for an :integer column)
  3. t.column :name, :default => 'Unknown' (for a :string column)
  4. t.column :reminder_on, :default => Time.now (for a :datetime, :date, :time or :timestamp column)
     

Note that the default value should match the data type of the column (not the field). For example, if you were using MySQL and had a :boolean column, even though boolean fields are represented internally in MySQL as 1 digit TINYINT fields, you would specify the :default as true or false (not 1 or 0). This keeps your migrations portable to other database back-ends (for example, while MySQL just emulates booleans, some database back-ends have a native boolean data type, and a value of 1 or 0 might not make sense).

The :limit option on a :blob or :text column specifies the size of the database field in bytes. You can set this directly in bytes, or use a convenience method to specify the size, e.g. 2.kilobytes, 2.megabytes, 2.gigabytes(!). Note that MySQL will actually create a field with a data type, which encompasses the size you specify, i.e.

• 1 to 256 bytes: TINYBLOB or TINYTEXT
• 257 to 65536 bytes (64KiB): BLOB or TEXT
• 65537 to 16777216 bytes (16 MiB): MEDIUMBLOB or MEDIUMTEXT
• 16777217 to 4294967296 bytes (4 GiB): LONGBLOB or LONGTEXT

The :precision option specifies the number of digits to store before the point in a decimal; the :scale option specifies the number of digits to store after the decimal point.

http://www.packtpub.com/article/Working-with-Rails-ActiveRecord-Migrations-Models-Scaffolding-and-Database-Completion