DATA MIGRATIONS 274

Tables with No Primary Key

Sometimes you may need to define a table that has no primary key. The most common case in Rails is for join tables—tables with just two columns where each column is a foreign key to another table. To create a join table using migrations, you have to tell Rails not to automatically add an id column.

create_table :authors_books, :id => false do |t| t.column :author_id, :integer, :null => false

t.column :book_id,	:integer, :null => false
end

In this case, you might want to investigate creating one or more indices on this table to speed navigation between books and authors.

16.4Data Migrations

Migrations are just Ruby code; they can do anything you want. And, because they’re also Rails code, they have full access to the code you’ve already written in your application. In particular, migrations have access to your model classes. This makes it easy to create migrations that manipulate the data in your development database.

Let’s look at two different scenarios where it’s useful to manipulate data in migrations: loading development data and migrating data between versions of your application.

Loading Data with Migrations

Most of our applications require a fair amount of background information to be loaded into the database before we can meaningfully play with them, even during development. If we’re writing an online store, we’ll need product data. We might also need information on shipping rates, user profile data, and so on. In the old days, developers used to hack this data into their databases, often by typing SQL insert statements by hand. This was hard to manage and tended not to be repeatable. It also made it hard for developers joining the project halfway through to come up to speed.

Migrations make this a lot easier. On virtually all my Rails projects, I find myself creating data-only migrations—migrations that load data into an existing schema rather than changing the schema itself.

Note that we’re talking here about creating data that’s a convenience for the developer when they play with the application and for creating “fixed” data such as lookup tables. You’ll still want to create fixtures containing data specific to tests.

Here’s a typical data-only migration drawn from the Rails application for the new Pragmatic Bookshelf store.

Report erratum

DATA MIGRATIONS 275

class TestDiscounts < ActiveRecord::Migration def self.up

down

rails_book_sku = Sku.find_by_sku("RAILS-B-00" ) ruby_book_sku = Sku.find_by_sku("RUBY-B-00") auto_book_sku = Sku.find_by_sku("AUTO-B-00")

discount = Discount.create(:name => "Rails + Ruby Paper", :action => "DEDUCT_AMOUNT" , :amount => "15.00")

discount.skus = [rails_book_sku, ruby_book_sku] discount.save!

discount = Discount.create(:name => "Automation Sale", :action => "DEDUCT_PERCENT" , :amount => "5.00")

discount.skus = [auto_book_sku] discount.save!

end

def self.down Discount.delete_all

end end

Notice how this migration uses the full power of my existing Active Record classes to find existing SKUs, create new discount objects, and knit the two together. Also, notice the subtlety at the start of the up method—it initially calls the down method, and the down method in turn deletes all rows from the discounts table. This is a common pattern with data-only migrations.

Loading Data from Fixtures

Fixtures normally contain data to be used when running tests. However, with a little extra plumbing, we can also use them to load data during a migration.

To illustrate the process, let’s assume our database has a new users table. We’ll define it with the following migration.

class AddUsers < ActiveRecord::Migration def self.up

create_table :users do |t| t.column :name, :string t.column :status, :string

end end

def self.down drop_table :users

end end

Report erratum

DATA MIGRATIONS 276

Let’s create a subdirectory under db/migrate to hold the data we’ll be loading in to our development database. Let’s call that directory dev_data.

depot> mkdir db/migrate/dev_data

In that directory we’ll create a YAML file containing the data we want to load into our users table. We’ll call that file users.yml.

dave:

name: Dave Thomas status: admin

mike:

name: Mike Clark status: admin

fred:

name: Fred Smith status: audit

Now we’ll generate a migration to load the data from this fixture into our development database.

depot> ruby script/generate migration load_users_data exists db/migrate

create db/migrate/0xx_load_users_data.rb

And finally we’ll write the code in the migration that loads data from the fixture. This is slightly magical, because it relies on a backdoor interface into the Rails fixture code.

require 'active_record/fixtures'

class LoadUserData < ActiveRecord::Migration def self.up

down

directory = File.join(File.dirname(__FILE__), "dev_data") Fixtures.create_fixtures(directory, "users")

end

def self.down User.delete_all

end end

The first parameter to create_fixtures is the path to the directory containing the fixture data. We make it relative to the migration file’s path, because we store the data in a subdirectory of migrations.

Be warned: the only data you should load in migrations is data that you’ll also want to see in production: lookup tables, predefined users, and the like. Do not load test data into your application this way.

Report erratum

ADVANCED MIGRATIONS 277

Migrating Data with Migrations

Sometimes a schema change also involves migrating data. For example, at the start of a project you might have a schema that stores prices using a float. However, if you later bump into rounding issues, you might want to change to storing prices as an integer number of cents.

If you’ve been using migrations to load data into your database, then that’s not a problem: just change the migration file so that rather than loading 12.34 into the price column, you instead load 1234. But if that’s not possible, you might instead want to perform the conversion inside the migration.

One way is to multiply the existing column values by 100 before changing the column type.

class ChangePriceToInteger < ActiveRecord::Migration def self.up

Product.update_all("price = price * 100") change_column :products, :price, :integer end

def self.down

change_column :products, :price, :float Product.update_all("price = price / 100.0") end

end

Note how the down migration undoes the change by doing the division only after the column is changed back.

16.5Advanced Migrations

Most Rails developers use the basic facilities of migrations to create and maintain their database schemas. However, every now and then it’s useful to push migrations just a bit further. This section covers some more advanced migration usage.

Using Native SQL

Migrations give you a database-independent way of maintaining your application’s schema. However, if migrations don’t contain the methods you need to be able to do what you need to do, you’ll need to drop down to database-specific code. To do this, use the execute method.

A common example in my migrations is the addition of foreign key constraints to a child table. We saw this when we created the line_items table.

Download depot_r/db/migrate/006_create_line_items.rb

class CreateLineItems < ActiveRecord::Migration def self.up

create_table :line_items do |t|

t.column :product_id, :integer, :null => false

Report erratum

			ADVANCED MIGRATIONS	278
t.column	:order_id,	:integer,	:null => false
t.column	:quantity,	:integer,	:null => false
t.column	:total_price,	:decimal,	:null => false, :precision => 8, :scale => 2
end
execute "alter table line_items
	add constraint fk_line_item_products
	foreign key	(product_id) references products(id)"
execute "alter table line_items
	add constraint fk_line_item_orders
	foreign key	(order_id)	references orders(id)"
end
def self.down
drop_table	:line_items
end

end

When you use execute, you might well be tying your migration to a specific database engine: SQL you pass as a parameter to execute uses your database’s native syntax.

The execute method takes an optional second parameter. This is prepended to the log message generated when the SQL is executed.

Extending Migrations

If you look at the line item migration in the preceding section, you might wonder about the duplication between the two execute statements. It would be nice to abstract the creation of foreign key constraints into a helper method.

We could do this by adding a method such as the following to our migration source file.

def self.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

(The self. is necessary because migrations run as class methods, and we need to call foreign_key in this context.)

Within the up migration, we can call this new method using

def self.up create_table ... do end

foreign_key(:line_items, :product_id, :products) foreign_key(:line_items, :order_id, :orders)

end

Report erratum