Ecto, You Got Some ‘Splainin To Do

Ecto is a powerful tool for interacting with databases, and I thoroughly enjoy using it. But with great power comes great responsibility.

I was looking through a particular part of an app trying to find which of 5 or so queries being made had the highest execution cost. The client was reporting 500 errors in production and I was hoping for a simple change that could be made to a very complicated part of that app.

Manual process is tedious

My process for this was to copy the query logs and params, use a SQL formatter so it could be read, then go paste in the params in the appropriate places. This process takes a few minutes and for long queries, it’s easy to mess up and spend even longer trying to find the syntax errors.

All that to EXPLAIN was driving me nuts, so I went to hexdocs looking for a better way. I found Ecto.Adapters.SQL.to_sql/3 would return a tuple that included the SQL string and the params to go with. I just needed a way to interpolate that into a complete SQL string.

Searching for a better way

As I searched for something to do that, I discovered something much better. I added EXPLAIN to the SQL strings and used Ecto.Adapters.SQL.query/4. That way, I was able to quickly put the following in a module as I was troubleshooting to get the job done.

def explain(query) do
  {sql, params} = Ecto.Adapters.SQL.to_sql(:all, MyApp.Repo, query)
  results = Ecto.Adapters.SQL.query!(MyApp.Repo, "EXPLAIN " <> sql, params)
  IO.inspect(results, printable_limit: :infinity)

  query
end

I love simple tools like this and had to share, so it’s been rolled into a package on hex that can be quickly added to any project’s repo module. Let me know if this helps you next time you’re using ecto.

Documentation is available on hexdocs.pm

Additional Ecto Resources

• https://github.com/revelrylabs/ecto_explain
• https://hexdocs.pm/ecto_explain
• https://www.postgresql.org/docs/9.1/sql-explain.html
• https://www.postgresql.org/docs/current/sql-explain.html
• https://hexdocs.pm

---

Development Ecto Engineering