How to store username or email with case insensitive search using Ecto - Part 2

In a previous blog post I was trying to store username/email in a case insensitive way in postgres. A few folks commented that the citext postgres extension actually did this in a very easy and straightforward way. So, I went back to my code and ripped out the unnecessary complexity and here is what I ended up with:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
defmodule SF.Repo.Migrations.EnableCitextExtension do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION citext", "DROP EXTENSION citext"
  end
end

 SF.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :email, :citext, null: false
      add :magic_token, :uuid
      add :magic_token_created_at, :naive_datetime
      add :confirmation_token, :uuid
      add :confirmed_at, :naive_datetime

      timestamps()
    end

    create index(:users, [:email], unique: true)
    create index(:users, [:magic_token], unique: true)
    create index(:users, [:confirmation_token], unique: true)
  end
end

defmodule SF.User do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "users" do
    field :email, :string
    field :magic_token, Ecto.Base64UUID
    field :confirmation_token, Ecto.Base64UUID
    field :confirmed_at, :naive_datetime

    timestamps()
  end

  @doc false
  def changeset(user, attrs) do
    user
    |> cast(attrs, [:email, :confirmation_token])
    |> validate_required([:email])
    |> unique_constraint(:email)
  end
end

defmodule SF.UserService do

  def find_by_email(email) do
    Repo.one(from u in User, where: u.email == ^email)
  end

end

So, the way citext works is similar to our previous approach. If you want to get into all the gory details about how citext is implemented you can check out the code on GitHub at: https://github.com/postgres/postgres/blob/6dd86c269d5b9a176f6c9f67ea61cc17fef9d860/contrib/citext/citext.c

I am currently working on LiveForm which makes setting up contact forms on your website a breeze.