Ecto.Multi: Composing Atomic Database Operations in Elixir

The problem Ecto.Multi solves

When a single business action needs several database writes — create a user, then a profile, then an audit record — calling Repo.insert/2 three times leaves you with partial state if the second call fails. You could hand-roll Repo.transaction(fn -> ... end) with exceptions for rollback, but that scatters error handling and makes it hard to know which step failed. Ecto.Multi gives you a declarative pipeline: you name each step, chain them, and the whole thing commits atomically or rolls back with a structured error tuple telling you exactly where it stopped.

The useful answer up front: a Multi returns {:ok, results_map} where every named step's result is keyed by its name, or {:error, failed_step_name, failed_value, changes_so_far}. That error tuple is the main reason to reach for Multi instead of a raw transaction function.

A worked example: user plus profile

Assume Ecto 3.x with a Postgres adapter, a User schema with a unique email, and a Profile schema with a user_id foreign key. The key mechanism is Multi.run/3 (or Multi.insert/4 with a function), which receives the map of results from previous steps so later steps can use earlier IDs:

alias Ecto.Multi

multi =
  Multi.new()
  |> Multi.insert(:user, User.changeset(%User{}, %{email: "[contact removed]"}))
  |> Multi.run(:profile, fn repo, %{user: user} ->
    %Profile{}
    |> Profile.changeset(%{user_id: user.id, display_name: "Bob"})
    |> repo.insert()
  end)
  |> Multi.insert(:audit, Audit.changeset(%Audit{}, %{event: "signup"}))

case Repo.transaction(multi) do
  {:ok, %{user: user, profile: profile}} ->
    {:ok, user, profile}

  {:error, :profile, changeset, _changes_so_far} ->
    {:error, {:profile_failed, changeset}}

  {:error, step, value, _changes} ->
    {:error, {step, value}}
end

Run this in your application context (or in iex -S mix with the repo started). No special permissions beyond normal repo access are needed. Two things to notice:

• The :profile step pattern-matches %{user: user} out of the results map — this is how you thread the generated ID forward. The results map only contains steps that have already run.
• Multi.run functions must return {:ok, value} or {:error, value}. Returning a bare changeset or raising will not produce the structured error tuple you want.

How atomicity actually works

When you pass the Multi to Repo.transaction/1, Ecto wraps the entire sequence in a single database transaction: one BEGIN, all steps executed sequentially in order, then one COMMIT. If any step returns an error, Ecto issues ROLLBACK and stops — later steps never execute. You can confirm this by enabling query logging (config :my_app, MyApp.Repo, log: :debug or the :ecto_loggers console setting depending on version) and checking that a single BEGIN/COMMIT pair surrounds all statements.

Steps run sequentially, in the order added. There is no parallelism, and there are no savepoints — a failure aborts everything, you cannot resume mid-Multi.

Limits and common mistakes

• Referencing results before they exist. Using inserted.id outside a Multi.run function won't work — the insert hasn't happened at Multi-build time. Always access prior results inside the function callback.
• Ignoring the results map. If you don't pattern-match on {:ok, map}, you lose access to created records and may end up passing nil into later code.
• Wrapping a Multi in another transaction. Repo.transaction(fn -> Repo.transaction(multi) end) creates a redundant nested transaction (Ecto flattens it via savepoint-like behavior, but it's pointless and confusing). Pass the Multi directly.
• Long Multis hold a connection. The whole sequence runs on one pooled connection inside one transaction. A Multi with many steps or slow queries can hold the connection long enough to hit the repo timeout (default 15s for transactions in many configurations) or starve the pool under load. Keep Multis short; move slow external calls (HTTP, emails) outside — they can't be rolled back anyway.
• Side effects don't roll back. Anything non-database inside a Multi.run (sending an email, writing a file) is not undone by rollback. Use Multi.run only for repo operations, or trigger side effects after a successful commit.

Verifying the behavior

In a test with the SQL sandbox (Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, :manual) in an IEx session, or the standard sandbox setup in tests):

1. Run the example and assert both the user and profile rows exist.
2. Introduce a deliberate failure — e.g., make the profile changeset invalid or insert a duplicate email — and assert the return matches {:error, :profile, %Ecto.Changeset{}, _} and that Repo.aggregate(User, :count) shows no partial user row was committed.
3. Check the log output for exactly one BEGIN and one ROLLBACK (or COMMIT) per call.

That deliberate-failure test is the most valuable check: it proves atomicity for your actual schemas and constraints, not just the happy path.