Ruby on Rails
UsingMigrations

See UnderstandingMigrations for the background on what they are and how they work in general.

1. Create a migration

1. Migrations should be under_scored or CamelCased, just like models, controllers and views.
2. Giving a migration a name identical to that of an existing model class will cause rake db:migrate to fail mysteriously.

2. Edit the code to tell it what to do.

Edit the newly created file (in db/migrate). Define the up and down methods.

Tables

create_table

An ID column(primary key) will be created automatically (this can be disabled with the :id => false option for habtm tables.)

example:

  class AddANewTable < ActiveRecord::Migration
    def self.up
      create_table :users do |table|
        table.column :name, :string
        table.column :login,  :string, :null => false
        table.column :password, :string, :limit => 32, :null => false
        table.column :email, :string
      end
    end

    def self.down
      drop_table :users
    end
  end

example where 2 tables are created. One is a habtm table:

  class AddTags < ActiveRecord::Migration
    def self.up
      create_table :images_tags, :id => false do |table|
        table.column :image_id, :integer
        table.column :tag_id, :integer
      end
      create_table :tags do |table|
        table.column :name, :string
      end
    end

    def self.down
      drop_table :tags
      drop_table :images_tags
    end
  end

Valid column types are integer, float, datetime, date, timestamp, time, text, string, binary, and boolean. Valid column options are limit, null (i.e. ” :null => false” implies NOT NULL), and default (to specify default values).

Note: The API doc on add_column refers to column_types which say that it can be of type :primary_key. This is not true. add_column cannot use :primary_key as a type_

create_table takes a number of options (described in the API reference). One of which is named options, it allows you to pass raw SQL that will be appended to the create table instruction. It is this way that you can set things like character encoding, or table types.

Set the ":option" option in the create_table method like so:

create_table(:families, :options => 'DEFAULT CHARSET=UTF8') do |t|

Columns

Migrations work with columns in create_table and also with add_column.

Column options dictate the properties of the column, for example:

add_column :addresses, :street, :string, :null => false

Creates a street column in the addresses table that is a string field and is set to “NOT NULL”

Note: the opposite of add_column is remove_column

Options when running rake db:migrate

Migrate to a specific version

A specific version can be targeted with VERSION=x

rake db:migrate VERSION=17

Older versions of Rails(eg. 0.13.1) required manual assistance:

% ./script/console
Loading development environment.
>> require 'db/migrate/017_example_migration'
=> true
>> ExampleMigration.down

Migrate a specific database

rake environment RAILS_ENV=production db:migrate

Troubleshooting Migrations

If you attempt to run rake db:migrate and it fails, what should be attempted next?

As with all rake tasks, appending -t or --trace will spill a backtrace with hopefully more information about the problem.

rake db:migrate -t

rake db:migrate—trace stops at Execute migrate

Questions

How can I use a migration to control my engine migrations? i.e. how can I call rake engine_migrate from my regular migration?

Can this be used in place of an initial DB schema, if you’d rather describe everything about your model with Ruby code to begin with?

Yes it can. I’m using this very method, as it’s much easier to do this than create a bunch of different SQL files for each database.

—MattMoriarity

A migration is run with the current model. Older migrations may fail when depending on a newer model.

That is true. Example: migration 003 may try to create a row in the Articles table when this table (and its model) may have been removed in version 004. This is why it is better to keep your migrations free of application models. Create temp models if you need to do basic queries, however.

class MyMigration < Migration
  class Article < AR::Base
  end

  def self.up
  end

  def self.down
  end
end

This essentially makes the model’s name MyMigration::Article, and won’t clash with your app’s Article model or any other migrations’
Article models.

Good rules of thumb:

• Always use local models in migrations (as above).
• Never rely on dynamic associations, always use foo[:bar_id] = qux[:id] rather than foo.bar = qux.

Following those rules allows changes to model code while also letting your app bootstrap itself in a single rake db:migrate pass.

How does the rake provide ordering to the migrations?

Imagine 2 migrations…

• A – creates a table FOO
• B – adds a new column to table FOO

The files are prefixed with a number. So the first migration you make will be called 001_add_table.rb, the next will be 002_add_flag.rb. Use the “script/generate migration” command and it’ll do this automatically.

It is essential that the migration file- and classnames be different in these cases. If you name your migrations something generic like “001_myapplication.rb’ and ‘002_myapplication.rb’, you will receive errors.

Name your migrations descriptively, and name them each differently.

Question: Why should I have to name the migrations uniquely when they each have a number associated with them? Couldn’t I name the classes that associate to each migration so they contain the sequence number?

How can I specify a constraint – a foreign key?*—AlanFrancis

While Rails 1.0 has yet to fully support adding foreign keys through the schema, you can fudge it by using SQL directly.

In this example, I’m adding a table of researchers who have references to a division and a department:

class AddResearchers < ActiveRecord::Migration
  def self.up
    create_table 'researchers', :force => true do |t|
      t.column 'department_id', :integer, :default => nil
      t.column 'division_id', :integer, :default => nil
      t.column 'last_name', :string, :default => '', :null => false
      t.column 'first_name', :string, :default => '', :null => false
    end

    # These two lines are required if you're running a version of MySQL prior to 4.1.2
    #add_index "researchers", ["department_id"], :name => "fk_researchers_departments" 
    #add_index "researchers", ["division_id"], :name => "fk_researchers_division" 

    execute 'ALTER TABLE researchers ADD CONSTRAINT fk_researchers_departments FOREIGN KEY ( department_id ) REFERENCES departments( id ) '
    execute 'ALTER TABLE researchers ADD CONSTRAINT fk_researchers_division FOREIGN KEY ( division_id ) REFERENCES divisions( id ) '
  end

  def self.down
    drop_table :researchers
  end
end

For other type of constraints, say UNIQUE, it might be better to specify them in your model via validation.

The above comment is ludicrous, you always want to guarantee data integrity. If anything, do both.

I got tired of typing all the ‘execute’ stuff, so I wrote a foreign key module that adds the f_key method and makes it much easier to add foreign keys and optionally the associated indexes. I hope it’s helpful.

Thanks David Pollak

Simon Harris has also written a foreign key migration plugin

Yet another guy here who got tired of writing all those execute statements, so I wrote my own Migrations Constraints plugin:
http://rubyforge.org/projects/mig-constraints/

This lets me do stuff like:

  create_table :chapters do |t|
    t.column :book_id, :integer, :null => false, :references => :books
    t.column :order, :integer,: :null => false, :check => '> 0'
    t.column :name, :string, :null => false, :unique => true
  end

It at least works (for me) for PostgreSQL 8.2.

How do I make rake override table “already exists” and the MySQL error “ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails.”

I had this problem when setting up Acts_as_authenticated. Newer MySQL versions don’t want you messing up tables that have foreign key constraints from other tables. Thus you won’t be able to drop the table properly and do your migration. Here’s what you need to add to the code:

def self.up
  execute "SET FOREIGN_KEY_CHECKS=0" 
  drop_table "users" 

  ..the rest of your migration code here
end

That solution didn’t work for me but what did was:

def self.down
  execute 'ALTER TABLE users DROP FOREIGN KEY fk_users_role'
  drop_table :users

  ..the rest of your migration code here
end

 

Guy Argo

How do I create a multiple-column index?

Pass an array of column names to the column parameter, e.g.

add_index :cars, [:license_plate, :state], :unique

How do I specify a decimal like the price from the Rails book?

Unless I have misunderstood the question then the esaiest way is use a :float

Use a plain :integer column, and do the math in your model. This is called fixed point decimal. Ties in very nicely with the Money library. See also ActiveRecord::Aggregations::ClassMethods

If you really need/want the DECIMAL/NUMERIC column, do:

create_table :mytable do |t|

  t.column(:mycolumn, :decimal, { :precision => 15, :scale => 3 })

end

I just did one (Jan 26) using :decimal. Not sure if the (9,2) part is allowed yet. :limit => "9,2" didn’t do it, it stayed the default (10,0) [mysql]. Anyone know anything more about this? :decimal isn’t mentioned in the api docs at all. —Paul_S

def self.up
  add_column :sale_line_items, :unit_price :decimal, :precision => 9, :scale => 2, :null => false, :default => 0
end

That should do it :)—Uwe Kubosch

How do I migrate from a legacy schema?

Try this in your 1_initial migration:

def up
  sql_code = <<_SQL
( paste your schema here )
_SQL
  sql_code.split(';').each do |stmt|
    execute stmt
  end
end

I have also found that splitting on a newline is helpful when your text fields might have a semicolon in them when inserting data from your legacy database.

def up
  sql_code = <<_SQL
( paste your schema here )
_SQL
  sql_code.split(";\n").each do |stmt|
    execute stmt
  end
end

How do I override the user specified in database.yml?

In my 001_initial, I’d like to add the execute statements to create the database and the user. In order to do so, I need to specify something like “rake USER=root migrate”.

How?

How to handle char/varchar?

use :string, :limit => 100 to have varchar(100)

def self.up
  add_column "icons", :string, :limit => 100
end

Problem is, this doesn’t allow you to specify char; apparently there is only varchar? If your data model specifies fixed-length fields, this could be a problem.

Can migrations from multiple project update the same database, or will the numbering scheme mess things up?

The numbering scheme present in the db/migrate/ directory must be consistent with the schema_info.version number in the database or else migrate makes no sense.
—DamonClinkscales

How Do I Tell My Migration To Create a Drop or Create a Database?

Is this possible?

In the source of Active Record 1.13.2 (part of the Rails 1.0 install), the SQL Server and MySQL adapters support a create_database() method. However, for database agnosticism, you’re off the reservation with this one. Also, usually you have to set up the connection info separately in your database to put in database.yml anyway, so it may not be all that useful anyhow. DamonClinkscales

Where does rails store the version information?

Rails creates a SCHEMA_INFO table in the target database[.schema for Oracle] and stores the version number in the version column.

What causes an ‘uninitialized constant’ error on `rake db:migrate`?

This is a result of your migration.rb file having a different name than the classname (i.e. if you create one by hand). If you make a task named 005_create_sometables_task.rb the class name must match (CreateSometablesTask) or you will get the above error.

Also, note that if your class name has an all-caps string in it (e.g. FileURL) the resulting stub migration will fail with this error too. Modify the migration class name to use lower case letters.

# migration originally created a class named CreateFileURLs
# which failed with an 'uninitialized constant' error.
# Fix by removing the capitalization.
class CreateFileUrls < ActiveRecord::Migration
...

You also need to be sure that the self.down method refers to the correctly named class.

How to migrate development database via capistrano?

See the Capistrano manual section on the migrate task

Will migration versioning work if we have multiple developers on various svn branches? Group A is continuing work/development on the main trunk while Group B is refactoring a specific area of a Featured Branch.

I have not been able to recreate the described effect of the “rake db:migrate VERSION=versionnumber” command.

I created a series of migrations named 001_migration.rb through 007_migration.rb. The last migration worked fine until I attempted to rollback to the previous version using

rake db:migrate VERSION=6

which dropped all the tables in my database.

Now the migration refuses to do anything, regardless of which version number I tell it to use. It always tried to run the latest version, which fails because the relevant tables do not exist. What is wrong?

ANSWER: My migration names were the same. Each migration filename must be different or you will receive errors.

How do I trash the DB and start again? And on a related point, under what circumstances will down() be called?

For full “trashing” you can always use your db’s DROP DATABASE foo command. As for down() calls—it will be executed any time that you run rake db:migrate VERSION=n, where n is less than whatever is the current version. You can check current version in either schema.rb or running SELECT version FROM schema_info. And for safe trashing you can do rake db:migrate VERSION=0.

OR

For mysql, add this code into a library file and require the library in your environment file. Then call the drop_all_tables method in any of your migrations.

class ActiveRecord::ConnectionAdapters::MysqlAdapter

  def drop_all_tables()
    ActiveRecord::Migration::say 'Dropping all tables besides the schema_info table...'
    result = ActiveRecord::Migration::execute 'show tables'
    while table = result.fetch_row
      drop_table table.to_s unless table.to_s == 'schema_info'
    end 
  end

end

OR

You can use a migration and set the database to version 0.

rake db:migrate VERSION=0

Ben Linton

BUT

rake db:migrate VERSION=0 will fail if any migration is not reversable.

Easiest now is:

rake db:drop

Then you can rake db:migrate (or load the schema).

How do I set a default time in a table’s column?

Use the Time.now method call.

class DateAvailableProducts < ActiveRecord::Migration
  def self.up
    add_column :products, :date_available, :datetime, :default => Time.now, :null => false
  end

  def self.down
    remove_column :products, :date_available
  end
end

This puts the current time into the column when the migration gets executed. (This does not put the current time into the column when your Rails app adds a new row to that table.)

I have create a table called people using a migration script. I used the command rake db:migrate and everything went well. Now I wanted to create another table so I created another migration script. How can I only run the second script to create the second table only. Whatever I do, I get the following error:

Mysql::Error: #42S01Table ‘people’ already exists: CREATE TABLE people (`id` int
(11) DEFAULT NULL auto_increment PRIMARY KEY) ENGINE=InnoDB

(See full trace by running task with—trace)

It basically tries to creat the first table I have already created. That is why it is giving me the error.

Any help would be appreciated.

Is there a way to get the existing DB schema autogenerated in a migration?

If you already have a DB schema and want to port the application to Ruby it’s not really in the ROR spirit to start writing the whole schema in the migration…maybe I’m missing something.

Thanks, Radu

Answer: When you ‘rake’ (or specifically, “rake db:schema:dump”) db/schema.rb is created. You can generate a initial migration and place the entire file contents as a migration, or you can generate the correct models/migrations and add the relative parts of the schema to the migrations.

I just upgraded to Rails 1.1.2 and am experiencing a “Marshal Data Too Short” error. After experimenting I have found that if I changed the “data” column, “sessions” table to a type “mediumtext” (instead of “text”), it worked just fine. How can I make a migration that allows for this since Rails only accepts “string” as a datatype?

For a pretty complete look at migrations and related issues, please take a look at my RailsConf presentation Agile Databases with Migrations

Thanks DamonClinkscales

Is it possible to have “rake db:migrate” connect to the database as a different user from the one specified in config/database.yml? I don’t want to give create/drop privileges to my entire application when I only need them for migrations.

You can run migrations as a different user by creating a new environment. I did the following and it worked for me:

• Copy and paste config/environments/devlopment.rb to config/environments/migration.rb
• Create a new entry in database.yml named migration and specify the admin username and password
• Run rake RAILS_ENV=migration db:migrate

Jeff Dean

hello,

noticed something funny:

script/generate sigration create_books

(mind the typo)

results in:

class CreateBooks < ActiveRecord::Migration
  def self.up
    create_table :sessions do |t|
      t.column :session_id, :string
      t.column :data, :text
      t.column :updated_at, :datetime
    end

    add_index :sessions, :session_id
  end

  def self.down
    drop_table :sessions
  end
end

:)))

Agnieszka

Answer:

This is because the generator script takes “sigration” to be an abbreviation for “session_migration”, which generates the migration for the ActiveRecord session store.

Is there any way to flatten migrations?

I’m up to version 37, and to a point where I know I wont be rolling backwards. I’ve thought about trashing my version files, running db_schema_dump, and rolling to version 1. Sounds scary though. I’d love to have like a db:migrate flatten command.

Do I have to put the id column in?

Or does migration scripts do that automatically for me?

Answer:
Do not put the id in. It is added automatically.

How do I specify an environment-specific migration (in the script, not from the rake command-line)?

I guess one way would be an if statement based on the current environment, but how do you determine that within the migration script? I have a staging environment and want to run certain commands only for that, but not for production.

rake RAILS_ENV=staging db:migrate

What happen if i need to create migrations also for plugins ?

I wrote a small tutorial, migrate plugin schemas using Ruby on Rails 1.2 that explain how to add an db:migrate:plugins task to your application.

Is there a fast way to set all columns in a table to a certain default?

With this code I changed all the columns in a table (40 columns) to default = null:

class NullToPats < ActiveRecord::Migration
  def self.up
      columns(:quest_pats).each do |campo|
          if campo.name != "id" 
               execute("alter table quest_pats alter column " + campo.name + " drop default")
          end
      end
  end

  def self.down
      columns(:quest_pats).each do |campo|
          if campo.name != "id"      
              execute("alter table quest_pats alter column " + campo.name + " set default '0'")
          end
      end
  end
end

’’‘Anthony Ettinger’’’ asks:

Is there a way to convert from one table format to another in a migration?

For example, i’m adding a new table for normalization, and want to convert the old column in the other table to the new format:

Example:

table payment_types (new table)
table payment_types column name (ie ‘Google’, ‘Paypal’)

table orders (existing table)
table orders column pay_type (string) ( has ‘google’, ‘paypal’, ‘whatever’ hardcoded).

I am adding a column (and foreign key) to orders table called ‘payment_type_id’.

So now in my migration file after the schema is set, i need to convert orders.pay_type (ie ‘google’, ‘paypal’) into the payment_types.id value (1, 2, etc) where payment_types.name is equal to whatever the value is in orders.pay_type.

...and then insert that back into the orders.payment_type_id field.

I know I can do this by hand, with sql, but on a large production table that would not be appropriate.

Where on Earth could anyone get the idea that using SQL on a large production table “would not be appropriate”? In fact, doing large batch-style jobs like refactoring tables is exactly the weakest point of ORM solutions (including active record) and SQL’s strongest.

You can solve the whole problem in 3 lines of SQL:

• insert into pay_types (pay_type) select distinct pay_type from orders
• alter table orders add column order_id integer references orders
• update orders, pay_types set orders.pay_type_id = pay_type.id where orders.pay_type = pay_types.pay_type

Is there a way to handle enums?

See Enum Column in MySql.

Here is the error log SQL (0.000000) Mysql::Error: Can’t create table ’.\depot_development\products.frm’ (errno: 121): CREATE TABLE products (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `title` varchar(255) DEFAULT NULL, `description` text DEFAULT NULL, `image_url` varchar(255) DEFAULT NULL) ENGINE=InnoDB

class CreateContacts < ActiveRecord::Migration
  def self.up
    create_table :contacts do |t|
      t.column :handle, :string, :limit => 8
      t.column :name, :string, :limit => 255
      t.column :postal, :string, :limit => 255
      t.column :country, :string, :limit => 2
    end
  end

  def self.down
    drop_table :contacts
  end
end

class AddTestContacts < ActiveRecord::Migration
  def self.up
    Contact.create(
    :handle => 'JXH1',
    :name => 'Jim H',
    :postal => 'St Paul MN 55111',
    :country => 'US'
    )
    Contact.create(
    :handle => 'HMB1',
    :name => 'Henry B',
    :postal => 'Westwood MA',
    :country => 'US'
    )
    Contact.create(
    :handle => 'F1',
    :name => 'Flynn',
    :postal => 'Boston MA',
    :country => 'US'
    )
  end

  def self.down
  end

results in:

mysql> select * from contacts;
+----+--------+---------+--------+---------+
| id | handle | name    | postal | country |
+----+--------+---------+--------+---------+
|  1 | NULL   | Jim H   | NULL   | NULL    | 
|  2 | NULL   | Henry B | NULL   | NULL    | 
|  3 | NULL   | Flynn   | NULL   | NULL    | 
+----+--------+---------+--------+---------+
3 rows in set (0.00 sec)

Huh? I copied the files in db/migrate/ exactly. MySQL is 5.0.45, Rails 1.2.5, ruby 1.8.6. Feels like the mysql adapter is screwey, but this is pretty bad: “rake db:migrate” shows no errors.

How do I handle triggers?

How do I set it up so that there’s an auto-rollback when there’s a rake exception? (e.g. it’d be nice if NO tables were created if there’s an error with one of my tables being created). I spend a lot of time dropping tables by hand.

I have no shell access. Can I run migrations by hitting a URL?

I’d guess I’ll need a migration controller, and the URL would be something like ”/migration/migrate/7” where “7” is the version to migrate to.

Is this possible?

Cheatsheet

For beginners and experts, there’s a compact Rails Migrations cheatsheet in printable PDF format, which makes a brilliant at-a-glance reference: Rails Migrations Cheatsheet

auto_migrations plugin

A really useful plugin that allows you to edit the `schema.rb` and apply the changes without the hassle of generating migration files. Brilliant for when you’re in the initial stages of developing your app and the database schema is constantly changing. An article on it is here: Auto Migrations