# Migrations don't really support transactions **URL:** \ **Category:** Rails\ **Created:** [May 10, 2006, 11:00pm UTC](https://www.ruby-forum.com/t/migrations-dont-really-support-transactions/60181 "2006-05-10T23:00:40Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\ **Post date:** [May 10, 2006, 11:00pm UTC](https://www.ruby-forum.com/t/migrations-dont-really-support-transactions/60181/1 "2006-05-10T23:00:40Z")
I read in various places that although migrations aren’t transactional, all you need to do is wrap you migration method in “ActiveRecord:: Base.connection.transaction do” to make the self.up or self.down transactional. In my experience (Rails + PostgreSQL), this doesn’t work very well. If my migration hits an exception, any tables that were touched remain modified. Even if I fix the error in my migration, I can’t just re-run the migration since my schema doesn’t match what the migration is expecting. I also can’t migrate down since it seems like the up migration wasn’t recorded. Here is a link to another write-up about this same problem. [http://www.height1percent.com/articles/2006/02/18/on-the-need-for-more-robust-rails-migrations](http://www.height1percent.com/articles/2006/02/18/on-the-need-for-more-robust-rails-migrations) Am I missing something or is there really no way to protect the DB from failed migrations? It can be a real pain to go into the DB an manually fix everything in order to run the migration after an exception. Does anyone have any suggestions other than being more careful when I write migrations? Thanks, Eden ---
**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\ **Post date:** [May 10, 2006, 11:22pm UTC](https://www.ruby-forum.com/t/migrations-dont-really-support-transactions/60181/2 "2006-05-10T23:22:24Z")
On May 10, 2006, at 1:59 PM, Eden B. wrote: > Am I missing something or is there really no way to protect the DB > from failed migrations? It can be a real pain to go into the DB an > manually fix everything in order to run the migration after an > exception. Does anyone have any suggestions other than being more > careful when I write migrations? You could test the migrations against a snapshot of production. You could use a database that supports transactional DDL, like PostgreSQL. – – Tom M. ---
**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\ **Post date:** [May 10, 2006, 11:41pm UTC](https://www.ruby-forum.com/t/migrations-dont-really-support-transactions/60181/3 "2006-05-10T23:41:07Z")
Hi Tom, Thanks for the suggestions. I am working with my production database, so it isn’t really an issue of lost data as much as it is an issue of lost time and extra frustration. I know there is the --dry-run option, but I don’t think that will catch my runtime exceptions or errors. What do you mean by “You could use a database that supports transactional DDL, like PostgreSQL.”? I am using PostgreSQL, but I haven’t found a way to get migrations to respect my request that the changes to the database be transactional. It seems like once rake hits an exception it just dies without trying to roll anything back. Thank you, Eden ---
**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\ **Post date:** [May 11, 2006, 12:36am UTC](https://www.ruby-forum.com/t/migrations-dont-really-support-transactions/60181/4 "2006-05-11T00:36:45Z")
No problem at all. I have been “using” transactions with migrations based on a blog article for a month now. All the time wondering if I had hit a strange bug when the transactions “failed”. Of course it looks like they never worked in the first place. ---
**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\ **Post date:** [May 11, 2006, 12:21am UTC](https://www.ruby-forum.com/t/migrations-dont-really-support-transactions/60181/5 "2006-05-11T00:21:31Z")
On May 10, 2006, at 2:38 PM, Eden B. wrote: > What do you mean by “You could use a database that supports > transactional DDL, like > PostgreSQL.”? I am using PostgreSQL, but I haven’t found a way to > get migrations to respect my request that the changes to the > database be transactional. It seems like once rake hits an > exception it just dies without trying to roll anything back. I thought I had seen blog entries about using transactions within migrations. A quick Google run only found people lamenting the fact that they cannot. Weird. Sorry for the misinformation. – – Tom M. ---
**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\ **Post date:** [May 11, 2006, 1:25am UTC](https://www.ruby-forum.com/t/migrations-dont-really-support-transactions/60181/6 "2006-05-11T01:25:20Z")
At this point, I would be really happy if I had a rake task that would do the following: 1. Clear all data and tables from my development db 2. Reset my migration status to blank (instead of VERSION X) 3. Optionally run rake migrate with the VERSION=X value I specify On the surface this doesn’t sound too difficult to do and would save me the hassle of manually fixing my db each time migrate fails. ---
**Author:** ![Eden\_B](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/6de8d8/32.png) [@Eden\_B](https://www.ruby-forum.com/u/Eden_B)\ **Post date:** [May 16, 2006, 11:07pm UTC](https://www.ruby-forum.com/t/migrations-dont-really-support-transactions/60181/7 "2006-05-16T23:07:50Z")
Tom, Thank you for the sh script for resetting my database. I frequently develop on Windows, so I decided to figure out how to clear my db using a rake task. The attached rake task drops all tables in the database and resets the version number in schema\_info. I haven’t really tested it carefully, so you are taking your chances if you use this. It is very bare-bones right now, but possible improvements could include: 1. Take VERSION=X as an input to allow for a quick jump back up to any migration level. 2. Rewrite this as a mixin module so that all my migrations could have the option to run this code when an exception occurs during a migration. If done smartly, the version that failed could be noted, this code executed, and the migration automatically run up to the last know good version (i.e. exception happens on version 15, so this clears db and runs migration up to version 14).