Making multiple mysql calls using JS promise in Node.js

Question

GOAL:

I am trying to make multiple SQL queries using the "mysql" module in Node.js such that the result of one query is used as input in the next statement.

sql1 = SELECT id FROM users WHERE email=? // get user id from email
val1 = [email protected]

sql2 = SELECT something FROM profiles WHERE user_id=?
val2 = user_id // result of sql1

APPROACH:

I am using a JS "promise" to achieve this:

var run_query = function(conn,sql){

return new Promise(function (fulfill, reject){

    conn.query(sql, val, function(err, res) {

        if(err) reject(err);
        else    fulfill(res);

    });

});

}

var conn = db.config(mysql);

run_query(conn,[ 'SELECT id FROM users WHERE email=?' , ['[email protected]'] ]).then(function(result){

  console.log(result.id); // result of 1st query

  return run_query(conn,[ 'SELECT something FROM profiles WHERE user_id=?' , [result.id] ]);

}).then(function(result){

    console.log(result.something); // result of 2nd query 

}).catch(function(err){

    console.log('there was an error', err);

});

conn.end();

ISSUE:

The 1st query returns the correct result, but the second query throws the following error from the mysql module:

Error: Cannot enqueue Query after invoking quit.
~blah blah blah~
code: 'PROTOCOL_ENQUEUE_AFTER_QUIT', fatal: false

MY ATTEMPTS TO RESOLVE:

I believe this has something to do with multiple calls being made while the connection is open and conn.end(); being called at the very end. I moved it around a bit but that didnt work.

I tried turning on multiple statement for the mysql module

var conn = mysql.createConnection({multipleStatements: true});

..but that didnt work either.

How does one use the mysql node module with a JS promise structure?

You might want to use something like Sequelize that supports promises out of the box rather than hacking together your own wrapper. — tadman
– tadman, Commented Nov 26, 2016 at 9:00

tadman · Accepted Answer · 2016-11-26 09:02:12Z

2

You are using promise but the promise code is synchronse and outer code is asynchronse . so conn.end run before your second query.

Use conn.end inside second .then block like this and remove outer conn.end:

var conn = db.config(mysql);

run_query(conn,[ 'SELECT id FROM users WHERE email=?' ,
['[email protected]'] ]).then(function(result){

 console.log(result.id); // result of 1st query

 return run_query(conn,[ 'SELECT something FROM profiles WHERE
user_id=?' , [result.id] ]);

}).then(function(result){

   console.log(result.something); // result of 2nd query 
    conn.end();
}).catch(function(err){

   console.log('there was an error', err);

});

edited Nov 26, 2016 at 9:02

tadman

212k23 gold badges237 silver badges266 bronze badges

answered Nov 26, 2016 at 8:58

Himanshu sharma

8,0436 gold badges48 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tadman Over a year ago

Translation: "Hey, MySQL, can you do this query, plus a bunch of other stuff depending on that? Oh, and while you're doing that, can you close the connection right now?" You're pulling the plug on this thing before it's finished.

Gandalf the White · Accepted Answer · 2016-11-26 09:02:48Z

1

Promises explicitly is used to write cleaner code but you are doing quite the opposite.

What Himanshu said is right your conn.end() is executed first. Given your use case I'd suggest you to use Async Module Click Here. Go through features available once.

I think Async.series will server you right although there are multiple async features that can help your case, notably, queue.

async.series([
    function(callback) {
     //Your First SQL Query based on response send success or failure callback
        callback(null, 'one');
    },
    function(callback) {
     //Your Second SQL Query based on response send success or failure callback
        callback(null, 'two');
    }
],
// optional callback
function(err, results) {
    //listen to results from your codes and put some logic based on what you want and close the connection here
conn.close();
});

Doubt : Why do you want to execute them one by one?

answered Nov 26, 2016 at 9:02

Gandalf the White

2,4752 gold badges21 silver badges39 bronze badges

4 Comments

Himanshu sharma Over a year ago

Gandalf the White , his promise syntax is right . No need to use async.series ,he can do by promise . You can say async.series is another approach to do this work.

Gandalf the White Over a year ago

the code looks far from clean and I never said his syntax is wrong? Read again. And, people do suggest better ways to solve a problem on SO. I hope you are aware about that.

yevg Over a year ago

why is the code not clean? I have a promise function and a .then chain. I have implemented an async version like you posted prior to this but the point is to use the result of one query as the input in another. doing this asynchronously would not make sense since you wouldnt have the first result reliably available

Gandalf the White Over a year ago

@yevg everyone has their opinion I believe, I would rather choose aysnc for such work. From the code written above I cannot see that you are using values from one part for another one. If you want that you can use waterfall flow from async module. Async module was made to solve anti patterns man.

Collectives™ on Stack Overflow

Making multiple mysql calls using JS promise in Node.js

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related