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| end end def self.down drop_table :contacts end end
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 end end def self.down drop_table :contacts end end
Now, apply the migration by running the following Rake command from within your project directory:
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.