Mysql Question posted in
The official MySQL documentation can be found here.

javascript MySQL – using Promise-await-async constructs, can't get code to wait for query completion

I’ve tried multiple versions of example code but can never seem to get something that guarantees completion of the query set before other code commences. I was under the impression that ‘await’ makes the query complete before continuing.

I’m running my javascript with node.js in Webstorm. The code below inserts three rows into the table ‘Verb’ in the DB ‘ItalianVerbs’. I’ve tried it with a couple different conditions. The first condition is where I have the last lines of code ‘g_con.end();’ and ‘process.exit(0);’ commented out. If I start it and just let it run this way, it finishes the calls quickly, but the program does not exit, and the table lines are successfully entered into the database – but, the order of the log messages indicate things are running asynchronously.

The second condition is where I un-comment the ‘process.exit(0)’ call at the end – in which case I see the log messages from the ‘USE’ queries, but none from the INSERT queries, which indicates that nothing is being waited for and the program exits before those threads complete.

This is making me a bit nuts – ultimately I want a block of code that I can encapsulate in a function, and have all the queries started in that function complete before the function returns.

My code below, cobbled up from multiple other examples.

const mysql = require('mysql');

const selectedDB = 'ItalianVerbs';
const str_VerbCols = " (infinitive, english, auxiliary, gerund, participle)";

const g_con = mysql.createConnection(
   {
      host: 'localhost',
      user: 'root',
      password: 'Kl1n&on0per@',
      database: selectedDB
   });

//============================================================================
// SECOND STEP USING MYSQL - select a specific database to use.
//============================================================================

function f_use_db(con, db_name) {
   let qry = 'USE ' + db_name + ';';            // qry = 'USE ItalianVerbs'
   con.query(qry, (err, pkt) => {
      if (err) { throw err;}
   });
   return qry;
}

//============================================================================
// Build query string for INSERT ROW into Verb table.
//============================================================================

function f_query_for_verb_tbl(val_list) {
   if (val_list.length < 5) {
      console.log("[f_insert_info_Verb_row] - ERROR: not enough values for inserted rown")
      return -1;
   }

   let qry = 'INSERT INTO Verb' + str_VerbCols + ' VALUES (';
   val_list.forEach(function (v, idx) {
      qry = qry + '"' + v + '"';
      if (idx < 4)
         qry = qry + ', ';
   })

   qry = qry + ');';
   return qry;
}

//============================================================================

qPromise1 = async () => {
   const query =  new Promise((resolve, reject) => {
      let qry = f_use_db(g_con, selectedDB, false);
      console.log('*** RUNNING QUERY: ' + qry);
      g_con.query(qry, (err, res) => {                  // q = 'USE ItalianVerbs'
         if (err) { return reject(err); }
         return resolve(res);
      });
   });

   await query;
}

//============================================================================

qPromise2 = async (v_q) => {                          // v_q is INSERT query string.
   console.log('*** RUNNING QUERY: ' + v_q);
   const query = new Promise((resolve, reject) => {
      g_con.query(v_q, (err, res) => {
         if (err) { return reject(err); }
         return resolve(res);
      });
   });

   await query;
}

//============================================================================

async function sequentialQueries (qry) {
   try {
      const r1 = await qPromise1();
      const r2 = await qPromise2(qry);
   }
   catch(err) {
      console.log(err);
      return(err);
   }

   return 0;
}

//============================================================================
//============================================================================

let nvd1 = ["adorare", "adore", "avere", "adorando", "adorato"];
let nvd2 = ["abitare", "dwell", "avere", "abitando", "abitato"];
let nvd3 = ["abbassare", "let-down", "avere", "abbandonando", "abbandonato"]

let lvd_list = [nvd1, nvd2, nvd3];

g_con.connect(function(err) {
   if (err) { console.log('error on connect to MySql - ' + err); }
});

lvd_list.forEach(function (v) {
   let v_qry = f_query_for_verb_tbl(v);
   let rval = sequentialQueries(v_qry);
})

// g_con.end();
console.log('exiting');
// process.exit(0);
Tags:

3

Answers


  1. 0

    Please try below approach:

    for (let v of lvd_list) {
   let v_qry = f_query_for_verb_tbl(v);
   let rval = await sequentialQueries(v_qry);
}
    Login or Signup to reply.
  2. 0

    I think u should use mysql2 package, it allows you to execute queries asynchronously

    Login or Signup to reply.
  3. 0

    I suggest you to use Sequelize. It is a Node.js ORM tool for Postgres, MySQL, etc. You can create your instance like that: const sequelize = new Sequelize('postgres://user:[email protected]:5432/dbname') // Example for postgres. But answering your question could be due to your last foreach.

    In JavaScript, using async/await within a forEach loop may not work as expected due to how asynchronous operations are handled in JavaScript. async/await works well with for or while loops, but forEach doesn’t wait for asynchronous functions to complete before moving on to the next iteration. You can use a for...of loop instead to properly handle asynchronous operations within the loop. Do you need an example of how to do it with for...of?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search