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:
and populate the migration with:
1 2 3
I really like this way of writing custom, yet reversible, SQL!
We apply the migration with:
Now we can create our new
User model with case insensitive email:
Please note that here we still specify
string – the generator will not allow
us to use
Let’s open the migration file and change the content to:
1 2 3 4 5 6 7 8 9
Here the specified column type is
citext. We’re also adding a
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
That’s fine for now. The important thing here is the specified type –
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
Let’s fire up IEx and see if our code works:
The last query also works – our search by email is case insensitive! How about duplicate email addresses and our unique index?
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
Now we can use it:
1 2 3 4 5
You can read more about implementing custom types in the documentation. It’s fn :)