DEV Community

George Arrowsmith
George Arrowsmith

Posted on • Updated on

ActiveRecord vs. Ecto - querying the database

#ecto #rails #phoenix #activerecord

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")
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Finding 1 record

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

# ActiveRecord
Person.find(1)
Enter fullscreen mode Exit fullscreen mode

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

# Ecto
Repo.get(Person, 1)
Repo.get!(Person, 1)
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode 
# Ecto
Repo.get_by(Person, first_name: "Jimi", last_name: "Hendrix")
Repo.get_by!(Person, first_name: "Jimi", last_name: "Hendrix")
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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

import Ecto.Query

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

(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
Enter fullscreen mode Exit fullscreen mode

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

Person
|> where(first_name: "Jimi")
|> Repo.all
Enter fullscreen mode Exit fullscreen mode

Existence 

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

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

Comparison 

# ActiveRecord
Person.where('age >= ?', 35)
Enter fullscreen mode Exit fullscreen mode 
# Ecto (declarative)
query = from p in Person,
        where: p.age >= 35
Repo.all(query)

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

Inclusion 

# ActiveRecord
Person.where(last_name: ["Hendrix", "Clapton"])
Enter fullscreen mode Exit fullscreen mode 
# 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
Enter fullscreen mode Exit fullscreen mode

Negation 

# ActiveRecord
Person.where.not(last_name: "Smith")
Enter fullscreen mode Exit fullscreen mode 
# 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
Enter fullscreen mode Exit fullscreen mode

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

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

OR 

# ActiveRecord
Person.where(last_name: "Hendrix")
    .or(Person.where(last_name: "Clapton"))
Enter fullscreen mode Exit fullscreen mode 
# 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
Enter fullscreen mode Exit fullscreen mode

Ordering 

# ActiveRecord
Person.order(:age) # ASC by default
Person.order(age: :desc)
Enter fullscreen mode Exit fullscreen mode 
# 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode 
query = from p in Person, select: [:first_name]
Repo.all(query)

Person
|> select([:first_name])
|> Repo.all
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode 
# Ecto (declarative)
query = from p in Person, limit: 10,
Repo.all(query)

# Ecto (functional)
Person
|> limit(10)
|> Repo.all
Enter fullscreen mode Exit fullscreen mode

Use limit in combination with offset to get pagination:

# ActiveRecord
Person.order(:last_name).limit(20).offset(80)
Enter fullscreen mode Exit fullscreen mode 
# 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
Enter fullscreen mode Exit fullscreen mode

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

# ActiveRecord
Person.order(:last_name).first
Person.order(:last_name).last
Enter fullscreen mode Exit fullscreen mode 
# Ecto
Person
|> order_by(:last_name)
|> first
|> Repo.one

Person
|> order_by(:last_name)
|> last
|> Repo.one
Enter fullscreen mode Exit fullscreen mode

Aggregates

To count all records:

# ActiveRecord
Person.count
Enter fullscreen mode Exit fullscreen mode 
# Ecto
Repo.aggregate(Person, :count)
Enter fullscreen mode Exit fullscreen mode

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 }
Enter fullscreen mode Exit fullscreen mode 
# Ecto
Person
|> group_by(:first_name)
|> select([p], [p.first_name, count(p.id)])
#=> [["Jack", 1], ["John", 2]]
Enter fullscreen mode Exit fullscreen mode

Uniqueness

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

# ActiveRecord
Person.distinct.pluck(:title)
#=> ["Jack", "John"]
Enter fullscreen mode Exit fullscreen mode 
# 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"]
Enter fullscreen mode Exit fullscreen mode

To count the number of distinct items:

# ActiveRecord
Person.distinct.count(:first_name)
#=> 2
Enter fullscreen mode Exit fullscreen mode 
# 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"]
Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Collapse
 
dkuku profile image
Daniel Kukula •

Useful post, some improvements you can make:
you can shorten the first/last example to

Person
|> last(:last_name)
|> Repo.one
Enter fullscreen mode Exit fullscreen mode

hexdocs.pm/ecto/Ecto.Query.html#fi...

To get the same result when grouping you need to select into a tuple and pass the data to Map.new():

Person
|> group_by(:first_name)
|> select([p], {p.first_name, count(p.id)})
|> Repo.all()
|> Map.new()
Enter fullscreen mode Exit fullscreen mode

For counting distinct values do:

Person
|> select([p], count(p.first_name, :distinct))
|> Repo.one()
Enter fullscreen mode Exit fullscreen mode

and I think

query = from p in Person, select: count(p.first_name, :distinct)
Repo.one(query)
Enter fullscreen mode Exit fullscreen mode

hexdocs.pm/ecto/Ecto.Query.API.htm...

Read next

viktor_eio profile image

What is Rails ActiveRecord?

Viktor Emilov -

lucianghinda profile image

Code Design - What would you choose?

Lucian Ghinda -

jetthoughts-dev profile image

Working with LLMs in Ruby on Rails: A Simple Guide

JetThoughts Dev -

ugifractal profile image

redis ACTION REQUIRED: Version approaching end of life

sugiarto -