Search code examples
node.jsexpressnode-mysql

Undefined value after returning an array of values from a MySQL query in a different file

I'm using the Mysql connector because I need to get some data from my database, but I'm having the following issue:

I have two files server.js and dbConnection.js and I have a return in the dbConnection.js file which should return an array and it should show it in the server.js file. However, it prints out an undefined array. I don't understand what I'm doing wrong since I also tried to print out the array before reurning it in the dbConnection.js file and it's shown with the data.

server.js:

const express = require('express');
const dbConnection = require('./dbConnection.js');

app.get('/', function (req, res) {
    const val1 = new Promise((resolve, reject) => {
        dbConnection
            .getData()
            .then(data => {
        resolve(data)
                })
        .catch(err => reject('error'))
    });

    Promise.all([val1])
    .then(data => {
        console.log(data) //here it prints out [undefined]
    });        
});

dbConnection.js:

const mysql = require('mysql');
const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    database: "db1"
});

const getData = async ()=> {
    var array = new Array();
    const sql1 = "SELECT * FROM table1 WHERE active=1";
    con.query(sql1, function (err, result, fields) {
        if (err) throw err;
        array = [];
        for(var i=0; i<result.length; i++) {
            array.push(result[i].active);                        
        }
        console.log(array) //here it prints out the array with its values
        return array;
    });
}
module.exports.getData = getData;

Edit: Maybe this will be helpful in order to figure out what's happening. I have just tried this and it prints out an empty array []:

const mysql = require('mysql');
var array = new Array();
const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    database: "db1"
});

const getData = async ()=> {
    const sql1 = "SELECT * FROM table1 WHERE active=1";
    con.query(sql1, function (err, result, fields) {
        if (err) throw err;
        //array = [];
        for(var i=0; i<result.length; i++) {
            array.push(result[i].active);

        }
        console.log(array) //here it prints out its data
        //return array;
    });
    console.log(array); //here it prints out []
}

module.exports.getData = getData;

When I print the array out in the dbConnection.js file:

When I print it out in the server.js file:

Why is this happening and how to fix it?

Thanks in advance.

Solution

  • Use Async/Await with promises. You cannot use the syntax with callback. You have to change your dbConnection.js as below. You have to promisify your callback.

    function myQuery(){ 
    return new Promise(function(resolve, reject){
        var array = new Array();
        const sql1 = "SELECT * FROM table1 WHERE active=1";
        con.query(sql1, function (err, result, fields) {
            if (err) throw err;
            array = [];
            for(var i=0; i<result.length; i++) {
                array.push(result[i].active);                        
            }
            console.log(array) //here it prints out the array with its values
            resolve(array);
        });
    })
}

const getData = async ()=> {
    var array= await myQuery();
    return array;       
}
module.exports.getData = getData;