Grouping Database Writes with Ecto.Multi Without Losing Track of Failures

One failed insert should not leave half a record behind

Imagine you are signing up a new customer. The request creates an account record, then a billing profile linked to that account. If the billing profile fails validation, you do not want an empty account sitting in the database. Wrapping each insert in its own transaction is not enough, because the second transaction can commit while the first has already succeeded. Nesting repository calls manually quickly becomes hard to read and harder to debug.

Ecto.Multi solves this by grouping multiple repository operations into one labeled pipeline. Every step either commits together or rolls back together, and the return value tells you exactly which label failed.

What a Multi step contains

An Ecto.Multi is a struct that holds a list of operations. You build it with functions like `Ecto.Multi.insert/4`, `Ecto.Multi.update/4`, `Ecto.Multi.delete/4`, and `Ecto.Multi.run/5`. Each operation is tagged with an atom, such as `:account` or `:profile`. A changeset is Ecto's structure for tracking and validating changes before they reach the database. When you pass the built multi to `YourApp.Repo.transaction/1`, Ecto runs the steps inside a single database transaction.

If every step succeeds, you receive `{:ok, %Ecto.Multi{}}`. If any step fails, you receive `{:error, failed_label, value, changes}`, where `failed_label` is the atom you assigned, `value` is the result up to that point, and `changes` contains the multi's accumulated changes. This shape makes it straightforward to render the right error message or retry the right step.

Worked example: account and owner in one transaction

Here is a context function that creates an account and its owner record atomically. Run this inside your application, for example in `lib/yourapp/accounts.ex`, with `iex -S mix` available for interactive checks.

defmodule Yourapp.Accounts do
  import Ecto.Query
  alias Yourapp.Repo
  alias Yourapp.Account
  alias Yourapp.Owner

  def create_account_with_owner(account_attrs, owner_attrs) do
    account_changeset =
      Account.changeset(%Account{}, account_attrs)

    owner_changeset =
      Owner.changeset(%Owner{}, owner_attrs)

    Ecto.Multi.new()
    |> Ecto.Multi.insert(:account, account_changeset)
    |> Ecto.Multi.insert(:owner, owner_changeset)
    |> Repo.transaction()
  end
end

The database user configured in `config/config.exs` needs INSERT privileges on both tables. Because `Owner` likely belongs to `Account`, make sure the changeset or a `run` step sets the foreign key before insertion; otherwise the database will reject the owner row and roll back the entire multi.

Call it like this:

case Yourapp.Accounts.create_account_with_owner(
       %{name: "Acme Corp"},
       %{email: "[contact removed]"}
     ) do
  {:ok, multi} ->
    IO.puts("Account and owner committed")

  {:error, :owner, _value, changes} ->
    IO.inspect(changes, label: "Owner step failed")

  {:error, :account, _value, changes} ->
    IO.inspect(changes, label: "Account step failed")
end

If the owner email violates a unique index, the database rejects the owner insert, Ecto rolls back the account insert too, and the tuple identifies `:owner` as the failed step. You do not end up with an account that has no owner.

Limitations to keep in mind

• Validation happens late. Ecto.Multi does not validate associations or database constraints until `Repo.transaction` runs the operations. Invalid data can reach the database before the rollback.
• Long transactions hold connections. Every step shares the same database connection and transaction for the full duration. Under high concurrency, this can reduce throughput and increase lock contention.
• Side effects need care. Do not place non-database actions such as sending email directly in a multi step unless you can compensate for them if the transaction rolls back.

Check that only one transaction ran

1. Enable SQL logging in your repository configuration so you can see `BEGIN` and `COMMIT`.
2. Call the function with valid attributes and confirm the log shows a single transaction block containing both inserts.
3. Call it again with an attribute that violates a constraint on the second step. Confirm the return tuple names the second label and that the first row does not remain in the table.

If you see two separate `BEGIN` blocks, the operations are not grouped as intended.

When to use it

Use Ecto.Multi when several writes must succeed or fail together and you want the failure label in your error handling. Avoid it for long-running steps, external API calls, or when you need independent commits for each record. For a single changeset, a plain `Repo.insert/2` is simpler.