How to close MySQL connection after multiple promise?

Question

I have to use multiple SQL request. So I create a Promise to do this. But I need to use this promise into the same promise. When I try to stop my SQL connection with "connection.end()", I have this error in my console :
Error: Cannot enqueue Query after invoking quit.

If I don't write "connection.end()", my code works.

function requete( sql ) {
    return new Promise( ( resolve, reject ) => {
        this.connection.query( sql, ( err, rows ) => {
            if ( err )
                return reject( err );
            resolve( rows );
        } );
    } );
}

myRouter.route('/profil/verifBadge')
.get(function (req, res) {

    var tabBadgeAjoute  =   [];
    var tabVerifBadge   =   [];

    //Etape 1 
    requete("my SQL request")
    .then( (rows) => {
        if (rows.length != 0){
            for (let competence of rows){
                if (competence.TotalLike >=5)    { tabVerifBadge.push("one SQL request"); tabBadgeAjoute.push(myString)}
                if (competence.TotalLike >=10)   { tabVerifBadge.push("one SQL request"); tabBadgeAjoute.push(myString)}
                if (competence.TotalLike >=25)   { tabVerifBadge.push("one SQL request"); tabBadgeAjoute.push(myString)}
                if (competence.TotalLike >=100)  { tabVerifBadge.push("one SQL request" ); tabBadgeAjoute.push(myString)}
            }
        }

    })
    //Etape 2 
    .then( () => {
        for (let requeteSQL in tabVerifBadge){
            requete(tabVerifBadge[requeteSQL].toString())
            .then( (rows2) => {
                if (rows2.length == 0){
                    requete("one SQL request")
                    .then(() => console.log("ok")
                    .catch( err => console.dir(err));
                };
            })
            .catch( err => console.dir(err));
        }

    })
    .then( () => 
        res.send(JSON.stringify({"status": 200, "error": null, "response": "ok"}))
    )
    .then((rows) => connection.end())
    .catch( err => {
        // handle the error
        console.dir(err);
    })



 })

Yes I have understand that. But I write the "connection.end()" command on the last "then" of the Promise. Apparently, it's not here I have to write it. Do you know where I must write "connection.end()" — Bastien Roussel, Apr 03 '19 at 07:43

score -1 · Accepted Answer · answered Apr 03 '19 at 10:46

You have a for...of loop in your first Promise and a for...in loop in your second Promise. You can't guarantee that the queries in these two Promises will be completed when you call the last .then() to close the connection.

Try something like that:

.then(async function() {
    for (let requeteSQL in tabVerifBadge){
        await requete(tabVerifBadge[requeteSQL].toString())
            .then( (rows2) => {
                //...
            })
            .catch( err => console.dir(err));
    }
})

See also this question for alternative methods.

How to close MySQL connection after multiple promise?

1 Answers1