Building promise chain with logic using PostgreSQL

Question

I'm new to promise and trying to build a logic with promises on NodeJS. I have an API for keep track of worked hours.

People just have to enter their PIN number to Clock In or Out.

What the API (for the CLOCK-IN part) does, is to check the database (PostgreSQL with pg-promise) to find the Employee ID with the PIN. If PIN is invalid (no employee has been found), reject the promise with an error message. Then it have to lookup for their "ClockStatus" to verify if they are already IN or OUT. It can be done with the same query that retrieve the Employee ID. If already IN, reject the promise with message. Then it have to lookup for the Shift ID from the Employee. After that, it will gather the Shift informations to compare Time. Finnaly, if will insert a row in the database with gathered informations from previous step.

In Short :

New Promise 
 Get the Employee ID and Clock Status, and Shift ID
Then
 If No Employee found then reject the promise
Then 
 If Clock Status is already in, reject the promise with a different message
Then
 Gather informations for the shift 
Then
 Insert a new row in the WorkHour table using Employee info and Shift informations

I have my router here (Using ExpressJS)

router.post('/clockin', function(req, res) {
    //Execute the API
    time
        .clockin(req.body.pin)
        .then(function(time) { res.send(time)})
        .catch(function(err) { res.status(500).send(err) });
});

I'm trying to build the promise chain, but I don't want to be caught in a pyramide of doom.

clockin: function(pin) {

    // Getting the resourceID from PIN
    var p = new Promise((resolve, reject) => {
        if (pin === undefined) {
            throw new Error('No PIN');
        } else {
            db.any('SELECT id, timeclockstatus From Employee Where PIN =  $1', pin)
            .then((EmployeeInfos) => {
                return (db.any('select id, timeclockstatus from res_resources where id = $1 ', ResourceID[0].id))
            })
//******  I'm stuck here

I would like to use native promise of ES6. I tried to lookup for examples but none seem to fit what i'm looking for. Or maybe I'm missing something... Any suggestion ?

Answer 1

In a simplified form, as not everything is clear from your case description:

router.post('/clockin', function (req, res) {
    db.task(t=> {
        var pin = req.body.pin;
        if (!pin) {
            throw new Error('No PIN');
        }
        return t.one('SELECT id, timeclockstatus FROM Employee WHERE PIN = $1', pin)
            .then(data=> {
                // return another query request, based on `data`
            })
            .then(data=> {
                // return your insert query as needed, etc...
            });
    })
        .then(data=> {
            // provide successful response;
        })
        .catch(error=> {
            // provide error response;
        });
});

Answer 2

If you are new to promises, it's maybe not surprising you are getting stuck. The essential issue is knowing how to access previous results in a then chain, in particular your need for employee info (from the first stage) and shift info (from the third stage) in the final stage.

As you can see in the reference, various approaches are available. Of those approaches :

• "Mutable contextual state" is the simplest but ugly.
• "Break the chain" is a possibility.
• "Nesting and closures" is totaly viable but would give you the pyramid of doom that you are trying to avoid.
• "Explicit pass-through" (various formulations) formulates nicely with Bluebird promises and would be my choice here.

clockin: function(pin) {
    if (!pin) {
        return Promise.reject(new Error('No PIN'));
    } else {
        // STAGE 1
        let sql = ....; // build sql for retrieving employee record
        return db.any(sql)
        .catch(e) => {
            throw new Error('problem finding employee info');
        }
        // STAGE 2
        .then((employeeInfo) => {
            let sql = ....; // build sql for retrieving employee's current status
            return Promise.all(employeeInfo, db.any(sql))
            .catch(e) => {
                throw new Error('problem finding current status');
            };
        })
        // STAGE 3
        .spread((employeeInfo, status) => {
            // a special case here, status === 'IN' requires us to throw.
            if (status === 'IN') {
                throw new Error('already IN');
            }
            let sql = ....; // build sql for gathering shift data
            return Promise.all(employeeInfo, status, db.any(sql))
            .catch(e) => {
                throw new Error('problem finding shift data');
            };
        })
        // STAGE 4
        .spread((employeeInfo, status, shiftData) => {
            let time = .....; // calculate `time` using all/any of `employeeInfo', 'status', 'shiftData`
            let sql = ...; // build sql for inserting new row in the WorkHour table.
            return db.any(sql)
            .then(() => [employeeInfo, status, shiftData, time]) // for completeness, make all data available to the caller, though you could return just `time`.
            .catch((e) => {
                throw new Error('problem saving new clocking');
            });
        });
    }
},

The four stages formulate slightly differently but are identical in nature. Each stage :

• does something asynchronous,
• passes on all previous results plus its own result down the main chain's success path,
• passes a meaningful error message down the main chain's error path.

Note the use of Promise.all() and (Bluebird's) .spread() to make multiple results available to the next stage. The same effect could be achieved with native ES6 promises but some of the simplicity exhibited above would be lost.

With the code above, the '/clockin' router function would be :

router.post('/clockin', function(req, res) {
    //Execute the API
    time.clockin(req.body.pin)
    .spread((employeeInfo, status, shiftData, time) => { res.send(time); })
    .catch((err) => { res.status(500).send(err) }); // any uncaught errors thrown above will percolate down to here.
});