Case insensitive column in Ecto?

I want to create a case insensitive unique column in my database for the username. I want the user who registers as “Dr.Doom” to be displayed with that case and no other user can be called e.g. “dr.doom”.

According to Ecto documentation

Unfortunately, different databases provide different guarantees when it comes to case-sensitiveness. For example, in MySQL, comparisons are case-insensitive by default. In Postgres, users can define case insensitive column by using the :citext type/extension.

so in the migration I defined a column

add :username, :citext, null: false

but when I do mix ecto.migrate I get

(Postgrex.Error) ERROR (undefined_object): type “citext” does not exist
(ecto) lib/ecto/adapters/sql.ex:187: Ecto.Adapters.SQL.query!/5
(ecto) lib/ecto/adapters/postgres.ex:71: Ecto.Adapters.Postgres.execute_ddl/3
…etc

I am using Postgres

What I am doing wrong? Also in the documentation where can I find the various field types that are available?

2 Likes

All you need is to just make the index user lower. IE

CREATE INDEX users ON name (lower(name)) UNIQUE true;

I don’t know the ecto syntax off hand required to do this, fragments may be required.

4 Likes

Thanks. I also thought of using constraints. May be a clever way to do it. But I am particularly curious about the :citext type

1 Like

I did some more research and I understood somehow what were you saying. It is scary how many things I have yet to learn.

Also I found the solution to the :citext problem. I had to enable it with

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

It was a Postgres problem, not an Ecto problem.

Solution found here

8 Likes

One other advantage that I found with using the citext column type is that I didn’t have to String.downcase my input data or wrap it in a postgres call to lower when querying. In other words I could insert a column with the username mmmries and then if a user searched for MmmrieS it would still find my record. It’s nice to have this automatically done so I don’t forget to downcase a piece of input later.

6 Likes

In case you’re wondering what your migration and schema might look like, consider this example where a “status” resource has a unique “slug” field that should be case-insensitive.

# Migration:
create table(:examples, primary_key: false) do
  add :slug, :string, size: 10, primary_key: true
  add :name, :string, size: 32, null: false
end

create index(:examples, ["lower(slug)"], name: :examples_index, unique: true)

# Schema
use Ecto.Schema
import Ecto.Changeset

@primary_key {:slug, :string, autogenerate: false}
schema "examples" do
   field :name, :string, size: 32
end

The changeset can define the slug as a read-only field, e.g.

validate_change(:slug, fn :slug, _ -> [slug: "slug is a read-only field"] end)
4 Likes

Working perfectly :+1:

And to add to the suggestion: in case one might want to replace a “normal” unique index with one like this (for example on a username column) that could be something like this inside a migration in the simplest case for reference:

drop(unique_index(:user, [:username]))
create(index(:user, ["lower(username)"], name: :user_username_index, unique: true))

In which case a |> unique_constraint(:username) within the changeset validation also should keep working due to the carefully chosen index name.

2 Likes

I wanted to do the same as the OP but wanted to enable the citext extension using a migration and found I could do the following:

defmodule MyApp.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION IF NOT EXISTS citext", "DROP EXTENSION citext"

    create table(:users) do
      add :email, :citext, null: false
      add :password_hash, :string, null: false

      timestamps()
    end

    create unique_index(:users, [:email])
  end
end

https://hexdocs.pm/ecto_sql/Ecto.Migration.html#execute/2

4 Likes

Even better, that execute command could go into its own migration. This way every migration reflects related changes.

3 Likes