Prisma1: executeRaw: Interpolation of Parameters

[mavilein]

Is your feature request related to a problem? Please describe.
Currently the mutation executeRaw does not allow to interpolate parameters.

Describe the solution you'd like
Ideally we would like to be able to write something like the following:

mutation {
  executeRaw(
    database: default
    query: "Select * from Employees where email = ? and isAdmin = ? and salary > ?"
    parameters: ["[email protected]", true, 100000]
  )
}

This means that we will allow to use ? as placeholders in the query argument. For each placeholder that was used a parameter has to be supplied in the parameters argument.

Describe alternatives you've considered
Alternatively we could also use $1, $2 ... for the placeholders in the query. This is how Postgres does it. However MySQL and JDBC use ? and therefore i used it in the proposal.

Problems
We will have limits in inferring the right type for the provided parameters. This will happen for instance for types that have ids of type UUID. For instance the following query would fail on Postgres with the error operator does not exist: uuid = character varying.

mutation {
  executeRaw(
    database: default
    query: "Select * from Employees where id = ?"
    parameters: ["59effa34-6d41-4384-80ae-7a174d731bde"]
  )
}

In order to fix this the query would have to include an explicit cast:

mutation {
  executeRaw(
    database: default
    query: "Select * from Employees where id = CAST(? as UUID)"
    parameters: ["59effa34-6d41-4384-80ae-7a174d731bde"]
  )
}

It's unclear for which other column types this would be a probleme as well and whether it can always be fixed by changing query.

[dominikh]

I was under the impression that raw queries where just that, raw. That is, that Prisma shouldn't do any processing on the query or inputs and pass them as is to the database.

In particular, I wouldn't expect Prisma to be the one doing parameter interpolation. This would require Prisma to a) implement full parsers for the respective query languages (how else will you be able to discern ? as a placeholder from ? as a character in a string literal, for example?) b) implement typing and value escaping for each supported backend. You allude to some of these problems in the context of type inference.

In contrast, when I initially requested support for parameterized queries, I was expecting Prisma to just pass the query and arguments verbatim to the database. In pseudo code,

mutation {
  executeRaw(
    database: default
    query: "Select * from Employees where email = ? and isAdmin = ? and salary > ?"
    parameters: ["[email protected]", true, 100000]
  )
}

would simply turn into

mysqlQuery("Select * from Employees where email = ? and isAdmin = ? and salary > ?", "[email protected]", true, 100000)

This would naturally necessitate different placeholders for different backends, which IMO is fine. Query languages already differ per backend. It would also require users to write correct queries. If casts are required, the user should be the one writing them.

tl;dr: I expected raw queries to be a very thin wrapper around the database's query function.

[mavilein]

@dominikh : If we use ? as placeholders we actually do not have to do any parsing. The Prisma Core is written in Scala, which runs on the JVM. Therefore we are using JDBC as an abstraction layer and do not use the native syntax for each database. Instead we use the JDBC syntax which already uses ? as placeholders and does the heavy lifting for us.
Supporting the native syntax of each database would actually be harder for us.

[terion-name]

Maybe they should be passed via arguments?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed in 10 days if no further activity occurs. Thank you for your contributions.