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    

Cloth

Upgraded to ecto 4.0 and the above is working! Not sure why Repo does not intrinsically support sql queries, but I'm happy to move forward – Cloth

The Repo doesn't support queries because it is meant to be SQL agnostic! I have mixing feelings if __schema__(:load, ..., ...) is meant to be used directly like that... but your solution is perfect for the current code! – Gorgeous

I'll want to do something similar to this on an example Ecto project I'm working on so I'd like thank the question author and leave an example for José. I've a tree structure using a parent_id (adjacency list) and I'd like to be able to a) use postgres recursive queries and b) return a tree of models so even if this would never be directly in Ecto itself I'd still love an official way of creating a bunch of models from some data I got myself. – Wrangle

@Cloth please answer your own question and accept this answer so this question no longer appears as unanswered. It makes it easier for future visitors to find the answer. Thanks! – Commutual

@PatrickOscity still working out the complete answer, the query part is :ok but still need to figure out how best to get the result into an Ecto.Model – Cloth

I would just like to update this mention using the fragments API, mentioned below, is the way to go. – Gorgeous

@JoséValim it appears the fragment API only allows fragments, I need to issue entire queries because the SQL cannot be abstracted via the ecto dsl. The equivalent of find_by_sql in ActiveRecord – Cloth

Answer 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

Bradbradan

Kernel.struct will bypass the type casting for the schema, so I think this would work if calling changeset as the last function. However, I would want to just get the changeset.data (reading the 2.0 docs real fast) and that is now a reference to the struct? – Cloth

Answer 2

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__

Phalansterian

Great what if I need to use raw SQL with left joins? I have tried this method but it does not work for joins. It returns the data associated with the model specified in the function parameters. – Committeewoman

This is great! I shrunk it just a bit, in case that's helpful. I couldn't get it to format in this comment so I stuck it here: https://mcmap.net/q/348562/-how-to-use-raw-sql-with-ecto-repo – Kirsten

Answer 3

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)

Shrubby

Answer 4

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)

Hoover

Answer 5

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

Enteritis

This appears to break if the query includes joins or computed columns. I observed certain attributes not being loaded using phoenix_ecto ~> 4.0 – Ratcliff

Answer 6

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}

Fascist

This just returns the row data and not the ecto struct – Cloth

Answer 7

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

Kirsten

Answer 8

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)

Cloth

There is no Ecto 4.0 yet. – Colton