Ecto Database Patterns

Schema Definition

Define schemas with proper types and associations.

defmodule MyApp.Media.Image do use Ecto.Schema import Ecto.Changeset

schema "images" do field :title, :string field :description, :string field :filename, :string field :file_path, :string field :content_type, :string field :file_size, :integer

belongs_to :folder, MyApp.Media.Folder

timestamps()

end end

Changesets

Always use changesets for data validation and casting.

def changeset(image, attrs) do image |> cast(attrs, [:title, :description, :filename, :file_path, :content_type, :file_size, :folder_id]) |> validate_required([:title, :filename, :file_path, :content_type, :file_size]) |> validate_length(:title, min: 1, max: 255) |> validate_inclusion(:content_type, ["image/jpeg", "image/png", "image/gif"]) |> validate_number(:file_size, greater_than: 0, less_than: 10_000_000) |> foreign_key_constraint(:folder_id) end

Query Composition

Build queries composably using Ecto.Query .

import Ecto.Query

def list_images_by_folder(folder_id) do Image |> where([i], i.folder_id == ^folder_id) |> order_by([i], desc: i.inserted_at) |> Repo.all() end

def search_images(query_string) do search = "%#{query_string}%"

Image |> where([i], ilike(i.title, ^search) or ilike(i.description, ^search)) |> Repo.all() end

Preloading Associations

Use preload to avoid N+1 queries.

Bad:

images = Repo.all(Image)

Later accessing image.folder causes N queries

Enum.each(images, fn image -> image.folder.name end)

Good:

images = Image |> preload(:folder) |> Repo.all()

Enum.each(images, fn image -> image.folder.name end)

Transactions

Use Repo.transaction for operations that must succeed together.

def transfer_images(image_ids, from_folder_id, to_folder_id) do Repo.transaction(fn -> with {:ok, from_folder} <- get_folder(from_folder_id), {:ok, to_folder} <- get_folder(to_folder_id), {count, nil} <- update_images(image_ids, to_folder_id) do {:ok, count} else {:error, reason} -> Repo.rollback(reason) _ -> Repo.rollback(:unknown_error) end end) end

Insert and Update

Use Repo.insert and Repo.update with changesets.

def create_image(attrs) do %Image{} |> Image.changeset(attrs) |> Repo.insert() end

def update_image(%Image{} = image, attrs) do image |> Image.changeset(attrs) |> Repo.update() end

Upsert Operations

Use on_conflict for upsert behavior.

def create_or_update_folder(attrs) do %Folder{} |> Folder.changeset(attrs) |> Repo.insert( on_conflict: {:replace, [:name, :updated_at]}, conflict_target: :name ) end

Associations

Define associations properly in schemas.

Parent schema

defmodule MyApp.Media.Folder do use Ecto.Schema

schema "folders" do field :name, :string has_many :images, MyApp.Media.Image

timestamps()

end end

Child schema

defmodule MyApp.Media.Image do use Ecto.Schema

schema "images" do field :title, :string belongs_to :folder, MyApp.Media.Folder

timestamps()

end end

Building Associations

Use Ecto.build_assoc to create associated records.

def add_image_to_folder(folder, image_attrs) do folder |> Ecto.build_assoc(:images) |> Image.changeset(image_attrs) |> Repo.insert() end

Casting Associations

Use cast_assoc when working with nested data.

def changeset(folder, attrs) do folder |> cast(attrs, [:name]) |> cast_assoc(:images, with: &Image.changeset/2) |> validate_required([:name]) end

Dynamic Queries

Build queries dynamically based on filters.

def list_images(filters) do Image |> apply_filters(filters) |> Repo.all() end

defp apply_filters(query, filters) do Enum.reduce(filters, query, fn {:folder_id, folder_id}, query -> where(query, [i], i.folder_id == ^folder_id)

{:search, term}, query ->
  where(query, [i], ilike(i.title, ^"%#{term}%"))

{:min_size, size}, query ->
  where(query, [i], i.file_size >= ^size)

_, query ->
  query

end) end

Aggregations

Use aggregation functions for statistics.

def count_images_by_folder do Image |> group_by([i], i.folder_id) |> select([i], {i.folder_id, count(i.id)}) |> Repo.all() |> Map.new() end

def total_storage_used do Image |> select([i], sum(i.file_size)) |> Repo.one() end

Repo Functions

Common Repo operations:

Fetch single record

Repo.get(Image, id) # Returns record or nil Repo.get!(Image, id) # Returns record or raises Repo.get_by(Image, title: "Photo")

Fetch all records

Repo.all(Image)

Insert

Repo.insert(changeset) # Returns {:ok, record} or {:error, changeset} Repo.insert!(changeset) # Returns record or raises

Update

Repo.update(changeset) Repo.update!(changeset)

Delete

Repo.delete(record) Repo.delete!(record)

Delete all matching

Repo.delete_all(Image) Repo.delete_all(where(Image, [i], i.folder_id == ^folder_id))

Migrations

Write clear, reversible migrations.

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

def change do create table(:images) do add :title, :string, null: false add :description, :text add :filename, :string, null: false add :file_path, :string, null: false add :content_type, :string, null: false add :file_size, :integer, null: false add :folder_id, references(:folders, on_delete: :nilify_all)

  timestamps()
end

create index(:images, [:folder_id])
create index(:images, [:inserted_at])

end end

Unique Constraints

Add unique constraints in schema and migration.

Migration

create unique_index(:folders, [:name])

Schema changeset

def changeset(folder, attrs) do folder |> cast(attrs, [:name]) |> validate_required([:name]) |> unique_constraint(:name) end

Virtual Fields

Use virtual fields for computed or temporary data.

schema "images" do field :title, :string field :file_path, :string field :url, :string, virtual: true

timestamps() end

def with_url(%Image{} = image) do %{image | url: "/uploads/#{Path.basename(image.file_path)}"} end

Custom Types

Define custom Ecto types for special data.

defmodule MyApp.FileSize do use Ecto.Type

def type, do: :integer

def cast(size) when is_integer(size) and size >= 0, do: {:ok, size} def cast(_), do: :error

def load(size), do: {:ok, size} def dump(size), do: {:ok, size} end

Context Pattern

Organize database operations in contexts.

defmodule MyApp.Media do alias MyApp.Media.{Image, Folder} alias MyApp.Repo

def list_images, do: Repo.all(Image)

def get_image!(id), do: Repo.get!(Image, id)

def create_image(attrs) do %Image{} |> Image.changeset(attrs) |> Repo.insert() end

def update_image(%Image{} = image, attrs) do image |> Image.changeset(attrs) |> Repo.update() end

def delete_image(%Image{} = image) do Repo.delete(image) end end