Support named bind variables in Ecto

Questions / Help
1

Hi there! I am working on the ecto adapter and want to discuss the problem that bothers me.

Let’s say we have a simple table foo and model for it.
We use Postgres and try to load paginated records:

import Ecto.Query
l = 1
o = 2
records = Foo |> limit(^l) |> offset(^o) |> Repo.all
# => SELECT * FROM "foo" AS f0 LIMIT $1 OFFSET $2 [1, 2]

Now, we build manually almost the same query just change the order of the placeholders and params:

sql = 'SELECT * FROM "foo" AS f0 LIMIT $2 OFFSET $1'
m_records = Ecto.Adapters.SQL.query!(Repo, sql, [2, 1])

records == m_records # If compare the returned records
# => true

Great :slight_smile:
This is because, in PostgreSQL, bind variables are numbers preceded by a $ sign representing index in passed params.

However, not all DBs follow this rule.
For example, if we work with Oracle\MS SQL the same query will be:

l = 1
o = 2
records = Foo |> limit(^l) |> offset(^o) |> Repo.all
# => SELECT * FROM "foo" AS f0 OFFSET :2 ROWS FETCH NEXT :1 ROWS ONLY [1, 2]

where :2 == 1, :1 == 2, because for these DBs bind variables are names, not indexes. (see: http://sqlrelay.sourceforge.net/sqlrelay/programming/binds.html)

Moreover,

# => SELECT * FROM "foo" AS f0 OFFSET :1 ROWS FETCH NEXT :2 ROWS ONLY [1, 2]

returns the same wrong result, because, again, placeholders in query are just names.

The problem is that we cannot (I don’t see how) control the order of binding variables in a query from the adapter side. Here ecto builds the params list, iterating by this constant.

Maybe we should allow defining these @*_exprs on the adapter level? Or is there another way to control bind variables?

For now, to fix this issue, I have to “fix” a query on application level.

1 Like