Making a field unique in ecto

Question

How to make a field unique in ecto?

I thought it's the same as the active record in Ruby, but it seems it isn't

Answer 1

You want to use unique_constraint/3. This is not like Active Record because it is using the database to ensure uniqueness. Active Record would do a query for records with the same value and if any were returned then it would fail. This has a race condition where, if a value is inserted between fetching to check uniqueness and inserting your record, you will either end up with duplicate data or an error being raised (depending on if an index is set on the database or not. unique_constraint/3 does not have this race condition.

One thing worth noting is that since the uniqueness is not known until an insert is attempted, the unique constraint will happen after your validations. It is not possible to display both validation and constraint errors at once.

The database you are using must support unique constraints too. They won't work with SQLite. You can read more on the GitHub issue.

In your migration:

create unique_index(:users, [:email]) 

Then in your model:

cast(user, params, ~w(email), ~w()) |> unique_constraint(:email) 

---

It is worth noting that Ecto used to provide a validate_unique/3 function which worked by doing a query on the database, however it was deprecated in favour of unique_constraint/3 In version 0.16.0