Connection Pools with MariaDB Connector/Node.js (Callback API)

Connection pools enable the reuse of database connections to minimize the performance overhead of connecting to the database and the churn of opening and closing connections.

Connection pools hold connections open in a pool. When the process is done with the connection, it is returned to the pool rather than closed, allowing MariaDB Connector/Node.js to acquire a connection as needed.

Connection Pools

createPool(options) -> Pool is the base function to create a Pool object for a connection pool.

The createPool(options) function supports the same options as the Promise API, and returns a Pool object. The Pool object supports a callback function in the following functions:

Function

Description

pool.getConnection(callback)

Obtains a connection as Connection object. The callback function is defined with two arguments as (error, conn). The first argument is for the Error object if an error condition occurs and the second argument is for a Connection object.

pool.query(sql[, values][, callback])

Used to run a single SQL statement or prepared statement. Function parameters have the same meaning as for the connection.query(sql[, values][, callback]) function.

pool.batch(sql, values[, callback])

Used to run a batch of SQL statements. Function parameters have the same meaning as for the connection.batch(sql[, values][, callback]) function.

pool.end([callback])

Closes the pool and the underlying connections after all running queries have completed. The callback function is defined with a single argument for the Error object if an error condition occurs in closing the pool.

Code Example: Create Connection Pool

The following code example initializes a connection pool:

const mariadb = require("mariadb/callback");

// Initialize Pool
const pool = mariadb.createPool({
     host: "192.0.2.50",
     user: "db_user",
     password: "db_user_password",
     database: "test",
     connectionLimit: 100,
 });

 function main() {
    try {
       pool.getConnection((err, conn) => {
          if (err) {
             console.log("Error in obtaining connection: " + err);
          } else {
             console.log("Connected. Connection id is " + conn.threadId);
             conn.query(
                "SELECT first_name, last_name, email from test.contacts",
                (err,res,meta) => {
                   if (err) {
                      console.error("Error querying data: ", err);
                   } else {
                      console.log(res);
                      console.log(meta);
                   }
                }
             );
             // release connection to pool
             conn.end(err => {if(err){console.error("Error releasing connection to pool   : ", err);}});
           }
        });

     } catch (err) {

        // Manage Errors
        console.log(err);

     } finally {

        // Return Connection to the Pool
        if (conn) conn.end(err => {
           if(err) {
              console.log("SQL error in closing connection: ", err);
           }
        })
     }
   }

   main();
  • A try...catch..finally statement is used for exception handling and closing the connection.

  • MariaDB Connector/Node.js selects data from the database with SELECT statement.

  • The callback function in the query() function call is (err,res,meta) => {}, which is supplied with three arguments err, res, and meta for the Error object (if thrown), result, and metadata respectively.

  • If an error condition is encountered the callback function outputs the error message to the console.

  • The callback function outputs the result to the console.

  • The callback function outputs the metadata fields to the console.

  • Connection is release to pool with conn.end(err => {}) function call.

The output from the script lists the query result set as JSON object. Also output is the metadata.

Connected. Connection id is 132
[
   {
      first_name: 'Johnny',
      last_name: 'Smith',
      email: 'johnny.smith@example.com'
   },
   {
      first_name: 'Johnny',
      last_name: 'Smith',
      email: 'johnny.smith@example.com'
   },
   {
      first_name: 'John',
      last_name: 'Smith',
      email: 'johnsmith@example.com'
   },
   {
      first_name: 'John',
      last_name: 'Smith',
      email: 'johnsmith@example.com'
   }
]
[
  //metadata
  //...
]

New connections may fail to get created with the following message if the connection pool cannot create any connections due the server reaching the limit.

pool fail to create connection ((conn=241, no: 1040, SQLState: 08004) Too many connections)

Increase the value on the max_connections system variable to raise this limit to allow more connections to the server.

Code Example: Close Connection

When you are done with a connection retrieved from the pool, close it using the end() function:

// Return Connection to the Pool
 if (conn) conn.end(err => {
    if(err) {
       console.log("SQL error in closing connection: ", err);
    }
 })

Connections retrieved from connection pools are returned to the pool when closed. The pool keeps a certain pre-configured number of connections available for use. When the application calls the getConnection() method again, the connection is given back to the application.