Unique index constraint not working in Phoenix app

Question

In my Phoenix app I have my User model as follows:

defmodule MyApp.User do
  use MyApp.Web, :model

  schema "users" do
    field :username, :string, unique: true
    field :email, :string, unique: true
    field :crypted_password, :string
    field :password, :string, virtual: true

    timestamps
  end

  @required_fields ~w(email password username)
  @optional_fields ~w()

  @doc """
  Creates a changeset based on the `model` and `params`.

  If no params are provided, an invalid changeset is returned
  with no validation performed.
  """
  def changeset(model, params \\ :empty) do
    model
    |> cast(params, @required_fields, @optional_fields)
    |> unique_constraint(:email)
    |> unique_constraint(:username)
    |> validate_format(:email, ~r/@/)
    |> validate_length(:password, min: 5)
  end
end

I also have the following migration:

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

  def change do
    create table(:users) do
      add :email, :string
      add :username, :string
      add :crypted_password, :string

      timestamps
    end

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

And I have my registration_controller_ex as follows:

defmodule MyApp.RegistrationController do
  use MyApp.Web, :controller
  alias MyApp.User

  def new(conn, _params) do
    changeset = User.changeset(%User{})
    render conn, changeset: changeset
  end

  def create(conn, %{"user" => user_params}) do
    changeset = User.changeset(%User{}, user_params)

    if changeset.valid? do
      user = MyApp.Registration.create(changeset, MyApp.Repo)
      conn
      |> put_flash(:info, "Your account was created")
      |> redirect(to: "/")
    else
      conn
      |> put_flash(:info, "Unable to create account")
      |> render("new.html", changeset: changeset)
    end
  end
end

So, with all that I'm pretty sure my username and email fields in User are unique indexes. I also make sure that they are unique by calling unique_constraint to validate User.changeset. However, when in my interface I create a user with the same email and username as a preivously created one, the changeset is validated and the user is "created". (it's not actually created, when I look into the database nothing is added)

I get the following on my server logs, but my changeset.valid? is true.

[debug] BEGIN [] OK query=139.3ms queue=8.2ms
[debug] INSERT INTO "users" ("crypted_password", "email", "inserted_at", "updated_at", "username") VALUES ($1, $2, $3, $4, $5) RETURNING "id" ["$2b$12$MN1YxFUGLMIJYXseZn0sjuuVs9U1jRdYtRr9D8XQsAqdh.D2sRRXa", "[email protected]", {{2015, 9, 30}, {11, 7, 25, 0}}, {{2015, 9, 30}, {11, 7, 25, 0}}, "username"] ERROR query=5.5ms
[debug] ROLLBACK [] OK query=0.4ms

Also, other things that I look for in my User.changeset function (such as minimum password length and other things) get reported to the user and are working just fine. It's just the unique indexes for :email and :username that are failing to be reported.

Gerard de Brieder Gerard de Brieder · Accepted Answer · 2015-09-30T13:05:43

The unique_constraint will be checked by the database and thus only be triggered upon inserting the record. Calling changeset.valid? will not check constraints and thus in this case return true. You need to check the return tuple of the Repo insert and act on that like:

def create(conn, %{"user" => user_params}) do
  changeset = User.changeset(%User{}, user_params)
  case  MyApp.Repo.insert changeset do
    {:ok, changeset} -> 
      conn
      |> put_flash(:info, "Your account was created")
      |> redirect(to: "/")      
    {:error, changeset} ->
      conn
      |> put_flash(:info, "Unable to create account")
      |> render("new.html", changeset: changeset)      
  end
end

now your changeset.errors is enriched and you should be able to fetch the error with changeset.errors[:email]

Unique index constraint not working in Phoenix app

1 Answers