I wanted to have a case insensitive database column to store email address in the open source application I’m working on. I’m using PostgreSQL and there’s a nice extension that does exactly that – citext – so I gave it a try. I spent some time figuring things out so now I’m sharing the knowledge in this blog post.

The instructions were tested in Elixir 1.5.3 / Phoenix 1.3.0 application.

We start by enabling citext extension. Run:

1 mix ecto.gen.migration EnableCitextExtension

and populate the migration with:

1 2 3 def change do execute "CREATE EXTENSION citext" , "DROP EXTENSION citext" end

I really like this way of writing custom, yet reversible, SQL!

We apply the migration with:

1 mix ecto . migrate

Now we can create our new User model with case insensitive email:

1 mix phx . gen . schema User users email: string

Please note that here we still specify string – the generator will not allow us to use citext .

Let’s open the migration file and change the content to:

1 2 3 4 5 6 7 8 9 def change do create table ( :users ) do add :email , :citext , null: false timestamps () end create index ( :users , [ :email ], unique: true ) end

Here the specified column type is citext . We’re also adding a NOT NULL constraint and a unique index.

When you open the corresponding generated model it will look similar to:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 defmodule Multipster . User do use Ecto . Schema import Ecto . Changeset alias Multipster . User schema "users" do field :email , :string timestamps () end @doc false def changeset ( % User {} = user , attrs ) do account |> cast ( attrs , [ :email ]) |> validate_required ([ :email ]) end end

That’s fine for now. The important thing here is the specified type – string . In the schema we describe how the database value should be transformed to a value in the Elixir land. Ecto doesn’t know about citext type so we have to keep string here.

Let’s fire up IEx and see if our code works:

1 iex - S mix

1 2 3 4 Multipster . Repo . insert! ( % Multipster . User { email: [email protected]" }) Multipster . Repo . get_by ( Multipster . User , email: [email protected]" ) Multipster . Repo . get_by ( Multipster . User , email: [email protected]" )

The last query also works – our search by email is case insensitive! How about duplicate email addresses and our unique index?

1 Multipster . Repo . insert! ( % Multipster . User { email: [email protected]" })

The above command fails – our unique index is also case insensitive.

However, there was one more small thing that annoyed me. Take a look:

The returned value is exactly as provided by the user when creating the account. It is not converted to lower case. What if we want to somehow change that behaviour? Fortunately Ecto has a very convenient way of defining custom types:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 defmodule EmailType do @behaviour Ecto . Type def type , do : :string def load ( data ) do { :ok , String . downcase ( data )} end def cast ( data ) do { :ok , data } end def dump ( data ) do { :ok , data } end end

Now we can use it:

1 2 3 4 5 schema "users" do field :email , EmailType timestamps () end

You can read more about implementing custom types in the documentation. It’s fn :)