Node, Bluebird Promises, MySQL, and the need for a stiff drink

Question

I'm struggling to implement Bluebird promises with MySql. I think I'm close, but my problems are starting to compound and I could use some help getting back on track. I had this code all working with callbacks, but I have jacked it up with promises.

The current problem is that it doesn't like the "then" on my query, as in:

conn.query("DO DB STUFF").then(function(result){});

First, I have a connection.js file that looks like this. I've added some comments in the code.

(function () {
var mysql = require("mysql");

var pool = mysql.createPool({
    connectionLimit: 10,
    host: "localhost",
    user: "myuser",
    password: "password",
    database: "dbName"
});

// stole this from http://stackoverflow.com/questions/24797314/how-will-a-promisified-mysql-module-work-with-nodejs
exports.getConnection = function(){
    return pool.getConnectionAsync().disposer(function(connection){
        try{
            connection.release();
        } catch (e) {}
    });
};
})();

Then I have a "db access" file that looks like this:

var Promise = require("bluebird");
Promise.promisifyAll(require("mysql/lib/Connection").prototype);
Promise.promisifyAll(require("mysql/lib/Pool").prototype);
var connection = require("./connection");
var common = require("../../domain/common");

exports.isKnownBadTicker = function (stockSymbol) {
    if (stockSymbol == null) { return false; }
    else {
        var ticker = common.standardizeStockSymbol(stockSymbol);
        var query = "SELECT COUNT(*) AS count FROM BadTicker WHERE Symbol = '" + ticker + "';";
        // it doesn't like the conn.query(query).then . . . says that "then" is not valid, and so I tried to just return the response of the query - which also doesn't work.
        Promise.using(connection.getConnection(), function (conn) {
            return conn.query(query)[0]["count"] > 0;
            // conn.query(query).then(function (result) {
            //  return result[0]["count"] > 0;
            // });
        });
    }
};

I feel like I'm close, but I just can't quite put it together. Can anyone help me straighten this mess out?

Also, I'm not sure I'm doing some other things right, like, can I return a boolean in a function that also returns promises? Feel free to straighten me out if you see that I've gone all whacky. Any help is appreciated.

Vic

What do you think this `Promise.promisifyAll(require("mysql/lib/Connection").prototype);` accomplishes? You aren't saving any return value from the `require()` operation so it makes me wonder what you think the result of this operation is? — jfriend00, Jul 27 '15 at 01:30
Took that code from here: https://github.com/petkaantonov/bluebird/blob/master/API.md#promisepromisifyallobject-target--object-options---object. That doesn't mean I'm using it correctly, obviously. — Vic F, Jul 27 '15 at 01:38

Vlad Ankudinov · Answer 1 · 2016-03-03T08:18:14.873

When you use promisifyAll asynchronous functions got they promisifyed siblings with Async suffix. So that you have to use queryAsync rather then query

conn.queryAsync("DO DB STUFF")
  .then(function(result){
    // dont forget here that result is an array
  });

Promisifying in second connection example is ok (my version even bit uglier)

var Promise = require('bluebird');
var mysql = require('mysql');
Promise.promisifyAll(mysql.Connection.prototype);
Promise.promisifyAll(require('mysql/lib/pool').prototype);

Getting connection in mysql module is sinchronous rather then asynchronous. I think it was done this way for simplification, but now it makes things more confusing. So when you call to mysql.createPool you dont have to wait and can work with returned pool instantly - mysql module will store you queries and execute it when connection will be really established.

/* store this pool in a good place - you have to reuse it. */
var pool = mysql.createPool(credentials); 
/* you could also create connection like this 
   var conn = mysql.createConnection(credentials);
   , but pull is better when you want to do many queries at a time */

Now you able to do promisifyed queries with your pool like this

pool.queryAsync("DO DB STUFF")
  .then(function(result){
    // dont forget here that result is an array
  });

score 0 · Answer 2 · answered Sep 21 '15 at 11:59

I couldn't figure it out either! Closest I got was something like:

var Promise = require("bluebird");    
var mysql = require("mysql");
var pool = mysql.createPool({
 connectionLimit: 10,
 host: "localhost",
 user: "myuser",
 password: "password",
 database: "dbName" });
pool.query = Promise.promisify(pool.query);
pool.query("DO DB STUFF")
  .spread(function(rows,fields){
    //access your rows here
    });

The catch was that fields was being appended to the rows array, so you needed to use .spread.

However, this whole thing made me very uncomfortable that I had no clue how to use this, so I looked at knex which is just a query builder and supports promises natively.

Node, Bluebird Promises, MySQL, and the need for a stiff drink

2 Answers2