Build Case-Insensitive Lookups with Rails' New QueryPredicates Types

· 10 min read

Note: ActiveRecord::Type::QueryPredicates is coming in Rails 8.2. It’s merged to main (PR #58479) but not in a released gem yet. The latest release is 8.1.3, so the API can still shift before it ships. To try it, point your Gemfile at the main branch:

gem "rails", github: "rails/rails", branch: "main"

That pulls in all of main’s unreleased changes, not just this one. Don’t do it on an app you can’t afford to break.

Every app I’ve worked on has the same bug in five places. A user signs up as [email protected], then tries to log in as [email protected], and User.find_by(email:) comes back empty. So someone writes where("lower(email) = ?", email.downcase). Then someone else adds a uniqueness validation and forgets case_sensitive: false. Then a third person sorts the admin list by username and Zed lands above adal.

The rule “emails compare case-insensitively” belongs to the attribute, but until now Rails had nowhere to put it. Rails 8.2 adds one: an Active Record type can define the SQL expression that queries compare through. Write lower() once, in the type, and every find_by, where, order, and uniqueness check picks it up.

What We’re Building

A User model where:

  • email and username match regardless of case, so [email protected] finds [email protected].
  • display_name also ignores accents, so a search for jose marti finds José Martí.
  • The stored value keeps the user’s own casing.
  • Every lookup uses a real index.

All with zero lower() calls in application queries.

The Rails Feature That Makes This Clean

Include ActiveRecord::Type::QueryPredicates in a type and override one method:

def comparison_expression(expression)
  Arel::Nodes::NamedFunction.new("lower", [expression])
end

Active Record applies that expression symmetrically to both sides of every comparison: the stored column and the value you query with. That covers =, IN, !=, ranges, IS NULL, subqueries, and ORDER BY. Writes are untouched, because serialize runs as normal and the expression only applies when comparing. group and aggregates also use the stored value.

Building It

This build uses PostgreSQL. The case-insensitive half works on any adapter that has lower(). Note that SQLite’s lower() only folds ASCII letters.

The Migration

The type tells Rails how to compare. The database needs a matching expression index, both for speed and to enforce uniqueness at the database level:

class CreateUsers < ActiveRecord::Migration[8.2]
  def change
    create_table :users do |t|
      t.string :email, null: false
      t.string :username, null: false
      t.string :display_name
      t.timestamps
    end

    add_index :users, "lower(email)", unique: true, name: "index_users_on_lower_email"
    add_index :users, "lower(username)", unique: true, name: "index_users_on_lower_username"
  end
end

The index expression must be exactly the one the type emits, lower(email), or PostgreSQL won’t use it.

The Type

# app/types/case_insensitive_string.rb
class CaseInsensitiveString < ActiveRecord::Type::String
  include ActiveRecord::Type::QueryPredicates

  def comparison_expression(expression)
    Arel::Nodes::NamedFunction.new("lower", [expression])
  end
end

app/types autoloads like any other directory under app/. Restart the server after you create it.

The Model

class User < ApplicationRecord
  attribute :email, CaseInsensitiveString.new
  attribute :username, CaseInsensitiveString.new

  normalizes :email, with: ->(email) { email.strip }

  validates :email, :username, presence: true, uniqueness: true
end

That’s the whole feature. Notice what’s missing: no case_sensitive: false, no downcase in normalizes, no custom scopes. normalizes also composes with the type: Rails strips the query value first, then wraps it in lower().

Seeing It Work

User.create!(email: "[email protected]",   username: "AdaL",  display_name: "Ada Lovelace")
User.create!(email: "[email protected]",  username: "josé",  display_name: "José Martí")
User.create!(email: "[email protected]", username: "grace", display_name: "Grace Hopper")
User.create!(email: "[email protected]",   username: "Zed",   display_name: "Zoë Zed")
User.create!(email: "[email protected]",   username: "bob",   display_name: "Bob")

Lookups ignore case, and normalizes trims the input as well:

User.find_by(email: "  [email protected] ").username
# SELECT "users".* FROM "users" WHERE lower("users"."email") = lower($1) LIMIT $2
#   [["email", "[email protected]"], ["LIMIT", 1]]
# => "AdaL"

Arrays, negation, and ranges all use the same expression:

User.where(username: ["ADAL", "GRACE"]).pluck(:username)
# WHERE lower("users"."username") IN (lower($1), lower($2))
# => ["AdaL", "grace"]

User.where.not(username: "BOB").count
# WHERE lower("users"."username") != lower($1)
# => 4

User.where(username: "a".."c").pluck(:username)
# WHERE lower("users"."username") BETWEEN lower($1) AND lower($2)
# => ["AdaL", "bob"]

Ordering is case-insensitive, so Zed no longer jumps the queue:

User.order(:username).pluck(:username)
# ORDER BY lower("users"."username") ASC
# => ["AdaL", "bob", "grace", "josé", "Zed"]

The uniqueness validation uses the expression too:

user = User.new(email: "[email protected]", username: "ADAL")
user.valid?
# SELECT "users"."id" FROM "users" WHERE lower("users"."email") = lower($1) LIMIT $2
# SELECT "users"."id" FROM "users" WHERE lower("users"."username") = lower($1) LIMIT $2
# => false
user.errors.full_messages
# => ["Email has already been taken", "Username has already been taken"]

The stored value is untouched:

User.find_by(username: "adal").email
# => "[email protected]"

And the index is used:

User.where(email: "[email protected]").explain
 Index Scan using index_users_on_lower_email on users
   Index Cond: (lower((email)::text) = '[email protected]'::text)

find_or_create_by!, exists?, and first_or_create! go through the same predicate builder, so they match case-insensitively as well. The validation can still race with a concurrent insert. If that happens, the unique index raises ActiveRecord::RecordNotUnique, the same as for any other uniqueness check.

Taking It Further: Accent-Insensitive Names

Case is only half of the problem for names. A user who types jose marti expects to find José Martí. PostgreSQL’s unaccent extension removes accents, but you can’t index it directly, because it isn’t marked IMMUTABLE. The standard fix is a thin wrapper that pins the dictionary:

class AddFoldedDisplayNameIndex < ActiveRecord::Migration[8.2]
  def up
    enable_extension "unaccent"
    execute <<~SQL
      CREATE FUNCTION immutable_unaccent(text) RETURNS text
        LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
        AS $$ SELECT public.unaccent('public.unaccent'::regdictionary, $1) $$;
    SQL
    add_index :users, "lower(immutable_unaccent(display_name))",
      name: "index_users_on_folded_display_name"
  end

  def down
    remove_index :users, name: "index_users_on_folded_display_name"
    execute "DROP FUNCTION immutable_unaccent(text)"
  end
end

schema.rb can’t record a custom function. Switch to config.active_record.schema_format = :sql so db/structure.sql keeps it. If you don’t, db:schema:load fails when it tries to build the index.

The type builds on the one we already have. Wrap the expression, then hand it to super:

# app/types/folded_string.rb
class FoldedString < CaseInsensitiveString
  def comparison_expression(expression)
    super(Arel::Nodes::NamedFunction.new("immutable_unaccent", [expression]))
  end
end
class User < ApplicationRecord
  attribute :display_name, FoldedString.new
  # ...
end
User.where(display_name: "jose marti").pluck(:display_name)
# WHERE lower(immutable_unaccent("users"."display_name")) = lower(immutable_unaccent($1))
# => ["José Martí"]

User.find_by(display_name: "ZOE ZED").username
# => "Zed"

EXPLAIN shows a bitmap index scan on index_users_on_folded_display_name. The same pattern works for any comparison rule you can write as SQL: date(created_at) for day-level matching, or trim() for legacy data with stray whitespace.

Wrapping Up

We built a User model whose email, username, and display name compare the way people expect: case-insensitive, accent-insensitive, indexed, and enforced by the uniqueness validation. Each rule lives in one small type instead of in every query. The feature that made this possible is ActiveRecord::Type::QueryPredicates and its one method, comparison_expression.

For the implementation and its many edge cases (enums, serialized columns, subqueries, and association joins), read PR #58479. For more about custom attribute types, see type-safe JSON attributes with has_json.