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

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.

It can be sometimes that the express session is saved when the out direct handler/function finished.

On that situation, if you want to save your session within a new async function, you should add the next function variable in your handler.

Then use it as the callback function to save you session.

It should look like this:

router.post('/login', (req, res, next) => {
    con.query(..., 
      (err, result) => {
        for(...) {
            if(...){

                res.redirect('/'); 
                next();
                return;
            }

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

Before it happens, make use your code is executed correctly.