Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 48 ยท Real-World Elixir

Ecto: Databases and Changesets

Learn Ecto, Elixir's database toolkit: repos, schemas, changesets for validation, composable queries, migrations and Ecto.Multi transactions.

Most applications need a database, and in Elixir the standard toolkit is Ecto. Ecto is not an ORM in the object-oriented sense: there are no objects with hidden state that save themselves. Instead you have plain structs for rows, a Repo module that talks to the database, changesets that validate and track changes, and a query language written as Elixir code.

This lesson is a tour of the five main parts: repo, schemas, changesets, queries and migrations, plus Ecto.Multi for transactions. It assumes you know maps and structs and error handling.

Ecto needs a database and Hex packages, and Elixir cannot run on this site, so the Elixir snippets here are read-only. The JavaScript TryIts are stand-ins: a changeset-style validator and a transaction that rolls back. Ecto itself is a library you add to a Mix project (ecto_sql plus a database adapter such as postgrex).

The five parts of Ecto

Repo Talks to the database.
Schema Maps a table to a struct.
Changeset Casts and validates changes.
Query Composable, checked at compile time.
1 / 2

The Repo

A repo is a module that wraps a database connection pool. You create it with use Ecto.Repo:

ELIXIR
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres
end

You configure the connection (database name, user, pool size) in your config files and add MyApp.Repo to the supervision tree so the pool starts with your application. Every database call goes through it: Repo.insert/2, Repo.get/2, Repo.all/1, Repo.update/2, Repo.delete/2.

Schemas

A schema describes how a table maps to a struct:

ELIXIR
defmodule MyApp.Accounts.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    field :email, :string
    field :age, :integer
    field :active, :boolean, default: true

    has_many :posts, MyApp.Blog.Post

    timestamps()
  end
end

This defines a %User{} struct with an id primary key (by default), your fields and inserted_at/updated_at. Reading from the database gives you that struct, and data is immutable like everything else: you never "set an attribute and save"; you create a changeset describing the change and hand it to the repo.

Changesets

A changeset is a struct that holds the original data, the proposed changes, the validation errors and whether it is valid. It is the one place where you decide what outside input may do.

ELIXIR
def changeset(user, attrs) do
  user
  |> Ecto.Changeset.cast(attrs, [:name, :email, :age])
  |> Ecto.Changeset.validate_required([:name, :email])
  |> Ecto.Changeset.validate_length(:name, min: 2, max: 80)
  |> Ecto.Changeset.validate_format(:email, ~r/@/)
  |> Ecto.Changeset.validate_number(:age, greater_than_or_equal_to: 0)
  |> Ecto.Changeset.unique_constraint(:email)
end

Read it as a pipeline:

  • cast/3 takes the raw input (usually string keys from a form or JSON), keeps only the permitted fields and converts types, so "42" becomes 42. A field not in the list is ignored, which protects you from users setting is_admin.
  • validate_* functions add errors to the changeset and never raise.
  • unique_constraint/2 turns a database unique-index violation into a normal changeset error instead of an exception.

Using it:

ELIXIR
changeset = User.changeset(%User{}, %{"name" => "A", "email" => "nope"})
changeset.valid?
# false

changeset.errors
# [email: {"has invalid format", [validation: :format]},
#  name: {"should be at least %{count} character(s)", [count: 2, validation: :length, kind: :min, type: :string]}]

case Repo.insert(changeset) do
  {:ok, user} -> {:ok, user}
  {:error, changeset} -> {:error, changeset}
end

Repo.insert/2 returns {:ok, struct} or {:error, changeset}, and an invalid changeset is rejected before any SQL is sent. Different operations can use different changeset functions, for example registration_changeset/2 and profile_changeset/2 with different permitted fields.

A changeset in JavaScript

This stand-in mimics cast, validate_required and validate_format: it keeps allowed fields only, collects errors and never throws.

JS A tiny changeset

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Queries

Ecto queries are written with the from macro and are composed from small pieces. Values from outside must be pinned with ^, which turns them into safe query parameters and prevents SQL injection.

ELIXIR
import Ecto.Query

adults =
  from u in User,
    where: u.age >= 18 and u.active == true,
    order_by: [asc: u.name],
    select: %{id: u.id, name: u.name}

Repo.all(adults)
ELIXIR
min_age = 21

User
|> where([u], u.age >= ^min_age)
|> order_by([u], desc: u.inserted_at)
|> limit(10)
|> Repo.all()

Because a query is just data until you pass it to the repo, you can build it in steps and reuse parts:

ELIXIR
def active(query), do: where(query, [u], u.active)
def older_than(query, age), do: where(query, [u], u.age > ^age)

User |> active() |> older_than(30) |> Repo.all()

Common repo reads: Repo.get(User, 1) (returns nil if missing), Repo.get!/2 (raises), Repo.get_by(User, email: "[email protected]"), Repo.one/1, Repo.aggregate(User, :count). Associations are not loaded automatically; ask for them with preload:

ELIXIR
from(u in User, preload: [:posts]) |> Repo.all()

Forgetting preloads and then reading user.posts raises an error; loading them one user at a time in a loop causes the N+1 problem covered in Performance and Common Pitfalls. Joins are also available: join: p in assoc(u, :posts).

Migrations

Migrations are versioned Elixir files that change the database layout. Generate one with mix ecto.gen.migration create_users, and then write it:

ELIXIR
defmodule MyApp.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :name, :string, null: false
      add :email, :string, null: false
      add :age, :integer
      add :active, :boolean, default: true, null: false

      timestamps()
    end

    create unique_index(:users, [:email])
  end
end

change/0 describes the forward step, and Ecto can reverse most operations itself for mix ecto.rollback. For steps it cannot reverse, write up/0 and down/0. The usual commands are mix ecto.create, mix ecto.migrate, mix ecto.rollback and mix ecto.reset. Commit migrations to version control and never edit one that has already run in a shared environment; add a new one instead.

Transactions with Ecto.Multi

When several database steps must succeed or fail together (create an account and its first project, move money between two rows), use Ecto.Multi. You name each step, and the whole sequence runs in one transaction:

ELIXIR
alias Ecto.Multi

Multi.new()
|> Multi.insert(:user, User.changeset(%User{}, attrs))
|> Multi.insert(:profile, fn %{user: user} ->
  Profile.changeset(%Profile{user_id: user.id}, %{})
end)
|> Multi.run(:welcome, fn _repo, %{user: user} -> Mailer.send_welcome(user) end)
|> Repo.transaction()

The result is either {:ok, results}, a map of step names to results, or {:error, failed_step, failed_value, changes_so_far}. If any step fails, everything is rolled back. Multi.run/3 lets you put any function in the chain; it must return {:ok, value} or {:error, value}. For one-off cases you can also use Repo.transaction(fn -> ... end) with Repo.rollback/1.

Rolling back in JavaScript

JS All steps succeed, or none do

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Contexts keep Ecto out of your web code

In Phoenix, queries and changesets are wrapped in context modules such as MyApp.Accounts, with functions like create_user/1 and list_active_users/0. The rest of the application never calls Repo directly, which keeps the data rules in one place. You will see this in Phoenix: Web Applications.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the purpose of Ecto.Changeset.cast/3?

    cast filters outside input to the listed fields and converts types, which also protects fields you did not permit.
  2. 2. What does Repo.insert/2 return for an invalid changeset?

    Invalid changesets are rejected before reaching the database and come back inside an {:error, changeset} tuple.
  3. 3. Why do you write where: u.age >= ^min_age in a query?

    Pinned values are sent as parameters, not pasted into SQL text, which prevents SQL injection.
  4. 4. When should you use Ecto.Multi?

    Multi names each step and runs them in a single transaction, rolling everything back if one fails.
  5. 5. Which is true about associations like user.posts?

    Ecto does not lazy-load. Accessing an association you did not preload raises an error, so preload explicitly.

Summary

  • A repo wraps the database pool; a schema maps a table to a struct.
  • Changesets cast permitted fields, validate them and carry errors; Repo functions return {:ok, _} or {:error, changeset}.
  • Queries are Elixir code built with from or pipes; pin outside values with ^; preload associations explicitly.
  • Migrations version the table layout (mix ecto.migrate, mix ecto.rollback).
  • Ecto.Multi groups steps in one transaction; wrap Ecto in context modules.

Next: serving data to browsers with Phoenix: Web Applications.