ActiveRecord vs. Ecto - querying the database
Tags: ecto, rails, phoenix, activerecord
Published: 2022-09-01 16:04:28

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](https://hexdocs.pm/ecto/Ecto.html) in a Phoenix app performs roughly the same function as [ActiveRecord](https://guides.rubyonrails.org/active_record_basics.html) 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](http://phoenixonrails.com), 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:

```ruby
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**:

```elixir
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.

```elixir
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.

```ruby
# ActiveRecord
Person.find(1)
```

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

```elixir
# 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:

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

```elixir
# 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:

```ruby
Person.where(last_name: "Smith")
```

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

```elixir
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:

```elixir
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:

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

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

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

### Existence

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

```elixir
# 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

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

```elixir
# 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

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

```elixir
# 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

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

```elixir
# 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:

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

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

### OR

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

```elixir
# 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

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

```elixir
# 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:

```elixir
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

```ruby
# ActiveRecord
Person.select(:first_name)
```

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

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

This returns a list of `%Person`s 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!`:

```elixir
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:

```ruby
# ActiveRecord
Person.limit(10)
```

```elixir
# 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:

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

```elixir
# 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:

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

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

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

### Aggregates

To count all records:

```ruby
# ActiveRecord
Person.count
```

```elixir
# Ecto
Repo.aggregate(Person, :count)
```

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

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

```elixir
# 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:

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

```elixir
# 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:

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

```elixir
# 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"]
```