NodeJS await/async with nested MySQL query

You have multiple problems in your program that you have to update.

First of all you must not use a global variable to store a per request database connection. If two requests arrive at the same time then one request would overwrite the connection of the other requests created, so you might use the same connection for both requests, and/or you don't close one of the connections which results in a dangling connection, which in the worst case could render your application unresponsive.

To resolve that issue you have to pass the connection with the request object:

app.use(async function(req, res, next) {
  try {
    if( req.dbConnection ) {
      // ensure that req.dbConnection was not set already by another middleware
      throw new Error('req.dbConnection was already set')
    }

    let connection = mysql.createConnection({
      host: 'xx',
      user: 'xx',
      password: 'xx',
      database: 'xx'
    });

    res.on("finish", function() {
      // end the connection after the resonponse was send
      req.dbConnection.end()
    });

    // assign a promise base version of connection to request
    req.dbConnection = connection.promise()

    // wait for the connection to be established
    await connection.connect();
    next();
  } catch(err) {
    next(err);
  }
});

To access the per request defined connection you would do something like this:

app.get('/', async function(req, res, next) {
   try {
     await checkIfUserCodeExist(req.dbConnection)

     // so something here after `checkIfUserCodeExist` finished
   }  catch(err) {
     next(err); // if an error occured pass it to the next
   }
})

async is ment to be used with await if you don't have a await in your function body then you don't need a async before the function.

If you don't have await in the function body then you need to return the Promise chain from the function so that the caller can wait for the function to be finished:

function checkIfUserCodeExist(connection) {
  return connection.query(sqlCheckIfExist)
    .then(([rows, fields]) => {
      if (rows == 0) {
        console.log("Non esiste!")

        return res.send(JSON.stringify({
          "status": 500,
          "response": "codeNotExist"
        }));
      }
      console.log("Esiste!")
      console.log(rows[0].my_refer);
      return  checkIfCodeIsSameAsMine(connection)
    })
}

function checkIfCodeIsSameAsMine(connection) {
  return connection.query(sqlCodeCheckSameAsMine)
    .then(([rows, fields]) => {
      if (rows == friendReferCode) {
        console.log("Codice uguale!")
        return res.send(JSON.stringify({
          "status": 500,
          "response": "sameCodeAsMine"
        }));
      }
      console.log("Codice non uguale!")
    })
}

If you want to go with async it would look like this:

async function checkIfUserCodeExist(connection) {
  let [rows, fields] = await connection.query(sqlCheckIfExist)

  if (rows == 0) {
    console.log("Non esiste!")
    return res.send(JSON.stringify({
      "status": 500,
      "response": "codeNotExist"
    }));
  }

  await checkIfCodeIsSameAsMine()

  console.log("Esiste!")
  console.log(rows[0].my_refer);
}

async function checkIfCodeIsSameAsMine(connection) {
  let [rows, fields] = await connection.query(sqlCodeCheckSameAsMine)

  if (rows == friendReferCode) {
    console.log("Codice uguale!")
    return res.send(JSON.stringify({
      "status": 500,
      "response": "sameCodeAsMine"
    }));
  }

  console.log("Codice non uguale!")
}

You would avoid something like:

return res.send(JSON.stringify({
  "status": 500,
  "response": "codeNotExist"
}));

Instead of that you would throw a custom error like:

throw new CustomError(500, "codeNotExist")

And have an error middleware:

app.use(function(err, req, res, next) {
  return res.send({
    "status": err.status,
    "response": err.message
  });
})

So you have only one place where you create the error response and you can create changes to that error response when ever necessary, e.g. add some additional logging.

EDIT (to match the updated question)

/* GET users listing. */
router.post('/', function(req, res, next) {
  var uid = req.body.uid;
  var friendReferCode = req.body.friendReferCode;

  var sqlCheckIfExist = "SELECT my_refer FROM hub_user WHERE my_refer = '" + friendReferCode + "'";
  var sqlCodeCheckSameAsMine = "SELECT my_refer FROM hub_user WHERE uid = '" + uid + "'";

  function checkIfUserCodeExist() {
    return req.dbConnection.query(sqlCheckIfExist)
      .then(([rows, fields]) => {
        if (rows == 0) {
          console.log("Non esiste!")

          return res.send(JSON.stringify({
            "status": 500,
            "response": "codeNotExist"
          }));
        }
        console.log("Esiste!")
        console.log(rows[0].my_refer);
        return checkIfCodeIsSameAsMine(connection)
      })
  }

  function checkIfCodeIsSameAsMine() {
    return req.dbConnection.query(sqlCodeCheckSameAsMine)
      .then(([rows, fields]) => {
        if (rows == friendReferCode) {
          console.log("Codice uguale!")
          return res.send(JSON.stringify({
            "status": 500,
            "response": "sameCodeAsMine"
          }));
        }
        console.log("Codice non uguale!")
      })
  }

  checkIfUserCodeExist()
   .catch(next)
});

It might be happening because you terminate your connection at the end of the checkIfUserCodeExist function. Remove the following line and I think it's going to work:

connection.end()

OR it you want to open and close it every time you can create a method with will return new connection and invoke it before making any DB queries. example:

 function getMysqlConnection() {
     const connection = mysql.createConnection({
         host: 'xx',
         user: 'xx',
         password: 'xx',
         database: 'xx'
     });
     connection.connect();
     return connection;
 }

 var sqlCheckIfExist = "SELECT my_refer FROM hub_user WHERE my_refer = '" + friendReferCode + "'";
 var sqlCodeCheckSameAsMine = "SELECT my_refer FROM hub_user WHERE uid = '" + uid + "'";
 async function checkIfUserCodeExist() {
     const connection = getMysqlConnection();
     connection.promise().query(sqlCheckIfExist)
         .then(([rows, fields]) => {
             if (rows == 0) {
                 console.log("Non esiste!")
                 return res.send(JSON.stringify({ "status": 500, "response": "codeNotExist" }));
             }
             checkIfCodeIsSameAsMine()
             console.log("Esiste!")
             console.log(rows[0].my_refer);
         })
         .catch(console.log)
         .then(() => connection.end());
 }

 async function checkIfCodeIsSameAsMine() {
     const connection = getMysqlConnection();
     connection.promise().query(sqlCodeCheckSameAsMine)
         .then(([rows, fields]) => {
             if (rows == friendReferCode) {
                 console.log("Codice uguale!")
                 return res.send(JSON.stringify({ "status": 500, "response": "sameCodeAsMine" }));
             }
             console.log("Codice non uguale!")
         })
         .catch(console.log)
         .then(() => connection.end());
 }

 checkIfUserCodeExist()

Okay, there are multiple problems with your code. I will start with addressing your specific question and then give some extra tips. :)

You problem lies within this logic:

connection.promise().query(sqlCheckIfExist)
    .then(([rows, fields]) => {
    // some code 

    checkIfCodeIsSameAsMine()

   // some code
  })
    .catch(console.log)
    .then(() => connection.end());

The checkIfCodeIsSameAsMine() function is asynchronous. So, what happens in this chain of code is that you call checkIfCodeIsSameAsMine() but you do not wait for its result and immediately jump to the last then() in which you close the DB connection. So, in essence, the code that is executed in checkIfCodeIsSameAsMine() is being executed after you close the connection.

You should return checkIfCodeIsSameAsMine(). This way you will wait for a Promise response from that function.

Now to my additional points.

First, "SELECT my_refer FROM hub_user WHERE uid = '" + uid + "'"; is BAD. You expose your application to vulnerabilities like SQL injection. You should escape the dynamic values in the SQL query through some parsing functionality. This is usually done through the ORM (the connection() that you use).

Second, if you use async functions then use the respective await functionality. Like this:

async function checkIfUserCodeExist() {
  let rows, fields;

  try {
    [rows, fields] = await connection.promise().query(sqlCheckIfExist);
  } catch (err) {
    console.log(err);
  }
  if (rows == 0) {
    console.log("Non esiste!");
    return res.send(JSON.stringify({
      "status": 500,
      "response": "codeNotExist"
    }));
  }
  console.log("Esiste!");
  console.log(rows[0].my_refer);

  let result;
  try {
    result = await checkIfCodeIsSameAsMine();
  } catch (err) {
    console.log(err);
  }

  // do something with "result" if you wish

  await connection.end();
}