Why does my express-session variable not get saved if I set it from in a nested query function?

Question

The following code handles post requests to my application's login page.

If the login credentials are correct it sets the is_logged_in session variable to true as well as the user_id to the user id fetched from a database.

In the query that gets the credentials, if everything passes I have a second query that gets the student id. For some odd reason when I try to set the req.session.student_id variable and log the session object I don't see it, it only sets the is_logged_in and user_id variables that are set from outside the second query function.

router.post('/login', (req, res) => {
    con.query("SELECT id, username, password FROM authorizedusers;", 
      (err, result) => {
        if(err) throw err;

        for(var i = 0; i < result.length; i++) {
            if(req.body.username === result[i].username 
               && req.body.password === result[i].password){

            con.query(`SELECT id FROM students WHERE user_id = 
              ${result[i].id};`, (err, result) => {

                    if(err) throw err;
                    
                    req.session.student_id = result[0].id;
                });
                
                req.session.is_logged_in = true;
                req.session.user_id = result[i].id;
                return res.redirect('/');
            }

        }
        return res.render('login', {
            msg: "Error! Invalid Credentials!"
        });
    });
});

This is what I get when I log the session object.

Session {
  cookie: { path: '/', _expires: null, originalMaxAge: null, httpOnly: true },
  is_logged_in: true,
  user_id: 3
}

However, if I try to set the student_id in the same scope as the other ones, as follows:

router.post('/login', (req, res) => {
    con.query("SELECT id, username, password FROM authorizedusers;", 
      (err, result) => {
        if(err) throw err;

        for(var i = 0; i < result.length; i++) {
            if(req.body.username === result[i].username 
              && req.body.password === result[i].password){

                con.query(`SELECT id FROM students WHERE user_id = 
                  ${result[i].id};`, (err, result) => {

                    if(err) throw err;
                    
                });
                
                req.session.student_id = "test";
                req.session.is_logged_in = true;
                req.session.user_id = result[i].id;
                return res.redirect('/');
            }

        }
        return res.render('login', {
            msg: "Error! Invalid Credentials!"
        });
    });
});

It does work, and this is what I get after when I log the session object.

Session {
  cookie: { path: '/', _expires: null, originalMaxAge: null, httpOnly: true },
  student_id: 'test',
  is_logged_in: true,
  user_id: 3
}

Why does this happen, and how can I fix it?

update

jfriend00 Answered my question, and said that I could solve my issue by using the mysql2 module instead of the mysql module.

I would like to use the mysql module though. So I would appriceate if somebody could answer how solve this issue while using the mysql module.

Answer 1

This is because of the non-blocking, asynchronous nature of the con.query() function call. It starts the asynchronous operation and then executes the lines of code after it. Then, sometime LATER, it calls its callback. So, in this code of yours with my adding logging:

router.post('/login', (req, res) => {
    con.query("SELECT id, username, password FROM authorizedusers;", (err, result) => {
        if(err) throw err;

        for(var i = 0; i < result.length; i++) {
            if(req.body.username === result[i].username && req.body.password === result[i].password){
                con.query(`SELECT id FROM students WHERE user_id = ${result[i].id};`, (err, result) => {
                    if(err) throw err;
                    console.log("got student_id");
                    req.session.student_id = result[0].id;
                });
                req.session.is_logged_in = true;
                req.session.user_id = result[i].id;
                console.log("redirecting and finishing request");
                return res.redirect('/');
            }

        }
        return res.render('login', {
            msg: "Error! Invalid Credentials!"
        });
    });
});

You would get this logging:

redirecting and finishing request
got student_id

So, you finished the request BEFORE you got the student_id and set it into the session. Thus, when you go to immediately try to use the data from the session, it isn't there yet.

---

This is not a particularly easy problem to solve without promises because you have an older-style asynchronous call inside a for loop. This would be much easier if you use the promise interface for your SQL library as then you can sequence things and can use await to run only one query at a time - your current loop is running all the queries from the for loop in parallel which is not going to make it easy to select the one winner and stop everything else.

If you switch to mysql2 and then use the promise version:

const mysql = require('mysql2/promise');

Then, you can do something like this (I'm no expert on mysql2, but hopefully you can see the idea for how you use a promise interface to solve your problem):

router.post('/login', async (req, res) => {
    try {
        const [rows, fields] = await con.query("SELECT id, username, password FROM authorizedusers;");
        for (let i = 0; i < rows.length; i++) {
            if (req.body.username === rows[i].username && req.body.password === rows[i].password) {
                let [students, fields] = await con.query(`SELECT id FROM students WHERE user_id = ${rows[i].id};`);
                req.session.student_id = students[0].id;
                req.session.is_logged_in = true;
                req.session.user_id = rows[i].id;
                return res.redirect('/');
            }
        }
        return res.render('login', { msg: "Error! Invalid Credentials!" });
    } catch (e) {
        console.log(e);
        res.sendStatus(500);
    }
});

Your existing implementation has other deficiencies too that are corrected with this code:

1. The loop variable declared with var will never be correct inside the asynchronous callback function.
2. Your if (err) throw err error handling is insufficient. You need to capture the error, log it and send an error response when you get an error in a response handler.
3. You will always call res.render() before any of the database calls in the loop complete.