Simple Queries for Ecto - yay or nay?

Before I dive into the topic, I need to make sure I’m not getting misunderstood.

Since I started working with Phoenix, I’ve been enjoying using Ecto A LOT. It is some fresh air to me coming from a Rails background. I have gone through nightmares with suboptimal, weird and slow queries because of the layer of abstraction that ActiveRecord imposes.

Ecto made me think in SQL again, and I love it for that. It allows me to fine-tune my queries a lot more, and avoid unexpected N+1 and other shenanigans.

That’s for the good part. The bad part, or rather, the annoying part, is that Ecto is quite a bit more verbose for even the simplest tasks. This is not a big deal when writing production code - give or take a couple of lines does not change anything. BUT when I’m in the interactive shell, I really, really dislike having to write multiple lines of code for what could be done in one line.

Let’s see an example. Imagine I want to collect all the ids of a given model / table. In ActiveRecord, one can do:


Done! To do the same in Ecto, you need to write the following 3 lines of code:

require Ecto.Query
query = Ecto.Query.from u in MyApp.User, select:

This gets old pretty quick!

So I’ve decided to write some shortcuts for some of the Ecto queries I use the most when working in the REPL. I’m not sure if this approach has any major caveats, but it’s been working really nicely for me. I’d be happy to see some feedback from you, since it’s the first time I write an Elixir macro myself.

This is the utility macro:

defmodule EctoPlus.SimpleQueries do
  defmacro __using__(repo: repo) do
    quote do
      def count do
        unquote(repo).aggregate(__MODULE__, :count, :id)

      def find(id) do
        unquote(repo).get(__MODULE__, id)

      def find_by(statement) do
        query =
          Ecto.Query.from rec in __MODULE__,
          where: ^statement,
          limit: 1


      def select(columns) when is_list(columns) do
        query =
          Ecto.Query.from rec in __MODULE__,
          select: ^columns


      def select(column) when is_atom(column), do: select([column])

      def pluck(columns) when is_list(columns) do
        for record <- select(columns), do: Map.take(record, columns)

      def pluck(column) when is_atom(column) do
        for record <- select(column), do: Map.get(record, column)

      def where(statement) do
        query =
          Ecto.Query.from rec in __MODULE__,
          where: ^statement


And to have it available in the models, I add the following line in web/web.ex, under the model definition:

use EctoPlus.SimpleQueries, repo: MyApp.Repo

And then I can run stuff in the iex like:


And so on… I even have aliases for the most common models in my .iex.exs file so that I can just write:


What do you think? And just to be sure: these functions are not meant to be used inside of my production code. The idea is to keep those reserved for iex / debugging sessions. Cheers!


Why put these in the model? You add only a couple characters if you did

User |> Repo.pluck([:id])

That is indeed an interesting option. And I guess it’s more idiomatic than my solution. Will give it a thought, thanks!

1 Like

Disclaimer: I started writing this answer before @benwilson512 posted his. But since I already had a lot, I decided to post it anyway.

One of the biggest ideas behind ecto’s architecture is separating data from database - separating pure operations from impure ones. The only way you can change something in the database is using the repo module. Having this guarantee makes you very conformable when writing code - you know you won’t accidentally change things in the database three associations away from the object you’re currently working on (these things happen).
Yes, it’s a bit more verbose - but code should not be optimised for writing, but for reading - you’re going to do a lot more of the later. Separating pure from impure makes it easier to reason about the code.

Also, some things can be greatly reduced using the macro versions of query API for simple cases like that:

User.find_by(foo: foo, bar: bar)


User |> select(:id) |> Repo.all
User |> where(foo: ^foo, bar: ^bar) |>

Yes, it remains more verbose, but the difference is definitely not that big. If you still find this too verbose for your liking, what you could do (and I do quite often) is define commonly repeating patterns as functions on the repo itself - after all it’s just a module.

defmodule MyApp.Repo do
  use Ecto.Repo, otp_app: :my_app

  def count(query), do: aggregate(query, :count, :id)

  def pluck(query, fields), do: one(select(query, ^fields))

And now we can write:

User |> Repo.pluck(:id)
User |> Repo.count

Not only that - the operations are composable, so we can do things like:

User |> where(foo: ^foo) |> Repo.count
User |> limit(100) |> order_by(desc: :inserted_at) |> Repo.pluck(:id)

Thanks for that great explanation. I guess it takes some time to “grasp” the idea behind Ecto. Although a bit more verbose, I like the idea of putting functions in the Repo module - seems really clear and being composable is a large benefit as well.

Thanks for the insights and keep up the great job!

PS: I saw you are going to organise a tutorial at the ElixirConf.EU this year - see you at the conference!

1 Like

great idea! I’ve used the following implementation though:

  def pluck(q, fields) do
    from(x in q, select: map(x, ^fields)) |> all

Surprise database call then? ^.^

I prefer putting database access fully visible. :slight_smile:

Though that could work if you put it on Repo instead.

1 Like

Though that could work if you put it on Repo instead.

Indeed, same as @michalmuskala, I’ve put this function as MyApp.Repo.pluck/2, I just changed function body a bit.


If the function is defined on the repo, it’s not really surprising that it does a db call :smirk: