How to correctly make multiple MySQL calls in a single request with Node.js, Express.js, MySQL2, and Promises

Question

Looking at the correct/best/better way to use AWAIT with MySQL2 in a Node.js/Express.js app when I need to run multiple queries in a single request.

Early on in my app I create a Promise Pool from my Database config

const promisePool = db.promise();

Then, on a POST request I accept 2 values, both of which I need to verify are valid, and then take the returned ID's and INSERT them in to another table.

Below is was my first attempt but I am missing out on the JS's concurrently goodness. (I've overly simplified all the calls/SQL for demonstration purposes),

app.post('/addUserToDepartment', async (req, res) => {
    // Get the POST variables
    let email = 'example@example.com';
    let departmentname = 'sales';
    let insertParams = [];

    // Need to check if Department ID is even valid
    const [departments] = await promisePool.query( "SELECT ? AS deptid", [departmentname] );

    // Need to check if Email address is valid
    const [user] = await promisePool.query( "SELECT ? AS userid", [email] );

    // This would normall be an INSERT or UPDATE statement
    if(departments.length && user.length){
        const [rows] = await promisePool.query( "SELECT ? AS passedDeptId,? AS passedUserid", [departments[0].deptid, user[0].userid] );
    }

    res.send( rows )
}

Here is my second stab at it, now wrapping the promises up.

app.post('/addUserToDepartment', async (req, res) => {
    // Get the POST variables
    let email = 'example@example.com';
    let departmentname = 'sales';
    let insertParams = [];

    // Need to check if Department ID is even valid
    let [[departments],[user]] =
    await Promise.all([
        promisePool.query( "SELECT ? AS deptid", [departmentname] ),
        promisePool.query( "SELECT ? AS userid", [email] )
    ])

    // This would normall be an INSERT or UPDATE statement
    if(departments.length && user.length){
        let [rows] = await promisePool.query( "SELECT ? AS passedDeptId,? AS passedUserid", [departments[0].deptid, user[0].userid] );
    }

    res.send( rows )
}

The IF at the end still doesn't 'feel' right, but I need to know that the first two queries are valid otherwise I'll send the user to an error page.

What would be a better way to achieve the above result without forfeiting readability too much?

Answer 1

Here is what I went with in the end. I added catches, I also did my last query as a part of the Promise.all() chain.

  app.get('/test2', async (req, res) => {
    // Get the POST variables
    let email = 'example@example.com';
    let departmentname = 'sales';
    let insertParams = [];
    let rtn = {
      status : '',
      errors : [],
      values : []
    }
    console.clear();
    // Need to check if Department ID is even valid

    let arrayOfPromises = [
      promisePool.query( "SELECT ? AS did", [departmentname] ),
      promisePool.query( "SELECT ? AS uid", [email] )
    ]
    await Promise.all(arrayOfPromises)
    .then( ([d,u] ) => {
      // Get the  values back from the queries
      let did = d[0][0].did;
      let uid = u[0][0].uid;
      let arrayOfValues = [did,uid];

      // Check the values
      if(did == 'sales'){
        rtn.values.push( did );
      } else{
        rtn.errors.push( `${did} is not a valid department`);
      }
      if(uid == 'example@example.com'){
        rtn.values.push( uid );
      } else{
        rtn.errors.push( `${did} is not a valid department`);
      }

      if( rtn.errors.length === 0){
        return arrayOfValues;
      } else{
        return Promise.reject();
      }
    })
    .then( async ( val ) => {
      // By this point everything is ok
      let [rows] = await promisePool.query( "SELECT ? AS passedDeptId,? AS passedUserid", val );
      res.send( rtn )
    })
    .catch((err) => {
      console.error(err)
      rtn.status = 'APPLICATION ERROR';
      rtn.errors.push( err.message);
      res.send( rtn )
    });
  });