Async/Await nodejs mysql function

Question

I have a problem with nodejs, in practice as you can see from the two modules that I entered via google drive link, I have a problem with the login form because although I have entered asyn / await in both the functions calls it does not wait but it goes on anyway and in fact it prints the return variable as "undefined". Thanks in advance to everyone who answers!

Router.js :

router.post('/ajax/login', function(req, res, next) {
 (async () => {
var loginDb = await login.login(req.body.matricola, req.body.password);

console.log(loginDb);
res.json({output: loginDb, matricola: req.body.matricola});
 })();
})

login.js

var response;
async function login(matricola, password){

var conn = connect();

await conn.connect(function(err){
if(!err){
  //query ricerca utente nel db
  password = md5(password); //cifro la password
  conn.query("SELECT * FROM user WHERE matricola=? AND password=?",[matricola, password] 
,function(err, result){
    if(!err){
      if(result.length == 1 && matricola == result[0].matricola && password == result[0].password){
        //Invio segnale di logged-in al client
        response = "logged-in";

      }
      else{
       response = 'error-login';
      }
     }
   })
  }
  else{
  response = 'error-db';
  }
})

return response;

}

exports.login = login;

await only does something useful if you await a promise. If you're using the original mysql library, it does not do promises so your await does nothing. You would need the mysql2 library for built-in promise support or you'd have to use one of the exteranal libraries that offers a promise wrapper for mysql. I'd also suggest you read about how async and await actually work so you better understand how they work and where they work. So many people think await has some magic powers to just wait for some asynchronous operation. It does not. — jfriend00
– jfriend00, Commented Dec 5, 2019 at 20:21
Also, as a design warning, you will pretty much never use await in combination with traditional callbacks because traditional callbacks are a sign that there is no promise interface in action there. So, await conn.connect(someCallback) will never be how you code with await. It will just be await conn.connect(). — jfriend00
– jfriend00, Commented Dec 5, 2019 at 20:24

Sandeep Patel · Accepted Answer · 2019-12-05 20:39:35Z

1

you can use await on a function that returns a promise.

Change login.js something as below:

function login(matricola, password){

    return new Promise(function(resolve,reject){
     conn.connect(function(err){
       if(!err){
      //query ricerca utente nel db
       password = md5(password); //cifro la password
        conn.query("SELECT * FROM user WHERE matricola=? AND password=?",[matricola, password] 
      ,function(err, result){
        if(!err){
           if(result.length == 1 && matricola == result[0].matricola && password == result[0].password){
            //Invio segnale di logged-in al client
            resolve("logged-in")
          }
           reject(err);
         }
       })
      }
      reject(err)
    })
    }) 
    }

Mysql2 has inbuilt support for promises. I would recommend using mysql2.
Sample code from mysql2 documentation:

async function main() {
  // get the client
  const mysql = require('mysql2/promise');
  // create the connection
  const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test'});
  // query database
  const [rows, fields] = await connection.execute('SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Morty', 14]);
}

Also, use try-catch block to handle errors :

try {
      const [rows, fields] = await connection.execute('SELECT * FROM `table` 
  WHERE `name` = ? AND `age` > ?', ['Morty', 14]);
}
catch(err) {
 handle error here
}

edited Dec 5, 2019 at 20:39

answered Dec 5, 2019 at 20:24

Sandeep Patel

5,1483 gold badges23 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jfriend00 Over a year ago

Much, much better to use a promise wrapper for the database APIs than to promisify manually at this level. Doing it this way leads to all sorts of problems and lack of code reuse. It's easy to make mistakes when mixing promises with nested callbacks like this (I spot a few mistakes already). Promisify the underlying asynchronous operations and use promise logic to control the flow rather than a mix of callback logic and promise logic.

Kwame Opare Asiedu · Accepted Answer · 2019-12-05 20:28:13Z

From the way your login.js flows, it looks like you'll have to supply a callback function to get the result from it.

login.js

function login (matricola, password, cb) {
    const conn = connect();

    conn.connect (function(err){
        // If there's an error in setting up the connection to db, terminate immediately
        if (err) return cb (new Error("DB connection error"));

        const hashedPassword = md5 (password);
        const query = "SELECT * FROM user WHERE matricola=? AND password=?";
        conn.query (query, [matricola, hashedPassword], function (err, result) {
            // If there's an error with the query, terminate immediately
            if (err) return cb(new Error("Query error"));

            // Matricola and passwords match, so return a positive response
            if (result.length === 1 && matricola === result[0].matricola && hashedPassword === result[0].password) 
                return cb (null, result[0]);
            return cb (new Error("User not found"));
        });
    });
}

exports.login = login

Then in your router.js, you call it like so router.js

router.post ('/ajax/login', function (req, res, next) {
    login.login (req.body.matricola, req.body.password, function (err, result) {
        if (err) return res.status(401).send("Invalid matricola/password");
        res.json ({ output: result, matricola: req.body.matricola });
    });
})

The IIFE used within the route handler is unnecessary...

Collectives™ on Stack Overflow

Async/Await nodejs mysql function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related