yevg yevg - 7 days ago 7
Node.js Question

Making multiple mysql calls using JS promise in Node.js

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

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);

});
Comments