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 = test@email.com

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=?' , ['test@email.com'] ]).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?

Answer 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?

Answer 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=?' ,
['test@email.com'] ]).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);

});