How to turn an Ecto Query struct into an SQL query?

Questions / Help
1

Is there any way to automatically turn a %Ecto.Query{} struct into a string that contains a SQL query that I can just copy-paste into psql without changing anything?

Let’s say I have this Ecto query:

recipient_id = "a6c0b3c0-27f0-4fea-8182-acd24b86c02e"
ecto_query = from(m in Message, where: is_nil(m.read_at) and m.recipient_id == ^recipient_id)
# => #Ecto.Query<from m0 in Message,
#     where: is_nil(m0.read_at) and m0.recipient_id == ^"a6c0b3c0-27f0-4fea-8182-acd24b86c02e">

I would execute the query like this:

Repo.aggregate(ecto_query, :count, :id) 
# => [debug] QUERY OK source="messages" db=11.3ms queue=0.5ms idle=1723.2ms
#     SELECT count(m0."id") FROM "messages" AS m0 WHERE ((m0."read_at" IS NULL) AND (m0."recipient_id" = $1)) [<<166, 192, 179, 192, 39, 240, 79, 234, 129, 130, 172, 210, 75, 134, 192, 46>>]

And the hypothetical function I am asking about would work something like this:

sql_query = some_magical_function({Repo, :aggregate, [ecto_query, :count, :id]})
IO.puts(sql_query)
# => SELECT count(m0."id") FROM "messages" AS m0 WHERE ((m0."read_at" IS NULL) AND (m0."recipient_id" = 'a6c0b3c0-27f0-4fea-8182-acd24b86c02e'));

I tried reading the sources of Ecto, ecto_sql, and postgrex but couldn’t figure out anything on my own.

Currently, whenever I need this, I do this slow manual process:

Step 0: I run my Ecto query and copy the SQL query from Ecto’s debug log and paste it somewhere.

SELECT count(m0."id") FROM "messages" AS m0 WHERE ((m0."read_at" IS NULL) AND (m0."recipient_id" = $1)) [<<166, 192, 179, 192, 39, 240, 79, 234, 129, 130, 172, 210, 75, 134, 192, 46>>]

Step 1: I read the query, find all the UUID binaries and turn them into strings. To do that, I fire up iex and I run Ecto.UUID.cast.

SELECT count(m0."id") FROM "messages" AS m0 WHERE ((m0."read_at" IS NULL) AND (m0."recipient_id" = $1)) ['a6c0b3c0-27f0-4fea-8182-acd24b86c02e']

Step 2: I substitute all the $ parameters with their values, remove the [...], and add ; at the end. The bigger the query, the more annoying it gets.

SELECT count(m0."id") FROM "messages" AS m0 WHERE ((m0."read_at" IS NULL) AND (m0."recipient_id" = 'a6c0b3c0-27f0-4fea-8182-acd24b86c02e'));
2

There is Ecto.Adapters.SQL.to_sql/3. However it doesn’t seem to support aggregate, so perhaps you might need to replace that with an explicit count & group_by in the query.

1 Like
3

Putting aggregate aside for a while, is there any way to make to_sql format UUIDs as hex strings? This output is still far away from being easily copyable to psql:

{sql_query, _} = Ecto.Adapters.SQL.to_sql(:all, Repo, (from m in Message, where: is_nil(m.read_at) and m.recipient_id == "a6c0b3c0-27f0-4fea-8182-acd24b86c02e"))

# => {<<83, 69, 76, 69, 67, 84, 32, 109, 48, 46, 34, 105, 100, 34, 44, 32, 109, 48,
#       46, 34, 98, 111, 100, 121, 34, 44, 32, 109, 48, 46, 34, 114, 101, 97, 100,
#       95, 97, 116, 34, 44, 32, 109, 48, 46, 34, 115, 101, 110, 100, ...>>, []}

iex()> IO.inspect(sql_query, binaries: :as_strings)
"SELECT m0.\"id\", m0.\"body\", m0.\"read_at\", m0.\"sender_id\", m0.\"recipient_id\", m0.\"publication_id\", m0.\"inserted_at\" FROM \"messages\" AS m0 WHERE ((m0.\"read_at\" IS NULL) AND (m0.\"recipient_id\" = '\xA6\xC0\xB3\xC0''\xF0Oꁂ\xAC\xD2K\x86\xC0.'))"
4

This one is a bit tricky but something can be hacked if UUID is not hardcoded as a constant, but passed as a parameter:

(from m in Message, where: is_nil(m.read_at) and m.recipient_id == ^"a6c0b3c0-27f0-4fea-8182-acd24b86c02e")

Notice ^ before the constant. If you pass such query to to_sql the second element (the list of parameters) is going to contain the uuid:

elem(to_sql(...), 1)
# [<<166, 192, 179, 192, 39, 240, 79, 234, 129, 130, 172, 210, 75, 134, 192, 46>>]

This is the encoded representation, and to bring it back to hex you can invoke Ecto.UUID.cast:

Ecto.UUID.cast( <<166, 192, 179, 192, 39, 240, 79, 234, 129, 130, 172, 210, 75, 134, 192, 46>>)
# {:ok, "a6c0b3c0-27f0-4fea-8182-acd24b86c02e"}

So the algorithm could be: go through the list of parameters (second element of the tuple), try to cast each element, if you get error keep the original value, otherwise replace it with the casted one. Not sure if this can be generalized, or if there’s a more elegant/automatic way to make it happen.

2 Likes