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?

Danas

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]}

Moshemoshell

Thanks. In my case, I needed to pass the query struct in place of Post. – Danas

@NathanLong You can pass anything that implements the Queryable protocol. That includes a query (obviously) but also anything that uses Ecto.Schema. I'll update my answer. – Moshemoshell

Followup question: #41022046 – Danas

As of Ecto 3, the sql portion was split out into ecto_sql. Here is the updated link hexdocs.pm/ecto_sql/3.0.3/Ecto.Adapters.SQL.html#to_sql/3 for future googlers. – Corrugate

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

Sublime

This does not work for relations or preloads. You only get the parent level query in my case. – Ungrudging

@DanAndreasson Can you please help me out here: #65183575 – Execute

Answer 3

to_sql/2 is added to the module you use Ecto.Repo on. By convention, that module would be named MyApp.Repo (MyApp would be your app's name). Internally, it would use Ecto.Adapters.SQL.to_sql/3, but Ecto.Adapters.SQL is more of an internal module.

Using it would look like:

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

Exaggerated

On Elixir 1.7.3 I get Repo.to_sql/3 is undefined or private – Ungrudging

@Ungrudging It isn't Repo.to_sql/3 but MyApp.Repo.to_sql/2. It should work with Ecto 3 or maybe even earlier – Exaggerated

Answer 4

It's basically Gazlers answer, but modified to use in code:

query = from p in Post
{query, params} = Ecto.Adapters.SQL.to_sql(:all, Repo, query)
IO.puts("#{query}, #{inspect(params)}")

You could use simple IO.inspect, but it'll output a query with backslashes.

Romona

I'm getting "Ecto.Queryable not implemented" errors for each of my relational models of the query. Do you have to preload them first? Seems odd that I would need to preload. – Ungrudging