I included a uniqueness constraint in my app to prevent what appears to be the same entry from appearing in a list multiple times and confusing people.

I later realised that I hadn’t taken PostgreSQL’s case sensitivity into account so multiple otherwise identical names were allowed, potentially confusing people.

-- SELECT * FROM tags
-----------
   name
-----------
 john
 JOHN
 jOhN
-----------
(3 rows)

Rather than slinging data around in the model validations AND again in the database constraints I decided the best way to deal with it would be to convert the column from string to citext (case insensitive text) and let Postgres deal with it.

---

The first thing I tried was to migrate the column to citext:

def change
  change_column :tags, :name, :citext
end

The result was an error stating that Postgres doesn’t know about citext; PG::UndefinedObject: ERROR: type "citext" does not exist. That was easily fixed, I added enable_extension 'citext' to the migration and ran rails db:migrate again.

---

The next problem I encountered was when I tried to rollback. Whenever I write a migration I try to remember to test that it works backwards as well as forwards. In this case I received an ActiveRecord::IrreversibleMigration error due to the fact that change_column is not reversible (the current way it’s defined doesn’t let you specify what column type it’s being changed from so the rollback has no idea what to change it back to).

I could have used a reversible block for each direction but I find separate up and down methods to be clearer. In this case I renamed change to up and duplicated it (with two minor changes) for down:

def up
  enable_extension 'citext'
  change_column :tags, :name, :citext
end

def down
  disable_extension 'citext'
  change_column :tags, :name, :string
end

The rollback still failed though, this time with a mysterious error stating the column doesn’t exist:
PG::UndefinedColumn: ERROR: column "name" of relation "tags" does not exist

After double checking for typos, scratching my head a bit and then triple checking for spelling mistakes it finally dawned on me what I’d done. By disabling the citext extension too early I’d effectively dropped a cloak of invisibility over the citext column. Postgres could no longer see it.

Once I swapped the order of the method calls in the down method the migration worked in both directions and everything was hunky dory. Everything, that is, except for one little niggle…

---

I thought about future me and realised what could happen in six months time (or six days for that matter) once I’d forgotten all about this little disappearing column problem. Some day in the future I’ll probably copy this migration into another file, or maybe even another app.

The up migration will work, the down migration will work and I’ll be happy as larry… right up until I try to rollback even further. After this roll back the citext extension will be gone and rolling back the next migration that refers to a citext column will raise an undefined column error… in a completely different file!

The chances of all those things happening are vanishingly small but if it were to happen I can see myself spending hours pulling my hair out trying to figure it out! If fixing it were difficult I might decide to accept the (tiny) risk of it happening but in this case defending against it is as easy as splitting it out into two migrations and providing the reason why in my commit message:

# migration 1.rb
class EnableCitext < ActiveRecord::Migration[6.0]
  def change
    enable_extension 'citext'
  end
end


# migration 2.rb
class ConvertTagNameToCaseInsensitive < ActiveRecord::Migration[6.0]
  def up
    change_column :tags, :name, :citext
  end

  def down
    change_column :tags, :name, :string
  end
end

Now, if I ever copy and paste the contents of ConvertTagNameToCaseInsensitive into another migration it will either work perfectly (in both directions) or I’ll get a sensible error reminding me to enable the citext extension first.