Using Ecto.Multi to Keep Database Operations Atomic in Elixir

Problem

When you need to write several records at once—say, create a user and related profile rows—the database must treat those writes as a single unit. If one step fails, the others should not remain half‑committed, otherwise the application sees an inconsistent state.

Thesis

Ecto.Multi lets you bundle inserts, updates, and deletes into one atomic transaction, so either all succeed or all are rolled back automatically. This replaces nested Repo.transaction blocks and manual rollback code with a declarative pipeline.

How Ecto.Multi works

Ecto.Multi sits on top of Ecto.Repo.transaction/1. You build a list of operations—usually changeset functions or raw SQL—using the Ecto.Multi helpers. The list is passed to Ecto.Multi.run/1, which is executed inside a transaction started by Repo.transaction/1. Each operation runs in the order defined; if any step returns an error tuple, Ecto aborts the transaction and returns {:error, changeset, failed_operation, params, multi}. When every step succeeds, the result is {:ok, multi}. Because changeset validations are performed before hitting the database, an invalid changeset yields an error tuple instead of raising an exception. Nesting is supported via Ecto.Multi.savepoint/2 when the underlying adapter (PostgreSQL) implements savepoints; MySQL support varies.

Key fields in the error tuple: changeset holds the invalid changeset, failed_operation names the step that failed (e.g., :user or :account), and params contains the arguments supplied to that step. This structure lets callers handle validation and constraint errors without rescuing exceptions.

Concrete example

Imagine two schemas: MyApp.User and MyApp.Account. The goal is to create a user and immediately create a matching account, with both operations succeeding together.

1. Define a changeset for the user. This can be a simple function that sets required fields and runs validations.
2. Define a changeset for the account that references the user’s primary key once it is known.
3. Construct the multi by chaining insert/2, update/2, or run/1 steps, naming each step for clear error reporting.
4. Execute the multi inside a transaction with Repo.transaction/1. Pattern‑match the result to distinguish success from failure.

Full code (run in IEx after your application is compiled):

def create_user_and_account(%{email: email, name: name}) do
  # 1️⃣ User changeset – pure function, no side effects
  user_changeset =
    MyApp.User.changeset(%MyApp.User{}, %{email: email, name: name})

  # 2️⃣ Account changeset – placeholder user_id, will be filled after insert
  account_changeset =
    MyApp.Account.changeset(%MyApp.Account{}, %{user_id: nil})

  # 3️⃣ Build the multi, giving each step a descriptive name
  multi =
    Ecto.Multi.new()
    |> Ecto.Multi.insert(:user, user_changeset)
    |> Ecto.Multi.update(:account, account_changeset)  # will be updated after user insert
    |> Ecto.Multi.run()

  case multi do
    {:ok, _} ->
      # Success – retrieve the inserted structs from the result
      {:ok, %{user: user, account: account}}
    {:error, changeset, :user, _params, _multi} ->
      # User validation failed (e.g., missing email)
      {:error, changeset}
    {:error, changeset, :account, _params, _multi} ->
      # Account validation failed (unlikely here)
      {:error, changeset}
  end
end

Running the function in IEx:

iex> {:ok, result} = MyApp.UserAndAccount.create_user_and_account(%{email: "[contact removed]", name: "Alice"})

To test the rollback behavior, force the second changeset to fail by providing an invalid user_id after the user insert. Because the transaction aborts, the user row will not be persisted.

Verification steps:

• Start IEx in the project directory.
• Call Repo.transaction(Ecto.Multi.new()) with the multi built above.
• Modify the account changeset to set user_id: -1 (an invalid foreign key) before running.
• Confirm that Repo.all_from(MyApp.User) returns an empty list, showing the user insert was rolled back.
• Inspect the returned tuple; it should match {:error, changeset, :account, params, multi}.

Trade‑offs and limitation

A Multi holds a database connection for the entire transaction, so large or deeply nested multis can increase lock contention and reduce overall concurrency. Keep each Multi focused on a small, coherent set of operations to minimise the time the connection is occupied.

Partial success is impossible; if you need independent atomicity for different groups, split the work into separate Multis. Validation errors are returned as structured tuples, so callers must pattern‑match rather than rely on exceptions, which adds a small cognitive overhead.

Adapter differences matter: PostgreSQL fully supports savepoints, allowing deep nesting, while MySQL’s support for nested transactions is more limited, which can affect how deep your Multi can be without hitting adapter‑specific limits.

Actionable closing

To start using Ecto.Multi in your project:

• Identify logical groups of database writes (e.g., create‑user + create‑profile).
• Write pure changeset functions for each step; they can be reused across contexts.
• Build the multi with Ecto.Multi.insert/2, update/2, or run/1, giving each step a clear name.
• Wrap the multi in Repo.transaction/1 and handle the two possible return forms with a case statement.
• Test the behavior in a development console by forcing a failure and verifying that the database state rolls back completely.

By replacing nested Repo.transaction blocks with a declarative Multi pipeline, your data‑access code becomes clearer, less error‑prone, and easier to maintain.