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

Overview

Node.js developers can connect to MariaDB database products using MariaDB Connector/Node.js to use connection pools with the Promise API. Promise API is the default 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 a process is done with the connection, the connection is returned to the pool rather than being closed, allowing MariaDB Connector/Node.js to acquire a connection as needed. MariaDB Connector/Node.js also supports clustering pools, for when you want a group of connection pools to multiple end points.

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:

Function

Return Type

Description

createPool(options) Pool

Pool

The base function 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:

Function

Description

pool.getConnection() Promise

Returns a Promise object that resolves with a Connection object, or rejects with an Error object.

pool.query(sql[, values]) Promise

Gets a connection from the pool, runs a SQL statement, and returns the connection back to the pool. Returns a Promise object that resolves with a JSON object for update/insert/delete or a result-set object for a select query, or rejects with an Error object. The function parameters have the same meaning as in the connection.query(sql[, values]) and connection.batch(sql, values) functions.

pool.batch(sql, values) Promise

Gets a connection from the pool, runs a batch of SQL statements, and returns the connection to the pool. Returns a Promise object that resolves with a JSON object, or rejects with an Error. The function parameters have the same meaning as in the connection.query(sql[, values]) and connection.batch(sql, values) functions.

pool.end() Promise

Ends or closes the connection pool and the underlying connections after all running queries have completed. Returns a Promise object that resolves with no arguments, or rejects with an Error.

Options

The createPool(options) function supports the following pool-specific options:

Option

Description

Type

Default Value

acquireTimeout

Timeout to get a new connection from pool in ms. In order to have connection error information, acquireTimeout must be higher than connectTimeout

integer

10000

connectionLimit

Maximum number of connections in pool

integer

10

idleTimeout

Sets idle time after which a pool connection is released. Value must be lower than wait_timeout. In seconds (0 means never release).

integer

1800

minimumIdle

Permits setting a minimum number of connections in pool. We recommend using a fixed pool, and so not setting this value.
*: The default changes with the connectionLimit value.

integer

10*

minDelayValidation

When asking for a connection to pool, the pool will validate the connection state. To avoid unnecessary verifications in the case of frequent reuse of connections, "minDelayValidation" permits disabling this validation if the connection has been borrowed recently. 0 means validation is done each time the connection is asked. (in ms)

integer

500

noControlAfterUse

After giving a connection back to the pool (connection.end) connector will reset or rollback connection to ensure a valid state. This option permits disabling those controls.

boolean

false

resetAfterUse

When a connection is given back to pool, this option enables resetting the connection if the server allows it (only for MariaDB version >= 10.2.22 / 10.3.13). If disabled or server version doesn't allows reset, pool will only rollback open transaction if any.

boolean

true before 3.0, false since

leakDetectionTimeout

Sets a timeout to log connection borrowed from pool. When a connection is borrowed from pool and this timeout is reached, a message will be logged to console indicating a possible connection leak. Another message will tell if the possible logged leak has been released. A value of 0 disables leak detection.

integer

0

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:

Function

Return Type

Description

mariadb#createPoolCluster(options)

PoolCluster

The base 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:

Option

Description

Type

Default Value

canRetry

Whether pool cluster can try to get connection from other pool if getting connection from one pool fails.

boolean

true

removeNodeErrorCount

The error count for a single pool in obtaining a connection after which the pool is removed from the pool cluster. Set to null if pool should never be removed.

integer

5 before 3.0, infinity since

restoreNodeTimeout

Timeout in milliseconds after which a pool is retried for a connection after a connection fails. The default value is 1000.

integer

1000

defaultSelector

Specifies the default pool selector. A pool selector strategy is used in selecting which pool in the cluster to try a connection. 'RR' is for a round-robin selector. 'RANDOM' is to select randomly. 'ORDER' is to select in sequence or order in which added to the pool cluster.

string

'RR'

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

Load Balancing

Selector

Description

Order

ORDER

Servers selected in the order in which they are listed.

Random

RANDOM

Servers selected at random.

Round-robin

RR

Servers selected using round-robin.

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>