Alter Table Rails Plugin
A rails plugin to execute multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement by Sebastian Röbke and Timothy Payton
We are using the Ruby on Rails framework for a lot of our website. And during every week’s release we have migrations running on our database. We want those migrations to be as fast as possible, to keep our site response times low and make modifications to our database as easy as possible. As the size of the data grows, the migrations get slower and slower. We began to notice over the last couple months a pattern in raw SQL we were writing to use MySQL’s native ability to run multiple alterations on a table in a single ALTER TABLE statement. Our new alter_table plugin for Rails is intended to provide nice ruby code for creating the same raw SQL we were writing by hand.
A migration changing an existing database table ‘people’ might look like this:
class AlterPeopleTable < ActiveRecord::Migration def self.up change_column :people, :first_name, :string, :default => "John", :null => false add_column :people, :last_name, :string, :default => "Doe", :null => false remove_column :people, :street rename_column :people, :phone, :telephone add_index :people, :last_name remove_index :people, :zip end end
Now, we see two problems with this code. First, the table name ‘people’ has to be repeated in every line. Second, and more important, the code will lead to six separate ALTER TABLE statements being sent to the database. This will slow down the execution of the migration significantly, especially for large tables.
The good news is that databases like MySQL and PostgreSQL allow an ALTER TABLE statement to contain multiple clauses, so no matter how many alterations you want to apply to a table, they can be combined into a single ALTER TABLE statement.
Truthfully, we were surprised Rails did not already provide this. So, we went ahead and added an alter_table method that follows the syntax already used in Rails migrations.
To take advantage of that, here is how you would write the same migration using the alter_table plugin:
class AlterPeopleTable < ActiveRecord::Migration def self.up alter_table :people do |t| t.change_column :first_name, :string, :default => "John", :null => false t.add_column :last_name, :string, :default => "Doe", :null => false t.remove_column :street t.rename_column :phone, :telephone t.add_index :last_name t.remove_index :zip end end end
The produced SQL would look like this:
ALTER TABLE `people` CHANGE `first_name` `first_name` VARCHAR(255) DEFAULT 'John' NOT NULL, ADD `last_name` VARCHAR(255) DEFAULT 'Doe' NOT NULL, DROP `street`, CHANGE `phone` `telephone` VARCHAR(64), ADD INDEX `index_people_on_last_name` (`last_name`), DROP INDEX `index_people_on_zip`
Currently the plugin only supports MySQL databases, but other database types will be added soon.
If you find yourself making changes to large tables in your Rails project, you should consider using the plugin.
The full README, including how to install and use it, and the code are here:
http://github.com/xing/alter_table
About the Author
Sebastian Röbke works as a Manager Engineering at XING. He loves writing clean, well-tested ruby code. XING Profile »
Timothy Payton works as a Manager Engineering at XING. He loves looking at clean, well-tested ruby code. XING Profile »
Are you aware of change_table? With it there is no need for the repeated table name for changes affecting a single table.
Given change_table, wouldn’t your change be more useful as a patch to the existing functionality?