Waiting for mysql database query complete before proceeding

Question

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.

Go take a read on Promises. It can allow you to avoid the callback pyramid hell while solving your problem. You can try wrapping the query function into Promises. — ionizer
– ionizer, Commented Mar 11, 2018 at 12:41
@ionizer Could you please show me an example on how would you wrap the query functions? Just on one query function and I'll be very grateful. — TheE
– TheE, Commented Mar 11, 2018 at 13:08
You can try this kind of code: function q(str, params) { return new Promise((resolve, reject) => {dbconn.query(str, params, (err, result, fields) => {if (err) reject(err); resolve(result)})})}. I have came up with an answer using this if you don't mind to wait a little more. — ionizer
– ionizer, Commented Mar 11, 2018 at 13:14
@ionizer Yeah sure if you want I can wait for the answer, but I think I know what you mean and will be able to get it to work, but still I'll wait for a neat version. :) — TheE
– TheE, Commented Mar 11, 2018 at 13:19
I've posted an answer, you can pick either approach. What version of Node are you using btw? — ionizer
– ionizer, Commented Mar 11, 2018 at 14:14

ionizer · Accepted Answer · 2018-03-11 14:22:12Z

3

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!

edited Mar 11, 2018 at 14:22

answered Mar 11, 2018 at 13:42

ionizer

1,73112 silver badges23 bronze badges

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

3 Comments

TheE Over a year ago

One question, if I put let's say "myfunc()" as async then how do I wait for it's return call, because I'm doing like so: var squareweb = myfunc(); And later using the squareweb variable so myfunc() can't really be async. Will I have to be creating another promise for myfunc() or ?

ionizer Over a year ago

async functions returns a Promise, and whatever you return from inside of them are the same with resolve()-ing a promise with data. So you can use squareweb.then() or even use await again. What is the purpose of var squareweb and how will it be used?

TheE Over a year ago

somefunc(req).then(function(gweb) { squareweb = gweb; /*Some other code*/ } Done the job. Big thanks :)

Collectives™ on Stack Overflow

Waiting for mysql database query complete before proceeding

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related