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
endI 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