Ruby on Rails/ActiveRecord/Migrations

Migrations

Migrations meant to solve the problem of rolling out changes to your database. By defining the changes to your database schema in Ruby files, development teams can ensure that all changes to the database are properly versioned. Additionally migrations help to ensure that rolling out changes to fellow developers as well as other servers (development, QA, production) is handled in a consistent and manageable fashion.

Building a Migration

You can either build the migration on its own using

ruby script/generate migration Category

and write the specific commands afterwards (if you want to create custom SQL, this is the way to go) or you can create a model that comes with the migration using

ruby script/generate model Category name:string amount:integer

The console tells you that there were some files created and some already existent. As mentioned before, Rails will never overwrite existing files unless stated otherwise.

Now lets take a look at the migration

# 20090409120944_create_categories.rb
class CreateCategories < ActiveRecord::Migration
  def self.up
    create_table :categories do |t|
      t.string :name
      t.integer :amount

      t.timestamps
    end
  end

  def self.down
    drop_table :categories
  end
end

First of all, take a look at the number (20090409120944) in front of your file. This is the timestamp of your file and important for the creation of the database tables. This timestamp will always be different, depending on the exact time of the creation of your migration. The idea behind this is to have a "history" of all your migrations available.

But why is this important?

Imagine that you work on a Rails project and you create tables, alter columns or remove columns from your database via migrations. After some time, your client changes his mind and he wants only very basic features and you already started to create advanced features and altered the database. Because you can't remember all the changes that went into the database and their order, you will either spend a lot of time working on the database to have the "old" state available or you have to start from scratch because it would take too long to remember and redo all changes. This is where migration come in handy, because of the timestamp, Rails is able to recognize the changes in their actual order and all changes can be undone easily. Never alter the timestamp manually. This will certainly cause problems. For more on those topic, check out the "managing migrations" section

Speaking of undoing and redoing: notice the two methods inside your migration self.up and self.down. Both of them do exactly the opposite of each other. While self.up creates our categories table with all columns, self.down removes (drops) the table from the database with all its contents(!!). When Rails sees that the migration has not been moved to the database, it will use the self.up method, if you undo the migration, the self.down method gets executed. This way you can make sure that you will always be able to go back to a past state of your database. Keep in mind when writing own migrations always include a self.up and a self.down method to assure that the database state will be consistent after an rollback.

OK, let's start with the migration itself:

create_table :categories do |t|
      t.string :name
      t.integer :amount
      t.timestamps
end

We want to create a table called categories(create_table :categories) that has a name and an amount column. Additionally Rails adds an timestamp for us where it will store the creation date and the update date for each row. Rails will also create an primary key called model_id that auto-increments (1,2,3,...) with every row.

You can choose from a variety of datatypes that go with ActiveRecord. The most common types are:

• string
• text
• integer
• decimal
• timestamp
• references
• boolean

But wait, there is not yet a single table nor column in our database. We need to write the migration file into the database.

rake db:migrate

handles this job. The command is able to create the table and all the necessary columns inside the table. This command is not limited to migrating a single file so you can migrate an unlimited number of files at once. Rake also knows what migrations are already in the database so it won't overwrite your tables. For more info see "Managing Migrations".

To add a connection between your tables we want to add references in our model. References are comparable to foreign keys (Rails doesn't use foreign keys by default because not all databases can handle foreign keys but you can write custom SQL to make use of foreign keys) and tell your table where to look for further data.

Let's add another model to our already existent database. We want to create a category that has multiple products. So we need to reference this product in our category. We want to create a model:

ruby script/generate model Products name:string category:references

and insert it into the database

rake db:migrate

Note the type :references for the category. This tells Rails to create a column inside the database that holds a reference to our category. Inside our database there is now a category_id column for our product. (In order to work with these two models, we need to add associations inside our models, see Associations)

Managing Migrations

We already talked about how migrations can help you to organise your database in a very convenient manner. Now we will take a look at how this is achieved. You already know that the timestamp in the filename tells rails when the migration was created and Rake know what migrations are already inside the database.

To restore the state of the database as it was, say 5 migrations before the current, we can use

 $rake db:rollback STEP=5

This will undo the last 5 migrations that have been committed to the database.

To redo the last 5 steps, we can use a similar command

 $ rake db:migrate:redo STEP=5

You can also rollback or redo a specific version of a migration state, you just need to provide the timestamp:

 $ rake db:migrate:up VERSION=20080906120000

Choose whether you want the db_migrate:up method to be executed or the db_migrate:down method

Keep in mind, that restoring your database to a previous state will delete already inserted data completely!

Schema Method Reference

The following methods are available to define your schema changes in Ruby:

• create_table(name, options): Creates a table called name and makes the table object available to a block that adds columns to it, following the same format as add_column. See example above. The options hash is for fragments like "DEFAULT CHARSET=UTF-8" that are appended to the create table definition.
• drop_table(name): Drops the table called name.
• rename_table(old_name, new_name): Renames the table called old_name to new_name.
• add_column(table_name, column_name, type, options): Adds a new column to the table called table_name named column_name specified to be one of the following types: :string, :text, :integer, :float, :datetime, :timestamp, :time, :date, :binary, :boolean. A default value can be specified by passing an options hash like { :default => 11 }.
• rename_column(table_name, column_name, new_column_name): Renames a column but keeps the type and content.
• change_column(table_name, column_name, type, options): Changes the column to a different type using the same parameters as add_column.
• remove_column(table_name, column_name): Removes the column named column_name from the table called table_name.
• add_index(table_name, column_names, index_type, index_name): Add a new index with the name of the column, or index_name (if specified) on the column(s). Specify an optional index_type (e.g. UNIQUE).
• remove_index(table_name, index_name): Remove the index specified by index_name.

Command Reference

• rake db:create[:all]: If :all not specified then create the database defined in config/database.yml for the current RAILS_ENV. If :all is specified then create all of the databases defined in config/database.yml.
• rake db:fixtures:load: Load fixtures into the current environment's database. Load specific fixtures using FIXTURES=x,y
• rake db:migrate [VERSION=n]: Migrate the database through scripts in db/migrate. Target specific version with VERSION=n
• rake db:migrate:redo [STEP=n]: (2.0.2) Revert the database by rolling back "STEP" number of VERSIONS and re-applying migrations.
• rake db:migrate:reset: (2.0.2) Drop the database, create it and then re-apply all migrations. The considerations outlined in the note to rake db:create apply.
• rake db:reset: Drop and re-create database using db/schema.rb. The considerations outlined in the note to rake db:create apply.
• rake db:rollback [STEP=N]: (2.0.2) Revert migration 1 or n STEPs back.
• rake db:schema:dump: Create a db/schema.rb file that can be portably used against any DB supported by AR
• rake db:schema:load: Load a schema.rb file into the database
• rake db:sessions:clear: Clear the sessions table
• rake db:sessions:create: Creates a sessions table for use with CGI::Session::ActiveRecordStore
• rake db:structure:dump: Dump the database structure to a SQL file
• rake db:test:clone: Recreate the test database from the current environment's database schema
• rake db:test:clone_structure: Recreate the test databases from the development structure
• rake db:test:prepare: Prepare the test database and load the schema
• rake db:test:purge: Empty the test database

You can obtain the list of commands at any time using rake -T from within your application's root directory.

You can get a fuller description of each task by using

rake --describe task:name:and:options

See also

The official API for Migrations