ExpressJS Await not working - SQL SELECT too fast (before INSERT)

Question

I want to make a SQL Insert and after that a Select with the complete List to show in Vue inclusive the new entry. But the Select is faster than the Insert and does only return the old values which are already in the Table. I tried with setTimeout and Async-Await. (SQL Statements are working)

Can someone help me i am new into JS.

async function select_time(datum, mitarbeiter_id){
    let returnQuery = 'SELECT ...'
    let retquery = mysql.format(returnQuery,[datum, mitarbeiter_id]);
    pool.query(retquery,(err, data) => {
        if(err) {
            console.error(err);
            return err
        }
 
        return data
    });
}

async function insert_time(datum, mitarbeiter_id, projekt_id, zeit_von, zeit_bis, stunden, taetigkeit){

    let selectQuery = 'INSERT INTO ...';    
    let query = mysql.format(selectQuery,[taetigkeit, projekt_id, mitarbeiter_id, zeit_von, zeit_bis, stunden, datum]);
    pool.query(query,(err) => {
        if(err) {
            console.error(err);
        }
    });
    if(taetigkeit == 15){
        let updateQuery = "UPDATE ..."
        let query2 = mysql.format(updateQuery, [mitarbeiter_id])
        pool.query(query2,(err, data) => {
        if(err) {
            console.error(err);
        }
        });        
    }
}

async function run_insert_time(datum, mitarbeiter_id, projekt_id, zeit_von, zeit_bis, stunden, taetigkeit){
    await insert_time(datum, mitarbeiter_id, projekt_id, zeit_von, zeit_bis, stunden, taetigkeit)
    const jsret = await select_time(datum, mitarbeiter_id)
    return jsret
}

app.get('/insertTime', (req, res) => {
    const datum = req.query.datum;
    const mitarbeiter_id = req.query.mitarbeiter_id;
    let projekt_id = req.query.projekt_id;
    let zeit_von = req.query.zeit_von;
    let zeit_bis = req.query.zeit_bis;
    const stunden = req.query.stunden;
    const taetigkeit = req.query.taetigkeit;

    const js = run_insert_time(datum, mitarbeiter_id, projekt_id, zeit_von, zeit_bis, stunden, taetigkeit)

    res.json(js)
 })

score 1 · Answer 1 · answered Feb 13 '22 at 16:19

If pool.query does not have a similar function with Promise support then you need to wrap all such calls into promises to be able to wait for results and return them outside if you want.

async function select_time(datum, mitarbeiter_id){
    let returnQuery = 'SELECT ...'
    let retquery = mysql.format(returnQuery,[datum, mitarbeiter_id]);
    return new Promise((resolve, reject) => {
      pool.query(retquery,(err, data) => {
          if(err) {
              console.error(err);
              reject(err);
          }
 
          resolve(data);
      });
    });
}

Look at this answer to find out more about how to convert functions with callbacks into promise-like functions.

score 0 · Accepted Answer · answered Feb 13 '22 at 16:29

To expand on @anatoly's very good answer:

I find the inline return new Promise... to be visually disruptive of the program flow. By creating a separate "promisified" version, your async function becomes much cleaner. It's just rearranging the code, with the same ultimate outcome.

const pQuery = qryText => new Promise((resolve, reject) => {
  pool.query(qryText, (err, data) => {
    if (err) reject(err);
    else resolve(data);
  });
});

Then your async functions become quite clean. For example:

async function select_time(datum, mitarbeiter_id){
  let returnQuery = 'SELECT ...'
  let retquery = mysql.format(returnQuery,[datum, mitarbeiter_id]);
  const data = await pQuery(retquery);
  return data;
}

Aside: node.js has util.promisify, which makes this even easier to implement.

const { promisify } = require('util');
const pQuery = promisify(pool.query.bind(pool));

(Pretty sure you need the bind, but you could try without and see if it works)

ExpressJS Await not working - SQL SELECT too fast (before INSERT)

2 Answers2