Elixir Ecto Patterns

Elixir Ecto 模式

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

精通Ecto——Elixir的数据库包装器和查询生成器。本技能涵盖Schema、Changeset、查询、关联和事务，助力构建健壮的数据库应用。

Schema Definition

Schema 定义

elixir

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

elixir

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

Changeset 验证

elixir

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

elixir

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

基础查询

elixir

import Ecto.Query

elixir

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)

undefined

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

undefined

Complex Queries

复杂查询

elixir

undefined

elixir

undefined

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)

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

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

undefined

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

undefined

Associations and Preloading

关联与预加载

elixir

undefined

elixir

undefined

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)

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)

undefined

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

Repo.all(query)

undefined

Aggregations and Grouping

聚合与分组

elixir

undefined

elixir

undefined

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)

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)

undefined

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

Repo.all(query)

undefined

Inserting and Updating

插入与更新

elixir

undefined

elixir

undefined

Insert with changeset

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

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

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()

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)

undefined

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

undefined

Transactions

事务

elixir

undefined

elixir

undefined

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()

undefined

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()

undefined

Embedded Schemas

嵌入Schema

elixir

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

elixir

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

自定义Ecto类型

elixir

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

elixir

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

undefined

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

undefined

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

当你需要以下操作时，使用elixir-ecto-patterns：

构建基于数据库的Elixir应用
定义带验证的Schema和数据模型
使用Ecto的DSL编写复杂数据库查询
管理数据库关系与关联
用Changeset处理数据转换
实现事务以保证数据一致性
操作PostgreSQL、MySQL或其他数据库
构建带数据库访问的Phoenix应用
创建健壮的数据验证层

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

始终使用Changeset进行数据验证
预加载关联以避免N+1查询问题
多步骤数据库操作使用事务
复杂事务逻辑使用Ecto.Multi
保持Schema聚焦，避免全能对象
虚拟字段用于计算或临时数据
为外键和频繁查询的字段创建索引
需要时使用fragments处理复杂SQL
编写可组合的查询函数
测试数据库约束与验证

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

未预加载关联（N+1查询问题）
忘记验证必填字段
关联操作未使用事务
硬编码查询而非组合查询
忽略Schema中的数据库约束
未正确处理Changeset错误
对关系型数据过度使用嵌入Schema
外键缺少索引
多步骤操作未使用Repo.transaction
业务逻辑中暴露原始Ecto查询

elixir-ecto-patterns

Original

Translation

Elixir Ecto Patterns

Elixir Ecto 模式

Schema Definition

Schema 定义

Changeset Validations

Changeset 验证

Basic Queries

基础查询

Get all users

Get all users

Get user by ID

Get user by ID

Get by specific field

Get by specific field

Filter with where clause

Filter with where clause

Select specific fields

Select specific fields

Order results

Order results

Limit and offset

Limit and offset

Complex Queries

复杂查询

Combining multiple conditions

Combining multiple conditions

Using pipe syntax

Using pipe syntax

Dynamic queries

Dynamic queries

Associations and Preloading

关联与预加载

Preload associations

Preload associations

Preload nested associations

Preload nested associations

Query with preload

Query with preload

Custom preload query

Custom preload query

Join and preload

Join and preload

Aggregations and Grouping

聚合与分组

Count records

Count records

Count with condition

Count with condition

Other aggregations

Other aggregations

Group by

Group by

Group with having

Group with having

Inserting and Updating

插入与更新

Insert with changeset

Insert with changeset

Insert without changeset

Insert without changeset

Update

Update

Update all

Update all

Delete

Delete

Delete all

Delete all

Transactions

事务

Basic transaction

Basic transaction

Multi for complex transactions

Multi for complex transactions

Embedded Schemas

嵌入Schema

Custom Ecto Types