name: elixir-ecto-patterns description: Use when Elixir Ecto patterns including schemas, changesets, queries, and transactions. Use when building database-driven Elixir applications. allowed-tools:

Bash
Read

Elixir Ecto Patterns

Master Ecto, Elixir's database wrapper and query generator. This skill covers schemas, changesets, queries, associations, and transactions for building robust database applications.

Schema Definition

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

  schema "users" do
    field :name, :string
    field :email, :string
    field :age, :integer
    field :is_active, :boolean, default: true
    field :role, Ecto.Enum, values: [:user, :admin, :moderator]

    has_many :posts, MyApp.Post
    belongs_to :organization, MyApp.Organization

    timestamps()
  end

  def changeset(user, attrs) do
    user
    |> cast(attrs, [:name, :email, :age, :is_active, :role])
    |> validate_required([:name, :email])
    |> validate_format(:email, ~r/@/)
    |> validate_number(:age, greater_than: 0, less_than: 150)
    |> unique_constraint(:email)
  end
end

Changeset Validations

defmodule MyApp.Post do
  use Ecto.Schema
  import Ecto.Changeset

  schema "posts" do
    field :title, :string
    field :body, :text
    field :published, :boolean, default: false
    field :tags, {:array, :string}, default: []

    belongs_to :user, MyApp.User

    timestamps()
  end

  def changeset(post, attrs) do
    post
    |> cast(attrs, [:title, :body, :published, :tags, :user_id])
    |> validate_required([:title, :body, :user_id])
    |> validate_length(:title, min: 3, max: 100)
    |> validate_length(:body, min: 10)
    |> foreign_key_constraint(:user_id)
  end

  def publish_changeset(post) do
    post
    |> change(published: true)
  end
end

Basic Queries

import Ecto.Query

# Get all users
Repo.all(User)

# Get user by ID
Repo.get(User, 1)
Repo.get!(User, 1)  # Raises if not found

# Get by specific field
Repo.get_by(User, email: "user@example.com")

# Filter with where clause
query = from u in User, where: u.age > 18
Repo.all(query)

# Select specific fields
query = from u in User, select: {u.id, u.name}
Repo.all(query)

# Order results
query = from u in User, order_by: [desc: u.inserted_at]
Repo.all(query)

# Limit and offset
query = from u in User, limit: 10, offset: 20
Repo.all(query)

Complex Queries

# Combining multiple conditions
query =
  from u in User,
    where: u.is_active == true,
    where: u.age >= 18,
    order_by: [desc: u.inserted_at],
    limit: 10

Repo.all(query)

# Using pipe syntax
User
|> where([u], u.is_active == true)
|> where([u], u.age >= 18)
|> order_by([u], desc: u.inserted_at)
|> limit(10)
|> Repo.all()

# Dynamic queries
def filter_users(params) do
  User
  |> filter_by_name(params["name"])
  |> filter_by_age(params["min_age"])
  |> Repo.all()
end

defp filter_by_name(query, nil), do: query
defp filter_by_name(query, name) do
  where(query, [u], ilike(u.name, ^"%#{name}%"))
end

defp filter_by_age(query, nil), do: query
defp filter_by_age(query, min_age) do
  where(query, [u], u.age >= ^min_age)
end

Associations and Preloading

# Preload associations
user = Repo.get(User, 1) |> Repo.preload(:posts)

# Preload nested associations
user = Repo.get(User, 1) |> Repo.preload([posts: :comments])

# Query with preload
query = from u in User, preload: [:posts, :organization]
Repo.all(query)

# Custom preload query
posts_query = from p in Post, where: p.published == true

query = from u in User, preload: [posts: ^posts_query]
Repo.all(query)

# Join and preload
query =
  from u in User,
    join: p in assoc(u, :posts),
    where: p.published == true,
    preload: [posts: p]

Repo.all(query)

Aggregations and Grouping

# Count records
Repo.aggregate(User, :count)

# Count with condition
query = from u in User, where: u.is_active == true
Repo.aggregate(query, :count)

# Other aggregations
Repo.aggregate(User, :avg, :age)
Repo.aggregate(User, :sum, :age)
Repo.aggregate(User, :max, :age)

# Group by
query =
  from u in User,
    group_by: u.role,
    select: {u.role, count(u.id)}

Repo.all(query)

# Group with having
query =
  from u in User,
    group_by: u.role,
    having: count(u.id) > 5,
    select: {u.role, count(u.id)}

Repo.all(query)

Inserting and Updating

# Insert with changeset
attrs = %{name: "John", email: "john@example.com", age: 30}

%User{}
|> User.changeset(attrs)
|> Repo.insert()

# Insert without changeset
Repo.insert(%User{name: "Jane", email: "jane@example.com"})

# Update
user = Repo.get(User, 1)

user
|> User.changeset(%{age: 31})
|> Repo.update()

# Update all
query = from u in User, where: u.is_active == false
Repo.update_all(query, set: [is_active: true])

# Delete
user = Repo.get(User, 1)
Repo.delete(user)

# Delete all
query = from u in User, where: u.is_active == false
Repo.delete_all(query)

Transactions

# Basic transaction
Repo.transaction(fn ->
  user = Repo.insert!(%User{name: "Alice"})
  Repo.insert!(%Post{title: "First post", user_id: user.id})
end)

# Multi for complex transactions
alias Ecto.Multi

Multi.new()
|> Multi.insert(:user, User.changeset(%User{}, user_attrs))
|> Multi.insert(:post, fn %{user: user} ->
  Post.changeset(%Post{}, Map.put(post_attrs, :user_id, user.id))
end)
|> Multi.run(:send_email, fn _repo, %{user: user} ->
  send_welcome_email(user)
end)
|> Repo.transaction()

Embedded Schemas

defmodule MyApp.Address do
  use Ecto.Schema
  import Ecto.Changeset

  embedded_schema do
    field :street, :string
    field :city, :string
    field :state, :string
    field :zip, :string
  end

  def changeset(address, attrs) do
    address
    |> cast(attrs, [:street, :city, :state, :zip])
    |> validate_required([:city, :state])
  end
end

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

  schema "users" do
    field :name, :string
    embeds_one :address, MyApp.Address

    timestamps()
  end

  def changeset(user, attrs) do
    user
    |> cast(attrs, [:name])
    |> cast_embed(:address, required: true)
  end
end

Custom Ecto Types

defmodule MyApp.Encrypted do
  use Ecto.Type

  def type, do: :binary

  def cast(value) when is_binary(value), do: {:ok, value}
  def cast(_), do: :error

  def dump(value) when is_binary(value) do
    {:ok, encrypt(value)}
  end

  def load(value) when is_binary(value) do
    {:ok, decrypt(value)}
  end

  defp encrypt(value) do
    # Encryption logic
    value
  end

  defp decrypt(value) do
    # Decryption logic
    value
  end
end

# Usage in schema
schema "users" do
  field :secret, MyApp.Encrypted
end

When to Use This Skill

Use elixir-ecto-patterns when you need to:

Build database-backed Elixir applications
Define schemas and data models with validations
Write complex database queries with Ecto's DSL
Manage database relationships and associations
Handle data transformations with changesets
Implement transactions for data consistency
Work with PostgreSQL, MySQL, or other databases
Build Phoenix applications with database access
Create robust data validation layers

Best Practices

Always use changesets for data validation
Preload associations to avoid N+1 queries
Use transactions for multi-step database operations
Leverage Ecto.Multi for complex transaction logic
Keep schemas focused and avoid god objects
Use virtual fields for computed or temporary data
Index foreign keys and frequently queried fields
Use fragments for complex SQL when needed
Write composable query functions
Test database constraints and validations

Common Pitfalls

Not preloading associations (N+1 query problem)
Forgetting to validate required fields
Not using transactions for related operations
Hardcoding queries instead of composing them
Ignoring database constraints in schemas
Not handling changeset errors properly
Overusing embedded schemas for relational data
Missing indexes on foreign keys
Not using Repo.transaction for multi-step operations
Exposing raw Ecto queries in business logic

elixir-ecto-patterns