How to use raw sql with ecto Repo

Solution 1:

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

Solution 2:

Now that Ecto 1.0 is out, this should work for some time:

Add the following functions to your Repo module:

def execute_and_load(sql, params, model) do
  Ecto.Adapters.SQL.query!(__MODULE__, sql, params)
  |> load_into(model)
end

defp load_into(response, model) do
  Enum.map response.rows, fn(row) ->
    fields = Enum.reduce(Enum.zip(response.columns, row), %{}, fn({key, value}, map) ->
      Map.put(map, key, value)
    end)

    Ecto.Schema.__load__(model, nil, nil, [], fields, &__MODULE__.__adapter__.load/2)
  end
end

And use as such:

Repo.execute_and_load("SELECT * FROM users WHERE id = $1", [1], User)