Running tests outside the Ecto Sandbox

I came across a curious problem and am looking for help.

So first things first, …
The setup
We’re running an Application relying on Postgres using Ecto and use the Sandbox supplied by Ecto to setup DB interaction for tests. This works finde for all the ~5000 tests.
Until now.

The issue
We came across a curious situation of two DB entries existing simultaneously, all while this should not have been possible. Should comes down to a missing db constraint and the eventual db connection taking about a minute to commit.
So roughly two hours of my life later, reproducing the error and after deactivating the sandbox for tests, the issue is fixed and as a responsible person I want to build a test for it.

We arrive at …
The problem
Since this bug requires two concurrent transactions:

A: (begin)---------------------------(commit)
B:            (begin)----------(commit)

I cannot use the sandbox mode for it but need to run this single test using a “real” Database.
Has anyone made any experience how to setup tests like this?

1 Like

You can set the sandbox mode to manual for those tests. You need to take care of cleanup on your own though and you might not be able to run them concurrently, e.g. if you actually need to persist changes.

1 Like

Good idea but sadly this does not work. Since both transaction have to run concurrently both require their own db connections.
Using the sandbox this is impossible since

  1. Using the same connection from the sandbox pool will fail when the second transaction tries to check it out
  2. Using separate connections will not work since both transaction will run in an isolated transaction started when checking the connection out from the sandbox. So the underlying issue won’t be triggered since the changes from A will never be permitted in the database for B to trigger the constraint on commit.

By setting the mode to manual you’re allowing db connections to be checked out at will. By not using the default setup call that comes with using MyAppWeb.DataCase on phoenix you disable the automatic checkout of a sandbox connection.

Having done that you should be able to execute ecto code in the test like anywhere else. No isolation, no wrapped transaction.


Ok after reading the manual it’s possible to pass sandbox: false to Ecto.Adapters.SQL.Sandbox.checkout/2 this way the connection is no longer wrapped in a transaction.
I’ll try that out. Thanks!

So just to get back on this the solution looks like this.

The conn and databases uses passed @tag values for the Ecto.Adapters.SQL.Sandbox.checkout/2 call and during the setup we register a clean up for the db.

if tags[:sandbox] == false do
  on_exit(fn -> 
    # ... remove things from the db

checkout_opts = tags |> Map.take(~w(sandbox isolation ownership_timeout)a) |>

Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo, checkout_opts)

So the final test can look like this.

@tag sandbox: false
test "concurrent transactions trigger the deferred constraint" do
  attrs = %{something: "complete different"}
  task =
    Task.async(fn ->
      Ecto.Adapters.SQL.Sandbox.checkout(Repo, sandbox: false)

      try do
        Repo.transaction(fn ->

        e -> e

    assert {:ok, _} =
    assert %{postgres: %{code: :unique_violation}} = Task.await(task)

Again thanks for the help @LostKobrakai !