Manage Your MySQL Database with Rails' Migrations

Dropping a Table Using Migrations

So how do you cause the down method to execute? Because migrations operates much like a version control tool, you can roll the database back to an earlier version. Version numbers are identified by the migration script's prepended number; Because the 001_add_contacts_table.rb script's version number is 001, you can roll those changes back like so:

%>rake db:migrate VERSION=000
(in C:/ruby/addressbook)
== AddContactsTable: migrating ===============================
-- create_table(:contacts)
   -> 0.1250s
== AddContactsTable: migrated (0.1250s) ======================

Execute SHOW TABLES anew and you'll see the contacts table has been dropped!

Modifying a Table Using Migrations

Of course, developers will want an easy means to manage the database tables. Again, all you need to do is create a new migration script. For instance, suppose you want to add a city column to the contacts table:

%>ruby script/generate migration modify_contacts_add_city
       exists  db/migrate
       create  db/migrate/002_modify_contacts_add_city.rb

Open 002_modify_contacts_add_city.rb and modify the file so it looks like this:

class ModifyContactsAddCity < ActiveRecord::Migration
   def self.up
      add_column :contacts, :city, :string
   end

   def self.down
      remove_column :contacts, :city
   end
end

Execute describe contacts anew and you'll see the city column has been added to the table!

mysql> describe contacts;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | YES  |     | NULL    |                |
| email    | varchar(255) | YES  |     | NULL    |                |
| birthday | date         | YES  |     | NULL    |                |
| city     | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Notice I've also implemented the down method; it will remove the city column. To drop the column, just roll the migration back to version 001 like so:

C:\ruby\addressbook>rake db:migrate VERSION=001
(in C:/ruby/addressbook)
== ModifyContactsAddCity: reverting ===========================
-- remove_column(:contacts, :city)
   -> 0.1720s
== ModifyContactsAddCity: reverted (0.1720s) ==================

Conclusion

This tutorial introduced you to just a smattering of what migrations has to offer; it not only supports MySQL, but also popular databases such as SQLite, PostgreSQL, SQL Server, and Oracle. See the migrations documentation on the Rails web site for a complete breakdown of this wonderful tool's capabilities. Although migrations is a Rails-specific feature, there's nothing to prevent you from quickly creating a solution for managing MySQL no matter what web development language you settle upon.

About the Author

W. Jason Gilmore is Apress' Open Source Editor, and co-founder of IT Enlightenment. He's the author of several books, including the best-selling Beginning PHP and MySQL 5: Novice to Professional, Second Edition (Apress, 2006. 913pp.). Jason loves receiving e-mail, so don't hesitate to write him at wjATwjgilmore.com.