Chapter 7. Being persistent with a database

This chapter covers

• Configuring Ecto to use a real database
• Using Ecto to query the database for items
• Using Ecto to insert items into and delete them from the database

Your Auction application has a public interface where you can query for items, and you know how to bring external package dependencies into the application. In chapter 5, you readied the application for real database use by bringing in Ecto and postgrex (or a different database adapter based on your needs).

You originally set up your Auction application to use a “fake” repo so as not to introduce too much complexity while you built up a small public interface for querying items. It was relatively quick to set up, required no external dependencies, and was fast, but it only allowed you to read a static list of items. For a true data-driven web application, you need the ability to do complex queries as well as insert new items and update existing ones.

In this chapter, you’ll get rid of the fake database and move to a real database. To achieve this, a few prerequisites need to be taken care of:

1. A database needs to be installed in your local dev environment. I’m using PostgreSQL, but other options are available in Ecto if you’d rather use them (such as MySQL or MSSQL). Because Ecto provides abstractions away from SQL-like language, there may be database-specific gotchas for your database of choice. Check the Ecto documentation to see if your database has any specific issues (https://github.com/elixir-ecto/ecto).
2. Once the database is installed, you need to know how to access it. This involves the database address (typically localhost or 127.0.0.1) as well as the port number.
3. You’ll need a database user ID and password (either created specifically for the Auction application or one already available).

Once you have those things, you’re ready to tackle the next section.

7.1. A quick intro to Ecto

Ecto does more than allow you to communicate with a particular database. It provides a unified language for talking to multiple kinds of databases—even simultaneously. It provides an easy way to specify module relationships, query databases, track changes throughout the lifecycle of a database request, and group multiple queries into a single transaction so that if one query fails, the rest of the queries can be rolled back.

It provides a way to describe how your structs will handle data and map them to specific database tables. It provides migrations so that you can track how a database’s structure changes over time, and it allows you to migrate up or down any connected database to match the required structure. It provides module-side and database-side validations to ensure you don’t have orphaned or bad data in your database.

There are many things that Ecto does well, and the best way to start learning about them is to dive in and get your hands dirty.

7.2. Configuring Ecto

If you haven’t already specified Ecto as a dependency in your application (as in chapter 5), you need to do that first. The following listing shows what the deps function in your mix.exs file should look like.

Listing 7.1. Specifying Ecto and Postgrex as dependencies

defp deps do
  [
    {:ecto_sql, "3.0.3"},
    {:postgrex, "0.14.1"}        1
  ]
end

• 1 postgrex is the PostgreSQL adapter for ecto_sql.

I’m using PostgreSQL in my version of Auction, so I’ve included postgrex as a dependency along with ecto_sql. Refer to table 7.1 if you’re using a different database to make sure you’ve specified the correct adapter.

Table 7.1. Ecto database adapters

Database	Ecto adapter	Dependency
PostgreSQL	Ecto.Adapters.Postgres	postgrex
MySQL	Ecto.Adapters.MySQL	mariaex
MSSQL	MssqlEcto	mssql_ecto
SQLite	Sqlite.Ecto2	sqlite_ecto2
Mnesia	EctoMnesia.Adapter	ecto_mnesia

Your Phoenix application also needs to bring in some Ecto helpers. Even though you created your Phoenix app with the --no-ecto flag, there’s a specific package that’s nice to have when working with Ecto in Phoenix (which you’ll be doing eventually). The phoenix_ecto package provides all you need to interact with Ecto within Phoenix.

To bring that dependency into your application, you need to edit the Phoenix app’s mix.exs file, located at auction_umbrella/apps/auction_web/mix.exs. You can simply add {:phoenix_ecto, "~> 4.0"} to the list of dependencies inside deps. Once you have the correct dependencies listed, run mix deps.get in your terminal to fetch the packages from hex.pm.

In order for Ecto to work with the new database, you need to give it some information, such as the database name, the username and password it can access that database with, and the database adapter you’ll be using. Application configuration like that is typically done in the config directory, more specifically, in the config.exs file.

You may (or may not) be surprised to learn that you already have an auction_umbrella/apps/auction/config/config.exs file in your Auction application. How did it get there? It was created along with the rest of the standard files and directories for the application when you used the mix new command line script. If you open that file, you’ll notice that there’s little in the file apart from comments. But these comments can be very helpful as you figure out what can be configured and how to read those configurations in your application.

For Ecto, the reading and using of the configuration are taken care of for you—you just need to tell Ecto about your local database environment. Specifically, there are three initial things you need to let Ecto know:

• What repos you have in your application
• How to access each of those repos
• The minimal setup for the repos

As described in the comments in config.exs, you do that with the config function. There are two different versions of config: config/2 and config/3. You’ll be using both. config/2 requires the name of your application (:auction) and the key/value pairs you’d like to configure.

To start, you’ll tell your application that the name of your repo is Auction.Repo. Ecto can handle multiple repos simultaneously, so it expects a list as the repo’s value. Add the following line to your config.exs file.

Listing 7.2. Letting Ecto know which repos you have in your application

config :auction, ecto_repos: [Auction.Repo]

You’ll use config/3 to tell Ecto how your Auction.Repo repo will communicate with your database. config/3 requires the name of your application (:auction), a key to store the configuration under, and key/value pairs for configuration. The following listing shows the basic values that Ecto requires in config.exs.

Listing 7.3. Configuring Auction.Repo

config :auction, Auction.Repo,
  database: "auction",
  username: "postgres",      1
  password: "postgres",      1
  hostname: "localhost",
  port: "5432"

• 1 Sets your username and password

These values should be pretty self-explanatory. Plug in the values that work for your local database. I’m using PostgreSQL, so the adapter for PostgreSQL is Ecto.Adapters.Postgres. If you require a different database adapter, refer to table 7.1.

Finally, you need to create a file that will house the code for your repo. This is super-simple as Ecto provides almost all the functionality you need. All you need in your module is use Ecto.Repo, the name of your application (:auction), and the adapter you’re using. In a file named auction_umbrella/apps/auction/lib/auction/repo.ex, enter the code in the following listing. Naming the module Auction.Repo matches the Ecto configuration in listing 7.3.

Listing 7.4. Setting up the Auction.Repo file

defmodule Auction.Repo do
  use Ecto.Repo,
    otp_app: :auction,
    adapter: Ecto.Adapters.Postgres       1
end

• 1 Using Ecto.Repo to configure your application repo

By including use Ecto.Repo, opt_app: :auction in your module, you benefit from a number of functions that Ecto brings in and that you don’t have to define yourself. This keeps boilerplate code to a minimum.

That’s it on the Elixir side of things! You’re now ready to use Ecto.

7.2.1. Using Ecto’s Mix tools to set up your database

Believe it or not, you’ve already provided Ecto with the minimum amount of information it needs to start interacting with your application. The first thing you want Ecto to do is create the database you specified when you configured Auction.Repo in config.exs (database: "auction"). To make that happen, you’ll use the Mix tasks that Ecto provides.

If you haven’t yet installed your database and set up the user specified in your config file (username: "postgres", password: "postgres"), do that now, and make sure the user has the ability to create a database. Then you can run the ecto.create Mix task as follows:

> mix ecto.create
The database for Auction.Repo has been created

If you received the preceding response when you ran that command, you’re in business! Your application can connect to the database and interact with it successfully.

7.3. Preparing Auction to use the database

Now that you’ve created the database, you need to create its structure. How will the information in the database be structured? What will it look like? What kind of data will you be storing? You need to provide this information to Ecto as a schema.

7.3.1. Defining the Auction.Item schema

A database is made up of tables that store information in rows, and each row has data that corresponds to the columns that the table defines. For example, if you needed to store data about a menu for a coffee shop, there could be a table named menu_items to store that data. The schema would define the kind of information you need to store in that database table. It could look something like table 7.2.

Table 7.2. A table storing menu items for a coffee shop

Column name	Data type	Example
title	String	Fancy Espresso
description	String	A little pick-me-up
price	Integer	295

Note

In table 7.2, why store the price as an integer instead of a float (like 2.95)? Well, computers are notorious for rounding and precision errors. You can eliminate those by just multiplying your price by 100 and storing it as an integer.

If you open auction_umbrella/apps/auction/lib/auction/item.ex, you’ll see something similar already defined—a defstruct defining what your data looks for an Auction.Item. This was fine for your fake repo, but now you need to let Ecto know the details of how your data is structured. To do that, you delete the defstruct definition and instead use Ecto’s Ecto.Schema.schema/2 function.

Ecto.Schema.schema/2 takes as arguments the table name that the data will live in, plus a list of field definitions (these will roughly match up with the columns in your table) defined by Ecto.Schema.field/2. It will have a familiar feel, but you’ll provide Ecto with more information about your module’s data structure, such as the data type and which table it can find all your information in.

The following listing shows the Auction.Item schema defined for Ecto.

Listing 7.5. Defining Auction.Item’s schema for Ecto

defmodule Auction.Item do
  use Ecto.Schema

  schema "items" do
    field :title, :string
    field :description, :string
    field :ends_at, :utc_datetime
    timestamps()                     1
  end
end

• 1 A convenience function that adds inserted_at and updated_at columns

Here are a few points to note about this schema:

• use Ecto.Schema allows you to type just the function names provided by the module (such as schema and field). Otherwise, you’d have to type Ecto .Schema.schema and Ecto.Schema.field. This line also provides other functions under the hood to allow Ecto to do what it needs to do.
• Ecto.Schema provides a pretty extensive list of types for your data (and even allows you to create custom ones). See table 7.3 for a list of possibilities.

  Table 7.3. Some often-used Ecto data types

  Ecto type	Elixir type	Example(s)
  :id	Integer	1, 100, 1_000
  :integer	Integer	1, 2, 3
  :float	Float	3.14, 9.3976387565388
  :boolean	Boolean	true, false
  :string	UTF-8 string	“Phoenix in Action”
  :date	Date	2018-10-30
  :time	Time	03:08:32.936199
  :naive_datetime	NaiveDateTime	Date and time without time zone information
  :utc_datetime	DateTime	Date and time with time zone information

• timestamps() is a simple way to let Ecto know that your data will have inserted_at and updated_at columns with timestamps of when the particular row was created and updated.
• You may have noticed that you aren’t defining a field :id. Ecto realizes that most tables you create will have a corresponding id column, and it provides one by default. You can override that default if you’d like, but you want the id column, in this case.

7.3.2. Migrating the database

Now that you have your schema defined, Ecto knows how to interact with the database when dealing with Items. But the database itself doesn’t know anything about Items. You’ve created the database itself, but you haven’t created the table or columns that your Auction.Item schema is referencing. That’s your next step—creating the table and columns in the database.

To do that, you can use another Ecto construct known as a migration. Migrations are pieces of code that allow you to build up and tear down your database programmatically. This allows you to commit your migrations into a version control system, share it with other developers, and recreate the database structure as needed. It also provides a good boundary separating code and database, allowing Ecto to do the hard work of interacting directly with the database.

Ecto provides a Mix task to easily create migrations: mix ecto.gen.migration will create the file (and any necessary directories) for a migration that’s timestamped in order to maintain the order of migrations. Not only that, but like mix new, it will generate a small amount of boilerplate code that will help you get started on your migration. To use it, you simply type mix ecto.gen.migration along with the filename you’d like to use for your migration. It’s typically a good idea to be descriptive about what you’re doing to the database in your migration’s filename so you can easily find it later if required.

Let’s name your migration create_items, since you’re creating the items table. The following code snippet shows the output when I ran the command on my development machine. Make sure you run this command in the auction_umbrella/apps/auction directory.

> mix ecto.gen.migration create_items
* creating priv/repo/migrations
* creating priv/repo/migrations/20181212023436_create_items.exs

If you peek into the file that was created (priv/repo/migrations/20181212023436_create_items.exs in my case; yours will be different depending on when you ran the command), you’ll find that some boilerplate code was generated for you.

Listing 7.6. Boilerplate migration code

defmodule Auction.Repo.Migrations.CreateItems do
  use Ecto.Migration

  def change do
                     1
  end
end

• 1 Here’s where you’d place the code detailing the change for the migration.

An Ecto.Migration is made up of a single change/0 function, or an up/0 function accompanied by a down/0 function. These tell Ecto how you’d like to change your database in this migration. If you provide both an up/0 and a down/0 function, Ecto will run the code in up/0 as it’s building your database, migration by migration, and it will run down/0 as it’s tearing your database down. If you only provide a change/0 function, Ecto is smart enough to know how the database changes as it goes up and down. Typically, you’ll only need to give Ecto a change/0 migration, but there are times when you might need more control over how specific tables are torn down (such as removing specific data or notifying some external service). In your case, creating a table is simple enough that you’ll stick with the provided change/0 function.

Ecto provides a number of functions to define your table’s structure. To create your table, you use the create/1 function. create can create tables, indexes, and constraints, but you want to create a table, in this case. The argument you provide create/1 is the directive to create a table, and you provide its name.

Then, you provide a list of actions (via functions) that should be performed on the table after creating it (adding columns, in this case). Each column is added by using the add/3 function, which accepts the name of the column (provided as an atom), what type of data it’ll contain, and any options you’d like to specify (such as default values). add/3 will look a lot like the field function you used when defining the schema for Auction.Item. Also, like in your schema, you don’t have to specify that you want an :id column (it creates one by default), and you can use the timestamps() function to automatically generate inserted_at and updated_at columns.

The following listing shows how you create the table in your migration.

Listing 7.7. Filling out the data for your table-creation migration

defmodule Auction.Repo.Migrations.CreateItems do
  use Ecto.Migration

  def change do
    create table("items") do           1
      add :title, :string
      add :description, :string
      add :ends_at, :utc_datetime
      timestamps()
    end
  end
end

• 1 “items” will be the name of the table. By convention, table names are plural nouns.

Now that you have your migration filled out, describing to Ecto exactly what you’d like to do in the migration, you can use a Mix task to ask Ecto to run the migration. Ecto provides a Mix task that lists the application’s migrations and their current status (whether they’re “up” or “down”). Run mix ecto.migrations to see that list.

> mix ecto.migrations

Repo: Auction.Repo

  Status    Migration ID    Migration Name
--------------------------------------------------
  down      20181212023436  create_items                  1

• 1 The down status shows that this migration hasn’t yet been run.

You can see by the output that your new migration hasn’t yet been run on the database. mix ecto.migrate will migrate any migrations that haven’t yet been run. You’ve only got one so far, so that command will run it.

When you run it, you’ll see some output from Ecto describing exactly what it’s doing to your database. Here’s the output I saw when I ran the command:

> mix ecto.migrate                              1
18:36:48.180 [info]  == Running 20181212023436
     Auction.Repo.Migrations.CreateItems.change/0 forward
18:36:48.180 [info]  create table items
18:36:48.196 [info]  == Migrated 20181212023436 in 0.0s

• 1 Migrates the database in the up direction

You just created a database table! Really easy and fast, huh? You didn’t need to know a lick of SQL or the small intricate differences between how one database does things versus another (like MySQL compared to PostgreSQL). It just works.

What if you made a mistake and weren’t quite ready to migrate yet? Ecto provides a Mix task for that as well. You can run mix ecto.rollback to roll back just the last migration, either using its down/0 function or by reversing the changes in the change/0 function. Figure 7.1 illustrates the direction the database migrates when using up and down. You don’t have any data in your database, so you can try out the feature without worrying about destroying any data.

Figure 7.1. mix ecto.migrate vs. mix ecto.rollback

> mix ecto.rollback                                 1
18:37:23.710 [info]  == Running 20181212023436
     Auction.Repo.Migrations.CreateItems.change/0 backward
18:37:23.710 [info]  drop table items
18:37:23.714 [info]  == Migrated 20181212023436 in 0.0s

• 1 Migrates the database down, undoing the previous migration

You can see that Ecto is smart enough to know that you created an items table when you migrated forward (or up) and that the reverse is to drop (or destroy) the items table when you migrate backwards (or down). Note that ecto.migrate attempts to bring as many migrations up as possible, whereas ecto.rollback only rolls back one migration at a time.

Although mix ecto.rollback can be extremely helpful, it can be dangerous as well. If you had thousands of rows of customer data in that table, it would all be gone now! Use it with care.

To continue with your Auction application, you need that table to exist. Run mix ecto.migrate again to recreate the table before moving on.

7.3.3. Pointing the public interface to the new repo

When you initially created your Auction application, you also created a fake repo so you could develop your public interface. It’s now time to point your public interface to use your new database. You made it really easy to do that by not referencing the repo directly, instead using a module context variable.

lib/auction.ex is the file that provides your public interface and does the work of talking with your repo. The only thing you need to change in that file now is which repo it points at. It currently reads

@repo FakeRepo

Change that line to point to your new repo:

@repo Auction.Repo

Because you’re no longer using FakeRepo in that file, you can also remove it from the list of aliases. The compiler will spit out a warning (more like a notice) every time you compile your application unless you remove it.

7.3.4. Instructing the application to supervise the database connection

You have one final step before you can test your new database connection. You’ve defined your schema, created the table in the database, and pointed your public interface to the new repo, but you still haven’t told your application to connect to the database.

The best way to do that is through a supervisor. We haven’t discussed supervisors and workers much, but a supervisor can ensure that a worker is running. In this case, your worker will be the connection to the database server. Supervising the connection will allow it to attempt to reconnect to the database if the connection is ever severed (along with other benefits we won’t discuss here). So you need to set up your first worker to be supervised.

The place to set up workers for supervision is in the auction_umbrella/apps/auction/lib/auction/application.ex file that was automatically generated when you used mix new with the --sup flag. Right now, it mostly contains comments that will help you configure your workers and supervisors.

All the options, and the great depth of functionality provided by supervisors and workers, can be a little overwhelming. Fortunately, Ecto makes it super easy. Do you remember how you put the line use Ecto.Repo, otp_app: :auction in the lib/auction/repo.ex module? That provided your module with all the information it needed to become a supervised Ecto worker. All you have to do now is specify that you’d like to have it supervised.

To do so, add {Auction.Repo, []} to the children list in lib/auction/application.ex. This provides your application with enough information to know that you want that worker (in the list of workers) to be supervised at startup. Right now it’s the only worker, but you can add more to the list as needed.

The following listing shows what your lib/auction/application.ex module should look like after adding the worker.

Listing 7.8. Adding your repo to the list of workers to be supervised

defmodule Auction.Application do
  use Application

  def start(_type, _args) do
    # List all child processes to be supervised
    children = [
      # Starts a worker by calling: Auction.Worker.start_link(arg)
      # {Auction.Worker, arg},
      {Auction.Repo, []}             1
    ]

    opts = [strategy: :one_for_one, name: Auction.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

• 1 Tells Elixir you’d like Auction.Repo to act as a worker

With that simple addition, the Auction.Repo worker will be supervised at startup. This means that if it goes down for any reason, Elixir will do its best to bring it back up.

7.3.5. Testing it out

Finally, the exciting part—you get to test whether your database connection is running and you have set everything up correctly. Because you set up your database connection to start automatically with your application in listing 7.8, all you need to do is start the application. As in the past, you’ll interact with your application through an IEx session for now (run iex -S mix now).

Once you’re in IEx, you can use the public interface calls to interact with the database. To list all the items you currently have (which is none), you can run Auction .list_items, just like you did when you had your fake repo running. Right now, though, you get an empty list (as in the following example) because you haven’t added any rows to the database (you’ll do that soon). So why run the query? Running it will not only ensure that the database connection is up and running, but also that Ecto is talking to it correctly and without error.

iex(1)> Auction.list_items()

21:16:18.226 [debug] QUERY OK source="items" db=1.5ms
SELECT i0."id", i0."title", i0."description", i0."ends_at" FROM "items" AS i0 []
[]                        1

• 1 [] is the return value. (You currently have no items.)

If you see something similar to the preceding output, congratulations! Your application is talking to your real database via Ecto. Ecto provides some debug output to show how it’s interacting with the database. In this case, you can also see the SQL statement it generated for the PostgreSQL database to list the items (SELECT i0."id", i0."title", i0."description", i0."ends_at" FROM "items" AS i0 []). Finally, you can see the return of the function call ([]), which shows that you have no items yet.

You can also try running Auction.get_item/1 and Auction.get_item_by/1. One will give you an error, and the other will return nil. Which do you think does what, and why? You’ll fix both instances soon, but try experimenting a little with each of the three public interface calls you currently have in Auction, and examine Ecto’s debug output. You’ll soon get a good grasp on what it’s trying to do.

7.4. Creating, retrieving, and deleting data in the database

You now have a working database connection and the beginnings of a public interface. If you were wondering how the public interface you built just works so easily, even after you moved from a fake, static repo to a live database, it’s because you wrote functions for your fake repo that mirrored functions that Ecto provides. The sidebar “Some of Ecto.Repo’s database functions” includes a truncated list of functions that Ecto.Repo provides (and that it gives your Auction.Repo through the use Ecto.Repo call at the top of the module). Many more functions are available, but these are the ones you’ll likely use the most.

7.4.1. Inserting data

The first thing you want to do with your new database setup is insert some data to play with. To do that, you use insert/2 or insert!/2.

Because you typically want to keep your public interface separate from your database calls, you first create a function to insert an Item into the database in lib/auction.ex. Ecto.Repo.insert/2 knows what table to insert the data into based on the struct that you give it. The only struct you currently have (Auction.Item) knows where it goes because of how you defined its schema. Can you start to see how the pieces are fitting together?

To make inserting Item data more explicit but also a little easier at the same time, you can design your public interface so that you only have to pass a map (%{}) instead of a full struct (%Auction.Item{}). Elixir provides a function named Kernel.struct/2 that changes a map to a struct based on the arguments provided. You’ll use that function to change a map and its data to the Auction.Item struct, and then pass that struct into the Ecto.Repo.insert/2 function. The following listing shows this new function in the lib/auction.ex module.

Listing 7.9. Adding insert_item/1 to Auction

def insert_item(attrs) do
  Auction.Item
  |> struct(attrs)
  |> @repo.insert()       1
end

• 1 insert/2 will return either {:ok, struct} or {:error, changeset}.

Because you’re using the insert/2 function instead of the bang version (insert!/2), you can expect to get either an {:ok, struct} or an {:error, changeset} as a return value. You won’t worry about the second part of these return values for now, but you do want to know whether you’re getting an :ok or an :error as the first value returned. You can use pattern matching to ensure the query took place as you intended.

Note

A changeset (in the second part of the return value) allows you to track changes to the data, as well as validations and any errors those validations may throw. We’ll go into exactly what a changeset is in chapter 8.

Save lib/auction.ex with the new public interface function, and use it to insert your first Item into the real database. You’ll pattern-match the return value with {:ok, item} to ensure there were no errors. You can attempt to do so on your own, in an IEx session, or follow along with the next listing.

Listing 7.10. Inserting the first Item

iex(1)> {:ok, item} =
iex...> Auction.insert_item(
iex...> %{title: "WarGames Bluray",                              1
iex...> description: "Computer games and thermonuclear war",
iex...> ends_at: DateTime.from_naive!(~N[2019-02-22 11:43:39], "Etc/UTC")}
iex...> )

08:43:10.661 [debug] QUERY OK db=3.3ms
INSERT INTO "items"
     ("description","ends_at","title","inserted_at","updated_at") VALUES
     ($1,$2,$3,$4,$5) RETURNING "id" ["Computer games and thermonuclear war",
     #DateTime<2019-02-22 11:43:39Z>, "WarGames Bluray", ~N[2018-12-12
     02:54:15], ~N[2018-12-12 02:54:15]]

{:ok,
%Auction.Item{
  __meta__: #Ecto.Schema.Metadata<:loaded, "items">,
  description: "Computer games and thermonuclear war",
  ends_at: #DateTime<2019-02-22 11:43:39Z>,
  id: 1,
  inserted_at: ~N[2018-12-12 02:54:15],
  title: "WarGames Bluray",
  updated_at: ~N[2018-12-12 02:54:15]
}}

• 1 WarGames is my favorite movie. Feel free to input data about your favorite.

The preceding listing shows that Ecto provides debug output describing what it’s doing with the database, including the SQL it generated and submitted to the database. It also shows the complete return value of {:ok, ...}, which you pattern-matched against with {:ok, item}.

You now have the database version of the data in the item variable, which you can use as you please.

7.4.2. Retrieving data

Now that you have some actual data to retrieve, you can use your data retrieval functions. You’ve created three functions in your public interface that allow you to retrieve items: list_items/0, get_item/1, and get_item_by/1. Because you now have an item in your database with an id of 1 and a title of WarGames Bluray, you can use those functions to practice retrieving data, as shown in the following listing. And guess what? Because you created your public interface to match Ecto’s, no further changes are required in the Auction module.

Listing 7.11. Listing the items in your database

iex(3)> Auction.list_items

09:45:33.007 [debug] QUERY OK source="items" db=2.0ms
SELECT i0."id", i0."title", i0."description", i0."ends_at", i0."inserted_at",
     i0."updated_at" FROM "items" AS i0 []
[
  %Auction.Item{                                          1
    __meta__: #Ecto.Schema.Metadata<:loaded, "items">,
    description: "Computer games and thermonuclear war",
    ends_at: #DateTime<2019-02-22 11:43:39Z>,
    id: 1,
    inserted_at: ~N[2018-12-12 02:54:15],
    title: "WarGames Bluray",
    updated_at: ~N[2018-12-12 02:54:15]
  }
]

• 1 You received the WarGames item, which means that things are hooked up correctly.

You can retrieve specific items as well, referencing them either with their id number or some other attribute.

Listing 7.12. Retrieving items from the database

iex(4)> Auction.get_item(1)                                        1

09:45:57.537 [debug] QUERY OK source="items" db=1.9ms
SELECT i0."id", i0."title", i0."description", i0."ends_at", i0."inserted_at",
     i0."updated_at" FROM "items" AS i0 WHERE (i0."id" = $1) [1]
%Auction.Item{
  __meta__: #Ecto.Schema.Metadata<:loaded, "items">,
  description: "Computer games and thermonuclear war",
  ends_at: #DateTime<2019-02-22 11:43:39Z>,
  id: 1,
  inserted_at: ~N[2018-12-12 02:54:15],
  title: "WarGames Bluray",
  updated_at: ~N[2018-12-12 02:54:15]
}

iex(5)> Auction.get_item_by(%{title: "WarGames Bluray"})           2

09:46:53.205 [debug] QUERY OK source="items" db=2.5ms
SELECT i0."id", i0."title", i0."description", i0."ends_at", i0."inserted_at",
     i0."updated_at" FROM "items" AS i0 WHERE (i0."title" = $1) ["WarGames
     Bluray"]
%Auction.Item{
  __meta__: #Ecto.Schema.Metadata<:loaded, "items">,
  description: "Computer games and thermonuclear war",
  ends_at: #DateTime<2019-02-22 11:43:39Z>,
  id: 1,
  inserted_at: ~N[2018-12-12 02:54:15],
  title: "WarGames Bluray",
  updated_at: ~N[2018-12-12 02:54:15]
}

• 1 Auction.get_item/1 requests an item based on its id.
• 2 Auction.get_item_by/1 requests an item based on specified attributes.

As you can see in the preceding listing, all of your Auction functions for retrieving items from the database appear to be working correctly.

7.4.3. Deleting data

There may come a time when you no longer need specific rows in the database, so you need a way to delete data. Ecto.Repo provides the delete/2 and delete!/2 functions for that purpose. They’re simple functions that take a struct as their argument and delete the row that matches that struct’s id.

By this point, you probably know the drill: you want to keep the database and public interface separate, so you’ll create your own border in the Auction module instead of calling Auction.Repo.delete/2 directly. Because Ecto’s function is so simple, your border function will also be very simple—it will just pass the struct through to Ecto.

The following listing shows the function definition you need to add to lib/auction.ex.

Listing 7.13. Adding delete_item/1 to Auction

def delete_item(%Auction.Item{} = item), do: @repo.delete(item)      1

• 1 Because the function body is so short, you use the inline version of the function definition.

Note how you pattern-match the first argument in that function definition to make sure that it receives an Auction.Item struct. You don’t want to accidentally delete any non-items in Auction.delete_item/1.

7.4.4. Updating data

You’ve inserted, retrieved, and deleted data from your database. What about updating? In order to update data with Ecto, you need a changeset. At first glance, it can seem limiting that Ecto requires a changeset to simply update a row in the database, but you’ll see that it’s very powerful and can save you a lot of heartache when used correctly. In fact, this is such a deep and meaningful topic that the whole next chapter is devoted to changesets and to updating and verifying data as it goes into the database.

With that in mind, we’ll wrap up this chapter. Before we move on, I encourage you to create, retrieve, and delete more items in your database. Doing so will get you used to interacting with your database and the public interface, and it’ll provide more data for you to eventually work with.

7.4.5. What about the website?

One more thing: you know how I’ve been harping on keeping the database concerns and business logic separate from the website concerns? Let’s look at one of the benefits of doing that.

Navigate to auction_umbrella/apps/auction_web and run mix phx.server. Load up http://localhost:4000 in your browser. What do you see? The auction site is now displaying data directly from your database instead of from the fake repo (see figure 7.2). You didn’t have to change a single line of code on the web side of the umbrella app, because you kept the public interface of your business logic exactly the same.

Figure 7.2. The web side of your application didn’t require any changes to see the real database data.

Summary

• Ecto provides a number of Mix tasks to make interacting with your database setup simple, such as mix ecto.create, mix ecto.migrate, and mix ecto .rollback.
• Use Ecto’s Ecto.Schema.schema/2 function to let Ecto know how your data is structured in the database.
• Use Ecto’s Ecto.Migration module and Mix task to create and run migrations that change the structure of your database programmatically.
• Ecto provides functions for inserting data into and retrieving and deleting data from the database, though it’s a good idea to write your own functions that utilize Ecto’s to provide a clean border between concerns.