The Node.js MySQL Driver Doesn't .end() As Gracefully With Connection Pooling

with-connection.js

// Require the core node modules.
var chalk = require( "chalk" );
var mysql = require( "mysql" ); // v2.11.1

// Define our connection to the MySQL database.
// --
// NOTE: In this demo, we are using a single connection.
var connection = mysql.createConnection({
	host : "127.0.0.1",
	user : "node",
	password: "node",
	database : "testing"
});


// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //


// When we submit a query before a connection is available, the driver will queue the
// query until the connection becomes available.
connection.on(
	"enqueue",
	function hanldeEvent() {

		console.log( chalk.bgYellow.white( "Waiting for connection slot." ) );

	}
);

// Run a few queries in serial. Since there is only one connection (we are not pooling),
// some of these queries will be enqueued before they are sent to the server.
for ( var i = 0 ; i < 5 ; i++ ) {

	connection.query(
		`
			SELECT
				COUNT( * ) AS userCount
			FROM
				user
		`,
		function handleResponse( error, records, fields ) {

			if ( error ) {

				console.log( chalk.bgRed.white( "Error:" ) );
				console.log( error );
				return;

			}

			console.log( chalk.bgGreen.white( "Count:", records[ 0 ].userCount ) );

		}
	);

} // END: For loop.

// Close the connection to the database so the Node.js process can close. Otherwise,
// the process will remain open until the database kills the connection.
// --
// NOTE: The .end() is a graceful operation on the connection - it will flush any
// queued queries before it sends the quit command to the MySQL server.
connection.end();

with-pool-promise.js

// Require the core node modules.
var chalk = require( "chalk" );
var mysql = require( "mysql" ); // v2.11.1
var Q = require( "q" ); // v2.11.1

// Define our connection to the MySQL database.
// --
// NOTE: In this demo, we are using a connection pool.
var pool = mysql.createPool({
	host : "127.0.0.1",
	user : "node",
	password: "node",
	database : "testing",

	connectionLimit: 2, // Default value is 10.
	waitForConnections: true, // Default value.
	queueLimit: 0 // Unlimited - default value.
});


// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //


// Connections, within the pool, are created in a lazy manner. When a connection is
// established, the connection event is fired. This does not happen each time a
// connection is obtained from the pool - only when the connection is first created.
pool.on(
	"connection",
	function hanldeEvent() {

		console.log( chalk.bgYellow.white( "Pooled connection established." ) );

	}
);

// When we submit a query before a pooled connection is available, the driver will
// queue the query until a pooled connection becomes available.
pool.on(
	"enqueue",
	function hanldeEvent() {

		console.log( chalk.bgYellow.white( "Waiting for connection slot." ) );

	}
);


// Let's create a simple database API that exposes a promise-based query method. This
// way, we can wait for responses to come back before we teardown the connection pool.
var db = {
	query: function( sql, params ) {

		var deferred = Q.defer();

		// CAUTION: When using the node-resolver, the records and fields get passed into
		// the resolution handler as an array.
		pool.query( sql, params, deferred.makeNodeResolver() );

		return( deferred.promise );

	}
};


// Run a few queries in parallel using the connection pool.
// --
// NOTE: We are initiated more queries than we have connections in the pool (Limit: 2).
var promises = [ 0, 1, 2, 3, 4 ].map(
	function iterator() {

		var promise = db
			.query(
				`
					SELECT
						COUNT( * ) AS userCount
					FROM
						user
				`
			)
			.then(
				function handleResponse( results ) {

					console.log( chalk.bgGreen.white( "Count:", results[ 0 ][ 0 ].userCount ) );

				},
				function handleError( error ) {

					console.log( chalk.bgRed.white( "Error:" ) );
					console.log( error );
					return( Q.reject( error ) );

				}
			)
		;

		return( promise );

	}
); // END: Map loop.

// When all the queries have completed, close the pooled connections to the database
// so the Node.js process can close. Otherwise, the process will remain open until the
// database kills the connections.
Q
	.allSettled( promises )
	.then(
		function handleSettled( snapshots ) {

			pool.end();

		}
	)
;

with-pool.js

// Require the core node modules.
var chalk = require( "chalk" );
var mysql = require( "mysql" ); // v2.11.1

// Define our connection to the MySQL database.
// --
// NOTE: In this demo, we are using a connection pool.
var pool = mysql.createPool({
	host : "127.0.0.1",
	user : "node",
	password: "node",
	database : "testing",

	connectionLimit: 2, // Default value is 10.
	waitForConnections: true, // Default value.
	queueLimit: 0 // Unlimited - default value.
});


// ----------------------------------------------------------------------------------- //
// ----------------------------------------------------------------------------------- //


// Connections, within the pool, are created in a lazy manner. When a connection is
// established, the connection event is fired. This does not happen each time a
// connection is obtained from the pool - only when the connection is first created.
pool.on(
	"connection",
	function hanldeEvent() {

		console.log( chalk.bgYellow.white( "Pooled connection established." ) );

	}
);

// When we submit a query before a pooled connection is available, the driver will
// queue the query until a pooled connection becomes available.
pool.on(
	"enqueue",
	function hanldeEvent() {

		console.log( chalk.bgYellow.white( "Waiting for connection slot." ) );

	}
);

// Run a few queries in parallel using the connection pool.
// --
// NOTE: We are initiated more queries than we have connections in the pool (Limit: 2).
for ( var i = 0 ; i < 5 ; i++ ) {

	pool.query(
		`
			SELECT
				COUNT( * ) AS userCount
			FROM
				user
		`,
		function handleResponse( error, records, fields ) {

			if ( error ) {

				console.log( chalk.bgRed.white( "Error:" ) );
				console.log( error );
				return;

			}

			console.log( chalk.bgGreen.white( "Count:", records[ 0 ].userCount ) );

		}
	);

} // END: For loop.

// Close the pooled connections to the database so the Node.js process can close.
// Otherwise, the process will remain open until the database kills the connections.
// --
// CAUTION: The .end() method, when using a connection pool, is NOT AS GRACEFUL as
// when using a single connection. Calling the .end() at this point will actually
// break the script because the preceding connections, required by the queries, have
// not yet been obtained.
pool.end();