@megaorm/pool NPM

MegaORM Pool

This package is a connection manager designed to optimize database connection handling. Rather than creating and closing a new connection for each request, which can slow down performance under heavy loads, MegaPool ensures efficient management of connections.

Installation

To install this package, run the following command:

npm install @megaorm/pool

What is MegaPool

MegaPool is a connection manager that optimizes the process of creating, reusing, and closing database connections. When your application handles a high number of requests per second (e.g., 1000), repeatedly opening and closing connections is inefficient. MegaPool solves this problem by maintaining a pool of connections, which can be reused for multiple requests.

Instead of opening a new connection for every request, MegaPool creates connections when needed and stores them for future use. If a connection is not used for a specific amount of time, it is closed automatically.

How MegaPool Works

MegaPool operates with two types of connections:

Acquired connections: Connections that are currently in use.
Idle connections: Connections that are available for use.

When you need a connection, MegaPool checks if there are any idle connections available. If so, it provides one. If there are no idle connections, MegaPool automatically creates a new one. When you’re done with a connection, you release it back to the pool for future use. This eliminates the overhead of creating and closing connections for every request.

How to Use MegaPool

Create a Driver Instance

Start by creating an instance of the database driver (e.g., MySQL) with your connection configuration:

// Import MySQL driver
const { MySQL } = require('@megaorm/mysql');

// Creating a MySQL driver instance
const driver = new MySQL({
  host: 'localhost',
  user: 'root',
  password: 'root',
  db: 'test',
});

Create a Pool Instance

Next, create a pool instance using your driver:

// Import MegaPool
const { MegaPool } = require('@megaorm/pool');

// Creating a MegaPool instance using a MySQL driver
const pool = new MegaPool(driver);

Requesting a Connection

To request a connection from the pool, use the request method:

pool
  .request() // Request a connection
  .then((con) => con.query('SELECT * FROM users;')) // Execute an SQL query
  .then((result) => console.log(result)); // Log the result

Alternatively, using async/await syntax:

const con = await pool.request(); // Request a connection
const result = await con.query('SELECT * FROM users'); // Execute an SQL query
console.log(result); // Log the result

If you just want to execute a single query, you can do this:

await pool
  .query('SELECT * FROM users') // Execute an SQL query
  .then((result) => console.log(result)); // Log the result

Shutting Down the Pool

If you no longer need the pool, you can shut it down. This will close all connections and release the resources:

pool
  .shutdown()
  .then(() => console.log('Pool has been shut down successfully.'))
  .catch((error) => console.log(error));

After shutting down, any further attempts to use the pool will result in a rejection:

// This will reject because the pool has been shut down
await pool.request();

MegaPool Config

You can configure MegaPool with a set of options to control how connections are managed. Here’s an explanation of each option along with a practical example of how to use them.

maxConnection: Defines the maximum number of connections that can be created in the pool.
- Defaul value is 10.

// Create a MegaPool instance with a max of 5 connections
const pool = new MegaPool(driver, { maxConnection: 5 });

This configuration ensures that no more than 5 connections are created at any given time.

maxIdleTime: Sets the lifetime (in milliseconds) for idle connections. If a connection remains idle for longer than this time, it will be closed automatically.
- Defaul value is 60000 (1 minute).

// Create a MegaPool instance where idle connections will be closed after 2 minutes
const pool = new MegaPool(driver, { maxIdleTime: 120000 });

This means that if a connection is idle for 2 minutes, it will be closed to free up resources.

shouldQueue: Determines whether connection requests should be queued when there are no idle connections available. If set to true, the request will be placed in a queue until an idle connection is available.
- Default value is true.

// Create a MegaPool instance with request queuing disabled
const pool = new MegaPool(driver, { shouldQueue: false });

With shouldQueue: false, if there are no idle connections, the request will fail immediately.

maxQueueSize: Limits the number of connection requests that can be queued when there are no idle connections available.
- Default value is Infinity.

// Create a MegaPool instance with a queue size limit of 5
const pool = new MegaPool(driver, { maxQueueSize: 5 });

This limits the queue to 5 requests. If there are more than 5 requests while the pool is exhausted, the extra requests will be rejected.

maxQueueTime: Sets the maximum amount of time (in milliseconds) a request can remain in the queue before it is rejected.
- Default value is 1000 (1 second).

// Create a MegaPool instance where queued requests will be rejected after 2 seconds
const pool = new MegaPool(driver, { maxQueueTime: 2000 });

With this setting, any request that has been in the queue for longer than 2 seconds will be rejected.

shouldRetry: Determines whether failed connection creation or closure attempts should be retried.
- Default value is true.

// Create a MegaPool instance that disables retrying on connection failure
const pool = new MegaPool(driver, { shouldRetry: false });

Setting this to false means that if a connection creation or closure fails, it will not be retried.

maxRetry: Specifies the maximum number of retry attempts to make when connection creation or closure fails.
- Default value is 3.

// Create a MegaPool instance that will retry up to 5 times on failure
const pool = new MegaPool(driver, { maxRetry: 5 });

This ensures that if connection creation fails, the system will try up to 5 times before giving up.

retryDelay: Defines the delay (in milliseconds) between retry attempts when a failure occurs.
- Default value is 500 (500 ms).

// Create a MegaPool instance that waits 1 second between retries
const pool = new MegaPool(driver, { retryDelay: 1000 });

This sets a 1-second delay between retry attempts when a failure happens.

extraDelay: Adds extra time (in milliseconds) between retry attempts. This increases the delay incrementally with each retry.
- Default value is 500 (500 ms).

// Create a MegaPool instance with additional delay between retries
const pool = new MegaPool(driver, { extraDelay: 1000 });

This ensures that the delay between retries increases by an additional second for each retry attempt.

Here’s how you can configure a MegaPool with all the options set:

const pool = new MegaPool(driver, {
  maxConnection: 15, // Max number of connections in the pool
  maxIdleTime: 180000, // Idle connections will be closed after 3 minutes
  shouldQueue: true, // Queue requests when no idle connections are available (15 acquired)
  maxQueueSize: 10, // Max 10 requests can be queued
  maxQueueTime: 3000, // Requests will be rejected if they stay in the queue for more than 3 seconds
  shouldRetry: true, // Retry failed connection creation/closure
  maxRetry: 5, // Retry up to 5 times on failure
  retryDelay: 1000, // Wait 1 second between retries
  extraDelay: 500, // Add an additional 500ms delay after each retry
});

In this example, you have customized all the connection pool options, which would suit a high-traffic environment where connection reuse is critical but failure resilience and queue handling are also important.

MegaPool Events

In MegaORM Pool, several events are emitted during the execution of various operations such as querying, connection management, transaction handling, and pool shutdown. Developers can listen to these events to handle success or failure scenarios.

Below are examples of how to handle these events:

const { QUERY_FAIL, QUERY_SUCCESS } = require('@megaorm/pool');

// Handle when a query fails
pool.on(QUERY_FAIL, (error) => {
  console.log('Query execution failed:', error);
});

// Handle when a query succeeds
pool.on(QUERY_SUCCESS, (result) => {
  console.log('Query executed successfully:', result);
});

const { CREATE_FAIL, CREATE_SUCCESS } = require('@megaorm/pool');

// Handle when a connection creation fails
pool.on(CREATE_FAIL, (error) => {
  console.log('Connection creation failed:', error);
});

// Handle when a connection creation succeeds
pool.on(CREATE_SUCCESS, (connection) => {
  console.log('Connection created successfully:', connection);
});

const { CLOSE_FAIL, CLOSE_SUCCESS } = require('@megaorm/pool');

// Handle when closing a connection fails
pool.on(CLOSE_FAIL, (connection, error) => {
  console.log('Connection closing failed for', connection, 'Error:', error);
});

// Handle when closing a connection succeeds
pool.on(CLOSE_SUCCESS, () => {
  console.log('Connection closed successfully');
});

You must handle CLOSE_FAIL event and store MegaPendingConnection for later closing attempts. This is automatically managed in @megaorm/cluster.

const { TRANSACTION_FAIL, TRANSACTION_SUCCESS } = require('@megaorm/pool');

// Handle when a transaction fails
pool.on(TRANSACTION_FAIL, (error) => {
  console.log('Transaction failed:', error);
});

// Handle when a transaction succeeds
pool.on(TRANSACTION_SUCCESS, () => {
  console.log('Transaction started successfully');
});

const { COMMIT_FAIL, COMMIT_SUCCESS } = require('@megaorm/pool');

// Handle when committing a transaction fails
pool.on(COMMIT_FAIL, (error) => {
  console.log('Transaction commit failed:', error);
});

// Handle when committing a transaction succeeds
pool.on(COMMIT_SUCCESS, () => {
  console.log('Transaction committed successfully');
});

const { ROLLBACK_FAIL, ROLLBACK_SUCCESS } = require('@megaorm/pool');

// Handle when rolling back a transaction fails
pool.on(ROLLBACK_FAIL, (error) => {
  console.log('Transaction rollback failed:', error);
});

// Handle when rolling back a transaction succeeds
pool.on(ROLLBACK_SUCCESS, () => {
  console.log('Transaction rolled back successfully');
});

const { SHUTDOWN_FAIL, SHUTDOWN_SUCCESS } = require('@megaorm/pool');

// Handle when the shutdown process of the pool fails
pool.on(SHUTDOWN_FAIL, (error) => {
  console.log('Pool shutdown failed:', error);
});

// Handle when the shutdown process of the pool succeeds
pool.on(SHUTDOWN_SUCCESS, () => {
  console.log('Pool shut down successfully');
});

const { MAX_QUEUE_TIME } = require('@megaorm/pool');

// Handle when the maximum allowed queue time is reached
pool.on(MAX_QUEUE_TIME, (error) => {
  console.log('Max queue time reached:', error);
});

const { MAX_QUEUE_SIZE } = require('@megaorm/pool');

// Handle when the maximum allowed queue size is reached
pool.on(MAX_QUEUE_SIZE, (error) => {
  console.log('Max queue size reached:', error);
});

const { MAX_CONNECTION } = require('@megaorm/pool');

// Handle when the maximum number of allowed connections is reached
pool.on(MAX_CONNECTION, (error) => {
  console.log('Max connections reached:', error);
});

const { RELEASED } = require('@megaorm/pool');

// Handle when a connection is released back to the pool
pool.on(RELEASED, (connection) => {
  console.log('Connection released back to the pool:', connection);
});

MegaPool Getter

The MegaPool Getter provides an easy-to-use interface for accessing real-time information about the connection pool's state, configuration, and performance. This enables you to monitor and adjust the pool’s behavior effectively.

get.count.idle(): Returns the number of idle (unused) connections in the pool.

console.log(`Idle: ${pool.get.count.idle()}`);

get.count.acquired(): Returns the number of active (acquired) connections in the pool.

console.log(`Acquired: ${pool.get.count.acquired()}`);

get.count.request(): Returns the number of requests currently queued in the pool.

console.log(`Requests: ${pool.get.count.request()}`);

get.options(): Returns the current configuration options of the pool.

console.log('Options:', pool.get.options());

get.driver(): Returns the database driver used by the pool.

console.log('Driver:', pool.get.driver());

get.performance(): Evaluates the performance of the pool based on the number of active connections.
- GREAT_POOL: Less than 50% of maxConnection in use.
- GOOD_POOL: Around 50% of maxConnection in use.
- BAD_POOL: More than 80% of maxConnection in use.

const { GREAT_POOL, GOOD_POOL, BAD_POOL } = require('@megaorm/pool');

const performance = pool.get.performance();

if (performance === GREAT_POOL) console.log('GREAT!');
else if (performance === GOOD_POOL) console.log('GOOD!');
else if (performance === BAD_POOL) console.log('BAD!');

MegaPool Checker

The MegaPool Checker provides a quick way to verify specific conditions or states within the connection pool. These methods help ensure that the pool is operating as expected or to make decisions based on its current status.

has.acquired(): Checks if the pool has any active (acquired) connections.
- Returns: true if there are acquired connections, otherwise false.

if (pool.has.acquired()) console.log('The pool has acquired connections.');
else console.log('No connections are currently acquired.');

has.idle(): Checks if the pool has any idle (unused) connections available.
- Returns: true if there are idle connections, otherwise false.

if (pool.has.idle()) console.log('The pool has idle connections available.');
else console.log('No idle connections in the pool.');

has.request(): Checks if there are any connection requests queued in the pool.
- Returns: true if there are queued requests, otherwise false.

if (pool.has.request()) console.log('There are connection requests.');
else console.log('No connection requests are queued.');

Notes on MegaPool

MegaPool automatically manages connections by creating new ones when needed and closing idle ones after a set period.
Always release connections back to the pool using the release() method when you’re done with them. Once released, the connection cannot be reused.
After shutting down the pool, it becomes a dummy pool and can no longer process connection requests.
Before shutting down the pool, ensure all connections are released and there are no pending requests in the queue. Freeze the pool first to prevent new requests.
You can use @megaorm/cluster to handle pool freezing and shutdown or implement your own logic.
If you choose not to use @megaorm/cluster, handle the CLOSE_FAIL event and store MegaPendingConnection for retrying closure later.
The main difference between a driver-created connection (MegaConnection) and a pool-created connection (MegaPoolConnection) is the way they are closed:
- MegaConnection uses the close() method.
- MegaPoolConnection uses the release() method.
Both types of connections can execute queries and manage transactions.
A MegaPendingConnection is a connection that could not be closed by the pool for some reason (e.g., internet issues, server restarts). In this case, MegaPool emits a CLOSE_FAIL event and provides these connections for you to store somewhere and attempt to close them when you are free. You can implement this logic yourself or simply use @megaorm/cluster for that.