Connection pooling node-db with generic-pool

I have recently been asked to add connection pooling to node-db. While I confess it crossed my mind to add it as part of the generic framework, I then decided to remain true to node-db’s purpose: to provide a common framework for writing C++ based database bindings for Node.js.

Therefore I’d like to show through this post just how easy it is to pool node-db connections using existing Node.js solutions. For this example, I chose generic-pool, a generic pooling solution for node.js that looks so simple yet so elegant. We wish to create an HTTP server that listens for requests, and on each request performs a database query and outputs the results back to the client.

Start by installing the required packages (db-mysql and generic-pool):

$ npm install db-mysql
$ npm install generic-pool

Now, create a file named test.js with the following contents (update the connection settings and table name to match your system):

var http = require('http'),
    mysql = require('db-mysql'),
    generic_pool = require('generic-pool');

var pool = generic_pool.Pool({
    name: 'mysql',
    max: 10,
    create: function(callback) {
        new mysql.Database({
            hostname: 'localhost',
            user: 'root',
            password: 'password',
            database: 'database'
        }).connect(function(err, server) {
            callback(err, this);
        });
    },
    destroy: function(db) {
        db.disconnect();
    }
});

http.createServer(function (req, res) {
    res.writeHead(200, {'Content-Type': 'text/plain'});

    pool.acquire(function(err, db) {
        if (err) {
            return res.end("CONNECTION error: " + err);
        }

        db.query().select('*').from('users').execute(function(err, rows, columns) {
            pool.release(db);

            if (err) {
                return res.end("QUERY ERROR: " + err);
            }

            res.end(JSON.stringify(rows));
        });
    });
}).listen(1337, "127.0.0.1");

console.log('Server running at http://127.0.0.1:1337/');

Finally, run the server with:

$ node test.js

Whenever you access http://127.0.0.1:1337/ the database connections will be coming from the pool. Enjoy!

Comments (1)

  1. 4:41 am, April 4, 2012Andrius Virbičianskas  / Reply

    I’m not sure what causes the following issue, but when I try to connect with a pool larger than 1, I sometimes get “Error: Can’t execute a query without being connected” or “Host … is not allowed to connect to this MySQL server” This is even more apparent with multiple workers using Node.js cluster module. My guess is, that there is some hidden race conditions. If you have the same problem and want to fix it, put

    {async: false}

    as a first parameter in the connect() method. It’s not a good idea to use this inside a http listener though, because there is a chance of performance degradation.

Leave a Reply

Allowed Tags - You may use these HTML tags and attributes in your comment.

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Pingbacks (1)

  1. 2:07 pm, March 6, 2012Favicon of www.quora.comQuora