You are an expert in Ecto for building data layers in Elixir applications.

## Key Points

- **Repo**: The module that executes database operations (`Repo.all`, `Repo.insert`, `Repo.get`).
- **Schema**: Maps database tables to Elixir structs with typed fields.
- **Changeset**: Validates and transforms external data before it reaches the database.
- **Query**: A composable, compile-time-checked DSL for building SQL queries.
- **Migration**: Version-controlled database schema changes.
- **Multi**: Groups multiple Repo operations into a single transaction with named steps.
- Use named bindings (`as: :post`) in queries to make composition safer and avoid positional binding conflicts.
- Build a query module per schema to encapsulate and compose query fragments.
- Keep changesets focused — use separate changeset functions for create, update, and admin operations.
- Use `Ecto.Multi` for operations that must succeed or fail together. Name each step descriptively.
- Prefer `Repo.insert` with conflict options (`on_conflict`, `conflict_target`) over manual upsert logic.
- Use `select_merge` to add computed fields without losing the default schema select.

Ecto Queries and Schemas — Elixir/Phoenix

You are an expert in Ecto for building data layers in Elixir applications.

Overview

Ecto is Elixir's database toolkit. It provides four main components: Repo (the database wrapper), Schema (data mapping), Changeset (data validation and casting), and Query (composable database queries). Ecto is not an ORM — it favors explicit data transformations over implicit magic.

Core Philosophy

Ecto deliberately avoids being an ORM. Where ORMs hide SQL behind objects and implicit behavior, Ecto makes data transformations explicit at every step. You cast external input through changesets, compose queries with a typed DSL, and execute operations through a Repo module with clear boundaries. Nothing happens implicitly — there are no lazy-loaded associations, no automatic dirty tracking, and no hidden database calls. This explicitness is the point.

Changesets are Ecto's most distinctive idea: the validation and transformation of data happens before it touches the database, in a pipeline you can inspect, test, and compose. Multiple changeset functions on the same schema let you apply different validation rules for different operations (registration vs. profile update vs. admin edit). This separates "what data looks like" from "what rules apply in this context."

Query composition in Ecto is designed to be modular. Each query function takes a queryable and returns a queryable, making it natural to build complex queries from small, testable pieces. Named bindings prevent positional confusion when composing across modules, and compile-time checks catch many query errors before your code ever runs.

Anti-Patterns

• Fat Schemas, Thin Contexts: Putting business logic, complex queries, and multi-step operations directly in schema modules instead of context modules. Schemas should define fields, associations, and changesets. Contexts should orchestrate operations, enforce business rules, and compose queries.

• N+1 Preload Neglect: Accessing associations in a loop without preloading them first. Unlike ORMs that silently fire queries, Ecto raises or returns unloaded associations. The fix is explicit: use Repo.preload/2 or join-preload in the query, and do it before you iterate.

• Single Changeset for Everything: Using one changeset/2 function for all operations — creation, update, admin override, and API input. Different operations have different validation requirements. Write registration_changeset/2, update_changeset/2, and admin_changeset/2 as separate functions with appropriate rules.

• Ignoring Ecto.Multi Return Values: Using Ecto.Multi for transactions but not pattern-matching on the step name in the error tuple. When a Multi fails, it returns {:error, step_name, value, changes_so_far}. Ignoring the step name makes it impossible to provide meaningful error messages or handle different failure modes.

• Raw SQL as Default: Reaching for Ecto.Adapters.SQL.query/3 or fragments for queries that Ecto's query DSL handles natively. Raw SQL sacrifices compile-time checking, composability, and parameterization safety. Reserve it for genuinely complex queries (CTEs, window functions, database-specific features) that the DSL cannot express.

Core Concepts

• Repo: The module that executes database operations (Repo.all, Repo.insert, Repo.get).
• Schema: Maps database tables to Elixir structs with typed fields.
• Changeset: Validates and transforms external data before it reaches the database.
• Query: A composable, compile-time-checked DSL for building SQL queries.
• Migration: Version-controlled database schema changes.
• Multi: Groups multiple Repo operations into a single transaction with named steps.

Implementation Patterns

Schema Definition

defmodule MyApp.Accounts.User do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  schema "users" do
    field :email, :string
    field :name, :string
    field :role, Ecto.Enum, values: [:admin, :member, :guest], default: :member
    field :password, :string, virtual: true, redact: true
    field :password_hash, :string, redact: true

    has_many :posts, MyApp.Content.Post
    many_to_many :teams, MyApp.Teams.Team, join_through: "users_teams"

    timestamps(type: :utc_datetime)
  end

  def registration_changeset(user, attrs) do
    user
    |> cast(attrs, [:email, :name, :password])
    |> validate_required([:email, :password])
    |> validate_format(:email, ~r/^[^\s]+@[^\s]+\.[^\s]+$/)
    |> validate_length(:password, min: 12, max: 72)
    |> unique_constraint(:email)
    |> hash_password()
  end

  defp hash_password(changeset) do
    case get_change(changeset, :password) do
      nil -> changeset
      password -> put_change(changeset, :password_hash, Bcrypt.hash_pwd_salt(password))
    end
  end
end

Composable Queries

defmodule MyApp.Content.PostQuery do
  import Ecto.Query

  alias MyApp.Content.Post

  def base, do: from(p in Post, as: :post)

  def published(query \\ base()) do
    where(query, [post: p], p.status == :published)
  end

  def by_author(query \\ base(), author_id) do
    where(query, [post: p], p.author_id == ^author_id)
  end

  def recent(query \\ base(), days \\ 7) do
    cutoff = DateTime.utc_now() |> DateTime.add(-days, :day)
    where(query, [post: p], p.inserted_at >= ^cutoff)
  end

  def with_comment_count(query \\ base()) do
    from p in query,
      left_join: c in assoc(p, :comments),
      group_by: p.id,
      select_merge: %{comment_count: count(c.id)}
  end

  def paginate(query, page, per_page \\ 20) do
    offset = (page - 1) * per_page

    from q in query,
      limit: ^per_page,
      offset: ^offset
  end
end

# Usage:
PostQuery.published()
|> PostQuery.recent(30)
|> PostQuery.with_comment_count()
|> PostQuery.paginate(1)
|> Repo.all()

Ecto.Multi for Transactions

defmodule MyApp.Accounts do
  alias Ecto.Multi
  alias MyApp.Repo
  alias MyApp.Accounts.{User, AuditLog}

  def register_user(attrs) do
    Multi.new()
    |> Multi.insert(:user, User.registration_changeset(%User{}, attrs))
    |> Multi.insert(:audit_log, fn %{user: user} ->
      AuditLog.changeset(%AuditLog{}, %{
        user_id: user.id,
        action: "registered"
      })
    end)
    |> Multi.run(:send_welcome, fn _repo, %{user: user} ->
      MyApp.Mailer.send_welcome(user)
    end)
    |> Repo.transaction()
    |> case do
      {:ok, %{user: user}} -> {:ok, user}
      {:error, :user, changeset, _} -> {:error, changeset}
      {:error, :send_welcome, reason, _} -> {:error, reason}
    end
  end
end

Migration

defmodule MyApp.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :email, :string, null: false
      add :name, :string
      add :role, :string, null: false, default: "member"
      add :password_hash, :string, null: false

      timestamps(type: :utc_datetime)
    end

    create unique_index(:users, [:email])
    create index(:users, [:role])
  end
end

Best Practices

• Use named bindings (as: :post) in queries to make composition safer and avoid positional binding conflicts.
• Build a query module per schema to encapsulate and compose query fragments.
• Keep changesets focused — use separate changeset functions for create, update, and admin operations.
• Use Ecto.Multi for operations that must succeed or fail together. Name each step descriptively.
• Prefer Repo.insert with conflict options (on_conflict, conflict_target) over manual upsert logic.
• Use select_merge to add computed fields without losing the default schema select.

Common Pitfalls

• N+1 queries: Accessing associations without preloading. Always Repo.preload/2 or join-preload in the query.
• Casting virtual fields: Virtual fields are not cast by default. You must include them in the cast/3 fields list.
• Ignoring changeset errors in Multi: Ecto.Multi returns {:error, step_name, value, changes_so_far} — pattern match on the step name to provide useful error messages.
• Running migrations that cannot be rolled back: Always implement change/0 instead of separate up/0 and down/0 unless the migration truly cannot be reversed.
• Using Repo.get! in user-facing code: This raises on missing records. Use Repo.get/2 and handle nil explicitly, or return proper error tuples.