Understanding Elixir’s Ecto Querying DSL: The Basics

This article will take a look at the basics of querying with Ecto, a domain-specific language (DSL) for writing queries and interacting with databases in Elixir. This will involve going through joins, associations, aggregation functions, and so on.

A basic knowledge of Elixir is assumed, and knowing the fundamentals of Ecto beforehand will help too.

Ectoing Application

All of the examples in this article series can be run against my demonstrative Ectoing application. I highly encourage you to set this up (as described below) and run all of the queries as you read along. This will help to solidify your understanding by playing around with the examples to see what works and what doesn’t work.

Let’s quickly set up the application:

git clone https://github.com/tpunt/ectoing
cd ectoing
mix deps.get

# don't forget to update the credentials in config/config.exs
mix ecto.create
mix ecto.migrate

# populate the database with some dummy data
mix run priv/repo/seeds.exs

(I’ve chosen to use MySQL for this. The examples throughout this article should work uniformly across all supported databases, so whilst the Mariaex dependency could be switched out to use another database, I would advise against this. This is because some examples in the next article will contain MySQL-dependent code.)

The database structure is as follows:

Basic Querying

Let’s start with some basic queries to get a feel for Ecto’s querying DSL.

Note that whilst all examples can be executed in the Elixir shell (via iex -S mix in the ectoing base directory), the Ecto.Query module will have to be imported first. This will make all of the querying DSL macros (such as from) available to us whilst working in the shell.

Let’s start with the most trivial of queries — selecting all users with their complete records:

SELECT * FROM users;

query = Ectoing.User

Ectoing.Repo.all query

(All examples will firstly show the SQL syntax, and then how this translates into the Ecto query syntax.)

To get the complete records for all users, we simply perform a query on the desired model (in this case, it’s Ectoing.User). This works because Ecto will, by default, return all of the fields defined in the corresponding model’s schema definition if its select clause is omitted. Ecto’s Repo.all/2 function is then used to execute the query, where it fetches a list of results (since we expect more than one result from the query). Let’s take a quick look at one of these returned records:

[%Ectoing.User{__meta__: #Ecto.Schema.Metadata<:loaded>,
  firstname: "Thomas",
  friends_of: #Ecto.Association.NotLoaded<association :friends_of is not loaded>,
  friends_with: #Ecto.Association.NotLoaded<association :friends_with is not loaded>,
  id: 1,
  inserted_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,
  messages: #Ecto.Association.NotLoaded<association :messages is not loaded>,
  surname: "Punt",
  updated_at: #Ecto.DateTime<2016-05-15T20:23:58Z>,
  username: "tpunt"},
  ...
]

A struct of type Ectoing.User has been returned, where members of the struct match the field names for the Ectoing.User model. Notably, we also have some associations (friend, friends, and messages) not loaded that have been embedded in there. We’ll be covering Ecto’s associations in more detail later, but for now just note that they are Ecto’s solution for managing the loading of foreign key relationships across models.

Quite often though, we won’t want to fetch the whole record. So let’s just select a user’s first name and last name:

SELECT firstname, surname FROM users;

query = from u in Ectoing.User,
  select: [u.firstname, u.surname]

Ectoing.Repo.all query

In the from macro, we specify the model (Ectoing.User) to select data from and use a convenience variable (u) to reference it. The select clause is then used to select the columns u.firstname and u.surname as a list, making the resulting value a list of two element lists:

[["Thomas", "Punt"], ["Liam", "Mann"], ["John", "Doe"], ["Jane", "Doe"],
 ["Bruno", "Škvorc"]]

The select clause enables us to pattern match inside of it, so whilst square brackets were used above to return the columns as a list, we could quite easily have returned a list of tuples or a list of maps instead:

query = from u in Ectoing.User,
  select: {u.firstname, u.surname}

Ectoing.Repo.all query

# result
[{"Thomas", "Punt"}, {"Liam", "Mann"}, {"John", "Doe"}, {"Jane", "Doe"},
 {"Bruno", "Škvorc"}]

query = from u in Ectoing.User,
  select: %{firstname: u.firstname, surname: u.surname}

Ectoing.Repo.all query

# result
[%{firstname: "Thomas", surname: "Punt"}, %{firstname: "Liam", surname: "Mann"},
 %{firstname: "John", surname: "Doe"}, %{firstname: "Jane", surname: "Doe"},
 %{firstname: "Bruno", surname: "Škvorc"}]

Querying API Styles

So far, we’ve been using the keywords query syntax for our queries. This is generally the most prevalent syntax, but occasionally you’ll see Ecto’s alternative querying API syntax: the macro syntax. Let’s take a look at this alternative syntax by translating the above query to select the first names and surnames of all users:

query = (Ectoing.User                   
|> select([u], [u.firstname, u.surname]))

Ectoing.Repo.all query

(The parentheses encapsulating the whole query aren’t needed, but have been included because they enable for the code to be easily copied and pasted directly into IEx.)

This time, we pass the model as the first argument to select/3, where the second argument specifies the binding variable for the model (in this case, it’s u). The third argument selects the columns, which can (again) be pattern matched to return tuples or maps instead of lists.

Just so that we can get used to both syntax styles, I’ll demonstrate both from now on alongside the SQL code for each query.

Restrictive Querying and Customizing Result Sets

Almost always, we will want to select only a subset of records from the database. This can be done by a range of functions that closely resemble their clause counterparts in SQL. These include where (in combination with the comparison operators in and like (there is an ilike, though it has been deprecated in Ecto 2.0)), limit, offset, and distinct.

For example, we can select all users with surnames equal to “doe” with the following:

SELECT * FROM users WHERE surname = "doe";

surname = "doe"

# Keywords query syntax
query = from u in Ectoing.User,
  where: u.surname == ^surname

# Macro syntax
query = (Ectoing.User
|> where([u], u.surname == ^surname))

Ectoing.Repo.all query

The above comparison is case-insensitive and returns back both of the John Doe and Jane Doe users. Notice the usage of the pin operator on the surname variable: this is to (explicitly) interpolate variables into the query. Such interpolated variables have their values automatically casted to the underlying column type defined in the model’s schema definition.

Let’s try something a little more complex by selecting all distinct surnames, ordering them, and then limiting the result set:

SELECT DISTINCT surname FROM users LIMIT 3 ORDER BY surname;

# Keywords query syntax
query = from u in Ectoing.User,
  select: u.surname,
  distinct: true,
  limit: 3,
  order_by: u.surname

# Macro syntax
query = (Ectoing.User
|> select([u], u.surname)
|> distinct(true)
|> limit(3)
|> order_by([u], u.surname))

Ectoing.Repo.all query
# ["Doe", "Mann", "Punt"]

The distinct/3 function above will select distinct values according to the column(s) specified in the select/3 function. distinct/3 also allows for columns to be directly passed to it (i.e. distinct: u.surname), enabling for certain columns to be selected by distinctly, and then for alternative columns to be returned (via select/3). Since MySQL does not support the DISTINCT ON syntax, however, we are unable to do this when using the MySQL adapter (the Postgres adapter allows this, for example). The final order_by/3 then orders the result set (in ascending order, by default) according to the passed columns.

For a complete list of operators and functions supported in Ecto’s querying API, check out its documentation. Also, for a complete list of literals that can be used in Ecto queries, see its query expressions documentation.

Aggregation Queries

Ecto provides us with a number of aggregation functions that we’d typically find in SQL, including: group_by, having, count, avg, sum, min, and max.

Let’s try a couple of these out by selecting users who have an average rating of 4 or greater by their friends:

SELECT friend_id, avg(friend_rating) AS avg_rating
FROM friends
GROUP BY friend_id
    HAVING avg_rating >= 4
ORDER BY avg_rating DESC;

# Keywords query syntax
query = from f in Ectoing.Friend,
  select: %{friend_id: f.friend_id, avg_rating: avg(f.friend_rating)},
  group_by: f.friend_id,
  having: avg(f.friend_rating) >= 4,
  order_by: [desc: avg(f.friend_rating)]

# Macro syntax
query = (Ectoing.Friend
|> select([f], %{friend_id: f.friend_id, avg_rating: avg(f.friend_rating)})
|> group_by([f], f.friend_id)
|> having([f], avg(f.friend_rating) >= 4)
|> order_by([f], [desc: avg(f.friend_rating)]))

Ectoing.Repo.all query
# [%{avg_rating: #Decimal<4.0000>, friend_id: 3},
#  %{avg_rating: #Decimal<4.0000>, friend_id: 5}]

It’s a little less elegant from Ecto’s perspective, since we can’t alias the average rating column — requiring us to use the same column calculation three times in the query. But as with the previous examples, Ecto’s querying DSL maps very closely to raw SQL, which makes it quite easy to use.

Conclusion

We’ve covered the absolute fundamentals of Ecto’s querying DSL in this article, showing how closely it maps to raw SQL. In the next article, Elixir’s Ecto Querying DSL: Beyond the Basics, we cover some more complex topics, including joins, composition queries, SQL fragment injection, loading associations, and query prefixing.

Frequently Asked Questions (FAQs) about Elixir’s Ecto Querying DSL