How to use raw sql with ecto Repo

On Ecto 2.0 (beta) with Postgres, you can use Ecto.Adapters.SQL.query() (current docs, 2.0-beta2 docs) to execute arbitrary SQL; in addition to a list of the rows themselves (“rows“), it happens to return a list of column names (“columns“).

In the below example, I

1. run a custom query with no parameters,
2. convert the result’s column names from strings to atoms, and
3. combine those with each row of the results and map it into a struct with Kernel.struct()

(You’ll probably want to run the query() version (without the bang !) and check for {ok, res}.)

qry = "SELECT * FROM users"
res = Ecto.Adapters.SQL.query!(Repo, qry, []) # 1

cols = Enum.map res.columns, &(String.to_atom(&1)) # 2

roles = Enum.map res.rows, fn(row) ->
  struct(MyApp.User, Enum.zip(cols, row)) # 3
end