0
# Connection Pools
1

2
Advanced connection pooling for high-performance, scalable database applications with automatic connection lifecycle management.
3

4
## Capabilities
5

6
### Pool Management
7

8
Connection pools provide efficient resource management for database connections.
9

10
```javascript { .api }
11
interface Pool {
12
  // Get connection from pool
13
  getConnection(options?: GetConnectionOptions): Promise<Connection>;
14
  
15
  // Close pool and all connections
16
  close(drainTime?: number): Promise<void>;
17
  
18
  // Reconfigure pool settings
19
  reconfigure(options: PoolAttributes): Promise<void>;
20
  
21
  // Set access token for cloud authentication
22
  setAccessToken(options: AccessTokenOptions): Promise<void>;
23
  
24
  // Pool statistics (read-only properties)
25
  connectionsInUse: number;
26
  connectionsOpen: number;
27
  poolAlias: string;
28
  poolIncrement: number;
29
  poolMax: number;
30
  poolMaxPerShard: number;
31
  poolMin: number;
32
  poolPingInterval: number;
33
  poolPingTimeout: number;
34
  poolTimeout: number;
35
  queueMax: number;
36
  queueTimeout: number;
37
  sodaMetaDataCache: boolean;
38
  status: number;
39
  stmtCacheSize: number;
40
  thin: boolean;
41
}
42

43
interface GetConnectionOptions {
44
  tag?: string;
45
  shardingKey?: (string | number | Date)[];
46
  superShardingKey?: (string | number | Date)[];
47
}
48

49
interface AccessTokenOptions {
50
  token: string;
51
  privateKey?: string;
52
}
53
```
54

55
**Usage Examples:**
56

57
```javascript
58
const oracledb = require('oracledb');
59

60
// Create a connection pool
61
const pool = await oracledb.createPool({
62
  user: "hr",
63
  password: "welcome123",
64
  connectString: "localhost:1521/XE",
65
  poolMin: 5,
66
  poolMax: 20,
67
  poolIncrement: 2,
68
  poolTimeout: 300,
69
  poolAlias: "hrpool"
70
});
71

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

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

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

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

85
// Close the pool when done
86
await pool.close(10); // 10 second drain time
87
```
88

89
### Pool Configuration
90

91
Comprehensive pool configuration options for optimal performance.
92

93
```javascript { .api }
94
interface PoolAttributes extends ConnectionAttributes {
95
  poolAlias?: string;           // Pool identifier
96
  poolIncrement?: number;       // Connections to create when pool needs more
97
  poolMax?: number;            // Maximum pool connections
98
  poolMaxPerShard?: number;    // Maximum connections per shard
99
  poolMin?: number;            // Minimum pool connections
100
  poolPingInterval?: number;   // Seconds between connection health checks
101
  poolPingTimeout?: number;    // Timeout for ping operations
102
  poolTimeout?: number;        // Idle connection timeout
103
  queueMax?: number;          // Maximum queued getConnection requests
104
  queueTimeout?: number;       // Timeout for queued requests
105
  sessionCallback?: string | SessionCallback; // Session fixup callback
106
  sodaMetaDataCache?: boolean; // Enable SODA metadata caching
107
  enableStatistics?: boolean;  // Enable pool statistics
108
}
109

110
type SessionCallback = (
111
  connection: Connection,
112
  requestedTag: string,
113
  callbackFn: (error?: Error, connection?: Connection) => void
114
) => void;
115
```
116

117
**Usage Examples:**
118

119
```javascript
120
// Comprehensive pool configuration
121
const pool = await oracledb.createPool({
122
  // Connection parameters
123
  user: "app_user",
124
  password: "app_password",
125
  connectString: "mydb.example.com:1521/XEPDB1",
126
  
127
  // Pool sizing
128
  poolMin: 10,          // Always keep 10 connections open
129
  poolMax: 50,          // Maximum 50 connections
130
  poolIncrement: 5,     // Create 5 connections at a time when needed
131
  
132
  // Timeouts
133
  poolTimeout: 600,     // Close idle connections after 10 minutes
134
  queueTimeout: 5000,   // Wait max 5 seconds for connection
135
  poolPingInterval: 60, // Check connection health every minute
136
  
137
  // Queue management
138
  queueMax: 100,        // Maximum 100 waiting requests
139
  
140
  // Session management
141
  sessionCallback: (connection, requestedTag, cb) => {
142
    // Custom session setup
143
    connection.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'")
144
      .then(() => cb(null, connection))
145
      .catch(cb);
146
  },
147
  
148
  poolAlias: "mainpool"
149
});
150
```
151

152
### Pool Reconfiguration
153

154
Dynamically reconfigure pool settings without recreating the pool.
155

156
```javascript { .api }
157
/**
158
 * Reconfigures pool settings
159
 * @param options - New pool configuration options
160
 * @returns Promise that resolves when reconfiguration is complete
161
 */
162
reconfigure(options: PoolAttributes): Promise<void>;
163
```
164

165
**Usage Examples:**
166

167
```javascript
168
// Initially create pool
169
const pool = await oracledb.createPool({
170
  user: "hr",
171
  password: "welcome123",
172
  connectString: "localhost:1521/XE",
173
  poolMin: 5,
174
  poolMax: 10
175
});
176

177
// Later, reconfigure to handle more load
178
await pool.reconfigure({
179
  poolMin: 10,
180
  poolMax: 30,
181
  poolIncrement: 5
182
});
183

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

187
### Tagged Connections
188

189
Use connection tags for session state reuse and optimization.
190

191
```javascript { .api }
192
/**
193
 * Gets a connection with optional tag for session state reuse
194
 * @param options - Connection options including tag
195
 * @returns Promise resolving to tagged Connection
196
 */
197
getConnection(options: GetConnectionOptions): Promise<Connection>;
198

199
interface GetConnectionOptions {
200
  tag?: string;                              // Session tag for reuse
201
  shardingKey?: (string | number | Date)[];  // Sharding key
202
  superShardingKey?: (string | number | Date)[]; // Super sharding key
203
}
204
```
205

206
**Usage Examples:**
207

208
```javascript
209
// Get connection with specific tag
210
const connection1 = await pool.getConnection({
211
  tag: "reporting"
212
});
213

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

218
// Return to pool
219
await connection1.close();
220

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

226
// Session state may already be configured
227
console.log('Connection tag:', connection2.tag);
228
```
229

230
### Pool Statistics
231

232
Monitor pool performance and health.
233

234
```javascript { .api }
235
interface PoolStatistics {
236
  // Connection counts
237
  connectionsInUse: number;     // Currently active connections
238
  connectionsOpen: number;      // Total open connections
239
  
240
  // Pool status
241
  status: number;              // Pool status (OPEN, DRAINING, CLOSED, etc.)
242
  
243
  // Configuration (read-only)
244
  poolMin: number;
245
  poolMax: number;
246
  poolIncrement: number;
247
  poolTimeout: number;
248
  queueMax: number;
249
  queueTimeout: number;
250
}
251

252
// Pool status constants
253
const POOL_STATUS_OPEN = 6000;
254
const POOL_STATUS_DRAINING = 6001;
255
const POOL_STATUS_CLOSED = 6002;
256
const POOL_STATUS_RECONFIGURING = 6003;
257
```
258

259
**Usage Examples:**
260

261
```javascript
262
// Monitor pool health
263
function logPoolStats(pool) {
264
  console.log(`Pool Status: ${pool.status}`);
265
  console.log(`Connections: ${pool.connectionsInUse}/${pool.connectionsOpen} (in use/open)`);
266
  console.log(`Pool limits: ${pool.poolMin}-${pool.poolMax}`);
267
  
268
  if (pool.connectionsInUse === pool.poolMax) {
269
    console.warn('Pool at maximum capacity!');
270
  }
271
}
272

273
// Periodic monitoring
274
setInterval(() => {
275
  logPoolStats(pool);
276
}, 30000); // Every 30 seconds
277

278
// Check if pool needs reconfiguration
279
if (pool.connectionsInUse / pool.poolMax > 0.8) {
280
  console.log('Consider increasing pool size');
281
}
282
```
283

284
### Pool Lifecycle Management
285

286
Proper pool lifecycle management for application shutdown.
287

288
```javascript { .api }
289
/**
290
 * Closes the pool and all connections
291
 * @param drainTime - Time in seconds to wait for connections to be returned
292
 * @returns Promise that resolves when pool is closed
293
 */
294
close(drainTime?: number): Promise<void>;
295
```
296

297
**Usage Examples:**
298

299
```javascript
300
// Graceful shutdown
301
async function shutdown() {
302
  console.log('Shutting down application...');
303
  
304
  // Stop accepting new requests
305
  server.close();
306
  
307
  // Close pool with drain time
308
  try {
309
    await pool.close(30); // Wait up to 30 seconds
310
    console.log('Pool closed successfully');
311
  } catch (error) {
312
    console.error('Error closing pool:', error);
313
  }
314
  
315
  process.exit(0);
316
}
317

318
// Handle shutdown signals
319
process.on('SIGINT', shutdown);
320
process.on('SIGTERM', shutdown);
321

322
// Emergency shutdown (no drain time)
323
process.on('SIGKILL', async () => {
324
  await pool.close(0);
325
  process.exit(1);
326
});
327
```
328

329
### Multiple Pools
330

331
Managing multiple connection pools for different databases or users.
332

333
```javascript { .api }
334
/**
335
 * Gets an existing pool by alias
336
 * @param alias - Pool alias (defaults to 'default')
337
 * @returns Pool instance
338
 */
339
function getPool(alias?: string): Pool;
340
```
341

342
**Usage Examples:**
343

344
```javascript
345
// Create multiple pools
346
const hrPool = await oracledb.createPool({
347
  user: "hr",
348
  password: "hr_password",
349
  connectString: "localhost:1521/XE",
350
  poolAlias: "hr"
351
});
352

353
const financePool = await oracledb.createPool({
354
  user: "finance",
355
  password: "finance_password", 
356
  connectString: "localhost:1521/XE",
357
  poolAlias: "finance"
358
});
359

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

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

368
// Clean shutdown of all pools
369
await hrPool.close();
370
await financePool.close();
371
```