Function won't return an array of items created from MySQL Query (async/Await)

Question

I'm trying to create an array of items from a MySQL query to return from an async function but the returns are empty.

Things I've tried:

Read up to the latest info about async/await
Stack overflow
Numerous test around changing code, replacing return calls, rewriting functions.

This is for a new webservice (nodejs) that needs to initialize values from a MySQL database and after that fast access to the values to compare them against values pulled from the internet. To limit the amount of database calls I'm planning to have the values in an array (in-memory) and whenever they change enough (based on calculations) write them to the DB.

All is Linux based using Node 11

require('dotenv').config()

var mysql = require('mysql')
var dbconnection = mysql.createConnection({
  host: 'localhost',
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PW,
  database: process.env.MYSQL_DB
})
dbconnection.connect(function (err) {
  if (err) {
    console.error('error connecting: ' + err.stack)
  }
})
async function ReadDB () {
  var ArrBuyPrice = []
  var query = 'SELECT * FROM pricing'
  var items = await dbconnection.query(query, function (err, rows, result) {
    if (err) throw err
    for (var i in rows) {
      ArrBuyPrice.push(rows[i].price_buy.toFixed(8))
    }
    return ArrBuyPrice
  })
  return items
}

async function InitialProcess () {
  var DbResult = await ReadDB()
  console.log(DbResult)
}

InitialProcess()

I would expect the console.log output to be [ '0.00000925', '0.00000012' ]

Wang Liang · Accepted Answer · 2019-05-04T09:38:05.333

// https://stackoverflow.com/questions/44004418

var mysql = require('mysql')
var dbconnection = mysql.createConnection({
    host: 'localhost',
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PW,
    database: process.env.MYSQL_DB
})

const util = require('util');

async function ReadDB() {
    var ArrBuyPrice = []

    const query = util.promisify(dbconnection.query).bind(dbconnection);

    try {
        const rows = await query('select * from pricing');
        for (var i in rows) {
            ArrBuyPrice.push(rows[i].price_buy.toFixed(8))
        }
        return ArrBuyPrice;
    } catch (error) {
        console.log(error)
        return [];
    } finally {
        dbconnection.end();
    }
}

async function InitialProcess() {
    var DbResult = await ReadDB()
    console.log(DbResult)
}

InitialProcess();

What is Problem:

async function ReadDB() {
  var ArrBuyPrice = []
  var query = 'SELECT * FROM pricing';

  var items = await dbconnection.query(query, function (err, rows, result) {
    if (err) throw err
    for (var i in rows) {
      ArrBuyPrice.push(rows[i].price_buy.toFixed(8))
    }
    return ArrBuyPrice
  });
  return items
}

At await dbconnection.query, await is not working, and it is unnecessary, because dbconnection.query() function is not Promise function.

So, the rows which query result will arrive after return items; And return ArrBuyPrice function is not running.

Thank you for this answer, worked like a charm! Would you be so kind to give me some insights about where I went wrong in my thinking process? — MaPo, May 04 '19 at 08:24
Thank, I voted this for the correct answer but it doesn't show. — MaPo, May 05 '19 at 18:17
I tested https://stackoverflow.com/questions/44004418, But that works well on my side — Wang Liang, May 05 '19 at 18:25

Function won't return an array of items created from MySQL Query (async/Await)

1 Answers1