Testing unique indexes in your db

The Perils of Uniqueness Validations by Derek Prior and the Ruby on Rails Tutorial by Michael Hartl mention that:

class Service < ActiveRecord::Base
  validates :name, uniqueness: { case_sensitive: false }

doesn’t actually ensure each service has a unique name in the database. The articles recommend adding unique indexes to the database for extra protection.

Here is the rspec example I wrote to test the unique index at the database level:

require 'spec_helper'

describe Service do

  describe "validations" do

    it "raises error if name is not unique (case insensitive), even if validations pass" do
      create(:service, name: "UPCASE")
      service2 = create(:service)
      expect { service2.update_columns(name: "upcase") }.to raise_error(ActiveRecord::RecordNotUnique)



Unfortunately, this test fails because the Postgres unique index is case-sensitive.

Michael Hartl suggests adding a callback to standardize the casing of each record before saving. But:

  • What if ActiveRecord callbacks are skipped?
  • What if the database is accessed from outside the Rails app?
  • What if I need to preserve the capitalization of records exactly as users have entered them?

I could make the Postgres index case-insensitive by using raw SQL in my migrations, and by switching from a schema.rb file to a structure.sql file. But I would rather stay database agnostic, so I will mark that test “Pending” and come back to this later to decide how to deal with this issue.

The ability to add case-insensitive indexes through rails migrations in a database agnostic manner would be a great addition to Rails. Maybe I could work on this at some point in the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s