Database wrapper and query generator for elixir.

The 4 Components

• Ecto.Repo — where the data is
• Ecto.Schema — what the data is
• Ecto.Query — how to read the data
• Ecto.Changeset — how to change the data

Setting up a Repo

mix ecto.gen.repo -r Friends.Repo

• config/config.exs

config :friends, Friends.Repo,
  database: "friends",
  username: "user",
  password: "pass",
  hostname: "localhost"
  
config :friends, ecto_repos: [Friends.Repo] # Define repo to use in ecto

• lib/friends/repo.ex

defmodule Friends.Repo do
  use Ecto.Repo,
    otp_app: :friends,
    adapter: Ecto.Adapters.Postgres
end

• add Friends.Repo in the app supervision tree in lib/friends/application.ex

Setting up the database

• Create database: mix ecto.create
• Create a migration with mix ecto.gen.migration create_people
• Write migration code to create new table inside change method in priv/repo/migrations/{generated_create_people_migration_file}

create table(:people) do
  add :first_name, :string
  add :last_name, :string
  add :age, :integer
end

• Run the migration: mix ecto.migrate

Schema

defmodule Friends.Person do
  use Ecto.Schema

  schema "people" do
    field :first_name, :string
    field :last_name, :string
    field :age, :integer
  end
end

• Create a new struct: person = %Friends.Person{}
• Set field value: person = %{person | age: 28}
• Retrieve value: person.age

Repo

• Inserting data with a schema struct: {:ok, person} = Friends.Repo.insert person
• Inserting data with a changeset: Friends.Repo.insert changeset

Changeset

• Create a changeset: Ecto.Changeset.cast(person, params, [:first_name, :last_name, :age])
• Validate changeset values:
  • Ecto.Changeset.validate_required([:first_name])
• Check changeset validity: changeset.valid?
• Get changeset errors: changeset.errors
• Errors

[first_name: {"can't be blank", [validation: :required]},
 last_name: {"can't be blank", [validation: :required]},
 bio: {"should be at least %{count} character(s)", [count: 15, validation: :length, kind: :min, type: :string]}]

• Display human friendly errors using Ecto.Changeset.traverse_errors/2

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

%{
  first_name: ["can't be blank"],
  last_name: ["can't be blank"],
  bio: ["should be at least 15 character(s)"],
}

Query

• Create query with from/2

# `in` expression
from(c in City, select: c)

# Ecto.Queryable
from(City, limit: 1)

# Fragment with user-defined function and predefined columns
from(f in fragment("my_table_valued_function(arg)"), select: f.x)

# Fragment with built-in function and undefined columns
from(f in fragment("select generate_series(?::integer, ?::integer) as x", ^0, ^10), select: f.x)

• Interpolation with ^

def with_minimum(age, height_ft) do
  from u in "users",
    where: u.age > type(^age, :integer) and u.height > ^(height_ft * 3.28),
    select: u.name
end

with_minimum(18, 5.0)

• Nil comparison

from u in User, where: is_nil(u.age)

• Composition

from u in (from u in User, where: u.age > 18), select: u.name

Other concepts

• Macro API - https://hexdocs.pm/ecto/Ecto.Query.html#module-macro-api
• Query prefix - https://hexdocs.pm/ecto/Ecto.Query.html#module-query-prefix

Resources

• https://hexdocs.pm/ecto/getting-started.html

CRUD

Fetching records

Single record

• By ID - Repo.get(Movie, 1)
• By attributes - Repo.get(Movie, title: "Ready Player One")
• The first - Movie |> Ecto.Query.first() |> Repo.one()
• The last - Movie |> Ecto.Query.last() |> Repo.one()
• Raise error if not found - Repo.get!(Movie, 1)

Multiple records

• All at one - Movie |> Repo.all()
• Stream all - Movie |> Repo.stream() |> Enum.each(fn r -> ... end)
• At least one exists? - Movie |> Repo.exists?()

Querying records

Keyword-based queries

• Bindingless

query =
	from Movie,
	where: [title: "Ready Player One"],
	select: [:title, :tagline]
Repo.all(query)

• Bindings in queries

query =
	from m in Movie,
	where: m.title == "Ready Player One"
	select: [m.title, m.tagline]
Repo.all(query)

Interpolation with ^

title = "Ready Player One"
query =
	from m in Movie,
	where: m.title == ^title,
	select: [m.title, m.tagline]
Repo.all(query)

Pipe-based queries

Movie
|> where([m], m.title == "Ready Player One")
|> select([m], {m.title, m.tagline})
|> Repo.all

Inserting records

Single record

• Using internal data

%Person{name: "Bob"}
|> Repo.insert()

• Using external data

params = %{"name"=>"Bob"}

%Person{}
|> Ecto.Changeset.cast(params, [:name])
|> Repo.insert()

Multiple records

data = [%{name: "Bob"}, %{name: "Alice"}]
Repo.insert_all(Person, data)

Updating records

Single record

• Using internal data

person =
	Person
	|> Ecto.Query.first()
	|> Repo.one!()
	
changeset = change(person, %{age: 29})
Repo.update(changeset)

• Using external data

params = %{"age" => "29"}

person =
	Person
	|> Ecto.Query.First()
	|> Repo.one!()
	
changeset = cast(person, params, [:age])
Repo.update(changeset)

Multiple records

Repo.update_all(Person, set: [age: 29])

Deleting records

Single record

person = Repo.get!(Person, 1)
Repo.delete(person)

Multiple records

Repo.delete_all(Person)

Associations