March 3, 2021
Hot Topics:

Manage Your MySQL Database with Rails' Migrations

  • By W. Jason Gilmore
  • Send Email »
  • More Articles »

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:

   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

   def self.down

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

   def self.down
      drop_table :contacts

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.

Page 1 of 2

This article was originally published on September 24, 2007

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date