Composing Database Transactions with Ecto.Multi in Elixir

When a Single Database Call Isn't Enough

Imagine a user signup flow that must create a account record, generate an API token, and write an audit entry. If the token generation fails, you don’t want a half‑created user lingering in the database.

The usual pattern nests Repo.transaction(fn -> ... end) blocks. As the flow grows, the anonymous function becomes hard to read and error handling scatters across multiple case clauses.

Takeaway: Ecto.Multi lets you describe the whole transaction as data, separate the definition from execution, and automatically roll back on any failure.

Defining the Steps

Each step in a Multi is given a name (an atom) and an operation that returns {:ok, value} or {:error, reason}. Successful steps feed a map of their results to later steps, so you can use an ID from an insert to populate a foreign key in the next step.

Worked Example: Processing a Purchase

Suppose an order must create an order row, reserve inventory, and record a payment intent. All three must succeed or fail together.

alias Ecto.Multi
alias MyApp.Repo
alias MyApp.{Order, Inventory, Payment}

def place_order(user_id, product_id, qty) do
  Multi.new()
  # 1️⃣ Insert the order
  |> Multi.insert(:order, Order.changeset(%Order{}, %{user_id: user_id, product_id: product_id, quantity: qty}))
  # 2️⃣ Reserve stock using the order id
  |> Multi.run(:reserve, fn _repo, %{order: order} ->
        case Inventory.reserve(order.product_id, order.quantity) do
          {:ok, _} -> {:ok, \"reserved\"}
          {:error, reason} -> {:error, reason}
        end
      end)
  # 3️⃣ Create a payment intent (custom function)
  |> Multi.run(:payment, fn _repo, %{order: order} ->
        Payment.create_intent(order.id, order.quantity * 10) # cents
      end)
  |> Repo.transaction()
end

Checking the Outcome

The call to Repo.transaction/1 returns one of two shapes:

• {:ok, %{order: order, reserve: \"reserved\", payment: payment_intent}} when every step succeeded.
• {:error, failed_step, reason, changes_so_far} when a step failed. changes_so_far shows what would have been committed had the error not occurred, which is useful for debugging.

To verify the rollback, you can query the database after a failing run and confirm that no order row exists.

Trade‑offs and Limits

Side‑effects that live outside the DB

Ecto.Multi only guarantees atomicity for database operations. If a step sends an email, calls an external API, or writes a file, and a later DB step fails, those side effects are not undone. Keep non‑DB work after the transaction returns {:ok, _}.

Connection pool pressure

A Multi holds a single connection for the duration of all its steps. Long‑running calculations or slow network calls inside Multi.run can tie up a connection and lead to pool exhaustion. Move heavy work out of the transaction or use background jobs.

Quick Checklist for Using Ecto.Multi

• Name each step with a clear atom (e.g., :insert_user).
• Place operations that are most likely to fail early to fail fast.
• Test both success and failure paths; intentionally trigger a constraint violation to confirm rollback.
• Ensure the repo user has privileges for every insert, update, or delete in the Multi.
• Keep external side effects outside the Repo.transaction/1 block.