CODEWITHSUNDEEP
javascriptmysqlnode.js
TheE
TheE

Reputation: 378

Waiting for mysql database query complete before proceeding

I'm having an issue where I want to wait for a async database query and it's operations before I continue.

I could of course put operations inside a pyramid, but it won't be cool on the long run, because I will have to duplicate code some of the times.

Here is my example (that will of course not work currently, because of async on database queries):

var level = 5;
var difficulty = 30; //example
let randomDecider = Math.floor(Math.random()*(50+level)+1);
if (difficulty < randomDecider) {
  var type;
  var name;
  var heroid = "";
  dbconn.query("SELECT * FROM owned_heroes WHERE user = ? AND equipped = 1", [req.session.login], function(err, result, fields) {
    heroid = result[0].heroid;
  });
  if (randomDecider > 50) {
    type = "skill";
    var ownedSkills = [];
    var skillsKeys = Object.keys(Skill);
    dbconn.query("SELECT * FROM owned_skills WHERE user = ? AND heroid = ? AND equipped = 1", [req.session.login, heroid], function(err, result, fields) {
      for (var i = 0; i < result.length; i++) {
        ownedSkills.push(result[i].name);
      }
    });
    //Here later I also want to remove ownedSkills from skillsKeys.
    name = skillsKeys[Math.floor(Math.random()*skillsKeys.length)];
  }
  else {
    type = "item";
    var itemsKeys = Object.keys(Item);
    var ownedItems = [];
    dbconn.query("SELECT * FROM owned_items WHERE user = ? AND heroid = ? AND equipped = 1", [req.session.login, heroid], function(err, result, fields) {
      for (var i = 0; i < result.length; i++) {
        ownedItems.push(result[i].name);
      }
    });
    //Here later I also want to remove ownedItems from itemsKeys.
    name = itemsKeys[Math.floor(Math.random()*itemsKeys.length)];
  }
  //Some other code using the type and name variable.

So could someone based on this explain me how would I convert this into something working, like waiting for the actions after queries to complete before going forward. I tried async/await, but I couldn't figure it out currently.

Also any other suggestions to the code is highly welcome.
Thanks.

Upvotes: 0

Views: 3955

Answers (1)

ionizer
ionizer

Reputation: 1721

I have explained in the comments about using Promises. And I assume you're using the mysql library for your NodeJS program.

So first, to avoid the pyramid (or callback hell as we usually call it), we can try wrapping the async function into a Promise like so:

function queryPromise(str, params) { 
  return new Promise((resolve, reject) => {
    dbconn.query(str, params, (err, result, fields) => {
      if (err) reject(err); 
      resolve(result);
    })
  })
}

Then, I only promisified the important part:

var level = 5;
var difficulty = 30; //example
let randomDecider = Math.floor(Math.random() * (50 + level) + 1);
if (difficulty < randomDecider) {
  var type;
  var name;
  var heroid = "";

  queryPromise("SELECT * FROM owned_heroes WHERE user = ? AND equipped = 1", [req.session.login]).then(result => {
    heroid = result[0].heroid;
    if (randomDecider > 50) {
      ........

You can use this function for your other queries as well. But wait! Is this not the same as putting it into the pyramid? Well, it is more or less the same and is usually the way to do it so your code executes the way we want it to. But now, we can chain the Promise's .then()s from the other queries inside and avoid indenting your code even more which result in creating the callback hell. Though, this is not what I like to do as Promise chaining also brings about hell when we're not careful with it.

If you're using Node 8 or above, using async await is the best solution. Considering async await uses Promises as well, we can do it like this:

var level = 5;
var difficulty = 30; //example
let randomDecider = Math.floor(Math.random() * (50 + level) + 1);
if (difficulty < randomDecider) {
  var type;
  var name;
  var heroid = "";

  let heroidQuery = await queryPromise("SELECT * FROM owned_heroes WHERE user = ? AND equipped = 1", [req.session.login]);
  heroid = heroidQuery[0].heroid;

  if (randomDecider > 50) {
      ........

Looks much better now, isn't it? As the keyword suggests, await makes your code wait for the result to be retrieved from your query. Just don't forget to wrap that whole code into an async function() block as await is not allowed outside async functions.

async function myfunc() {
  var level 5;
  ....

I suggest looking into libraries such as mysql2/promise or even sequelize which uses Promises when you're comfortable with Promises (or even better, together with async-await)

I hope this helps. Good luck!

Upvotes: 3

Related Questions