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 using the repository will automatically check connections out as with any other pool. The only difference is that connections are not checked back in automatically but by explicitly calling checkin/2.

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. This means developers have a safe mechanism for running concurrent tests against the database.

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:

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

Since you don’t want those options in your production database, we typically recommend to create a config/test.exs and add the following to the bottom of your config/config.exs file:

import_config "config/#{Mix.env}.exs"

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 uses an explicit mechanism, their advantage is that you can still runs 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:

test "create two posts, one sync, another async" do
  Ecto.Adapters.SQL.Sandbox.mode(TestRepo, {:shared, self()})
  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.

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

• Using allowances - requires explicit allowances via allow/3. Tests may run concurrently.
• Using shared mode - does not require explicit allowances. Tests cannot run concurrently.