Using a MySQL database in your application naturally requires a connection to that database. For small-scale development or experimenting, simply creating a single connection may work fine. More extensive operations like production or complex queries, however, may require more flexibility. In order to see the difference between a single connection and a connection pool, let's start with a simple example of the former:
const express = require('express');
const app = express();
const mysql = require('mysql');
const db_connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'test'
});
db_connection.connect((err) => {
if (err) console.error(err);
console.log('MySQL Connection Established.');
});
const getUsers = () => new Promise((resolve, reject) => {
db_connection.query('SELECT * FROM USERS', (err, results) => {
if (err) console.error(err);
console.log('User Query Results: ', results);
resolve(results);
db_connection.end(err => { if (err) console.error(err) });
});
});
app.get('/', (req, res) => {
getUsers()
.then(users => res.send(users))
.catch(err => console.error(err));
});
app.listen(8080, () => {
console.log('Server listening on port 8080.');
});
This simple server will establish a database connection and query the database named "test" (previously created) for all rows from the table "users" (which was preloaded with one entry). After starting the server with node app.js, a Postman GET call to http://localhost:8080 sends back the expected response:
[
{
"id": 1,
"username": "gdup",
"location": "New Orleans"
}
]
And in the terminal:
Server listening on port 8080.
MySQL Connection Established.
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
BUT—a subsequent Postman call produces an empty response and kills the server with errors:
User Query Results: undefined
Error: Cannot enqueue Quit after invoking quit.
at Protocol._validateEnqueue (/Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/Protocol.js:215:16)
at Protocol._enqueue (/Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/Protocol.js:138:13)
at Protocol.quit (/Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/Protocol.js:91:23)
at Connection.end (/Users/Work/immersion/database-test/node_modules/mysql/lib/Connection.js:239:18)
at Query.<anonymous> (/Users/Work/immersion/database-test/app.js:22:19)
at Query.<anonymous> (/Users/Work/immersion/database-test/node_modules/mysql/lib/Connection.js:526:10)
at Query._callback (/Users/Work/immersion/database-test/node_modules/mysql/lib/Connection.js:488:16)
at Query.Sequence.end (/Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
at /Users/Work/immersion/database-test/node_modules/mysql/lib/protocol/Protocol.js:236:14
at processTicksAndRejections (internal/process/task_queues.js:79:11) {
code: 'PROTOCOL_ENQUEUE_AFTER_QUIT',
fatal: false
}
The problem here is the db_connection.end() part of our database connection. Once the connection is ended, it can't be restarted and used again. "Well why don't you just not end the connection?" you may be asking yourself. Well, that's all fine and good as long as nothing goes wrong with the single lifeline you have to the database. But beyond that, there are other reasons you want more flexibility. For one, you will likely eventually need to be able to have multiple connections for multiple simultaneous users. Likewise, you may eventually want to implement queries in which you need to have more than one connection. Keep in mind that all queries performed through a connection are sequential. If you have 10 queries that each take one second, the total execution time would be 10 seconds—unless they were run in parallel, of course.
Let's set up one more part of the demonstration by installing siege, which will allow us to blast our server with concurrent get requests. In a benchmark.js file, the following code will allow us to do just that by running node benchmark.js:
const siege = require('siege');
siege()
.on(8080)
.concurrent(10)
.get('/')
.attack();
Starting up the server and running node benchmark.js kills the server as expected and produces the same errors as Postman did above. Now let's refactor our connection:
const express = require('express');
const app = express();
const mysql = require('mysql');
const db_connection = mysql.createPool({
host: 'localhost',
user: 'root',
password: '',
database: 'test'
});
const getUsers = () => new Promise((resolve, reject) => {
db_connection.getConnection((err, connection) => {
if (err) console.error(err);
console.log('MySQL Connection Established: ', connection.threadId);
connection.query('SELECT * FROM USERS', (err, results) => {
if (err) console.error(err);
console.log('User Query Results: ', results);
resolve(results);
connection.release(err => { if (err) console.error(err) });
});
});
});
app.get('/', (req, res) => {
getUsers()
.then(users => res.send(users))
.catch(err => console.error(err));
});
app.listen(8080, () => {
console.log('Server listening on port 8080.');
});
Now, instead of createConnection, we've used createPool, which makes a pool of connections ready to be borrowed. When the time comes, we use getConnection with connection as a parameter of the callback function. That connection, the one the pool gives us, is the one we'll use for the query. Now, running the siege test produces several sets of query results without breaking the server. Here are a few:
Server listening on port 8080.
MySQL Connection Established: 124
MySQL Connection Established: 123
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established: 123
MySQL Connection Established: 124
MySQL Connection Established: 125
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established: 126
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
Notice how the console.log of the connection.threadId, which shows a distinct number for each connection. shows a range of connections used. We didn't actually specify how many to create, but default is 100. If we set the limit to 1 in our connection parameters:
const db_connection = mysql.createPool({
host: 'localhost',
user: 'root',
password: '',
database: 'test',
connectionLimit: 1,
});
...and run the siege attack again, we get the same single pool connection being used over and over:
Server listening on port 8080.
MySQL Connection Established: 131
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established: 131
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established: 131
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established: 131
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
MySQL Connection Established: 131
User Query Results: [ RowDataPacket { id: 1, username: 'gdup', location: 'New Orleans' } ]
Take note of the connection.release() at the end of the query. This is an important part of the equation in which the connection is given back to the pool to be reused—much like a library book is borrowed from a library and subsequently returned. And as before, all connections can be closed (simultaneously) with db_connection.end() if needed.
I hope this tutorial on MySQL/Node.js pool connections has been helpful. Pool connections can seem mysterious at first, and how they work "under the hood" is not immediately apparent. With the experiment above, however, I hope you've also gained some concrete understanding of their functionality and can now agree that implementing pool connections is not only easy, but also quite beneficial!
Top comments (0)