Node.js and MySQL Connection Pool Example

Node and MySQL are a go-to combination for a quick prototype build and in various cases for production build as well. In this tutorial, we will learn how to connect Node with MySQL and how to pool the connections for better performance and optimization of resources.

You need Node and MySQL installed in your system before proceeding towards the tutorial. Click here to read a comprehensive guide on the installation and setup of Node.js. And if you want to know how to run and connect to a MySQL server, check out: NodeJS Create MySQL Connection.

Node MySQL Connection

To begin with, create a new folder and initialize a new Node.js project using the command shown below.

npm init --y

NPM stands for Node Package Manager used to manage and install modules in Node.js.

Now, install the MySQL module.

npm install --save mysql

MySQL Module is used to make connect and interact with the MySQL database.

Copy and paste the code below into a file and name it app.js.

const mysql = require('mysql');

const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'username',
  password : 'password',
  database : 'databasename'
});

connection.connect((err) => {
    if(err) throw err;
    console.log('Connected to MySQL Server!');
});

Code Explanation:

The first line here will import the mysql module, then the mysql.createConnection() will create an object for making connection to the MySQL local server, make sure you adjust the configuration accordingly. Then connection.connect() will establish the connection to the server having the specided configuration and check for errors otherwise print the defined message to the console.

Now, run the code and see the output on the terminal.

node app.js

Output:

You should see the following message in the terminal.

Connected to MySQL Server!

The code successfully made a connection to the MySQL server. We can now perform any database operations we like. This code is not enough though, we need to implement the connection pool to achieve our goal.

What is Connection Pooling?

In a nutshell, the Connection pool is similar to a cache where we store frequently accessed data. Here the data is a database connection. The goal is to achieve the reusability of the database connections instead of creating a new connection every time there is a demand for the data.

Node MySQL Connection Pool Example

Here is the code with MySQL connection pool implementation.

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit : 100, //important
    host     : 'localhost',
    user     : 'yourmysqlusername',
    password : 'yourmysqlpasword',
    database : 'yourdbname',
    debug    :  false
});

pool.query("SELECT * FROM TABLE_NAME",(err, data) => {
    if(err) {
        console.error(err);
        return;
    }
    // rows fetch
    console.log(data);
});

Code Explanation:

In the above code, we have used a method createPool() which creates a connection pool, we have configured an additional property connectionLimit which is the set maximum number of connections allowed. We have also used a query() method using the pool object to execute a SQL query to select all records from a table, then in the callback we have an error check, which will stop the execution if any error occurs, else the print statement will execute that will print the selected data to the console.

Now run the code and observe the output. It should return all the entries of the table. Kindly change the details in the code with your own MySQL configuration.

Conclusion

That’s all for this tuitorial, we have seen the process for connecting to the MySQL database and then pooling connections. You should definitely implement this while building a Node.js application.

If you want to learn more about the select command, consider reading the tutorial NodeJS MySQL Select Record, and if you want to know how to create a MySQL database using Node.js, consider the tutorial NodeJS MySQL Create Database. Hope you have enjoyed reading the content.