The Node.js MySQL Driver Doesn't .end() As Gracefully With Connection Pooling
When you're running a Node.js web application, there's a good chance that you never think about gracefully shutting down your application. In all likelihood, you just let it run forever (or at least until it crashes). When you're using Node.js as a scripting language, however, just the opposite is true - tearing down the script is an inevitable part of the script lifecycle. Recently, I was using Node.js as part of a MySQL data-migration script. During this process, I stumbled upon the fact that a connection pool, in the Node.js MySQL driver, doesn't shutdown quite as gracefully as a single connection.
With the Node.js MySQL driver, you can switch between a single connection and a connection pool quite seamlessly. Both APIs present a .query() method for running queries and a .end() method for closing communication with the MySQL database server. In a web application, you might never use the .end() method. But, with a migration script, actively using the .end() method is critical because the Node.js process will hang without it (until the MySQL database drops the connections).
As I discovered, however, the .end() method in the connection pool API behaves a little differently than the .end() method in the single connection API. Well, depending on how you look at it. When using a single connection, the .query() method will implicitly call the .connect() method under the hood. The .end() method will then flush any queued queries on the connection before disconnecting from the MySQL server. As such, we can call .end() at the end of our migration script and everything will work as one would hope.
To see this in action, here's a simple demo that initiates five queries on a single connection and then immediately calls .end():
// 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();
While the queries will run and return asynchronously, we can still call the .end() synchronously. The connection won't be terminated until the queued queries return with a result. As such, when we run the above Node.js code, we get the following terminal output:
As you can see, everything ran smoothly.
Now, let's switch from using a single connection to using a connection pool. The advantage of a connection pool is that we can run queries in parallel. In the following, we're using the same general logic; only, we're using a connection pool instead of a single connection:
// 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();
As you can see, just as with the first demo, we're initiating the queries and then immediately call the .end() method. This time, however, we get a very different result:
As you can see, none of the queries could be initiated because none of the connections in the connection pool could be established before the .end() method was called. So, while the .end() method on the connection pool is "graceful," it is only graceful at the connection level. Meaning, established connections will end gracefully, but queued connections will not.
To solve this problem, we ultimately created a simple Promise-based proxy to the underlying connection pool. This way, rather than calling .end() immediately, we could easily call .end() once all the queries had actually returned.
// 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();
}
)
;
Now that we're waiting for all the promises to "settle" before calling the .end() method, we can teardown the Node.js script without prematurely disconnecting from the MySQL database.
While I love JavaScript; and, I'm really excited to learn more about Node.js; I have to admit that writing data migration scripts in Node.js is an order of magnitude harder than it is in a "blocking" language like ColdFusion. Asynchronousity is awesome when you're dealing with high concurrency. But, when its just a single process moving data from one place to another, I'd much rather using a blocking, synchronous language that can selectively spawn asynchronous threads when it needs to.
Want to use code from this post? Check out the license.
Reader Comments
Great article - I endede up doing precisely the same when implementing a large app a year ago.
The approach also has the bonus advantage that you now get to use your database im a promise chain..
I myself was caught with my pants down when our db connection resources kept running out.
@Lars Rye,
Totally - working with promises is much nicer than callbacks, once you are used to it.
Now, someone just told me about a project called "mysql2" which is supposed to have some even better features around parameters and prepared statements. I guess I should start looking into that now.
This isn't true...
"While the queries will run and return asynchronously, we can still call the .end() synchronously. The connection won't be terminated until the queued queries return with a result."
...if there hasn't yet been time to get any queries into the queue.
It's very possible to have the end called before your first query is executed, therefore, like in your pooling example, the connection is closed before any query occurs.
This is really obvious when you're connecting to a DB over a slow connection.
It's better to always write your code with async/promises .