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

Questions / Help
, ,
1

Hi. I am using Ecto.Sandbox for all of my unit tests, so I’ve decided to create new ExUnit.CaseTemplate that will setup this sandbox for each test. Here is that file:

# Helper module for setting up tests that can run DB queries concurrently
# withourh interfearing with each other
# https://hexdocs.pm/ecto/testing-with-ecto.html
defmodule Guard.RepoCase do
  use ExUnit.CaseTemplate

  using do
    quote do
      import Guard.RepoCase
    end
  end

  setup tags do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(Guard.Repo)
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(Guard.FrontRepo)

    unless tags[:async] do
      Ecto.Adapters.SQL.Sandbox.mode(Guard.Repo, {:shared, self()})
      Ecto.Adapters.SQL.Sandbox.mode(Guard.FrontRepo, {:shared, self()})
    end

    :ok
  end
end

Now this is how one of my Test modules looks, where above mentioned RepoCase with Ecto.Sandbox is used:

defmodule Guard.Store.RbacRole.Test do
  use Guard.RepoCase, async: true

  @user_id "cb358a11-4185-4b5b-8829-5619805ac1fe"

  setup_all do
    Support.Factories.RbacUser.insert_user_into_db(@user_id)
    {:ok, %{}}
  end

Support.Factories.RbacUser.insert_user_into_db is literally just one Ecto.insert call that creates user with the given id, nothing special. This user needs to be in database for each test within this module, and that is why I decided to use setup_all instead of setup.
BUT, whenever I run tests like this, I get this error:

** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2623ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:

       1. Ensuring your database is available and that you can connect to it
       2. Tracking down slow queries and making sure they are running fast enough
       3. Increasing the pool_size (albeit it increases resource consumption)
       4. Allowing requests to wait longer by increasing :queue_target and :queue_interval

     See DBConnection.start_link/2 for more information

     stacktrace:
       (db_connection 2.4.0) lib/db_connection/ownership.ex:95: DBConnection.Ownership.ownership_checkout/2
       (ecto_sql 3.7.0) lib/ecto/adapters/sql/sandbox.ex:499: Ecto.Adapters.SQL.Sandbox.checkout/2
       (guard 0.1.0) test/support/concurrent_repo_case.ex:15: Guard.RepoCase.__ex_unit_setup_0/1
       (guard 0.1.0) test/support/concurrent_repo_case.ex:4: Guard.RepoCase.__ex_unit__/2
       test/guard/store/rbac_role_test.exs:1: Guard.Store.RbacRole.Test.__ex_unit__/2

The problem is (at least I think) that setup_all from my Test module is called before setup function where Sandbox is initialized, and that creates some sort of problem.

If I change setup_all within my Test module to setup, which looks like this:

  setup do
    Support.Factories.RbacUser.insert_user_into_db(@user_id)
    {:ok, %{}}
  end

everything works just fine.

Im not satisfied because now I have to insert this user before each test, which is ~15 db insert queries, instead of just one.

Does anyone have any idea if it is possible to run setup_all method just once, but to ‘delay’ execution of that method for after ecto.Sandbox has been already initialized. Thanks a lot :slight_smile:

2 Likes
2

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 :slight_smile: 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

4 Likes
3

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.

3 Likes
4

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.

1 Like
5

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

2 Likes
6

@LostKobrakai @trisolaran Thanks for help guys.

7

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.

1 Like
8

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.

9

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 :slight_smile:

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
2 Likes
10

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...
1 Like