Ecto: From the Ground Up

ElixirConf 2018

Darin Wilson / Zachary Berkompas

Ecto

  • Repo

  • Query

  • Schema

  • Changeset

  • Multi

Repo

The Repository Pattern

artist = Artist.get(1)

artist.name = "Miles Davis"

artist.save

Active Record Pattern

The Repository Pattern

  • Centralized class/module connects to DB

  • Database operations sent to the Repository

artist = Repo.get(Artist, 1)

changeset =
  Ecto.Changeset.change(artist, name: "Miles Davis")

Repo.update(changeset)

Repository Pattern

artist = Repo.get(Artist, 1)

changeset =
  Ecto.Changeset.change(artist, 
    name: "Miles Davis")

Repo.update(changeset)

Active Record

artist = Artist.get(1)

artist.name = "Miles Davis"

artist.save

Repository

artist = Repo.get(Artist, 1)

changeset =
  Ecto.Changeset.change(artist, 
    name: "Miles Davis")

Repo.update(changeset)

Active Record

artist = Artist.get(1)

artist.name = "Miles Davis"

artist.save

Repository

artist = Repo.get(Artist, 1)

changeset =
  Ecto.Changeset.change(artist, 
    name: "Miles Davis")

Repo.update(changeset)

Active Record

artist = Artist.get(1)

artist.name = "Miles Davis"

artist.save

Repository

# lib/my_app/repo.ex
defmodule MyApp.Repo

  use Ecto.Repo, otp_app: :my_app

end

Typical Use

# lib/my_app/repo.ex
defmodule MyApp.Repo

  use Ecto.Repo, otp_app: :my_app

end

# config/config.exs
config :my_app, MyApp.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "my_app_db",
  username: "postgres",
  password: "postgres",
  hostname: "localhost"

Typical Use

Work directly with the data

The *all functions

insert_all

update_all

delete_all

all
alias MyApp.Repo

Repo.insert_all("artists", [[name: "John Coltrane"]])
=> {1, nil}

Inserts

Repo.insert_all("artists", [[name: "John Coltrane"]])
=> {1, nil}

Repo.insert_all("artists", [[name: "John Coltrane"], 
  [name: "Max Roach"]])
=> {2, nil}

Inserts

Repo.insert_all("artists", [[name: "John Coltrane"]])
=> {1, nil}

Repo.insert_all("artists", [[name: "John Coltrane"], 
  [name: "Max Roach"]])
=> {2, nil}


Repo.insert_all("artists", [%{name: "John Coltrane"}, 
  %{name: "Max Roach"}])
=> {2, nil}

Inserts

Repo.update_all("artists", 
  set: [updated_at: Ecto.DateTime.utc])
=> {75, nil}

Updates

Repo.delete_all("artists")
=> {75, nil}

Deletes

Ecto.Adapters.SQL.query(Repo, 
  "select * from artists where id=$1", [1])

=> {:ok,
 %Postgrex.Result{columns: ["id", "name", "inserted_at", "updated_at"],
  command: :select, connection_id: 10467, num_rows: 1,
  rows: [[1, "Miles Davis", {{2016, 8, 25}, {7, 20, 34, 0}},
    {{2016, 8, 25}, {7, 20, 34, 0}}]]}}

Queries

Repo.insert_all("artists", [[name: "John Coltrane"]])


Repo.update_all("artists", 
  set: [updated_at: Ecto.DateTime.utc])


Repo.delete_all("artists")

Exercises

Queries

Queries

The Basics

 

SELECT t.id, t.title, a.title
  FROM tracks t
  JOIN albums a ON t.album_id = a.id
  WHERE t.duration > 900;
query = from t in "tracks",
  join: a in "albums", on: t.album_id == a.id, 
  where: t.duration > 900,
  select: [t.id, t.title, a.title]

The Basics

 

SELECT t.id, t.title, a.title
  FROM tracks t
  JOIN albums a ON t.album_id = a.id
  WHERE t.duration > 900;
query = 
  "tracks"
  |> join(:inner, [t], a in "albums", t.album_id == a.id) 
  |> where([t,a], t.duration > 900)
  |> select([t,a], [t.id, t.title, a.title])

Inspecting SQL

query = from "artists", select: [:name] 

Ecto.Adapters.SQL.to_sql(:all, Repo, query)
#=> {"SELECT a0.\"name\" FROM \"artists\" AS a0", []}

Inspecting SQL

query = from "artists", select: [:name] 
Repo.all(query)

#=> [["Miles Davis"], ["Bill Evans"], ["Bobby Hutcherson"]]

Start with Where

q = from "artists", 
  where: [name: "Bill Evans"], 
  select: [:id, :name] 

Repo.all(q)

#=> [[2, "Bill Evans"]]


# What if we want to dynamically set the name?

artist_name = "Taylor Swift"

q = from "artists", 
  where: [name: ^artist_name], 
  select: [:id, :name]


Query Bindings

# This doesn't work

q = from "artists", where: name == "Bill Evans", select: [:id, :name]

# Now this works :)

q = from a in "artists", where: a.name == "Bill Evans", select: [:id, :name]


Query Bindings

# like statements

q = from a in "artists", where: like(a.name, "Miles%"), select: [:id, :name] 

# checking for null

q = from a in "artists", where: is_nil(a.name), select: [:id, :name] 

# checking for not null

q = from a in "artists", where: not is_nil(a.name), select: [:id, :name]

# date comparision - this finds artists added more than 1 year ago

q = from a in "artists", where: a.inserted_at < ago(1, "year"), select: [:id, :name]

Fragments

q = from a in "artists",
  where: fragment("lower(?)", a.name) == "miles davis", select: [:id, :name]


Ecto.Adapters.SQL.to_sql(:all, Repo, q)
#=> {"SELECT a0.\"id\", a0.\"name\" FROM \"artists\" AS a0
#=> WHERE (lower(a0.\"name\") = 'miles davis')",

Ordering & Grouping

# Select all artists names ordered alphabetically 

q = from a in "artists", select: [a.name], order_by: a.name 

Repo.all(q)

#=> [["Bill Evans"], ["Bobby Hutcherson"], ["Miles Davis"]]

# Order by results descending.

q = from a in "artists", select: [a.name], order_by: [desc: a.name] 

Repo.all(q)

#=> [["Miles Davis"], ["Bobby Hutcherson"], ["Bill Evans"]]

Ordering & Grouping

# Grouping tracks by album and summing their times played

q = from t in "tracks", select: [t.album_id, sum(t.number_of_plays)], 
  group_by: t.album_id

Repo.all(q)

#=> [[4, 2540], [1, 2619], [5, 3057], [3, 2528], [2, 4491]]

Joins

Joins

# Select all tracks that have a duration longer than 15 minutes.

q = from t in "tracks",
  join: a in "albums", on: t.album_id == a.id, 
  where: t.duration > 900,
  select: [a.title, t.title]

Repo.all(q)

#=> [["Cookin' At The Plugged Nickel", "No Blues"],
#=> ["Cookin' At The Plugged Nickel", "If I Were A Bell"]]

Joins

# You can use the join keyword more than once to join in multiple tables!

q = from t in "tracks",
  join: a in "albums", on: t.album_id == a.id, 
  join: ar in "artists", on: a.artist_id == ar.id,
  where: t.duration > 900,
  select: %{album: a.title, track: t.title, artist: ar.name}

Repo.all(q)

#=> [%{album: "Cookin' At The Plugged Nickel", artist: "Miles Davis",
#=> track: "If I Were A Bell"},
#=> %{album: "Cookin' At The Plugged Nickel", artist: "Miles Davis",
#=> track: "No Blues"},

Composing Queries

# First query to get all albums by Miles Davis.

q = from a in "albums",
  join: ar in "artists", on: a.artist_id == ar.id, 
  where: ar.name == "Miles Davis",
  select: [a.title]

Repo.all(q)
#=> [["Cookin' At The Plugged Nickel"], ["Kind Of Blue"]]

# Then we want to list all the tracks on Miles Davis's albums

q = from a in "albums",
  join: ar in "artists", on: a.artist_id == ar.id, 
  join: t in "tracks", on: t.album_id == a.id, 
  where: ar.name == "Miles Davis",
  select: [t.title]


# Reuse the first query!

albums_by_miles = from a in "albums",
  join: ar in "artists", on: a.artist_id == ar.id, 
  where: ar.name == "Miles Davis"

q = from [a,ar] in albums_by_miles,
  join: t in "tracks", on: t.album_id == a.id,
  select: [t.title]

Composing Queries

# You can also use functions to compose queries.

def albums_by_artist(artist_name) do 
  from a in "albums",
    join: ar in "artists", on: a.artist_id == ar.id,
    where: ar.name == ^artist_name 
end

def with_tracks_longer_than(query, duration) do 
  from a in query,
    join: t in "tracks", on: t.album_id == a.id, 
    where: t.duration > ^duration,
    distinct: true
end

q = 
  albums_by_artist("Taylor Swift")
  |> with_tracks_longer_than(720) 

Other Uses for Queries

# Update tracks with the title of "Autum Leaves"

q = from t in "tracks", where: t.title == "Autum Leaves" 

Repo.update_all(q, set: [title: "Autumn Leaves"])

#=> [{1, nil}]

# Delete all tracks with the title of "Autum Leaves" 

from(t in "tracks", where: t.title == "Autum Leaves")
|> Repo.delete_all

Exercise Time!

Schemas

Creating Schemas

# You can create an elixir struct for tracks separate from Ecto like so:

defmodule MusicDB.Track do
  defstruct [:id, :title, :duration, :index, :number_of_plays]
end

# With Ecto, you use the schema macro to define your struct:

defmodule MusicDB.Track do 
  use Ecto.Schema

  schema "tracks" do
    field :title, :string
    field :duration, :integer
    field :index, :integer
    field :number_of_plays, :integer 
    timestamps()
  end 
end

# We can use %MusicDB.Track{} to create new instances of the above struct.

Data Types

Ecto Type Elixir Type
:id integer
:binary_id binary
:integer integer
:float float
:boolean boolean
:string UTF-8 encoded string 
:binary binary
{:array, inner_type}  list
:map map
{:map, inner_type}  map
:decimal Decimal
:date Date
:time Time
:naive_datetime NaiveDateTime 
:utc_datetime DateTime

Schemas in Queries

# Schema-less Query

q = from "artists", where: [name: "Taylor Swift"], select: [:name]

Repo.all(q)
#=> [["Taylor Swift"]]

# Converted to use the Artist schema

q = from Artist, where: [name: "Taylor Swift"]

# You no longer need to include the select, since results will be loaded into %Artist{} structs.

Inserting & Deleting w/ Schemas

 

# Schema-less insert

Repo.insert_all("artists", [[name: "John Coltrane"]]) 
#=> {1, nil}

# Inserting a schema

Repo.insert(%Artist{name: "John Coltrane"})
#=> {:ok, %MusicDB.Artist{__meta__: #Ecto.Schema.Metadata<:loaded, "artists">, 
#=> id: 4, name: "John Coltrane", ...}

# Schema-less deletion 

from(t in "tracks", where: t.title == "Autum Leaves") 
|> Repo.delete_all

# Deleting a schema

track = Repo.get_by(Track, title: "The Moontrane")
Repo.delete(track)

#=> {:ok, %MusicDB.Track{__meta__: #Ecto.Schema.Metadata<:deleted, "tracks">, #=> id: 28, title: "The Moontrane", ...}

More Exercises!

Associations

defmodule MusicDB.Album do 
  use Ecto.Schema

  schema "albums" do
    field :title, :string 
    field :release_date, :date

    has_many :tracks, MusicDB.Track
  end
end

One-to-many

                                                                                                      
                                       +-----------------------+                                      
                                       |                       |                                      
                                       |         Artist        |                                      
                                       |                       |                                      
                                       +-----------------------+                                      
                                                   |                                                  
                                                   |                                                  
                                                   |                                                  
                                                   |                                                  
     ----------------------------------------------|--------------------------------------------      
     |                     |                       |                     |                     |      
     |                     |                       |                     |                     |      
+-----------+         +-----------+           +-----------+         +-----------+        +-----------+
|           |         |           |           |           |         |           |        |           |
|   Album   |         |   Album   |           |   Album   |         |   Album   |        |   Album   |
|           |         |           |           |           |         |           |        |           |
+-----------+         +-----------+           +-----------+         +-----------+        +-----------+
                                                                                                      

One-to-many

schema "tracks" do
  field :title, :string 
  # other fields here...

  belongs_to :album, MusicDB.Album 
end

belongs_to

# in album.ex
schema "albums" do
  # field definitions here...

  has_many :tracks, MusicDB.Track
  belongs_to :artist, MusicDB.Artist
end

# in artist.ex
schema "artists" do
  # field definitions here...

  has_many :albums, MusicDB.Album 
end

Album to Artist

Nested Associations

schema "artists" do
  # field definitions here...
  has_many :albums, MusicDB.Album
  has_many :tracks, through: [:albums, :tracks]
end

Nested Associations

Queries

Queries

 

Many-To-Many

Many-To-Many

# in album.ex
schema "albums" do
  # field definitions here...
  many_to_many :genres, MusicDB.Genre, join_through: MusicDB.AlbumGenre
end

# in genre.ex
schema "genres" do
  # field definitions here...
  many_to_many :albums, MusicDB.Album, join_through: MusicDB.AlbumGenre
end

# in album_genre.ex
schema "albums_genres" do
  # field definitions here...
  belongs_to :albums, MusicDB.Album
  belongs_to :genres, MusicDB.Genre
end

Many-To-Many

# in album.ex
schema "albums" do
  # field definitions here...
  many_to_many :genres, MusicDB.Genre, join_through: "albums_genres"
end

# in genre.ex
schema "genres" do
  # field definitions here...
  many_to_many :albums, MusicDB.Album, join_through: "albums_genres"
end

Many-To-Many

Loading Associations

album = Repo.one(
  from a in Album,
  where: a.title == "Kind Of Blue"
)

Loading Associations

album = Repo.one(
  from a in Album,
  where: a.title == "Kind Of Blue"
)

album.tracks

Loading Associations

album = Repo.one(
  from a in Album,
  where: a.title == "Kind Of Blue"
)

album.tracks

#Ecto.Association.NotLoaded<association :tracks is 
#  not loaded>

Loading Associations

album = Repo.one(
  from a in Album,
  where: a.title == "Kind Of Blue"
)

Loading Associations

album = Repo.one(
  from a in Album,
  where: a.title == "Kind Of Blue",
  preload: :tracks
)

Loading Associations

album = Repo.one(
  from a in Album,
  where: a.title == "Kind Of Blue",
) |> Repo.preload(:tracks)

Loading Associations

album = Repo.one(
  from a in Album,
  where: a.title == "Kind Of Blue",
)

tracks_query = Ecto.assoc(album, :tracks)
tracks = Repo.all(tracks_query)

Loading Associations

albums = Repo.all(
  from a in Album,
  where: a.artist_id == 1,
)

tracks_query = Ecto.assoc(albums, :tracks)
tracks = Repo.all(tracks_query)

Loading Associations

Embedded Schemas

# in album.ex
schema "albums" do
  # field definitions here...

  has_many :tracks, MusicDB.Track
end

# in track.ex
schema "tracks" do
  # field definitions here...

  belongs_to :album, MusicDB.Album 
end

Handling Deletes

# in album.ex
schema "albums" do
  # field definitions here...

  has_many :tracks, MusicDB.Track, on_delete: :delete_all
end

# in track.ex
schema "tracks" do
  # field definitions here...

  belongs_to :album, MusicDB.Album 
end

Handling Deletes

Inserts With Schemas

Repo.insert_all("artists", [[name: "John Coltrane"]])
#=> {1, nil}

Inserts With Schemas

Repo.insert_all("artists", [[name: "John Coltrane"]])
#=> {1, nil}

Repo.insert(%Artist{name: "John Coltrane"})
#=> {:ok, %MusicDB.Artist{ ... }}

Inserts With Schemas

Repo.insert(
  %Artist{
    name: "John Coltrane", 
    albums: [
      %Album{
        title: "A Love Supreme"
      } 
    ]
  } 
)

Nested Inserts

Repo.insert(
  %Artist{
    name: "John Coltrane",
    albums: [
      %Album{
        title: "A Love Supreme",
        tracks: [
          %Track{title: "Part 1: Acknowledgement", index: 1},
          %Track{title: "Part 2: Resolution", index: 2},
          %Track{title: "Part 3: Pursuance", index: 3},
          %Track{title: "Part 4: Psalm", index: 4},
        ],
        genres: [
          %Genre{name: "spiritual jazz"},
        ]
      }
    ]
  }
)

Nested Inserts

Exercises

New "releases" table/schema

  • album -> has_many -> releases

  • "Giant Steps" (album)

    • "Giant Steps" (original release)

    • "Giant Steps (remastered)"

    • "Giant Steps Collector's Edition"

    • etc.

schema "albums" do
  ...
  has_many :tracks, MusicDB.Track, on_delete: :delete_all
  belongs_to :artist, MusicDB.Artist
end


Repo.insert(
  %Artist{
    name: "John Coltrane", 
    albums: [
      %Album{
        title: "A Love Supreme"
      } 
    ]
  } 
)


albums = Repo.all(from a in Album, preload: :tracks)

Exercises

Infinite Red Academy

https://academy.infinite.red/elixir

Ecto Workshop - ElixirConf 2018

By Infinite Red

Ecto Workshop - ElixirConf 2018

1-day workshop, presented by Darin & Zach

  • 78
Loading comments...

More from Infinite Red