Chapter 16

Migrations

Rails encourages an agile, iterative style of development. We don’t expect to get everything right the first time. Instead we write tests and interact with our customers to refine our understanding as we go.

For that to work, we need a supporting set of practices. We write tests to help us design our interfaces and to act as a safety net when we change things, and we use version control to store our application’s source files, allowing us to undo mistakes and to monitor what changes day to day.

But there’s another area of the application that changes, an area that we can’t directly manage using version control. The database schema in a Rails application constantly evolves as we progress through the development: we add a table here, rename a column there, and so on. The database changes in step with the application’s code.

Historically, that has been a problem. Developers (or database administrators) make schema changes as needed. However, if the application code is rolled back to a previous version, it was hard to undo the database schema changes to bring the database back in line with that prior application version—the database itself has no versioning information.

Over the years, developers have come up with ways of dealing with this issue. One scheme is to keep the Data Definition Language (DDL) statements that define the schema in source form under version control. Whenever you change the schema, you edit this file to reflect the changes. You then drop your development database and re-create the schema from scratch by applying your DDL. If you need to roll back a week, the application code and the DDL that you check out from the version control system are in step: when you re-create the schema from the DDL, your database will have gone back in time.

Except...because you drop the database every time you apply the DDL, you lose any data in your development database. Wouldn’t it be more convenient

CREATING AND RUNNING MIGRATIONS 263

to be able to apply only those changes that are necessary to move a database from version x to version y? This is exactly what Rails migrations let you do.

Let’s start by looking at migrations at an abstract level. Imagine we have a table of order data. One day, our customer comes in and asks us to add the customer’s e-mail address to the data we capture in an order. This involves a change to the application code and the database schema. To handle this, we create a database migration that says “add an e-mail column to the orders table.” This migration sits in a separate file, which we place under version control alongside all our other application files. We then apply this migration to our database, and the column gets added to the existing orders table.

Exactly how does a migration get applied to the database? It turns out that every migration has a sequence number associated with it. These numbers start at 1—each new migration gets the next available number. Rails remembers the sequence number of the last migration applied to the database. Then, when you ask it to update the schema by applying new migrations, it compares the sequence number of the database schema with the sequence numbers of the available migrations. If it finds migrations with sequence numbers higher than the database schema it applies them, one at a time, and in order.

But how do we revert a schema to a previous version? We do it by making each migration reversible. Each migration actually contains two sets of instructions. One set tells Rails what changes to make to the database when applying the migration and the other set tells Rails how to undo those changes. In our orders table example, the apply part of the migration adds the e-mail column to the table, and the undo part removes that column. Now, to revert a schema, we simply tell Rails the sequence number that we’d like the database schema to be at. If the current database schema has a higher sequence number than this target number, Rails takes the migration with the database’s current sequence number and applies its undo action. This removes the migration’s change from the schema, decrementing the database’s sequence number in the process. It repeats this process until the database reaches the desired version.

16.1Creating and Running Migrations

A migration is simply a Ruby source file in your application’s db/migrate directory. Each migration file’s name starts with (by default) three digits and an underscore. Those digits are the key to migrations, because they define the sequence in which the migrations are applied—they are the individual migration’s version number.

Here’s what the db/migrate directory of our Depot application looks like.

Report erratum

CREATING AND RUNNING MIGRATIONS 264

depot> ls db/migrate

001_create_products.rb 005_create_orders.rb 002_add_price.rb 006_create_line_items.rb 003_add_test_data.rb 007_create_users.rb 004_add_sessions.rb

Although you could create these migration files by hand, it’s easier (and less error prone) to use a generator. As we saw when we created the Depot application, there are actually two generators that create migration files.

•The model generator creates a migration to create the table associated with the model (unless you specify the --skip-migration option). As the example that follows shows, creating a model called discount also creates a migration called ddd_create_discounts.rb.

depot> ruby	script/generate model discount
exists	app/models/
exists	test/unit/
exists	test/fixtures/
create	app/models/discount.rb
create	test/unit/discount_test.rb
create	test/fixtures/discounts.yml
exists	db/migrate
create	db/migrate/014_create_discounts.rb

• You can also generate a migration on its own.

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

create db/migrate/015_add_price_column.rb

Later, starting in Anatomy of a Migration, we’ll see what goes in the migration files. But for now, let’s jump ahead a little in the workflow and see how to run migrations.

Running Migrations

Migrations are run using the db:migrate Rake task.

depot> rake db:migrate

To see what happens next, let’s dive down into the internals of Rails.

The migration code maintains a table called schema_info inside every Rails database. This table has just one column, called version, and it will only ever have one row. The schema_info table is used to remember the current version of the database.

When you run rake db:migrate, the task first looks for the schema_info table. If it doesn’t yet exist, it will be created, and a version number of 0 will be stored in it. If it does exist, the version number is read from it.

Report erratum

ANATOMY OF A MIGRATION 265

The migration code then looks at all the migration files in db/migrate. If any have a sequence number (the leading digits in the filename) greater than the current version of the database, then each is applied, in turn, to the database. After each migration finishes, its version in the schema_info table is updated to its sequence number.

If we were to run migrations again at this point, nothing much would happen. The version number in the database would equal the sequence number of the highest-numbered migration, so there’d be no migrations to apply.

However, if we subsequently create a new migration file, it will have a sequence number one greater than the database version. If we then run migrations, this new migration file will be executed.

You can force the database to parameter to the rake db:migrate

a specific version by supplying the VERSION= command.

depot> rake db:migrate VERSION=23

If the version you give is greater than the database version, migrations will be applied starting at the database version and ending at the version number you supply.

If, however, the version number on the command line is less than the current database version, something different happens. In these circumstances, Rails looks for the migration file whose number matches the database version and undoes it. It then decrements the version, looks for the matching file, undoes it, and so on, until the version number matches the version you specified on the command line. That is, the migrations are unapplied in reverse order to take the schema back to the version that you specify.

16.2Anatomy of a Migration

Migrations are subclasses of the Rails class ActiveRecord::Migration. The class you create should contain at least the two class methods up and down.

class SomeMeaningfulname < ActiveRecord::Migration def self.up

# ...

end

def self.down

# ...

end end

The up method is responsible for applying the schema changes for this migration while the down method undoes those changes. Let’s make this more concrete. Here’s a migration that adds an e_mail column to the orders table.

Report erratum

ANATOMY OF A MIGRATION 266

class AddEmailColumnToOrders < ActiveRecord::Migration def self.up

add_column :orders, :e_mail, :string end

def self.down

remove_column :orders, :e_mail end

end

See how the down method undoes the effect of the up method?

Column Types

The third parameter to add_column specifies the type of the database column. In the previous example we specified that the e_mail column has a type of :string. But just what does this mean? Databases typically don’t have column types of

:string.

Remember that Rails tries to make your application independent of the underlying database: you could develop using MySQL and deploy to Postgres if you wanted. But different databases use different names for the types of columns. If you used a MySQL column type in a migration, that migration might not work if applied to a Postgres database. So Rails migrations insulate you from the underlying database type systems by using logical types. If we’re migrating a MySQL database, the :string type will create a column of type varchar(255). On Postgres, the same migration adds a column with the type char varying(255).

The types supported by migrations are :binary, :boolean, :date, :datetime, :decimal, :float, :integer, :string, :text, :time, and :timestamp. Figure 16.1, on the following page, shows the default mappings of these types for the database adapters in Rails. Using this figure, you could work out that a column declared to be :integer in a migration would have the underlying type int(11) in MySQL and number(38) in Oracle.

You can specify up to three options when defining most columns in a migration; decimal columns take an additional two options. Each of these options is given as a key => value pair. The common options are

:null => true or false

If false, the underlying column has a not null constraint added (if the database supports it).

:limit => size

Sets a limit on the size of the field. This basically appends the string (size) to the database column type definition.

:default => value

Sets the default value for the column. Note that the default is calculated

Report erratum

ANATOMY OF A MIGRATION

267

	db2	mysql	openbase	oracle
:binary	blob(32768)	blob	object	blob
:boolean	decimal(1)	tinyint(1)	boolean	number(1)
:date	date	date	date	date
:datetime	timestamp	datetime	datetime	date
:decimal	decimal	decimal	decimal	decimal
:float	float	float	float	number
:integer	int	int(11)	integer	number(38)
:string	varchar(255)	varchar(255)	char(4096)	varchar2(255)
:text	clob(32768)	text	text	clob
:time	time	time	time	date
:timestamp	timestamp	datetime	timestamp	date

	postgresql	sqlite	sqlserver	sybase
:binary	bytea	blob	image	image
:boolean	boolean	boolean	bit	bit
:date	date	date	datetime	datetime
:datetime	timestamp	datetime	datetime	datetime
:decimal	decimal	decimal	decimal	decimal
:float	float	float	float(8)	float(8)
:integer	integer	integer	int	int
:string	(note 1)	varchar(255)	varchar(255)	varchar(255)
:text	text	text	text	text
:time	time	datetime	datetime	time
:timestamp	timestamp	datetime	datetime	timestamp

Note 1: character varying(256)

Figure 16.1: Migration and Database Column Types

Report erratum

ANATOMY OF A MIGRATION 268

once, at the point the migration is run, so the following code will set the default column value to the date and time when the migration was run.1

add_column :orders, :placed_at, :datetime, :default => Time.now

In addition, decimal columns take the options :precision and :scale. The precision option specifies the number of significant digits that will be stored, and the scale option determines where the decimal point will be located in these digits (think of the scale as the number of digits after the decimal point). A decimal number with a precision of 5 and a scale of 0 can store numbers from -99,999 to +99,999. A decimal number with a precision of 5 and a scale of 2 can store the range -999.99 to +999.99.

The :precision and :scale parameters are optional for decimal columns. However, incompatibilities between different databases lead us to strongly recommend that you include the options for each decimal column.

Here are some column definitions using the migration types and options.

add_column	:orders,	:name,	:string, :limit => 100, :null => false
add_column	:orders,	:age,	:integer
add_column	:orders,	:ship_class, :string, :limit => 15, :default		=> 'priority'
add_column	:orders,	:price, :decimal, :precision => 8, :scale => 2
add_column	:meter,	:reading, :decimal, :precision => 24, :scale => 0

Renaming Columns

When we refactor our code, we often change our variable names to make them more meaningful. Rails migrations allow us to do this to database column names, too. For example, a week after we first added it, we might decide that e_mail isn’t the best name for the new column. We can create a migration to rename it using the rename_column method.

class RenameEmailColumn < ActiveRecord::Migration def self.up

rename_column :orders, :e_mail, :customer_email end

def self.down

rename_column :orders, :customer_email, :e_mail end

end

Note that the rename doesn’t destroy any existing data associated with the column. Also be aware that renaming is not supported by all the adapters.

Changing Columns

Use the change_column method to change the type of a column or to alter the options associated with a column. Use it the same way you’d use add_column,

1. If you want a column to default to having the date and time its row was inserted, simply make it a datetime and name it created_at.

Report erratum