The Joy of Migrations

Posted by kev Thu, 27 Oct 2005 09:42:00 GMT

Migrations are one of the more exciting features in Rails today. I think they’re wonderful and that they should be used. I’ll go over how any why you should use these gems.

First the why…

  • Migrations are database agnostic. What this means is that you can write your description of the database in ruby -once- and have it easily translate to MySQL, PostgreSQL or SQLite out of the box.
  • Migrations mean never having to say “I don’t know the create syntax in #{SQL_SERVER_X}”.
  • Migrations allow for different versions of a database. This means that when you decide to add user authentication on that application that you know is horribly insecure, but “it would interfere with existing infrastructure”, you can do so easily and without complications.
  • Because migrations allow for easy changes to database schemas it allows for good iterative development.

In general, migrations make developing a database driven application much much easier.

How

Now, this is going to blow your mind but you should be able to pick up migrations quickly. Here’s the quick version, then I’ll go more in depth.

For the impatient

  • Step 1: Create a migration with script/generate migration WhatImChanging
  • Step 2: Modify your generated migration file in db/migrate
  • Step 3: Run rake migrate
  • Step 4: Revel in the fact that your database is converted to the newest schema!

For the slightly less impatient

Step 1: Create a migration
script/generate migration WhatImChanging

The generate migration syntax allows you to describe the migration in CamelCase or with_underscores, and will generate a file in db/migrate called VERSIONNUM_your_name_in_underscores.rb

For example, if I’m adding a users table to my schema I might do the following: script/generate migration add_user_table This might then create a file: 2_add_user_table.rb in db/migrate

Step 2: Modify your migration file

The next step is to make your migration do something. A bare bones migration looks like this:

class AddUserTable < ActiveRecord::Migration
  def self.up
  end

  def self.down
  end
end

This new class, AddUserTable, is what describes our migration. The two methods, up and down, describe what actions need to be taken to upgrade to this version of the schema and how to downgrade if that is needed.

For our sample application we want to create a new User model and its table. To do this, we simply use the create_table syntax. I’m going to write out the whole migration and then we can talk about what it all means.

class AddUserTable < ActiveRecord::Migration
  def self.up
    create_table :users do |t|
      t.column :first_name, :string
      t.column :last_name, :string
      t.column :birthday, :date
    end
  end

  def self.down
    drop_table :users
  end
end

Lets go through this a bit at a time.

create_table :users do |t| describes the creation of a table called users, passing that table as the variable t to the block. Inside the block we tell the table that it has columns named first_name, last_name and birthday. create_table and friends are called schema statements and you can find the full range of them documented quite well in the rails api. column is an example of a table definition method which can be found in a slightly different part of the api.

If you look at self.down, you’ll see where we describe how to reverse our changes: we simply drop our users table.

All of the migrations methods look similar and follow similar syntax. You do something to a table or method and describe its attributes.

Step 3: Run the migration

This is the part that is going to blow your mind. Go to the command line and run rake migrate. You’ve just been upgraded to your latest schema! If you want to reverse your changes, you can do so with rake migrate VERSION=MyVersionNumber.

How this all works

So, basically, rake has a task described which goes to the database and says, “Hey. How old are you?” The database, though slightly offended, decides to give up the information in the schema_info table’s verion column. Rake compares that version to how many migrations it has and if it detects new migrations, it runs them one at a time.

What about changing tables that already have data?

Migrations are data safe. This is a wonderful thing. For example, in a recent project I had written a shopping cart system. Another developer had named a column visibility to determine if the product should be shown or not. I knew that rails treated boolean columns like boolean methods in ruby, so I felt it was more natural to call the column visible. This way, I could write if myproduct.visible? as opposed to if myproduct.visibility == true or if myproduct.visibility?. Anyway, to make a long story short, here’s the migration in action:

class RenameProductVisibilityToVisible < ActiveRecord::Migration
  def self.up
    rename_column :products, :visibility, :visible
  end

  def self.down
    rename_column :products, :visible, :visibility
  end
end

The data was safe, and I had my asthetically pleasing code.

Tips and Tricks

  • If you’ve already got a database established and you want to work with migrations, use rake db_schema_dump. This will dump your current schema into db/schema.rb as migration markup. You can then create your initial schema with step 1 and copy schema.rb (the stuff inside ActiveRecord::Schema.define()) into self.up. You may also need to create a schema_info table with one column called version of type integer.

Something terribly witty and conclusive

Go forth and make database agnostic abstractions of your schemas! It’s good for you, really.

Posted in ,  | 17 comments | 3 trackbacks

Comments

  1. Avatar Nola said about 9 hours later:

    Wow, thats awesome! :) thanks

  2. Avatar David said about 14 hours later:

    So, can this also be used to store the entire schema? That is, could you use it to set up an entirely new DB with a simple ‘rake migrate’?

  3. Avatar Kev said about 14 hours later:

    David: It can! If you use the schema dumper with rake db_schema_dump, it will place your entire schema in a migration formatted file into db/schema.rb. You can then later use rake db_schema_import to bring the schema into your database.

    This is also good if you are 100 migrations down the line and you don’t want to go through the entire process which has the posibility of creating and then removing tables.

  4. Avatar ian said about 18 hours later:

    hey kev, any idea why i get this with rails 0.14.1?

    rake aborted! Don’t know how to build task ‘db_schema_dump’

  5. Avatar ian said about 18 hours later:

    oh, and look how cool i am. i just emptied my tables with rake migrate.

  6. Avatar Kev said about 19 hours later:

    ian: I’m not sure about 0.14.1, but both on edge rails and 0.14.2 the task is there if you take a look at rake—tasks. Emptied your tables in what way?

  7. Avatar Jorge said 1 day later:

    How about integrity constraints? Can migrations deal with it?

  8. Avatar Ryan Platte said 1 day later:

    Note that the migrations can run arbitrary Ruby code, so the sky’s the limit. You can certainly make migrations do anything you can write SQL for, or for that matter anything else.

    But watch out, when I was using this last, I did occasionally have problems from exceptions didn’t get handled gracefully. I’m not at all sure that’s a problem with migrations, either—but watch out for migrations that don’t complete. Definitely practice putting migrations through their paces before writing a bunch of mission-critical ones so you know what can go sour.

    Migrations are one of my favorite Rails features—is there any other framework out there that actually supports database refactoring right in the framework?!

  9. Avatar Kev said 1 day later:

    Jorge: Migrations can do integrity constraints. If you look at the column method api it gives you options for limit (max length), default values and null/not null constraints. I don’t believe migrations handles anything more complex than that. Really, more complex things are usually reserved for validations in rails anyway

  10. Avatar Jorge said 1 day later:

    Yup, but the question was more about table integrity constraints (foreign keys). I don’t think these are supported directly. But have to read the docs.

  11. Avatar Ray said 38 days later:

    Correction and also the solution to Ian’s problem #4

    rake db_schema_dump

    should be

    rake db_structure_dump

    at least for rake version 0.6.2

  12. Avatar Scott Mitchell said 52 days later:

    Kevin, maybe you can help me figure something out I’ve been wondering about migrations. How does the version number work in an environment where multiple developers adding migrations concurrently. It seems that this would cause multiple, unrelated changes to get into a single schema version. If you create 2_add_user_table while I create 2_add_date_to_post you will never run my change and I will never run yours because each of our local schemas think they are already at version 2.

    Do you know how, or if, Rails deals with this?

  13. Avatar Kevin Clark said 52 days later:

    Scott: I’m not sure how creating two migrations with the same version number would work. Whatever it does, unless it raises an error its probably a bug (or unintended feature). The simple answer here is that adding multiple files with the same version number is wrong and rails may be upset with you. This does of course mean that people working on the same project concurrently could have versioning problems, but unless they directly effect each other (ex. one removes the table that the other modifies) it shouldn’t matter so much and you should be able to just renumber the file.

    Hope it helps. Reply if I didn’t give you what you were looking for.

  14. Avatar Scott Mitchell said 53 days later:

    Hi Kevin, thanks for the reply. I wrote up my question with a detailed example of the problems that could ensue over on my blog (http://blog.the-mitchells.org/archives/2005/12/18/rails-migrations-and-multiple-developers/). I also pulled a copy of Rails and did a little playing. It looks like the generator only looks at the existing files in you migrate directory to determine the next sequence number so I definately think there is exposure there. Take a peek at the scenario I lay out and the consequences and let me know what you think.

  15. Avatar Kevin Clark said 53 days later:

    Scott: Turns out rails will tell you if this happens. I created a testbed app with two migrations with version 2. Here’s the snip.

    [Dionysus:~/web/testbed] kevincla% rake migrate (in /Users/kevinclark/web/testbed) rake aborted! Multiple migrations have the version number 2

  16. Avatar Konstantin Gredeskoul said 148 days later:

    I haven’t seen any way to create an index on the table in Rails? I think that any database that grows to respectable size will need indexes on tables..

    Can anyone point in the right direction?

    Thx.

  17. Avatar Kevin Clark said 148 days later:

    Konstantin: See add_index in the api.

Trackbacks

Use the following link to trackback from your own site:
http://glu.ttono.us/articles/trackback/64

  1. From hand|paper|lake
    More Rails Tech
    There are a few areas where, at least in the proposal, I down played a some aspects of the project -- mostly in terms of the sorts of compatibilities that may be offered. So, when I see things fly by the Ruby On Rails site, I make some note. I'm sure t...
  2. From hand|paper|lake
    More Rails Tech
    There are a few areas where, at least in the proposal, I down played a some aspects of the project -- mostly in terms of the sorts of compatibilities that may be offered. So, when I see things fly by the Ruby On Rails site, I make some note. I'm sure t...
  3. From Crazy Or Genius?
    Writing Database Code In Rails: Migrations and Neutrility
    As I recently said, I switched TigerEvents over from using a MySQL input file to migrations. There were many decisions behind this choice. I wanted the code to be database neutral. Using migrations would allow me to automatically support MySQL, Pos......

Comments are disabled