Elixir by Example

Ecto Basics

Every command ran when the site was built; the output is real Download project

Everything so far kept its state in process memory, and a restart lost it. Data that must survive goes in a database, and most databases speak SQL. Ecto is how Elixir programs talk to them. Rows come back as plain structs, every write is checked before it reaches the database, queries are Elixir code that you build up piece by piece, and migrations keep the tables' shape in version control. Ecto is not an ORM: a struct is just data, with no connection behind it, and nothing is loaded behind your back (Ecto doesn't lazy load). Every trip to the database is a call you can see.

This track uses SQLite through the ecto_sqlite3 adapter, so the whole database is one file and there's nothing to install. The code is the same on Postgres, apart from the adapter.

On this page:

  1. The four pieces: a repo, a migration, a schema and a changeset.
  2. Inserting rows, and reading why a write was refused.
  3. Reading, updating and deleting, and queries with from.

The project

A tiny library catalog with one table, books. The only dependency is the SQLite adapter, which brings in ecto (schemas, changesets, queries) and ecto_sql (migrations, SQL databases).

mix.exs
defmodule Library.MixProject do
  use Mix.Project

  def project do
    [app: :library, version: "0.1.0", elixir: "~> 1.20", deps: deps()]
  end

  def application do
    [mod: {Library.Application, []}]
  end

  defp deps do
    [{:ecto_sqlite3, "~> 0.26"}]
  end
end

ecto_repos is for the mix ecto.* tasks: it tells them which repos to create and migrate. The second line configures the repo itself.

config/config.exs
import Config

# Tells the `mix ecto.*` tasks which repos this app has.
config :library, ecto_repos: [Library.Repo]

# The repo's settings. `log: false` keeps query logs (with
# their timings) out of the output. SQLite allows one
# writer at a time, so one connection is enough.
config :library, Library.Repo, database: "library.db", log: false, pool_size: 1

The repo is your handle on one database. use Ecto.Repo gives the module insert, get, all, update, delete and the rest; otp_app: says where its config lives and adapter: which database it talks to.

lib/library/repo.ex
defmodule Library.Repo do
  use Ecto.Repo, otp_app: :library, adapter: Ecto.Adapters.SQLite3
end

A repo is also a process: it owns a pool of database connections, so it goes in the application's supervision tree like any other child.

lib/library/application.ex
defmodule Library.Application do
  use Application

  @impl true
  def start(_type, _args) do
    Supervisor.start_link([Library.Repo], strategy: :one_for_one, name: Library.Supervisor)
  end
end

A migration changes the database's shape. Its file name starts with a timestamp, and Ecto runs pending migrations in that order and records which ones ran, so every copy of the database ends up with the same tables.

priv/repo/migrations/20261108000000_create_books.exs
defmodule Library.Repo.Migrations.CreateBooks do
  use Ecto.Migration

  # `change` describes the new shape. Ecto works out how to
  # undo it too (`mix ecto.rollback`).
  def change do
    # `create table` adds an `id` primary key by itself.
    create table(:books) do
      add :title, :string, null: false
      add :author, :string, null: false
      add :pages, :integer
      # `inserted_at` and `updated_at`, filled in by Ecto.
      timestamps()
    end

    create unique_index(:books, [:title])
  end
end

A schema maps a table to a struct, and a changeset is a proposed change to one: cast/3 takes the allowed fields from outside data (a form, a JSON body) and converts them to the field types, then the validate_* functions check them. unique_constraint/2 checks nothing by itself: it tells Ecto that if the unique index refuses the write, return an error instead of raising.

lib/library/book.ex
defmodule Library.Book do
  use Ecto.Schema
  import Ecto.Changeset

  # Maps the `books` table to a `%Library.Book{}` struct.
  # `id` is added by default, as in the migration.
  schema "books" do
    field :title, :string
    field :author, :string
    field :pages, :integer
    timestamps()
  end

  # Every write goes through here: pick the fields an
  # outsider may set, convert them to the field types,
  # then validate.
  def changeset(book, attrs) do
    book
    |> cast(attrs, [:title, :author, :pages])
    |> validate_required([:title, :author])
    |> validate_length(:title, min: 2)
    |> validate_number(:pages, greater_than: 0)
    |> unique_constraint(:title)
  end
end

The Library module wraps the repo calls. alias Library.{Book, Repo} is two aliases in one line.

lib/library.ex
defmodule Library do
  import Ecto.Query
  alias Library.{Book, Repo}

  def add_book(attrs) do
    %Book{}
    |> Book.changeset(attrs)
    |> Repo.insert()
  end

  def set_pages(title, pages) do
    Repo.get_by!(Book, title: title)
    |> Book.changeset(%{pages: pages})
    |> Repo.update()
  end

  def remove(title), do: Repo.delete(Repo.get_by!(Book, title: title))

  # Keyword syntax: the whole query in one `from`.
  # `^author` puts an Elixir value into the query (as a
  # parameter, never pasted into the SQL text).
  def titles_by(author) do
    query =
      from b in Book,
        where: b.author == ^author,
        order_by: b.title,
        select: b.title

    Repo.all(query)
  end

  # Pipe syntax: the same building blocks as functions.
  # Each step takes a query and returns a bigger one.
  def shorter_than(pages) do
    Book
    |> where([b], b.pages < ^pages)
    |> order_by([b], asc: b.pages)
    |> select([b], {b.title, b.pages})
    |> Repo.all()
  end

  # Turns a changeset's errors into a map of readable
  # messages, filling in `%{count}` and friends.
  def errors(changeset) do
    Ecto.Changeset.traverse_errors(changeset, fn {message, opts} ->
      Enum.reduce(opts, message, fn {key, value}, acc ->
        String.replace(acc, "%{#{key}}", to_string(value))
      end)
    end)
  end
end

Set up

Fetching and compiling the dependencies prints progress and timings, so that output is hidden. ecto.create creates the database (here, the file library.db) and ecto.migrate runs the migration. Migrations open two connections by default; --pool-size 1 keeps it to one, because SQLite takes one writer at a time and two connections opening a fresh file can fail with "database is locked".

$ mix deps.get > /dev/null
$ mix deps.compile > /dev/null 2>&1
$ mix compile
$ mix ecto.create --quiet
$ mix ecto.migrate --quiet --pool-size 1
Compiling 4 files (.ex)
Generated library app

Inserting

A schema's struct is an ordinary struct, plus __meta__, where Ecto notes whether it came from the database (:built means not yet). Repo.insert/1 returns {:ok, book} with the id the database gave it. The second book arrives the way a web form would send it, with string keys and "264" as a string; cast/3 turns that into the integer 264.

$ mix run -e '
$ IO.inspect(%Library.Book{title: "Dune"})
$ {:ok, book} = Library.add_book(%{title: "Dune", author: "Frank Herbert", pages: 412})
$ IO.inspect({book.id, book.title, book.pages}, label: "inserted")
$ {:ok, book} = Library.add_book(%{"title" => "Kindred", "author" => "Octavia E. Butler", "pages" => "264"})
$ IO.inspect({book.id, book.title, book.pages}, label: "inserted")
$ {:ok, _} = Library.add_book(%{title: "The Dispossessed", author: "Ursula K. Le Guin", pages: 387})
$ {:ok, _} = Library.add_book(%{title: "A Wizard of Earthsea", author: "Ursula K. Le Guin", pages: 183})
$ '
%Library.Book{
  __meta__: #Ecto.Schema.Metadata<:built, "books">,
  id: nil,
  title: "Dune",
  author: nil,
  pages: nil,
  inserted_at: nil,
  updated_at: nil
}
inserted: {1, "Dune", 412}
inserted: {2, "Kindred", 264}

When a write is refused

A failed write returns {:error, changeset}, and nothing reaches the database. The changeset's errors hold each message with a template (%{count}) and its values; Ecto.Changeset.traverse_errors/2 (used in Library.errors/1) turns them into a map of readable messages.

The last two lines show the order of checks: a second "Dune" passes every validation, so it reaches the database and the unique index refuses it. With a bad page count as well, validation fails first and the database is never asked, so the duplicate title isn't reported.

$ mix run -e '
$ {:error, changeset} = Library.add_book(%{title: "X", pages: -5})
$ IO.inspect(changeset)
$ IO.inspect(Library.errors(changeset))
$ {:error, changeset} = Library.add_book(%{title: "Dune", author: "Someone Else"})
$ IO.inspect(Library.errors(changeset))
$ {:error, changeset} = Library.add_book(%{title: "Dune", author: "Someone Else", pages: -1})
$ IO.inspect(Library.errors(changeset))
$ '
#Ecto.Changeset<
  action: :insert,
  changes: %{title: "X", pages: -5},
  errors: [
    pages: {"must be greater than %{number}",
     [validation: :number, kind: :greater_than, number: 0]},
    title: {"should be at least %{count} character(s)",
     [count: 2, validation: :length, kind: :min, type: :string]},
    author: {"can't be blank", [validation: :required]}
  ],
  data: #Library.Book<>,
  valid?: false,
  ...
>
%{
  title: ["should be at least 2 character(s)"],
  author: ["can't be blank"],
  pages: ["must be greater than 0"]
}
%{title: ["has already been taken"]}
%{pages: ["must be greater than 0"]}

Reading, updating, deleting

Repo.get/2 fetches by id and returns nil if there's no such row (Repo.get!/2 raises instead). Repo.all/1 takes a schema for every row, or a query. Library.titles_by/1 uses the keyword syntax, one from with where:, order_by: and select:; Library.shorter_than/1 builds a query with the pipe syntax, one step at a time. Both mean the same thing. In both, ^ (the pin from Pattern Matching) brings an Elixir value into the query; Ecto sends it to the database as a parameter, never as SQL text.

$ mix run -e '
$ IO.inspect(Library.Repo.get(Library.Book, 1).title)
$ IO.inspect(Library.Repo.get(Library.Book, 99))
$ IO.inspect(length(Library.Repo.all(Library.Book)), label: "books")
$ IO.inspect(Library.titles_by("Ursula K. Le Guin"))
$ IO.inspect(Library.shorter_than(300))
$ '
"Dune"
nil
books: 4
["A Wizard of Earthsea", "The Dispossessed"]
[{"A Wizard of Earthsea", 183}, {"Kindred", 264}]

Updates go through the same changeset as inserts, so the same rules apply, and Repo.update/1 writes only the fields that changed. Repo.delete/1 takes the struct to delete.

$ mix run -e '
$ {:ok, book} = Library.set_pages("Dune", 896)
$ IO.inspect(book.pages, label: "Dune pages")
$ {:ok, _} = Library.remove("Kindred")
$ IO.inspect(Library.shorter_than(1000))
$ '
Dune pages: 896
[{"A Wizard of Earthsea", 183}, {"The Dispossessed", 387}, {"Dune", 896}]

Going further

That's enough Ecto for the rest of this track. For more:

Pitfall: a validation only sees the data in front of it. Two requests can both check "is this title free?" and both pass. Rules that must hold under concurrent writes belong in the database (a unique index, a CHECK), with unique_constraint/2 or check_constraint/3 turning the refusal into a changeset error. Transactions and Constraints shows this.

Pitfall: only fields listed in cast/3 can come from outside data. That's the point: never cast fields a user shouldn't set (an admin flag, a balance). Set those in code, with change/2 or put_change/3.

Improve this page on GitHub