Ecto Introduction

Ecto is a persistence framework for Elixir. That is a fancy way of saying that we use Ecto to talk to a SQL database. This chapter will introduce you to the very basics of Ecto in the Phoenix context. This means that whenever available, we use functions that were created by Phoenix generators.

You’ll learn:

  • How to create a database and a table.

  • How to insert data into that table.

  • How to list all data from that table.

  • How to get a dataset from that table by its ID.

  • How to update a dataset.

  • How to delete a dataset.

  • Basic search queries.

  • Basic associations (e.g. one-to-many)

We are going to use a PostgreSQL database. Ecto works fine with other databases too (e.g. MariaDB), but most Phoenix developers prefer PostgreSQL. For this book, we assume that you have a PostgreSQL user on your development system with the right to create a database and the following credentials:

  • user: postgres

  • password: postgres

These are the default values for the development system in Phoenix.

Ecto is a vast topic. In one chapter, I can only scratch its surface. If you want to dive into it more in-depth, you should read the book Programming Ecto by Darin Wilson. Also search for Darin’s Ecto talks on YouTube.

Create a Database

Before we can do anything, we have to create a database. Let’s create a small bookstore database:

$ mix phx.new bookstore
[...]
$ cd bookstore
$ mix ecto.create
Compiling 14 files (.ex)
Generated bookstore app
The database for Bookstore.Repo has been created
In case you want to drop this database (e.g. to rerun the mix ecto.create command) you can do so with the command mix ecto.drop.

The configuration for this development database can be found in the file config/dev.exs. Please make changes to that file if you use a different database or user.

config/dev.exs
use Mix.Config

# Configure your database
config :bookstore, Bookstore.Repo,
  username: "postgres",
  password: "postgres",
  database: "bookstore_dev",
  hostname: "localhost",
  show_sensitive_data_on_connection_error: true,
  pool_size: 10
[...]

Create a Table

For this example, we are going to use the mix phx.gen.context generator, which creates a database table and adds files that are useful for working with the new table.

You can do everything a generator does manually, but it is faster and results in fewer errors if you use a generator.

As a first resource, we want to create a table with authors. In the Phoenix world, we have to put that new resource authors into a context.

A context is an API boundary for one or more resources. Since version 1.3 of Phoenix, it has been recommended to organize the code that interfaces with the database using contexts, as this can help make the code easier to test, maintain and refactor. At this stage, don’t worry if you find the idea of contexts difficult to understand. You will soon get a feel for them as you work through the examples.
$ mix phx.gen.context Store Author authors first_name last_name twitter_account
* creating lib/bookstore/store/author.ex
* creating priv/repo/migrations/20200526112425_create_authors.exs
* creating lib/bookstore/store.ex
* injecting lib/bookstore/store.ex
* creating test/bookstore/store_test.exs
* injecting test/bookstore/store_test.exs

Remember to update your repository by running migrations:

    $ mix ecto.migrate

Migrations

Migrations are used to keep track of the process of generating or changing your database. This makes it a lot easier to keep all the databases, of other developers in your team, the production server, etc., synchronized.

The generated migration is stored in the file priv/repo/migrations/20200526112425_create_authors.exs. What it does is pretty clear once you have a look into it:

Migration filenames include a timestamp. It is better not to copy and paste the migration filenames as you work through this chapter.
priv/repo/migrations/20200526112425_create_authors.exs
defmodule Bookstore.Repo.Migrations.CreateAuthors do
  use Ecto.Migration

  def change do
    create table(:authors) do
      add :first_name, :string
      add :last_name, :string
      add :twitter_account, :string

      timestamps() (1)
    end

  end
end
1timestamps() generates the fields updated_at and inserted_at. Both are updated with the current timestamp during the creation of a dataset. Afterwards, only updated_at is updated with the current timestamp whenever Ecto changes that dataset.

To run the migration, we call mix ecto.migrate:

$ mix ecto.migrate
Compiling 2 files (.ex)
Generated bookstore app

13:30:35.437 [info]  == Running 20200526112425 Bookstore.Repo.Migrations.CreateAuthors.change/0 forward

13:30:35.440 [info]  create table authors

13:30:35.458 [info]  == Migrated 20200526112425 in 0.0s

According to this output, the authors table was created in the bookstore_dev database. But let’s double check:

$ psql -U postgres bookstore_dev (1)
psql (12.2)
Type "help" for help.

bookstore_dev=# SELECT column_name FROM information_schema.columns WHERE TABLE_NAME='authors'; (2)
   column_name
-----------------
 id
 first_name
 last_name
 twitter_account
 inserted_at
 updated_at
(6 rows)

bookstore_dev=# \q (3)
1psql is the command-line client for PostgreSQL. If you are not familiar with it: Don’t try this at home!
2This command lists all column names of the table authors.
3\q is the command to quit the PostgreSQL command-line client.

We can see that the migration created the authors table and added the columns.

You can undo a migration with a rollback:

$ mix ecto.rollback

12:48:54.388 [info]  == Running 20200526112425 Bookstore.Repo.Migrations.CreateAuthors.change/0 backward

12:48:54.390 [info]  drop table authors

12:48:54.398 [info]  == Migrated 20200526112425 in 0.0s

If you test the rollback now, you will have to run the migration again afterwards.

phx.gen.context generated, in addition to the migration file, the schema in lib/bookstore/store/author.ex and the context module in lib/bookstore/store.ex. We’ll look at both of these files in the next section.

Create a Dataset

We have a database and a table. But we still need to create our first set of data. To do that, we have to open iex. Within a Phoenix project, we can do this with the command iex -S mix phx.server. It loads the whole Phoenix project. It starts the webserver too (you see it sorting out the assets during startup) but right now we only use iex.

$ iex -S mix phx.server
Erlang/OTP 22 [erts-10.6.1] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:1] [hipe]

[info] Running BookstoreWeb.Endpoint with cowboy 2.7.0 at 0.0.0.0:4000 (http)
[info] Access BookstoreWeb.Endpoint at http://localhost:4000
Interactive Elixir (1.10.2) - press Ctrl+C to exit (type h() ENTER for help)
iex> (1)
1Actually you will see a couple of more messages here which are related to the assets pipeline (e.g. CSS and JavaScript). No need to bother with those now.

The context module in lib/bookstore/store.ex includes the create_author/1 function which we use to create a new author:

iex> Bookstore.Store.create_author(%{first_name: "Dave", last_name: "Thomas", twitter_account: "pragdave"})
[debug] QUERY OK db=3.8ms decode=1.5ms queue=2.5ms idle=1355.7ms (1)
INSERT INTO "authors" ("first_name","last_name","twitter_account","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" ["Dave", "Thomas", "pragdave", ~N[2020-05-26 11:54:37], ~N[2020-05-26 11:54:37]]
{:ok,
 %Bookstore.Store.Author{
   __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
   first_name: "Dave",
   id: 1,
   inserted_at: ~N[2020-05-26 11:54:37],
   last_name: "Thomas",
   twitter_account: "pragdave",
   updated_at: ~N[2020-05-26 11:54:37]
 }}
1These two debug lines show information about how the query is processed. We will not show this output in the other examples in this chapter.
Use alias Bookstore.Store at the beginning of an iex session and afterwards Store.create_author() to save typing time. This can make the code easier to read.

If you are wondering what create_author/1 does, look at the lib/bookstore/store.ex file:

lib/bookstore/store.ex
[...]
alias Bookstore.Repo
alias Bookstore.Store.Author
[...]
def create_author(attrs \\ %{}) do
  %Author{}
  |> Author.changeset(attrs) (1)
  |> Repo.insert() (2)
end
[...]
1Creates a new Author changeset with the attributes. Changesets are structs that can be used to filter, cast and validate the data.
2Uses Bookstore.Repo to insert the changeset into the database table.

If the insert in the table was successful, the function returns a {:ok, %Bookstore.Store.Author{}} tuple.

Assuming you’d like to assign the new author to the variable author. How would you do that? create_author/1 returns a tuple and not an Author. Pattern matching to the rescue! Example:

iex> {:ok, author} = Bookstore.Store.create_author(%{first_name: "Dave", last_name: "Thomas", twitter_account: "pragdave"})
{:ok,
 %Bookstore.Store.Author{
   __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
   first_name: "Dave",
   id: 1,
   inserted_at: ~N[2020-05-27 11:00:19],
   last_name: "Thomas",
   twitter_account: "pragdave",
   updated_at: ~N[2020-05-27 11:00:19]
 }}
iex> author
%Bookstore.Store.Author{
  __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
  first_name: "Dave",
  id: 1,
  inserted_at: ~N[2020-05-27 11:00:19],
  last_name: "Thomas",
  twitter_account: "pragdave",
  updated_at: ~N[2020-05-27 11:00:19]
}

Validations

If we try to create an empty dataset this happens:

iex> Bookstore.Store.create_author(%{})
{:error,
 #Ecto.Changeset<
   action: :insert,
   changes: %{},
   errors: [
     first_name: {"can't be blank", [validation: :required]},
     last_name: {"can't be blank", [validation: :required]},
     twitter_account: {"can't be blank", [validation: :required]}
   ],
   data: #Bookstore.Store.Author<>,
   valid?: false
 >}

The create_author/1 function returns a {:error, #Ecto.Changeset …​} tuple and it lists the reasons in the errors list:

  • first_name: {"can’t be blank", [validation: :required]}

  • last_name: {"can’t be blank", [validation: :required]}

  • twitter_account: {"can’t be blank", [validation: :required]}

It seems that some sort of data validation is happening. To understand how this works, we have to look at the lib/bookstore/store/author.ex file.

lib/bookstore/store/author.ex
defmodule Bookstore.Store.Author do
  use Ecto.Schema
  import Ecto.Changeset

  schema "authors" do (1)
    field :first_name, :string
    field :last_name, :string
    field :twitter_account, :string

    timestamps()
  end

  @doc false
  def changeset(author, attrs) do
    author
    |> cast(attrs, [:first_name, :last_name, :twitter_account]) (2)
    |> validate_required([:first_name, :last_name, :twitter_account]) (3)
  end
end
1This is the schema of the authors model.
2cast/4 casts the input. Only fields which are listed in the list can make it through. Everything else is thrown away right here.
3Here’s the reason why Bookstore.Store.create_author(%{}) resulted in an error. The function validate_required/3 checks whether all the list items are included.

Changesets are the gatekeepers of Ecto. In the next example, let’s add some more validations to our author changeset:

lib/bookstore/store/author.ex
[...]
  def changeset(author, attrs) do
    author
    |> cast(attrs, [:first_name, :last_name, :twitter_account])
    |> validate_required([:last_name]) (1)
    |> validate_length(:first_name, max: 255) (2)
    |> validate_length(:last_name, max: 255) (3)
    |> validate_length(:twitter_account, max: 15) (4)
  end
[...]
1We make sure that a dataset has a last_name. But it doesn’t have to have a first_name or a twitter_account.
2If a first_name is used, it can not be longer than 255 characters.
3A last_name can not be longer than 255 characters.
4If a twitter_account is used, it can not be longer than 15 characters (the max limit for Twitter handles).

Now we get a different error message:

iex> Bookstore.Store.create_author(%{})
{:error,
 #Ecto.Changeset<
   action: :insert,
   changes: %{},
   errors: [last_name: {"can't be blank", [validation: :required]}],
   data: #Bookstore.Store.Author<>,
   valid?: false
 >}

But let’s try to add an author with a missing first_name:

iex> Bookstore.Store.create_author(%{last_name: "Thomas", twitter_account: "pragdave"})
{:ok,
 %Bookstore.Store.Author{
   __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
   first_name: nil,
   id: 3,
   inserted_at: ~N[2020-05-27 05:37:46],
   last_name: "Thomas",
   twitter_account: "pragdave",
   updated_at: ~N[2020-05-27 05:37:46]
 }}

No surprise here. It works.

A list of available validations can be found at https://hexdocs.pm/ecto/Ecto.Changeset.html

Uniqueness Validation

In the last section, we created two datasets with the same Twitter account. That shouldn’t happen because it is unique. We have to add a validation for that.

A uniqueness validation needs a uniqueness database index. Since we haven’t added an index during the creation of the authors table, we have to add a migration to do it now.

To add a migration, we first need to create a migration file using the mix ecto.gen.migration command:

$ mix ecto.gen.migration add_twitter_account_index
Compiling 1 file (.ex)
* creating priv/repo/migrations/20200527054827_add_twitter_account_index.exs

Next, we have to edit the generated file, adding a function to create a unique index:

priv/repo/migrations/20200527054827_add_twitter_account_index.exs
defmodule Bookstore.Repo.Migrations.AddTwitterAccountIndex do
  use Ecto.Migration

  def change do
    create unique_index(:authors, [:twitter_account])
  end
end

And finally, we need to run the migration:

$ mix ecto.migrate

07:55:14.846 [info]  == Running 20200527054827 Bookstore.Repo.Migrations.AddTwitterAccountIndex.change/0 forward

07:55:14.849 [info]  create index authors_twitter_account_index
** (Postgrex.Error) ERROR 23505 (unique_violation) could not create unique index "authors_twitter_account_index"

    table: authors
    constraint: authors_twitter_account_index

Key (twitter_account)=(pragdave) is duplicated.
[...]

Oops! Because we have two entries with the same twitter_account the unique index can not be created. We can solve this in the following ways:

  • Delete one entry in the table and rerun the migration.

  • Do a mix ecto.drop, mix ecto.create and mix ecto.migrate. That destroys the existing data. Since this is a development system, there is little harm in doing this.

  • Do a mix ecto.reset, which is an alias (defined in the mix.exs file) for the above set of commands. It also populates the database with seeds if you have them. We don’t have any seeds yet.

We will use the alias mix ecto.reset:

$ mix ecto.reset
The database for Bookstore.Repo has been dropped (1)
The database for Bookstore.Repo has been created (2)

08:02:33.469 [info]  == Running 20200526112425 Bookstore.Repo.Migrations.CreateAuthors.change/0 forward

08:02:33.471 [info]  create table authors

08:02:33.481 [info]  == Migrated 20200526112425 in 0.0s (3)

08:02:33.540 [info]  == Running 20200527054827 Bookstore.Repo.Migrations.AddTwitterAccountIndex.change/0 forward

08:02:33.541 [info]  create index authors_twitter_account_index

08:02:33.543 [info]  == Migrated 20200527054827 in 0.0s (4)
1mix ecto.drop drops the database.
2mix ecto.create creates a new database.
3Runs the first migration. The one with 20200526112425 in it’s filename.
4Runs the second migration. The one with 20200527054827 in it’s filename. This one creates the index.

The uniqueness index in the table does make sure that we can’t add a second author with the same Twitter account to the table, but this constraint violation raises an exception. This is better than nothing, but not what we want. We want an error added to our changeset, and so we have to add one more line to the changeset:

lib/bookstore/store/author.ex
[...]
  def changeset(author, attrs) do
    author
    |> cast(attrs, [:first_name, :last_name, :twitter_account])
    |> validate_required([:last_name])
    |> validate_length(:first_name, max: 255)
    |> validate_length(:last_name, max: 255)
    |> validate_length(:twitter_account, max: 15)
    |> unique_constraint(:twitter_account) (1)
  end
[...]
1The unique_constrain/3 validation which will add an error to our changeset.

Time to check our work. Please fire up iex -S mix phx.server and follow me:

$ iex -S mix phx.server
[...]
iex> Bookstore.Store.create_author(%{first_name: "Dave", last_name: "Thomas", twitter_account: "pragdave"})
{:ok,
 %Bookstore.Store.Author{
   __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
   first_name: "Dave",
   id: 1,
   inserted_at: ~N[2020-05-27 06:17:18],
   last_name: "Thomas",
   twitter_account: "pragdave",
   updated_at: ~N[2020-05-27 06:17:18]
 }} (1)
iex> Bookstore.Store.create_author(%{first_name: "Dave", last_name: "Thomas", twitter_account: "pragdave"})
{:error,
 #Ecto.Changeset<
   action: :insert,
   changes: %{
     first_name: "Dave",
     last_name: "Thomas",
     twitter_account: "pragdave"
   },
   errors: [
     twitter_account: {"has already been taken",
      [constraint: :unique, constraint_name: "authors_twitter_account_index"]}
   ],
   data: #Bookstore.Store.Author<>,
   valid?: false
 >} (2)
1Works nicely. It results in a {:ok, %Bookstore.Store.Author()} which tells us that the dataset is saved.
2Works too. The second attempt to create an entry with the same data results in {:error, #Ecto.Changeset}. The errors tell us that twitter_account: {"has already been taken", [constraint: :unique, constraint_name: "authors_twitter_account_index"]}.

Uniqueness over multiple fields

Sometimes you need to assure a uniqueness not just over one but over multiple fields. To show how this is done, I assume that our authors table should not contain two authors with the same full name (e.g. no two Dave Thomas or Stefan Wintermeyer). To achieve that we have to check first_name and last_name in combination. We have to do that in the database with a combined index.

$ mix ecto.gen.migration add_full_name_index
* creating priv/repo/migrations/20200527071855_add_full_name_index.exs
priv/repo/migrations/20200527071855_add_full_name_index.exs
defmodule Bookstore.Repo.Migrations.AddFullNameIndex do
  use Ecto.Migration

  def change do
    create unique_index(:authors, [:first_name, :last_name]) (1)
  end
end
1Creates a concatenated index of the fields first_name and last_name.
lib/bookstore/store/author.ex
[...]
  def changeset(author, attrs) do
    author
    |> cast(attrs, [:first_name, :last_name, :twitter_account])
    |> validate_required([:last_name])
    |> validate_length(:first_name, max: 255)
    |> validate_length(:last_name, max: 255)
    |> validate_length(:twitter_account, max: 15)
    |> unique_constraint(:twitter_account)
    |> unique_constraint([:first_name, :last_name]) (1)
  end
[...]
1This unique_constraint/1 will trigger an error message instead of raising an exception.
$ mix ecto.reset (1)
Compiling 1 file (.ex)
The database for Bookstore.Repo has been dropped
The database for Bookstore.Repo has been created.
[...]
09:45:21.380 [info]  create index authors_first_name_last_name_index

09:45:21.382 [info]  == Migrated 20200527071855 in 0.0s

$ iex -S mix phx.server
[...]
iex> alias Bookstore.Store (2)
Bookstore.Store
iex> Store.create_author(%{first_name: "Dave", last_name: "Thomas"}) (3)
{:ok,
 %Bookstore.Store.Author{
   __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
   first_name: "Dave",
   id: 1,
   inserted_at: ~N[2020-05-27 08:35:29],
   last_name: "Thomas",
   twitter_account: nil,
   updated_at: ~N[2020-05-27 08:35:29]
 }}
iex> Store.create_author(%{first_name: "Dave", last_name: "Thomas"}) (4)
{:error,
 #Ecto.Changeset<
   action: :insert,
   changes: %{first_name: "Dave", last_name: "Thomas"},
   errors: [
     first_name: {"has already been taken",
      [
        constraint: :unique,
        constraint_name: "authors_first_name_last_name_index"
      ]}
   ],
   data: #Bookstore.Store.Author<>,
   valid?: false
 >}
1Resets our database.
2Sets a Bookstore.Store alias to saves us some precious time to type the command.
3The first Dave Thomas is created.
4A second Dave Thomas can not be created.

In the example above, the error message says that the :first_name has been taken, but, strictly speaking, this is not correct because the constraint is for the :first_name and :last_name together. Below is a slightly different approach, where a more descriptive name is set for the index.

priv/repo/migrations/20200527071855_add_full_name_index.exs
defmodule Bookstore.Repo.Migrations.AddFullNameIndex do
  use Ecto.Migration

  def change do
    create unique_index(:authors, [:first_name, :last_name], name: :full_name) (1)
  end
end
1We tell Ecto which name the index should have.
lib/bookstore/store/author.ex
[...]
  def changeset(author, attrs) do
    author
    |> cast(attrs, [:first_name, :last_name, :twitter_account])
    |> validate_required([:last_name])
    |> validate_length(:first_name, max: 255)
    |> validate_length(:last_name, max: 255)
    |> validate_length(:twitter_account, max: 15)
    |> unique_constraint(:twitter_account)
    |> unique_constraint(:full_name, name: :full_name) (1)
  end
[...]
1The unique_constraint uses :full_name.
iex> Bookstore.Store.create_author(%{first_name: "Dave", last_name: "Thomas"})
{:error,
 #Ecto.Changeset<
   action: :insert,
   changes: %{first_name: "Dave", last_name: "Thomas"},
   errors: [
     full_name: {"has already been taken",
      [constraint: :unique, constraint_name: "full_name"]} (1)
   ],
   data: #Bookstore.Store.Author<>,
   valid?: false
 >}
1We get an error for full_name.

Seeds

Often you need the database prefilled with data for your application. That’s what seeds are for. By default, they are in the priv/repo/seeds.exs file. For our bookstore we can work with these seeds:

priv/repo/seeds.exs
alias Bookstore.Store

Store.create_author(%{
  first_name: "Dave",
  last_name: "Thomas",
  twitter_account: "pragdave"
})
Store.create_author(%{
  first_name: "James",
  last_name: "Gray",
  twitter_account: "jeg2"
})
Store.create_author(%{
  first_name: "Ulisses",
  last_name: "Almeida",
  twitter_account: "ulissesalmeida"
})

To populate the database we call mix run priv/repo/seeds.exs.

$ mix run priv/repo/seeds.exs
During development, the command mix ecto.reset is often very useful. It resets the database (drops, creates and migrates the database) and runs the seeds.

Querying the Database

After adding data to the database table, we now want to be able to fetch any data that we need.

In this section, we will look at fetching all of a table’s entries, fetching a single entry using the ID (primary key) and fetching a single entry using one or more attributes.

Return all of a Table’s Entries

The generated Bookstore.Store module offers a list_authors/0 function which simply returns a list of all authors in the table:

$ iex -S mix phx.server
[...]
iex> Bookstore.Store.list_authors
[
  %Bookstore.Store.Author{
    __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
    first_name: "Dave",
    id: 1,
    inserted_at: ~N[2020-05-27 11:48:17],
    last_name: "Thomas",
    twitter_account: "pragdave",
    updated_at: ~N[2020-05-27 11:48:17]
  },
  %Bookstore.Store.Author{
    __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
    first_name: "James",
    id: 2,
    inserted_at: ~N[2020-05-27 11:48:17],
    last_name: "Gray",
    twitter_account: "jeg2",
    updated_at: ~N[2020-05-27 11:48:17]
  },
  %Bookstore.Store.Author{
    __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
    first_name: "Ulisses",
    id: 3,
    inserted_at: ~N[2020-05-27 11:48:17],
    last_name: "Almeida",
    twitter_account: "ulissesalmeida",
    updated_at: ~N[2020-05-27 11:48:17]
  }
]

If we look at the code for list_authors/0, we can see that it uses Ecto’s Repo.all/1 to fetch the data:

lib/bookstore/store.ex
defmodule Bookstore.Store do
[...]
  alias Bookstore.Repo
  alias Bookstore.Store.Author

[...]
  def list_authors do
    Repo.all(Author) (1)
  end
[...]
1Repo.all/1 requires one argument, which should be a 'queryable' data structure (at this stage, there’s no need to worry about what 'queryable' means). In this case, it is the name of the module where the schema is defined.

As list_authors/0 returns a list, we can use pattern matching to get item(s) from the output:

iex> authors = Bookstore.Store.list_authors
[...]
iex> [first_author | _] = authors (1)
[...]
iex> first_author
%Bookstore.Store.Author{
  __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
  first_name: "Dave",
  id: 1,
  inserted_at: ~N[2020-09-04 02:40:21],
  last_name: "Thomas",
  twitter_account: "pragdave",
  updated_at: ~N[2020-09-04 02:40:21]
}
iex> [first_author, second_author | _] = authors (2)
[...]
iex> second_author
%Bookstore.Store.Author{
  __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
  first_name: "James",
  id: 2,
  inserted_at: ~N[2020-09-04 02:40:21],
  last_name: "Gray",
  twitter_account: "jeg2",
  updated_at: ~N[2020-09-04 02:40:21]
}
1With pattern matching we take the first item of the list (the head) and assign it to first_author.
2And on this line, we can get the second item of the list and assign it to second_author.

Fetch Entries by ID

If you know the id of a dataset and you want to fetch it, use get_author!/1, which is in the Bookstore.Store module. Let me first show you how to use it:

iex> Bookstore.Store.get_author!(1)
%Bookstore.Store.Author{
  __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
  first_name: "Dave",
  id: 1,
  inserted_at: ~N[2020-05-27 11:48:17],
  last_name: "Thomas",
  twitter_account: "pragdave",
  updated_at: ~N[2020-05-27 11:48:17]
}

And let’s look at the code for get_author!/1. Here, we see that it uses Ecto’s Repo.get!/2 to fetch the single entry:

lib/bookstore/store.ex
[...]
  def get_author!(id), do: Repo.get!(Author, id) (1)
[...]
1The second argument for Repo.get! is the entry’s ID.

[IMPORTANT] ==== Functions which end with a ! (exclamation point) raise an exception if something goes wrong. Let me show you this with get/2 and get!/2 for an id we don’t have in our table:

iex> Repo.get(Author, 10000) (1)
nil
iex> Repo.get!(Author, 10000) (2)
** (Ecto.NoResultsError) expected at least one result but got none in query:

from a0 in Bookstore.Store.Author,
  where: a0.id == ^10000

    (ecto 3.4.4) lib/ecto/repo/queryable.ex:122: Ecto.Repo.Queryable.one!/3
1The ID 10000 doesn’t exist in the table, and the function returns nil.
2The requested ID doesn’t exist, and the function raises an exception. In your Phoenix application, if this happens in a function called by a controller, phoenix will automatically display a 404 page (this functionality depends on phoenix_ecto, which is included in this example app).

Fetch Entries by Attribute

There are many times when we want to fetch an entry using one or more of the entry’s attributes. One way of doing this is to use the Repo.get_by!/2 function.

For example, let’s create a function to fetch an entry using the author’s twitter account name:

lib/bookstore/store.ex
[...]
  def get_author_by_twitter!(twitter_account) do (1)
    Repo.get_by!(Author, twitter_account: twitter_account) (2)
  end
[...]
1We have added a ! to the end of this function (as it is using Repo.get_by!) to make it clear that it will raise an exception if no results are found. As mentioned earlier, if this happens, Phoenix will handle the exception and render a 404 page.
2The second argument for Repo.get_by! should be a keyword list or map.

And here is the output for get_author_by_twitter!/1:

iex> Bookstore.Store.get_author_by_twitter!("jeg2")
%Bookstore.Store.Author{
  __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
  first_name: "James",
  id: 2,
  inserted_at: ~N[2020-09-04 02:40:21],
  last_name: "Gray",
  twitter_account: "jeg2",
  updated_at: ~N[2020-09-04 02:40:21]
}

We can also use Repo.get_by!/2 to fetch an entry based on multiple attributes. In the next example, we will create a function that returns an entry based on the first_name and last_name.

lib/bookstore/store.ex
[...]
  def get_author_by_first_name_last_name!(first_name, last_name) do
    Repo.get_by!(Author, first_name: first_name, last_name: last_name) (1)
  end
[...]
1Again, the second argument for Repo.get_by! is a keyword list, this time containing two entries (for first_name and last_name).

This is the output if the entry is found:

iex> Bookstore.Store.get_author_by_first_name_last_name!("Ulisses", "Almeida")
%Bookstore.Store.Author{
  __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
  first_name: "Ulisses",
  id: 3,
  inserted_at: ~N[2020-09-04 02:40:21],
  last_name: "Almeida",
  twitter_account: "ulissesalmeida",
  updated_at: ~N[2020-09-04 02:40:21]
}

Update an Entry

If you want to change a dataset, you have to assign it to a variable first. Assuming we want to update the twitter_account of the dataset with the id 1, we would do the following:

$ iex -S mix phx.server
[...]
iex> alias Bookstore.Store (1)
Bookstore.Store
iex> alias Bookstore.Store.Author
Bookstore.Store.Author
iex> author = Store.get_author!(1) (2)
%Bookstore.Store.Author{
  __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
  first_name: "Dave",
  id: 1,
  inserted_at: ~N[2020-05-27 11:48:17],
  last_name: "Thomas",
  twitter_account: "pragdave",
  updated_at: ~N[2020-05-27 11:48:17]
}
iex> Store.update_author(author, %{twitter_account: nil}) (3)
{:ok,
 %Bookstore.Store.Author{
   __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
   first_name: "Dave",
   id: 1,
   inserted_at: ~N[2020-05-27 11:48:17],
   last_name: "Thomas",
   twitter_account: nil,
   updated_at: ~N[2020-05-28 10:54:55]
 }}
iex> Store.get_author!(1) (4)
%Bookstore.Store.Author{
  __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
  first_name: "Dave",
  id: 1,
  inserted_at: ~N[2020-05-27 11:48:17],
  last_name: "Thomas",
  twitter_account: nil,
  updated_at: ~N[2020-05-28 10:54:55]
}
1We set these aliases to make our lives a bit easier. No technical reason.
2We fetch the dataset with the ID 1 and assign it to the variable author.
3update_author/2 updates the twitter_account field in the database. You can read the SQL command.
4Just checking if it worked.

Associations

In this section, we will look at how to define and work with associations between schemas.

There are three kinds of associations that we can use: one-to-one, one-to-many and many-to-many. In the following example, we will look at the one-to-many association. We will create an association in which each author is associated with many books.

First, we need to generate a migration file to create the books table:

$ mix ecto.gen.migration create_books
Compiling 1 file (.ex)
* creating priv/repo/migrations/20200907084536_create_books.exs

Next, we need to edit the migration file to create the books table and to add a reference to the author:

priv/repo/migrations/20200907084536_create_books.exs
defmodule Bookstore.Repo.Migrations.CreateBooks do
  use Ecto.Migration

  def change do
    create table(:books) do
      add :title, :string
      add :author_id, references(:authors) (1)

      timestamps()
    end
  end
end
1This adds an author_id column to the books table which references an entry in the authors table.

Now run the migration by calling mix ecto.migrate:

We now need to add a Bookstore.Store.Book module with the schema for the books table:

lib/bookstore/store/book.ex
defmodule Bookstore.Store.Book do
  use Ecto.Schema
  import Ecto.Changeset

  alias Bookstore.Store.Author

  schema "books" do
    field :title, :string
    belongs_to :author, Author (1)

    timestamps()
  end

  @doc false
  def changeset(book, attrs) do
    book
    |> cast(attrs, [:title, :author_id])
    |> validate_required([:title, :author_id])
  end
end
1The belongs_to macro makes the associated schema accessible through the book.

We also need to make a small change to the Bookstore.Store.Author module:

lib/bookstore/store/book.ex
[...]
  schema "authors" do
    [...]
    has_many :books, Book (1)
    [...]
  end
[...]
1has_many lets us access the books through the author.

Querying Associations

We have created the one-to-many association between authors and books, and we have updated the schemas, so we can now start adding new books to the database. To do this, we will add a create_book function to the Bookstore.Store context module:

lib/bookstore/store.ex
[...]
  alias Bookstore.Store.Book

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

Now we are ready to try this out in iex:

iex> Bookstore.Store.create_book(%{title: "Programming Elixir", author_id: 1})
{:ok,
 %Bookstore.Store.Book{
   __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
   author: #Ecto.Association.NotLoaded<association :author is not loaded>,
   author_id: 1,
   id: 4,
   inserted_at: ~N[2020-09-08 01:17:34],
   title: "Programming Elixir",
   updated_at: ~N[2020-09-08 01:17:34]
 }}

We now want to write some queries to fetch this data from the database. We will add two functions to the Bookstore.Store context module - one that returns a list of an author’s books and one that fetches a book by querying the title.

lib/bookstore/store.ex
[...]
  def list_books_by_author(author) do
    Book
    |> where(author_id: ^author.id) (1)
    |> Repo.all()
  end
[...]
  def get_book_by_title!(title) do
    Repo.get_by!(Book, title: title)
  end
[...]
1When using external values in query expressions, we need to prefix the value with a ^ sign. We will look at the where macro later, when we start using more complex queries.

Let’s see how these queries work in iex:

iex> author = Bookstore.Store.get_author_by_twitter!("pragdave") (1)
%Bookstore.Store.Author{
  __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
  books: #Ecto.Association.NotLoaded<association :books is not loaded>,
  first_name: "Dave",
  id: 1,
  inserted_at: ~N[2020-09-08 01:56:38],
  last_name: "Thomas",
  twitter_account: "pragdave",
  updated_at: ~N[2020-09-08 01:56:38]
}
iex> Bookstore.Store.list_books_by_author(author)
[
  %Bookstore.Store.Book{
    __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
    author: #Ecto.Association.NotLoaded<association :author is not loaded>, (2)
    author_id: 1,
    id: 1,
    inserted_at: ~N[2020-09-08 01:56:51],
    title: "Programming Elixir",
    updated_at: ~N[2020-09-08 01:56:51]
  }
]
iex> Bookstore.Store.get_book_by_title!("Programming Elixir")
%Bookstore.Store.Book{
  __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
  author: #Ecto.Association.NotLoaded<association :author is not loaded>,
  author_id: 1,
  id: 1,
  inserted_at: ~N[2020-09-08 01:56:51],
  title: "Programming Elixir",
  updated_at: ~N[2020-09-08 01:56:51]
}
1We fetch the author as we need to use it in the list_books_by_author/1 function.
2By default, the associated data (in this case, the author data) is not loaded. In the next subsection, we will look at how to load this data.

Preloading Associated Data

As can be seen in the examples above, the data for an associated table is not loaded by default. To request this data, we need to preload it, using either Ecto.Query.preload or Ecto.Repo.preload. In this subsection, we will use Ecto.Repo.preload, which allows us to preload structs after they have been fetched from the database.

Let’s open up iex and see how preloading data works:

iex> elixir_book = Bookstore.Store.get_book_by_title!("Programming Elixir") (1)
%Bookstore.Store.Book{
  __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
  author: #Ecto.Association.NotLoaded<association :author is not loaded>,
  author_id: 1,
  id: 1,
  inserted_at: ~N[2020-09-08 01:56:51],
  title: "Programming Elixir",
  updated_at: ~N[2020-09-08 01:56:51]
}
iex> Bookstore.Repo.preload(elixir_book, :author) (2)
%Bookstore.Store.Book{
  __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
  author: %Bookstore.Store.Author{ (3)
    __meta__: #Ecto.Schema.Metadata<:loaded, "authors">,
    books: #Ecto.Association.NotLoaded<association :books is not loaded>,
    first_name: "Dave",
    id: 1,
    inserted_at: ~N[2020-09-08 01:56:38],
    last_name: "Thomas",
    twitter_account: "pragdave",
    updated_at: ~N[2020-09-08 01:56:38]
  },
  author_id: 1,
  id: 1,
  inserted_at: ~N[2020-09-08 01:56:51],
  title: "Programming Elixir",
  updated_at: ~N[2020-09-08 01:56:51]
}
1We first fetch the book with the title "Programming Elixir".
2We use a single atom to preload a single association. To preload multiple associations, we would use a list of atoms.
3We can now see the associated (author) data.

For more information about associations, see https://hexdocs.pm/ecto/2.2.11/associations.html

Work in progess