[004.2] Ecto Basics

Getting started with Ecto by creating a basic schema and querying it.

Subscribe now

Ecto Basics [04.17.2017]

Today we're going to start exploring Ecto by beginning to build out the data layer for the Firestorm Forum. It's a forum, so we'll have Users, Categories, Threads, and Posts. Let's get started.


We're starting with a basic umbrella app. We haven't covered these yet, but I've linked to a great primer for them. They make it easy to create independent Elixir applications inside of a single repository and use them as dependencies. The individual apps live inside of the apps directory, so let's cd into there and create a firestorm_data application that will serve as our data layer.

cd apps
mix new --sup firestorm_data
cd firestorm_data

We're going to be using Ecto, so let's pull that in as a dependency.

vim mix.exs

We'll also need to pull in postgrex since we're using Ecto with PostgreSQL.

defmodule FirestormData.Mixfile do
  # ...
  defp deps do
      {:ecto, "~> 2.1.4"},
      {:postgrex, "~> 0.11"}

Then we'll install the dependencies.

mix deps.get

Next, we want to create a Repo. From the docs:

A repository maps to an underlying data store, controlled by the adapter.

In essence, a Repo is how you ultimately interact with your data store.

We can create a repo like so:

mix ecto.gen.repo -r FirestormData.Repo

This created a Repo module for us and a bit of configuration. It also tells us to perform a couple of manual tasks:

Don't forget to add your new repo to your supervision tree
(typically in lib/firestorm_data.ex):

    supervisor(FirestormData.Repo, [])

And to add it to the list of ecto repositories in your
configuration files (so Ecto tasks work as expected):

    config :firestorm_data,
      ecto_repos: [FirestormData.Repo]

Let's do that (though the application actually lives at lib/firestorm_data/application.ex rather than where the hint suggests):

vim lib/firestorm_data/application.ex
defmodule FirestormData.Application do
  @moduledoc false

  use Application

  def start(_type, _args) do
    import Supervisor.Spec, warn: false

    children = [
      # Add this line
      supervisor(FirestormData.Repo, [])

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

This starts the Repo application in our supervision tree.

Next we'll list this in our ecto_repos configuration key:

vim config/config.exs
# This file is responsible for configuring your application
# and its dependencies with the aid of the Mix.Config module.
use Mix.Config

config :firestorm_data,
  ecto_repos: [FirestormData.Repo]

import_config "#{Mix.env}.exs"

We've also uncommented the last line - this allows us to have environment specific configuration. We'll configure environments for dev and test, ignoring prod for now:

vim config/dev.exs
use Mix.Config

config :firestorm_data, FirestormData.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "firestorm_data_repo_dev",
  username: System.get_env("POSTGRES_USER")     || "postgres",
  password: System.get_env("POSTGRES_PASSWORD") || "postgres",
  hostname: System.get_env("DB_HOST")           || "localhost"

Here I use a configuration that also lets you override the user, password, and host with environment variables if you'd like. This is what I use on Heroku, and I figured I might as well show it off now. Let's copy this file to config/test.exs, changing the database name:

vim config/test.exs
use Mix.Config

config :firestorm_data, FirestormData.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: System.get_env("POSTGRES_DB")       || "firestorm_data_repo_test",
  username: System.get_env("POSTGRES_USER")     || "postgres",
  password: System.get_env("POSTGRES_PASSWORD") || "postgres",
  hostname: System.get_env("DB_HOST")           || "localhost",
  pool: Ecto.Adapters.SQL.Sandbox

Here I've also allows the database to be renamed since that's something that I use in continuous integration environments. We also specified that we want to use the Ecto.Adapters.SQL.Sandbox pool. This is necessary for testing purposes later.

NOTE: You might need slightly different configuration, depending on how your PostgreSQL database is set up. There are good hints for this in Ecto's Getting Started Guide.

So now we have our Repo configured, at least for test and dev environments. Let's see what the generated lib/firestorm_data/repo.ex file looks like:

cat lib/firestorm_data/repo.ex
defmodule FirestormData.Repo do
  use Ecto.Repo, otp_app: :firestorm_data

Pretty straightforward. The otp_app configuration setting tells this Repo which OTP application holds the configuration for it.

Now we're all set to get started. Let's create the database, so we can query it:

mix ecto.create

Next, we need to make a table in the database. Ecto supports migrations for tracking modifications to your database schema. Let's generate a migration to create a users table:

mix ecto.gen.migration create_users
vim priv/repo/migrations/20170414055712_create_users.exs
defmodule FirestormData.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :username, :string
      add :name, :string
      add :email, :string


Now we can run the migration to create the table we described:

mix ecto.migrate

Next, we'll create a Schema. Our schema is an Elixir representation of the data that this table stores. I'm going to create a schema directory to store these:

mkdir lib/firestorm_data/schema
vim lib/firestorm_data/schema/user.ex
defmodule FirestormData.User do
  use Ecto.Schema

  schema "users" do
    field :username, :string
    field :name, :string
    field :email, :string


Here we're saying that our User schema maps to the users table, and describing the fields again for it. This will create a FirestormData.User struct, among other things.

Let's drop into iex and insert a couple of Users into the database:

iex -S mix
alias FirestormData.{User, Repo}
josh = %User{name: "Josh Adams"}
adam = %User{name: "Adam Dill"}
Repo.insert josh
Repo.insert adam

Now we have a couple of users in our database table. Let's look at basic querying next:

# We'll generate a query to fetch the first user from the `users` table
User |> Ecto.Query.first
# => #Ecto.Query<from u in FirestormData.User, order_by: [asc: u.id], limit: 1>

This just generated a data structure that defines our query - it does not execute it. That's because, since it hasn't discussed our Repo yet, it has no clue where to query it at all. We can pass this query to Repo.one to fetch a single record from the database.

User |> Ecto.Query.first |> Repo.one
# 01:09:16.093 [debug] QUERY OK source="users" db=2.2ms
# SELECT u0."id", u0."username", u0."name", u0."email", u0."inserted_at", u0."updated_at" FROM "users" AS u0 ORDER BY u0."id" LIMIT 1 []

# => %FirestormData.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
       email: nil, id: 1, inserted_at: ~N[2017-04-14 06:07:15.539965],
       name: "Josh Adams", updated_at: ~N[2017-04-14 06:07:15.544949], username: nil}

Here, we can see the record coming back from the database.


That's all we have time for today. In today's episode, we:

  • set up Ecto from scratch on a new project
  • configured it per-environment
  • created a migration
  • created a corresponding schema
  • inserted records
  • retrieved records

We haven't really scratched the surface yet. Later we'll look at validations, more complex queries, and associations. I hope you enjoyed it. See you soon!