CODEWITHSUNDEEP
node.jspostgresqltransactionscontrollerspg-promise
Faz
Faz

Reputation: 379

sharing a transaction or task object between multiple queries across multiple controllers in pg-promise

I am relatively new to node.js, postgresql, promises and infact stackoverflow so apologies in advance if things sound a little disjointed!

I am currently trying to run multiple queries within chained promises spread across various controllers. I want to run all the queries within the same transaction or task to eliminate multiple connects and disconnects to the database.

I have tried the following where I am adding a student and assigning two mentors for that student. The HTTP request is routed to the student controller which adds a student via the student repository. The student repository is where the task starts and is returned to the controller which forwards it to the mentor controller and along the chain it goes...

@ HttpPost("/api/students/create")
addStudent( @ Req()request) {

var studentid;
var mentorids= [];
//Add the student
return this.studentRepository.addStudent(request.body.student)
.then(newStudentId => {
    studentid = newStudentId;

    //Add the mentors, passing the transaction object received back from
      studentRepository

    return this.mentorController.addMentor(request.body.schoolid, request.body.mentors, newStudentId.transaction)
    .then(result => {
        var data = [];
        console.log(result);
        for (var role in result) {
            data.push({
                mentorid: result[role].roleid,
                studentid: studentid
            });
        }
        //Assigns the student to mentors in the link table
        return this.studentRepository.assignMentor(data)
        .then(result => {
            return result;
        })

    })

});

}

Student repository

addStudent(student): any {
return this.collection.task(t => {
    return this.collection.one(this.sql.addStudent, student)
    .then(studentid => {
        return {
            studentid: studentid.studentid,
            transaction: t
        }
    });

})
}

Mentor controller

addMentor(institutionid: number, mentors, t): any {

var promises = [];
var mentorIds = [];
for (var role in mentors) {
    promises.push(this.roleController.registerRole(institutionid,mentors[role].role,t));
}
return t.batch(promises)
.then(result => {
    return Promise.resolve(result);
})

}

Role controller

@ HttpPost("/api/roles/register")
registerRole(institutionid,  @ Req()request, t ? ) : any {
console.log(request);
return this.roleRepository.checkRoleEnrollment(institutionid, request.email, request.roletype, t)
.then(result => {
    return this.roleRepository.addRoleEnrollment(institutionid, request, t)
    .then(data => {
        return this.roleRepository.updateRoleEnrollment(data.roleenrollmentid, data.roleid)
        .then(d => {
            return data;
        })

    })
})
.catch (error => {
    return Promise.reject(error);
});
}

I am getting the following error when I call checkEnrollment in the Role Controller:

"name": "Error",
"message": "Unexpected call outside of task.",
"stack": "Error: Unexpected call outside of task. at Task.query   
(\api\node_modules\pg-promise\lib\task.js:118:19) 
at Task.obj.oneOrNone (\api\node_modules\pg-promise\lib\database.js:491:31)         
at RoleRepository.checkRoleEnrollment....

Any help would be much appreciated. Thanking you in advance.

Upvotes: 3

Views: 940

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

As per my earlier comment:

That error means you are trying to access connection t allocated by a task somewhere outside of the task's callback function, i.e. the task's callback has returned, the connection was released, and then you are using the connection object allocated by the task from somewhere else, which is, of course, invalid.

b.t.w. I'm the author of pg-promise ;)

Below is what your code effectively doing, in a simplified form:

var cnReference;

db.task(t => {
    cnReference = t;

    // can only use `t` connection while executing the callback
})
    .then(data => {
        // we are now outside of the task;
        // the task's connection has been closed already,
        // and we can do nothing with it anymore!
        return cnReference.query('SELECT...');
    })
    .catch(error => {
        // ERROR: Unexpected call outside of task.

        // We cannot use a task connection object outside of the task's callback!
    });

You need to correct the implementation to make sure this doesn't happen.

Upvotes: 3

Related Questions