Locks

image by Marcos Mayer

Use Multiple Migrations When Adding Database Constraints

Adding constraints to your application at the database level is a good idea as it provides an extra layer of quality control on top of the data powering your application.

RailsConf 2024

I'm co-chairing RailsConf 2024 in Detroit May 7–9. Come and join us 

One way to do that is to add default values or constraints—like making sure a field can’t be blank. This is easy to do with Rails’ migrations. But beware…

Instead of…

…adding a column and stipulating a default non-null value all in one go:

class AddComposerToSongs < ActiveRecord::Migration[5.2]
  def change
    add_column :songs, :composer, :string, default: "Lin-Manuel Miranda", null: false
  end
end

Use…

…a ‘multiple migration’ strategy to add a field with a constraint to your databases.

Add a column

class AddComposerToSongs < ActiveRecord::Migration[5.2]
  def change
    add_column :songs, :composer, :string
  end
end

Deploy code that sets default, in your application

class Song < ApplicationRecord
  before_save :set_default_composer

  def set_default_composer
    self.composer = "Lin-Manuel Miranda" if composer.nil?
  end
end

This will catch any code that writes to the database during this intermediate stage.

Update records with nil values

Using a rake task or using rails console.

Song.where(composer: nil).find_each do |song|
  song.update(composer: "Lin-Manuel Miranda")
end

Change the column to have a constraint

class AddRecommendedIndexes < ActiveRecord::Migration[5.1]
  def change
    change_column :songs, :composer, :string, default: "Lin-Manuel Miranda", null: false
  end
end

…and then you can delete the Ruby code that sets a default.

But why?

If you take the “all in one” approach you run the risk of significant downtime for your application.

If you set null: false in your migration, the database will rewrite the whole table, locking it whilst doing so. This may take a significant amount of time, depending on how much data you already have stored. Locking your database’s table will likely cause write timeouts for any users trying to write to your database at the same time.

The multi-stage deployment is a bit of a pain, but it enables you to keep the application available for your users during any migration of data tables.

Why not?

If you’re starting a new app or have a very limited dataset.

You might get away with a whole table rewrite and write-lock if you have low amounts of traffic writing to the database. But you would be getting away with it. Chances are you’re going to have some downtime.

If you don’t mind putting your application into maintenance mode, where users are blocked from using the site, you could run the migration with downtime.

Brighton Ruby 2024

Still running UK’s friendliest, Ruby event on Friday 28th June. Ice cream + Ruby 


Last updated on January 13th, 2019 by @andycroll

An email newsletter, with one Ruby/Rails technique delivered with a ‘why?’ and a ‘how?’ every two weeks. It’s deliberately brief, focussed & opinionated.