In my previous post I briefly covered some lessons I’d learned while building a (kind of real) web app in Elixir. Today, I’d like to take an in-depth look at composable queries in Ecto. First, a brief introduction to Ecto.

What is Ecto?

I think of Ecto as a light-weight ORM. Ecto uses Elixir structs to represent database tables and provides a DSL for building and executing database queries. Because I’m boring, we’re going to use the ages-old “post has many comments” example to demonstrate Ecto’s capabilities. We’ll assume we have the following models.

defmodule MyApp.Post do
  use Ecto.Model
  import Ecto.Query

  schema "posts" do
    field :body, :string
    field :published, :boolean
    field :published_at, :datetime
    field :title, :string

    has_many :comments, MyApp.Comment
  end
end

defmodule MyApp.Comment do
  use Ecto.Model
  import Ecto.Query

  schema "comments" do
    field :commenter, :string
    field :title, :string
    field :votes, :integer

    belongs_to :post, MyApp.Post
  end
end

Querying with Ecto

Ecto provides two styles of querying syntax: keyword query syntax and query expressions. Note that in both query styles, the construction of the query is a distinct and separate process from the execution on the query. In each style, a constructed query is passed to the application’s Repo to be executed.

Keyword Query Syntax

Keyword query syntax closely mirrors SQL and feels a lot like LINQ. I’ll demonstrate using some example queries.

Select all posts

MyApp.Repo.all(
  from p in MyApp.Post,
  select: p
)

Select all published posts

MyApp.Repo.all(
  from p in MyApp.Post,
   where: p.published == true,
  select: p
)

Select all comments for post 1

MyApp.Repo.all(
  from c in MyApp.Comment,
    join: p in assoc(c, :post),
   where: p.id == 1,
  select: c
)

Query Expressions

Query expressions follow the pipeline concept often seen in Elixir APIs. It is important to note that these queries all start with the model module (e.g. MyApp.Post). The model itself is a queryable object that represents all items in the given table. Here are the same examples using query expressions.

Select all posts

MyApp.Post |> MyApp.Repo.all

Select all published posts

MyApp.Post
|> where([p], p.published == true)
|> MyApp.Repo.all

Select all comments for post 1

MyApp.Comment
|> join(:left, [c], p in assoc(c, :post))
|> where([_, p], p.id == 1)
|> select([c, _], c)
|> MyApp.Repo.all

Query Composition

It is easy to see how queries built in the query expression style can be composed - you simply add new constraints to your pipeline. It is not immediately obvious how to compose queries built with the keyword query syntax nor how to compose queries of differing types.

First, we must understand an important feature of the keyword query syntax. In the from clause, the token after the in can be any queryable object and other queries are queryable! Here’s an example.

query  = from p in MyApp.Post,
         select: p

query2 = from p in query,
         where: p.published == true

MyApp.Repo.all(query2)

Knowing this, we can now mix and match query syntax types.

query  = from p in MyApp.Post,
         select: p

query |> where([p], p.published == true) |> MyApp.Repo.all

Putting It All Together

Now, let’s add some functions to our Ecto models with nice, descriptive names.

defmodule MyApp.Post do
  use Ecto.Model
  import Ecto.Query

  schema "posts" do
    field :body, :string
    field :published, :boolean
    field :published_at, :datetime
    field :title, :string

    has_many :comments, MyApp.Comment
  end

  def published(query) do
    from p in query,
    where: p.published == true
  end

  def sorted(query) do
    from p in query,
    order_by: [desc: p.published_at]
  end
end

defmodule MyApp.Comment do
  use Ecto.Model
  import Ecto.Query

  schema "comments" do
    field :commenter, :string
    field :title, :string
    field :votes, :integer

    belongs_to :post, MyApp.Post
  end

  def for_post(query, post) do
    from c in query,
     join: p in assoc(c, :post)
    where: p.id == ^post.id
  end

  def popular(query) do
    query |> where([c], c.votes > 10)
  end
end

I’ve used both styles of querying to show their interchangability when it comes to composition. Now, let’s use our functions to build some queries. Normally, I’d do this type of composition in my Phoenix controllers.

alias MyApp.Post
alias MyApp.Comment

published_posts = Post
|> Post.published
|> MyApp.Repo.all

last_post = Post
|> Post.published
|> Post.sorted
|> MyApp.Repo.one

recent_popular_comments = Comment
|> Comment.for_post(last_post)
|> Comment.popular
|> MyApp.Repo.all

Wrap Up

I hope this gives you an idea of the power and flexibility Ecto provides for extracting reusable query components and composing them to build more complex queries. I’ve found these techniques reduce duplication and complexity while aiding testing.

Update

You can find a talk I gave on this subject at ElixirConf 2015 here.