ActiveRecord vs. Ecto - querying the database

This post is the first in a planned series on Ecto. Its aim is to explain Ecto to developers who are already familiar with Rails and ActiveRecord.

Ecto in a Phoenix app performs roughly the same function as ActiveRecord in a Ruby on Rails app. Both are used to validate data and to query and update the database. However, their design is quite different, and for Rails developers learning Phoenix, Ecto can take some getting used to.

The first post in this series will cover querying the database with Ecto. It's intended as a quick reference for Rails devs who know how to load records from a DB using ActiveRecord, and want to know how to get the same functionality with Ecto in Phoenix.

(PS: if you're a Rails developer learning Phoenix, you can learn much more from the course Phoenix on Rails, a comprehensive guide to Phoenix written for developers who already know Ruby on Rails.)

The Basics

In Rails you query the DB using the class methods of an ActiveRecord model:

Person.all
Person.find(1)
Person.where(last_name: "Smith")

In Phoenix there are no "models". Instead you define the shape of your data within a schema:

defmodule Person do
  use Ecto.Schema

  # The first arg to `schema/1` tells Ecto that the name of the
  # DB table is 'people'. Note that we don't need to specify
  # that 'people' has a primary key column called 'id' because
  # `schema/1` assumes this by default.
  schema "people" do
    field :first_name, :string
    field :last_name, :string
    field :age, :integer
  end
end

Then you query the DB using functions of the Repo module in conjunction with the schema and, for more complex queries, the Ecto.Query module.

Repo.all(Person)
Repo.get(Person, 1)

import Ecto.Query

Repo.all(from p in Person, where: last_name == "Smith")

# Alternative syntax:
Person
|> where(last_name: "Smith")
|> Repo.all

Finding 1 record

The most basic way to retrieve a record is by its primary key.

# ActiveRecord
Person.find(1)

Person.find raises ActiveRecord::RecordNotFound if no record exists with the given PK.

# Ecto
Repo.get(Person, 1)
Repo.get!(Person, 1)

Repo.get returns nil if no record is found; Repo.get! raises Ecto.NoResultsError.

To find a record by its other fields, use find_by in ActiveRecord and Repo.get_by in Ecto:

# ActiveRecord
Person.find_by(first_name: "Jimi", last_name: "Hendrix")
Person.find_by!(first_name: "Jimi", last_name: "Hendrix")

# Ecto
Repo.get_by(Person, first_name: "Jimi", last_name: "Hendrix")
Repo.get_by!(Person, first_name: "Jimi", last_name: "Hendrix")

find_by and Repo.get_by both return nil if no record is found. find_by! and Repo.get_by! will raise an error.

An important difference between ActiveRecord and Ecto is that in Ecto, Repo.get_by and Repo.get_by! will raise an error if they find more than one result. (Specifically, they raise an Ecto.MultipleResultsError.) ActiveRecord's find_by and find_by!, on the other hand, don't care and simply return the first result.

Finding N records

In ActiveRecord you can use where to return all results which match a query:

Person.where(last_name: "Smith")

In Ecto you construct a query using from then pass it to Repo.all:

import Ecto.Query

query = from p in Person,
        where: p.first_name == "Jimi"
Repo.all(query)

Alternatively, instead of from you can use a functional syntax:

import Ecto.Query

Person
|> where([p], p.first_name == "Jimi")
|> Repo.all

(From here on all sample code assumes that Ecto.Query has already been imported.)

The choice of p in the above queries is arbitrary. We could have used any valid Elixir variable name:

Person
|> where([person], person.first_name == "Jimi")
|> Repo.all

For simpler queries you can pass a basic keyword list to where:

Person
|> where(first_name: "Jimi")
|> Repo.all

Existence

# ActiveRecord
Person.exists?(first_name: "Jimi")

# Ecto (declarative)
query = from p in Person, where: p.first_name == "Jimi"
Repo.exists?(query)

# Ecto (functional)
Person
|> where(first_name: "Jimi")
|> Repo.exists?

Comparison

# ActiveRecord
Person.where('age >= ?', 35)

# Ecto (declarative)
query = from p in Person,
        where: p.age >= 35
Repo.all(query)

# Ecto (functional)
Person
|> where([p], p.age >= 35)
|> Repo.all

Inclusion

# ActiveRecord
Person.where(last_name: ["Hendrix", "Clapton"])

# Ecto (declarative)
query = from p in Person,
        where: p.last_name in ["Hendrix", "Clapton"]
Repo.all(query)

# Ecto (functional)
Person 
|> where([p], p.last_name in ["Hendrix", "Clapton"])
|> Repo.all

Negation

# ActiveRecord
Person.where.not(last_name: "Smith")

# Ecto (declarative)
query = from p in Person,
        where: p.last_name != "Smith"
Repo.all(query)

# Ecto (functional)
Person
|> where([p], p.last_name != "Smith")
|> Repo.all

Use Elixir's not keyword to negate more complex queries:

# ActiveRecord
Person.where.not(last_name: ["Hendrix", "Clapton"])

# Ecto (declarative)
query = from p in Person,
        where: p.last_name not in ["Hendrix", "Clapton"]
Repo.all(query)

OR

# ActiveRecord
Person.where(last_name: "Hendrix")
    .or(Person.where(last_name: "Clapton"))

# Ecto (declarative)
query = from p in Person,
        where: [last_name: "Hendrix"],
        or_where: [last_name: "Clapton"]
Repo.all(query)

# Ecto (functional)
Person
|> where(last_name: "Hendrix")
|> or_where(last_name: "Clapton")
|> Repo.all

Ordering

# ActiveRecord
Person.order(:age) # ASC by default
Person.order(age: :desc)

# Ecto (declarative)
query = from p in Person,
        order_by: p.age # or just order_by: :age
Repo.all(query)

query = from p in Person,
        order_by: [desc: p.age] # or order_by: [desc: :age]
Repo.all(query)

# Ecto (functional)
Person
|> order_by(:age)
|> Repo.all

Person
|> order_by(desc: :age)
|> Repo.all

Note that Ecto uses the wording order_by instead of order, which can trip up ActiveRecord devs.

Also note the reversed ordering: in ActiveRecord you write age: :desc but in Ecto you write desc: :age. This is possible in Elixir because Elixir keyword lists can have duplicate keys; so for example it's possible to write desc: multiple times in an order_by query:

Person
|> order_by(desc: :age, desc: :first_name)
|> Repo.all

This wouldn't make sense in Ruby because the second value of the :desc key would overwrite the first value.

Selecting columns

# ActiveRecord
Person.select(:first_name)

query = from p in Person, select: [:first_name]
Repo.all(query)

Person
|> select([:first_name])
|> Repo.all

This returns a list of %Persons where only the first_name attribute is initialized (and the rest are nil.)

Limits

For more complex queries which you expect to return only one result, you can use Repo.one or Repo.one!:

import Ecto.Query

query = from p in Person,
        where: p.age < 18
Repo.one(query) # nil if no results
Repo.one!(query) # error if no results

This searches for people whose age is below 18. It expects to find and return one result - if it finds more than one, it will raise an Ecto.MultipleResultsError. If it finds no results, it returns nil.

To return the first N results regardless of how many exist:

# ActiveRecord
Person.limit(10)

# Ecto (declarative)
query = from p in Person, limit: 10,
Repo.all(query)

# Ecto (functional)
Person
|> limit(10)
|> Repo.all

Use limit in combination with offset to get pagination:

# ActiveRecord
Person.order(:last_name).limit(20).offset(80)

# Ecto (declarative)
query = from p in Person,
        order_by: :last_name,
        limit: 20,
        offset: 80
Repo.all(query)

# Ecto (functional)
Person
|> order_by(:last_name)
|> limit(20)
|> offset(80)
|> Repo.all

To return only the first or last from a set of results:

# ActiveRecord
Person.order(:last_name).first
Person.order(:last_name).last

# Ecto
Person
|> order_by(:last_name)
|> first
|> Repo.one

Person
|> order_by(:last_name)
|> last
|> Repo.one

Aggregates

To count all records:

# ActiveRecord
Person.count

# Ecto
Repo.aggregate(Person, :count)

Suppose we have two people with first name "John" and one with first name "Jack" in our DB. Then:

# ActiveRecord
Person.group(:first_name).count
#=> { "Jack" => 1, "John" => 2 }

# Ecto
Person
|> group_by(:first_name)
|> select([p], [p.first_name, count(p.id)])
#=> [["Jack", 1], ["John", 2]]

Uniqueness

Suppose again that our DB contains two people called "John" and one caled "Jack". Then:

# ActiveRecord
Person.distinct.pluck(:title)
#=> ["Jack", "John"]

# Ecto (declarative)
query = from p in Person, select: p.first_name, distinct: true
Repo.all(query)
#=> ["Jack", "John"]

# Ecto (functional)
Person
|> select([p], p.first_name)
|> distinct(true)
|> Repo.all
#=> ["Jack", "John"]

To count the number of distinct items:

# ActiveRecord
Person.distinct.count(:first_name)
#=> 2

# Ecto (declarative)
query = from p in Person, select: p.first_name, distinct: true
Repo.aggregate(query, :count)
#=> ["Jack", "John"]

# Ecto (functional)
person
|> select([p], p.first_name)
|> distinct(true)
|> repo.aggregate(:count)
#=> ["Jack", "John"]

Blog