Build Atomic Transactions with Ecto.Multi – A Practical Guide

Why Ecto.Multi Matters

When you need to perform several database operations that must either all succeed or all fail together, Ecto.Multi is your go‑to tool. It lets you compose insert, update, delete, and custom logic into a single transaction, avoiding nested callbacks and providing a clear view of what succeeded before a failure.

Building a Multi Pipeline

Start with an empty Ecto.Multi and add steps. Each step receives the results of all previous steps, so you can chain dependent operations.

# lib/my_app/accounts.ex
alias MyApp.{Repo, User, Profile}

def create_user_with_profile(params, old_token) do
  multi =
    Ecto.Multi.new()
    |> Ecto.Multi.insert(:user, User.changeset(%User{}, params))
    |> Ecto.Multi.run(:profile, fn %{user: user} ->
      # Create a profile for the newly inserted user
      Profile.create_for_user(user)
    end)
    |> Ecto.Multi.delete(:old_token, old_token)

  Repo.transaction(multi)
end

Explanation of each part:

• Ecto.Multi.new/0 creates an empty accumulator.
• Ecto.Multi.insert/3 queues an INSERT and returns the saved struct as user to later steps.
• Ecto.Multi.run/3 runs a custom function. The function receives a map of all previous results (here %{user: user}) and must return either {:ok, result} or {:error, reason}.
• Ecto.Multi.delete/3 removes the old_token record.

Executing and Inspecting Results

Call Repo.transaction/1 to execute the pipeline. The return shape is:

Success	Result
{:ok, results_map}	All steps committed. results_map contains a key for each step name (e.g., :user, :profile, :old_token) mapping to the step’s return value.
{:error, failed_step, failed_result, all_results_so_far}	Transaction rolled back. failed_step is the atom name of the step that caused the failure. failed_result is whatever the step returned (often an error tuple). all_results_so_far lets you see what succeeded before the failure.

Example of a failure path:

{:error, :profile, {:error, :invalid_email}, %{user: #User<…>}}

In the example above, the profile creation failed; the user insert still appears in all_results_so_far, but the transaction rolled back and the user record was not persisted.

Common Pitfalls and How to Avoid Them

• Running DB writes inside run/3: If you call Repo.insert! inside a run step, the write executes immediately outside the transaction. Instead, return a changeset or struct, or use Ecto.Multi.insert directly.
• Assuming step results are committed rows: The value returned by a prior insert is the struct with an id but is not yet committed until the transaction completes. Do not query the database for that row inside a later run step expecting it to exist—use the struct passed in the map instead.
• Long‑running side effects inside run/3: Because the entire Repo.transaction holds a database lock, performing slow HTTP requests or file I/O inside run can increase lock contention. Keep side effects short or move them out of the transaction and use a saga pattern if you need eventual consistency.
• Conditional logic inside run/3: If you need to decide whether to add a step based on earlier data, build the Ecto.Multi dynamically before calling Repo.transaction rather than using a no‑op inside run.
• Nested Multi.merge/2 misinterpretation: merge does not create a sub‑transaction; all steps are flattened into a single transaction. A failure in any merged step rolls back the entire parent.

When Multi Isn’t Enough

Because Ecto.Multi is all‑or‑nothing, it cannot express partial commits or savepoints. If you need to commit some changes while allowing others to fail independently, you’ll need to run separate transactions or implement a saga/compensation workflow. Also, if you must perform operations that cannot be rolled back (e.g., sending an email after a database write), run those side effects after the transaction succeeds.

Practical Check‑In

After executing a transaction, verify its outcome by inspecting the return tuple. In tests, use Ecto.Adapters.SQL.Sandbox to ensure no rows remain after a failure:

{:error, _, _, _} = MyApp.Accounts.create_user_with_profile(%{email: "bad"}, token)
assert Repo.aggregate(User, :count, :id) == 0

Running this in a sandbox guarantees that the transaction was rolled back and the database state is unchanged.