TLDR: Use case insensitive text (citext) in Postgres, and don’t worry about email/usernames in application code.

Problem: Emails and usernames usually need to be unique and case insensitive. Typically, this gets handled one of two ways: lowercasing the value when saving, or less frequently, creating a unique index for email/username that uses the lowercase version, like LOWER(username)

Both of these have issues though. Lowering the values loses data and can be annoying later on, and also puts responsibility for data integrity on the developer. Using a LOWER() index doesn’t lose data, but it requires lowering values when querying on that field.

Solution: Postgres has a case insensitive text extension, called citext (big surprise). Citext works exactly like a text column, except it keeps the submitted value and indexes it how we want. This means we never have to worry about case sensitivity in our application code.

Support: Phoenix (Ecto) and Rails (ActiveRecord) both support citext, each with only a tiny tweak necessary. Additionally, because Postgres doesn’t enable the Citext extension by default, we need to enable it via a migration.

Example: Using fresh Phoenix and Rails apps, let’s add a users table that has a single column, email . Using the built in generators will help show how minimal the tweaking required is.

# Phoenix mix phx.gen.html Accounts User users email:string # Phoenix doesn't accept :citext as a valid type for the generator, so we will use :string here. # Rails bundle exec rails generate model User email:citext

After running the generators, here’s the tweaks needed for both Phoenix and Rails:

# priv/repo/migrations/20190416235625_create_users.exs use Ecto.Migration def change do + execute "CREATE EXTENSION IF NOT EXISTS citext", "DROP EXTENSION IF EXISTS citext" create table(:users) do - add :email, :string + add :email, :citext timestamps() end + create index(:users, [:email], unique: true) end # db/migrate/20190416235153_create_users.rb def change + enable_extension(:citext) create_table :users do |t| t.citext :email + t.index :email, unique: true t.timestamps end

Now it’s ready to go! Let’s run the migrations and add some tests to verify it works.

# Phoenix mix ecto.migrate # Rails bundle exec rails db.migrate

Couple quick tests to double check.

# test/my_app/accounts/accounts_test.exs defmodule MyApp . AccountsTest do # ... describe " users" do # ... test " can't duplicate case insensitive emails" do email = " [email protected] .com" user = Accounts . create_user (%{ email: email }) assert { :error , % Ecto . Changeset { errors: [ email: { " has already been taken" , _ }]} } = Accounts . create_user (%{ email: String . downcase ( email )}) end # ... end

# test/models/user_test.rb class UserTest < ActiveSupport :: TestCase setup do @email = [email protected]" @user = User . create ( email: @email ) end test "looks up case insensitive email" do assert_equal @user , User . find_by ( email: @email . downcase ) end test "can't duplicate case insensitive emails" do # DB constraint will raise an error assert_raise ( ActiveRecord :: RecordNotUnique ) { User . create ( email: @email . downcase ) } # validates_uniqueness_of will return a user object which is not persisted to the DB, and has an error on it assert_equal [ "has already been taken" ], User . create ( email: @email . downcase ). errors . messages [ :email ] end end

Note about the Rails tests: When using validates_uniqueness_of , ActiveSupport will return an unpersisted model object, which includes the validation errors. Therefore, we use one assertion when relying on Postgres and a different assertion when using the validation helper. Because there are varying opinions on using validates_uniqueness_of , I’ve included both examples.