ecto-database

Define schemas with proper types and associations.

```elixir

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

```

Always use changesets for data validation and casting.

```elixir

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

```

Build queries composably using Ecto.Query.

```elixir

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

```

Use preload to avoid N+1 queries.

Bad:

```elixir

images = Repo.all(Image)

# Later accessing image.folder causes N queries

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

```

Good:

```elixir

images =

Image

|> preload(:folder)

|> Repo.all()

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

```

Use Repo.transaction for operations that must succeed together.

```elixir

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

```

Use Repo.insert and Repo.update with changesets.

```elixir

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

```

Use on_conflict for upsert behavior.

```elixir

def create_or_update_folder(attrs) do

%Folder{}

|> Folder.changeset(attrs)

|> Repo.insert(

on_conflict: {:replace, [:name, :updated_at]},

conflict_target: :name

)

end

```

Define associations properly in schemas.

```elixir

# 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

```

Use Ecto.build_assoc to create associated records.

```elixir

def add_image_to_folder(folder, image_attrs) do

folder

|> Ecto.build_assoc(:images)

|> Image.changeset(image_attrs)

|> Repo.insert()

end

```

Use cast_assoc when working with nested data.

```elixir

def changeset(folder, attrs) do

folder

|> cast(attrs, [:name])

|> cast_assoc(:images, with: &Image.changeset/2)

|> validate_required([:name])

end

```

Build queries dynamically based on filters.

```elixir

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

```

Use aggregation functions for statistics.

```elixir

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

```

Common Repo operations:

```elixir

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

```

Write clear, reversible migrations.

```elixir

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

```

Add unique constraints in schema and migration.

```elixir

# Migration

create unique_index(:folders, [:name])

# Schema changeset

def changeset(folder, attrs) do

folder

|> cast(attrs, [:name])

|> validate_required([:name])

|> unique_constraint(:name)

end

```

Use virtual fields for computed or temporary data.

```elixir

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

```

Define custom Ecto types for special data.

```elixir

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

```

Organize database operations in contexts.

```elixir

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

```