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

elixir ecto
Nathan Long picture Nathan Long · Apr 21, 2016 · Viewed 12.3k times · Source

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

Gazler picture Gazler · Apr 21, 2016

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

Related questions

Turn postgres date representation into ISO 8601 string

I'm trying to format a Postgres date representation into a ISO 8601 string. I'm assuming that there is a Postgres function that can do it, but I found the documentation short on examples. My query is SELECT now()::timestamp which returns [{{2016, 8, 9}, {3, 56, 55, 754181}}] …

Read More
How to use raw sql with ecto Repo

I have an upsert requirement, so I need to call a postgres stored procedure or use a common table expression. I also use the pgcrypto exgtension for passwords and would like to use postgres functions (such as "crypt" to encode/…

Read More
Is there a Phoenix equivalent to Rails Console

I'm just learning Phoenix and Elixir and I'm coming from Ruby/Rails where I work in the REPL using pry to inspect my database and application state. I'm trying to figure out how to interact with my database and models …

Read More