Does your PostgreSQL-backed Rails app deal with slugs, emails or usernames? Do you find yourself littering your code with things like Model.where('lower(slug) = ?', slug.downcase)?

Postgres strings are case-sensitive, but you often want to look these fields up regardless of case. So you use downcase/lower everywhere... You may refactor this inconvenience in dedicated methods like find_by_slug, but all too often your team will forget about it and use find_by(slug:, leading to inevitable bugs.

What if I told you that you could delegate all this dirty work to Postgres, thanks to the case-insensitive citext type? You can change your column type to citext like so:

class ChangeSlugsToCitext < ActiveRecord::Migration
  def change
    enable_extension('citext')
    change_column :blah, :slug, :citext
  end
end

Now, you can use find_by(slug: as you are used to, and Postgres will internally call lower on the two compared values. Problem solved!

#Lookups #DataTypes