In Part 1, you learned ExUnit fundamentals: describe blocks, setup callbacks, and assertions. Now comes the real challenge: testing code that touches a database. This is Part 2 of our 7-part series on Elixir testing patterns, where we tackle database test isolation with Ecto.Sandbox.

Database tests can be notoriously flaky. One test creates a user with email “[email protected]”, another tries to create the same user and fails due to a uniqueness constraint. Tests pass when run individually but fail when run together. Sound familiar? Ecto.Sandbox eliminates these problems entirely.

This post corresponds to PR #3: Database Testing with Ecto.Sandbox in ex-test. You’ll see exactly how to set up database isolation and run tests in parallel without any conflicts.

The Problem: Database State in Tests

Let’s start with why database testing is hard.

Why Tests Can’t Share Database State

Imagine two tests running simultaneously:

# Test 1
test "creates todo with title 'Buy milk'" do
Todos.create_todo(%{title: "Buy milk"})
assert length(Todos.list_todos()) == 1
end

# Test 2
test "creates todo with title 'Walk dog'" do
Todos.create_todo(%{title: "Walk dog"})
assert length(Todos.list_todos()) == 1  # FAILS! Sees Test 1's data
end

If both tests share the same database, Test 2 sees the todo from Test 1. The assertion fails because list_todos/0 returns two todos, not one. You could fix this by running tests sequentially, but then your test suite crawls. With hundreds of tests, you’re waiting minutes instead of seconds.

The traditional solution is database cleanup between tests - truncate tables, run migrations down and up, or manually delete records. These approaches are slow, error-prone, and still don’t solve the parallelization problem.

Elixir takes a different approach: transaction-based isolation.

How Ecto.Sandbox Works

Ecto.Sandbox wraps each test in a database transaction that automatically rolls back when the test completes. Your test creates data, runs assertions, and when it finishes, everything disappears. The next test starts with a clean database.

Transaction-Based Isolation

Here’s what happens behind the scenes:

Test starts
↓
BEGIN TRANSACTION
↓
Your test code runs
- Insert records
- Update data
- Run assertions
↓
ROLLBACK TRANSACTION
↓
Test ends (database unchanged)

Every test runs in its own isolated transaction. Data created in one test never reaches the actual database - it exists only within that transaction’s scope. When the transaction rolls back, all changes vanish.

This is blazingly fast. Transactions are cheap database operations, far faster than truncating tables or running migrations. And because each test is isolated, they can run in parallel without interfering with each other.

The Magic of Automatic Rollback

The key insight: you never commit test transactions. Ecto.Sandbox starts a transaction, hands the database connection to your test, and when the test finishes, rolls back everything. No cleanup code needed. No manual teardown. The database returns to its initial state automatically.

This works because database transactions provide ACID guarantees. Changes are isolated from other connections until committed. Since test transactions never commit, changes never become visible to other tests.

The DataCase Template

Phoenix generates a DataCase module in test/support/data_case.ex that encapsulates database test patterns. Let’s examine it piece by piece from ex-test.

Anatomy of DataCase

defmodule ExTest.DataCase do
use ExUnit.CaseTemplate

using do
quote do
alias ExTest.Repo
import Ecto
import Ecto.Changeset
import Ecto.Query
import ExTest.DataCase
import ExTest.Factory
end
end

setup tags do
ExTest.DataCase.setup_sandbox(tags)
:ok
end

def setup_sandbox(tags) do
pid = Ecto.Adapters.SQL.Sandbox.start_owner!(ExTest.Repo, shared: not tags[:async])
on_exit(fn -> Ecto.Adapters.SQL.Sandbox.stop_owner(pid) end)
end

def errors_on(changeset) do
Ecto.Changeset.traverse_errors(changeset, fn {message, opts} ->
Regex.replace(~r"%{(\w+)}", message, fn _, key ->
opts |> Keyword.get(String.to_existing_atom(key), key) |> to_string()
end)
end)
end
end

Let’s break this down:

CaseTemplate Pattern: use ExUnit.CaseTemplate tells ExUnit this module is a template for other test modules. When you use ExTest.DataCase in a test, you get all the imports and setup logic defined here.

The using Block: Everything in quote do ... end gets injected into test modules that use DataCase. This imports database helpers (Ecto, Ecto.Query), the Repo, and factory functions. Now your tests can query the database without explicit imports.

Setup Callback: The setup tags do ... end runs before each test. It calls setup_sandbox/1, passing test tags. This is where Ecto.Sandbox gets configured.

The setup_sandbox Function

This is the heart of database isolation:

def setup_sandbox(tags) do
pid = Ecto.Adapters.SQL.Sandbox.start_owner!(ExTest.Repo, shared: not tags[:async])
on_exit(fn -> Ecto.Adapters.SQL.Sandbox.stop_owner(pid) end)
end

start_owner!: Starts a database connection ownership process. This process owns the transaction for the current test. The shared: not tags[:async] option is crucial - we’ll explain it in detail shortly.

on_exit: Registers a callback to stop the owner process when the test completes. This is when the transaction rolls back, cleaning up all data.

The pattern is elegant: setup code prepares the sandbox, the test runs with its isolated database state, and cleanup happens automatically via on_exit.

The errors_on Helper

def errors_on(changeset) do
Ecto.Changeset.traverse_errors(changeset, fn {message, opts} ->
Regex.replace(~r"%{(\w+)}", message, fn _, key ->
opts |> Keyword.get(String.to_existing_atom(key), key) |> to_string()
end)
end)
end

This helper converts changeset errors into a simple map for testing. Instead of complex nested structures, you get:

changeset = Todo.changeset(%Todo{}, %{title: nil})
assert %{title: ["can't be blank"]} = errors_on(changeset)

Clean, readable, and perfect for pattern matching in assertions.

Async vs Sync Tests

Now we tackle one of Ecto.Sandbox’s most powerful features: the choice between parallel and sequential execution.

When to Use async: true

Async tests run in parallel with other async tests. They’re fast, efficient, and the default choice for most database tests:

defmodule ExTest.Todos.AsyncTest do
use ExTest.DataCase, async: true

alias ExTest.Todos

describe "async database tests" do
test "test 1 - creates data in isolated transaction" do
{:ok, todo} = Todos.create_todo(%{title: "Async Test 1"})
assert todo.title == "Async Test 1"
assert length(Todos.list_todos()) == 1
end

test "test 2 - doesn't see test 1's data" do
assert Todos.list_todos() == []
end

test "test 3 - also isolated from other tests" do
Enum.each(1..5, fn i ->
Todos.create_todo(%{title: "Todo #{i}"})
end)
assert length(Todos.list_todos()) == 5
end
end
end

Each test gets its own transaction. Test 2 doesn’t see Test 1’s todo because they’re in separate transactions. Test 3 creates five todos, but they vanish when the test completes.

Run these tests and watch them execute simultaneously:

mix test test/ex_test/todos_async_test.exs

You’ll see output like:

...

Finished in 0.1 seconds (0.1s async, 0.00s sync)
3 tests, 0 failures

Notice the timing: 0.1 seconds for three tests. If they ran sequentially with database cleanup, it would take much longer.

When to Use async: false

Sync tests run sequentially, one after another. Use them when tests require exclusive database access:

defmodule ExTest.Todos.SyncTest do
use ExTest.DataCase, async: false

alias ExTest.Todos

@moduletag :sync_required

describe "sync database tests" do
test "runs sequentially - safe for locks" do
{:ok, todo} = Todos.create_todo(%{title: "Sync Test"})
assert todo.title == "Sync Test"
end

test "runs after previous test completes" do
assert Todos.list_todos() == []
end
end

describe "when to use sync vs async" do
test "use sync for database transactions that might deadlock" do
assert {:ok, _} = Todos.create_todo(%{title: "Transaction safe"})
end

test "use sync for tests that modify shared application state" do
assert is_list(Todos.list_todos())
end
end
end

When to choose sync:

Database Locks: Tests that acquire table locks or advisory locks need sequential execution to prevent deadlocks.

GenServer State: If your context uses a GenServer to cache database state, parallel tests can create race conditions. Sync tests ensure one test completes before the next starts.

Schema Migrations: Tests that modify database schema (add columns, create tables) must run sequentially.

Shared Resources: External services, file system operations, or anything outside the database that can’t be parallelized.

For the vast majority of tests - CRUD operations, validations, business logic - use async: true.

The shared Option Explained

Remember this line from setup_sandbox?

pid = Ecto.Adapters.SQL.Sandbox.start_owner!(ExTest.Repo, shared: not tags[:async])

The shared: not tags[:async] pattern controls connection ownership:

Async tests (shared: false): The test process exclusively owns the database connection. No other process can use it. This enables parallel execution - each async test has its own connection and transaction.

Sync tests (shared: true): The database connection can be shared across processes. This is necessary when your code spawns new processes (like GenServers or Tasks) that need database access.

Here’s the logic:

• If tags[:async] is true, then shared: not true → shared: false (exclusive)
• If tags[:async] is false, then shared: not false → shared: true (shared)

It’s a clever pattern: async tests automatically get exclusive connections, sync tests get shared ones.

Practical Examples

Let’s see Ecto.Sandbox in action with examples from ex-test.

Async Tests in Action

This example demonstrates transaction isolation beautifully:

describe "demonstrating transaction rollback" do
test "data created here won't persist" do
{:ok, _todo} = Todos.create_todo(%{title: "Will be rolled back"})
assert length(Todos.list_todos()) == 1
end

test "confirms previous test's data was rolled back" do
assert Todos.list_todos() == []
end
end

The first test creates a todo and sees it in the database. When the test completes, the transaction rolls back. The second test starts with a clean slate - no todos exist.

Here’s a more complex example with multiple operations:

test "complex operations are fully isolated" do
# Create multiple records
{:ok, todo1} = Todos.create_todo(%{title: "First"})
{:ok, todo2} = Todos.create_todo(%{title: "Second"})
{:ok, todo3} = Todos.create_todo(%{title: "Third"})

# Update one
Todos.update_todo(todo2, %{completed: true})

# Delete one
Todos.delete_todo(todo3)

# Assertions
remaining = Todos.list_todos()
assert length(remaining) == 2
assert Enum.find(remaining, &(&1.id == todo2.id)).completed == true

# All of this rolls back - next test sees nothing
end

Create, update, delete - all isolated to this test’s transaction.

Sync Tests for Special Cases

When tests interact with processes, use sync mode:

defmodule ExTest.Notifications.CacheTest do
use ExTest.DataCase, async: false

setup do
# Start a GenServer that caches todos
{:ok, cache} = TodoCache.start_link()
{:ok, cache: cache}
end

test "cache sees database changes", %{cache: cache} do
{:ok, todo} = Todos.create_todo(%{title: "Cached"})

# GenServer process needs database access
TodoCache.refresh(cache)

assert TodoCache.get(cache, todo.id) == todo
end
end

The shared: true connection mode allows the GenServer process to access the same transaction as your test. Without it, you’d get connection ownership errors.

Remember the @moduletag :sync_required from earlier? Use it to selectively run tests:

mix test --only sync_required

Or exclude them:

mix test --exclude sync_required

Combine filters:

mix test --only database --exclude slow

Common tagging patterns for database tests:

@moduletag :database        # All database tests
@moduletag :integration     # Tests hitting external services
@tag :slow                  # Long-running tests
@tag :migration_required    # Needs specific schema state

Common Pitfalls

Even with Ecto.Sandbox, you can run into issues. Here’s what to watch for.

Connection Ownership Errors

If you see this error:

** (DBConnection.OwnershipError) cannot find ownership process for #PID<0.123.0>

Your test spawned a process that tried to access the database, but you’re using async: true with exclusive connection ownership. Solutions:

Option 1: Switch to async: false with shared connections:

use ExTest.DataCase, async: false

Option 2: Explicitly allow the spawned process to use the connection:

test "spawned process can access database" do
parent = self()

task = Task.async(fn ->
Ecto.Adapters.SQL.Sandbox.allow(ExTest.Repo, parent, self())
Todos.list_todos()
end)

Task.await(task)
end

The allow/3 function transfers connection ownership from the test process to the spawned process.

Process-Based Tests

Tests involving processes (GenServers, Tasks, Agents) need careful handling. You have two options:

Option 1: Use async: false with shared connections (simpler):

use ExTest.DataCase, async: false

test "GenServer accesses database" do
{:ok, pid} = MyServer.start_link()
MyServer.do_database_work(pid)  # Works with shared connection
end

Option 2: Use async: true with explicit connection sharing (more performant):

use ExTest.DataCase, async: true

test "GenServer accesses database" do
parent = self()
{:ok, pid} = MyServer.start_link()

# Allow GenServer to use parent's connection
Ecto.Adapters.SQL.Sandbox.allow(ExTest.Repo, parent, pid)

MyServer.do_database_work(pid)  # Works!
end

Both approaches work. Use async: false for simplicity, or async: true with allow/3 when you need parallel execution.

Summary

You’ve mastered database testing with Ecto.Sandbox:

• Transaction-based isolation: Each test runs in its own transaction that automatically rolls back
• DataCase template: Encapsulates setup, helpers, and imports for database tests
• Async vs sync: Use async: true for parallel execution, async: false for process-based tests
• Connection ownership: The shared option controls whether connections can cross process boundaries
• Automatic rollback: No cleanup code needed - Ecto.Sandbox handles it

ex-test demonstrates these patterns across 136 passing tests. All async tests run in parallel, all sync tests maintain proper isolation, and everything rolls back cleanly.

In Part 3, we’ll level up test data creation with factory patterns. Instead of manually building attributes in every test, you’ll use ExMachina and Faker to generate realistic test data with minimal code.

All code examples are available in the ex-test.

---

Series Navigation

Previous: Part 1 - ExUnit Fundamentals

Next: Part 3 - Test Data with ExMachina

All Parts

1. ExUnit Fundamentals
2. Database Isolation with Ecto.Sandbox (You are here)
3. Test Data with ExMachina
4. Mocking with Mox
5. Adapter Pattern and Stubs
6. Centralized Test Helpers
7. Phoenix Controller Testing

---

Resources

• Ecto.Adapters.SQL.Sandbox Documentation
• Testing Phoenix Applications (Official Guide)
• ExUnit.Case Documentation
• ex-test