Promises resolving to undefined with node.js mysql queries

Question

I'm writing a route that takes in user-input data from a form and uses that data to execute a few separate queries on my database. That is all well-tested, and I know I can get the user input, query the database, and return the results. The part giving me trouble, however, is that I want to randomly select a few results out of all of the returned data, so I am pushing the results into one big array. But since the queries execute asynchronously, the only way to access the data is in the callback and of course if I tried to push the rows in the callback, I still can't access that array outside of the callback functions because that code will execute before the queries are even run. So I'm attempting to use JavaScript async/await syntax to do the queries, as follows:

module.exports = {
search: (req, res) => {
    function saveResults(resultsObj) {
        Object.keys(resultsObj).forEach(function (key) {
            var row = resultsObj[key];
            var song = row.song_name
            resultsArray.push(song);
            return resultsArray;
        });
    }
    async function queryForSong(){
            var songGenre;
            getSongGenre = "The queries aren't important, I know they work as expected";
            await mysqlConnection.query(getSongGenre, (err, rows) => {
                if (err) throw err;
                songGenre = row[0].song_genre;

                songSearchQuery = "SELECT query";
                mysqlConnection.query(songSearchQuery, (err, rows) => {
                    if (err) throw err;
                    return saveResults(rows);
                });
            });
    }
    async function queryForArtist() {
            var artistID;
            getArtistID = "SELECT query";
            await mysqlConnection.query(getArtistID, (err, rows) => {
                if (err) throw err;
                artistID = rows[0].artist_id;
                console.log(rows, artistID);

                artistSearchQuery = "SELECT query";
                mysqlConnection.query(artistSearchQuery, (err, rows) => {
                    if (err) throw err;
                    return saveResults(rows);
                });
            });
    }
    async function queryForGenre() {
        genreSearchQuery = "SELECT query";
        await mysqlConnection.query(genreSearchQuery, (err, rows) => {
            if (err) throw err;
            return saveResults(rows);
        });
    }
    async function queryForDecade() {
        decadeSearchQuery = "SELECT query";
        await mysqlConnection.query(decadeSearchQuery, (err, rows) => {
            if (err) throw err;
            return saveResults(rows);
        });
    }
    var resultsArray = [];
    var artist = req.body.searchArtist;
    var song = req.body.searchSong;
    var genre = req.body.searchGenre;
    var decade = req.body.searchDecade;

    if (song) {
        queryForSong().then((value) => console.log(value)); //these all log undefined
    }
    if (artist) {
        queryForArtist().then((value) => console.log(value));
    }
    if (genre) {
        queryForGenre().then((value) => console.log(value));
    }
    if (decade) {
        queryForDecade().then((value) => console.log(value));
    }
}
}

Then the idea is to save the resolved rows from the promises into one big array and randomize it from there. My only thought of why this isn't working is that it could be because the return statements are located inside the callback functions? I have also tried it using promises directly as this post explains, by writing all of my async functions like this instead:

function queryForGenre() {
        genreSearchQuery = "SELECT query";
        return new Promise(function (resolve, reject) {
            mysqlConnection.query(genreSearchQuery, (err, rows) => {
                if (err) reject(err);
                resolve(saveResults(rows));
            });
        });
    }

But either way, the console.log statements in the .then still print undefined, meaning my promises aren't resolving to the saveResults(rows) value that I give it. Do I need to make the saveResults function asynchronous as well? From my understanding of promises, this seems like it should work. What am I missing? Or is there a simpler way to do this?

`queryForSong` doesn't have a `return`, so by default it produces `undefined`. Same with the rest. Moreover, it seems like you're `await`ing a method that accepts a callback - in general, those don't produce a promise. You'll have to check the documentation for the library you use to see what the API that produces promises is. Most libraries either take a callback or not, so it's possible that just omitting the callback is what you need. Again, check the documentation. And return the promise from the function. — VLAZ, Dec 08 '20 at 15:56

score 1 · Answer 1 · answered Dec 08 '20 at 16:04

Your functions queryForSong, queryForDecade, etc are using await, but the mysql driver query function accepts a callback and does not return a promise. Also, the return inside your callback is scoped within the callback, not scoped with respect to your outside async function. Instead wrap the function within a promise, resolve and reject where necessary. Also avoid using await unless absolutely necessary because using it blocks the event loop. I have given an example for how to wrap with promise for queryForSong

function queryForSong(){
  return new Promise((resolve, reject) => {
    let songGenre;
    const getSongGenre = "The queries aren't important, I know they work as expected";
    mysqlConnection.query(getSongGenre, (err, rows) => {
        if (err) reject(err);
        else {
          songGenre = row[0].song_genre;
          const songSearchQuery = "SELECT query";
          mysqlConnection.query(songSearchQuery, (err, rows) => {
              if (err) reject(err);
              else
                resolve(saveResults(rows));
          });
        }
    });
  });
}

I actually tried this, as you can see in my second block of code. I did it for all of the functions but didn't list them all for simplicity's sake. But yes, I now understand that it doesn't make any sense to await a function that accepts a callback! — MRB, Dec 08 '20 at 16:18

score 0 · Answer 2 · answered Dec 08 '20 at 16:27

So it turns out it was the saveResults function causing me issues. I don't totally understand why this would fix it, but instead of resolving to the value of the saveResults function, I instead tried just including the saveResults body in each function that returns a promise and that works. For example,

function queryForSong() {
        var songResultsArray = [];
        var songGenre;
        getSongGenre = "SELECT query";
        return new Promise(function (resolve, reject) {
            mysqlConnection.query(getSongGenre, (err, rows) => {
                if (err) reject(err);
                var row = rows[0];
                songGenre = row.song_genre;
                songSearchQuery = "SELECT query #2";
                mysqlConnection.query(songSearchQuery, (err, rows) => {
                    if (err) throw err;
                    //this is the body of the saveResults function
                    Object.keys(rows).forEach(function (key) {
                        var row = rows[key];
                        var song = row.song_name;
                        songResultsArray.push(song);
                    });
                    resolve(songResultsArray);
                });
            });
        });
    }

Promises resolving to undefined with node.js mysql queries

2 Answers2