Ecto is a data validation and database persistence elixir library. This is the backbone of database operations in the Phoenix Webframework.

Transactions in Ecto

Ecto well supports database transactions via the Multi module. You can setup arbitrary functions and run them in sequence with good error handling support1:

step_1 = fn repo, _ ->
  %Dog{name: "hello"}
  |> repo.insert()

step_2 = fn repo, %{step_1_call: dog} ->
  |> Repo.insert
|>, step_1)
|>, step_2)
|> Repo.transaction

Notice that the results from the first call are packed into the map argument to the function of the next call under the name that the step was run (:step_1_call).

Partial Unique Index (Boolean column) in Ecto Migration

It’s useful to be able to say that for a given column pair, there can be only one of one kind of a pair of attributes. For example, consider a team has many mambas but it can only have one captain.

Assume a structure like

defmodule Player do
  schema "players" do
    field :name, :string
    field :is_captain, :boolean, default: false
    belongs_to :team, Team


defmodule Team do
  schema "teams" do
    field :name, :string
    has_many :players, Player


Now we want to enforce that for a given, there is only one player at a time who can be the captain (is_captain true).

In the migration that creates the players:

    create table(:players) do
      add :is_captain, :boolean, default: false, null: false
      add :name, :string
      add :team_id, references(:teams,  on_delete: :nothing)


    create index(:players, [:team_id])

    create unique_index(:players, [:team_id, :is_captain],
             name: "player_team_captain_index",
             where: "is_captain IS TRUE"

Importantly is the where clause on the unique index which makes the constrain unique for team id where the is_captain is true. Note that you cannot just do where: is_captain because the index definition is finicky in postgres. Also, is_captain = true will not work.

Finally, you can setup the changeset to properly catch this uniqueness conflict:

  def changeset(player, attrs) do
    |> cast(attrs, [:name, :team_id, :is_captain])
    |> cast_assoc(:team)
    |> unique_constraint([:team_id, :is_captain], name: "player_team_captain_index")


Buszkiewicz, M. How database transactions work in Ecto and why Elixir makes it awesome? Curiosum at (2019).