Case insensitive emails and usernames with Postgres

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]"
      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.

Summary: Using citext makes things easier for us. We don’t have to worry about character case or data integrity, it’s just automatically handled for us. Actual code for the Phoenix example is available on the citext branch of my phoenix_examples repo on Github.