Hacking Ecto: Constructing Ecto Query from Raw SQL

Hi all, here's sharing "hack" tips for constructing Ecto.Query from Raw SQL. You would want to do this in case you want all good feature of Ecto.Query but want to bypass safeguard put in place.

A bit of introduction

Ecto is really cool database layer in Elixir, for SQL it let you define SQL query inside Elixir in readable and concise way, and execute it with automatic mapping to defined data structure.

example of SQL query and execution in Ecto

Ecto.Query is main module and data structure for defining a query in Ecto. Ecto.Query is composable and dynamically programmable, which means that we can extend a lot and reuse query functionality.

we define with_search_keyword query filtering function and use it as part of bill_of_ladings function

Constructing Ecto from Raw Query

By default, from, usual construction method of Ecto.Query doesn't support complicated SQL. from support only Ecto.Schema data structure and table name in string, like:

from(t in "transporter",
  where: t.name == "STAR TRANSPORT COMPANY")

However, you can specify Raw SQL in join, via fragment, which usually used in case you have complicated join query.

from(t in "transporter",
  join: i in fragment("SELECT * FROM invoice WHERE invoice.transporter_id = ?", t.id),
  where: i.date == ^date
)

We can leverage this Raw SQL join to get Raw SQL as base object of Ecto.Query.

Define dummy query

We need a dummy query, need to has exactly one result. as example we can query from "constants" table and limit it to 1

dummy_query = from x in "truck_types", as: :dummy, select: x.ksuid, limit: 1

Join dummy query with raw query

By using subquery, we can reuse the dummy query to be used as join component, which we use then to join with raw data

aggregate_subquery = 
  from(d in subquery(dummy_query))
    |> join(:inner, [], raw in fragment(@raw_sql), on: true, as: :raw)
    |> select([raw: raw], %{
      id: raw.id,
      name: raw.name
    })

Example of @raw_sql would be below. @raw_sql need to be constant string.

SELECT id, name FROM transporter
UNION ALL
SELECT id, name FROM shipper

Wrap joined query in subquery (again)

By wrapping the joined query again in subquery, we will have a query which is just like Ecto.Query but with custom SQL

query = from(q in subquery(aggregate_subquery))

Now you can use query as in usual Ecto way

Like in below, which search from transporter and shipper who has name "ANDI"

from(q in query,
  where: name == "ANDI"
)

Why would you want to hack it?

Ecto has really flexible and expressive way to write SQL, why then you would want to resort to such hacking as writing Raw SQL?

For me, the case come when i need to retrofit an existing SQL query, ~500 line - full with Postgres specific SQL feature, with existing functionality which is already built with Ecto.Query. Rewriting it in Ecto would need to decipher such 500 line of SQL, and would be full of fragment for safety hatch of writing Raw SQL. I reason that doing it all in Raw SQL would be easier to comprehend, which then led to this hack.