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.
  • Leave a database in an initial, clean state. This is usually achieved by rolling back the transaction after the test.
  • Be able to run in parallel with other tests. This means no mutable, shared state between the tests.
  • Be able to spawn multiple child processes which also may access the database freely. The child processes should see the changes made by the parent process but not those made by other tests. This is necessary to test any Elixir code that takes advantage of concurrency to speed up database operations. You will have a lot of this code.

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.

:shared

  • Test wrapped in a transaction: yes
  • Test leaves db in a clean state: yes
  • Runs in parallel: no
  • Child processes share parent connection and transaction: yes
  • Connection check-out from pool: explicit, before each test
  • Connection check-in back to pool: auto, after each test

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() })
end

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.

:manual

  • Test wrapped in a transaction: yes
  • Leaves db in a clean state: yes
  • Runs in parallel: yes
  • Child processes share parent connection and transaction: no (but see details below)
  • Connection check-out from pool: explicit, before each test
  • Connection check-in back to pool: auto, after each test

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())
end
# ... child code ...
end)

This, obviously, is very hard to justify.

:auto

  • Test wrapped in a transaction: no
  • Leaves db in a clean state: no
  • Runs in parallel: yes
  • Child processes share parent connection and transaction: no
  • Connection check-out from pool: auto, before each usage (Repo call)
  • Connection check-in back to pool: auto, after each usage

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

Summary

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.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.