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
- run a custom query with no parameters,
- convert the result's column names from strings to atoms, and
- 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
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__
Solution 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)
Solution 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)
Solution 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
Krut
Updated on December 10, 2021Comments
-
Krut over 2 years
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
-
Krut about 9 yearsThis just returns the row data and not the ecto struct
-
Krut almost 8 years
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? -
W.M. over 7 yearsGreat 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.
-
denis.peplin about 7 yearsThere is no Ecto 4.0 yet.
-
Michael Bishop over 6 yearsThis 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: stackoverflow.com/a/46247304/45114
-
tfwright about 5 yearsThis appears to break if the query includes joins or computed columns. I observed certain attributes not being loaded using phoenix_ecto ~> 4.0