MySQL DB Pool with Node.js and Restify

Question

I am currently developing a node.js backend for a mobile app with potentially many users. However it's my first time in developing node.js. I was following a tutorial on how to connect to a mysql database via mysql pools.

I am able to create a single mysql connection and do queries via my routes.

The problem arises once I establish the file structure mentioned in the tutorial:

dbConnect
-[models]
--users.js
-db.js
-server-ks

I am not getting an error message regarding the connection of the mysql database - even if I enter a wrong password.

// server.js

/////////////////////////////   basic setup ///////////////////////////////////

var restify = require('restify');
var bodyParser = require('body-parser');
var mysql = require('mysql');
var db = require('./db');
var users = require('./models/users');



/////////////////////////////   initilisation of the server   ///////////////////////////////////
var server = restify.createServer({
    name: 'testUsers',
});
server.use(restify.bodyParser({ mapParams: true }));

/////////////////////////////       Säuberung der URL       //////////////////////////////////////////
server.pre(restify.pre.sanitizePath()); 

/////////////////////////////       MySQL Instanz starten   //////////////////////////////////////////

db.connect(db.MODE_PRODUCTION, function (err) {
    if (err) {
        console.log('Unable to connect to MySQL.')
        process.exit(1)
    } else {
        server.listen(8080, function () {
            console.log('Listening on port 8080 ...')
        })
    }
})


/////////////////////////////       implementation of the routes  ///////////////////////////////////
function send(req, res, next) {
var test = users.getAll();
    res.json({ test: 'Hello ' + req.params.name });
    return next();
};

My DB.js file looks the following:

    var mysql = require('mysql'),
         sync = require('async')
    
    var PRODUCTION_DB = 'userDB',
        TEST_DB = 'userDB'
    
    exports.MODE_TEST = 'mode_test'
    exports.MODE_PRODUCTION = 'mode_production'
    
    var state = {
        pool: null,
        mode: null,
    }
    
    exports.connect = function (mode, done) {
        state.pool = mysql.createPool({
            connectionLimit: 50,
            host: 'localhost',
            user: 'user',
            password: 'password',
            database: 'userDB' // test
    
            //mode === exports.MODE_PRODUCTION ? PRODUCTION_DB : TEST_DB
        })
    
        
        state.mode = mode
        done()
     }
    
    exports.get = function () {
        return state.pool
    }

Could it be, that the tutorial spared out an essential part in utilizing mysql pools and node.js?

Thanks in advance for at least trying to answer that question.

Are there better methods sequelize(?) available to create performant connections to a MySQL database?

score 1 · Answer 1 · answered Oct 04 '16 at 03:29

It looks like creating the pool object does not actually connect to the database. A big clue is that the createPool function is not asynchronous, which is what you would expect if it was actually connecting at that moment.

You have to make use of the returned pool object to perform a query, which IS asynchronous.

From the documentation:

var mysql = require('mysql');
var pool  = mysql.createPool({
    connectionLimit : 10,
    host            : 'example.org',
    user            : 'bob',
    password        : 'secret',
    database        : 'my_db'
});

pool.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
    if (err) throw err;

    console.log('The solution is: ', rows[0].solution);
});

MySQL DB Pool with Node.js and Restify

1 Answers1