node-mysql-pool

Purpose

node-mysql-pool is a MySQL connection pool for node.js on top of Felix Geisendörfer's MySQL client node-mysql.

Using a connection pool instead of a single connection should render a remarkable speed-up, when you have many short living connections, e.g. with message board applications.

TOC

• Tutorial
• Current status
• Contributors
• Compatibility
• Installation
• API
  • Creation of a new pool
  • Options
  • Methods affecting all connections
  • Methods invoked on a single connection
  • Methods unrelated to connections
  • event: 'error' (err)
• Todo
• Licence

Tutorial

var MySQLPool = require("mysql-pool").MySQLPool;
var pool = new MySQLPool({
  poolSize: 4,
  user:     'root',
  password: 'root',
  database: 'test'
});
 
pool.query("SELECT 'Hello, World!' AS hello", function(err, rows, fields) {
  if(err) throw err;
  console.log(rows[0].hello);
});
 
for(var i = 0; i < 10; ++i) {
  pool.query("SELECT SLEEP(2), ? AS i", [i], function(err, rows, fields) {
    if(err) throw err;
    console.log("Slept: " + rows[0].i);
  });
}

You probably do not have to change anything if you already used node-mysql or any of its forks!

Current status

This module is currently not backed by proper unit testing. Nevertheless I found it stable for my testings.

If you find an error, please file an issue!

Contributors

• René Kijewski
• Michael Lai (fixed issue #1)
• Daniel Dickison (fixed issue #3)
• dall (spotted issue #5)
• Demián Rodriguez (fixed issue #7)

Compatibility

This module was only tested using node >= 0.4.x. It does not work with older versions of node.js.

The node-mysql-pool even works with unknown forks of node-mysql, as long as

• the last parameter of any method is the callback function,
• no events at all are emitted when supplying a callback function, and
• when the first parameter of a callback is set, it denotes an error.

Otherwise the requirements are the same as for node-mysql.

Installation

• Using npm: npm install mysql-pool
• Using git:
  • git clone git@github.com:Kijewski/node-mysql-pool.git node-mysql-pool
  • or
  • git submodule add git@github.com:Kijewski/node-mysql-pool.git deps/node-mysql-pool

API

The API of this module is as similar to node-mysql as possible, with two exceptions:

• You must always supply a callback function. Using listeners is not supported.
• Property x, when not supplied while creation, are to be set to instance.properties.x.

When called back, this will be the used connection. (You probably never need to know which connection was actually used.)

Creation of a new pool

mysqlPool.Pool([options])

creates a new, currently empty. Any property for the single connections or the connectionpool, resp., can be set using the options object.

If the parameter poolsize is omitted, 1 is used.

Only if all connection attemps failed err is supplied. If some connections failed, result.error will contain a list of Errors. If some or all connections succeeded, results.connections will contains the pool's size.

Options

Defaults:

pool.poolSize = 1
pool.mysql = require("mysql")

• pool.poolSize:
  • The number of connections to establish to the server.
• pool.mysql:
  • If you do not want the npm version of node-mysql—e.g. because you forked and tweaked it for your purposes—you can supply a different library to use.
• pool.properties.xyz = undefined:
  • Property xyz of the mysql.Client object. See the original documentation of node-mysql for more property related information.

Methods affecting all connections

client.useDatabase(database, cb)
client.end([cb])
client.destroy()

• pool.useDatabase(database, cb):
  • Changes the database for every connection.
• pool.end([cb]):
  • Shuts down every connection, not waiting for any enqueued and waiting queries. Active queries won't be aborted, though.
• pool.destroy():
  • Kills every connection. You do not want do use this method!

For all methods you can invoke on a single connection, there is an equivalent methodnameAll(...) method. E.g. you can use pool.pingAll(cb), if you want you to ping all connections for some reason.

cb will be called once for every connection affected. Subject to change!

Methods invoked on a single connection

All methods of the Client object will be supported—with connect(...), end(...), useDatabase(...) and destroy(...) being overwritten.

If you do not use a fork, that are currently:

query(sql, [params], cb)
ping([cb]))
statistics([cb])

See the original documentation of node-mysql for method related information.

Beware:

• You must supply a callback method, if you have any parameters.
• No events are emitted but error.

Methods unrelated to connections

format(sql, params)
escape(val)

Will behave exactly like the original methods. They do not belong to a single connection.

event: 'error' (err)

Emitted if and only if an error occurred and no callback function was supplied. You should always supply a callback function!

Todo

• The methods affecting all connections have a strange API. cb should be called only once.

Licence

node-mysql-pool is licensed under the MIT license.