db_pools_multi.js

var Promise = require('bluebird'),
    mysql = require('mysql'),
    using = Promise.using;

Promise.promisifyAll(require("mysql/lib/Connection").prototype);
Promise.promisifyAll(require("mysql/lib/Pool").prototype);

var env = (process.env.NODE_ENV == 'production') ? 'prod': 'dev';
var pools = {};
var base = {
    host: undefined,
    user: undefined,
    password: undefined,
    database: undefined,
    connectionLimit: undefined,
    multipleStatements: true,
    acquireTimeout: 30000,
    typeCast: function (field, next) {
        if (field.type == "BIT" && field.length == 1) {
            var bit = field.string();
            return (bit === null) ? null : bit.charCodeAt(0);
        }
        return next();
    }
};
var dbs = {
    dev: {
        main: {
            host: 'localhost:80',
            user: 'user',
            password: 'password',
            database: 'db_dev',
            connectionLimit: 5
        },
        log: {
            host: 'localhost:80',
            user: 'user',
            password: 'password',
            database: 'db_dev_log',
            connectionLimit: 3
        }
    },
    prod: {
        main: {
            host: 'localhost:81',
            user: 'user',
            password: 'password',
            database: 'db_prod',
            connectionLimit: 10
        },
        log: {
            host: 'localhost:81',
            user: 'user',
            password: 'password',
            database: 'db_prod_log',
            connectionLimit: 5
        }
    }
};
var pools = {};
var create_pools = function () {
    Object.keys(dbs[env]).forEach(function (d) {
        var o = Object.assign({}, base);
        Object.keys(dbs[env][d]).forEach(function (k) {
            if (o[k] === undefined) o[k] = dbs[env][d][k];
        });
        pools[d] = mysql.createPool(o);
    });
};
var get_connection = function (database) {
    return pools[database].getConnectionAsync().disposer(function (connection) {
        return connection.release();
    });
};
var query = function (database, command) {
    return using(get_connection(database), function (connection) {
        return connection.queryAsync(command);
    });
};
var execute = function (database, q, p, single, type) {
    if (p) q = mysql.format(q, p);
    return query(database, q).then(function (r) {
        if (type == 'sp') return (single) ? (r[0] ? r[0][0] : {}) : r[0];
        return (single) ? (r ? r[0] : {}) : r;
    }).catch(function () {
        return single ? {} : [];
    });
};
var raw = function (database, q, p, single) {
    return execute(database, q, p, single, 'raw');
};
var sp = function (database, q, p, single) {
    return execute(database, q, p, single, 'sp');
};
module.exports = {
    sp: function (database, q, p, single) {
        return sp(database, q, p, single);
    },
    raw: function (database, p, single) {
        return raw(database, q, p, single);
    }
};
create_pools();

var mock = function () {
    var q = 'SELECT name FROM items WHERE id = ? LIMIT 1;';
    execute('main', q, [1], true, 'raw').then(function (r) {
        console.log(r);
    });
};