Querying a database in node.js asynchronously

Question

I am trying to figure out the proper way to call a function asynchronously if it is also making asynchronous calls to the database.

In my code I call a function called 'check_foods()` which will query a database up to two times if it has not been called already. The database queries will populate the two variables vegetables and fruit (assuming they haven't been populated already). Once the function returns I want to make use of those variables.

However, I assume that the way my code is now that it will try to begin processing the two console lines even if it hasn't pulled the data from the database yet. Am I correct in this assumption? If so, what should I do to implement this correctly?

A simplified version of my code looks like this:

var vegetables = {};
var fruit = {};


async function starter (){
    sql = "select growth_items from garden where room_id = ?";
    db_connection.query(sql, [socket.room], function (err, result){
        if (err){
            console.log("Garden Error: " + err);
            return;
        }

        console.log(result);

        check_foods();

        //Should not get to this point until variables have been populated
        //However, due to the async nature of the code this does not seem to be implemented correctly. 
        //process vegetables[socket.room] and fruit[socket.room]
        console.log(vegetables[socket.room]);
        console.log(fruit[socket.room]);
    });    
}

async function check_foods(){
       //Check to see if vegetables[socket.room] already exists, otherwise load it from the database
       if (typeof vegetables[socket.room] !== "undefined"){
            sql = "select name, qty from vegetables where room_id = ?";
            db_connection.query(sql, [socket.room], function (err, result){
                if (err){
                    console.log("An vegetable error has occurred: " + err);
                    return;
                }

                vegetables[socket.room] = result;
            });
        };

        //Check to see if fruit already exists before looking up again

        if (typeof fruit[socket.room] !== "undefined"){
            sql = "select name, qty from fruit where room_id = ?";
            db_connection.query(sql, [socket.room], function (err, result){
                if (err){
                    console.log("An fruit error has occurred: " + err);
                    return;
                }

                fruit[socket.room] = result;
            });
        };

}

user_3874623 · Answer

It's really helpful with projects like this to break everything into small units. That way the asynchronous parts can be contained or promisized in ways that make it easier to reason about. It looks like you want to use async/await which is nice for this, but it mean you need to generate promises. Last time I checked the standard MySQL library doesn't do that, but it's easy enough to wrap them.

So I would break this down to like this:

function check_growth_items(room){
   //simple function to get growth items
    return new Promise((resolve, reject) => {
        sql = "select growth_items from garden where room_id = ?";
        db_connection.query(sql, [room], function (err, result){
            if (err){
                console.log("Garden Error: " + err);
                reject(err);
            }
            resolve(result)
        })
    })
}

function get_food(type, room_id){
    // expects a type [vegetable | fruit] and room
    return new Promise((resolve, reject) => {
        sql = "select name, qty from ? where room_id = ?";
        db_connection.query(sql, [type, room_id], function (err, result){
            if (err){
                console.log("An vegetable error has occurred: " + err);
                reject(err);
            }
            resolve(result);
        });
    })
}

Now all of your async stuff is simple. In an async function you can call something like:

let items = await check_growth_items(socket.room)

or get the food items:

fruit[socket.room] = await get_food('fruit',socket.room )

or

get_food('fruit',socket.room )
.then(result => fruit[socket.room] = result)
.catch(err => console.log(err))

I'm admittedly not 100% sure what you're final code should do, but your main function should be able to look something like:

async function starter (){
    // it's not clear in your code how you're using this
    // of it the other async calls depend on the return value
    var growth_items = await check_growth_items(socket.room)
    if (!vegetables[socket.room]) {
        vegetables[socket.room] = await get_food('vegetables',socket.room )
    }
    if (!fruit[socket.room]) {
        fruit[socket.room] = await get_food('fruit',socket.room )
    }

    console.log(vegetables[socket.room]);
    console.log(fruit[socket.room]);
}

This probably won't be a cut & paste solution, but hopefully will give you some ideas about nicer ways to organize the pieces.

user_6315366 · Answer

You can use promisify to convert callback to promise and then use await

sample code should be like this

const vegetables = {};
const fruit = {};
const Promise = require('bluebird'); // or you can use node utility module as well
const db = Promise.promisifyAll(require('YOUR DB LIB'));
const db_connection = 'YOUR DB CONNECTION'

async function starter (){
  const SQL = "select growth_items from garden where room_id = ?";
  try {
    const result = await db_connection.query(SQL);
    console.log(result);
    check_foods();
  } catch(err) {
    console.log("Garden Error: " + err);
  }
}

async function check_foods(){
  //Check to see if vegetables[socket.room] already exists, otherwise load it from the database
  if (typeof vegetables[socket.room] !== "undefined"){
    const SQL = "select name, qty from vegetables where room_id = ?";
    try {
      const result = await db_connection.query(SQL);
      vegetables[socket.room] = result;
    } catch(err) {
      console.log("An vegetable error has occurred: " + err);
      return;
    }
  }

  //Check to see if fruit already exists before looking up again
  if (typeof fruit[socket.room] !== "undefined"){
      const SQL = "select name, qty from fruit where room_id = ?";
      try {
        const result = await db_connection.query(SQL);
        fruit[socket.room] = result;
      } catch(err) {
        console.log("An fruit error has occurred: " + err);
        return;
      }
  };
}

Querying a database in node.js asynchronously

2 Answers