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:
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:
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
.
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
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
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
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
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
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 Replication
MariaDB 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:
canRetry
Whether pool cluster can try to get connection from other pool if getting connection from one pool fails.
boolean
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
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
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 <[email protected]>
Total connections: 100
Active connections: 1
Idle connections: 99
Jon Smith <[email protected]>
Total connections: 100
Active connections: 1
Idle connections: 99
Johnny Smith <[email protected]>
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:
