Search⌘ K
AI Features
Home
Courses
Building Database Applications in Elixir with Ecto

Query Basics

Learn how to write basic queries in Ecto using both keyword and macro syntax. Understand how Ecto transforms these queries into SQL and executes them through the Repo module, giving you the ability to interact effectively with relational databases in Elixir.

We'll cover the following...

Query syntax

Let’s start with the basics. We’ll take a quick look at Ecto’s query syntax, then write simple queries, including some that integrate user input.

The Query module uses Elixir macros to create a DSL (domain-specific language) that sits right in our Elixir code. The DSL syntax feels like Elixir, but it’s a little more fluid and makes writing queries feel natural.

SQL based query

For example, here’s a SQL query based on the data model in our sample application.

Elixir
 SELECT t.id, t.title, a.title
    FROM tracks t
    JOIN albums a ON t.album_id = a.id
    WHERE t.duration > 900;

Ecto based query

And here’s that same query written in Ecto:

Elixir
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]

Even if we don’t understand everything these queries do at first, we can probably see the similarities. Most of the keywords are identical, and the expressions are similar.

Macro syntax

Ecto provides two ways to compose queries. The preceding example uses the keyword syntax, but we can also use the macro syntax, which leans heavily on Elixir’s |> operator. Here’s the same query written using the macro syntax:

Elixir
query = "tracks" \
|> join(:inner, [t], a in "albums", on: t.album_id == a.id) \
|> where([t,a], t.duration > 900) \
|> select([t,a], [t.id, t.title, a.title])

Some developers prefer this approach, as the pipe operator makes the code feel more like Elixir, but it’s also more ...