Database query in node.js asynchronously

Question

I am trying to figure out the correct way to call a function asynchronously if it also makes asynchronous calls to the database.

In my code, I call a function called "check_foods ()" that will query the database up to two times if it has not already been called. Two variables will fill in the database queries: vegetables and fruits (provided that they have not been filled yet). As soon as the function returns, I want to use these variables.

However, I assume that now my code is now trying to start processing two console lines, even if it has not yet pulled data from the database. Am I correct in this assumption? If so, what should I do to implement this correctly?

A simplified version of my code is as follows:

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

Answer 1

This is really useful when such projects break everything down into small units. Thus, asynchronous parts may be contained or offered in ways that facilitate reasoning. It sounds like you want to use async/await , which is suitable for this, but that means you need to generate promises. The last time I checked the standard MySQL library, this is not done, but it's easy enough to wrap them.

So, I would break it 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 your asynchronous things are simple. In an async function, you can call something like:

 let items = await check_growth_items(socket.room) 

or get food:

 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 the final code should do, but your main function should be able to look something like this:

 async function starter (){ // it 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 the solution for cutting and pasting, but hopefully gives you some ideas on more convenient ways to organize fragments.

Answer 2

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

sample code should look 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; } }; }