Building Database Applications in Elixir with Ecto/

...

/

Query Basics

Query Basics

Learn Ecto's query syntax.

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.

Press + to interact
 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:

Press + to interact
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:

Press + to interact
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 ...