Rails's DB-Insensitive Case Insensitivity

I’ve been working with Rails for the past few weeks. But seriously, folks, it’s been great. Anyway, what are we all here for? To hear about my first big trouble with Rails.

We had a simple request at work to make a database field case-insensitive. Which is all to say, we had to make this touch field’s once defaultly true case sensitivity:

validates_uniqueness_of :a_touchy_field

into a blatant lie:

validates_uniqueness_of :a_touchy_field, :case_sensitive => false

And this is why Rails is so great. We’re done!

BUT WAIT. WHAT ABOUT THE BIG TROUBLE?

Oh right! Well, this field is very touchy. Touchy like… millions of rows touchy. And it didn’t like all this insensitivity we we’re giving it. And neither did MySQL. There’s a lighthouse ticket all about it.

The issue is that to keep things DB-neutral, Rails is having MySQL perform a LOWER() on that field on each row to do a binary comparison for the uniqueness validation. How unkind to our conveniently indexed database field.

By default, MySQL does a case-insensitive string comparison. So we rolled our own uniqueness validation…

def TouchyModel < ActiveRecord::Base
  validate :ensure_unique_case_insensitive_touchy_field

  def ensure_unique_case_insensitive_touchy_field
    if new_record? || a_touchy_field_changed?
      if TouchyModel.exists?(:a_touchy_field => a_touchy_field)
        errors.add(:a_touchy_field, "must be alone, or else it'll get whiny")
      end
    end
  end
end

This lets us tip-toe around the call to LOWER() and get on with our validations using the default MySQL comparison.