Executing Raw SQL with Ecto

Executing Raw SQL with Ecto

In order to execute a raw query we need to use the function YourApp.Repo.query which wraps Ecto.Adapters.SQL.query.Repo. In most cases it's better to rely on Ecto's composable query syntax and there are very few queries where I would reach for this particular option, however there are times when the ability to execute a query directly is necessary and yields a better result.

Example Usage:

alias YourApp.Repo

{:ok, result} = Repo.query("select * from table_name order by id asc")

   columns: ["id", "name","inserted_at", "updated_at"],
   command: :select,
   connection_id: 00001,
   num_rows: 1,
   rows: [
       "test name",
       {{2019, 6, 18}, {22, 40, 56, 0}},
       {{2019, 6, 18}, {2, 23, 4, 0}}

Note: The results returned will be a postgrex result struct and may require some transformation.