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

Connection Pools with Promise API

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

The createPool(options) function does not return a Promise, and therefore must be wrapped in a new Promise object if returned directly from an async function.

The createPool(options) options include the options from the createConnection(options) function, which are discussed in Code Example: Connect.

The createPool(options) function returns a Pool object. The Pool API provides several 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.

With Promise API, pool clusters can be used. The Promise API provides the following function to create a pool cluster:

The createPoolCluster(options) → PoolCluster function does not return a Promise, and therefore must be wrapped in a new Promise object if its return value is returned directly from an async function.

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 Promise API supports the following load balancing selectors:

Code Example: Connection Pools

The following example shows how to use connection pools with the example table created in Setup for Examples.

// Required Modules
const mariadb = require("mariadb");

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

console.log("Total connections: ", pool.totalConnections());
console.log("Active connections: ", pool.activeConnections());
console.log("Idle connections: ", pool.idleConnections());

async function main() {
   let conn;

   try {
      conn = await fetchConn();

      // Use Connection
      var rows = await get_contacts(conn);
      for (i = 0, len = rows.length; i < len; i++) {
         console.log("Total connections: ", pool.totalConnections());
         console.log("Active connections: ", pool.activeConnections());
         console.log("Idle connections: ", pool.idleConnections());

         console.log(`${rows[i].first_name} ${rows[i].last_name} <${rows[i].email}>`);
      }
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.end();
   }
}

// Fetch Connection
async function fetchConn() {
   let conn = await pool.getConnection();
   console.log("Total connections: ", pool.totalConnections());
   console.log("Active connections: ", pool.activeConnections());
   console.log("Idle connections: ", pool.idleConnections());
   return conn;
}

//Get list of contacts
async function get_contacts(conn) {
   return await conn.query("SELECT first_name, last_name, email FROM test.contacts");
}
main();

• Use the async keyword to declare asynchronous functions.

• Use the await keyword with the function calls to asynchronous functions.

• Add an async function fetchConn() to fetch a connection from the connection pool. Use a try-catch-finally statement in the function.

• Declare a constant using the const keyword called pool to create a new connection pool. Initialize a connection pool using the createPool(options) function in the mariadb module.

• The host option sets the host as localhost.

• The user option sets the user name.

• The password option sets the password for thee user.

• The database option sets the database name to test.

• The connectionLimit option sets the maximum number of connections in the pool. The default value is 10.

• The minimumIdle option sets a minimum number of connections in the pool. By default it is the same as the connectionLimit option value.

• Set connectionLimit option value to the number of connections that are estimated to be needed. Setting it too high creates an abundance of idle connections.

• The idleTimeout option sets the idle time (seconds) after which a connection is released. Default value is 1800 seconds (30 minutes).

• The createPool(options) function returns a Pool object.

• Because the createPool(options) function does not return a Promise object, if the function is to be called asynchronously, function call createPool(options) should be wrapped in a new Promise object.

• The Pool#getConnection() function is used to get a connection from the connection pool.

• The Pool#getConnection() function returns a Promise object.

• If fetchConn() is unable to obtain a connection from the connection pool, it creates a new connection using the mariadb.createConnection(options) function as fallback in the catch block and returns that instead.

• Add an async function get_contacts(conn) to run a SQL query with the query() function to get data for contacts using a SELECTSELECT statement.

• Loop over the contacts data returned using a for loop, and print the first_name, last_name, and email in each row of data.

• The total number of connections in the connection pool, the number of idle connections in the connection pool, and the number of active connections in the connection pool are logged to the console using functions pool.totalConnections(), pool.activeConnections(), and pool.idleConnections() respectively.

• These functions may be called at various stages of a connection pool use, such as just after a new connection pool is initialized, when a new connection is obtained from the pool, when a connection is passed as an argument to another async function, and when data retrieved by a query is looped through and logged to the console.

• These connection counts could vary throughout the use of a connection pool.

• Idle connections getting removed from a connection pool after the default timeout of 1800 seconds could greatly reduce the number of available connections.

• In the script output, note the varying number of total, active, and idle connections in the connection pool.

Example output:

Total connections:  0
Active connections:  0
Idle connections:  0
Total connections:  1
Active connections:  1
Idle connections:  0
Total connections:  1
Active connections:  1
Idle connections:  0
SQL statement completed
Total connections:  100
Active connections:  1
Idle connections:  99
John Smith <john.smith@example.com>
Total connections:  100
Active connections:  1
Idle connections:  99
Jon Smith <jon.smith@example.com>
Total connections:  100
Active connections:  1
Idle connections:  99
Johnny Smith <johnny.smith@example.com>

Code Example: Load Balancing

The following example shows how to use a pool cluster with the example table created in Setup for Examples. The example assumes that the example table, and example data exists on all cluster nodes.

const mariadb = require("mariadb");

// Initialize Pool Cluster
const clust = mariadb.createPoolCluster();

// Create Connection Pool to server1 Server

clust.add("server1", {
   host: "192.0.2.1",
   user: "db_user",
   password: "db_user_password",
   database: "test",
   connectionLimit: 5,
});

// Create Connection Pools to server2  Server
clust.add("server2", {
   host: "192.0.2.2",
   user: "db_user",
   password: "db_user_password",
   database: "test",
   connectionLimit: 5,
});

// Create Connection Pools to server3  Server
clust.add("server3", {
   host: "192.0.2.3",
   user: "db_user",
   password: "db_user_password",
   database: "test",
   connectionLimit: 5,
});

async function main() {
   try {
      var sql = "SELECT first_name, last_name, email FROM test.contacts";
      var rows = await queryData(sql);
      for (i = 0, len = rows.length; i < len; i++) {
         console.log(`${rows[i].first_name} ${rows[i].last_name} <${rows[i].email}>`);
      }

      // Manage Errors
   } catch (err) {
      console.log(err);
   } finally {
      // Close Connection
   }
}
async function queryData(sql) {
   let conn;
   var rows;
   try {
      conn = await clust.getConnection(/^server*$/, "RR");
      return conn.query(sql);
  } catch (err) {
      console.error("Error Processing Query: ", err);
  } finally {
      if (conn) conn.end();
  }
}

main();

• Use the async keyword to declare asynchronous functions.

• Use the await keyword with the function calls to asynchronous functions.

• Declare a constant called clust with the const keyword to initialize a pool cluster using the createPoolCluster(options) base function.

• The pool cluster is usable only after adding pools to the cluster.

• Once you have created a pool cluster, use the add() method to add MariaDB servers to the cluster, providing them with an arbitrary name and a JSON object containing the connection configuration.

• The function syntax is poolCluster.add(id, config). The id is the node identifier string. The config is the JSON for the pool options. These options are discussed in Connection Pools.

• For host the host name, public IPv4 address, or the public DNS may be used.

• Obtain a connection from the pool cluster using function poolCluster.getConnection(pattern, selector).

• The pool selector 'RR' is used.

• The pattern to use is server*, which selects any server with name starting with server in a round-robin.

• Load balancing is provided by using a Regular Expression to indicate a group of servers from which the Connector selects one.

• To retrieve a connection from a specific server from the pool cluster, use the getConnection() function, providing the server name you want to reach.

• Send a SQL query to the database using the query() function.

• Close the connection with the connection.end() function to free up resources.

Example output:

John Smith <john.smith@example.com>
Jon Smith <jon.smith@example.com>
Johnny Smith <johnny.smith@example.com>