NodeJS MySQL Async- Return object back to caller function

Question

I am brand new to NodeJS and am struggling to find a solution to making a reusable function to execute a query (passed via a parameter) and then simply return the response to the caller. I want to do it this way as there will be over 100 functions requiring database queries and it'll help reduce code size.

I’ve tried the following as a very primitive test, but the dbA_Read_Data() function always returns undefined, because it's asynchronous. However, 6 days later, and over 15 alternative callback/promise solutions that work for some haven't worked for me and I am no closer to solving this issue and accomplishing the following.

const { Databases } = require('../../utils/enums');
var mysql = require('mysql');


function getAllUsers() {
    //This function would be called when an endpoint is hit in my HapiJS system.
    var myQuery = "SELECT * FROM Users WHERE Account_Status== 'ACTIVE' ";
    var response = dbA_Read_Data(myQuery);

    return response;
}




//dbA => website database, contains users/permissions/etc
//Other databases... you get the idea

function dbA_Read_Data(query) {
    dbConn = createConnection("localhost", Databases.db_A.name, Databases.db_A.username, Databases.db_A.password);

    dbConn.connect();
    dbConn.query(query, function(err, rows, fields) {
        if (err) throw err;
        var myResponseObject = {
            query: query,
            data: {
                count: rows.length,
                records: rows
            }
        }
    });
}



function createConnection(host, database, user, password) {
    var connection = mysql.createConnection({
        host: host,
        user: user,
        password: password,
        database: database
    });

    return connection;
}

How do I get the function dbA_Read_Data(query) to return the response object to the caller function?

I have read this 3 times and tried to follow it, but I'd still either get undefined or just errors. I need a bit more specific guidance — user5156141
– user5156141, Commented Jan 30, 2017 at 17:07
You will always get undefined if you don't return anything from the function. You can't "simply return the response to the caller". That's impossible to do with an asynchronous response. Stop trying to return the response and return a Promise instead. The linked question explains how to do that. — Paul
– Paul, Commented Jan 30, 2017 at 17:09

piotrbienias · Accepted Answer · 2017-02-01 14:25:46Z

1

As in the comments section, the database call conn.query is asynchronous so it is impossible to simply return the result. In order to obtain the result of this query and operate on it, you could use a Promise.

I suppose your dbA_Read_Data(query) function could look like that:

function dbA_Read_Data(query){
    var dbConn = createConnection("localhost", Databases.db_A.name, Databases.db_A.username, Databases.db_A.password);

    dbConn.connect();

    return new Promise(function(resolve, reject){

        dbConn.query(query, function(err, rows, fields) {
            if ( err ) {
                reject(err);
            } else {
                var myResponseObject = {
                    query: query,
                    data: {
                        count: rows.length,
                        records: rows
                    }
                }

                resolve(myResponseObject);
            }
        });

    });
}

Then your getAllUsers() function call would require .then() in order to obtain the results:

function getAllUsers(){
    var myQuery = "SELECT * FROM Users WHERE Account_Status== 'ACTIVE' ";

    return dbA_Read_Data(myQuery).then(function(result){
        return result;
    }).catch(function(error){
        // handle the error properly here...
        console.log('error in database operation');
    });
}

getAllUsers().then(function(users){
    // do something with the result...
    console.log(users);
});

answered Feb 1, 2017 at 14:25

piotrbienias

7,4211 gold badge32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5156141 Over a year ago

Hello. Thank you. I already solved this in a similar (if not the same) way you've shown

Collectives™ on Stack Overflow

NodeJS MySQL Async- Return object back to caller function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related