0
# Connection Pooling
1

2
The Pool class provides connection pool management for optimizing database resource usage and handling concurrent connections. It extends EventEmitter and maintains a pool of Client instances for efficient connection reuse.
3

4
## Capabilities
5

6
### Pool Constructor
7

8
Creates a new connection pool with configuration options.
9

10
```javascript { .api }
11
/**
12
 * Creates a new connection pool
13
 * @param config - Pool and connection configuration
14
 */
15
class Pool extends EventEmitter {
16
  constructor(config?: PoolConfig);
17
}
18

19
interface PoolConfig extends ClientConfig {
20
  /** Maximum number of clients in the pool (default: 10) */
21
  max?: number;
22
  /** Minimum number of clients in the pool (default: 0) */
23
  min?: number;
24
  /** Idle timeout in milliseconds (default: 10000) */
25
  idleTimeoutMillis?: number;
26
  /** Connection timeout in milliseconds (default: 0) */
27
  connectionTimeoutMillis?: number;
28
  /** Maximum number of uses per connection before recreation */
29
  maxUses?: number;
30
  /** Maximum lifetime of connections in seconds */
31
  maxLifetimeSeconds?: number;
32
  /** Allow process to exit when pool is idle */
33
  allowExitOnIdle?: boolean;
34
  /** Log function for pool events */
35
  log?: (message: string, level: string) => void;
36
  /** Client verification function */
37
  verify?: (client: Client) => Promise<void>;
38
}
39
```
40

41
**Usage Examples:**
42

43
```javascript
44
const { Pool } = require('pg');
45

46
// Basic pool
47
const pool = new Pool({
48
  user: 'postgres',
49
  host: 'localhost',
50
  database: 'myapp',
51
  password: 'secret',
52
  port: 5432,
53
  max: 20,
54
  idleTimeoutMillis: 30000,
55
  connectionTimeoutMillis: 2000,
56
});
57

58
// Pool with connection string
59
const pool2 = new Pool({
60
  connectionString: 'postgresql://user:password@host:5432/database',
61
  max: 5
62
});
63

64
// Pool with SSL
65
const pool3 = new Pool({
66
  host: 'secure-host.com',
67
  ssl: true,
68
  max: 10,
69
  allowExitOnIdle: true
70
});
71
```
72

73
### Connect
74

75
Acquire a client from the pool for direct connection management.
76

77
```javascript { .api }
78
/**
79
 * Acquire a client from the pool
80
 * @returns Promise resolving to a PoolClient instance
81
 */
82
connect(): Promise<PoolClient>;
83

84
interface PoolClient extends Client {
85
  /** Release the client back to the pool */
86
  release(err?: Error | boolean): void;
87
}
88
```
89

90
**Usage Examples:**
91

92
```javascript
93
// Manual client management
94
const client = await pool.connect();
95
try {
96
  const res = await client.query('SELECT * FROM users');
97
  console.log(res.rows);
98
} finally {
99
  client.release(); // Return client to pool
100
}
101

102
// Release with error (removes client from pool)
103
const client2 = await pool.connect();
104
try {
105
  await client2.query('INVALID SQL');
106
} catch (err) {
107
  client2.release(err); // Client will be destroyed
108
  throw err;
109
}
110
```
111

112
### Direct Query Execution
113

114
Execute queries directly on the pool without manual client management.
115

116
```javascript { .api }
117
/**
118
 * Execute a SQL query using pool resources
119
 * @param text - SQL query string
120
 * @param values - Optional parameter values
121
 * @param callback - Optional callback for query result
122
 * @returns Promise resolving to query result
123
 */
124
query(text: string, values?: any[], callback?: QueryCallback): Promise<QueryResult>;
125

126
/**
127
 * Execute a SQL query with configuration object
128
 * @param config - Query configuration
129
 * @param callback - Optional callback for query result
130
 * @returns Promise resolving to query result
131
 */
132
query(config: QueryConfig, callback?: QueryCallback): Promise<QueryResult>;
133
```
134

135
**Usage Examples:**
136

137
```javascript
138
// Simple query (client acquired and released automatically)
139
const res = await pool.query('SELECT NOW()');
140
console.log(res.rows[0]);
141

142
// Parameterized query
143
const users = await pool.query('SELECT * FROM users WHERE age > $1', [21]);
144

145
// Named prepared statement
146
const result = await pool.query({
147
  name: 'fetch-user-by-email',
148
  text: 'SELECT * FROM users WHERE email = $1',
149
  values: ['user@example.com']
150
});
151

152
// Callback style
153
pool.query('SELECT COUNT(*) FROM users', (err, result) => {
154
  if (err) throw err;
155
  console.log('User count:', result.rows[0].count);
156
});
157
```
158

159
### Transaction Management
160

161
Perform database transactions using manual client management.
162

163
**Usage Examples:**
164

165
```javascript
166
// Transaction with manual client management
167
const client = await pool.connect();
168
try {
169
  await client.query('BEGIN');
170
  await client.query('INSERT INTO users (name) VALUES ($1)', ['Alice']);
171
  await client.query('INSERT INTO profiles (user_id) VALUES ($1)', [123]);
172
  await client.query('COMMIT');
173
} catch (err) {
174
  await client.query('ROLLBACK');
175
  throw err;
176
} finally {
177
  client.release();
178
}
179

180
// Helper for cleaner transaction syntax
181
async function withTransaction(pool, callback) {
182
  const client = await pool.connect();
183
  try {
184
    await client.query('BEGIN');
185
    const result = await callback(client);
186
    await client.query('COMMIT');
187
    return result;
188
  } catch (err) {
189
    await client.query('ROLLBACK');
190
    throw err;
191
  } finally {
192
    client.release();
193
  }
194
}
195

196
// Usage
197
const result = await withTransaction(pool, async (client) => {
198
  const user = await client.query('INSERT INTO users (name) VALUES ($1) RETURNING *', ['Bob']);
199
  await client.query('INSERT INTO profiles (user_id) VALUES ($1)', [user.rows[0].id]);
200
  return user.rows[0];
201
});
202
```
203

204
### Pool Termination
205

206
Shut down the connection pool and close all connections.
207

208
```javascript { .api }
209
/**
210
 * End all connections in the pool
211
 * @returns Promise that resolves when all connections are closed
212
 */
213
end(): Promise<void>;
214
```
215

216
**Usage Examples:**
217

218
```javascript
219
// Graceful shutdown
220
await pool.end();
221

222
// With error handling
223
try {
224
  await pool.end();
225
  console.log('Pool has ended');
226
} catch (err) {
227
  console.error('Error ending pool', err);
228
}
229
```
230

231
### Pool Monitoring
232

233
Access pool statistics and status information.
234

235
```javascript { .api }
236
interface Pool {
237
  /** Total number of clients in the pool */
238
  readonly totalCount: number;
239
  /** Number of idle clients available for use */
240
  readonly idleCount: number;
241
  /** Number of clients currently in use */
242
  readonly waitingCount: number;
243
  /** Number of expired clients that will be removed */
244
  readonly expiredCount: number;
245
}
246
```
247

248
**Usage Examples:**
249

250
```javascript
251
// Monitor pool status
252
console.log(`Pool stats: ${pool.totalCount} total, ${pool.idleCount} idle, ${pool.waitingCount} waiting`);
253

254
// Pool status endpoint for health checks
255
app.get('/health/db', (req, res) => {
256
  res.json({
257
    totalConnections: pool.totalCount,
258
    idleConnections: pool.idleCount,
259
    waitingRequests: pool.waitingCount
260
  });
261
});
262
```
263

264
## Pool Events
265

266
Pool emits various events for monitoring and debugging:
267

268
```javascript { .api }
269
// Client lifecycle events
270
pool.on('connect', (client) => {
271
  console.log('New client connected');
272
});
273

274
pool.on('acquire', (client) => {
275
  console.log('Client acquired from pool');
276
});
277

278
pool.on('remove', (client) => {
279
  console.log('Client removed from pool');
280
});
281

282
pool.on('release', (err, client) => {
283
  console.log('Client released back to pool');
284
});
285

286
// Error events
287
pool.on('error', (err, client) => {
288
  console.error('Pool client error:', err);
289
});
290

291
// Pool status events
292
pool.on('drain', () => {
293
  console.log('Pool has drained');
294
});
295
```
296

297
## Pool Configuration Best Practices
298

299
### Basic Configuration
300

301
```javascript
302
const pool = new Pool({
303
  // Connection settings
304
  host: process.env.DB_HOST,
305
  user: process.env.DB_USER,
306
  password: process.env.DB_PASSWORD,
307
  database: process.env.DB_NAME,
308
  port: parseInt(process.env.DB_PORT) || 5432,
309
  
310
  // Pool settings
311
  max: 10,                      // Maximum connections
312
  idleTimeoutMillis: 30000,     // Close idle connections after 30s
313
  connectionTimeoutMillis: 2000, // Timeout when acquiring connection
314
  allowExitOnIdle: true         // Allow process to exit when idle
315
});
316
```
317

318
### Production Configuration
319

320
```javascript
321
const pool = new Pool({
322
  connectionString: process.env.DATABASE_URL,
323
  
324
  // SSL in production
325
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false,
326
  
327
  // Pool sizing based on expected load
328
  max: parseInt(process.env.DB_POOL_SIZE) || 20,
329
  min: 2,                       // Minimum connections to maintain
330
  
331
  // Timeouts
332
  idleTimeoutMillis: 10000,     // Close idle connections quickly
333
  connectionTimeoutMillis: 5000, // Fail fast on connection issues
334
  
335
  // Connection health
336
  maxUses: 7500,                // Recreate connections periodically
337
  
338
  // Monitoring
339
  log: (message, level) => {
340
    if (level === 'error') {
341
      console.error('Pool error:', message);
342
    }
343
  }
344
});
345
```
346

347
## Connection Pool Types
348

349
```javascript { .api }
350
type PoolConfig = ClientConfig & {
351
  max?: number;
352
  min?: number;
353
  idleTimeoutMillis?: number;
354
  connectionTimeoutMillis?: number;
355
  maxUses?: number;
356
  maxLifetimeSeconds?: number;
357
  allowExitOnIdle?: boolean;
358
  log?: (message: string, level: string) => void;
359
  verify?: (client: Client) => Promise<void>;
360
};
361

362
interface PoolClient extends Client {
363
  release(err?: Error | boolean): void;
364
  processID: number;
365
  secretKey: number;
366
}
367
```