Rails Database Migrations

Migrations are a convenient way for you to alter your database in a structured and organised manner. You could edit fragments of SQL by hand but you would then be responsible for telling other developers that they need to go and run it. You’d also have to keep track of which changes need to be run against the production machines next time you deploy.

Active Record tracks which migrations have already been run so all you have to do is update your source and run rake db:migrate. Active Record will work out which migrations should be run. It will also update your db/schema.rb file to match the structure of your database.

Migrations also allow you to describe these transformations using Ruby. The great thing about this is that (like most of Active Record’s functionality) it is database independent: you don’t need to worry about the precise syntax of CREATE TABLE any more that you worry about variations on SELECT * (you can drop down to raw SQL for database specific features). For example you could use SQLite3 in development, but MySQL in production.

You’ll learn all about migrations including:

* The generators you can use to create them

* The methods Active Record provides to manipulate your database

* The Rake tasks that manipulate them

* How they relate to schema.rb

1 Anatomy of a Migration

Before I dive into the details of a migration, here are a few examples of the sorts of things you can do:

class CreateProducts < ActiveRecord::Migration def self.up create_table :products do |t| t.string :name t.text :description t.timestamps end end def self.down drop_table :products end end

This migration adds a table called products with a string column called name and a text column called description. A primary key column called id will also be added, however since this is the default we do not need to ask for this. The timestamp columns created_at and updated_at which Active Record populates automatically will also be added. Reversing this migration is as simple as dropping the table.

Migrations are not limited to changing the schema. You can also use them to fix bad data in the database or populate new fields:

class AddReceiveNewsletterToUsers < ActiveRecord::Migration def self.up change_table :users do |t| t.boolean :receive_newsletter, :default => false end User.update_all ["receive_newsletter = ?", true] end def self.down remove_column :users, :receive_newsletter end end

This migration adds a receive_newsletter column to the users table. We want it to default to false for new users, but existing users are considered to have already opted in, so we use the User model to set the flag to true for existing users.

Some caveats apply to using models in your migrations.

1.1 Migrations are Classes

A migration is a subclass of ActiveRecord::Migration that implements two class methods: up (perform the required transformations) and down (revert them).

Active Record provides methods that perform common data definition tasks in a database independent way (you’ll read about them in detail later):

* create_table

* change_table

* drop_table

* add_column

* change_column

* rename_column

* remove_column

* add_index

* remove_index

If you need to perform tasks specific to your database (for example create a foreign key constraint) then the execute function allows you to execute arbitrary SQL. A migration is just a regular Ruby class so you’re not limited to these functions. For example after adding a column you could write code to set the value of that column for existing records (if necessary using your models).

On databases that support transactions with statements that change the schema (such as PostgreSQL), migrations are wrapped in a transaction. If the database does not support this (for example MySQL and SQLite) then when a migration fails the parts of it that succeeded will not be rolled back. You will have to unpick the changes that were made by hand.

1.2 What’s in a Name

Migrations are stored in files in db/migrate, one for each migration class. The name of the file is of the form YYYYMMDDHHMMSS_create_products.rb, that is to say a UTC timestamp identifying the migration followed by an underscore followed by the name of the migration. The migration class’ name must match (the camelcased version of) the latter part of the file name. For example 20080906120000_create_products.rb should define CreateProducts and 20080906120001_add_details_to_products.rb should define AddDetailsToProducts. If you do feel the need to change the file name then you have to update the name of the class inside or Rails will complain about a missing class.

Internally Rails only uses the migration’s number (the timestamp) to identify them. Prior to Rails 2.1 the migration number started at 1 and was incremented each time a migration was generated. With multiple developers it was easy for these to clash requiring you to rollback migrations and renumber them. With Rails 2.1 this is largely avoided by using the creation time of the migration to identify them. You can revert to the old numbering scheme by setting config.active_record.timestamped_migrations to false in config/environment.rb.

The combination of timestamps and recording which migrations have been run allows Rails to handle common situations that occur with multiple developers.

For example Alice adds migrations 20080906120000 and 20080906123000 and Bob adds 20080906124500 and runs it. Alice finishes her changes and checks in her migrations and Bob pulls down the latest changes. Rails knows that it has not run Alice’s two migrations so rake db:migrate would run them (even though Bob’s migration with a later timestamp has been run), and similarly migrating down would not run their down methods.

Of course this is no substitution for communication within the team. For example, if Alice’s migration removed a table that Bob’s migration assumed to exist, then trouble would certainly strike.

1.3 Changing Migrations

Occasionally you will make a mistake when writing a migration. If you have already run the migration then you cannot just edit the migration and run the migration again: Rails thinks it has already run the migration and so will do nothing when you run rake db:migrate. You must rollback the migration (for example with rake db:rollback), edit your migration and then run rake db:migrate to run the corrected version.

In general editing existing migrations is not a good idea: you will be creating extra work for yourself and your co-workers and cause major headaches if the existing version of the migration has already been run on production machines. Instead you should write a new migration that performs the changes you require. Editing a freshly generated migration that has not yet been committed to source control (or more generally which has not been propagated beyond your development machine) is relatively harmless. Just use some common sense.

2 Creating a Migration

2.1 Creating a Model

The model and scaffold generators will create migrations appropriate for adding a new model. This migration will already contain instructions for creating the relevant table. If you tell Rails what columns you want then statements for adding those will also be created. For example, running

ruby script/generate model Product name:string description:text

will create a migration that looks like this

class CreateProducts < ActiveRecord::Migration def self.up create_table :products do |t| t.string :name t.text :description t.timestamps end end def self.down drop_table :products end end

You can append as many column name/type pairs as you want. By default t.timestamps (which creates the updated_at and created_at columns that are automatically populated by Active Record) will be added for you.

2.2 Creating a Standalone Migration

If you are creating migrations for other purposes (for example to add a column to an existing table) then you can use the migration generator:

ruby script/generate migration AddPartNumberToProducts

This will create an empty but appropriately named migration:

class AddPartNumberToProducts < ActiveRecord::Migration def self.up end def self.down end end

If the migration name is of the form “AddXXXToYYY” or “RemoveXXXFromYYY” and is followed by a list of column names and types then a migration containing the appropriate add_column and remove_column statements will be created.

ruby script/generate migration AddPartNumberToProducts part_number:string

will generate

class AddPartNumberToProducts < ActiveRecord::Migration def self.up add_column :products, :part_number, :string end def self.down remove_column :products, :part_number end end

Similarly,

ruby script/generate migration RemovePartNumberFromProducts part_number:string

generates

class RemovePartNumberFromProducts < ActiveRecord::Migration def self.up remove_column :products, :part_number end def self.down add_column :products, :part_number, :string end end

You are not limited to one magically generated column, for example

ruby script/generate migration AddDetailsToProducts part_number:string price:decimal

generates

class AddDetailsToProducts < ActiveRecord::Migration def self.up add_column :products, :part_number, :string add_column :products, :price, :decimal end def self.down remove_column :products, :price remove_column :products, :part_number end end

As always, what has been generated for you is just a starting point. You can add or remove from it as you see fit.

3 Writing a Migration

Once you have created your migration using one of the generators it’s time to get to work!

3.1 Creating a Table

Migration method create_table will be one of your workhorses. A typical use would be

create_table :products do |t| t.string :name end

which creates a products table with a column called name (and as discussed below, an implicit id column).

The object yielded to the block allows you create columns on the table. There are two ways of doing this: The first (traditional) form looks like

create_table :products do |t| t.column :name, :string, :null => false end

the second form, the so called “sexy” migration, drops the somewhat redundant column method. Instead, the string, integer, etc. methods create a column of that type. Subsequent parameters are the same.

create_table :products do |t| t.string :name, :null => false end

By default create_table will create a primary key called id. You can change the name of the primary key with the :primary_key option (don’t forget to update the corresponding model) or if you don’t want a primary key at all (for example for a HABTM join table) you can pass :id => false. If you need to pass database specific options you can place an SQL fragment in the :options option. For example

create_table :products, :options => "ENGINE=BLACKHOLE" do |t| t.string :name, :null => false end

will append ENGINE=BLACKHOLE to the SQL statement used to create the table (when using MySQL the default is ENGINE=InnoDB).

The types supported by Active Record are :primary_key, :string, :text, :integer, :float, :decimal, :datetime, :timestamp, :time, :date, :binary, :boolean.

These will be mapped onto an appropriate underlying database type, for example with MySQL :string is mapped to VARCHAR(255). You can create columns of types not supported by Active Record when using the non-sexy syntax, for example

create_table :products do |t| t.column :name, 'polygon', :null => false end

This may however hinder portability to other databases.

3.2 Changing Tables

A close cousin of create_table is change_table, used for changing existing tables. It is used in a similar fashion to create_table but the object yielded to the block knows more tricks. For example

change_table :products do |t| t.remove :description, :name t.string :part_number t.index :part_number t.rename :upccode, :upc_code end

removes the description and name columns, creates a part_number column and adds an index on it. Finally it renames the upccode column. This is the same as doing

remove_column :products, :description remove_column :products, :name add_column :products, :part_number, :string add_index :products, :part_number rename_column :products, :upccode, :upc_code

You don’t have to keep repeating the table name and it groups all the statements related to modifying one particular table. The individual transformation names are also shorter, for example remove_column becomes just remove and add_index becomes just index.

3.3 Special Helpers

Active Record provides some shortcuts for common functionality. It is for example very common to add both the created_at and updated_at columns and so there is a method that does exactly that:

create_table :products do |t| t.timestamps end

will create a new products table with those two columns (plus the id column) whereas

change_table :products do |t| t.timestamps end

adds those columns to an existing table.

The other helper is called references (also available as belongs_to). In its simplest form it just adds some readability

create_table :products do |t| t.references :category end

will create a category_id column of the appropriate type. Note that you pass the model name, not the column name. Active Record adds the _id for you. If you have polymorphic belongs_to associations then references will add both of the columns required:

create_table :products do |t| t.references :attachment, :polymorphic => {:default => 'Photo'} end

will add an attachment_id column and a string attachment_type column with a default value of ‘Photo’.

The references helper does not actually create foreign key constraints for you. You will need to use execute for that or a plugin that adds foreign key support.

If the helpers provided by Active Record aren’t enough you can use the execute function to execute arbitrary SQL.