Ecto

Ecto is a data validation and database persistence Elixir library. This is the backbone of database operations in the Phoenix Webframework, this is the analog to ActiveRecord within the Rails ecosystem.

Transactions in Ecto hacks

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

 
step_1 = fn repo, _ ->
  %Dog{name: "hello"}
  |> repo.insert()
end
 
step_2 = fn repo, %{step_1_call: dog} ->
  %DogCollar{dog_id: dog.id}
  |> Repo.insert
end
 
Multi.new
|> Multi.run(:step_1_call, step_1)
|> Multi.run(:step_2_call, 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_1call).

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
 
    timestamps()
  end
end
 
defmodule Team do
  schema "teams" do
    field :name, :string
    has_many :players, Player
 
    timestamps()
  end
end

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)
 
  timestamps()
end
 
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
  player
  |> cast(attrs, [:name, :team_id, :is_captain])
  |> cast_assoc(:team)
  |> unique_constraint([:team_id, :is_captain], name: "player_team_captain_index")
end

1. Buszkiewicz, M. How database transactions work in Ecto and why Elixir makes it awesome? https://curiosum.com/blog/elixir-ecto-database-transactions (2019).