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. Which 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 which was granted the right to create a database and which has 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 re-run 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 change that in case 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

The generator mix phx.gen.context (https://hexdocs.pm/phoenix/Mix.Tasks.Phx.Gen.Context.html) can be used to create a context with a couple of useful functions around an Ecto schema.

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. It was included in Phoenix with version 1.3. Don’t lose any sleep on the question of what a context is. You’ll get a feeling about it soon. Good naming of contexts is essential but not at this stadium of your Ecto learning process.
$ 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 are used to keep track of the process of generating or changing your database. If you are in a team of multiple developers, migrations are the tool so that you don’t pull each other’s carpets.

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

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

The migration script created the table authors in the database bookstore_dev. 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 command-line client.

The migration created that 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

In case you test the rollback now you have to run the migration again afterwards.

phx.gen.context generated in addition to the migration the schema in lib/bookstore/store/author.ex and the context module lib/bookstore/store.ex. We’ll tackle both in the next section.

Migration filenames include a timestamp. Obviously, you are doing this at a later time than I. So you have to adjust the filenames in this chapter accordingly. No copy and paste for migration filenames!

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 the 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 the 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 asset pipeline (e.g. CSS and JavaScript). No need to bother with that now.

The context module 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. Often makes code easier to read.

If you wander what create_author/1 exactly does:

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.
2 Uses Repo to insert the changeset into the 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 results in a tuple and not an Author. Pattern matching for 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 happens. To understand that we have to look into lib/bookstore/store/author.ex

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 (2)
    author
    |> cast(attrs, [:first_name, :last_name, :twitter_account]) (3)
    |> validate_required([:first_name, :last_name, :twitter_account]) (4)
  end
end
1 This is the schema of the authors model.
2 Ecto.Changeset is a mechanism to filter, cast and validate the data.
3 cast/4 casts the input. Only fields which are listed in the list can make it through. Everything else is thrown away right there.
4 Here’s the reason why Bookstore.Store.create_author(%{}) resulted in an error. The function validate_required/3 checks weather all the list items are included.

Changesets are the gatekeepers of Ecto. Let’s add some validations to our author changeset as an example:

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:

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

The generator created the migration file. But we have to fill it with live:

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

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.
[...]

Ups! 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 re-run 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.

  • Do a mix ecto.reset which does all the above. Plus it populates the database with seeds if you have them. We don’t have seeds yet.

We take the shortcut 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 would raise an exception. Better than nothing but not what we want. We want an error to our changeset. 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 fist_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 concatinated 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 an exception without it.
$ 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 Reset our database.
2 Set 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.

Here’s a slightly different approach for those of you who say that an error for :first_name is not ideal.

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 file priv/repo/seeds.exs. 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
[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 (drop, create and migrate) and runs the seeds.

Return all Entries of a Table

The generated Bookstore.Store offers a list_authors 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]
  }
]

We can use all the mechanisms of a list with this result:

iex(2)> authors = Bookstore.Store.list_authors
[...]
iex(3)> [first_author | _] = authors (1)
[...]
iex(4)> first_author
%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)> for author <- authors do
...(5)> IO.puts author.last_name
...(5)> end
Thomas
Gray
Almeida
[:ok, :ok, :ok]
1 With pattern matching we take the first item of the list (the head) and assign it to first_author.

Fetch one Entry of a Table by ID

If you know the id an dataset and you want to fetch it use get_author!/1 which got generated 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 here is the concise code of it:

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

[...]
  def get_author!(id), do: Repo.get!(Author, id)
[...]
One can argue that get_author!(id) is not easier/better than Repo.get!(Author, id).

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 Nothing happens. The ID 10000 doesn’t exist in the table. The function returns nil.
2 All hell breaks loose. As dramatic as an exception sounds, it is often the better way because it will result in a 404 page for your web application. It’s easier to catch bugs, and you can be sure not to display something wrong.

Update an Entry

If you want to change a dataset, you’d have to assign it to a variable first. Assuming we want to update the twitter_account or the dataset with the id 1 with nil we’d 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.