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.
IE 7
Firefox 2.0
