Ecto query into Plain SQL

Thanks for sharing, very useful!

Wow, I just found GitHub - akoutmos/ecto_dbg: A utility to format and output Ecto queries · GitHub

image1220×490 21 KB

It produces well-formatted, copy-paste-ready SQL right into my logs @akoutmos

I don’t know what exactly happens behind the scenes, but I doubt the db does actually interpolate values:

Repo.explain(:all, query)

Do you mind sharing any pointers on how exactly Postgres does that interpolation for us? I just badly need a full plain SQL from a (complex) Ecto Query for performance analysis.

It looks like you want to convert an ecto query to an sql query to use in migrations (hence the execute). The problem here is that ecto actually cannot do that completely. Ecto as well as the underlying postgrex adapter does never interpolate parameters into the query. This is left to the database to do. So you’ll at best get the query with placeholders in the sql query and you’re left to replace those with actual values on your own. That’s why to_sql returns a tuple of {query, parameters}.

What are you trying to do exactly. Because I have strange feeling that we are dealing with XY problem.

In this case use query

I want to query something like following

    execute"""
    insert into workspaces (
      name
      )
      values("#{prefix() || "public"}");
    """

becuase I don’t want to use the schema in the command.

Did you try it?

It’s documented here: ecto_sql/lib/ecto/adapters/sql.ex at 2ec71d74655cc076b6c965740a357dfe1505883a · elixir-ecto/ecto_sql · GitHub and here’s the macro, which adds it to the repo: ecto_sql/lib/ecto/adapters/sql.ex at 2ec71d74655cc076b6c965740a357dfe1505883a · elixir-ecto/ecto_sql · GitHub

It’s not part of the Ecto.Repo behaviour though, which is why it’s not documented in that module. It’s only available if you’re using a SQL based adapter.

In Ecto 3.0 there are no to_sql callback on repo