Manage Your MySQL Database with Rails' Migrations

Over the past ten years, I've created and managed perhaps hundreds of MySQL databases, and in the process have used all manner of tools for facilitating the process. Among my stable of preferred tools include PHPMyAdmin, a web-based table and data manager, and even the MySQL client, a command-line interface that helps you get the job done quickly and with minimum distraction. Yet I'm a programmer at heart, and not a database administrator, and accordingly I've always thought there was an unnatural disconnect between code and database management. Why can't database structures be managed much in the same way as code, programatically and using revision control? Why must multiple development tools be used for a single application? This luxury has never really been possible—that is, until I started using Rails. Rails' migrations feature was expressly built for this purpose, turning my disdain for database management into pure delight.

Migrations allows you to manage your database schemas using the Ruby language, and take advantage of Ruby-specific tools such as Rake to update the database according to the instructions provided by these Ruby scripts. Further, migrations has a built-in version control feature, allowing you to roll your changes backwards and forwards much like you would do with Subversion or CVS. Sounds appealing, right? In this article, I'll guide you through several key migrations concepts and show you how to manage your MySQL database more effectively than ever.

To begin, create a new Rails project named addressbook:

%>rails addressbook

Creating the Database

You'll next need to create the databases Rails requires for each stage of an application's development lifecycle, in this case addressbook_development, addressbook_test, and addressbook_production:

mysql> create database addressbook_development;
Query OK, 1 row affected (0.00 sec)

mysql> create database addressbook_test;
Query OK, 1 row affected (0.00 sec)

mysql> create database addressbook_production;
Query OK, 1 row affected (0.00 sec)

Finally, for Rails to be able to talk to the databases, open the application's database.yml file and modify the authorization parameters. For the purposes of this article, I'll use just the addressbook_development database and will therefore just reproduce that part of the database.yml file here:

development:
   adapter: mysql
   database: addressbook_development
   username: addressbook_user
   password: secret
   host: localhost

With that done, you're ready to begin using migrations!

Creating Your First Migration Script

The first migration script will perform table creation and deletion functions. To create this script, execute the following command:

%>ruby script/generate migration add_contacts_table
create  db/migrate
create  db/migrate/001_add_contacts_table.rb

Executing this command will create the class skeleton used to create and drop a table named contacts. But, you're responsible for implementing these features. To do so, open up the 001_add_contacts_table.rb file, which looks like this:

class AddContactsTable < ActiveRecord::Migration
   def self.up
   end

   def self.down
   end
end

Because the purpose of this class is to create the table, you'll use the up method to create the table and add the columns, and the down method to drop the table. Therefore, modify these two methods to look like this:

class AddContactsTable < ActiveRecord::Migration
   def self.up
      create_table :contacts do |table|
             table.column :name, :string
             table.column :email, :string
             table.column :birthday, :date
      end
   end

   def self.down
      drop_table :contacts
   end
end

Each table.column call is responsible for adding a new column to the table. Two parameters are passed, the first being the column name, and the second the column data type. Note that you don't have to specify the column size, although it's supported using the :limit option; if :limit isn't supplied, the maximum allowable size will be used. Also, add_column supports all of the typical data types, among them text, float, decimal, datetime, timestamp, and boolean. The ability to assign default values and designate columns as NOT NULL is also supported.

Now, execute the following command; it will tell Rails to use the development database, and create the contacts table. If you're running Linux, use export in lieu of Window's set command:

%>set RAILS_ENV=development
%>rake db:migrate

Presuming the command executes without error, you won't see any output. Therefore, to view the results, log in to your MySQL database, select the addressbook_development database, and execute DESCRIBE contacts, producing the following:

+----------+--------------+------+-----+---------+----------------+
| 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    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

You'll see an id field has been created automatically, despite not being specified in the table creation statement. This is a great example of Rails taking care of conventional tasks for you, leaving you to focus on more important matters. Also, you're not limited to creating a single table within a migration; just add multiple create_table blocks to the up method as necessary.

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.