Javascript/ Node.js not waiting for sql query to come back with a response before it continues the code

Question

I have a .js file that calls an external .js file that runs the following code:

const sql = require('../../node_modules/mysql');

module.exports =
    {
        connect_to_db: function (sql_query)
        {
            let con = sql.createConnection({
                host: "localhost",
                user: config.server_username,
                password: config.server_password,
                database: config.database_name
            });

            con.connect((err)=> {
                if (err){
                    console.log("Problem connecting to the DB!");
                    return;
                }
                console.log("Connected to the DB!");
            });

            con.query(sql_query, (err, result) => {
                if (err) throw err;
                console.log('Data received from the DB');
                console.log(result);
                return result;
            });

            con.end((err) => {});


        }
    };

Which is run like:

const connect_to_DB = require('DB_Connection');     
let sql_query = "SELECT * FROM table";
                    database_results.push(connect_to_DB.connect_to_db(sql_query));

                    console.log(database_results);

however this results in the code finishing before the sql query comes back with a result (data removed):

[ undefined ]

Connected to the DB!
Data received from the DB
[ RowDataPacket {
    mail_id: ,
    from: ,
    to: ',
    subject: ,
    message: ,
    date:,
    read_date:  } ]

Process finished with exit code 0

It looks like the push of the result is coming back as undefined as there is nothing to push at the point it does this. However I want it to wait until the response from the query comes back before it continues.

I was thinking of a promise perhaps but not sure if that would work something like:

    const sql = require('../../node_modules/mysql');

module.exports =
    {
        connect_to_db: function (sql_query)
        {
            return new Promise((resolve, reject) => {
                (async () => {
                    let con = sql.createConnection({
                        host: "localhost",
                        user: config.server_username,
                        password: config.server_password,
                        database: config.database_name
                    });

                    con.connect((err)=> {
                        if (err){
                            console.log("Problem connecting to the DB!");
                            return;
                        }
                        console.log("Connected to the DB!");
                    });

                    con.query(sql_query, (err, result) => {
                        if (err) throw err;
                        console.log('Data received from the DB');
                        console.log(result);
                        resolve();
                        return result;
                    });

                    con.end((err) => {});

                })();
            });
        }
    };

but when I run this I get this back:

[ Promise { <pending> } ]

I just need some help in order for the result to come back then the code to continue.

How do you know that 'con' is ready for use, createConnection may not be done before progressing to con.connect, can you display 'con' to the console before using? — SPlatten, Nov 21 '18 at 08:21
Your right, the state is disconnected before and after the con.connect call — Daniel, Nov 21 '18 at 08:32
Can you wait for the connection and take appropriate action on failure or success? — SPlatten, Nov 21 '18 at 08:38
Can you give some example code as I've tried await con.connect to no avail. — Daniel, Nov 21 '18 at 08:39
Sorry, I'm not familiar with the API you are using, there must be some way of waiting or a callback to act on the result. — SPlatten, Nov 21 '18 at 08:40
let sql_query = "SELECT * FROM table"; await connect_to_DB.connect_to_db(sql_query).then(function (value) { database_results.push(value); }); — Daniel, Nov 21 '18 at 08:44
this then waits until the query finishes before it continues. Thank you for your help — Daniel, Nov 21 '18 at 08:44

Arul Vyas Mohan · Answer 1 · 2018-11-21T09:10:27.170

According to my view, The best possible way of solving this is by using callbacks in Node js.

Node js executes codes sychronously, let me explain by explaining what happened at your code

database_results.push(connect_to_DB.connect_to_db(sql_query));
console.log(database_results);

Here in your code console.log(database_results) is returned before executing the function connect_to_DB.connect_to_db(sql_query))

Your DBConnection.js can be modified as:

const sql = require('mysql');

exports.connect_to_db = function (sql_query, callback) {
let con = sql.createConnection({
    host: "localhost",
    user: config.server_username,
    password: config.server_password,
    database: config.database_name
});

con.connect((err) => {
    if (err) {
        console.log("Problem connecting to the DB!");
        return;
    }
    console.log("Connected to the DB!");
});

con.query(sql_query, (err, result) => {
    if (err) callback(err);
    console.log('Data received from the DB');
    console.log(result);
    callback(result);
});

con.end((err) => { });
};

and the external js that calls the function connect_to_db function can be modified as:

'use strict';

const connect_to_DB = require('DB_Connection');
let sql_query = "SELECT * FROM table";
connect_to_DB.connect_to_db(sql_query, function (data, err) {
  if (err) {
    console.log(err);
  }
  else {
    database_results.push(data);
  }
});

console.log(database_results);

to know more about callbacks visit

score 0 · Answer 2 · answered Nov 21 '18 at 09:17

You don't need to use promises and async/await in the same piece of code. Try something like this:

module.exports =
{
    connect_to_db: async function (sql_query)
    {
        let con = sql.createConnection({
            host: "localhost",
            user: config.server_username,
            password: config.server_password,
            database: config.database_name
        });

        con.connect((err)=> {
            if (err){
                console.log("Problem connecting to the DB!");
                return;
            }
            console.log("Connected to the DB!");
        });

        return await con.query(sql_query);
    }
};

and then

const connect_to_DB = require('DB_Connection');     
let sql_query = "SELECT * FROM table";
database_results.push(await connect_to_DB.connect_to_db(sql_query));
console.log(database_results);

Note that sice await keyword id only allowed inside async functions, this line database_results.push(await connect_to_DB.connect_to_db(sql_query)); should be inside an async function to work

Javascript/ Node.js not waiting for sql query to come back with a response before it continues the code

2 Answers2