Note: Make sure you help out the next dev by commenting if you get stuck.

You want each test to run in a clean database so they don't interfere with each other. So how do you clear the database?

Best is to keep your tests in a sandbox. A sandbox is basically a temporary database that gets deleted automatically. Here's how to set it up.

1. Config

First configure the testing environment to use sandbox in config/test.exs:

config :bashboard, Bashboard.Repo,
  # your connection settings
  pool: Ecto.Adapters.SQL.Sandbox

2. Overwrite the test command

The command mix test runs tests. Since we also want to migrate the database before tests are run, make sure you overwrite the test command in mix.exs:

  defp aliases do
    [
      test: ["ecto.create", "ecto.migrate", "test"]
    ]
  end

That should be there by default, if you used Phoenix to generate your project structure.

3. Start sandbox

The test_helper.ex file is what actually starts the tests. In here we also enable sandbox mode:

Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, :manual)

:manual here means that the database is only available in the test process. So if inside the test you start another process (with Task for example) then that will fail. This is the preferred way. Only enable :shared more if needed (example later).

4. Define pre-test action

You need to checkout a new database connection before each test. This ensures that the database is reduced to the starting state.

The simplest way is to include a setup function in the beginning of the test module.

setup do
  Ecto.Adapters.SQL.Sandbox.checkout(MyRepo)
end

Usually you have many test files though so rewriting this makes no sense. Instead you can define a function somewhere. Let's write a test/test_conn.exs file:

defmodule MyApp.TestConn do

  def checkout(_context \\ nil) do
    Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo)
  end
  
end

Now you can just import it in the beginning of any test module and that setup function will run in all the tests in that file:

defmodule Bashboard.UserTest do
  use ExUnit.Case
  use Plug.Test
  import MyApp.TestConn

  setup :checkout

  test "description" do
    # your test
  end
end

Troubleshooting

1. process ownership error

** (RuntimeError) cannot find ownership process for #PID<0.35.0>

This error tells you that you tried to use the DB connection outside of the intended test do process. For example by invoking a Task. This happens because of that :manual setting. The idea is to limit the scope of the sandbox so it's explicit.

To fix this tell the connection to be :shared instead:

defmodule MyApp.TestConn do

  def checkout(_context \\ nil) do
    Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo)
    Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, {:shared, self()})
  end
  
end

Now any process that needs the database can access the same one the test process is using. Make sure you always checkout a connection before sharing it though! Sharing a connection means you can't run in concurrent mode though (running multiple tests at the same time).

To keep tests running concurrently, you can use the more cumbersome, but specific allowances.

2. Database still not empty

You may have some data in the test database from previous runs. So either just empty it once with mix ecto.drop or add the same command to the alias list:

  defp aliases do
    [
      test: ["ecto.drop", "ecto.create", "ecto.migrate", "test"]
    ]
  end

Generally this should not be needed.