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 from automatic to either manual or shared. In the latter two 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 preferably 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 explicit checkouts
Ecto.Adapters.SQL.Sandbox.mode(Repo, :manual)

defmodule PostTest do
  # Once the mode 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(Repo)
  end

  test "create post" do
    # Use the repository as usual
    assert %Post{} = Repo.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(Repo)
end

test "calls worker that runs a query" do
  GenServer.call(MyApp.Worker, :run_query)
end

The test above will fail with an error similar to:

** (DBConnection.OwnershipError) 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 the worker attempts to perform a query, 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 "calls worker that runs a query" do
  allow = Process.whereis(MyApp.Worker)
  Ecto.Adapters.SQL.Sandbox.allow(Repo, self(), allow)
  GenServer.call(MyApp.Worker, :run_query)
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(Repo)
  # Setting the shared mode must be done only after checkout
  Ecto.Adapters.SQL.Sandbox.mode(Repo, {:shared, self()})
end

test "calls worker that runs a query" do
  GenServer.call(MyApp.Worker, :run_query)
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.

Also, beware that if the test process terminates while the worker is using the connection, the connection will be taken away from the worker, which will error. Therefore it is important to guarantee the work is done before the test concludes. In the example above, we are using a call, which is synchronous, avoiding the problem, but you may need to explicitly flush the worker or terminate it under such scenarios in your tests.

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.

FAQ

When running the sandbox mode concurrently, developers may run into issues we explore in the upcoming sections.

"owner exited"

In some situations, you may see error reports similar to the one below:

23:59:59.999 [error] Postgrex.Protocol (#PID<>) disconnected:
    ** (DBConnection.Error) owner #PID<> exited
Client #PID<> is still using a connection from owner

Such errors are usually followed by another error report from another process that failed while executing a database query.

To understand the failure, we need to answer the question: who are the owner and client processes? The owner process is the one that checks out the connection, which, in the majority of cases, is the test process, the one running your tests. In other words, the error happens because the test process has finished, either because the test succeeded or because it failed, while the client process was trying to get information from the database. Since the owner process, the one that owns the connection, no longer exists, Ecto will check the connection back in and notify the client process using the connection that the connection owner is no longer available.

This can happen in different situations. For example, imagine you query a GenServer in your test that is using a database connection:

test "gets results from GenServer" do
  {:ok, pid} = MyAppServer.start_link()
  Ecto.Adapters.SQL.Sandbox.allow(Repo, self(), pid)
  assert MyAppServer.get_my_data_fast(timeout: 1000) == [...]
end

In the test above, we spawn the server and allow it to perform database queries using the connection owned by the test process. Since we gave a timeout of 1 second, in case the database takes longer than one second to reply, the test process will fail, due to the timeout, making the "owner down" message to be printed because the server process is still waiting on a connection reply.

In some situations, such failures may be intermittent. Imagine that you allow a process that queries the database every half second:

test "queries periodically" do
  {:ok, pid} = PeriodicServer.start_link()
  Ecto.Adapters.SQL.Sandbox.allow(Repo, self(), pid)
  # more tests
end

Because the server is querying the database from time to time, there is a chance that, when the test exits, the periodic process may be querying the database, regardless of test success or failure.

"owner timed out because it owned the connection for longer than Nms"

In some situations, you may see error reports similar to the one below:

09:56:43.081 [error] Postgrex.Protocol (#PID<>) disconnected:
    ** (DBConnection.ConnectionError) owner #PID<> timed out
    because it owned the connection for longer than 120000ms

If you have a long running test (or you're debugging with IEx.pry), the timeout for the connection ownership may be too short. You can increase the timeout by setting the :ownership_timeout options for your repo config in config/config.exs (or preferably in config/test.exs):

config :my_app, MyApp.Repo,
  ownership_timeout: NEW_TIMEOUT_IN_MILLISECONDS

The :ownership_timeout option is part of DBConnection.Ownership and defaults to 120000ms. Timeouts are given as integers in milliseconds.

Alternately, if this is an issue for only a handful of long-running tests, you can pass an :ownership_timeout option when calling Ecto.Adapters.SQL.Sandbox.checkout/2 instead of setting a longer timeout globally in your config.

Deferred constraints

Some databases allow to defer constraint validation to the transaction commit time, instead of the particular statement execution time. This feature, for instance, allows for a cyclic foreign key referencing. Since the SQL Sandbox mode rolls back transactions, tests might report false positives because deferred constraints are never checked by the database. To manually force deferred constraints validation when using PostgreSQL use the following line right at the end of your test case:

Repo.query!("SET CONSTRAINTS ALL IMMEDIATE")

Database locks and deadlocks

Since the sandbox relies on concurrent transactional tests, there is a chance your tests may trigger deadlocks in your database. This is specially true with MySQL, where the solutions presented here are not enough to avoid deadlocks and therefore making the use of concurrent tests with MySQL prohibited.

However, even on databases like PostgreSQL, performance degradations or deadlocks may still occur. For example, imagine a "users" table with a unique index on the "email" column. Now consider multiple tests are trying to insert the same user email to the database. They will attempt to retrieve the same database lock, causing only one test to succeed and run while all other tests wait for the lock.

In other situations, two different tests may proceed in a way that each test retrieves locks desired by the other, leading to a situation that cannot be resolved, a deadlock. For instance:

Transaction 1:                Transaction 2:
begin
                              begin
update posts where id = 1
                              update posts where id = 2
                              update posts where id = 1
update posts where id = 2
                      **deadlock**

There are different ways to avoid such problems. One of them is to make sure your tests work on distinct data. Regardless of your choice between using fixtures or factories for test data, make sure you get a new set of data per test. This is specially important for data that is meant to be unique like user emails.

For example, instead of:

def insert_user do
  Repo.insert!(%User{email: "sample@example.com"})
end

prefer:

def insert_user do
  Repo.insert!(%User{email: "sample-#{counter()}@example.com"})
end

defp counter do
  System.unique_integer([:positive])
end

In fact, avoiding unique emails like above can also have a positive impact on the test suite performance, as it reduces contention and wait between concurrent tests. We have heard reports where using dynamic values for uniquely indexed columns, as we did for email above, made a test suite run between 2x to 3x faster.

Deadlocks may happen in other circumstances. If you believe you are hitting a scenario that has not been described here, please report an issue so we can improve our examples. As a last resort, you can always disable the test triggering the deadlock from running asynchronously by setting "async: false".