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:
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:rubyaddressbook>rake db:migrate VERSION=001 (in C:/ruby/addressbook) == ModifyContactsAddCity: reverting =========================== -- remove_column(:contacts, :city) -> 0.1720s == ModifyContactsAddCity: reverted (0.1720s) ==================
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.