How to use raw sql with ecto Repo

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    

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

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)

Modified solution for Ecto 2.0:

in repo.ex:

  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, nil, fields,
                           &Ecto.Type.adapter_load(__adapter__, &1, &2))
    end)
  end

Usage:

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

UPDATE: for Ecto 3 you can use __MODULE__.load(model, fields) instead of Ecto.Schema.__load__

In addition to Ecto.Adapters.SQL.query/4, there is also Ecto.Query.API.fragment/1, which can be used to send query expressions to the database. For example, to use Postgres's array function array_upper, one might use

Ecto.Query.where([x], fragment("array_upper(some_array_field, 1)]" == 1)

Ecto 2.2.8 provides Ecto.Query.load/2, so you can do something like this:

use Ecto.Repo

def execute_and_load(sql, params, model) do
  result = query!(sql, params)
  Enum.map(result.rows, &load(model, {result.columns, &1}))
end

See https://hexdocs.pm/ecto/Ecto.Repo.html#c:load/2

Ecto, at least as of version ~> 0.7 you should use:

Ecto.Adapters.SQL.query/4

def query(repo, sql, params, opts \\ [])

Runs custom SQL query on given repo.

In case of success, it must return an :ok tuple containing a map with at least two keys:

• :num_rows - the number of rows affected • :rows - the result set as a list. nil may be returned instead of the list if the command does not yield any row as result (but still yields the number of affected rows, like a delete command without returning would)

Options

• :timeout - The time in milliseconds to wait for the call to finish, :infinity will wait indefinitely (default: 5000) • :log - When false, does not log the query

Examples

> iex> Ecto.Adapters.SQL.query(MyRepo, "SELECT $1 + $2", [40, 2])

> %{rows: [{42}], num_rows: 1}

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

With at least ecto 4.0 you can query using the adaptor and then feed the results to Ecto.Model.schema/3:

q = Ecto.Adapters.Postgres.query(Rocket.Repo,"select * from users limit 1",[])
Rocket.User.__schema__(:load,q.rows |> List.first,0)