How to use raw sql with ecto Repo

Question

I have an upsert requirement, so I need to call a postgres stored procedure or use a common table expression. I also use the pgcrypto exgtension for passwords and would like to use postgres functions (such as "crypt" to encode/decode passwords).

But I can not find a way to get Ecto to play with raw sql in part or whole, is it intended that ecto will only support the elixir dsl and not allow shelling out to raw sql when the dsl is not sufficient?

I've found that I can query via the adapter (Rocket is the name of the app)

q = Ecto.Adapters.Postgres.query(Rocket.Repo,"select * from users limit 1",[])

But not sure how to get this to the model. I'm new to elixir and it seems I should be able to use Ecto.Model.Schem.schema/3 but this fails

Rocket.User.__schema__(:load,q.rows |> List.first,0)
** (FunctionClauseError) no function clause matching in Rocket.User.__schema__/3    

Answer 1

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)

Answer 2

This is https://stackoverflow.com/users/1758892/thousandsofthem sample, but just shrunk a little (credit: him/her)

defmodule MyApp.Repo do
  [...]
  def execute_and_load(sql, params, schema) do
    response = query!(sql, params)
    Enum.map(response.rows, fn row ->
      fields = Enum.zip(response.columns, row) |> Enum.into(%{})
      Ecto.Schema.__load__(schema, nil, nil, nil, fields,
        &Ecto.Type.adapter_load(__adapter__(), &1, &2))
    end)
  end
end