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;
            });
        };

}

async/await works with promises. you need to promisify the query function. then you can do await db_connection.query(...), and the code will be executed "synchronously" — Eric Guan
– Eric Guan, Commented Nov 9, 2017 at 23:18
@EricGuan - await does not make things execute synchronously and telling someone that is just misleading. It allows you to write code in a more synchronous looking way, but the code is all still executing asynchronously, event loop not blocked, other events being served, etc... await is pure syntactical sugar. One has to first understand promises and asynchronous code design and then one could use await to save a few lines of code. — jfriend00
– jfriend00, Commented Nov 9, 2017 at 23:43
If I use await as it is currently, the function will still return right away as the query functions are being called asynchronously. — kojow7
– kojow7, Commented Nov 10, 2017 at 4:19
What db library are you using? Many now return promises if you don't pass a callback. This would allow you to use async/await. Without promises you can't really use it. — Mark
– Mark, Commented Nov 10, 2017 at 7:36

Mark · Accepted Answer · 2017-11-12 05:09:44Z

10

+50

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.

answered Nov 12, 2017 at 5:09

Mark

92.7k8 gold badges116 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kojow7 Over a year ago

Is there a more popular sql library that does handle promises?

Mark Over a year ago

@kojow7 I think promise-mysql is a pretty popular choice for this: npmjs.com/package/promise-mysql

Sam H. Over a year ago

I use and like knex. We're on postgres but I think you can use MySQL

Mark Over a year ago

@kojow7 - I’ll second @Sam_H’s suggestion of knex. It’s mature and works well. I use it with Postgres.

Alongkorn · Accepted Answer · 2017-11-15 04:08:22Z

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;
      }
  };
}

Collectives™ on Stack Overflow

Querying a database in node.js asynchronously

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related