The Secret Life of Your Database, Part 1: Migrations

As young developers, we often get caught up in what we don’t know. One of the first and hardest lessons to learn when you’re starting out as a programmer is the sheer volume of things that you don’t know. You have to get comfortable not knowing them, and you have to work towards learning as much about them as you can.

However, sometimes we focus so much on learning new things, that we forget to come back to what we already know – or at least, what we think we already know. In fact, this is exactly what I’ve been guilty of for the past few weeks. I’ve been focusing so much on learning new frameworks and getting comfortable with other languages, that I forgot to question my knowledge of Rails. Since it was a known domain and language, I assumed that I didn’t need to revisit it that often.

But boy, was I wrong. Just because you’re familiar with something doesn’t mean that you understand it completely. The trick to dealing with this is to make yourself feel uncomfortable in your otherwise familiar language. And that’s exactly what happened to me. Last week, while writing some lines of SQL (yes, really), I ran across a database migration that used an up and a down method. I saw those lines of code and realized something: I had no idea how my database really works. In fact, I’ve written so many migrations in so many Rails applications that, at some point, I’ve stopped thinking about what was actually going on under the hood. So, I set out to make myself uncomfortable and uncover the secret life of my database.

Teach Me How To Migrate

Most of us were introduced to Rails databases through migrations. Migrations are how we alter our database schema over time. They implement a Ruby DSL (domain-specific language), and run SQL queries in our database for us. And, they are super easy to learn to use and understand:

rails generate migration CreateBooks

Running a command like the one above prompts Active Record to create a unique, timestamped file within our /db directory, with a migration class that might look something like this:

class CreateBooks < ActiveRecord::Migration
  def change
    create_table :books do |t|
      t.string :title
      t.integer :year

      t.timestamps null: false
    end
  end
end

Nothing too surprising here; we’ve seen this kind of change method before. But what you may not have seen – or at least, understood – is something that looks like this:

class CreateBooks < ActiveRecord::Migration
  def up
    create_table :books do |t|
      t.string :title
      t.integer :year
    end
  end

  def down
    drop_table :books
  end
end

So, what does that up and down actually mean? What is going up, exactly…and what’s going down? And how is this different from using the change method, which probably seems way simpler right about now? Well, it’s time for us to find out.

All The Migrations Fit To Run

The first step to understanding how something works is by unpacking it, step by step. And that’s what we’ll do with our migrations. Let’s first look at how many migrations we currently have.

We’ve already generated a few migrations using the rails generate migration commands for our in-progress bookstore application. We can take a look at what all those migrations look like by running the rake db:migrate:status command, which will show us the status of our migrations, including any pending ones we may have:

❤ rake db:migrate:status

database: bookstore_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20140217160517  Create users
   up     20150424175043  Create books
   up     20150429145355  Add media columns to books
   up     20150430140850  Add author to books
   up     20150430160704  Add genre to books
   up     20150430192426  Create reading lists
   down   20150430191950  Add user id to reading lists

Whoa – look at all those migrations! And more importantly, look at the column to the left of the migrations: seem familiar? Each one of our migrations has a status, which is either up or down. Let’s remember this, we’re going to come back to it in a second.

According to the Rails source code, the rake:db:migrate:status rake task displays the status of all our migrations. This can be helpful in determining if we have any migrations we need to run. And it looks like we do! Let’s run rake db:migrate and then check the status of our migrations again with rake db:migrate:status:

❤ rake db:migrate:status

database: bookstore_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20140217160517  Create users
   up     20150424175043  Create books
   up     20150429145355  Add media columns to books
   up     20150430140850  Add author to books
   up     20150430160704  Add genre to books
   up     20150430192426  Create reading lists
   up     20150430191950  Add user id to reading lists

Hm, interesting. That last migration, which adds a user_id column to our reading_lists table, now has a status of up. We’ve basically migrated our database up, meaning that we have no pending migrations and all of our migrations are up to date.

But what if we wanted to rollback our migration, instead? Or, better yet, what if we wanted to rollback the last two migrations? Could we go back in time? Back to when we didn’t even have a reading_lists table in our database? How would we do that?

Well, we could run a command like rake db:rollback STEP=, which rolls back our database however many steps we specify. For this example, we’ll just rollback to two migrations ago (rake db:rollback STEP=2). What is the state of our database now? Let’s check the migrate:status again:

❤ rake db:migrate:status

database: bookstore_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20140217160517  Create users
   up     20150424175043  Create books
   up     20150429145355  Add media columns to books
   up     20150430140850  Add author to books
   up     20150430160704  Add genre to books
   down   20150430192426  Create reading lists
   down   20150430191950  Add user id to reading lists

Nice! We’ve migrated our database down, back to two migrations ago. There’s some serious Back To The Future stuff going down right now.

What Your Database Won’t Tell You (Unless You Ask)

Okay, so we can migrate up and down a database. Cool. We can go back and forth in time, to older versions of our database and to newer – and even the newest! – version of our database. Double cool. But what about what we originally set out to figure out? What do the up and down methods do in our migrations? And why do we only see them some of the time?

If you had an inkling that the up and down methods were somehow interlinked with the migration status column we saw when we ran rake db:migrate:status…well, your inkling was right on the money. They are connected, and in a really cool way.

The up and down methods are defined on a Migration class, which inherits from the ActiveRecord::Migration class. Active Record literally puts the M (model) in MVC, since it’s what handles all of our database records. And Active Record is pretty darn smart. Depending on which rake task we provide it, Active Record will execute either an up method in a migration file, or a down method.

The Rails Guides explain this pretty well:

“The up method should describe the transformation you’d like to make to your schema, and the down method of your migration should revert the transformations done by the up method. In other words, the database schema should be unchanged if you do an up followed by a down. For example, if you create a table in the up method, you should drop it in the down method. It is wise to perform the transformations in precisely the reverse order they were made in the up method.””

As you might have guessed, these two methods are inverse of each other in both form and function. The up method is called when migrating “up” the database – forward in time – while the down method is called when migrating “down” the database – or, back in time. In other words, the up method is a set of directions for running a migration, while the down method is a set of instructions for reverting a migration. This implies that the code in these two methods should fundamentally do the opposite things of one another.

This also means is that only one of these methods can ever actually run during a migration. If we run rake db:migrate, all of the up methods will execute, and every migration that is currently set to down will change to a status of up:

❤ rake db:migrate:status

database: bookstore_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20140217160517  Create users
   up     20150424175043  Create books
   down   20150429145355  Add media columns to books
   down   20150430140850  Add author to books
   down   20150430160704  Add genre to books
   down   20150430192426  Create reading lists
   down   20150430191950  Add user id to reading lists

❤ rake db:migrate
❤ rake db:migrate:status

database: bookstore_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20140217160517  Create users
   up     20150424175043  Create books
   up     20150429145355  Add media columns to books
   up     20150430140850  Add author to books
   up     20150430160704  Add genre to books
   up     20150430192426  Create reading lists
   up     20150430191950  Add user id to reading lists

And if we run rake db:rollback, the most recent migration that was run (and had its status set to up) will be reverted by calling the down method in the migration file. If we run rake db:rollback STEP=, the down method will be invoked in every migration file we specify we want to rollback (or how many steps back we want to go in the database’s history):

❤ rake db:migrate:status

database: bookstore_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20140217160517  Create users
   up     20150424175043  Create books
   up     20150429145355  Add media columns to books
   up     20150430140850  Add author to books
   up     20150430160704  Add genre to books
   up     20150430192426  Create reading lists
   up     20150430191950  Add user id to reading lists

❤ rake db:rollback STEP=3
❤ rake db:migrate:status

database: bookstore_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20140217160517  Create users
   up     20150424175043  Create books
   up     20150429145355  Add media columns to books
   up     20150430140850  Add author to books
   down   20150430160704  Add genre to books
   down   20150430192426  Create reading lists
   down   20150430191950  Add user id to reading lists

And, we could even run or revert a specific migration by giving ActiveRecord the version number/migration id of the migration:

❤ rake db:migrate:down VERSION=20150429145355
❤ rake db:migrate:status

database: bookstore_development

 Status   Migration ID    Migration Name
--------------------------------------------------
   up     20140217160517  Create users
   up     20150424175043  Create books
   down   20150429145355  Add media columns to books
   up     20150430140850  Add author to books
   up     20150430160704  Add genre to books
   up     20150430192426  Create reading lists
   up     20150430191950  Add user id to reading lists

In the example above, when we ran a migrate:down task, all that was happening is that only the down method written in the “AddMediaColumnsToBooks” migration file was was invoked!

Neat, right!? Or maybe just kind of mind-blowing if you’ve never seen this before. Who knew that this is what migrations were really all about? Well, now you and I both know, so that’s pretty rad.

Why Change When You Can Go Up And Down?

The change method is pretty standard when it comes to migrations partly because it’s a newer addition to Rails. Just like up and down, the change method is defined on the ActiveRecord::Migration class. In fact, it does exactly what up and down accomplish together. The change method is usually able to automatically figure out the inverse operation you provide it; for example, if you call create_table inside of the change method, when you run rake db:rollback, it will drop_table. The same goes for add_column and remove_column.

So, if the change method can do all of these things in one go (rather than in two methods), why do we sometimes see an up and down method defined together in a migration file?

Well, there are many times when we might want Active Record to be smart and figure out when to drop a column or table. But other times, it might not be as clear.

For example, what if we wanted a migration that just created or fixed data? We wouldn’t want ActiveRecord to try to figure out whether to add or remove a column…or worse, drop our table! Or what if we wanted to remove columns when we migrated up, and add columns when we migrated down? We’d have to specify that explicitly in our up and down methods.

We could even get fancy with some SQL and do something a bit more granular like rename a table:

class RenameReadingListsToWishLists < ActiveRecord::Migration
  def up
    execute <<-SQL
      ALTER TABLE reading_lists
        RENAME TO wish_lists;
    SQL
  end

  def down
    execute <<-SQL
      ALTER TABLE wish_lists
        RENAME TO reading_lists;
    SQL
  end
end

In this example, we’re using the execute method, which takes either a string value of a SQL query, or a heredoc like the one we’ve written above.

And we might even want to make it completely impossible for someone to ever revert a migration. We could specify that kind of behavior in our down method by raising an ActiveRecord error:

def down
  raise ActiveRecord::IrreversibleMigration
end

Depending on which migration file this down method lived in, that migration could never be “migrated down”. This can sometimes be dangerous, but also useful – particularly if we had many other data models that were depending on those tables existing in the first place!

It turns out that our database is a pretty powerful thing. Rails provides us with a lot of functionality and flexibility of moulding it to be exactly the way that we want it to be. It’s just up to us to take advantage of it in the right situations! The more we learn about shaping our database, the better equipped we’ll be to creating effective, concise, and streamlined databases for each and every one of our applications.

But the truth is, we’ve only scratched the surface of databases today. Tune in again next week, when I’ll uncover the secret life of all your…JOIN TABLES! Try and contain your enthusiasm, my friends.

tl;dr?

The up and down methods are a more granular way of defining the change method in a Rails migration. The up method is a set of instructions of what to do when you migrate, and the down method is a set of directions of what to do when you rollback.
Want to see more examples of when you might want to use the up and down methods in place of the change method? Check out the Rails Guides on Active Record Migrations.
Need to read more on running migrations? Read this. Gotta brush up on migrations after reading this post? Head over here.

Words and Code

One writer’s journey from words to code.