How to get return values from Async/await function when fetching the data from mySQL in Nodejs

Question

I am fetching some exchange data from DB, then extracting the name of distinct exchanges and passing again into MYSQL query to fetch data from a different table.

The problem I am facing is that async await does not return the value rather just return Promise { }.

Below is the code that I am trying, wondering where I am going wrong.

//Function that fetches the exchanges from DB

const getExchange = () => {
        return new Promise((resolve, reject) => {
            db.connection.query(`
            SELECT  *
            FROM,
            (
            SELECT
                exchange,
                COUNT(pair) as noOfMarkets
                FROM ticker_data

        ) as t
            `, (err, resp) => {
                if (!err) {
                    resolve(resp)
                } else {
                    reject(err)
                }
            })
        })
    }


// push unique exchanges to an array.
const getExchangesData = async () => {
    const allExchanges = await getExchanges();

    let exchanges = []
    allExchanges.forEach(item => {
        let exchange = {
            exchange: item.exchange
        }
        exchanges.push(exchange)
    })
    return await exchanges
}


// mapping through an array of exchanges and passing to DB query to get data from the DB.

const getSingleExchange = async () => {
    const exchanges = await getExchangesData()
    await Promise.all(exchanges.map(async (item) => {
        db.connection.query(`
        SELECT
       exchange_rank,
       name
       volume24hUSD
       (
            SELECT
            volume24hUSD as tradingVolYesterday
            FROM exchanges
            WHERE name = '${item.exchange}'
            AND createdAt >= now() -interval 1 day
            AND createdAt <  now() -interval 1 day + interval 120 second
            LIMIT 1
       ) volumeDay1
FROM exchanges
WHERE name = '${item.exchange}'
        `, (err, resp) => {
            if (!err) {
                console.log(resp) // getting all the values

                let volData = {
                     name: resp[0].name,
                     exchange_rank: resp[0].exchange_rank,
                     icon: resp[0].icon
                 }
                 return volData 
            }
        })
    }))
}


const data = getSingleExchange()
console.log(data) // returning Promise { <pending> } 

Edit

After making changes suggested in an answer, I still have an issue:

//Function that fetches the exchanges from DB

const getExchange = () => {
        return new Promise((resolve, reject) => {
            db.connection.query(`
            SELECT  *
            FROM,
            (
            SELECT
                exchange,
                COUNT(pair) as noOfMarkets
                FROM ticker_data

        ) as t
            `, (err, resp) => {
                if (!err) {
                    resolve(resp)
                } else {
                    reject(err)
                }
            })
        })
    }


// push unique exchanges to an array.
const getExchangesData = async () => {
    const allExchanges = await getExchanges();

    let exchanges = []
    allExchanges.forEach(item => {
        let exchange = {
            exchange: item.exchange
        }
        exchanges.push(exchange)
    })
    return await exchanges
}


    // mapping through an array of exchanges and passing to DB query to get data from the DB.

const getSingleExchange = async () => {
    const exchanges = await getExchangesData()
    await Promise.all(exchanges.map((item) => {
        return new Promise((resolve, reject) => {
            db.connection.query(`...`, (err, resp) => {
                if (!err) {
                    resolve(resp)
                } else {
                    reject(err)
                }
            }).then(resp => {
                console.log(resp)
                let volData = {
                    name: resp[0].name,
                    exchange_rank: resp[0].exchange_rank,
                    icon: resp[0].icon
                }
                return volData
            })
        })
    }))

}

getSingleExchange().then(data => {
    console.log(data)
});

I now get this error:

(node:30583) UnhandledPromiseRejectionWarning: TypeError: db.connection.query(...).then is not a function
    at Promise (/getExchanges.js:217:16)
    at new Promise ()
    at Promise.all.exchanges.map (/getExchanges.js:145:16)
    at Array.map ()
    at getSingleExchange (/getExchanges.js:144:33)

Answer 1

The main issue is in this part:

await Promise.all(exchanges.map(async (item) => {

That map callback is not returning anything, and it has no await, so using async makes no sense.

Instead remove async:

await Promise.all(exchanges.map((item) => {

... and return a promise in the callback function, much like you had done in the first function:

    return new Promise((resolve, reject) => {
        db.connection.query(`...`), (err, resp) => {
            if (!err) {
                resolve(resp)
            } else {
                reject(err)
            }
        })
    }).then(resp => {
        console.log(resp)
        let volData = {
             name: resp[0].name,
             exchange_rank: resp[0].exchange_rank,
             icon: resp[0].icon
         }
         return volData 
    });

You would benefit from writing one generic function that promisifies query, so that you don't have to do that new Promise-thing for every single query you need.

Finally, you cannot expect to get an asynchronous result synchronously: async functions do not return the asynchronous result synchronously, but return a promise for it. So your last lines (main code) should still await. So either do this:

(async () => {
    const data = await getSingleExchange()
    console.log(data)
})(); // immediately executing (async) function expression

Or:

getSingleExchange().then(data => {
    console.log(data)
});

NB: doing return await exchanges in the second function makes no sense (exchanges is not a promise), so you can just do return exchanges.