Execute a MySQL command with named parameters

[jangeorgiev]

Steps to reproduce

1. Install node v12.19.0
2. Install package "loopback-connector-mysql": "6.0.0"
3. use repository that extends DefaultCrudRepository and run:
   await repo.execute("SELECT * FROM table WHERE name = :name", {name: "John Doe"})

Current Behavior

Receive error:
strong-error-handler Handling AssertionError [ERR_ASSERTION]: params must be an array

Expected Behavior

According to doc the type NamedParameters is supported, so object should be handled

Additional information

My stack trace:
strong-error-handler Handling AssertionError [ERR_ASSERTION]: params must be an array at MySQL.SQLConnector.execute (/api/node_modules/loopback-connector-mysql/node_modules/loopback-connector/lib/sql.js:566:3) at /api/node_modules/loopback-datasource-juggler/lib/datasource.js:2711:39 at new Promise (<anonymous>) at MainDBDataSource.DataSource.execute (/api/node_modules/loopback-datasource-juggler/lib/datasource.js:2710:10) at MySQLRepository.execute (/api/node_modules/@loopback/repository/dist/repositories/legacy-juggler-bridge.js:363:46) at MySQLRepository.query (/api/dist/repositories/mysql.repository.js:16:21) at MyTestController.finances (/api/dist/controllers/my-test.controller.js:63:37) at invokeTargetMethod (/api/node_modules/@loopback/context/dist/invocation.js:156:49) at /api/node_modules/@loopback/context/dist/invocation.js:138:16 at Object.transformValueOrPromise (/api/node_modules/@loopback/context/dist/value-promise.js:257:16) at invokeTargetMethodWithInjection (/api/node_modules/@loopback/context/dist/invocation.js:133:28) at InterceptedInvocationContext.invokeTargetMethod (/api/node_modules/@loopback/context/dist/invocation.js:83:20) at targetMethodInvoker (/api/node_modules/@loopback/context/dist/interceptor.js:241:57) at /api/node_modules/@loopback/context/dist/interceptor-chain.js:110:20 at Object.transformValueOrPromise (/api/node_modules/@loopback/context/dist/value-promise.js:257:16) at GenericInterceptorChain.invokeNextInterceptor (/api/node_modules/@loopback/context/dist/interceptor-chain.js:105:32)

node -e 'console.log(process.platform, process.arch, process.versions.node)'
Outputs: linux x64 12.19.0

npm ls --prod --depth 0 | grep loopback
Outputs:
├── @loopback/authentication@7.0.2
├── @loopback/authorization@0.7.2
├── @loopback/boot@3.0.2
├── @loopback/context@3.12.0
├── @loopback/core@2.11.0
├── @loopback/openapi-v3@5.0.0
├── @loopback/repository@3.1.0
├── @loopback/rest@8.0.0
├── @loopback/security@0.3.2
├── @loopback/service-proxy@3.0.2
├── loopback-connector-kv-redis@4.0.0
├── loopback-connector-mysql@6.0.0
├── loopback-connector-rest@3.7.0

Related Issues

I have found #5264 which is related if the problem is in documentation

[dhmlau]

@jangeorgiev, looking at the example of https://loopback.io/doc/en/lb4/apidocs.repository.defaultcrudrepository.execute.html#example and the error message you shared above, it seems like it is expecting an array. i.e. something like:

await repo.execute("SELECT * FROM table WHERE name = ?", ["John Doe"])

[jangeorgiev]

Hey @dhmlau, the example in doc is about PositionalParameters, but according to https://loopback.io/doc/en/lb4/apidocs.repository.defaultcrudrepository.execute.html#parameters NamedParameters are available as well

[dhmlau]

From the error message, it looks like it's expecting an array not a json.
According to mysqljs/mysql#920, it seems like the mysql nodejs driver doesn't support named parameters. When I tried it, it also complains about the syntax error from the mysql code:

500 Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':name' at line 1
    at Query.Sequence._packetToError (/Users/xxx/test1020-1/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)

[jangeorgiev]

Yes, you are right mysqljs doesn't support named parameters yet, but according to this comment this feature can be handled by using named-placeholders library.

So in this case I see 4 options:

1. I have to handle myself NamedParameters by using named-placeholders.
2. I have to handle myself NamedParameters by using my custom method.
3. The feature will be handled in loopback library
4. The documentation will be edited in order to be clear that NamedParameters are not supported for SQL databases

[dhmlau]

+1 the API docs should add some text around not all databases support NamedParameters.
Would you be interested to submit a PR? Thanks.

[cassioscofield]

+1 this is a very nice feature to have, hope to see it in the next release

[stale]

This issue has been marked stale because it has not seen activity within six months. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository. This issue will be closed within 30 days of being stale.

[jangeorgiev]

+1 the API docs should add some text around not all databases support NamedParameters.
Would you be interested to submit a PR? Thanks.

Sorry, but I have no time to submit a PR.