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(https://hexdocs.pm/phoenix/Mix.Tasks.Phx.Gen.Context.html), 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(https://hexdocs.pm/phoenix/contexts.html) 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
1 timestamps() 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)
1 psql is the command-line client for PostgreSQL. If you are not familiar with it: Don’t try this at home!
2 This 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)> (1)
1 Actually 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(2)> 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
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]
 }}
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
[...]
1 Creates a new Author changeset with the attributes. Changesets(https://hexdocs.pm/ecto/Ecto.Changeset.html) are structs that can be used to filter, cast and validate the data.
2 Uses 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(2)> {:ok, author} = Bookstore.Store.create_author(%{first_name: "Dave", last_name: "Thomas", twitter_account: "pragdave"})
[debug] QUERY OK db=3.4ms decode=1.5ms queue=1.3ms idle=1013.5ms
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-27 11:00:19], ~N[2020-05-27 11:00:19]]
{: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(3)> 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(3)> 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
1 This is the schema of the authors model.
2 cast/4 casts the input. Only fields which are listed in the list can make it through. Everything else is thrown away right here.
3 Here’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
[...]
1 We make sure that a dataset has a last_name. But it doesn’t have to have a first_name or a twitter_account.
2 If a first_name is used, it can not be longer than 255 characters.
3 A last_name can not be longer than 255 characters.
4 If 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(4)> 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(6)> Bookstore.Store.create_author(%{last_name: "Thomas", twitter_account: "pragdave"})
[debug] QUERY OK db=1.0ms queue=0.5ms idle=1349.0ms
INSERT INTO "authors" ("last_name","twitter_account","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["Thomas", "pragdave", ~N[2020-05-27 05:37:46], ~N[2020-05-27 05:37:46]]
{: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)
1 mix ecto.drop drops the database.
2 mix ecto.create creates a new database.
3 Runs the first migration. The one with 20200526112425 in it’s filename.
4 Runs 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
[...]
1 The 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(1)> Bookstore.Store.create_author(%{first_name: "Dave", last_name: "Thomas", twitter_account: "pragdave"})
[debug] QUERY OK db=3.0ms decode=1.4ms queue=1.4ms idle=1965.4ms
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-27 06:17:18], ~N[2020-05-27 06:17:18]]
{: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(2)> Bookstore.Store.create_author(%{first_name: "Dave", last_name: "Thomas", twitter_account: "pragdave"})
[debug] QUERY ERROR db=8.1ms queue=2.3ms idle=1590.4ms
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-27 06:17:20], ~N[2020-05-27 06:17:20]]
{: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)
1 Works nicely. It results in a {:ok, %Bookstore.Store.Author()} which tells us that the dataset is saved.
2 Works 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
1 Creates 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
[...]
1 This 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(1)> alias Bookstore.Store (2)
Bookstore.Store
iex(2)> Store.create_author(%{first_name: "Dave", last_name: "Thomas"}) (3)
[debug] QUERY OK db=5.2ms decode=2.6ms queue=1.5ms idle=1377.6ms
INSERT INTO "authors" ("first_name","last_name","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["Dave", "Thomas", ~N[2020-05-27 08:35:29], ~N[2020-05-27 08:35:29]]
{: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(3)> Store.create_author(%{first_name: "Dave", last_name: "Thomas"}) (4)
[debug] QUERY ERROR db=9.1ms queue=1.3ms idle=1548.4ms
INSERT INTO "authors" ("first_name","last_name","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["Dave", "Thomas", ~N[2020-05-27 08:35:31], ~N[2020-05-27 08:35:31]]
{: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
 >}
1 Resets our database.
2 Sets a Bookstore.Store alias to saves us some precious time to type the command.
3 The first Dave Thomas is created.
4 A 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
1 We 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
[...]
1 The unique_constraint uses :full_name.
iex(3)> Bookstore.Store.create_author(%{first_name: "Dave", last_name: "Thomas"})
[debug] QUERY ERROR db=2.1ms queue=0.7ms idle=1.5e3ms
INSERT INTO "authors" ("first_name","last_name","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["Dave", "Thomas", ~N[2020-05-27 11:19:42], ~N[2020-05-27 11:19:42]]
{: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
 >}
1 We 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
[debug] QUERY ERROR db=11.1ms queue=1.1ms idle=5.3ms
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-27 11:47:40], ~N[2020-05-27 11:47:40]]
[debug] QUERY OK db=1.9ms queue=1.4ms idle=36.1ms
INSERT INTO "authors" ("first_name","last_name","twitter_account","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" ["James", "Gray", "jeg2", ~N[2020-05-27 11:47:40], ~N[2020-05-27 11:47:40]]
[debug] QUERY OK db=1.0ms queue=0.7ms idle=39.8ms
INSERT INTO "authors" ("first_name","last_name","twitter_account","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" ["Ulisses", "Almeida", "ulissesalmeida", ~N[2020-05-27 11:47:40], ~N[2020-05-27 11:47:40]]
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(2)> Bookstore.Store.list_authors
[debug] QUERY OK source="authors" db=11.5ms decode=1.3ms queue=1.2ms idle=889.7ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at" FROM "authors" AS a0 []
[
  %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
[...]
1 Repo.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(1)> authors = Bookstore.Store.list_authors
[...]
iex(2)> [first_author | _] = authors (1)
[...]
iex(3)> 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(4)> [first_author, second_author | _] = authors (2)
[...]
iex(5)> 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]
}
1 With pattern matching we take the first item of the list (the head) and assign it to first_author.
2 And 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(2)> Bookstore.Store.get_author!(1)
[debug] QUERY OK source="authors" db=16.1ms decode=1.5ms queue=6.3ms idle=1543.4ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at" FROM "authors" AS a0 WHERE (a0."id" = $1) [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)
[...]
1 The 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(5)> Repo.get(Author, 10000) (1)
nil
[debug] QUERY OK source="authors" db=3.7ms idle=132.4ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at" FROM "authors" AS a0 WHERE (a0."id" = $1) [10000]
iex(6)> Repo.get!(Author, 10000) (2)
[debug] QUERY OK source="authors" db=3.6ms idle=1004.2ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at" FROM "authors" AS a0 WHERE (a0."id" = $1) [10000]
** (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
1 The ID 10000 doesn’t exist in the table, and the function returns nil.
2 The 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
[...]
1 We 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.
2 The 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(1)> Bookstore.Store.get_author_by_twitter!("jeg2")
[debug] QUERY OK source="authors" db=6.1ms decode=0.8ms queue=1.1ms idle=1649.9ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at" FROM "authors" AS a0 WHERE (a0."twitter_account" = $1) ["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
[...]
1 Again, 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(1)> Bookstore.Store.get_author_by_first_name_last_name!("Ulisses", "Almeida")
[debug] QUERY OK source="authors" db=6.3ms decode=0.7ms queue=1.0ms idle=405.5ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at" FROM "authors" AS a0 WHERE ((a0."first_name" = $1) AND (a0."last_name" = $2)) ["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(2)> alias Bookstore.Store (1)
Bookstore.Store
iex(3)> alias Bookstore.Store.Author
Bookstore.Store.Author
iex(4)> author = Store.get_author!(1) (2)
[debug] QUERY OK source="authors" db=8.3ms decode=1.1ms queue=0.9ms idle=1955.5ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at" FROM "authors" AS a0 WHERE (a0."id" = $1) [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]
}
iex(5)> Store.update_author(author, %{twitter_account: nil}) (3)
[debug] QUERY OK db=2.2ms queue=3.6ms idle=1569.2ms
UPDATE "authors" SET "twitter_account" = $1, "updated_at" = $2 WHERE "id" = $3 [nil, ~N[2020-05-28 10:54:55], 1]
{: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(6)> Store.get_author!(1) (4)
[debug] QUERY OK source="authors" db=4.8ms idle=659.0ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at" FROM "authors" AS a0 WHERE (a0."id" = $1) [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: nil,
  updated_at: ~N[2020-05-28 10:54:55]
}
1 We set these aliases to make our lives a bit easier. No technical reason.
2 We fetch the dataset with the ID 1 and assign it to the variable author.
3 update_author/2 updates the twitter_account field in the database. You can read the SQL command.
4 Just 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
1 This 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
1 The 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
[...]
1 has_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(1)> Bookstore.Store.create_book(%{title: "Programming Elixir", author_id: 1})
[debug] QUERY OK db=8.0ms decode=1.7ms queue=2.8ms idle=1669.3ms
INSERT INTO "books" ("author_id","title","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" [1, "Programming Elixir", ~N[2020-09-08 01:17:34], ~N[2020-09-08 01:17:34]]
{: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
[...]
1 When 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(1)> author = Bookstore.Store.get_author_by_twitter!("pragdave") (1)
[debug] QUERY OK source="authors" db=6.0ms decode=0.8ms queue=1.3ms idle=1390.3ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at" FROM "authors" AS a0 WHERE (a0."twitter_account" = $1) ["pragdave"]
%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(2)> Bookstore.Store.list_books_by_author(author)
[debug] QUERY OK source="books" db=1.3ms queue=1.4ms idle=1802.0ms
SELECT b0."id", b0."title", b0."author_id", b0."inserted_at", b0."updated_at" FROM "books" AS b0 WHERE (b0."author_id" = $1) [1]
[
  %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(3)> Bookstore.Store.get_book_by_title!("Programming Elixir")
[debug] QUERY OK source="books" db=1.6ms queue=3.6ms idle=969.6ms
SELECT b0."id", b0."title", b0."author_id", b0."inserted_at", b0."updated_at" FROM "books" AS b0 WHERE (b0."title" = $1) ["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]
}
1 We fetch the author as we need to use it in the list_books_by_author/1 function.
2 By 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(1)> elixir_book = Bookstore.Store.get_book_by_title!("Programming Elixir") (1)
[debug] QUERY OK source="books" db=5.9ms decode=0.7ms queue=1.1ms idle=809.0ms
SELECT b0."id", b0."title", b0."author_id", b0."inserted_at", b0."updated_at" FROM "books" AS b0 WHERE (b0."title" = $1) ["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]
}
iex(2)> Bookstore.Repo.preload(elixir_book, :author) (2)
[debug] QUERY OK source="authors" db=0.5ms queue=0.8ms idle=151.0ms
SELECT a0."id", a0."first_name", a0."last_name", a0."twitter_account", a0."inserted_at", a0."updated_at", a0."id" FROM "authors" AS a0 WHERE (a0."id" = $1) [1]
%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]
}
1 We first fetch the book with the title "Programming Elixir".
2 We use a single atom to preload a single association. To preload multiple associations, we would use a list of atoms.
3 We 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