WHEN MIGRATIONS GO BAD 
279
However, we may want to go a step further and make our foreign_key method available to all our migrations. To do this, create a module in the application’s lib directory, and add the foreign_key method. This time, however, make it a regular instance method, not a class method.
module MigrationHelpers
def foreign_key(from_table, from_column, to_table) constraint_name = "fk_#{from_table}_#{from_column}"
execute %{alter table #{from_table}
add constraint #{constraint_name} foreign key (#{from_column}) references #{to_table}(id)}
end end
You can now add this to any migration by adding the following lines to the top of your migration file.
require "migration_helpers"
class CreateLineItems < ActiveRecord::Migration
extend MigrationHelpers
The require line brings the module definition into the migration’s code, and the extend line adds the methods in the MigrationHelpers module into the migration as class methods. You can use this technique to develop and share any number of migration helpers.
(And, if you’d like to make your life even easier, someone has written a plugin3 that automatically handles adding foreign key constraints.)
16.6When Migrations Go Bad
Migrations suffer from one serious problem. The underlying DDL statements that update the database schema are not transactional. This isn’t a failing in Rails—most databases just don’t support the rolling back of create table, alter table, and other DDL statements.
Let’s look at a migration that tries to add two tables to a database.
class ExampleMigration < ActiveRecord::Migration def self.up
create_table :one do ...
end
create_table :two do ...
end end
3. http://www.redhillconsulting.com.au/rails_plugins.html
Report erratum
SCHEMA MANIPULATION OUTSIDE MIGRATIONS 
280
def self.down drop_table :two drop_table :one
end end
In the normal course of events, the up method adds tables one and two, and the down method removes them.
But what happens if there’s a problem creating the second table? We’ll end up with a database containing table one but not table two. We can fix whatever the problem is in the migration, but now we can’t apply it—if we try, it will fail because table one already exists.
We could try to roll the migration back, but that won’t work: because the original migration failed, the schema version in the database wasn’t updated, so Rails won’t try to roll it back.
At this point, you could mess around and manually change the schema information and drop table one. But it probably isn’t worth it. Our recommendation in these circumstances is simply to drop the entire database, re-create it, and apply migrations to bring it back up-to-date. You’ll have lost nothing, and you’ll know you have a consistent schema.
All this discussion suggests that migrations are dangerous to use on production databases. I suggest that as a minimum you should back any production database up before running a migration against it. You’ll need to research on your own how to make a migration run in production—I’d rather not say here.
16.7Schema Manipulation Outside Migrations
All of the migration methods described so far in this chapter are also available as methods on Active Record connection objects and so are accessible within the models, views, and controllers of a Rails application.
For example, you might have discovered that a particular long-running report runs a lot faster if the orders table has an index on the city column. However, that index isn’t needed during the day-to-day running of the application, and tests have shown that maintaining it slows the application appreciably.
Let’s write a method that creates the index, runs a block of code, and then drops the index. This could be a private method in the model or could be implemented in a library.
def run_with_index(column) connection.add_index(:orders, column) begin
yield
Report erratum
MANAGING MIGRATIONS 
281
ensure
connection.remove_index(:orders, column) end
end
The statistics-gathering method in the model can use this as follows.
def get_city_statistics run_with_index(:city) do
# .. calculate stats end
end
16.8Managing Migrations
There’s a downside to migrations. Over time, your schema definition will be spread across a number of separate migration files, with many files potentially affecting the definition of each table in your schema. When this happens, it becomes difficult to see exactly what each table contains. Here are some suggestions for making life easier.
One answer is to look at the file db/schema.rb. After a migration is run, this file will contain the entire database definition in Ruby form.
Alternatively, some teams don’t use separate migrations to capture all the versions of a schema. Instead, they keep a migration file per table and other migration files to load development data into those tables. When they need to change the schema (say to add a column to a table), they edit the existing migration file for that table. They then drop and re-create the database and reapply all the migrations. Following this approach, they can always see the total definition of each table by looking at that table’s migration file.
To make this work in practice, each member of the team needs to keep an eye on the files that are modified when updating their local source code from the project’s repository. When a migration file changes, it’s a sign that the database schema needs to be re-created.
Although it seems like this scheme flies against the spirit of migrations, it actually works well in practice.
Another approach is to use migrations the way we described earlier in the chapter, creating a new migration for each change to the schema. To keep track of the schema as it evolves, you can use the annotate_models plugin. When run, this plugin looks at the current schema and adds a description of each table to the top of the model file for that table.
Install the annotate_models plugin using the following command (which has been split onto two lines to make it fit the page).
Report erratum
MANAGING MIGRATIONS 
282
depot> ruby script/plugin install \ http://svn.pragprog.com/Public/plugins/annotate_models
Once installed, you can run it at any time using
depot> rake annotate_models
After this completes, each model source file will have a comment block that documents the columns in the corresponding database table. For example, in our Depot application, the file line_item.rb would start with
#Schema as of June 12, 2006 15:45 (schema version 7)
#Table name: line_items
# |
|
|
|
|
# |
id |
:integer(11) |
not null, primary key |
|
# |
product_id |
:integer(11) |
default(0), |
not null |
# |
order_id |
:integer(11) |
default(0), |
not null |
# |
quantity |
:integer(11) |
default(0), |
not null |
# |
total_price |
:integer(11) |
default(0), |
not null |
# |
|
|
|
|
class LineItem < ActiveRecord::Base
# ...
If you subsequently change the schema, just rerun the Rake task: the comment block will be updated to reflect the current state of the database.
Report erratum
Chapter 17
Active Record Part I:
The Basics
Active Record is the object-relational mapping (ORM) layer supplied with Rails. In this chapter, we’ll look at the basics—connecting to databases, mapping tables, and manipulating data. We’ll look at using Active Record to manage table relationships in the next chapter and dig into the Active Record object life cycle (including validation and filters) in the chapter after that.
Active Record closely follows the standard ORM model: tables map to classes, rows to objects, and columns to object attributes. It differs from most other ORM libraries in the way it is configured. By using a sensible set of defaults, Active Record minimizes the amount of configuration that developers perform. To illustrate this, here’s a stand-alone program that uses Active Record to wrap a table of orders in a MySQL database. After finding the order with a particular id, it modifies the purchaser’s name and saves the result back in the database, updating the original row.1
require "rubygems" require_gem "activerecord"
ActiveRecord::Base.establish_connection(:adapter => "mysql", :host => "localhost", :database => "railsdb")
class Order < ActiveRecord::Base end
order = Order.find(123) order.name = "Dave Thomas" order.save
1. The examples in this chapter connect to various MySQL databases on the machines we used while writing this book. You’ll need to adjust the connection parameters to get them to work with your database. We discuss connecting to a database in Section 17.4, Connecting to the Database, on page 290.