Ecto named querys in raw sql statements

Questions / Help
1

Is it possible to do named querys with ecto like this

query = "INSERT INTO widgets (id, name) VALUES (:id, :name)"

Ecto.Adapters.SQL.query(query, %{ :id => 1, :name => "Jim" })
2

The query function takes string query as argument, but according to the documentation you can pass a string with numbered params and then these params:

Ecto.Adapters.SQL.query(MyRepo, "SELECT $1::integer + $2", [40, 2])`

so you can save your query as a string variable and pass it around like that.

1 Like
3

I’ve read the documentation. Can it not do what I specified?

4

As you can see at:
https://github.com/elixir-ecto/ecto/blob/master/lib/ecto/adapters/sql.ex#L224
The query call requires the Repo (so it knows what syntax to use for the specific database and all such and to call it), the string, and the arguments (as a list), and you are passing in just a string and arguments. :slight_smile:

However yes, but instead of using things like :id you need to use the index in the list that it is pulling from (plus 1) as well as you should type it so the SQL knows what type it should be (this may be optional in most cases though), so your example should be:

query = "INSERT INTO widgets (id, name) VALUES ($1, $2)"

Ecto.Adapters.SQL.query(MyWhateverRepo, query, [1, "Jim"])

It is all documented with examples at the docs: https://hexdocs.pm/ecto/Ecto.Adapters.SQL.html#query/4

It would be quite possible to have your example work, but you’ll need to write a helper (and maybe PR it?) as that is not how it is currently specified. :wink:

1 Like