Creating a query that relies on results of previous query using node-postgres

Question

The first query to the postgres database is a SELECT query gets all shift/break data for the current day which is used to determine the type of scan

The second query is an INSERT query that is dependent on the results of the first query

My handler function looks like this right now:

const scanEvent = (request, response) => {
   employee_id = request.body.employee_id;
   var shifts;
   Promise.all([
      pool.query('Select * FROM shifts WHERE employee_id=$1 AND date=CURRENT_DATE', [employee_id])
   ]).then(function([queryResults]) {
      shifts = queryResults.rows;
   }).catch(function(e) {
      response.status(500).send('Error retrieving data');
   })
   // based on the results of the query there will be a bunch of different cases for
   // INSERT queries to put in the proper data to the database
   if(shifts.length == 0) {
      Promise.all([
         pool.query('INSERT INTO shifts (employee_id, start_time) VALUES ($1, NOW())', [employee_id])
      ]).then(function() {
         response.status(204).send('Successfully Inserted');
      }).catch(function (e) {
         response.status(500).send("Error");
      });
    } // else if ... handle all other cases
}

My issue is that I cannot access the results of the first query as it seems that the shifts variable is local in scope to the first Promise.all

** EDIT **

I have now realized my approach was not optimal (was just learning node-postgres) A better way to solve this problem is to use async / await:

const scanEvent = async (request, response) => {
   employee_id = request.body.employee_id;
   var shifts;

   const getShifts = await pool.query('Select * FROM shifts WHERE employee_id=$1 AND date=CURRENT_DATE', [employee_id]);

   shifts = getShifts.rows;

   // based on the results of the query there will be a bunch of different cases for
   // INSERT queries to put in the proper data to the database
   if(shifts.length == 0) {
      await pool.query('INSERT INTO shifts (employee_id, start_time) VALUES ($1, NOW())', [employee_id]);

    } // else if ... handle all other cases
}

Answer 1

The variable shifts will not yet have a value when the if statement is executed, because it receives its value only in the .then function. Therefore, if the second half or your code relies on the value of shifts, move it into the .then function:

.then(function([queryResults]) {
  shifts = queryResults.rows;
  if(/* first scan therefore scanning in for shift */) {
    ...
  } // else if ... handle all other cases
})

(If you want two independent queries executed in parallel, see here.)