a tornado of razorblades

Jeremy Kemper merged my patch to wrap migrations in a transaction into Rails edge last week. (This was a two year old patch - props to Tarmo Tänav for some mad rebasing.)

Wrapping migrations in a transaction is obviously the Right Thing To Do. But much to my surprise, there was substantial resistance to patches that added this. Those opposed to transactional migrations were concerned that it would alter the behavior of failed migrations. But I hardly imagine there is any app whose behavior depends on the way a migration fails.

I think the real reason there was resistance to this change is that MySQL doesn’t support DDL transactions; and wrapping migrations in a transaction is mostly pointless without this feature. Since MySQL doesn’t support it, most Rubyists don’t know what DDL transactions are - so here’s a quick primer on this incredibly useful and certainly underrated feature.

DDL = data definition language, also known as the schema. DDL commands include CREATE TABLE, DROP TABLE, ALTER TABLE, and CREATE INDEX. DDL transaction support means that the following sequence of SQL commands will not modify your database:

BEGIN;
DROP TABLE mybigtable;
ROLLBACK;

You can create tables, drop tables, add new fields - whatever. The database will safely roll back if you abort the transaction. Any regular data operations (insert, update, delete) will work exactly as you expect within the transaction.

To my mind, this capability is essential for database migrations on a production site. When a migration breaks while not run inside a DDL-capable transaction, your database is left in an undefined state. This leaves you manually picking through your production database, trying to determine what changed and manually tweak it back to something sane.

For a development database, this isn’t a big deal - you can always fall back to rake db:reset. But for a large production site, this is awful.

With DDL transactions, a failed migration never affects the database in any way. Hold up, I think that deserves some bold and italics:

Sounds great, right? The catch, as I mentioned earlier, is that MySQL doesn’t support it.

In my ActiveRecord patch, I added a ddl_transactions? method on the adapter, currently set only for Postgres. Other databases support DDL transactions, but Postgres is the only one I have easy access to. I understand that Oracle and MS SQL Server both have this capability; if you have access to one of these to test with, submit a patch for the appropriate adapter.

Heroku uses Postgres, and we have the transactional_migrations plugin hotpatched into all apps deployed on our platform. So you can’t get your Heroku database into an undefined state, which saves us from all sorts of confusion and support headaches.

It’s too bad that neither of the two most popular Rails SQL backends - MySQL or SQLite3 - support this excellent feature. I wouldn’t expect it of SQLite, because SQLite is not intended for large production databases. But it would be nice to see this in MySQL. Maybe Sun can throw a few programmers at it.

In summary: Wrapping migrations in a transaction is the correct solution to the problem of database migration inconsistency. Rails 2.2 will support it, assuming your database does, and your adapter returns true for ddl_transactions?.