why does my async function returns undefine while working with mysql2?

Question

I've been trying non-stop to work on this query for my datatables front end.

this is my config.js

var config = {

host : 'localhost',
user : 'root',
password : '',
database : 'ef45db'
 }

module.exports = config;

this is the function I want to work with async (wait for the query to return the table's columns name)

async function getColumnNames()
            {

        try{

             aColumns = [];
            await connection.query('SHOW COLUMNS FROM '+sTable,
                 function selectCb(err, results, fields){

                    console.log("entro a getColumnNames");

                  if(err){
                    console.log(err);
                  }
                  for(var i in results)
                  {
                    aColumns.push(results[i]['Field']);
                  }

                  connection.end();
                });


                }catch (e){
                    console.log(e);
                }
            }

and this is the controller code to execute that function:

var mysql = require('mysql2');

var config = require('.././database/config');
var connection = mysql.createConnection(config);
var sIndexColumn = '*';
var sTable = 'users';
var aColumns = [];

 module.exports = {

    getInfo : async function(req,res,next)
            {
                var request = req.query;

                (async () => await  getColumnNames());

                console.log(aColumns);

            }

I'm trying to get the column's name so I can work with datatable's filtering for backend, since node is async this query was getting executed, but the value was undefined (and still is), I've read hundreds of post regarding promises, bluebird and async methods and trying to make this work, the last I've read a lot thats the best and I choosed it because the code seems cleaner. Any ideas whats happening?

Answer 1

For getColumnNames(), you shouldn't use await because connection.query doesn't return promise. It is a callback function.

However, we can make getColumnNames to return promise.

function getColumnNames() {
  const aColumns = [];

  return new Promise((resolve, reject) => {
    connection.query('SHOW COLUMNS FROM ' + sTable,
      function selectCb(err, results, fields) {

        console.log("entro a getColumnNames");

        if (err) {
          console.log(err);
          reject(err); // if error happens, reject
        }

        for (var i in results) {
          aColumns.push(results[i]['Field']);
        }

        connection.end();

        resolve(aColumns); // resolve with our database columns
      });
  });
}

and for your controller we can use async await since getColumnNames returns promise as in

module.exports = {
  getInfo: async function (req, res, next) {
    var request = req.query;

    const aColumns = await getColumnNames();

    console.log(aColumns);

  }
}

Let me know if it works for you.