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

Overview

Node.js developers can connect to MariaDB database products using MariaDB Connector/Node.js to use connection pools with the 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 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:

Function

Return Type

Description

createPool(options) Pool

Pool

The base function to create a connection pool.

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.

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.

Pool clusters can be used with Callback API. The Callback 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) 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 Callback 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: 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.