Async function in Node.JS calling SQL Server not working properly-CodePudding

I have a function Im calling to login a user, as follows:

async function loginUser(username, password) {
  let msg = {
    success: 0,
    message: '',
  };
  sql.connect(process.env.DB_CONNSTRING, function (err) {
    if (err) {
      res.json(err);
      return;
      msg.success = '0';
      msg.message = 'ERR_04: Could not connect to server.';
      return msg;
    }

    let sqlString = ` SELECT        userid
         FROM            dbo.users
         WHERE        (email = @username) AND (password = @password)`;
    request = new sql.Request();
    request.input('username', sql.NVarChar, username);
    request.input('password', sql.NVarChar, password);
    request.query(sqlString, function (err, results) {
      if (err) {
        msg.success = '0';
        msg.message = 'ERR_011: Could not connect to server.';
        return msg;
      }
      if (results.rowsAffected[0] != 0) {
        console.log(results.recordset[0]);
        return results.recordset[0];
      } else {
        msg.success = '0';
        msg.message = 'Invalid username and password combination.';
        return msg;
      }
    });
  });
}

Please notice a console.log if the query brings back results.

Im calling that function as follows:

await loginUser(username, encryptedPassword)
  .then((result) => {
    console.log(result);
  })
  .catch((err) => {
    msg.message = 'API Error.';
    res.json(msg);
  });

Where I also have a console.log()

The problem is that the console.log for the call is coming as undefined, but the one inside the function is throwing the data.

I do not know what I'm missing here, so any help will be appreciated.

Thanks.

CodePudding user response：

If you don't need to call a function, you can try this out.

    let sqlString = ` SELECT  userid  FROM dbo.users  WHERE (email ='${username}') AND (password = '${password}')`;
    let conn = await sql.connect(process.env.DB_CONNSTRING)
    let recordset = await conn.query(sqlString)
    res.json(recordset)

CodePudding user response：

In JS if function doesn't have explicit return, it returns undefined.

So, you have a function async function loginUser: async here indicates your function might be ran asynchronous(as promise), but it doesn't return any values;

So, instead of marking you function as async, you need promisify your callbacks and return promise;

It might be something like:

const { promisify } = require('util');

async function loginUser(username, password) {
 let msg = {
   success: 0,
   message: '',
 };
 await promisify(sql.connect(process.env.DB_CONNSTRING));
 let sqlString = `SELECT  userid
 FROM dbo.users
 WHERE (email = @username) AND (password = @password)`;
 request = new sql.Request();
 request.input('username', sql.NVarChar, username);
 request.input('password', sql.NVarChar, password);
 return await promisify(request.query(sqlString));
}

Pay special attention on error handling, and where you want(need) call await, and where you don't;