Dynamic SQL Queries With Elixir

Building a MongoDB-style Query API for Postgres with Ecto

There are challenges that come up in almost every web application project. One of those challenges is finding a way to let users query your database or filter a given dataset.

Again and again you’ll have to build a UI or an API in which users can compose queries and specify sorting options.

Depending on your use-case, this can quickly become a complex endeavour. How do you even design such a querying API? And more importantly: How do compose those queries in a secure fashion?

This article will introduce you to a little-known feature of Ecto and show you how to use it to build a querying API in the style of MongoDB.

Before You Continue Reading this Article …

This article is based on learnings from building Keila, a free/libre Open Source alternative to MailChimp, written in Elixir. Be sure to check it out at keila.io!

A Quick Word on Designing a Querying API

There are many ways to design a querying API. In fact, there are so many ways that it can easily become an issue of bike-shedding.

In recent years, GraphQL has become a popular choice for many. There is good support for GraphQL in Elixir with Absinthe, but GraphQL requires special tooling and this can actually make things more complex than they need to be.

Another opption is to build a RESTful API with filter support. But there is no commonly accepted standard for this and dealing with more complex filter conditions can quickly become cumbersome. So more bike-shedding lies ahead down this road - best avoid it!

Fortunately, there is no need to reinvent the wheel here. The folks behind MongoDB have come up with a querying standard based on JSON: Query Documents.

Here’s an example from the MongoDB documentation:

db.inventory.find( { $or: [ { status: "A" }, { qty: { $lt: 30 } } ] } )

This is quite easy to understand. We’re looking for inventory entries that have either the status "A" or a quantity of less than 30.

This JSON representations is well-suited for even the most complex of queries, it is extendable, and doesn’t require dedicated tools. All we need is a JSON parser.

And while it was originally designed for MongoDB’s noSQL database, in this article I will show you how to implement it in Elixir with Ecto for Postgres (or MariaDB/MySQL).

Dynamic Queries with Ecto

You probably already known that Ecto queries are composable. Simple queries on a small number of fields can be implemented very easily like this:

filter = %{"artist" => "ABBA", "year" => 1974}

def filter(filter) do
  from(s in Songs)
  |> maybe_filter_artist(filter)
  |> maybe_filter_year(filter)
  |> Repo.all()
end

defp maybe_filter_artist(query, %{"artist" => artist}),
  do: where(query, [s], s.artist == ^artist)

defp maybe_filter_artist(query, _), do: query

defp maybe_filter_year(query, %{"year" => year}),
  do: where(query, [s], s.year == ^year)

defp maybe_filter_year(query, _), do: query

field/2

The example above is already a decent solution in some use cases.

But as you add more fields, it starts getting very verbose. Fortunately, there’s a way to make it easier: Ecto.Query’s field/2 function. It allows us to use fields without hard-coding their name as we write the query.

With the help of this function, we can simplify the code from the example above and make it much more flexible.

filter = %{
  "artist" => "ABBA",
  "year" => 1974,
  "language" => "English"
}

def filter(filter) do
  from(s in Songs)
  |> maybe_filter("artist", filter["artist"])
  |> maybe_filter("year", filter["year"])
  |> maybe_filter("language", filter["language"])
  |> Repo.all()
end

@allowed_fields ~w{artist year language}
defp maybe_filter(query, _field, nil),
  do: query

defp maybe_filter(query, field, value)
     when field in @allowed_fields do
  field = to_existing_atom(field)

  query
  |> where([s], field(s, ^field) == ^value)
end

dynamic/3

Let’s say we want to fetch all songs that were either released by ABBA in 1974 or written in Swedish. Our MongoDB-style filter would look something like this:

filter = %{
    "$or" => [
        %{"artist" => "ABBA", "year" => 1974},
        %{"language" => "Swedish"}
    ]
}

Building a more complex query with OR and NOT conditions isn’t easily possible when chaining Ecto.Query.where/3 calls.

This is where the next cool feature of Ecto comes in: Ecto.Query.dynamic/3. Instead of directly operating on the Ecto.Query struct like where does, this function lets you build a fully dynamic query independently.

This is how dynamic works in principle:

condition1 = dynamic([s], s.artist == "ABBA" and s.year == 1974)
condition2 = dynamic([s], s.language == "Swedish")
or_condition = dynamic([s], ^condition1 or ^condition2)

from(s in Songs)
|> where([s], ^or_condition)
|> Repo.all()

This example of using dynamic/3 is, ironically, very static.

Let’s try to build our conditions dynamically from user input by combining what we’ve learned about field/2 and dynamic/3:

filter = %{
    "$or" => [
        %{"artist" => "ABBA", "year" => 1974},
        %{"language" => "Swedish"}
    ]
}

def filter(filter) do
  from(s in Songs)
  |> filter(filter)
  |> Repo.all()
end

defp filter(query, filter) do
  # The top level of the query is always an AND condition
  conditions = build_and(filter)
  from(q in query, where: ^conditions))
end

# Building a group of AND-connected conditions
defp build_and(filter) do
  Enum.reduce(filter, nil, fn
    {k, v}, nil ->
      build_condition(k, v)

    {k, v}, conditions ->
      dynamic([c], ^build_condition(k, v) and ^conditions)
  end)
end

# Building a group of OR-connected conditions
defp build_or(filter) do
    Enum.reduce(filter, nil, fn
      filter, nil ->
        build_and(filter)

      filter, conditions ->
        dynamic([c], ^build_and(filter) or ^conditions)
    end)
  end

@allowed_fields ~w{artist year language}
defp build_condition(field_or_operator, filter)

defp build_condition("$or", filter),
  do: build_or(filter)

defp build_condition(field, filter)
     when field in @allowed_fields,
  do: build_condition(String.to_existing_atom(field), filter)

defp build_condition(field, value)
     when is_atom(value),
  do: dynamic([c], field(c, ^field) == ^value)

We now have framework for building dynamic queries that is extremely easy to extend.

Do you want to add further operators, e.g. a greater than or an in operator? Just add the following functions:

defp build_condition(field, %{"$gt" => value}),
  do: dynamic([c], field(c, ^field) > ^value)

defp build_condition(field, %{"$in" => value}) when is_list(value),
  do: dynamic([c], field(c, ^field) in ^value)

Learning about dynamic/3 can radically simplify how you build APIs with Ecto. If you want to see a more detailed and well-commented implementation of the principle shown in this article, take a look at the querying module in the Keila GitHub repository.