Back to article

Raking MySQL Over Rails

December 19, 2007

I'm a programmer, not a database administrator; I prefer to live in a world of code rather than table schemas and confusing SQL queries. This mindset is one of the reasons I find the Rails framework such an appealing solution; Rails so effectively implements an approach to database integration that plays to the developer's strengths. This approach is most notable in Rails' Active Record implementation, in which the object-relational mapping mechanism allows you to treat database tables like objects.

Yet inevitably, most developers are going to be forced to interact with the database outside of the world of models, controllers, and views. But, you may not be aware of other Rails features at your disposal for performing database-related tasks. For instance, did you know you can create and manage table schemas, populate tables with seed data, and back up your databases from within the confines of your Rails project by using the Rake build tool, which greatly simplifies otherwise complicated tasks such as the aforementioned? In this tutorial, I'll introduce you to just a few of the significant boosts in productivity Rake has to offer you by showing you how to carry out these important, yet often mind-numbing, tasks.

Managing Table Schemas with Migrations

Thanks to Active Record, interacting with a MySQL table via a Rails controller is done as easily as modifying the database.yml file, creating a model, and creating an appropriately named database table. For example, if you create a model named contact, the table name should be contacts. From there, you can begin populating and interacting with the table as easily as instantiating the contact model class and accessing its attributes like any other object. Newcomers to Rails find this feature alone a particularly compelling reason to adopt the framework. But, did you know you can actually manage the table schemas in Rails as well?

This feature is known as Migrations, and like Active Record, is bundled with the standard Rails distribution. Migrations give you the ability to programatically manage table schemas, allowing you to create, modify, and drop tables and columns using a very simple syntax. What's more, Migrations offers built-in version control, enabling you to roll schema versions forward and backwards with a simple command.

In fact, migrations are such a key part of Rails that a migration file is created automatically when you create a new model. If you create the aforementioned contact model, navigate to the project's db directory and you'll see a file named 001_create_contacts.rb that looks like this:

class CreateContacts < ActiveRecord::Migration
   def self.up
      create_table :contacts do |t|

   def self.down
      drop_table :contacts

To create the table schema, you can modify this file to look like this:

class CreateContacts < ActiveRecord::Migration
   def self.up
      create_table :contacts do |t|
         t.column :name, :string, :null => false
         t.column :email, :string
         t.column :phone, :string, :limit => 10, :null => false

   def self.down
      drop_table :contacts

Now, apply the migration by running the following Rake command from within your project directory:

%>rake db:migrate

If you log in to your MySQL server, you'll see that the contacts table has been created! Want to undo your changes? Roll the migration back to the previous version by using the VERSION option. This will execute the self.down method as defined in the migration file:

%>rake db:migrate VERSION=0

Check the database again and you'll see the table has been dropped. What's more, as your schema evolves, you'll naturally create additional migrations to add new tables, or modify column datatypes or constraints, for instance. You can create a new migration file by executing the following command from within your project directory:

%>ruby script/generate migration your_desired_migration_name

Notice how each migration file is prepended with a version number? You can use that number to roll the schema backwards or forwards as far as you please!

An earlier installment of this ongoing series introduced migrations, so if you're not yet familiar with this great feature be sure to check it out.

Use Fixtures to Populate Tables

When creating a new application, chances are you already have at least some data on hand, and need to populate the application database. For instance, you might require customers to identify their state of residence when checking out for billing and shipping purposes. Because state names and abbreviations might be used in multiple places within your application, it makes sense to create a state model and corresponding table. But, you certainly wouldn't want to manually populate this table; not only would that be a tedious, but error prone.

Instead, you can take advantage of fixtures, a Rails feature created with the intent of simplifying the population of sample data into the test database for use during the ongoing testing of your application. However, they also provide a great way to populate tables with seed data such as state names and abbreviations you'll use in all phases of the application, including production. All you need to do is store the data in a format recognized by fixtures, of which there are two: YAML and comma-separated. Because the latter is the more widely-recognized of the two, I'll show you how to create a comma-separated fixture file containing state names and abbreviations and then use Rake to populate the table with this data. I'll presume you've created the state model and migration file, and now want to populate the table.

To begin, keep in mind that you always want to conform to Rails conventions; therefore, these sorts of fixtures shouldn't reside in the project's test/fixtures directory because that data should be used solely for testing purposes. Therefore, consider instead creating a directory named seed (for seed data) within the project's db directory.

Next, create the states.csv file, and store it within the newly created db/seed directory. A shortened version of the file is presented here:

id, name, abbreviation
1, Alabama, AL
2, Alaska, AK
3, Arizona, AZ
4, Arkansas, AR
5, California, CA
6, Colorado, CO

Next, you'll need to create a Rake file. The process and syntax behind doing so is worthy of an article unto itself, so just trust me on this. I'd been using a homebrewed task for some time, but recently came across a much more succinct solution created by Jeffrey Alan Hardy, which I've slightly modified to account for my preferred use of comma-separated seed files. Paste the following code into a file named seeder.task and save it to your Rails project's lib/tasks directory:

namespace :db do
   desc "Load seed fixtures (from db/seed) into the current
         environment's database."
   task :seed => :environment do
      require 'active_record/fixtures'
      Dir.glob(RAILS_ROOT + '/db/fixtures/*.csv').each do |file|
                                  File.basename(file, '.*'))

To populate the states table, execute the following command from within your project directory:

%>rake db:seeder

Check the states table, and you'll see it's been populated!

Migrating Data Between Databases

Ask five developers what the superior database solution is, and you're sure to get five different answers. Like the endless vi/emacs and Linux/Windows arguments, there's never a shortage of opinion when it comes to database adoption. However, reality occasionally can take precedence over preference, and you may find yourself in a position where the client has decided to make a last minute switch to MySQL after you've been developing the application for weeks using PostgreSQL. Due to a variety of inconsistencies among various database solutions, it just isn't as easy to migrate data as one might think.

Noted Rails developer Tobias Lütke encountered a similar problem, and created a great Rake task for dumping database data into YAML format, and repopulating any database supported by Rails Migrations (at the time of writing, MySQL, PostgreSQL, SQLite, SQL Server, Sybase, and Oracle). I won't reproduce the task here because it's rather lengthy. Instead, download it from here and place it into your project's lib/tasks directory.

Next, run the following Rake command to retrieve the data in YAML format.

%>rake db:backup:write

All of the tables found in the current environment's database have been backed up to db/backup! Now, all you need to do is update your database.yml file to point to your new database solution, and then execute the following command to populate the new database:

%>rake db:backup:write

Keep in mind that anything found in that new database will be deleted before the new data is populated!


Rake is an amazing tool capable of doing so much more than what was demonstrated in this tutorial. Be sure to check out the Rake homepage, and this great tutorial for more information about this powerful tool.

About the Author

W. Jason Gilmore is 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 email, so don't hesitate to write him at

Sitemap | Contact Us

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