Composing Atomic Database Operations with Ecto.Multi

The Problem: Coordinating Multiple Writes Without Partial State

When a single user action requires several database writes — creating a user, a profile, and an audit log — the naive approach is to call Repo.insert multiple times. If the third insert fails, the first two remain committed, leaving the database in an inconsistent state. Wrapping everything in a manual Repo.transaction works, but it forces you to thread results through nested callbacks, making the code hard to read and test.

How Ecto.Multi Replaces Manual Transaction Management

Ecto.Multi lets you build a list of named operations (insert, update, delete, run) that execute in a single database transaction when passed to Repo.transaction/2. Each step can reference the results of previous steps via an accumulator map keyed by operation name. On any error, the whole transaction rolls back automatically, and the error tuple tells you exactly which step failed, its error value, and the partial results accumulated so far.

Worked Example: User Registration with Profile and Welcome Notification

Assume a Phoenix context Accounts with User and Profile schemas. We want to create both records and then enqueue a welcome email — all atomically.

defmodule MyApp.Accounts do
  alias MyApp.Repo
  alias MyApp.Accounts.{User, Profile}
  alias Ecto.Multi

  def register_user(attrs) do
    user_changeset = User.registration_changeset(%User{}, attrs)
    profile_changeset = Profile.changeset(%Profile{}, attrs["profile"])

    Multi.new()
    |> Multi.insert(:create_user, user_changeset)
    |> Multi.insert(:create_profile, profile_changeset)
    |> Multi.run(:enqueue_welcome, fn %{create_user: user} ->
      # This runs inside the same DB transaction
      case MyApp.EmailWorker.enqueue_welcome(user.id) do
        {:ok, job_id} -> {:ok, job_id}
        {:error, reason} -> {:error, reason}
      end
    end)
    |> Repo.transaction()
  end
end

If enqueue_welcome returns {:error, :queue_full}, the user and profile inserts are rolled back. The caller receives {:error, :enqueue_welcome, :queue_full, %{create_user: user, create_profile: profile}} — precise information for error handling or logging.

Composing Multis Across Contexts

Multi.merge/2 enables building larger transactions from smaller, context-owned multis. For example, a Notifications context can expose a function that returns a multi for sending a welcome notification:

defmodule MyApp.Notifications do
  alias Ecto.Multi

  def welcome_multi(user_id) do
    Multi.new()
    |> Multi.run(:send_welcome, fn _ ->
      MyApp.EmailWorker.enqueue_welcome(user_id)
    end)
  end
end

The registration flow then merges it:

Multi.new()
|> Multi.insert(:create_user, user_changeset)
|> Multi.insert(:create_profile, profile_changeset)
|> Multi.merge(Notifications.welcome_multi(user_id))  # user_id not yet known!
|> Repo.transaction()

Wait — user_id isn't available until :create_user runs. The merge approach works when the child multi doesn't need prior results. If it does, use Multi.run with the accumulator or restructure so the parent multi adds the dependent step after the merge.

Trade-offs and Limitations

• No savepoints or nested transactions. A failure aborts the entire multi. For partial commit semantics, split into separate Repo.transaction calls.
• Long-running work inside run steps holds the DB connection. External API calls, heavy computation, or job queue enqueues increase lock contention. Move side effects outside the transaction or use a job queue that guarantees eventual delivery.
• Duplicate operation names silently overwrite accumulator entries. Use unique, descriptive names like :create_user, :create_profile.
• Not a query builder. Complex reads still use Ecto.Query and typically run before building the multi.

Practical Verification Steps

1. Create a new Phoenix/Ecto project: mix phx.new demo --no-ecto false.
2. Generate a User schema and write a multi that inserts a user, inserts a profile, and runs a custom step logging the user ID.
3. Observe the single transaction in SQL logs (BEGIN ... COMMIT).
4. Intentionally return {:error, :boom} from a run step and inspect the tuple returned by Repo.transaction to see the failed operation name, error, and partial results.
5. Check the Ecto.Multi documentation for current function signatures, especially Multi.run/4 (per-step timeout) and Repo.transaction/3 (whole-transaction timeout).

Closing Recommendation

Validate all inputs first with changesets, build a multi from the valid changesets, then call Repo.transaction once. This keeps controllers thin, avoids partial writes, and gives you a clear audit trail of which step failed. For any step that talks to an external service, consider returning {:ok, :enqueued} immediately and processing the side effect asynchronously — your database connections will thank you.