rails ActiveRecord database deployment migrations

Adding a Column in Production

Adding a column default to an existing field can be a very simple change. Although, that can be proven untrue if the respected table has many rows of data. It may result in a long running migration causing unforeseen side effects, errors, or even downtime. We will look at few common approaches below.

Context

Consider the table User which has about 15 million rows.

User
- first_name
- last_name
- email

Goal

We want to add a locked flag to the table so we can restrict access to users without deleting them.

Technical Plan

Attempt 1

First, we generate the migration like so:

> rails generate migration AddLockedToUsers locked:boolean

Now, we can to add a default value of false so initialized users are active. So let’s update the pending migration we just created.

add_column :users, :locked, :boolean, default: false

All that is left to do is run the migration

> rails db:migrate

If you open a rails console you will see the default is working as expected. New and existing users will both have locked set to false.

> User.new
=> #<User:0x00007fec71e4d508 id: nil, first_name: nil, last_name: nil, locked: false>

> User.first
=> #<User:0x00007fec71e4d508 id: 1, first_name: 'Joe', last_name: 'Davidson', locked: false>

Problem with Attempt 1

Our User table has over 15 million records. We do not want our database locking up or other dependent code being affected during deployment.

Since in our case, timing is an issue and we need the new column to be added quickly so we will try another solution.

Attempt 2

This time, we are going to break up this feature into two deploys.

Create new column

The first deploy will include two migrations. One: adding the column, and two: adding the default.

> rails generate migration AddLockedToUsers locked:boolean
> rails db:migrate

Add default

As a separate unit of work, we will alter the existing column to include a default. Adding a default to an already created column will not populate the old values in the migration. Although, new users going forward will have the default.

In our use case, the support code would work with locked being nil. Although for future reporting queries, data integrity, and a little bit of OCD.. we are going to ensure every User has a value set.

Let’s generate a new migration adding the default.

> rails generate migration ChangeLockedDefaultForUsers
# Rails 4 migration

  def up
    change_column_default(:users, :locked, false)
  end

  def down
    change_column_default(:users, :locked, nil)
  end

 # Rails 5 migration

  def change
    change_column_default(:users, :locked, from: nil, to: false)
  end

Notice the difference between rails 4 and 5? We want to make sure each migration is reversible. You can use from and to with Rails 5. Otherwise we need to implement both up and down methods.

Deploy

Deployment is quick and easy as we did not need to update 15 million records.

Update existing users

For our send phase of this approach, we are going to update all the existing records. Let’s create a rake task which update the users in batches.

User.where(locked: nil).in_batches.update_all(locked: false)

Problem with Attempt 2

Attempt 2 does not ensure locked is always set. The value will be populated but it can still be set to nil.

> user = User.first
> user.locked = nil
> user.valid?
=> true
> user.save
=> true
> user
=> #<User:0x00007fec71e4d508 id: 1, first_name: 'Joe', last_name: 'Davidson', locked: nil>

Add null constraint

After all of the Users have a value set for locked then we can add a null constraint to the column. The migration will not pass if any values are nil. That is important to keep in mind because we don’t want developers on the project not being able to run their migration’s because they didn’t know to run the rake task.

  def up
    change_column_null(:users, :locked, false)
  end

  def down
    change_column_null(:users, :locked, nil)
  end

Takeaway

It is important to always consider scale when working on a production application. In our example, we highlighted how adding a column with a default value and spitting up its migration into multiple can help avoid a headache of a deploy.

 

Did you like this article? Check out these too.


 

Found this useful? Know how it can be improved? Get in touch and share your thoughts at blog@hocnest.com