Making Sense of Ecto 2 SQL.Sandbox and Connection Ownership Modes

The Ecto 2 introduces SQL.Sandbox, the special pool of database connections used only for testing.

The Goal

The context here is testing a usual [web] application working on top of a relational database. In this article we consider the tests that need to access the database.

Ideally, every test should:

  • Run in isolation from changes made by other tests. This is typically achieved by running the test in a database transaction.

The Ecto 2 approach

With Ecto 2.0 SQL.Sandbox you can choose 3 out of 4 properties described above. This is selected by the “ownership mode”.

Elixir process may own a database connection, hence ownership modes.

Ecto 2 ownership modes dictate how processes relate to database connections.

There are 3 modes: shared, manual and auto. You can select only one mode per Ecto.Repo. In a usual app you will have a single Ecto.Repo over a single database so this effectively means mode setting is global.


  • Test wrapped in a transaction: yes

The shared mode works exactly how things used to work in Ecto 1.x.

The shared mode seems to me like the most pragmatic choice. Yes, we give up running db-tests in parallel, which is a sad compromise on Erlang VM. But we do have a convenient and clean environment for running tests, including testing Elixir code that spawns multiple processes to access db.

Ability to test production code with concurrent db access is particularly important because you do want to take full advantage of concurrency to speed up your app.

To use the shared mode, firstly ensure you have no mode setting or connection checkout in your global test_helper. Then use the following setup for all db-based tests:

setup do
# Explicitly get a connection before each test
# By default the test is wrapped in a transaction
:ok = Ecto.Adapters.SQL.Sandbox.checkout(EctoExperiments.Repo)

# The :shared mode allows a process to share
# its connection with any other process automatically
Ecto.Adapters.SQL.Sandbox.mode(EctoExperiments.Repo, { :shared, self() })

Finally, why can’t the shared mode be parallelized? Fortunately, this is simply a limitation of current implementation of ExUnit and Ecto. There are no intrinsic limitations here. I’m confident we will eventually get there.


  • Test wrapped in a transaction: yes

At first glance the manual mode seems like a perfect choice. The story ends when your production code uses Task/async or any other mean to parallelize db access for quicker user feedback. The ease and joy of concurrency is why we use Elixir in the first place so we definitely do not want to avoid it just for the sake of passing tests.

In the manual mode the child processes do not have any db connection assigned and soon crash with an — admittedly — very informative error message.

To work around that, you can code the child process to explicitly use parent connection, if inside the test. The mechanism is called “allowances”. However, this means something like this creeps into your production code:

parent = self()
task = Task.async(fn ->
if Mix.env == :test do
Ecto.Adapters.SQL.Sandbox.allow(Repo, parent, self())
# ... child code ...

This, obviously, is very hard to justify.


  • Test wrapped in a transaction: no

Due to no isolation the auto mode isn’t practical for running tests. It’s designed for production usage.


Ecto 2 introduces a fine way to tackle integration testing against the database. If in doubt, use the shared mode.

In your setup code remember to checkout the connection first before setting the shared mode.

Also, please do not mix different modes in your test code.