Connection Pools

interface Pool {
  // Get connection from pool
  getConnection(options?: GetConnectionOptions): Promise<Connection>;
  
  // Close pool and all connections
  close(drainTime?: number): Promise<void>;
  
  // Reconfigure pool settings
  reconfigure(options: PoolAttributes): Promise<void>;
  
  // Set access token for cloud authentication
  setAccessToken(options: AccessTokenOptions): Promise<void>;
  
  // Pool statistics (read-only properties)
  connectionsInUse: number;
  connectionsOpen: number;
  poolAlias: string;
  poolIncrement: number;
  poolMax: number;
  poolMaxPerShard: number;
  poolMin: number;
  poolPingInterval: number;
  poolPingTimeout: number;
  poolTimeout: number;
  queueMax: number;
  queueTimeout: number;
  sodaMetaDataCache: boolean;
  status: number;
  stmtCacheSize: number;
  thin: boolean;
}

interface GetConnectionOptions {
  tag?: string;
  shardingKey?: (string | number | Date)[];
  superShardingKey?: (string | number | Date)[];
}

interface AccessTokenOptions {
  token: string;
  privateKey?: string;
}

const oracledb = require('oracledb');

// Create a connection pool
const pool = await oracledb.createPool({
  user: "hr",
  password: "welcome123",
  connectString: "localhost:1521/XE",
  poolMin: 5,
  poolMax: 20,
  poolIncrement: 2,
  poolTimeout: 300,
  poolAlias: "hrpool"
});

// Get connection from pool
const connection = await pool.getConnection();

// Use connection
const result = await connection.execute('SELECT * FROM employees');

// Return connection to pool (important!)
await connection.close();

// Check pool statistics
console.log('Connections in use:', pool.connectionsInUse);
console.log('Connections open:', pool.connectionsOpen);

// Close the pool when done
await pool.close(10); // 10 second drain time

interface PoolAttributes extends ConnectionAttributes {
  poolAlias?: string;           // Pool identifier
  poolIncrement?: number;       // Connections to create when pool needs more
  poolMax?: number;            // Maximum pool connections
  poolMaxPerShard?: number;    // Maximum connections per shard
  poolMin?: number;            // Minimum pool connections
  poolPingInterval?: number;   // Seconds between connection health checks
  poolPingTimeout?: number;    // Timeout for ping operations
  poolTimeout?: number;        // Idle connection timeout
  queueMax?: number;          // Maximum queued getConnection requests
  queueTimeout?: number;       // Timeout for queued requests
  sessionCallback?: string | SessionCallback; // Session fixup callback
  sodaMetaDataCache?: boolean; // Enable SODA metadata caching
  enableStatistics?: boolean;  // Enable pool statistics
}

type SessionCallback = (
  connection: Connection,
  requestedTag: string,
  callbackFn: (error?: Error, connection?: Connection) => void
) => void;

// Comprehensive pool configuration
const pool = await oracledb.createPool({
  // Connection parameters
  user: "app_user",
  password: "app_password",
  connectString: "mydb.example.com:1521/XEPDB1",
  
  // Pool sizing
  poolMin: 10,          // Always keep 10 connections open
  poolMax: 50,          // Maximum 50 connections
  poolIncrement: 5,     // Create 5 connections at a time when needed
  
  // Timeouts
  poolTimeout: 600,     // Close idle connections after 10 minutes
  queueTimeout: 5000,   // Wait max 5 seconds for connection
  poolPingInterval: 60, // Check connection health every minute
  
  // Queue management
  queueMax: 100,        // Maximum 100 waiting requests
  
  // Session management
  sessionCallback: (connection, requestedTag, cb) => {
    // Custom session setup
    connection.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'")
      .then(() => cb(null, connection))
      .catch(cb);
  },
  
  poolAlias: "mainpool"
});

/**
 * Reconfigures pool settings
 * @param options - New pool configuration options
 * @returns Promise that resolves when reconfiguration is complete
 */
reconfigure(options: PoolAttributes): Promise<void>;

// Initially create pool
const pool = await oracledb.createPool({
  user: "hr",
  password: "welcome123",
  connectString: "localhost:1521/XE",
  poolMin: 5,
  poolMax: 10
});

// Later, reconfigure to handle more load
await pool.reconfigure({
  poolMin: 10,
  poolMax: 30,
  poolIncrement: 5
});

console.log('Pool reconfigured - new max:', pool.poolMax);

/**
 * Gets a connection with optional tag for session state reuse
 * @param options - Connection options including tag
 * @returns Promise resolving to tagged Connection
 */
getConnection(options: GetConnectionOptions): Promise<Connection>;

interface GetConnectionOptions {
  tag?: string;                              // Session tag for reuse
  shardingKey?: (string | number | Date)[];  // Sharding key
  superShardingKey?: (string | number | Date)[]; // Super sharding key
}

// Get connection with specific tag
const connection1 = await pool.getConnection({
  tag: "reporting"
});

// Set up session state
await connection1.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'");
connection1.tag = "reporting"; // Tag the connection

// Return to pool
await connection1.close();

// Later, request connection with same tag (may reuse session state)
const connection2 = await pool.getConnection({
  tag: "reporting"
});

// Session state may already be configured
console.log('Connection tag:', connection2.tag);

interface PoolStatistics {
  // Connection counts
  connectionsInUse: number;     // Currently active connections
  connectionsOpen: number;      // Total open connections
  
  // Pool status
  status: number;              // Pool status (OPEN, DRAINING, CLOSED, etc.)
  
  // Configuration (read-only)
  poolMin: number;
  poolMax: number;
  poolIncrement: number;
  poolTimeout: number;
  queueMax: number;
  queueTimeout: number;
}

// Pool status constants
const POOL_STATUS_OPEN = 6000;
const POOL_STATUS_DRAINING = 6001;
const POOL_STATUS_CLOSED = 6002;
const POOL_STATUS_RECONFIGURING = 6003;

// Monitor pool health
function logPoolStats(pool) {
  console.log(`Pool Status: ${pool.status}`);
  console.log(`Connections: ${pool.connectionsInUse}/${pool.connectionsOpen} (in use/open)`);
  console.log(`Pool limits: ${pool.poolMin}-${pool.poolMax}`);
  
  if (pool.connectionsInUse === pool.poolMax) {
    console.warn('Pool at maximum capacity!');
  }
}

// Periodic monitoring
setInterval(() => {
  logPoolStats(pool);
}, 30000); // Every 30 seconds

// Check if pool needs reconfiguration
if (pool.connectionsInUse / pool.poolMax > 0.8) {
  console.log('Consider increasing pool size');
}

/**
 * Closes the pool and all connections
 * @param drainTime - Time in seconds to wait for connections to be returned
 * @returns Promise that resolves when pool is closed
 */
close(drainTime?: number): Promise<void>;

// Graceful shutdown
async function shutdown() {
  console.log('Shutting down application...');
  
  // Stop accepting new requests
  server.close();
  
  // Close pool with drain time
  try {
    await pool.close(30); // Wait up to 30 seconds
    console.log('Pool closed successfully');
  } catch (error) {
    console.error('Error closing pool:', error);
  }
  
  process.exit(0);
}

// Handle shutdown signals
process.on('SIGINT', shutdown);
process.on('SIGTERM', shutdown);

// Emergency shutdown (no drain time)
process.on('SIGKILL', async () => {
  await pool.close(0);
  process.exit(1);
});

/**
 * Gets an existing pool by alias
 * @param alias - Pool alias (defaults to 'default')
 * @returns Pool instance
 */
function getPool(alias?: string): Pool;

// Create multiple pools
const hrPool = await oracledb.createPool({
  user: "hr",
  password: "hr_password",
  connectString: "localhost:1521/XE",
  poolAlias: "hr"
});

const financePool = await oracledb.createPool({
  user: "finance",
  password: "finance_password", 
  connectString: "localhost:1521/XE",
  poolAlias: "finance"
});

// Use specific pools
const hrConnection = await oracledb.getPool("hr").getConnection();
const financeConnection = await oracledb.getPool("finance").getConnection();

// Or get pools by alias
const hrPool2 = oracledb.getPool("hr");
const defaultPool = oracledb.getPool(); // Gets default pool

// Clean shutdown of all pools
await hrPool.close();
await financePool.close();