48

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?

Nathan Long
  • 122,748
  • 97
  • 336
  • 451

4 Answers4

78

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]}
Seivan
  • 668
  • 6
  • 13
Gazler
  • 83,029
  • 18
  • 279
  • 245
  • Thanks. In my case, I needed to pass the `query` struct in place of `Post`. – Nathan Long Apr 21 '16 at 13:28
  • @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. – Gazler Apr 21 '16 at 13:34
  • Followup question: http://stackoverflow.com/questions/41021546/can-i-get-ecto-to-log-raw-sql – Nathan Long Dec 07 '16 at 15:45
  • 1
    As of Ecto 3, the sql portion was split out into ecto_sql. Here is the updated link https://hexdocs.pm/ecto_sql/3.0.3/Ecto.Adapters.SQL.html#to_sql/3 for future googlers. – Chase Dec 18 '18 at 14:54
17

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
Dan Andreasson
  • 15,380
  • 5
  • 29
  • 30
  • This does not work for relations or preloads. You only get the parent level query in my case. – AlxVallejo Jun 24 '19 at 18:27
  • @DanAndreasson Can you please help me out here: https://stackoverflow.com/questions/65183075/add-interval-to-timestamp-using-ecto-fragments/ – Tab Key Dec 08 '20 at 07:26
4

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]}
Gjaldon
  • 5,534
  • 24
  • 32
2

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.

denis.peplin
  • 9,585
  • 3
  • 48
  • 55
  • 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. – AlxVallejo Jun 24 '19 at 15:40