Phoenix Ecto 1.3

Phoenix Ecto 1.3 integrates Ecto 2.x database wrapper with Phoenix 1.3 web framework. Provides repository pattern, schema definitions, changesets, and query DSL.

# mix.exs
def deps do
  [
    {:phoenix, "~> 1.3.0"},
    {:phoenix_ecto, "~> 1.3"},
    {:ecto, "~> 2.2"},
    {:postgrex, ">= 0.0.0"}  # or {:mariaex, ">= 0.0.0"}
  ]
end

# Define schema
defmodule MyApp.Accounts.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    field :age, :integer
    timestamps()
  end
end

# Query data
import Ecto.Query
alias MyApp.Repo

Repo.all(from u in User, where: u.age > 18)

# config/config.exs
config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "postgres",
  password: "postgres",
  database: "my_app_dev",
  hostname: "localhost",
  pool_size: 10

# config/dev.exs
config :my_app, MyApp.Repo,
  database: "my_app_dev",
  pool_size: 10

# config/test.exs
config :my_app, MyApp.Repo,
  database: "my_app_test",
  pool: Ecto.Adapters.SQL.Sandbox

# config/prod.secret.exs
config :my_app, MyApp.Repo,
  url: System.get_env("DATABASE_URL"),
  pool_size: 20

# lib/my_app/repo.ex
defmodule MyApp.Repo do
  use Ecto.Repo, otp_app: :my_app

  @doc """
  Dynamically loads the repository url from the
  DATABASE_URL environment variable.
  """
  def init(_, opts) do
    {:ok, Keyword.put(opts, :url, System.get_env("DATABASE_URL"))}
  end
end

# lib/my_app/application.ex
defmodule MyApp.Application do
  use Application

  def start(_type, _args) do
    children = [
      # Start the Ecto repository
      MyApp.Repo,
      # Start the endpoint
      MyAppWeb.Endpoint
    ]

    opts = [strategy: :one_for_one, name: MyApp.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

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

  schema "users" do
    field :name, :string
    field :email, :string
    field :age, :integer
    field :bio, :string
    field :admin, :boolean, default: false

    has_many :posts, MyApp.Blog.Post
    belongs_to :company, MyApp.Accounts.Company

    timestamps()  # Adds inserted_at and updated_at
  end
end

def changeset(user, attrs) do
  user
  |> cast(attrs, [:name, :email, :age, :bio])
  |> validate_required([:name, :email])
  |> validate_length(:name, min: 2, max: 100)
  |> validate_format(:email, ~r/@/)
  |> validate_number(:age, greater_than: 0)
  |> unique_constraint(:email)
end

# Required fields
|> validate_required([:name, :email])

# Length validation
|> validate_length(:name, min: 2, max: 100)
|> validate_length(:bio, max: 1000)

# Format validation (regex)
|> validate_format(:email, ~r/@/)

# Number validation
|> validate_number(:age, greater_than: 0)
|> validate_number(:age, less_than: 150)

# Inclusion validation
|> validate_inclusion(:role, ["admin", "user", "moderator"])

# Exclusion validation
|> validate_exclusion(:username, ["admin", "root"])

# Confirmation validation (password confirmation)
|> validate_confirmation(:password)

# Acceptance validation (terms of service)
|> validate_acceptance(:terms_of_service)

# Unique constraint
|> unique_constraint(:email)
|> unique_constraint(:username, name: :users_username_index)

# Foreign key constraint
|> foreign_key_constraint(:company_id)

# Check constraint
|> check_constraint(:age, name: :age_must_be_positive)

# No assoc constraint (prevents delete if associations exist)
|> no_assoc_constraint(:posts)

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

# With struct
user = %User{name: "John", email: "john@example.com"}
Repo.insert(user)

# With changeset
changeset = User.changeset(%User{}, params)
Repo.insert(changeset)

# Insert all (bulk insert)
users = [
  %{name: "Alice", email: "alice@example.com"},
  %{name: "Bob", email: "bob@example.com"}
]
Repo.insert_all(User, users)

# Update with changeset
user = Repo.get(User, 1)
changeset = User.changeset(user, %{name: "Jane"})
Repo.update(changeset)

# Update all (bulk update)
from(u in User, where: u.age < 18)
|> Repo.update_all(set: [minor: true])

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

# Delete with changeset
changeset = User.changeset(user, %{})
Repo.delete(changeset)

# Delete all (bulk delete)
from(u in User, where: u.active == false)
|> Repo.delete_all()

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

# Get by ID (returns nil if not found)
Repo.get(User, 1)

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

# Fetch all
Repo.all(User)

# Fetch one
Repo.one(from u in User, where: u.id == 1)

# All records
query = from u in User, where: u.active == true
Repo.all(query)

# One record (raises if not exactly one)
Repo.one!(query)

# Aggregate functions
Repo.aggregate(User, :count, :id)
Repo.aggregate(User, :avg, :age)
Repo.aggregate(User, :sum, :points)

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

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

# Nested preloading
user = Repo.get(User, 1) |> Repo.preload([posts: :comments])

# Preload with query
posts_query = from p in Post, where: p.published == true
user = Repo.get(User, 1) |> Repo.preload(posts: posts_query)

import Ecto.Query
alias MyApp.Repo

# Keyword syntax
query = from u in User,
        where: u.age > 18,
        select: u

Repo.all(query)

# Pipe syntax
User
|> where([u], u.age > 18)
|> select([u], u)
|> Repo.all()

# Simple where
from u in User, where: u.age > 18

# Multiple conditions (AND)
from u in User, where: u.age > 18 and u.active == true

# OR conditions
from u in User, where: u.admin == true or u.moderator == true

# IN operator
from u in User, where: u.role in ["admin", "moderator"]

# LIKE operator
from u in User, where: like(u.name, "%John%")

# IS NULL
from u in User, where: is_nil(u.deleted_at)

# Fragment for raw SQL
from u in User, where: fragment("lower(?)", u.email) == "john@example.com"

# Select entire struct
from u in User, select: u

# Select specific fields
from u in User, select: {u.id, u.name}

# Select map
from u in User, select: %{id: u.id, name: u.name}

# Select struct
from u in User, select: %User{id: u.id, name: u.name}

# Select with computed fields
from u in User, select: %{
  id: u.id,
  full_name: fragment("? || ' ' || ?", u.first_name, u.last_name)
}

# Order by
from u in User, order_by: u.name
from u in User, order_by: [desc: u.inserted_at]

# Multiple order by
from u in User, order_by: [u.last_name, u.first_name]

# Limit and offset
from u in User, limit: 10
from u in User, limit: 10, offset: 20

# First and last
User |> first() |> Repo.one()
User |> last() |> Repo.one()

# Inner join
from u in User,
  join: p in Post, on: p.user_id == u.id,
  select: {u, p}

# Left join
from u in User,
  left_join: p in Post, on: p.user_id == u.id,
  select: {u, p}

# Join with associations
from u in User,
  join: p in assoc(u, :posts),
  select: {u, p}

# Multiple joins
from u in User,
  join: p in assoc(u, :posts),
  join: c in assoc(p, :comments),
  select: {u, p, c}

# Count
from u in User, select: count(u.id)

# Sum
from o in Order, select: sum(o.amount)

# Average
from u in User, select: avg(u.age)

# Min and Max
from p in Product, select: {min(p.price), max(p.price)}

# Group by
from o in Order,
  group_by: o.user_id,
  select: {o.user_id, sum(o.amount)}

def filter_users(params) do
  User
  |> apply_filters(params)
  |> Repo.all()
end

defp apply_filters(query, params) do
  query
  |> filter_by_name(params["name"])
  |> filter_by_age(params["age"])
end

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

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

# Schema definition
defmodule MyApp.Accounts.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    has_many :posts, MyApp.Blog.Post
    timestamps()
  end
end

defmodule MyApp.Blog.Post do
  use Ecto.Schema

  schema "posts" do
    field :title, :string
    belongs_to :user, MyApp.Accounts.User
    timestamps()
  end
end

# Foreign key defaults to :user_id
belongs_to :user, MyApp.Accounts.User

# Custom foreign key
belongs_to :author, MyApp.Accounts.User, foreign_key: :author_id

# With on_replace option
belongs_to :company, MyApp.Accounts.Company, on_replace: :nilify

# Schema definition
defmodule MyApp.Blog.Post do
  use Ecto.Schema

  schema "posts" do
    field :title, :string
    many_to_many :tags, MyApp.Blog.Tag, join_through: "posts_tags"
    timestamps()
  end
end

defmodule MyApp.Blog.Tag do
  use Ecto.Schema

  schema "tags" do
    field :name, :string
    many_to_many :posts, MyApp.Blog.Post, join_through: "posts_tags"
    timestamps()
  end
end

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

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

# Nested preloading
user = Repo.get(User, 1)
  |> Repo.preload([posts: [:comments, :tags]])

# Preload with custom query
posts_query = from p in Post, where: p.published == true, order_by: [desc: p.inserted_at]
user = Repo.get(User, 1) |> Repo.preload(posts: posts_query)

# Preload in query
from u in User,
  where: u.id == ^user_id,
  preload: [:posts, :comments]

# Build association (not persisted)
user = Repo.get(User, 1)
post = Ecto.build_assoc(user, :posts, %{title: "Hello"})

# Insert with association
changeset = User.changeset(%User{}, user_params)
  |> Ecto.Changeset.put_assoc(:posts, [post1, post2])
Repo.insert(changeset)

# Cast association
def changeset(user, attrs) do
  user
  |> cast(attrs, [:name, :email])
  |> cast_assoc(:posts)
end

defmodule MyAppWeb.UserController do
  use MyAppWeb, :controller
  alias MyApp.Accounts
  alias MyApp.Accounts.User

  def new(conn, _params) do
    changeset = Accounts.change_user(%User{})
    render(conn, "new.html", changeset: changeset)
  end

  def create(conn, %{"user" => user_params}) do
    case Accounts.create_user(user_params) do
      {:ok, user} ->
        conn
        |> put_flash(:info, "User created successfully.")
        |> redirect(to: Routes.user_path(conn, :show, user))

      {:error, %Ecto.Changeset{} = changeset} ->
        render(conn, "new.html", changeset: changeset)
    end
  end

  def edit(conn, %{"id" => id}) do
    user = Accounts.get_user!(id)
    changeset = Accounts.change_user(user)
    render(conn, "edit.html", user: user, changeset: changeset)
  end

  def update(conn, %{"id" => id, "user" => user_params}) do
    user = Accounts.get_user!(id)

    case Accounts.update_user(user, user_params) do
      {:ok, user} ->
        conn
        |> put_flash(:info, "User updated successfully.")
        |> redirect(to: Routes.user_path(conn, :show, user))

      {:error, %Ecto.Changeset{} = changeset} ->
        render(conn, "edit.html", user: user, changeset: changeset)
    end
  end
end

defmodule MyApp.Accounts do
  @moduledoc """
  The Accounts context.
  """

  import Ecto.Query, warn: false
  alias MyApp.Repo
  alias MyApp.Accounts.User

  def list_users do
    Repo.all(User)
  end

  def get_user!(id), do: Repo.get!(User, id)

  def create_user(attrs \\ %{}) do
    %User{}
    |> User.changeset(attrs)
    |> Repo.insert()
  end

  def update_user(%User{} = user, attrs) do
    user
    |> User.changeset(attrs)
    |> Repo.update()
  end

  def delete_user(%User{} = user) do
    Repo.delete(user)
  end

  def change_user(%User{} = user) do
    User.changeset(user, %{})
  end
end

# lib/my_app_web/templates/user/form.html.eex
<%= form_for @changeset, @action, fn f -> %>
  <%= if @changeset.action do %>
    <div class="alert alert-danger">
      <p>Oops, something went wrong! Please check the errors below.</p>
    </div>
  <% end %>

  <%= label f, :name %>
  <%= text_input f, :name %>
  <%= error_tag f, :name %>

  <%= label f, :email %>
  <%= email_input f, :email %>
  <%= error_tag f, :email %>

  <%= label f, :age %>
  <%= number_input f, :age %>
  <%= error_tag f, :age %>

  <%= submit "Submit" %>
<% end %>

# lib/my_app_web/views/error_helpers.ex
defmodule MyAppWeb.ErrorHelpers do
  use Phoenix.HTML

  def error_tag(form, field) do
    Enum.map(Keyword.get_values(form.errors, field), fn error ->
      content_tag(:span, translate_error(error), class: "help-block")
    end)
  end

  def translate_error({msg, opts}) do
    Enum.reduce(opts, msg, fn {key, value}, acc ->
      String.replace(acc, "%{#{key}}", to_string(value))
    end)
  end
end

# config/test.exs
config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "postgres",
  password: "postgres",
  database: "my_app_test",
  hostname: "localhost",
  pool: Ecto.Adapters.SQL.Sandbox

# test/test_helper.exs
ExUnit.start()

Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, :manual)

# test/support/data_case.ex
defmodule MyApp.DataCase do
  use ExUnit.CaseTemplate

  using do
    quote do
      alias MyApp.Repo
      import Ecto
      import Ecto.Changeset
      import Ecto.Query
      import MyApp.DataCase
    end
  end

  setup tags do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo)

    unless tags[:async] do
      Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, {:shared, self()})
    end

    :ok
  end
end

# test/support/conn_case.ex
defmodule MyAppWeb.ConnCase do
  use ExUnit.CaseTemplate

  using do
    quote do
      use Phoenix.ConnTest
      alias MyAppWeb.Router.Helpers, as: Routes
      @endpoint MyAppWeb.Endpoint
    end
  end

  setup tags do
    :ok = Ecto.Adapters.SQL.Sandbox.checkout(MyApp.Repo)

    unless tags[:async] do
      Ecto.Adapters.SQL.Sandbox.mode(MyApp.Repo, {:shared, self()})
    end

    {:ok, conn: Phoenix.ConnTest.build_conn()}
  end
end

defmodule MyApp.AccountsTest do
  use MyApp.DataCase
  alias MyApp.Accounts

  describe "users" do
    @valid_attrs %{name: "John Doe", email: "john@example.com", age: 30}
    @invalid_attrs %{name: nil, email: nil}

    test "list_users/0 returns all users" do
      user = user_fixture()
      assert Accounts.list_users() == [user]
    end

    test "create_user/1 with valid data creates a user" do
      assert {:ok, user} = Accounts.create_user(@valid_attrs)
      assert user.name == "John Doe"
      assert user.email == "john@example.com"
    end

    test "create_user/1 with invalid data returns error changeset" do
      assert {:error, %Ecto.Changeset{}} = Accounts.create_user(@invalid_attrs)
    end
  end

  defp user_fixture(attrs \\ %{}) do
    {:ok, user} =
      attrs
      |> Enum.into(@valid_attrs)
      |> Accounts.create_user()

    user
  end
end

defmodule MyAppWeb.UserControllerTest do
  use MyAppWeb.ConnCase
  alias MyApp.Accounts

  @create_attrs %{name: "John Doe", email: "john@example.com", age: 30}
  @invalid_attrs %{name: nil, email: nil}

  test "renders form for new user", %{conn: conn} do
    conn = get(conn, Routes.user_path(conn, :new))
    assert html_response(conn, 200) =~ "New User"
  end

  test "creates user when data is valid", %{conn: conn} do
    conn = post(conn, Routes.user_path(conn, :create), user: @create_attrs)
    assert %{id: id} = redirected_params(conn)
    assert redirected_to(conn) == Routes.user_path(conn, :show, id)

    conn = get(conn, Routes.user_path(conn, :show, id))
    assert html_response(conn, 200) =~ "John Doe"
  end

  test "renders errors when data is invalid", %{conn: conn} do
    conn = post(conn, Routes.user_path(conn, :create), user: @invalid_attrs)
    assert html_response(conn, 200) =~ "New User"
  end
end

Repo.transaction(fn ->
  user = Repo.insert!(%User{name: "John"})
  Repo.insert!(%Post{title: "Hello", user_id: user.id})
end)

# Returns {:ok, result} or {:error, reason}

Repo.transaction(fn ->
  user = Repo.insert!(%User{name: "John"})

  if some_condition do
    Repo.rollback(:some_reason)
  end

  Repo.insert!(%Post{title: "Hello", user_id: user.id})
end)

alias Ecto.Multi

Multi.new()
|> Multi.insert(:user, User.changeset(%User{}, user_params))
|> Multi.run(:post, fn %{user: user} ->
  %Post{user_id: user.id}
  |> Post.changeset(post_params)
  |> Repo.insert()
end)
|> Multi.run(:notification, fn %{user: user, post: post} ->
  send_notification(user, post)
  {:ok, :sent}
end)
|> Repo.transaction()

# Returns {:ok, %{user: user, post: post, notification: :sent}}
# or {:error, :user, changeset, %{}}

case Repo.transaction(multi) do
  {:ok, %{user: user, post: post}} ->
    {:ok, user, post}

  {:error, :user, changeset, _} ->
    {:error, :user_creation_failed, changeset}

  {:error, :post, changeset, %{user: user}} ->
    {:error, :post_creation_failed, changeset}
end

# Note: Ecto 2.x uses savepoints for nested transactions
Repo.transaction(fn ->
  user = Repo.insert!(%User{name: "John"})

  Repo.transaction(fn ->
    Repo.insert!(%Post{title: "Hello", user_id: user.id})
  end)

  user
end)

# Create database
mix ecto.create

# Drop database
mix ecto.drop

# Migrate database
mix ecto.migrate

# Rollback migration
mix ecto.rollback

# Reset database (drop, create, migrate)
mix ecto.reset

# Check migration status
mix ecto.migrations

# Generate migration
mix ecto.gen.migration create_users

# Rollback last migration
mix ecto.rollback

# Rollback to specific version
mix ecto.rollback --to 20170101000000

# Rollback N migrations
mix ecto.rollback --step 3

# Generate schema and migration
mix phx.gen.schema Accounts.User users name:string email:string:unique age:integer

# Generate context with CRUD
mix phx.gen.context Accounts User users name:string email:string age:integer

# Generate JSON API
mix phx.gen.json Accounts User users name:string email:string age:integer

# Generate HTML scaffold
mix phx.gen.html Accounts User users name:string email:string age:integer

# priv/repo/seeds.exs
alias MyApp.Repo
alias MyApp.Accounts.User

Repo.insert!(%User{
  name: "John Doe",
  email: "john@example.com",
  age: 30
})

# Run seeds
# mix run priv/repo/seeds.exs

# lib/mix/tasks/my_app.some_task.ex
defmodule Mix.Tasks.MyApp.SomeTask do
  use Mix.Task
  alias MyApp.Repo

  @shortdoc "Description of task"
  def run(_) do
    Mix.Task.run("app.start")
    # Your code here
  end
end

# ❌ WRONG - changeset not persisted
changeset = User.changeset(%User{}, params)
# User not saved!

# ✅ CORRECT - insert changeset
{:ok, user} = Repo.insert(changeset)

# ⚠️ N+1 Query Problem
users = Repo.all(User)
for user <- users do
  user.posts  # Triggers separate query for each user!
end

# ✅ CORRECT - Preload to avoid N+1
users = Repo.all(User) |> Repo.preload(:posts)
for user <- users do
  user.posts  # Already loaded!
end

# ✅ CORRECT - Preload in query
users = from(u in User, preload: [:posts]) |> Repo.all()

# ❌ WRONG - Structs don't validate
Repo.insert(%User{name: "x", email: "invalid"})

# ✅ CORRECT - Use changeset for validation
%User{}
|> User.changeset(%{name: "x", email: "invalid"})
|> Repo.insert()

# ❌ WRONG - Can't update through parent
user = Repo.get(User, 1) |> Repo.preload(:posts)
user.posts = [new_post]
Repo.update(user)  # Does nothing!

# ✅ CORRECT - Use put_assoc or cast_assoc
changeset = User.changeset(user, %{})
  |> Ecto.Changeset.put_assoc(:posts, [new_post])
Repo.update(changeset)

# ❌ WRONG - Exceptions don't rollback
Repo.transaction(fn ->
  Repo.insert!(%User{name: "John"})
  raise "Error!"  # Transaction committed before raise!
end)

# ✅ CORRECT - Use Repo.rollback
Repo.transaction(fn ->
  Repo.insert!(%User{name: "John"})
  if error_condition do
    Repo.rollback(:error_reason)
  end
end)

# ❌ WRONG - Constraint doesn't exist in DB
def changeset(user, attrs) do
  user
  |> cast(attrs, [:email])
  |> unique_constraint(:email)  # Will raise if no DB constraint!
end

# ✅ CORRECT - Create index in migration first
# priv/repo/migrations/xxx_create_users.exs
create unique_index(:users, [:email])

# cast - For external, untrusted data (user input)
def changeset(user, attrs) do
  user
  |> cast(attrs, [:name, :email])  # Only allow specific fields
end

# put_change - For internal, trusted data
def internal_update(changeset) do
  changeset
  |> put_change(:admin, true)  # Set internal field
end

# ❌ WRONG - timestamps() not called
schema "users" do
  field :name, :string
  # Missing timestamps()!
end

# ✅ CORRECT - Call timestamps() in schema
schema "users" do
  field :name, :string
  timestamps()  # Adds inserted_at and updated_at
end