A sneak peek at Ecto 3.0: query improvements (part 1)

• José Valim
• October 5th, 2018
• ecto, elixir

Welcome to the “A sneak peek at Ecto 3.0” series:

1. Breaking changes
2. Query improvements part 1 (you are here!)
3. Query improvements part 2
4. Performance, migrations and more

Let’s get started with the improvements to Ecto.Query APIs. The Ecto.Query API is the area that saw most improvements in Ecto 3.0, to the point we won’t be able to cover all improvement in a single article. Instead, we broke it in part 1 and part 2.

Let’s get started.

Better join composition with named bindings

Ecto has always supported joining over multiple schemas and tables using joins:

query =
  from p in Post,
    join: c in Comment,
    where: p.id == c.post_id,
    select: c

Now imagine we want to modify the query above to only return comments that are public. We could compose on the query above as follows:

from [_, c] in query, where: c.public

As you can see in the example above, we can extract all existing bindings in a query (p and c) and then apply filters to them. In the example above, the bindings are positional and they depend on the order they appear in the list on the left side of in. The names p and c are temporary and they are not relevant to the overall query. In other words, the query below would be equivalent to the one above:

from [_, comment] in query, where: comment.public

The problem with positional bindings is that sometimes it makes query composition quite challenging. When building complex search functionality, you may join over multiple tables, in a different order, and tracking where each positional binding is would be quite brittle and complex.

Ecto 3.0 changes this by allowing each from and join to have a name. Our initial query could be rewritten as:

query =
  from p in Post,
    join: c in Comment,
    as: :comments,
    where: p.id == c.post_id,
    select: c

Note we have added the as option after the join. Now to filter the existing :comments, regardless of the order it appears on the query, we can write:

from [comments: c] in query, where: c.public

We replace the positional binding by a keyword list, where the key is the binding name and the value is a variable we will assign the join to. Once again, the c variable here does not matter and it could have any name. The important bit is that we are binding it to the existing :comments.

Note Ecto 3.0 chose to introduce an explicit naming mechanism via the :as option, instead of relying on the variable names, as the variable names could lead to accidental clashing, especially as developers may shortcut the variable names to single letters in queries. Furthermore, if there is an attempt to bind to the same name more than once, an error will be raised.

Finally, keep in mind that the as option can also be given to from, for instance:

query =
  from p in Post,
    as: :posts,
    join: c in Comment,
    as: :comments,
    where: p.id == c.post_id,
    select: c

Named bindings will make Ecto much more flexible for building dynamic queries, as usually seen in complex search forms, search APIs and more. The bulk of the work was done by Adrian Gruntkowski. You can read on the proposal and the following discussion in the issues tracker.

Database prefixes and index hints

We have two new functionalities on top of the foundation we created to add named bindings to Ecto: per from/join prefixes and index hints.

Ecto v2.0 introduced the idea of prefixes. What the prefix means depends on the database engine. For Postgres, the prefix translates to a Postgres Schema. A database in Postgres has multiple schemas and the default schema is called “public”. MySQL does not support schemas, therefore the prefix functionality in MySQL simply translates to different databases.

When Ecto v2.0 introduced prefixes, the goal was to make it straightforward to select, insert, update and delete data from different prefixes. The goal was to support multi-tenant applications. However, Ecto v2.0 was limited to only work on a single prefix at a time. For example, it was not possible to write a query that would join data across two different prefixes.

Ecto v3.0 lifts this restriction by allowing the prefix option to be given to from/join, in the same way we could pass the as option. For example, imagine that you have a system where all of the posts are public but the comments are specific to each client using the system. Therefore, you have multiple prefixes in the system, one for each client, and each prefix has its own “comments” table. You can now query across those prefixes as follows:

from p in Post,
  prefix: "public",
  join: c in Comment,
  prefix: "client1",
  where: p.id == c.post_id,
  select: c

Similarly, Ecto 3.0 relies on a similar API to support the use of index hints, as found in MySQL and MSSQL databases:

from p in Post,
  join: c in Comment,
  hints: ["USE INDEX FOO", "USE INDEX BAR"],
  where: p.id == c.post_id,
  select: c

Keep in mind you want to use hints rarely, so don’t forget to read the database disclaimers about such functionality.

The prefix and hints options brings more flexibility to developers to structure and optimize their queries, allowing them to leverage Ecto.Query as much as possible, without having to fallback to SQL.

Other changes

Ecto.Query now supports tuples in where and having, allowing queries such as where: {p.foo, p.bar} > {^foo, ^bar} which can be used for cursor-based pagination.

We have also added support for arithmetic operators, such as +, -, *, /. Note those operators just delegate to the underlying database engine, so remember to check your database to see what are the possible types of the operands.

Finally, it is now possible to invoke database functions that expect the whole table/source as argument, by using fragments: fragment("some_function(?)", p).

This is it for now! If you have any questions about the features above, feel free to use the comments section below or search for the relevant discussion in Ecto’s issues tracker. Next week we will be back with further improvements and features added to Ecto.Query in Ecto 3.0.

P.S.: This post was originally published on Plataformatec’s blog.