Tip #11 - Transact Your Migrations!

April 19th, 2008

If you are using Rails, you are most likely using migrations. Have you ever had a migration fail half way through? Have you ever then had to figure out how to find each change and revert it in the database? Would you like to never have to do that again? Here is how…

USE TRANSACTIONS

I sometimes wonder why this just isn’t the default. When I am doing a migration, I either want the thing to work completely all the way through, or I want nothing done to the database.

Luckily, you can do this with transactions.

A transaction (for those non SQL people out there) is an idea of a series of events being atomic. That is, either, they all are executed without error, or if any one of them fails or faults, then no change is made to the database at all.

Luckily, adding transactions to a migration is very easy.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

class ComplexMigrationThatCanNotFail < ActiveRecord::Migration

  def self.up
    transaction do
      add_column :table, :name, :type
      add_column :table, :name, :type
      # Complex task #1
      remove_column :table, :name, :type
      remove_column :table, :name, :type
      # Complex task #2
    end
  end

  def self.down
    # ... Down script
  end

end

Now, say your migration fails at the second remove column, no fear, everything is rolled back to the start point, you can read the rake error messages, fix the problem, and try again.

Of course, you are testing this on the development server, so no major harm done if it blows up a couple of times, but I don’t know about you, but I have lots more things to do than try to restore two gigabytes of staging or development database :)

blogLater

Mikel

Posted by mikel Filed in PostgreSQL, Ruby on Rails, Ruby on Rails Tips

3 Responses to “Tip #11 - Transact Your Migrations!”

  1. Grant Hutchins Says:
    May 12th, 2008 at 05:28 PM

    You can automate this by using the transaction_migrations plugin:

    http://www.redhillonrails.org/transactional_migrations.html

  2. szeryf Says:
    May 12th, 2008 at 11:17 PM

    This only works if the DB supports transactional DDL (like PostrgreSQL). It will fail on other DBs, like MySQL or Oracle. On those bases, the schema modifing statements are un-rollback-able. That’s why it’s not a default :)

    (You can still, of course, rollback all other statements like INSERTs or UPDATEs.)

  3. Mikel Says:
    May 12th, 2008 at 11:21 PM

    Szerf: Yeah… but who wouldn’t use PostgreSQL? :D

    Good point though… I have gotten so used to the advanced features of Postgres that I sort of forget that the other DBs don’t support everything it does.

    Mikel

Leave a Reply