A pool for concurrent transactional tests.

The sandbox pool is implemented on top of an ownership mechanism. When started, the pool is in automatic mode, which means the repository will automatically check connections out as with any other pool.

The mode/2 function can be used to change the pool mode to manual or shared. In both modes, the connection must be explicitly checked out before use. When explicit checkouts are made, the sandbox will wrap the connection in a transaction by default and control who has access to it. This means developers have a safe mechanism for running concurrent tests against the database.

Database support

While both PostgreSQL and MySQL support SQL Sandbox, only PostgreSQL supports concurrent tests while running the SQL Sandbox. Therefore, do not run concurrent tests with MySQL as you may run into deadlocks due to its transaction implementation.

Example

The first step is to configure your database to use the Ecto.Adapters.SQL.Sandbox pool. You set those options in your config/config.exs (or preferrably config/test.exs) if you haven’t yet:

config :my_app, Repo,
  pool: Ecto.Adapters.SQL.Sandbox

Now with the test database properly configured, you can write transactional tests:

# At the end of your test_helper.exs
# Set the pool mode to manual for explicitly checkouts
Ecto.Adapters.SQL.Sandbox.mode(TestRepo, :manual)

defmodule PostTest do
  # Once the model is manual, tests can also be async
  use ExUnit.Case, async: true

  setup do
    # Explicitly get a connection before each test
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(TestRepo)
  end

  test "create post" do
    # Use the repository as usual
    assert %Post{} = TestRepo.insert!(%Post{})
  end
end

Collaborating processes

The example above is straight-forward because we have only a single process using the database connection. However, sometimes a test may need to interact with multiple processes, all using the same connection so they all belong to the same transaction.

Before we discuss solutions, let’s see what happens if we try to use a connection from a new process without explicitly checking it out first:

setup do
  # Explicitly get a connection before each test
  :ok = Ecto.Adapters.SQL.Sandbox.checkout(TestRepo)
end

test "create two posts, one sync, another async" do
  task = Task.async(fn ->
    TestRepo.insert!(%Post{title: "async"})
  end)
  assert %Post{} = TestRepo.insert!(%Post{title: "sync"})
  assert %Post{} = Task.await(task)
end

The test above will fail with an error similar to:

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

That’s because the setup block is checking out the connection only for the test process. Once we spawn a Task, there is no connection assigned to it and it will fail.

The sandbox module provides two ways of doing so, via allowances or by running in shared mode.

Allowances

The idea behind allowances is that you can explicitly tell a process which checked out connection it should use, allowing multiple processes to collaborate over the same connection. Let’s give it a try:

test "create two posts, one sync, another async" do
  parent = self()
  task = Task.async(fn ->
    Ecto.Adapters.SQL.Sandbox.allow(TestRepo, parent, self())
    TestRepo.insert!(%Post{title: "async"})
  end)
  assert %Post{} = TestRepo.insert!(%Post{title: "sync"})
  assert %Post{} = Task.await(task)
end

And that’s it, by calling allow/3, we are explicitly assigning the parent’s connection (i.e. the test process’ connection) to the task.

Because allowances use an explicit mechanism, their advantage is that you can still run your tests in async mode. The downside is that you need to explicitly control and allow every single process. This is not always possible. In such cases, you will want to use shared mode.

Shared mode

Shared mode allows a process to share its connection with any other process automatically, without relying on explicit allowances. Let’s change the example above to use shared mode:

setup do
  # Explicitly get a connection before each test
  :ok = Ecto.Adapters.SQL.Sandbox.checkout(TestRepo)
  # Setting the shared mode must be done only after checkout
  Ecto.Adapters.SQL.Sandbox.mode(TestRepo, {:shared, self()})
end

test "create two posts, one sync, another async" do
  task = Task.async(fn ->
    TestRepo.insert!(%Post{title: "async"})
  end)
  assert %Post{} = TestRepo.insert!(%Post{title: "sync"})
  assert %Post{} = Task.await(task)
end

By calling mode({:shared, self()}), any process that needs to talk to the database will now use the same connection as the one checked out by the test process during the setup block.

Make sure to always check a connection out before setting the mode to {:shared, self()}.

The advantage of shared mode is that by calling a single function, you will ensure all upcoming processes and operations will use that shared connection, without a need to explicitly allow them. The downside is that tests can no longer run concurrently in shared mode.

Summing up

There are two mechanisms for explicit ownerships:

• Using allowances - requires explicit allowances via allow/3. Tests may run concurrently.

• Using shared mode - does not require explicit allowances. Tests cannot run concurrently.