Composing Atomic Database Writes with Ecto.Multi in Elixir

The Problem: Coordinating Multiple Writes Atomically

When an application needs to create a user, their initial post, and send a welcome notification in a single operation, you face a coordination challenge. If the post insert fails after the user is created, you have an orphaned user record. If the notification fails after both writes, you have inconsistent state. Ecto.Multi solves this by letting you declare a sequence of dependent Repo operations that execute inside one database transaction, with explicit rollback on any failure.

How Ecto.Multi Builds a Transactional Workflow

Ecto.Multi is a data structure that accumulates named steps. Each step can be an insert, update, delete, or a custom function. Steps declare dependencies by referencing earlier step names, so later steps receive the changes map containing results from previous steps. When you pass the Multi to Repo.transaction/1, Ecto executes steps sequentially inside a single transaction. On success you get {:ok, changes} where changes is a map of step name to result. On failure you get {:error, failed_step, reason, changes} with the changes accumulated up to the failure point—already rolled back by the database.

Worked Example: User, Post, and Notification

The following module demonstrates a typical registration flow. It assumes a Phoenix/Ecto 3.x context with User and Post schemas and a Notifier module that sends emails.

defmodule Accounts.RegisterUser do\n  alias MyApp.{Repo, User, Post, Notifier}\n  alias Ecto.Multi\n\n  def call(attrs) do\n    Multi.new()\n    |> Multi.insert(:user, User.changeset(%User{}, attrs))\n    |> Multi.insert(:post, fn %{user: user} ->\n      Post.changeset(%Post{}, %{user_id: user.id, title: \"Welcome!\"})\n    end)\n    |> Multi.run(:notify, fn %{user: user, post: post}, _changes ->\n      case Notifier.deliver_welcome(user.email, post.id) do\n        :ok -> {:ok, :sent}\n        {:error, reason} -> {:error, reason}\n      end\n    end)\n    |> Repo.transaction()\n  end\nend

Key points in this example:

\n
• Multi.insert(:user, ...) runs a standard changeset insert. The result is stored under :user in the changes map.
\n
• The second Multi.insert(:post, fn %{user: user} -> ... end) receives the changes map, extracts the created user, and builds a post changeset that references user.id. This dependency is explicit and type-safe.
\n
• Multi.run(:notify, fn %{user: user, post: post}, _ -> ... end) runs arbitrary code inside the transaction. It must return {:ok, value} or {:error, reason}. Returning an error tuple aborts the whole transaction; the database rolls back the user and post inserts automatically.
\n
• The final Repo.transaction/1 returns either {:ok, %{user: %User{}, post: %Post{}, notify: :sent}} or {:error, :notify, :smtp_failed, %{user: %User{}, post: %Post{}}}.
\n

\n\n

Limits You Should Respect

\n

\n
• Single transaction boundary: All steps share one database transaction. Long-running work (external API calls, heavy computation) holds locks and increases contention. Keep Multi.run callbacks fast and pure.
\n
• No automatic retry: Serialization failures (e.g., Postgrex.Error with :serialization_failure) are not retried. Implement retry logic at the caller level if needed.
\n
• Step functions must return tagged tuples: Returning a raw value or raising an exception breaks the contract and may leave the transaction in an undefined state depending on the adapter.
\n
• Visibility is transaction-scoped: Changes are visible to later steps in the same Multi but not to other database connections until commit. Do not query the just-inserted rows from a separate Repo call outside the Multi expecting to see them.
\n

\n\n

Common Mistakes and How to Avoid Them

\n

Using Multi for Read-Only Queries

\n

Multi is designed for writes. Wrapping Repo.all or Repo.one in a Multi adds overhead without benefit. Use plain Repo queries for reads; compose them before or after the Multi.

\n\n

Nesting Repo.transaction Inside Multi.run

\n

Calling Repo.transaction again inside a Multi.run callback causes adapter errors (nested transactions are not supported by most adapters). If you need additional writes, add them as more Multi.insert/update/delete steps before the run step.

\n\n

Assuming Parallel Execution

\n

Steps run strictly in the order they are added. The changes map passed to each step reflects only earlier steps. There is no parallelism; plan step order accordingly.

\n\n

Mixing Manual Repo Calls Outside the Multi

\n

If you call Repo.insert directly before or after the Multi, those writes are in separate transactions and will not roll back with the Multi. Keep all related writes inside the same Multi.

\n\n

Verifying Behavior in Your Environment

\n

Because adapter behavior and Ecto version details can vary, run these quick checks in an IEx session with your application started:

\n

\n
1. Confirm the Multi API exists:\n

   iex> :erlang.function_info(Ecto.Multi, :new)\n# => [{:arity, 0}, ...]

   \n
\n
2. Create a minimal test schema (e.g., a Temp table) and run a Multi with an intentional error step:\n

   iex> Multi.new()\n...> |> Multi.insert(:temp, %Temp{name: \"test\"})\n...> |> Multi.run(:fail, fn _, _ -> {:error, :boom} end)\n...> |> Repo.transaction()\n# => {:error, :fail, :boom, %{temp: %Temp{...}}}

   \nThen query Repo.all(Temp) to verify zero rows—confirming rollback worked.\n
\n
3. Inspect the return shape of Repo.transaction/1 in your codebase to ensure it matches {:ok, map} / {:error, step, reason, map} for your adapter (Postgrex, MyXQL, etc.).
\n

\n\n

When to Choose Multi Over Alternatives

\n

Use Ecto.Multi when you have multiple dependent writes that must succeed or fail together, and the entire workflow completes in milliseconds. For long-running processes, external side effects, or workflows requiring independent commits, consider a saga pattern with explicit compensation logic or a message-driven architecture instead.