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

Connection Pools with Callback API

With Callback API, connection pools can be used. The Callback API provides the following functions in the Connection object to create a connection pool:

The Pool object supports a callback function in the following functions:

Load Balancing

In addition to connection pools, MariaDB Connector/Node.js also supports pool clusters. A prerequisite is a cluster of nodes running MariaDB. Where a connection pool is a set of connections maintained to an individual server, in a pool clusters the Connector maintains a set of connection pools to different servers.

Pool clusters are useful in cases of large deployments in which your application needs to connect to different servers for different purposes or when you want to perform load balancing at an application level. The benefits of pool cluster are high availability, and load distribution.

Pool clusters provide connection pooling across the different servers in the cluster. Pool clusters do not provide data replication to the MariaDB servers in the cluster. For replication, use a MariaDB ReplicationMariaDB Replication deployment.

Pool clusters can be used with Callback API. The Callback API provides the following function to create a pool cluster:

The createPoolCluster(options) function supports all of the options supported by the Pool API. Further, the Pool API options include the options from the createConnection(options) function, which are discussed in Connect.

The createPoolCluster(options) function supports the following additional options:

MariaDB Connector/Node.js Callback API supports the following load balancing selectors:

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.