Bulk ALTER TABLE with Rails 3 and MySQL
Published about 3 years ago

With this commit, Rails migrations will support adding/removing/changing multiple columns via a single ALTER TABLE statement for MySQL.

Let’s say you need to add 2 new columns to a table and modify data type of another column. Normally, you’d do something like this from a migration:

change_table(:users) do |t|
  t.string :im_handle
  t.belongs_to :company
  t.change :birthdate, :datetime
end

The above translates to 3 separate ALTER TABLE statements:

ALTER TABLE `users` ADD `im_handle` varchar(255)
ALTER TABLE `users` ADD `company_id` int(11)
ALTER TABLE `users` CHANGE `updated_at` `updated_at` datetime DEFAULT NULL

Now if you read the relevant MySQL documentation, it’s a little fucked up how ALTER TABLE works. But in a nutshell, here’s what MySQL does.

1. Write lock the table. So no more INSERT or UPDATE.
2. Make a temporary copy of the table, including all its data.
3. Do all that alteration on the temporary table.
4. Make the temporary table new primary. Get rid of the original primary table.

Whew! Imagine doing this 3 times for a table containing millions of rows. Hint: It’s not pleasant.

This commit adds a new :bulk => true option to change_table.

At the time of writing this, only ‘mysql’ adapter supports the :bulk option.

change_table(:users, :bulk => true) do |t|
  t.string :im_handle
  t.belongs_to :company
  t.change :birthdate, :datetime
end

:bulk => true makes sure the change_table runs only 1 ALTER TABLE statement.

ALTER TABLE `users` ADD COLUMN `im_handle` varchar(255), ADD COLUMN `company_id` int(11), CHANGE `updated_at` `updated_at` datetime DEFAULT NULL

When you are dealing with massive tables, :bulk => true will not only make your migration run faster, but also make it more atomic.