Why does Postgrex - Ecto throw this not_null_violation error?

Question

Error

Here's the error that I encountered when I'm testing my Account changeset. It seems like it would only be caused by the Ecto migration with wrongly structured database, but the ecto.migrate runs fine, as also Postgresql doesn't throw any error when I'm trying to insert a row using a similar changeset below.

** (Postgrex.Error) ERROR 23502 (not_null_violation): null value in column "email" violates not-null constraint

     table: accounts
     column: email

 Failing row contains (118, 66168645856, 1, 2018-08-17 03:19:12.176247, 2018-08-17 03:19:12.17626, null, null, null, null).
 code: account = insert(:account)
 stacktrace:
   (ecto) lib/ecto/adapters/sql.ex:554: Ecto.Adapters.SQL.struct/8
   (ecto) lib/ecto/repo/schema.ex:547: Ecto.Repo.Schema.apply/4
   (ecto) lib/ecto/repo/schema.ex:213: anonymous fn/14 in Ecto.Repo.Schema.do_insert/4
   (ecto) lib/ecto/repo/schema.ex:125: Ecto.Repo.Schema.insert!/4
   test/schema/account_test.exs:26: (test)

Ecto migrations

migration_create_account.ex

def change do
    create table(:accounts) do
        add :phone_number, :string
        add :access_level, :integer

        timestamps()
    end
end

migration_add_account.ex

def change do
    alter table(:accounts) do
        add :email, :string
        add :auth_token, :string
        add :auth_token_expires_at, :utc_datetime
        add :signed_in_at, :utc_datetime
    end

    create unique_index(:accounts, :email, where: "email IS NOT NULL")
    create unique_index(:accounts, [:phone_number], where: "phone_number IS NOT NULL")
end

ExMachina

factory.ex

def account_factory do
    random_mobile_number = Enum.map(0..10, fn _i -> :rand.uniform(9) end)
    |> List.foldl("", fn i, acc -> acc <> "#{i}" end)

    %Account{
      phone_number: random_mobile_number,
      access_level: 1
    }
end

ExUnit

account_test.exs

describe "Account.changeset/2" do
   test "should check for valid phone number" do
     account          = insert(:account)
     negative_number  = %{phone_number: "-123233239" }
     refute changeset(account, negative_number).valid?
   end
end

Ecto schema and changeset

schema "accounts" do
    field :email       , :string
    field :phone_number, :string
    field :access_level     , :integer
    field :access_level_text, :string, virtual: true
    field :auth_token           , :string
    field :auth_token_expires_at, :naive_datetime
    field :signed_in_at         , :naive_datetime

    timestamps()
end

@required_params ~w(phone_number email access_level access_level_text)
def changeset(account, attrs) do
  account
  |> cast(attrs, @required_params)
  |> cast_access_level_text()
  |> validate_required([:access_level])
  |> validate_required_contact_handle()
  |> validate_number(:access_level, less_than: 3, greater_than: 0)
  |> validate_subset(:access_level_text, @access_levels)
  |> validate_format(:email, @email_regex)
  |> validate_format(:phone_number, @phone_number_regex)
  |> unique_constraint(:phone_number)
end

Exceptions in Elixir are _raised_, not _thrown_ in the first place. Try to avoid this confusion, since Elixir has also [`Kernel.throw/1`](https://hexdocs.pm/elixir/master/Kernel.html#throw/1) for non-local returns. Now the main question is: **Ecto raises when you try to do exactly what?** — Aleksei Matiushkin, Aug 17 '18 at 04:16
Your `account_factory` is missing email. You defined `email IS NOT NULL` — TheAnh, Aug 17 '18 at 04:30
@TheAnh that’s not correct, `email` is defined as nullable. — Aleksei Matiushkin, Aug 17 '18 at 04:36
Try using a list instead of the `:email` atom by itself: `unique_index(:accounts, [:email])`. I also don't think you need the where clause to exclude null values since postgres does not take null values into account with unique constraints. — Abdullah Esmail, Aug 17 '18 at 06:34
You also need a `unique_constraint()` for the email field in your changeset. — Abdullah Esmail, Aug 17 '18 at 06:36
Off topic: in your `factory.ex` file, you can do the following: `Enum.random(1_000_000_000..9_999_999_999) |> to_string()` to get a random phone number. Much cleaner and easier to read. — Abdullah Esmail, Aug 17 '18 at 06:46

score 0 · Answer 1 · answered Aug 17 '18 at 08:42

Thanks guys. What happened in my case is that because I changed migrations after using ecto.migrate, so that the migration changes differs between the test database and development database.

I just ran MIX_ENV=test mix ecto.reset to sync database between the environments.