How can I see the raw SQL generated for an Ecto.Query?

Question

I have an Ecto.Query and a Repo, such that I can call Repo.all(query) and get results. However, the results are not what I expect.

How can I see the raw SQL the Repo will generate from the Ecto.Query?

Answer 1

You can use Ecto.Adapters.SQL.to_sql/3:

iex> Ecto.Adapters.SQL.to_sql(:all, Repo, Post) {"SELECT p.id, p.title, p.inserted_at, p.created_at FROM posts as p", []} 

This function is also available under the repository with name to_sql if you’re using a SQL based adapter:

 iex> Repo.to_sql(:all, Post)   {"SELECT p.id, p.title, p.inserted_at, p.created_at FROM posts as p", []} 

The query can be any struct that implements the Ecto.Queryable protocol like Post above(which is a module that imports Ecto.Schema). An Ecto.Query can also be passed:

iex> query = Ecto.Query.where(Post, [p], p.views > 10) iex> Ecto.Adapters.SQL.to_sql(:all, Repo, query) {"SELECT p.id, p.title, p.inserted_at, p.created_at FROM posts as p WHERE p.views > $1", [10]} 

Answer 2

A convenient helper method for printing raw SQL

def print_sql(queryable) do   IO.inspect(Ecto.Adapters.SQL.to_sql(:all, Repo, queryable))   queryable end  def list_new_foos() do   Foo   |> where([foo], foo.bar == 1337)   |> limit(100)   |> print_sql   |> Repo.all() end