Grouping Database Operations with Ecto.Multi: Atomic Transactions in Elixir

The Problem: Partial Failures Leave Inconsistent State

Imagine a user registration flow that must create a User record, a Profile linked to that user, and an AuditLog entry capturing the event. If the profile insert fails after the user row is written, you end up with an orphaned user and no audit trail. Wrapping each call in a manual transaction works, but the boilerplate grows fast and error handling becomes scattered.

Ecto.Multi solves this by letting you declare a sequence of Repo operations—inserts, updates, deletes, or custom functions—and execute them as a single atomic transaction. On success you get {:ok, changes}; on failure you receive {:error, failed_operation, changes}, making rollback behavior explicit and pattern-matchable.

How Ecto.Multi Structures a Transaction

An Ecto.Multi struct is built by chaining functions like insert/3, update/3, delete/3, and run/3. Each step receives a name (an atom) and the operation. Steps run in insertion order, and run/3 lets later steps access results from earlier ones via the changes map passed to its function.

Key properties:

• All operations share the same database connection and transaction.
• If any step returns an error tuple, the whole transaction rolls back.
• The changes map in the result contains only the operations that succeeded before the failure.

Because Multi is part of Ecto core (stable across 3.x), no extra dependencies or database-specific extensions are required.

Worked Example: User Registration with Profile and Audit Log

Assume a Phoenix context module Accounts with schemas User, Profile, and AuditLog. The following function builds and executes a Multi:

def register_user(attrs) do
  multi =
    Ecto.Multi.new()
    |> Ecto.Multi.insert(:user, User.changeset(%User{}, attrs))
    |> Ecto.Multi.insert(:profile, fn %{user: user} ->
      Profile.changeset(%Profile{}, user_id: user.id, %{})
    end)
    |> Ecto.Multi.run(:audit, fn _repo, %{user: user} ->
      {:ok, AuditLog.log_user_created(user.id)}
    end)

  Repo.transaction(multi)
end

Run this in iex -S mix inside your project (requires mix.exs with :ecto_sql and a configured Repo). The insert/3 for :profile uses a function that receives the changes map, so it can pull the newly created user.id. The run/3 step for :audit also receives that map and can call any function that returns {:ok, result} or {:error, reason}.

On success, the tuple looks like:

{:ok, %{user: %User{...}, profile: %Profile{...}, audit: %AuditLog{...}}}

If the profile changeset is invalid, you get:

{:error, :profile, %{user: %User{...}, profile: {:error, changeset}}}

The user row never commits because the transaction rolls back automatically.

Trade-offs: What Multi Doesn't Solve

No external side effects. Do not send emails, call third-party APIs, or write to message queues inside a Multi. A rollback will not undo those actions. Move them outside the transaction or use a saga pattern with compensating jobs.

No automatic retry. Deadlocks or serialization failures (common under high concurrency) surface as {:error, ...} tuples. Wrap the Repo.transaction/1 call in a retry helper if your workload demands it.

Error shapes can shift across major versions. Ecto 3.x to 4.x upgrades may change the exact tuple structure or timing. Read the changelog and run your test suite before deploying.

Debugging unnamed operations. Using anonymous functions without named steps produces vague errors like :erlang in the failed operation field. Always name each step (:user, :profile, :audit) for clear logs.

Verify It Works: Test with Sandbox

Ecto.Adapters.SQL.Sandbox lets tests wrap Multi calls in a transaction that rolls back automatically. In a test file:

test "registration rolls back on profile failure" do
  {:ok, _} = Sandbox.checkout(Repo)
  attrs = %{email: "bad@example", invalid_field: "oops"}

  assert {:error, :profile, _} = Accounts.register_user(attrs)
  assert Repo.all(User) == []
end

Run with mix test. The assertion Repo.all(User) == [] confirms the first insert did not persist. This is the practical way to check that your Multi behaves atomically.

Next Steps

Add a Multi to the next feature that touches two or more tables. Name every step, keep side effects out, and write a Sandbox test that forces a mid-transaction failure. You'll get explicit rollback semantics without hand-rolled transaction boilerplate.