← docs.speechwave.live

Ecto queries cheatsheet

Repo operations

OperationReturns on successReturns on failure
Repo.get(Schema, id)structnil
Repo.get!(Schema, id)structraises
Repo.get_by(Schema, col: val)structnil
Repo.get_by!(Schema, col: val)structraises
Repo.one(query)structnil (raises if >1)
Repo.all(query)list[]
Repo.aggregate(query, :count)integern/a
Repo.insert(changeset){:ok, struct}{:error, changeset}
Repo.update(changeset){:ok, struct}{:error, changeset}
Repo.delete(struct){:ok, struct}{:error, changeset}
Repo.insert!(changeset)structraises
Repo.delete_all(query){count, nil}n/a
Repo.preload(struct, [:assoc])struct with assocn/a
Repo.transaction(fn){:ok, result}{:error, reason}

Query clauses

from u in User,
  where: u.email == ^email,              # filter (^ pins external value)
  where: not is_nil(u.api_key),          # NULL check
  where: u.plan in [:pro, :org],         # IN list
  join: i in assoc(u, :identities),      # inner join via association
  left_join: t in assoc(u, :talks),      # left join (keeps unmatched)
  join: t in Talk, on: t.user_id == u.id,# explicit join condition
  preload: [:identities],               # eager load associations
  group_by: u.id,                        # group
  select: %{user: u, count: count(t.id)},# custom return shape
  select: {u.email, u.plan},             # return tuple
  order_by: [desc: u.inserted_at],       # sort
  limit: 10,                             # limit rows
  offset: 20                             # skip rows

Changeset pipeline

%Schema{}                              # start with struct
|> Schema.changeset(attrs)             # build changeset with validations
|> Repo.insert()                       # execute

existing_record                        # start with loaded struct
|> Schema.changeset(new_attrs)         # build changeset
|> Repo.update()                       # execute

Common changeset functions

Available after import Ecto.Changeset

FunctionPurpose
cast(struct, attrs, fields)Pick allowed fields from attrs
validate_required(cs, fields)Must be present
validate_format(cs, field, regex)Must match pattern
validate_length(cs, field, opts)String/list length limits
validate_inclusion(cs, field, list)Must be in list
unique_constraint(cs, field)DB uniqueness (checked on insert)
put_change(cs, field, value)Set a field programmatically
get_field(cs, field)Read current value (changed or original)
get_change(cs, field)Read changed value only (nil if unchanged)
add_error(cs, field, message)Add a custom error

with for chaining fallible operations

with {:ok, user} <- find_user(email),
     {:ok, token} <- generate_token(user) do
  send_email(user, token)
else
  {:error, :not_found} -> handle_missing()
  {:error, reason} -> handle_error(reason)
end

Each <- clause must match. First non-match jumps to else. Often used inside Repo.transaction/1.

fragment for raw SQL

where: fragment("lower(?) = ?", t.email, ^email)

# ? placeholders are filled with Ecto bindings (safe from injection)