node.js mysql result into a variable

Question

I've been using mountebank to do some stubbing for performance testing and its an awesome tool. The functional teams have asked if it can be repurposed to support functional testing and I'd said i'd have a look.

What I want to achieve is to select from a mysql database an account number and its account balance and then return the balance to the client (in this case a jmeter harness)

function (request, state, logger) {

logger.info('GBG - getAccountBalance');

var mysql = require('mysql');
var result = '';

var con = mysql.createConnection({
host: "localhost",
user: "user",
password: "password",
database: "customer"
});

con.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");
});  

con.query('select * from accounts', function (err, rows, fields) {
    if (err) throw err;
    console.log(rows);
    console.log('accountNumber is : ', rows[0].accountNumber);
    result = rows[0].accountNumber;
});

console.log('result is : ', result);
var response = result;

return {
    headers: {
                'Content-Type': 'application/xml',
                'Connection': 'Keep-Alive'
            },
    body: response
};      

}  

The result of the console log is:

result is :
Connected!
[ RowDataPacket { accountNumber: 777777, accountBalance: 777 } ]
accountNumber is :  777777

Not sure what I'm doing wrong and why the result is : lines comes up first despite being later in the code.

Any advice appreciated.

Full disclosure, I've been using mountebank for about two weeks so I'm a real beginner.

Answer 1

The function keyword inside connect and query is called callbacks, and only executed after the function itself is done. so your code would look like:

con.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");


con.query('select * from accounts', function (err, rows, fields) {
    if (err) throw err;
    console.log(rows);
    console.log('accountNumber is : ', rows[0].accountNumber);
    result = rows[0].accountNumber;

    console.log('result is : ', result);
    var response = result;
});        
});  

and so on, but you just introduced callback hell to your code.
async is your friend.

---

EDIT: following an example:

async.waterfall([
    function (callback) {
        //do some async function here
        con.connect(function(err) {
            if (err) throw err;
            console.log("Connected!");
            //call this when you are done
            //you can even pass param to next function
            callback(null,true);
        }); 
    },function (isConnected,callback1) {
        if !(isConnected){
        console.log("Connection failed! Skipping Query...")
        callback1(null,"Error");
        }
        //do another async function here:
        con.query('select * from accounts', function (err, rows, fields) {
            if (err) throw err;
            console.log(rows);
            console.log('accountNumber is : ', rows[0].accountNumber);
            result = rows[0].accountNumber;
            callback1(null,"Complete");
        });
    }
    ], function (err,result) {
                if(result == "Error"){
                alert("Someting went wrong!");
                }
                if(result == "Complete"){
                alert("Done!");
                }
                return 0;
            });

note:I haven't written JS for awhile. Written this off of some existing code and haven't been tested. Also, Promise is also something that would help, but haven't looked into personally. BlueBird is a library for that.

Answer 2

The simplest way to get Data form mysql database using Promise and async await. Get data dynamically by providing id to the SQL query.

With the help of following code snippet. First Your query will get execute fully the other process will execute.

response will be sent after execution of query is fully done. (sometimes response is sent first then execution of query completes)

async function getData(customerId){
    let sql = `SELECT * FROM customer_info WHERE customerID = ${customerId}`

    await connection.query(sql, (err, result) => {
        data = {
            CustomerId : result[0].customerID,
            FirstName: result[0].FirstName,
            LastName: result[0].LastName
        }
    })
}


function connectToDB(customerId){
    return new Promise((resolve, reject) => {
        getData(customerId).then(()=>resolve())
    })
}


app.get('/customer/:id', (req, res) => {

    let customerId = req.params.id
    
    // Caller Function to all functions
    async function callerFun(){

        await connectToDB(customerId);
        res.send("Execution Done");
    }

    callerFun();
})