Node js promises async/await

Question

I have defined a route in express that should contain multiple sql queries and i don't know how to structure it using promises and/or async/await.

Sql query A returns an id that is required in sql query B. Sql query B in turn returns three strings as an array that is required in sql query C. Sql query C consists of a loop that uses each of those strings of the array once. In this loop there are also if/else conditions to assign the return values to a specific variable.

These are my isolated queries:

A

connection.query('SELECT ID FROM Config WHERE ConfigID = ?', configid, function (error, result, fields) {
  if (error) throw error;
  else{
      id = result[0].ID;
  } 
});

B

connection.query('SELECT Var1, Var2, Var3 FROM Config2 WHERE ID = ?', id, function (error, result, fields){
   if (error) throw error;
   else {
        var varArray = [];
        varArray.push(result[0].Var1, result[0].Var2, result[0].Var3);
   }        
});

C

for (i = 0; i < varArray.length; i++){
     var selectedVar = varArray[i]; 
     connection.query('SELECT Value FROM Config3 WHERE ConfigID = ? AND VarName = ? ORDER BY Timestmp DESC LIMIT 1', [configid, selectedVar], function (error, result, fields) {
            if (error) throw error;
            else if (i == 0){
                aaa = parseInt(result[0].Value);
            } else if (i == 1){
                bbb = parseInt(result[0].Value);
            } else if (i == 2){
                ccc = parseInt(result[0].Value);
            }
     }); 
}

configid is predefined, so do not wonder where it comes from. A, B and C have to be connected in a way that they are executed sequentially.

Then there is another query D, that doe not need any of the previous values (except of the predefined configid), so it can be executed before the others have finished.

D

connection.query('SELECT Number FROM Config WHERE ConfigID = ?', configid, function (error, result, fields) {
        if (error) throw error;
        else{
            ddd = parseInt(result[0].Number);
        } 
});

When all queries are done and the respective values have been assigned, i want to write them into a new array where all data is stored:

data = [];
data[0]= aaa;
data[1]= bbb;
data[2]= ccc;
data[3]= ddd;

How do i connect all these queries in a way i described it? I have already read several atricles about promises and async/await but i am struggling to apply it to my specific problem.

By the way, it think sql query C does not work correctly but i would like to achieve that i have three different queries (the placeholder `selectedVar` changes each time) and assign each value from the database to a different variable. — , Dec 11 '19 at 07:58

score 0 · Accepted Answer · answered Dec 11 '19 at 08:04

0

If I were you I would promisify the connection.query function like this.

function runQuery(sql, configid) {
  return new Promise((resolve, reject) => {
    connection.query(sql, configid, function (error, result, fields) {
      if (error) reject(error);
      else {
        resolve(result);
      } 
    });
  });
}

Then I would use the new runQuery function to exectue SQL query. For example:

app.get('/', async (req, res, next) => {
  try {
    const result1 = await runQuery('SELECT ID FROM Config WHERE ConfigID = ?', configid);
    const id = result1[0].ID;

    const resutl2 = await runQuery('SELECT Var1, Var2, Var3 FROM Config2 WHERE ID = ?', id);

    // ... and so on
    res.send('Done with it');

  } catch (error) {
    next(error); // Pass the error the express error handler
  }
});

Hope it helps.

answered Dec 11 '19 at 08:04

Shihab

2,641
3
21
29

Thank you very much! How do i add query D, because it does not depend on anything of the other queries. – Dec 11 '19 at 08:57
You can call query D as you are already doing using your normal `connection.query` function. – Shihab Dec 11 '19 at 09:11
But where do i put it? I want all queries to be finished and then write the data into the array. So when i put query D right below the try and catch block, null is stored into my array. – Dec 11 '19 at 10:00
If you want to run query at the end then call it at the end of try block! – Shihab Dec 11 '19 at 10:02
Can you help me with this question also? https://stackoverflow.com/questions/59421010/nested-asyny-await-combined-with-loops – Dec 20 '19 at 07:33

Node js promises async/await

1 Answers1