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?

score 1 · Answer 1 · answered Nov 01 '18 at 16:47

first: both snippets are broken as the rows variable needs to be declared outside if if.

Aside from that, what you're doing is mostly fine, but the big issue here is that if length of either is 0, you return nothing.

Is that really the behavior you want? If I call /addUserToDepartment and there is a problem in your database, do you want this to silently fail?

I think a better approach is to return appropriate errors when something goes wrong. Ideally you should just throw an exception, (but you're using Express, and I'm not sure if they support catching exceptions).

Thanks for taking the time to answer. I did go back to it and add catches as Node.js was not happy without them. — Andy Jarrett, Nov 07 '18 at 16:14

score 0 · Accepted Answer · answered Nov 07 '18 at 16:18

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 )
    });
  });

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

2 Answers2