node.js - NodejS .then (promise) Caller gets bad results before async function finishes

Question

This is a reboot of the question I asked here: NodeJS then/catch in a function seems to be catching code outside the function I changed so many times, I think this is basically a different question, although similar.

My NodeJS code:

var express = require('express');
var app = express();

// config for your database
var config = {
    user: 'ReadOnlyUser1',
    password: 'Password1',
    server: 'localhost\SQLEXPRESS', 
    database: 'StudentsOld' 
};

var lookupStudentId = 31; 


const sql = require('mssql');

var connPool = new sql.ConnectionPool(config);

function getStudent(studentID) 
{
    console.log("getStudent"); 

    sql.connect(config).then(pool => {
        // Query 
        return pool.request()
        .input('input_parameter', sql.Int, studentID)
        .query('select student_firstname, student_lastname from students where student_id = @input_parameter')
    }).then(function(result) {
        console.log("getStudent:then(result=>"); 
        console.dir(result);
        sql.close(); 
        //return result; 
    })
    .catch(err => {
        // ... error checks 
        console.log("DB Error1: " + err); 
    })

    sql.on('error', err => {
        // ... error handler 
        console.log("DB Error2: " + err); 
    })
}


app.get('/student', function(request, response){
    console.log('Neal Test1 - start app.get for /student lookupStudentId = ' + lookupStudentId); 
    getStudent(lookupStudentId)
        .then (function(recordset)  {  /* this is line 82 of the error */
            objType = Object.prototype.toString.call(recordset);
            console.log('Back from getStudent objType=' + objType  ); 
            console.dir('recordSet=' + recordSet); 
            response.status(200).send(recordset); 
        })
        .catch (function(err) {
            objType = Object.prototype.toString.call(recordset);
            console.log('ERR: Back from getStudent objType=' + objType  ); 
            console.log("error400=" + err); 
            console.log("empty test=" + Object.keys(err).length); 
            response.status(400).send(err);
        })
    return;
});

app.listen(3000, function () {
    console.log('Express server is listening on port 3000');
});

Console.Log:

Express server is listening on port 3000
Neal Test1 - start app.get for /student lookupStudentId = 31
getStudent
TypeError: Cannot read property 'then' of undefined
    at C:Software
odejswisdomcallsindexPromises.js:82:9
    at Layer.handle [as handle_request] (C:Software
odejswisdomcalls
ode_modulesexpresslib
outerlayer.js:95:5)
    at next (C:Software
odejswisdomcalls
ode_modulesexpresslib
outer
oute.js:137:13)
    at Route.dispatch (C:Software
odejswisdomcalls
ode_modulesexpresslib
outer
oute.js:112:3)
    at Layer.handle [as handle_request] (C:Software
odejswisdomcalls
ode_modulesexpresslib
outerlayer.js:95:5)
    at C:Software
odejswisdomcalls
ode_modulesexpresslib
outerindex.js:281:22
    at Function.process_params (C:Software
odejswisdomcalls
ode_modulesexpresslib
outerindex.js:335:12)
    at next (C:Software
odejswisdomcalls
ode_modulesexpresslib
outerindex.js:275:10)
    at expressInit (C:Software
odejswisdomcalls
ode_modulesexpresslibmiddlewareinit.js:40:5)
    at Layer.handle [as handle_request] (C:Software
odejswisdomcalls
ode_modulesexpresslib
outerlayer.js:95:5)
getStudent:then(result=>
{ recordsets: [ [ [Object] ] ],
  recordset:
   [ { student_firstname: 'Jonah                ',
       student_lastname: 'Hill                    ' } ],
  output: {},
  rowsAffected: [ 1 ] }

So what this tells me is that the .then statement of the calling routine is running and getting the "undefined" probably while the function is still trying to get it's database connection.

How do I keep the Async best practice/model, and be able to handle my data properly after it has been retrieved?

I read this question: Node exits before async function completes which seems on target - but if that's the answer, how would I wrap that around my SQL request? I'm going to try it, and if I get it, I will post answer here.

Revision 2

function getStudent(studentID) 
{
    console.log("---------getStudent"); 

    sql.on('error', err => {
        // ... error handler 
        console.log("DB Error2: " + err); 
    })

    return sql.connect(config).then(pool => {
            // Query 
            return pool.request()
            .input('input_parameter', sql.Int, studentID)
            .query('select student_firstname, student_lastname from students where student_id = @input_parameter')
        }).then(function(result) {
            console.log("getStudent:then(result=>"); 
            console.dir(result);
            sql.close(); 
            return result; 
        })
        .catch(err => {
            // ... error checks 
            console.log("DB Error1: " + err); 
        })

}


app.get('/student', function(request, response){
    console.log('==================Neal Test1 - start app.get for /student lookupStudentId = ' + lookupStudentId+ '======================='); 

    //process.on('unhandledRejection', r => function(){});

    getStudent(lookupStudentId)
        .then (function(recordset)  {
            console.log('Back from getStudent'); 
            objType = Object.prototype.toString.call(recordset);
            console.log('objType=' + objType  ); 
            console.dir('recordSet=' + recordSet); 
            response.status(200).send(recordset); 
        })
        .catch (function(err) {
            objType = Object.prototype.toString.call(recordset);
            console.log('ERR: Back from getStudent objType=' + objType  ); 
            console.log("error400=" + err); 
            console.log("empty test=" + Object.keys(err).length); 
            response.status(400).send(err);
        })
    return;
});

Console.Log:

    ==================Neal Test1 - start app.get for /student lookupStudentId = 31=======================
    ---------getStudent
    getStudent:then(result=>
    { recordsets: [ [ [Object] ] ],
      recordset:
       [ { student_firstname: 'Jonah                ',
           student_lastname: 'Hill                    ' } ],
      output: {},
      rowsAffected: [ 1 ] }
Back from getStudent
objType=[object Object]
(node:26212) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 1): ReferenceError: recordset is not defined

At this point the program seems to hang. Never goes back to browser.

Answer 1

getStudent does not return anything (so undefined), so it does not have the then property.

It should return a promise, so add return here:

return sql.connect(config).then(pool => {

... and move the sql.on call before that return.

Secondly, uncomment this line:

// return result;

Because that result needs to be passed along the promise chain. You'll fetch its value as recordset in the app.get callback function.