Rails Migrations

September 4th, 2006 · 6 Comments

Migrations are a neat feature in Rails but they are documented pretty superficially; only the most basic features are documented - everything else requires much googling and gnashing of teeth. This post is an attempt to limit your dental bill.

The most basic feature is create table:

    create_table :products do |t|
      t.column :name, :string, :limit => 100
      t.column :description, :string, :limit => 1000, :null
      t.column :price, :integer
      t.column :launch_date, :timestamp, :null
    end

Note the use of limit and null to provide simple model constraints. Oddly, Rails defaults all columns to NOT NULL unless you explicitly allow it to be nullable via the null option. This is different from almost every other database tool I have ever used but I think it is the right thing to do - it’s always best to start with a more constrained system and loosen those constraints if needed.

A few examples for add_index from the ActiveRecord docs:

    add_index :suppliers, :name
    add_index :accounts, [:branch_id, :party_id], :unique => true
    add_index :accounts, [:branch_id, :party_id], :unique => true, :name => 'by_branch_party'

One of my pet peeves in migrations is the fact that it does not support foreign keys (even in edge rails) so you need to do this:

    create_table :categories do |t|
      t.column :name, :string, :limit => 50
    end

    create_table :categories_products, :id => false do |t|
      t.column :product_id, :integer
      t.column :category_id, :integer
    end

    add_index :categories_products, [:product_id, :category_id], :unique => true

    execute 'alter table categories_products add constraint fk_catprod_prodid on (product_id) references products(id)'
    execute 'alter table categories_products add constraint fk_catprod_catid on (category_id) references categories(id)'

I would love to see support for something like this:

    create_table :categories_products, :id => false do |t|
      t.column :product_id, :integer, :reference  # introspects the column name to determine the reference
      t.column :category_id, :integer, :reference => category[:id]  # explicit reference for non-standard naming
    end

Tags: Software

6 responses so far ↓

1 blah // Jun 6, 2007 at 5:47 pm

How do I reorder the existing table columns once they are set, without starting over?
For example, I need to add a column, and can do that no problem. But then I wanted the latest column that I added to be second in a list of five items. I am having trouble moving that column to display as the second in the list.
thanks
2 mperham // Jun 6, 2007 at 8:16 pm

You can’t AFAIK. I don’t know of any databases that allow you to reorder the intrinsic location of the column within a table because that would require the database to reorder the table data structures within the pages.

This sounds like it’s just a presentation issue - perhaps ActiveRecord allows you to specify the ordering of columns within a table?
3 blah // Jun 7, 2007 at 1:43 pm

I agree. I think it is an Active Record thing, but I cannot find specific info on doing this anywhere in the last two days of looking.
Rails framework docs haven;t helped either, but you have to know what you are looking for there.
like usual I am having trouble with something that is probably easy.
4 Brian Hardy // Jun 7, 2007 at 7:47 pm

If you’re using MySQL 5.0, here’s an example of syntax that could help you put a column in a specific place:

ALTER TABLE tbl_name MODIFY column_definition [FIRST | AFTER col_name]

Taken from here:
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

You can run that using ‘execute’ in your migration.

Unfortunately this requires you to repeat the column definition, including the type, which Migrations are supposed to be abstracting for you in the first place. Also I have not researched whether it works in anything other than MySQL 5+.
5 Chirag // Aug 12, 2007 at 9:55 am

Thanks Mike, here’s another site for Some advanced migration features (:force, :temporary, :opitons, execute native SQL)

http://rubycorner.net/rails-programming/how-to-use-rails-migrations-part-ii/
6 David Medinets // Oct 2, 2008 at 8:47 am

Thanks for the example showing multiple columns in an index.

Mutterings

Life in the Slow Lane