January 23, 2015

Composable Queries with Ecto

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.

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