Ecto.Sandbox does not work with tests that have setup_all function

Right, the idea of using the sandbox for tests is that each test has to checkout a connection at the beginning of the test, the connection is wrapped in a transaction which is then rolled back at the end of the test. So each test can work with its own data without interfering with other tests (with some caveats). The connection is checked out in the setup callback, which is called once for each test in the case. The setup_all callback is called once for the entire case, and it’s called before any setup call and therefore before any connection is checked out.

The sandbox connection is checked out once for every test, so “delaying” the execution of your setup_all logic until then would mean running it once for each test it would be just like adding that logic to the setup call.

I personally wouldn’t worry about it. Your test case is async so will run in parallel with other tests. The benefits of having isolated tests, each with their own test data are IMO greater then the performance gains of inserting the test data once for all tests. At least in the general case.

However, if you think that you’d benefit from having tests share some data, you could seed the test database with the appropriate data before running the test suite (of course, tests shouldn’t modify that data, otherwise they may fail in funny ways). See this thread: Fixture on tests load once and keep for all the tests

setup_all also runs in a different process than the test itself, while setup runs within the test’s process. Connections and any transactions opened cannot be shared between multiple processes, so setup_all can’t be used with the ecto sandbox no matter the timing/order of execution.

They can, that’s why we have allowances and the sanbox shared mode.

But yes, the fact that setup_all runs in a separate process before all tests is another reason why the idea of postponing its execution doesn’t make much sense.

That kinda blew my mind shortly, because you usually don’t have an API to do so, but true the sandbox actually does that.

@LostKobrakai @trisolaran Thanks for help guys.

One situation when you do want this is if RbacUser has a unique index on that user ID - inserting that record in a setup block (inside a transaction) will block any other test that also inserts that record in a setup block, causing mayhem with async: true tests.

I’m not sure setup_all blocks run early enough for that, though. I’ve used code in test_helper.ex to insert records before the sandbox is started:

# generated by Phoenix
ExUnit.start()

# add insert statements here

# generated by Phoenix
Ecto.Adapters.SQL.Sandbox.mode(YourAppNameHere.Repo, :manual)

The other side-effect of this approach is that the test database has records in it at the end, since those initial inserts aren’t inside a transaction. You can either live with those (and change the inserts to be find-or-inserts) or reset the DB on every test run.

You raised a good point with unique indexes, and that is the case here. Tests work fine, but I assume that parallelization thats enabled with Ecto.Sandbox and tests running in separate processes is reduced here since those separate processes cant write to DB simultaneously.

Correct. If you have 2 parallel tests A and B that both insert the same value in a column with unique index as part of their setup, and A runs first, then B will have to wait until A completes. Because each transaction is rolled back after the test ends, there will be no conflicts (unless you create a deadlock) and the tests will succeed, but they won’t really run in parallel. I was following this naive approach for quite some time until someone made me realize that it wasn’t a good idea

One way to solve this is to have A and B insert distinct, dynamic values and not hard-coded ones. For example this is what I typically do:

setup do 
 # Fixture.create_user/0 creates a new user with a randomly generated ID, or a new ID created in the DB
 user = Fixture.create_user()

 %{user: user}
end

test "can get user by id", %{user: user} do 
  assert User.get(user.id) == user
end

I have run into the same problem today. Here’s how I solved it.

in data_case.exs

defmodule MyModule.DataCase do
  @moduledoc """
  Sets up helpers for CRUD tests.

  In test file do:

  # this will set up a shared sandbox connection for all tests
  use MyModule.DataCase, async: true, shared_conn: true

  # each test will check it out before running
  setup [:checkout_repo_conn]

  # make specific test setup for all tests to share.
  # we create a fresh job,
  # which will persist for all tests in the suite and
  # after they finis, the transaction will be rolled back.
  setup_all do
    {:ok, job} = Job.create(Factory.params_for(:job))
    %{job: job}
  end

  # provide each test with the fresh job to use.
  test "blabbla", %{job: job} do...
  """
  use ExUnit.CaseTemplate

  using options do
    quote do
      alias Ecto.Changeset
      import MyModule.DataCase
      alias MyModule.{Factory, Repo}

      defp checkout_repo_conn(_context) do
        :ok = Ecto.Adapters.SQL.Sandbox.checkout(Repo)
      end

      setup_all do
        if unquote(options)[:shared_conn] == true do
          Ecto.Adapters.SQL.Sandbox.mode(MyModule.Repo, {:shared, self()})
          :ok
        else
          :ok = Ecto.Adapters.SQL.Sandbox.mode(MyModule.Repo, :manual)
        end
      end
    end
  end
end

in test.exs

defmodule MyModule.Test do
  use MyModule.DataCase, 
          async: true, 
          shared_conn: true    # make it shared

  alias MyModule.{Repo, JobDetail, Job}

  # each test will checkout a sandbox connection, which will be shared
  setup [:checkout_repo_conn]

  # here I set up shared context to run once
  setup_all do
    {:ok, job} = Job.create(Factory.params_for(:job))
    %{job: job}
  end

 # and provide the job to each test to use its job.id
 test "blabla", %{job: job} do...