Cookbook: Concurrent, transactional tests with Phoenix.Ecto.SQL.Sandbox

TeslaSQLSandboxMiddleware

The following Tesla middleware allows to run asynchronous, transactional tests on an API endpoint for applications using Ecto's SQL.Sandbox.

This is useful if ...

• you are developing an application that uses Ecto for database access,
• and you have some kind of HTTP API that you'd like to use to drive an integration test suite (using Tesla as API client),
• you would like to be able to use asynchronous, transactional tests using Ecto's Ecto.Adapters.SQL.Sandbox in :manual mode

How this works

• Ecto's sandbox wraps your test logic in a database transaction and automatically rolls it back when the tests completes, so tests are isolated from each other and any changes made to the database are undone.
• :manual mode requires processes to explicitly "checkout" a sandboxed DB connection before executing a SQL statement. This is usually done in a setup block in your test case templates -- see for instance the usual DataCase template generated by Phoenix.
  • :manual mode has the advantage that it allows concurrent tests to each have their own isolated transaction
  • However, SQL statements executed in processes that did not checkout a sandboxed connection will receive an error. Hence, your test logic must not trigger code that does not run within the same process -- for instance it may not call an HTTP endpoint where each request is handled by a new throw-away process, e.g. an API or web endpoint.
  • To overcome this "limitation", one can make a separate process "join" a sandboxed connection. It can explicitly request shared ownership from Ecto's sandbox, given it knows the pid (and some other metadata) of the process that originally initiated the transaction (i.e., the test process).
• Sharing this information is often quite tricky, depending on the structure of the code involved. The Phoenix.Ecto.SQL.Sandbox plug from the phoenix_ecto package neatly solves this problem for Phoenix endpoints: It tries to extract the sandbox metadata from an HTTP header -- defaulting to the user-agent -- and requests shared ownership for the process handling the HTTP request. Any test process exercising the given endpoint can use the HTTP header to store its sandbox information and thus extend its sandbox to the request process.
  • Some libraries (e.g. wallaby) have built-in logic to store the sandbox metadata in the user-agent field.
  • The code below provides this functionality as a Tesla middleware.

How to use

1. Set up Phoenix.Ecto.SQL.Sandbox as described in its documentation.
2. Import the Tesla middleware below into your codebase -- e.g. somewhere in test/support.
3. Make your tests use a Tesla client configured to use the middleware. Please note that the code below is disabled if the :sql_sandbox config variable isn't set. This is useful if your "test client" is also used in production. If your Tesla client is exclusively used for tests, you can remove the conditional.

The middleware

defmodule MyApp.TeslaSQLSandboxMiddleware do
  @moduledoc false
  @behaviour Tesla.Middleware

  if Application.compile_env!(:my_app, :sql_sandbox) do
    @impl Tesla.Middleware
    def call(env, next, _opts) do
      user_agent = Tesla.get_header(env, "user-agent")

      encoded_metadata =
        MyApp.Repo
        |> Phoenix.Ecto.SQL.Sandbox.metadata_for(self())
        |> Phoenix.Ecto.SQL.Sandbox.encode_metadata()
      
      env
      |> Tesla.put_header("user-agent", "#{user_agent}/#{encoded_metadata}")
      |> Tesla.run(next)
    end
  else
    @impl Tesla.Middleware
    def call(env, next, _opts) do
      Tesla.run(env, next)
    end
  end
end