Pass variable between app.method callbacks in Express

Question

I'm trying to get a recordset from my database and store it in a variable so I can use it in a subsequent callback that renders a view. I've tried creating a variable on the req object, but it comes back as undefined when I call it in the next callback.

app.get(
    '/behaviorchart',
    authenticated,
    function(req, res, next) {
        sql.connect(dbconfig, function (err) {
            new sql.Request()
                .input('TeacherID', sql.Int, req.user.UserID)
                .execute('dbo.GetTeacherStudentCards', function (err, recordsets, returnValue) {
                    req.studentCards = recordsets[0] // recordset without return value
                });
            sql.on('error', function (err) {
                // ... error handler 
            });
        });
        next();
    },
    function(req, res) {
        console.log(req.studentCards);
        res.render('behaviorchart.ejs', {
            user : req.user // gets user from session for ejs template
        }); 
    }
);

I've read that some people, especially when they're using the variable in their view, create a property on res.locals, but I can't seem to get this to work either.

What am I doing wrong here?

Answer 1

You have next() called too soon here:

function(req, res, next) {
    sql.connect(dbconfig, function (err) {
        new sql.Request()
            .input('TeacherID', sql.Int, req.user.UserID)
            .execute('dbo.GetTeacherStudentCards', function (err, recordsets, returnValue) {
                req.studentCards = recordsets[0] // recordset without return value
            });
        sql.on('error', function (err) {
            // ... error handler 
        });
    });
    // THIS IS CALLED BEFORE THE DATABASE QUERY FINISHES
    next();
},

Try something like this:

function(req, res, next) {
    sql.connect(dbconfig, function (err) {
        new sql.Request()
            .input('TeacherID', sql.Int, req.user.UserID)
            .execute('dbo.GetTeacherStudentCards', function (err, recordsets, returnValue) {
                req.studentCards = recordsets[0] // recordset without return value
                // CALL NEXT HERE:
                next();
            });
        sql.on('error', function (err) {
            // ... error handler 
        });
    });
},

But also check for errors:

function(req, res, next) {
    sql.connect(dbconfig, function (err) {
        new sql.Request()
            .input('TeacherID', sql.Int, req.user.UserID)
            .execute('dbo.GetTeacherStudentCards', function (err, recordsets, returnValue) {
                // CHECK FOR ERRORS:
                if (err) {
                    return next(err);
                }
                req.studentCards = recordsets[0] // recordset without return value
                // CALL NEXT HERE:
                next();
            });
        sql.on('error', function (err) {
            // ... error handler 
        });
    });
},

Update

Some explanation of why it happens. When you run a call like this:

x('something');
asyncFunction(args, function (err) {
  y();
});
z();

What actually happens is:

1. run x() with one arguments
2. run asyncFunction() with two arguments (one of which happens to be a function, but it is not called yet - just passed as an argument)
3. inside of asyncFunction() some async operation may be scheduled and the anonymous function is saved to be called later and the asyncFunction() returns
4. run z() with no arguments
5. sometime later the async operation finishes and the anonymous function that was saved before is run now
6. the anonymous function runs y()

See this answer where I explain the order of execution and control flow in cases like this in more detail:

• Node.js: Asynchronous Callback Execution. Is this Zalgo?