How can I execute a query based on the result of another query? (Express, NodeJS, mysql)

Question

I am trying to generate a session ID when an API call is made to my 'generateSession' endpoint. I want to make sure that I don't have any duplicate session ID, so I query the database checking for a match on the generated ID. If there isn't a match, the ID is valid and I make a second query to add an active user with said session ID.

Although my first query executes, the 'id_is_valid' boolean never gets set to true so my program gets stuck in the while loop.

I am fairly new to JavaScript, but from some research, I'm pretty sure the problem is due to the asynchronous nature of the database call. However, I'm not sure where to go from there. Could anyone with more js knowledge offer me some direction?

Thanks!

var express = require('express');
var router = express.Router();

var myDB = require('../db-connection');

function generateSession() {
    var session_id = '';
    var possible = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";

    for (var i = 0; i < 30; i++) session_id += possible.charAt(Math.floor(Math.random() * possible.length));

    return session_id;
}

router.get('/generateSession', function(req, res){
    var session_id = '';
    var id_is_valid = false;
    while (!id_is_valid){
        session_id = generateSession();

        myDB.query("SELECT * FROM activeUser WHERE session_id = ?", [session_id], function(error, results, field){
            if(error) throw error;
            else{
                if (results.length === 0) is_is_valid = true;
            }
        });
    }

    myDB.query("INSERT INTO activeUser (is_registered, session_id) VALUES (0, ?)", [session_id], function(error, results, fields){
        if (error) res.send('{"success": false}');
        else res.send('{"success": true, "session_id": "' + session_id + '"}');
    });
});

Answer 1

Although my first query executes, the id_is_valid boolean never gets set to true so my program gets stuck in the while loop.

This is because db call, by nature is asynchronous. If you run the following program you will know it.

'use strict';

let id_is_valid = false;
let count = 0;

while (!id_is_valid) {
  count++;
  console.log(`No of time setTimeout Invoked ${count}`);
  setTimeout(function() { // simulating a db call that takes a second for execution
    id_is_valid = true;
  }, 1000);
}

console.log('This line wont be printed');

Outputs

No of time setTimeout Invoked 61415
No of time setTimeout Invoked 61416
No of time setTimeout Invoked 61417
^C //I killed it.

Like damitj07, I too suggest using npms like shortId for uniquely generating the sessionId. This will help you to eliminate a database call. But if your business logic restricts and you need it in the current fashion you wrote. I think we can use async & await

'use strict';

let isFound = false;
let count = 0;

function doDbQuery() {
  return new Promise((resolve, reject) => {
    setTimeout(function () {
      resolve(true);
    }, 2000);
  });
}

async function run() {
  while (!isFound) {
    count++;
    console.log(`No of time setTimeout Invoked ${count}`);
    isFound = await doDbQuery();
  }
  console.log('This line WILL BE printed');
}

run();

Output

No of time setTimeout Invoked 1
This line WILL BE printed

Making those changes to your code,

router.get('/generateSession', async function (req, res) {
  var session_id = '';
  var id_is_valid = false;
  while (!id_is_valid) {
    session_id = generateSession();
    id_is_valid = await checkSessionIdInDb(session_id);
  }

  myDB.query('INSERT INTO activeUser (is_registered, session_id) VALUES (0, ?)', [session_id], function (error, results, fields) {
    if (error) {
      res.send('{"success": false}');
    } else {
      res.send('{"success": true, "session_id": "' + session_id + '"}');
    }
  });
});

function checkSessionIdInDb() {
  return new Promise((resolve, reject) => {
    myDB.query('SELECT * FROM activeUser WHERE session_id = ?', [session_id], function (error, results, field) {
      if (error) {
        return reject(error);
      } else {
        if (results.length === 0) {
          resolve(true);
        }
        resolve(false);
      }
    });
  });