Tip #11 - Transact Your Migrations!

Thu Apr 17 10:23:10 -0700 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

  1. Grant Hutchins Says:
    Sun May 11 14:28:11 -0700 2008

    You can automate this by using the transaction_migrations plugin:

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

  2. szeryf Says:
    Sun May 11 20:17:19 -0700 2008

    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:
    Sun May 11 20:21:26 -0700 2008

    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

  4. Pierre Yager Says:
    Sat May 31 23:36:08 -0700 2008

    Firebird is another great RDBMS with full transactioning support (but ok, Firebird connectors and ActiveRecord adapters are not… as good as one can expect)

  5. Mahmoud M'HIRI Says:
    Mon Jul 07 19:41:37 -0700 2008

    Well, it’s not accurate that MySQL do not support transactions, it does when the used storage engine is InnoDB and not the default MyISAM.

    http://railsforum.com/viewtopic.php?id=3632&p=2

  6. sdfg35gd Says:
    Thu Dec 22 18:26:18 -0800 2011

    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.bucheline bj reboehowto fjk camgraba sg delaserug

Leave a Reply