Jumpa Jumpa - 2 months ago 11
MySQL Question

Promisify Custom Method

I'm pretty new to Node and JS world. What I'm triyng to achieve is to "modularize" my queries and reuse them in various scenarios. This is my db manager:

'use strict'

const mysql = require('mysql')
var Promise = require('bluebird')
var using = Promise.using
Promise.promisifyAll(require('mysql/lib/Connection').prototype)
Promise.promisifyAll(require('mysql/lib/Pool').prototype)
const config = require('./config')

var pool = mysql.createPool({
connectionLimit: 100,
host: config.dbHost,
user: config.dbUser,
password: config.dbPassword,
database: config.db,
debug: config.dbDebug
})

var getConnection = function () {
return pool.getConnectionAsync()
.disposer(function (connection) {
return connection.release()
})
}

var query = function (command) {
return using(getConnection(), function (connection) {
return connection.queryAsync(command)
})
}

module.exports = {
query: query
}


In a separate file I want to call a query and depending on the result of that then call another one (the second one is using the result value of the first):

utils.method1()
.then(function (value) {
utils.method2(value)
})
.catch(function (error) {
console.error('Error while retrieving product id: ' + error)
res.json({ data: error })
})


How can I "promisify" my method? More importantly: is this the right way to separate mySQL queries? Can you suggest some best practices?

For completeness here's my method1 that execute the query:

module.exports = {
method1: function () {
// ...sql
db.query(mysql.format(sql, params))
.then(function (results) {
return results[0].id // clearly this is not a promise
})
.catch(function (error) {
console.error('Error while retrieving...: ' + error)
res.status(500).send('Internal server error')
})
}
}

Answer

You're actually very close from promisifying : )

Sure, results[0].id is not a promise, but it is the final value of one.

What you should do is return the chain of promises of your query :

return db.query(mysql.format(sql, params))
    .then(function (results) {
        return results[0].id // clearly this is not a promise
    })
    .catch(function (error) {
        console.error('Error while retrieving...: ' + error)
        res.status(500).send('Internal server error')
    })

Doing so, you will return a promise that will either resolve with the last value of your chain, or fail. You can use it the way you asked:

method1.then(function(value){
    // Here, value is results[0].id
})
.catch(function(err){
    // Manage a failed query
});

There's a great post that you might want to read about how Promises works : https://blog.domenic.me/youre-missing-the-point-of-promises/