Decision about Rails, PostgreSQL, DataTypes, Lookups

Avatar of jeromedalbert
Backend Engineer at StackShare ·
RailsRails
PostgreSQLPostgreSQL
#DataTypes
#Lookups

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

5 upvotes·3 comments·4.4K views
bufordtaylor
bufordtaylor
·
December 10th 2018 at 2:10pm

Well I'll be damned

·
Reply
Tom Hoen
Tom Hoen
·
December 10th 2018 at 5:37pm

Do you need to reindex the column after changing the type?

·
Reply
Jerome Dalbert
Jerome Dalbert
·
December 10th 2018 at 6:04pm

Yeah, you want to do something like:

add_index :blah, :slug

(probably with a "unique: true" too)

·
Reply
Avatar of Jerome Dalbert

Jerome Dalbert

Backend Engineer at StackShare