4.16. Migrations

SQL database tables are generated in Rails with migrations and they should also be changed with migrations. If you create a model with rails generate model, a corresponding migration file is automatically created in the directory db/migrate/. I am going to show you the principle using the example of a shop application. Let's create one first:
$ rails new shop
  [...]
$ cd shop
$
Then we create a Product model:
$ rails generate model product name 'price:decimal{7,2}' weight:integer in_stock:boolean expiration_date:date
      invoke  active_record
      create    db/migrate/20121119143522_create_products.rb
      create    app/models/product.rb
      invoke    test_unit
      create      test/unit/product_test.rb
      create      test/fixtures/products.yml
$
The migrations file db/migrate/20121119143522_create_products.rb was created. Let's have a closer look at it:
class CreateProducts < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.string :name
      t.decimal :price, :precision => 7, :scale => 2
      t.integer :weight
      t.boolean :in_stock
      t.date :expiration_date

      t.timestamps
    end
  end
end
The method change creates and deletes the database table in case of a rollback. The migration files have embedded the current time in the file name and are processed in chronological order during a migration (in other words, when you call rake db:migrate).
$ rake db:migrate
==  CreateProducts: migrating =================================================
-- create_table(:products)
   -> 0.0017s
==  CreateProducts: migrated (0.0018s) ========================================

$ 
Only those migrations that have not been executed yet are processed. If we call rake db:migrate again, nothing happens, because the corresponding migration has already been executed:
$ rake db:migrate
$
But if we manually delete the database with rm and then call rake db:migrate again, the migration is repeated:
$ rm db/development.sqlite3 
$ rake db:migrate
==  CreateProducts: migrating =================================================
-- create_table(:products)
   -> 0.0016s
==  CreateProducts: migrated (0.0017s) ========================================

$  
After a while we realise that we want to save not just the weight for some products, but also the height. So we need another database field. There is an easy to remember syntax for this, rails generate migration add_*:
$ rails generate migration add_height_to_product height:integer
      invoke  active_record
      create    db/migrate/20121119143758_add_height_to_product.rb
$
In the migration file db/migrate/20121119143758_add_height_to_product.rb we once again find a change method:
class AddHeightToProduct < ActiveRecord::Migration
  def change
    add_column :products, :height, :integer
  end
end
With rake db:migrate we can start in the new migration:
$ rake db:migrate
==  AddHeightToProduct: migrating =============================================
-- add_column(:products, :height, :integer)
   -> 0.0007s
==  AddHeightToProduct: migrated (0.0008s) ====================================

$
In the console we can look at the new field. It was added after the field updated_at:
$ rails console
Loading development environment (Rails 4.0.0)
>> Product
=> Product(id: integer, name: string, price: decimal, weight: integer, in_stock: boolean, expiration_date: date, created_at: datetime, updated_at: datetime, height: integer)
>> exit
$

Buy the new Rails 5.1 version of this book.

Please note that you need to add the new field in attr_accessible in app/models/product.rb, otherwise you will not have access to the height attribute.
What if you want to look at the previous state of things? No problem. You can easily go back to the previous version with rake db:rollback:
$ rake db:rollback
==  AddHeightToProduct: reverting =============================================
-- remove_column("products", :height)
   -> 0.0151s
==  AddHeightToProduct: reverted (0.0152s) ====================================

$
Each migration has its own version number. You can find out the version number of the current status via rake db:version:
$ rake db:version
Current version: 20121119143522
$

Buy the new Rails 5.1 version of this book.

Please note that all version numbers and timestamps only apply to the example printed here. If you recreate the example, you will of course get a different timestamp for your own example.
You will find the corresponding version in the directory db/migrate:
$ ls db/migrate/
20121119143522_create_products.rb
20121119143758_add_height_to_product.rb
$
You can go to a specific migration via rake db:migrate VERSION= and add the appropriate version number after the equals sign. The zero represents the version zero, in other words the start. Let's try it out:
$ rake db:migrate VERSION=0
==  CreateProducts: reverting =================================================
-- drop_table("products")
   -> 0.0005s
==  CreateProducts: reverted (0.0006s) ========================================

$
The table was deleted with all data. We are back to square one.

Which Database is Used?

The database table is created through the migration. As you can see, the table names automatically get the plural of the models (Person vs. people). But in which database are the tables created? This is defined in the configuration file config/database.yml:
# SQLite version 3.x
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000
Three different databases are defined there in YAML format (see http://www.yaml.org/ or http://en.wikipedia.org/wiki/YAML). For us, only the development database is relevant for now (first item). By default, Rails uses SQLite3 there. SQLite3 may not be the correct choice for the analysis of the weather data collected worldwide, but for a quick and straightforward development of Rails applications you will quickly learn to appreciate it. In the production environment, you can later still switch to "big" databases such as MySQL or PostgreSQL.[11]
To satisfy your curiosity, we have a quick look at the database with the command line tool sqlite3:
$ sqlite3 db/development.sqlite3 
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
schema_migrations
sqlite> .quit
$
Nothing in it. Of course not, as we have not yet run the migration:
$ rake db:migrate
==  CreateProducts: migrating =================================================
-- create_table(:products)
   -> 0.0142s
==  CreateProducts: migrated (0.0143s) ========================================

==  AddHeightToProduct: migrating =============================================
-- add_column(:products, :height, :integer)
   -> 0.0011s
==  AddHeightToProduct: migrated (0.0012s) ====================================

$ sqlite3 db/development.sqlite3 
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
products           schema_migrations
sqlite> .schema products
CREATE TABLE "products" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(255), "price" decimal(7,2), "weight" integer, "in_stock" boolean, "expiration_date" date, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL, "height" integer);
sqlite> .quit
$
The table schema_migrations is used for the versioning of the migrations. This table is created during the first migration carried out by Rails, if it does not yet exist.

Creating Index

I assume that you know what a database index is. If not, you will find a brief introduction at http://en.wikipedia.org/wiki/Database_index. In brief: you can use it to quickly search for a specific table column.
In our production database, we should index the field name in the products table. We create a new migration for that purpose:
$ rails generate migration create_index
      invoke  active_record
      create    db/migrate/20121120142002_create_index.rb
$
In the file db/migrate/20121120142002_create_index.rb we create the index with add_index in the method self.up, and in the method self.down we delete it again with remove_index:
class CreateIndex < ActiveRecord::Migration
  def up
    add_index :products, :name
  end

  def down
    remove_index :products, :name
  end
end
With rake db:migrate we create the index:
$ rake db:migrate
==  CreateIndex: migrating ====================================================
-- add_index(:products, :name)
   -> 0.0010s
==  CreateIndex: migrated (0.0011s) ===========================================

$ 
Of course we don't have to use the up and down method. We can use change too. The migration for the new index would look like this:
class CreateIndex < ActiveRecord::Migration
  def change
    add_index :products, :name
  end
end

Buy the new Rails 5.1 version of this book.

You can also create an index directly when you generate the model. In our case (an index for the attribute name) the command would look like this:
$ rails generate model product name:string:index 'price:decimal{7,2}' weight:integer in_stock:boolean expiration_date:date
      invoke  active_record
      create    db/migrate/20121120142344_create_products.rb
      create    app/models/product.rb
      invoke    test_unit
      create      test/unit/product_test.rb
      create      test/fixtures/products.yml
$ cat db/migrate/20121120142344_create_products.rb
class CreateProducts < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.string :name
      t.decimal :price, :precision => 7, :scale => 2
      t.integer :weight
      t.boolean :in_stock
      t.date :expiration_date

      t.timestamps
    end
    add_index :products, :name
  end
end
$ 

Miscellaneous

This book is aimed at beginners, so I cannot discuss the topic migrations in great depth. The main focus is on understanding the mechanics in principle. But there are a few details that are so important that I want to mention them here.

Automatically Added Fields (id, created_at and updated_at)

Rails kindly adds the following fields automatically in the default migration:
  • id:integer
    This is the unique ID of the record. The field is automatically incremented by the database. For all SQL fans: NOT NULL AUTO_INCREMENT
  • created_at:datetime
    The field is filled automatically by ActiveRecord when a record is created.
  • updated_at:datetime
    The field is automatically updated to the current time whenever the record is edited.
So you don't have to enter these fields yourself when generating the model.
At first you may ask yourself: "Is that really necessary? Does it make sense?". But after a while you will learn to appreciate these automatic fields. Omitting them would usually be false economy.

Further Documentation

The following webpages provide excellent further information on the topic migration:


[11] Some developers believe that you should always develop with the same database that you are later going to use for production and testing. Others feel that this is not necessary when using ORM abstraction layers. Please make your own decision on this. I often program a Rails application with SQLite and frequently use MySQL for the production.