where ecto's unit test inserted data?

Question

I have imitated hexpm project to write repo related unit test.

But the duplicated insert operation is always successful and assert :error failed.

assert error message is as follows:

macbook:pen yuchen$ mix test
   
 ...
    
      1) test username and email are unique (Pen.Account.UserTest)
         test/pen/accounts/user_test.exs:39
         match (=) failed
         code:  assert {:error, changeset} =
                  User.build(%{
                    name: "some_other_username",
                    email: "some_other_email@example.com",
                    password: "password"
                  })
                  |> insert()
         left:  {:error, changeset}
         right: {
                  :ok,
                  %Pen.Accounts.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, email: "some_other_email@example.com", id: 170, inserted_at: ~U[2022-09-06 08:08:35.902874Z], name: "some_other_username", password: "$2b$12$M1S.X2GmnGD/uASom2v01OYwdihE7sVbp4YY5mZAwgY9ITPMYdgzu", updated_at: ~U[2022-09-06 08:08:35.902874Z]}
                }
         stacktrace:
           test/pen/accounts/user_test.exs:54: (test)
    
    .
    
    Finished in 1.9 seconds (1.7s async, 0.1s sync)
    5 tests, 1 failure

My unit test code is as follows:

defmodule Pen.Account.UserTest do
  use Pen.DataCase, async: true
  require Logger

  alias Pen.Accounts.{Auth, User}

  setup do
    {:ok,user} = insert(%User{password: Auth.gen_password("password")})
    %{user: user, password: "password"}
  end

  describe "build/2" do
    test "builds user" do
      changeset =
        User.build(%{
          name: "username",
          email: "mail@example.com",
          password: "password"
        })

      assert changeset.valid?
    end
  end

  # test "validates username" do
  #   changeset = User.build(%{name: "x"})
  #   assert errors_on(changeset)[:name] == ["should be at least 3 character(s)"]

  #   changeset = User.build(%{name: "{€%}"})
  #   assert errors_on(changeset)[:name] == ["has invalid format"]
  # end

  # test "validates password" do
  #   changeset = User.build(%{password: "x"})
  #   assert errors_on(changeset)[:password] == ["should be at least 7 character(s)"]
  # end

  test "username and email are unique", %{user: user} do
    assert {:ok, changeset} =
             (User.build(
               %{
                 name: user.name,
                 email: "some_other_email@example.com",
                 password: "password"
               }
              #  ,
              #  true
             )
             |> insert())
    Logger.debug("first: #{inspect changeset}")
    # assert errors_on(changeset)[:name] == ["has already been taken"]

    assert {:error, changeset} =
             (User.build(
               %{
                 name: "some_other_username",
                 email: "some_other_email@example.com",
                 password: "password"
               }
             )
             |> insert())

    assert errors_on(changeset)[:email] == "already in use"
    # end
  end

sandbox is used, datacase code is as follows:

defmodule Pen.DataCase do
  @moduledoc """
  This module defines the setup for tests requiring
  access to the application's data layer.

  You may define functions here to be used as helpers in
  your tests.

  Finally, if the test case interacts with the database,
  we enable the SQL sandbox, so changes done to the database
  are reverted at the end of every test. If you are using
  PostgreSQL, you can even run database tests asynchronously
  by setting `use Pen.DataCase, async: true`, although
  this option is not recommended for other databases.
  """

  use ExUnit.CaseTemplate

  using do
    quote do
      # alias Pen.Repo
      import Pen.Repo

      import Ecto
      import Ecto.Changeset
      import Ecto.Query
      import Pen.DataCase
    end
  end

  setup tags do
    Pen.DataCase.setup_sandbox(tags)
    :ok
  end

  @doc """
  Sets up the sandbox based on the test tags.
  """
  def setup_sandbox(tags) do
    pid = Ecto.Adapters.SQL.Sandbox.start_owner!(Pen.Repo, shared: not tags[:async])
    on_exit(fn -> Ecto.Adapters.SQL.Sandbox.stop_owner(pid) end)
  end

  @doc """
  A helper that transforms changeset errors into a map of messages.

      assert {:error, changeset} = Accounts.create_user(%{password: "short"})
      assert "password is too short" in errors_on(changeset).password
      assert %{password: ["password is too short"]} = errors_on(changeset)

  """
  def errors_on(changeset) do
    Ecto.Changeset.traverse_errors(changeset, fn {message, opts} ->
      Regex.replace(~r"%{(\w+)}", message, fn _, key ->
        opts |> Keyword.get(String.to_existing_atom(key), key) |> to_string()
      end)
    end)
  end
end

I have checked postgres db，the users table records are not increased after every unit test.

 id | name |            email             |                           password                           |     inserted_at     |     updated_at      
----+------+------------------------------+--------------------------------------------------------------+---------------------+---------------------
  1 |      |                              | $2b$12$hw0fBhi7PtcjVdXRocxnReaToBe.IyeFZa7qvRolU2ymdgcmFbufW | 2022-09-06 05:47:41 | 2022-09-06 05:47:41

users's migration is as follows:

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

  def change do
    create table(:users) do
      add :name, :string
      add :email, :string
      add :password, :string

      timestamps()

    end
    create unique_index(:users, [:name],[unique: true])
    create unique_index(:users, [:email],[unique: true])
  end
end

postgres users's structure is as follows:

                                          Table "public.users"
   Column    |              Type              | Collation | Nullable |              Default              
-------------+--------------------------------+-----------+----------+-----------------------------------
 id          | bigint                         |           | not null | nextval('users_id_seq'::regclass)
 name        | character varying(255)         |           |          | 
 email       | character varying(255)         |           |          | 
 password    | character varying(255)         |           |          | 
 inserted_at | timestamp(0) without time zone |           | not null | 
 updated_at  | timestamp(0) without time zone |           | not null | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_index" UNIQUE, btree (email)
    "users_name_index" UNIQUE, btree (name)

Answer 1

Ecto is typically configured to use a different database for dev and test (defined in config/dev.exs and config/test.exs respectively).

Whenever you modify an existing migration that has already been run (for example, to add a unique index or a field while you're still tweaking your schema), Ecto won't re-run this migration automatically. You'll need to run:

• mix ecto.reset to fix the dev DB
• MIX_ENV=test mix ecto.reset to fix the test DB

If you forget to run the latter, you might end up with different table definitions for your test DB.

Also, it is worth mentioning that the Ecto.Adapters.SQL.Sandbox module which is typically used in tests will wrap each test in a transaction and rollback everything at the end. This is a very powerful mechanism which allows you to keep your tests independent and isolated and still run them in parallel, but it means that the tables in your test DB won't be populated after running your tests.