CSC/ECE 517 Fall 2013/ch1 1w22 ss

Database Migration in RAILS

Data migration, in general is the process of transferring data between two database systems or any other computer systems<ref>http://en.wikipedia.org/wiki/Data_migration</ref>. It is always an odious task to manage database schema as well as data changes. Rails provides solution to this problem using Active Record Migration. These Migrations allow you to define incremental changes to databases by maintaining different versions of migration scripts, in short it makes it possible to use a version control, which helps to keep things synchronized with the actual code. You would find the word ‘Active Record’, very frequently when you talk about Migrations in RAILS. So,

What is an Active Record?

Active Record is a model layer in MVC pattern, which uses object relational mapping. It basically maintains a mapping between all Model classes and tables in databases. It includes a whole lot of other functionality like validation, parameter passing etc in addition to many features it provides in Migration. A detailed information on Active Records can be found here

Why Migrations are necessary?

Example 1: If one developer makes a schema change, He should inform all others about the change and distribute the modification script.<ref>http://www.tutorialspoint.com/ruby-on-rails/rails-migrations.htm</ref> In addition, he should remember to run the script during deployment. In short, making ad-hoc changes to the database involves risk. There need to be a proper documentation of which scripts to run during deployment which is an additional burden to the developers.

In this scenario, Migration in Rails helps you create migration files with simple commands. With migrations, you create your database schema in Ruby instead of SQL DDL. If you want to change your data model, say by renaming a column or adding a table, you just write another migration class to do it.<ref>http://archive.railsforum.com/viewtopic.php?id=1011</ref> Your database keeps track of versioning for you, and you can easily see what changed in the data model just by reading the migration code.

Example 2: Let us consider, everyone creates the database for their application using sql. If you want to change the data model, you run the sql script and then if you want to get back to the previous version you need to remember the change and write the rollback script for that. So basically the developer has to take care of the rollback script and in the real time scenario, where the changes in data model is very frequent, Keeping track of all these scripts would be a very difficult.

In this scenario, users can use Migration in RAILS to keep track of all the versions of the script and one can easily see what changes have been made in the data model.

Naming Convention of Migration files

In RAILS, the small tip we follow to make our lives easy is ‘Convention over Configuration’. As many other files, there is a naming convention associated with migration files also. The name of the file is of the form YYYYMMDDHHMMSS_create_tablename.rb<ref>http://guides.rubyonrails.org/migrations.html</ref>. It has a UTC timestamp identifying the migration followed by an underscore followed by the name of the migration. The name of the migration class (CamelCased version) should match the latter part of the file name. For example 20080906120000_create_categories.rb should define class CreateCategories.

Rails uses this timestamp to determine which migration should be run and in what order. Be careful of the position of migration, whenever you're copying a migration from another application or generate a file yourself. As it is difficult for a user to calculate the timestamp and create a migration, Active Record provides a generator which creates the filename in that convention.

How does a Migration look?

Inside a Migration file, you have

• Migration class which is subclass of ActiveRecord::Migration

The name of the class is same as that of Migration, following the naming conventions. Inside the class you have a ‘change’ method, where you write all the RUBY DSL (RUBY Domain Specific Language)<ref>http://www.infoq.com/news/2007/06/dsl-or-not</ref>. You also have a reversible section, where you write all the code necessary to roll back the migration. In general, all reversible migrations get reversed automatically by RAILS when you roll back any migration. If you use any irreversible migrations, then we should write the code required for rolling back the operations inside the reversible block. If you don't write any code to specify how to reverse a irreversible migration, and try to rollback such migration RAILS would throw a 'ActiveRecord::IrreversibleMigration' Exception. Refer <ref>http://archives.edgerails.info/articles/what-s-new-in-edge-rails/2011/05/06/reversible-migrations/index.html</ref>

class CreateCategories < ActiveRecord::Migration
 def change
   create_table :categories do |t|
     t.string :name
     t.bool :presence
    end
 end
end

You can also use old style of migration where we use, up/down methods.

class CreateCategories < ActiveRecord::Migration
 def self.up
   create_table :categories do |t|
     t.string :name
     t.bool :presence
    end
 end
 def self.down
    drop_table : categories
 end
end

The ‘up’ method is used when you are running the migration and ‘down’ method is used when you are rolling back the migration operation. The code inside the ‘up’ and ‘down’ method should be such that, a ‘up’ followed by a ‘down’ operation should leave the database in a consistent state.

‘change’ Method

The ‘change’ method is introduced in the newer version of RAILS and acts as a primary method. It works for most of the cases where active record knows how to reverse the migration automatically. Currently the change method supports only these migration definitions <ref>http://guides.rubyonrails.org/migrations.html</ref>:

• add_column
• add_index
• add_reference
• add_timestamps
• create_table
• create_join_table
• drop_table (must supply a block)
• drop_join_table (must supply a block)
• remove_timestamps
• rename_column
• rename_index
• remove_reference
• rename_table

How to create a Migration?

1) In RAILS, framework itself creates a default migration file, whenever a new model is created. In addition, the statements for adding the columns to the table are also generated, if you tell RAILS which attributes are to be present in the model.

$ rails generate model Category name:string presence:bool

Will create a migration which has code for creating a table categories with two attributes name and presence as

class CreateCategories < ActiveRecord::Migration
 def change
   create_table :categories do |t|
     t.string :name
     t.bool :presence
    end
 end
end

2) You can also create a standalone migration using ‘generate migration’

$ rails generate migration AddDescriptionToCategories

This would create an empty appropriately named Migration.

class AddDescriptionToCategories < ActiveRecord::Migration
 def change
 end
end

As you can see, all migration classes are subclasses of ActiveRecord::Migration, whereas Model classes are subclasses of ActiveRecord::base.

RAILS provide many shortcut commands during creating Migrations.

• Migrations of form ‘AddXXToYY’ or ‘RemoveXXToYY’, followed by appropriate column names to be added or deleted are automatically created with corresponding statements to add or remove the columns.

$ rails generate migration AddDescriptionToCategories description:string

Will generate a script for adding a column called description to the categories table.

class AddDescriptionToCategories < ActiveRecord::Migration
 def change
   add_column :categories, :description, :string
 end
end

Similarly,

$ rails generate migration RemoveDescriptionToCategories description:string

Will generate script to remove a column called description from categories table

class RemoveDescriptionToCategories < ActiveRecord::Migration
 def change
   remove_column :categories, :description, :string
 end
end

You are not limited to create or remove only one column, you can add or remove any number of columns in the similar way.

• Migration starting with 'CreateXXX' followed by the columns, would generate the code for creating the table XXX with columns mentioned.

$ rails generate migration CreateCategories name:string description:string

will generate script for creating table categories with columns name and description,

class CreateCategories < ActiveRecord::Migration
 def change
   create_table :categories do |t|
     t.string :name
     t.string :description
   end
 end
end

Basic Examples of Migrations

Creating a Table

One of the most fundamental methods of migration is create_table method. This method will also be generated from a model or scaffold generator(Refer Scaffold(Programming) to get the basic idea). For example,

create_table :category do |t|
  t_string :name
  t_string :description
end

This creates a table with two columns called name and description along with a default primary key called id. The name of the primary key can be altered with the :primary_key option. Or the primary key can all together be removed by using the option id: false.

Note: All the tables will created with default primary key called id.

Database specific options can be passed to an SQL fragment using the :options option. For example,

create_table :category, options: “ENGINE = BLACKHOLE” do |t|
  t.string:name
  t_string :description
end

This will append ENGINE = BLACKHOLE to the sql statement which is used to create the table.

Adding data to the database

Sometimes, we would require to have some data when the tables are created. We can insert the data as follows,

class AddInitialCategories < ActiveRecord::Migration
 def up
   5.times do |i|
     Category.create(name: "Category ##{i}", description: "Default Category")
   end
 end
end 

Creating a Join Table

The method create_join_table creates a HABTM(means has_and_belongs_to_many relation) join table. For example,

create_join_table :category, :recipe

This creates category_recipe table with two columns category_id and recipe_id. By default, these columns have the option :null set to false. These columns can be set to True in the following way

create_join_table :category, :recipe, column_options: {null: true}

This will create the category_id and recipe_id with the :null option as true The name of the join table can be mentioned as follows

create_join_table :category, :recipe, table_name: :categoryrecipe

Indices or additional columns can also be added to the table created by joining two tables. This is as follows

create_join_table :category, :recipe do |t|
  t.index :category_id
  t.index :recipe_id
end

Changing tables

An existing table can be modified using the change_table method. For example,

change_table :category do |t|
   t.remove :description
   t.rename :name, :category_name
end

This removes the column description and renames the name of the column ‘name’ to ‘category_name’.

Adding Columns

Columns can be added later to the table using the add_column method

Add_column :recipe, :recipe_instrns, text

Dropping a Table

A table can be dropped using the drop_table method.<ref>http://apidock.com/rails/v3.2.13/ActiveRecord/ConnectionAdapters/SchemaStatements/drop_table</ref>

Drop_table :recipe

Running the Migrations

• RAILS provide a set of tasks to run certain set of migrations.

The basic task you will use is

$ rake db:migrate 

This would run the ‘change’ or ‘up’ method for all the migrations that have not yet been run. If there are no such migrations, it exits. It generally run the migrations based on the date of migration.

Note that running the db:migrate also invokes the db:schema:dump task, which will update your db/schema.rb file to match the structure of your database.

• If you want to run any specific function use the following command.

$ rake db:migrate VERSION=20120906120000

When you run a migration, a schema_info table is created, which stores the version of the database. Whenever you try to run a migration of previous version, all the next versions are rolled back. i.e for example, if your present version is greater than ‘20080906120000’, all your versions till this one would be rolled back.

• Rolling Back: This command roll back the last migration by default

$ rake db:rollback

You can also specify number of the migrations you need to roolback by

$ rake db:rollback STEP=5

• Resetting the database: To drop the database, then recreate it with the present schema, we use,

$ rake db:reset

• To run the migrations in different environments, we can use

$ rake db:migrate RAILS_ENV=production

• If you want to print messages when running your migration, insert the message using say or say_with_time in the migration.

Note: Be Careful whenever you are using models in migrations. if there is a change in model and you later migrations consider the change. When a new person, comes and run your migration he might get an error because the model change has not been reflected in his application. Therefore, to avoid such failures, you should always reset model information before referencing the model in the migration.<ref>http://guides.rubyonrails.org/migrations.html</ref>

Database support

RAILS migrations support various databases like PostgreSQL, MySQL, Sybase, SQLite, SQL Server, and Oracle (all supported databases except DB2)<ref>http://api.rubyonrails.org/classes/ActiveRecord/Migration.html</ref>.

Conclusion

Considering the importance of migration in RAILS, we have covered how to create and run a migration in addition to some examples of basic migrations. We have some limited set of reversible migrations which reduces developer's effort. We can hope to see a increase in this list in the near future. Always be careful to update the model whenever you are referencing it in migration script, to avoid errors while running the migrations on different versions.

See Also

1. Active Record Basics

2. DB2 and Ruby On Rails

3. Guide to Ruby on Rails Migration

References

<references/>