My experience with Rails Active Record migrations

WOCinTechChat.com

Imagine you’re a sales rep. You need to organize the steps you’ll take to make a sale. Think emails, phone calls and more.

You’d want to use one of SalesLoft’s most popular features, a Cadence, to manage this process in an organized way. How do you know if your Cadence is effective? A more recent feature called Cadence Analytics showcases a ton of data to help you answer that question.

A few months ago, I had the opportunity to collaborate with the software engineers that built Cadence Analytics. The team wanted me to use a Rails Active Record migration to change the data type of primary key ids from int to bigint in several tables that make up the Cadence Analytics database.

Overview

First, I’ll explain why this work was necessary. I’ll share the questions we considered along the way. I’ll also share which teams we collaborated with in order to approach this task successfully and with minimal impact to our customers.

Why

This modification was necessary for two reasons.

PostgreSQL, our relational database management system, has three integer data types: smallint, integer and bigint. Each has a maximum number it can hold (learn more here).

A lot of data is consumed in Cadence Analytics. We were afraid of auto-incrementing int fields within a given table reaching its max integer. If this occurred, PostgreSQL would raise a numeric field overflow error. The impact of an error like this depends on your application. That said, once an error like this happens, no more inserts can occur on the respective table.

Secondly, the team wanted the primary key and foreign key data types to be the same. If a primary key id datatype was bingint, we wanted the foreign key that referenced it to be bigint as well.

Preliminary questions

Before getting started, we connected with the database engineering and tech operations teams. My top questions for them were:

• How long will a migration like this take?
• Would there be an impact on users during the migration?
• Should the migration be done outside of business hours?

I learned that the duration of a migration is dependent on the size of the table. This meant that a migration on a table that contained a lot of data could take several minutes or more.

I also learned that when a table is undergoing a migration, it is locked until that migration has completed. Note, there are different levels of locking that a table can undergo. In our case, the type of migration we wanted to run would lock a table such that reads or writes on that table would be blocked.

For these reasons, the team and I decided that it would be best to perform the migrations outside of business hours.

How

In a Ruby on Rails application, the specific process we used to change the data type of the primary key ids from int to bigint is called an Active Record migration. A migration takes your current database schema and modifies it to add, remove or alter table columns or entries.

Since SalesLoft uses a continuous integration development process, I divided my work into three phases for a given migration.

Phase one: create a standalone migration

• Create a new branch to begin my work on.
• Use the Active Record generator to create a standalone migrationrails generate migration ChangeCadenceTeamIdToBigInt.
• Define up and down methods in my Migration class. The up method describes the column changes we’re going to make. The down method describes what to do if we ever need to undo the change. Alternatively, you can use the def change method to accomplish the same thing. See mock example below.

Phase two: execute migration on my branch and submit a pull request

• Execute the migration file locally with rake db:migrate. This command will also make the changes to our database and update our schema.
• Push changes on my branch to GitHub and create a pull request.
• Test changes in a QA environment to be sure that nothing is broken.

Phase three: execute migration manually in production and merge pull request

• Open an interactive shell in one of the cadence analytics pods.
• Connect to the cadence analytics database. Now that we’re in production, write the schema modifications in pure SQL instead.
• The migration I created in phase 1 has a version number, for example 20190829141124_change_cadence_team_id_to_big_int.rb. I use that version number to specify an addition to an internal Rails database table called schema_migrations like so insert into schema_migrations (version) values ('20190829121124');.
• Finally, I merge my pull request into the master branch. The migration won’t run again since I’ve added it manually to the schema_migrations table.

It was atypical for us to execute the migration changes manually with SQL. This approach allowed us to easily view the resulting logs separate from logging that occurs during the deployment process.

Conclusion

Photo by Val Vesa on Unsplash

My key takeaways from this experience are that:

• A task like this requires teamwork.
• You should chat with your team about the best way execute the migration (manually or automatically).
• It’s very important to collaborate with database engineering and tech operations teams to learn about the impact executing your migration will have (i.e. locking tables).

Thanks for reading!