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.
Consider the table
User which has about 15 million rows.
User - first_name - last_name - email
We want to add a
locked flag to the table so we can restrict access to users without deleting them.
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
> 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.
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
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
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
to with Rails 5. Otherwise we need to implement both
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
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