Node.js - MySQL Connection Pool

In the post Node.js - Connect to MySQL Promise API we have seen how to connect to MySQL using Promise based API of mysql2 package. Using mysql2 package you can also create a connection pool.

By using a database connection pool, you can create a pool of connections that can be reused, that helps in reducing the time spent connecting to the MySQL server. When you need to connect to DB you take a connection from the pool and that connection is released again to the pool, rather than closing it, when you are done.

Creating MySQl connection pool

You can create a connection pool by using createPool() method.

import mysql from 'mysql2/promise';
const pool = mysql.createPool({
  host: 'localhost',
  user: 'USER_NAME',
  password: 'PASSWORD',
  database: 'node', 
  port: 3306
});

Replace USER_NAME and PASSWORD with your MySQL configured user name and password. Port number used is the default 3306 (if you are using default port even port number is optional) and it is connecting to DB named node which I have created in MySQL.

There are other settings also for which default is used if no value is provided.

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
  idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
});

MYSQL2 Connection Pool with Promise Based API Example

Here is a complete example where connection pool is used to get a connection and execute queries.

Table used for the example-

CREATE TABLE `node`.`employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `join_date` DATE NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

util\database.js

This is the file where connection pool is created and pool object is exported so that it can be used in other files.

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'admin',
  database: 'node',
  waitForConnections: true, // this is default anyway
  connectionLimit: 10, // this is default anyway
});

module.exports = pool;

The pool does not create all connections upfront but creates them on demand until the connection limit is reached.

app.js

const pool = require('./util/database');

async function insertEmployee(empName, joinDate, age){
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values (?, ?, ?)";
  const values = [empName, joinDate, age];
  try{
    const conn = await pool.getConnection();
    const [result, fields] = await conn.execute(sql, values);
    console.log(result);
    console.log(fields);
    conn.release();
  }catch(err){
    console.log(err);
  }
}

insertEmployee('Rajesh', '2023-06-17', 34);

Important points to note here-

insertEmployee() function is a async function as we are using async/await (Promise based API) rather than callback based API.
We are using await with pool.getConnection() method.
By using array destructuring we get the returned values for result and fields.
fields variable contains extra meta data about results, if available.
result contains a ResultSetHeader object, which provides details about the operation executed by the server.
After the task, connection is released using conn.release() which means connection goes back to the pool.

On running the file-

>node app.js

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 1,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

undefined

In the above example connection is acquired manually from the pool and manually returned to the pool.

You can also use pool.query() and pool.execute() methods directly. When using these methods connection is automatically released when query resolves.

Here is one more function getEmployees() where pool.query() is used.

async function getEmployees(){
  const sql = "SELECT * FROM EMPLOYEE";
  try{
    const [result, fields] = await pool.query(sql);
    console.log(result);
  }catch(err){
    console.log(err);
  }
}

getEmployees();

On running the file app.js with getEmployee() method-

>node app.js
[
  {
    id: 1,
    name: 'Rajesh',
    join_date: 2023-06-16T18:30:00.000Z,
    age: 34
  }
]

That's all for this topic Node.js - MySQL Connection Pool. If you have any doubt or any suggestions to make please drop a comment. Thanks!

Related Topics

You may also like-

Tech Tutorials

Thursday, July 4, 2024