How to connect my route parameter in express with @mysql/x devAPI?

Question

Here is my code:

var express = require('express');
var router = express.Router();
var mysqlx = require('@mysql/xdevapi');




router.use(`/:email`, function (req, res, next){
    mysqlx.getSession( {

        user: 'username', 
        password: 'password',
        host: 'localhost', 
        port: '33060' } )

        // .then(session => {
        //   console.log(session.inspect());
        // })

        .then(function (session) {
            var db = session.getSchema('nms2019local');
            var opsTable = db.getTable('operators');
            return opsTable
                .select (['email', 'admin'])
                .where('email like :email')
                .bind('email',':email')
                .execute (function (row) {
                    console.log(row);

                });    
        })
        .then(function (myResult){
            console.log(myResult);

        })
        .catch(function (err){
            console.log(err);
        })

        next()


    });


    router.use('/', function (req, res,){

        res.send('DB Is Connected');

    });

    module.exports = router;

}

Through postman I run the following GET command:

get /expressroutename/email/[email protected] I get the following in nodemon:

GET /expressroutename/email/[email protected] 200 36.096 ms - 15
{
  getWarnings: [Function: getWarnings],
  getWarningsCount: [Function: getWarningsCount],
  fetchAll: [Function: fetchAll],
  fetchOne: [Function: fetchOne],
  getColumns: [Function: getColumns],
  getResults: [Function: getResults],
  nextResult: [Function: nextResult],
  toArray: [Function: toArray]
}

when I column out the .where command

 //.where('email like :email')

and put in the following in Postman I get in nodemon:

GET /expressroutename/email 200 45.116 ms - 15
[ '[email protected]', 1 ]
[ '[email protected]', 1 ]
{
  getWarnings: [Function: getWarnings],
  getWarningsCount: [Function: getWarningsCount],
  fetchAll: [Function: fetchAll],
  fetchOne: [Function: fetchOne],
  getColumns: [Function: getColumns],
  getResults: [Function: getResults],
  nextResult: [Function: nextResult],
  toArray: [Function: toArray]

I think its a syntax error, but I have typed it via the docs many times and it doesn't seem to work. Please help.

Thanks

Answer 1

It looks like it might be an issue with the way you are using bind().

Basically you are providing a filtering criteria 'email like :email' where :email is a placeholder that is supposed to be replaced by a specific value using bind(). However, you are assigning ':email' (the specific String value) to the placeholder, and I suspect you don't have any row in that operators table where email = ':email'. That's why when you remove the filtering criteria (specified by the where() API) you start getting proper results, since the filter isn't actually being applied anymore.

So, if you want to filter, what you need to do is provide the actual email address as the second parameter of bind(). I'm a bit rusty with Express, but I think the following should do the trick:

opsTable.select(['email', 'admin'])
  .where('email like :email')
  .bind('email', req.params.email)

Note that the first parameter of bind() is the name of the placeholder without the : (colon) prefix.

Disclaimer: I'm the lead dev of the MySQL X DevAPI Connector for Node.js