Rails Migration

Rails Migration A Complete Guide

Eric Boersma Developer Tips, Tricks & Resources

A Rails migration is a tool for changing an application’s database schema. Instead of managing SQL scripts, you define database changes in a domain-specific language (DSL). The code is database-independent, so you can easily move your app to a new platform. You can roll migrations back, and manage them alongside your application source code.

Let’s take a look at what Rails migrations are, why you might need them, and walk through examples using the sample code we used to show you how to troubleshoot Ruby applications.

What’s a Rails migration?

Requirements change all the time, and those changes often lead to database changes. Migrations give you a way to modify your database schema within your Rails application. So you use Ruby code instead of SQL. Using Rails migrations instead of SQL has several advantages.

Rails applications that can stay within the Active Record model are database-independent. If you have to write SQL to change your schema, you lose this independence. Migrations avoid this since you make the modifications in platform-independent Ruby.

Migrations keep your database schema changes with your application code. They’re written in Ruby and versioned with the rest of your app. Sure, you can (and should) version your SQL files, but do they belong in the same place? Not everyone feels the same way about that question. But it’s not an issue with Rails.

Migrations are additive. Each one represents a new version of your database schema. Rails applications can evolve, and publishing a new migration with a new release of your application isn’t unusual.

Why would I need it?

Rails migrations are useful any time you need to make a change to your application’s database. If you’re working with Rails Active Records, manipulating the database directly is a bad idea.

As we’ll see in the example below, Active Record uses migrations to update your app’s schema in schema.rb. This file is what Rails uses to deploy your application to a new database. So using migrations makes it possible for you to deploy your app to new platforms. You can develop on one database and deploy to another, or deploy to a new database platform in production.

Migrations are saved as part of your Rails project, so they’re versioned with the rest of your code. They’re also easy to share across development teams since each member of the team can deploy the migration to their local instance when they update their projects.

What can I do with it?

You can use migrations to make any changes you need to the database(s) your application connects to. You can use the Rails DSL for these changes, or you can use SQL. Of course, if you write your own database code, you lose most of the database independence, and often labor-saving, advantages of Rails.

You can also roll migrations back, assuming it didn’t do something irreversible like destroying data.

Let’s take a look at some specific operations, and then we’ll try a few hands-on migrations in the sample code.

Add a column

One of the most common changes in an app is adding a field to an existing object. So adding a column to a database is a typical migration. If you’re working with Active Records, Rails will create the migration for you.

You can use all of the Rails basic data types with migrations, and it’ll be matched to the corresponding type in the database you migrate to. Here’s a list of data types:

  • string
  • text
  • integer
  • bigint
  • float
  • decimal
  • numeric
  • datetime
  • time
  • date
  • datetime
  • timestamp
  • binary
  • primary_key
  • boolean

You can also specify a database-specific data type for a column, but this may cause problems if you try to migrate your application to a new platform.

Change a column

You can change an existing column to a new name or data type as well. Rails migrations know how to move data to a new type. Column changes, however, aren’t reversible.


Stackify Loves Developers

Add a new model (or table)

Adding a new class to an application is a frequent change too. When you add a new model to a Rails application, it generates a migration to create the corresponding table for you. If that’s not what you need, you can write your own.

Execute SQL

If you need functionality that’s not supported by Active Record, you can execute SQL inside a migration. It’s up to you to write the code to undo it, though. We’ll look more closely at how to implement rollbacks below.

How do I perform a Rails migration?

Let’s do a few migrations on our sample app. But before we start, let’s switch over to a MySQL database so we can use SQL to examine the results more quickly than we can with SQLite.

A Rails migration to MySQL

You’ll need a MySQL database to follow along with this. If you’re on a Mac, Linux, or Windows 10, the easiest way is probably to spin one up on Docker.

Next, grab the sample code from GitHub. After you’ve pulled a copy, navigate to the config directory and open database.yml.mysql in your favorite editor.

# Mysql 
default: &default
adapter: mysql2
username: root
password: password
host: 127.0.0.1
development:
<<: *default
database: todos_development
test:
<<: *default
database: todos_test
production:
<<: *default
database: todos_prod

This is a database configuration file that connects to a MySQL database running on localhost. It’ll use the root user and password “password.”Modify this for your database.

Then, copy this file to database.yml in the same directory, making sure you save the original if you want to retain the setup for SQLite.

Next, run Rake to create the database.

rake db:create
Created database 'todos_development'
Created database 'todos_test'

You may see some warnings before Rake creates the tables.

Now, run a migration to set up the application tables.

rake db:migrate
== 20181214203309 CreateTodos: migrating ======================================
-- create_table(:todos)
-> 0.0217s
== 20181214203309 CreateTodos: migrated (0.0218s) =============================

You’ve run your first migration! You moved this application from Sqlite3 to MySQL by changing a configuration file and running a Rails migration. Rails, via the Rake command, deployed the application’s schema (but not the data) to a different backend for you.

Start the server, and run a query with HTTPie:

http :3000/todos
HTTP/1.1 200 OK
Cache-Control: max-age=0, private, must-revalidate
Content-Type: application/json; charset=utf-8
ETag: W/"4f53cda18c2baa0c0354bb5f9a3ecbe5"
Transfer-Encoding: chunked
X-Request-Id: 640a17d4-7dad-4bde-a3eb-7c1a7d3c738c
X-Runtime: 0.103446
[]

You should see a 200 result code for success and an empty set of todos.

Now, add a todo to the database as we did in the first tutorial:

http POST :3000/todos description='Replace doors on limo, ashtrays full' done=0
HTTP/1.1 201 Created
Cache-Control: max-age=0, private, must-revalidate
Content-Type: application/json; charset=utf-8
ETag: W/"def9359bc9248d983495676ce523c1b6"
Transfer-Encoding: chunked
X-Request-Id: 2485b24a-3f58-44b4-bd82-65c8660ec893
X-Runtime: 0.324976
{
  "created_at": "2019-04-28T19:07:54.000Z",
  "description": "Replace doors on limo, ashtrays full",
  "done": 0,
   "id": 1,
   "updated_at": "2019-04-28T19:07:54.000Z"
}

You should see a 201 result code, and the server echos back the new record. You’re ready to go!

Add a column

Let’s start by adding a column to our todos table.

Connect to the database and get a description of the todos table:

mysql> desc todos;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| description | varchar(255) | YES  |     | NULL    |                |
| done        | int(11)      | YES  |     | NULL    |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

We want to add users to the todo application, so we need to add a username to each record.

First, create a migration:

rails generate migration AddUserToTodos user:string
    invoke  active_record
    create    db/migrate/20190428200056_add_user_to_todos.rb

As the command line implies, this tells Rails to generate a migration for us. It does all the work for us.

Let’s run this and see what happens:

rake db:migrate
= 20190428200056 AddUserToTodos: migrating ===============================
-- add_column(:todos, :user, :string)
-> 0.0234s
== 20190428200056 AddUserToTodos: migrated (0.0235s) ======================

Rake’s output says it created a column. Let’s take a look at the table in MySQL now:

mysql> desc todos;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| description | varchar(255) | YES  |     | NULL    |                |
| done        | int(11)      | YES  |     | NULL    |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
| user        | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

There it is! Rake added the column to the table for us.

Let’s take a closer look at what Rails did for us before we try a few more examples.

Files

When you created the migration above, the last bit of output was a filename like this: 20190428200056_add_user_to_todos.rb. Find that file in your project’s db/migrate directory and open it in an editor.

class AddUserToTodos < ActiveRecord::Migration[5.2]
def change
  add_column :todos, :user, :string
end
end

This is the database migration that Rails generated for you. Migrations are Ruby classes that implement changeup, or down methods. This one implements the change method. (We’ll look at up and down later.) Even with an API reference, it’s easy to see what it does: it adds a column named user with the data type string.

When you passed AddUserToTodos user:string to Rails, it recognized the migration name, AddUserToToDos, as a request to add a column. If you name your migration name “AddXXXToYYY” or “RemoveXXXFromYYY” and pass a list of column names and types, rails will figure out what you’re trying to do. You’re not limited to one column either. You can add or remove as many as you want.

Now, do a directory listing of the db/migrate directory:

ls -l db/migrate
total 16
-rw-r--r--  1 egoebelbecker  staff  181 Dec 14 19:05 20181214203309_create_todos.rb
-rw-r--r--  1 egoebelbecker  staff  121 Apr 28 16:00 20190428200056_add_user_to_todos.rb

There’s an older migration already in there. Here are the contents:

class CreateTodos < ActiveRecord::Migration[5.2]
 def change
create_table :todos do |t|
t.string :description
t.integer :done
end
end
end

Rails created a migration when I created the project. It’s filename is 20181214203309_create_todos.rb. The timestamp at the start of the name serves as a version ID. That’s the migration Rake used to deploy the application to MySQL. Even if you want to write your migration code by hand, using “rails generate migration” to create the file is a good idea since calculating those timestamps can be difficult.

Rollback a migration

Now, rollback the user change:

rake db:rollback
== 20190428200056 AddUserToTodos: reverting ===============================
-- remove_column(:todos, :user, :string)
-> 0.1666s
== 20190428200056 AddUsernameToTodos: reverted (0.1750s) ======================

When you take a look at the table, the column is gone:

mysql> desc todos;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| description | varchar(255) | YES  |     | NULL    |                |
| done        | int(11)      | YES  |     | NULL    |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

That was easy!

Add it back before moving on to the next step.

rake db:migrate
= 20190428200056 AddUserToTodos: migrating ===============================
-- add_column(:todos, :user, :string)
-> 0.0234s
== 20190428200056 AddUserToTodos: migrated (0.0235s) ======================

Change a column

What happens if you save a username with each todo and a user changes his or her name? You’ll need to modify every one of their todos! Instead of a name, let’s save a user ID. We’ll change the data type to an integer.

First, create a migration:

rails generate migration ChangeUserToInt
invoke  active_record
create    db/migrate/20190428213522_change_user_to_int.rb

Next, take a look at the file Rails created:

class ChangeUserToInt < ActiveRecord::Migration[5.2]
def change
end
end

This is empty migration, so you’ll need to fill it in. Let’s make a few changes. We want to make the user an integer, set a default value for it, and also make it not-nullable.

class ChangeUserToInt < ActiveRecord::Migration[5.2]
def change
change_column :todos, :user, :integer
change_column_default :todos, :user, 999
change_column_null :todos, :user, false
end
end

We’re calling three different methods in this migration. The first changes the data type, the second the default value, and the last sets the column to non-null.

Now run the migration:

rake db:migrate
== 20190428220000 ChangeUserToInteger: migrating ==============================
-- change_column(:todos, :user, :integer)
-> 0.0426s
-- change_column_default(:todos, :user, 999)
-> 0.0192s
-- change_column_null(:todos, :user, false)
-> 0.0355s
== 20190428220000 ChangeUserToInteger: migrated (0.0975s) =====================

Finally, take a look at the table:

mysql> desc todos;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| description | varchar(255) | YES  |     | NULL    |                |
| done        | int(11)      | YES  |     | NULL    |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
| user        | int(11)      | NO   |     | 999     |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Rake updated the column’s data type, default value, and set it to non-nullable.

Add a new table

Now, let’s add the users table. We’ve already seen a migration that creates a table. Here’s the one Rails generated back when we created the application:

class CreateTodos < ActiveRecord::Migration[5.2]
def change
create_table :todos do |t|
t.string :description
t.integer :done

t.timestamps
end
end
end

So if you want to use a migration to create a table that doesn’t correspond to an Active Record class, you can write one yourself. The create_table accepts a do loop with a list of column definitions.

But the easy way is to generate a new model:

rails generate model Users first_name:string last_name:string email:string
invoke  active_record
create    db/migrate/20190428234334_create_users.rb
create    app/models/user.rb
invoke    test_unit
create      test/models/user_test.rb
create      test/fixtures/users.yml

Rails prints the file name of the migration it creates. Take a look at it:

class CreateUsers < ActiveRecord::Migration[5.2]
def change
create_table :users do |t|
t.string :first_name
t.string :last_name
t.string :email

t.timestamps
end
end
end

No surprises there. The last column in the definition, timestamps, is added by Rails by default. This creates the created_at and updated_at columns we saw earlier in the todos table. What seems to be missing is the user ID though.

Let’s run the migration and see what happens.

rails db:migrate
== 20190428234334 CreateUsers: migrating ======================================
-- create_table(:users)
-> 0.0212s
== 20190428234334 CreateUsers: migrated (0.0213s) =============================

Now, check MySQL:

mysql> desc users;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| first_name | varchar(255) | YES  |     | NULL    |                |
| last_name  | varchar(255) | YES  |     | NULL    |                |
| email      | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

Rake added the table and automatically added a primary key to the table named ID. Since this corresponds to the user column in the todos table, it makes sense to set that up as a foreign key in todos, doesn’t it?

Adding a foreign key

We can add a foreign key to a table via a Rails migration. Generate a new migration:

rails g migration AddForeignKeyToTodos
invoke  active_record
create    db/migrate/20190428235632_add_foreign_key_to_todos.rb

Next, open the new file and add the code:

class AddForeignKeyToTodos < ActiveRecord::Migration[5.2]
def change
add_foreign_key :todos, :users
end
end

Adding a foreign key looks like the other migrations we’ve done so far. There’s a method that’s named add_foreign_key, just as you’d expect.

Now, run the migration.

rails db:migrate
== 20190428235632 AddForeignKeyToTodos: migrating =============================
-- add_foreign_key(:todos, :users)
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Key column 'user_id' doesn't exist in table: ALTER TABLE `todos` ADD CONSTRAINT `fk_rails_d94154aa95`
FOREIGN KEY (`user_id`)
REFERENCES `users` (`id`)

The migration failed! We created a problem for ourselves when we created the user column: it needs to be named user_id if it’s going to act as a foreign key for an ID column.

There are a few ways to fix this, but let’s imagine we have data in the user column that we don’t want to lose. We’ll rename the column. We’ll also change the data type to match the ID column since it needs to match.

Before we start though, let’s think about the change we’re going to make. What’s going to happen when we try to change user to a foreign key? The conversion will fail because we have a value in that column that doesn’t exist in the users table. We need to add a user to the table before we make the conversion. We need to execute some SQL.

Executing SQL

First, delete the failed conversion. We’ll add it into the new one.

rm db/migrate/20190428235632_add_foreign_key_to_todos.rb

Now, generate a new one.

rails g migration RenameUserToUserId
invoke  active_record
create    db/migrate/20190429001106_rename_user_to_user_id.rb

This is going to be a complicated class. It needs to

  1. rename the column in todos,
  2. change the data type,
  3. insert a user in users,
  4. and, finally, change user_id to a foreign key.

Let’s write the code.

class RenameUserToUserId < ActiveRecord::Migration[5.2]
def change
rename_column :todos, :user, :user_id
change_column :todos, :user_id, :bigint
execute "insert into users (first_name, last_name, email, id, created_at, updated_at) values('Eric', 'Goebelbecker', 'eric AT ericgoebelbecker.com', 999, now(), now())"
add_foreign_key :todos, :users
end
end

Run this migration, and take a look at the users table:

mysql> select * from users;
+-----+------------+--------------+---------------------------+---------------------+---------------------+
| id  | first_name | last_name    | email                     | created_at          | updated_at          |
+-----+------------+--------------+---------------------------+---------------------+---------------------+
| 999 | Eric       | Goebelbecker | eric@ericgoebelbecker.com | 2019-04-29 00:34:13 | 2019-04-29 00:34:13 |
+-----+------------+--------------+---------------------------+---------------------+---------------------+
1 row in set (0.11 sec)

The user is there! How does todos look?

mysql> desc todos;        
+-------------+--------------+------+-----+---------+----------------+        
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| description | varchar(255) | YES  |     | NULL    |                |
| done        | int(11)      | YES  |     | NULL    |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
| user_id     | bigint(20)   | NO   | MUL | 999     |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)                                            

It’s all set! We created a relationship between the two tables using a Rails migration.

There’s a problem with this migration, though. Rails doesn’t know how to rollback our SQL statement. You’re only supposed to place directives in a change method that Rails knows how to revert.

Migrations with up and down

Let’s write a migration that can be rolled back. Generate another migration named “AddAnotherUser” and open the file.

class AddAnotherUser < ActiveRecord::Migration[5.2]
def up
execute "insert into users (first_name, last_name, email, id, created_at, updated_at) values('Eric', 'Rekceblebeog', 'eric AT unknown.com', 998, now(), now())"
end
def down
execute "delete from users where id = 998"
end
end

Rake runs the up method for a migration and the down method for a rollback.

Run this migration, and the user is added:

rake db:migrate
== 20190429200308 AddAnotherUser: migrating ===================================
-- execute("insert into users (first_name, last_name, email, id, created_at, updated_at) values('Eric', 'Rekceblebeog', 'eric AT unknown.com', 998, now(), now())")
  -> 0.0271s

== 20190429200308 AddAnotherUser: migrated (0.0273s) ==========================

Roll it back, and the user is removed:

rake db:rollback
== 20190429200308 AddAnotherUser: reverting ===================================
-- execute("delete from users where id = 998")
-> 0.0124s
== 20190429200308 AddAnotherUser: reverted (0.0125s) ==========================

How do I verify a Rails migration?

We’ve been checking each of our migrations by logging into MySQL and examining the associated tables. This is time-consuming and doesn’t scale.

Use Schema.rb to verify your database schema

I mentioned schema.rb at the beginning of this tutorial. It’s where Rails stores the current state of the application’s database schema. While it’s not intended as a way to verify a migration, it’s good for getting an idea of what Rails thinks you wanted.

Open up db/schema.rb in an editor:

# This file is auto-generated from the current state of the database. Instead
# of editing this file, please use the migrations feature of Active Record to
# incrementally modify your database and then regenerate this schema definition.
#
# Note that this schema.rb definition is the authoritative source for your
# database schema. If you need to create the application database on another
# system, you should be using db:schema:load, not running all the migrations
# from scratch. The latter is a flawed and unsustainable approach (the more migrations
# you'll amass, the slower it'll run and the greater likelihood for issues).
#
# It's strongly recommended that you check this file into your version control system.
ActiveRecord::Schema.define(version: 2019_04_29_002812) do
create_table "todos", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
t.string "description"
t.integer "done"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.bigint "user_id", default: 999, null: false
t.index ["user_id"], name: "fk_rails_d94154aa95"
end
create_table "users", options: "ENGINE=InnoDB DEFAULT CHARSET=utf8", force: :cascade do |t|
t.string "first_name"
t.string "last_name"
t.string "email"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
add_foreign_key "todos", "users"
end

This is the current state of the schemas for users and todos. All of the changes we made are reflected in the table definitions.

As you can see from the comments, you should never modify this file directly! Always use migrations to make sure your database stays in sync with your code. Check schema.rb before you deploy to a new environment to make sure your picture of the schema and Rails are in sync.

Rails migration

Use Stackify’s Retrace to monitor your app

Stackify Retrace is a powerful way to monitor your Rails application. Retrace centralizes your logs so you can view them from a web console and set up alerts. Retrace also correlates logs with web errors so you can isolate new problems quickly. And if a migration fails, you can track down the problem in seconds. Sign up for a free trial here.

Migrations are a key Rails benefit

The ability to deploy database changes from code is one of Rails’ most powerful features. Rails migrations free you from worrying about the differences between various SQL grammar so you can focus on your application code. So, take a closer look at migrations before you write SQL for your Rails app.

Schedule A Demo

About Eric Boersma

This post was written by Eric Boersma. Eric is a software developer and development manager who's done everything from IT security in pharmaceuticals to writing intelligence software for the US government to building international development teams for non-profits. He loves to talk about the things he's learned along the way, and he enjoys listening to and learning from others as well.