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?

Have you tried to console.log recordset? Did sql insert any data to db at all? — Georgiy Dubrov, Mar 23 '17 at 15:22
Yes, but it's not an insert. The stored procedure is select statement. — Jade Cowan, Mar 23 '17 at 15:26

score 1 · Accepted Answer · edited May 23 '17 at 12:32

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:

run x() with one arguments
run asyncFunction() with two arguments (one of which happens to be a function, but it is not called yet - just passed as an argument)
inside of asyncFunction() some async operation may be scheduled and the anonymous function is saved to be called later and the asyncFunction() returns
run z() with no arguments
sometime later the async operation finishes and the anonymous function that was saved before is run now
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?

For my own edification, how was next called before the database finished it's query? It's after the sql.connect function in the callback. Wouldn't it have only ran after the sql.connect function was executed? — Jade Cowan, Mar 23 '17 at 15:49
@cwanjt See my updated answer for a more detailed explanation. — rsp, Mar 23 '17 at 16:06

Pass variable between app.method callbacks in Express

1 Answers1

Update