0
# Connection Management
1

2
Connection pooling, direct connections, and connection lifecycle management. pg-promise provides automatic connection management with support for both pooled and direct connections, along with comprehensive connection monitoring and error handling.
3

4
## Capabilities
5

6
### Database Initialization
7

8
Creating database instances with connection configuration.
9

10
```javascript { .api }
11
/**
12
 * Create database instance with connection configuration
13
 * @param cn - Connection string or configuration object
14
 * @param dc - Database context (optional user-defined data)
15
 * @returns Database instance with connection pool
16
 */
17
pgp(cn: string | IConnectionParameters, dc?: any): IDatabase
18

19
interface IConnectionParameters {
20
  // Connection details
21
  host?: string // Database host
22
  port?: number // Database port
23
  database?: string // Database name
24
  user?: string // Username
25
  password?: string // Password
26
  
27
  // SSL configuration
28
  ssl?: boolean | object // SSL settings
29
  
30
  // Pool configuration
31
  max?: number // Maximum pool connections
32
  min?: number // Minimum pool connections
33
  idleTimeoutMillis?: number // Idle connection timeout
34
  connectionTimeoutMillis?: number // Connection timeout
35
  maxUses?: number // Maximum connection uses before replacement
36
  
37
  // Other pg options
38
  application_name?: string // Application name
39
  keepAlive?: boolean // TCP keep-alive
40
  statement_timeout?: number // Statement timeout
41
  query_timeout?: number // Query timeout
42
  lock_timeout?: number // Lock timeout
43
  idle_in_transaction_session_timeout?: number // Idle in transaction timeout
44
}
45
```
46

47
**Usage Examples:**
48

49
```javascript
50
// Connection string
51
const db = pgp('postgres://username:password@host:port/database');
52

53
// Connection object
54
const db2 = pgp({
55
  host: 'localhost',
56
  port: 5432,
57
  database: 'myapp',
58
  user: 'postgres',
59
  password: 'secret',
60
  max: 20, // Maximum connections in pool
61
  min: 5,  // Minimum connections in pool
62
  idleTimeoutMillis: 30000,
63
  ssl: false
64
});
65

66
// With database context
67
const db3 = pgp(connectionString, {
68
  userId: 123,
69
  sessionId: 'abc123',
70
  environment: 'production'
71
});
72

73
// SSL configuration
74
const dbSSL = pgp({
75
  host: 'secure-db.example.com',
76
  database: 'myapp',
77
  user: 'app_user',
78
  password: process.env.DB_PASSWORD,
79
  ssl: {
80
    rejectUnauthorized: false,
81
    ca: fs.readFileSync('server-ca.pem'),
82
    key: fs.readFileSync('client-key.pem'),
83
    cert: fs.readFileSync('client-cert.pem')
84
  }
85
});
86
```
87

88
### Connection Pool Management
89

90
Automatic connection pooling with configuration and monitoring.
91

92
```javascript { .api }
93
/**
94
 * Database instance with automatic connection pooling
95
 */
96
interface IDatabase {
97
  // Pool access (read-only)
98
  readonly $pool: IPool // Connection pool instance
99
  readonly $cn: string | IConnectionParameters // Connection configuration
100
  readonly $dc: any // Database context
101
  readonly $config: ILibConfig // Library configuration
102
}
103

104
/**
105
 * Connection pool interface
106
 */
107
interface IPool {
108
  totalCount: number // Total connections in pool
109
  idleCount: number // Idle connections count
110
  waitingCount: number // Waiting clients count
111
  
112
  connect(): Promise<IClient> // Get connection from pool
113
  end(): Promise<void> // Close all connections
114
  
115
  on(event: string, listener: Function): IPool // Event handling
116
  removeListener(event: string, listener: Function): IPool
117
}
118
```
119

120
**Usage Examples:**
121

122
```javascript
123
// Monitor pool status
124
console.log('Pool status:');
125
console.log('Total connections:', db.$pool.totalCount);
126
console.log('Idle connections:', db.$pool.idleCount);
127
console.log('Waiting clients:', db.$pool.waitingCount);
128

129
// Pool event handling
130
db.$pool.on('connect', client => {
131
  console.log('New client connected:', client.processID);
132
});
133

134
db.$pool.on('remove', client => {
135
  console.log('Client removed:', client.processID);
136
});
137

138
db.$pool.on('error', error => {
139
  console.error('Pool error:', error.message);
140
});
141

142
// Graceful shutdown
143
process.on('SIGINT', async () => {
144
  console.log('Closing database connections...');
145
  await db.$pool.end();
146
  process.exit(0);
147
});
148
```
149

150
### Direct Connections
151

152
Manual connection management for special use cases.
153

154
```javascript { .api }
155
/**
156
 * Establish direct connection (not from pool)
157
 * @param options - Connection options
158
 * @returns Promise resolving to connected client
159
 */
160
db.connect(options?: IConnectionOptions): Promise<IConnected>
161

162
interface IConnectionOptions {
163
  direct?: boolean // Use direct connection (bypass pool)
164
  onLost?(err: any, e: ILostContext): void // Connection lost handler
165
}
166

167
interface ILostContext {
168
  cn: string // Connection string
169
  dc: any // Database context
170
  start: Date // Connection start time
171
  client: IClient // Lost client instance
172
}
173

174
interface IConnected {
175
  readonly client: IClient // Raw database client
176
  
177
  // Connection management
178
  done(kill?: boolean): void | Promise<void> // Release connection
179
  
180
  // All database query methods available
181
  // (none, one, many, etc.)
182
  
183
  // Batch operations from spex
184
  batch(values: any[], options?: IBatchOptions): Promise<any[]>
185
  page(source: any, options?: IPageOptions, dest?: any): Promise<any>
186
  sequence(source: any, options?: ISequenceOptions, dest?: any): Promise<any>
187
}
188
```
189

190
**Usage Examples:**
191

192
```javascript
193
// Basic connection
194
const connection = await db.connect();
195
try {
196
  const users = await connection.any('SELECT * FROM users');
197
  const user = await connection.one('SELECT * FROM users WHERE id = $1', [123]);
198
  
199
  // Use connection for multiple queries
200
  const profiles = await connection.any('SELECT * FROM user_profiles WHERE user_id = ANY($1)', 
201
    [users.map(u => u.id)]);
202
    
203
} finally {
204
  connection.done(); // Always release connection
205
}
206

207
// Direct connection (bypasses pool)
208
const directConn = await db.connect({ direct: true });
209
try {
210
  // Long-running operation that shouldn't tie up pool connection
211
  await directConn.any('SELECT * FROM large_table ORDER BY created_at');
212
} finally {
213
  await directConn.done(); // Direct connections return Promise
214
}
215

216
// Connection with loss monitoring
217
const monitoredConn = await db.connect({
218
  onLost: (err, context) => {
219
    console.error('Connection lost:', err.message);
220
    console.log('Connection was active for:', Date.now() - context.start.getTime(), 'ms');
221
    console.log('Client process ID:', context.client.processID);
222
  }
223
});
224

225
// Use connection...
226
monitoredConn.done();
227

228
// Connection with batch operations
229
const batchConn = await db.connect();
230
try {
231
  // Process data in batches
232
  const userIds = [1, 2, 3, 4, 5];
233
  const queries = userIds.map(id => 
234
    () => batchConn.one('SELECT * FROM users WHERE id = $1', [id])
235
  );
236
  
237
  const users = await batchConn.batch(queries, { concurrency: 3 });
238
  
239
} finally {
240
  batchConn.done();
241
}
242
```
243

244
### Connection Events and Monitoring
245

246
Global connection event handling and monitoring.
247

248
```javascript { .api }
249
/**
250
 * Connection event handlers in initialization options
251
 */
252
interface IInitOptions {
253
  // Connection events
254
  connect?(e: IConnectEvent): void // Client connected
255
  disconnect?(e: IDisconnectEvent): void // Client disconnected
256
  query?(e: IEventContext): void // Query executed
257
  receive?(e: IReceiveEvent): void // Data received
258
  task?(e: IEventContext): void // Task started
259
  transact?(e: IEventContext): void // Transaction started
260
  error?(err: any, e: IEventContext): void // Error occurred
261
  extend?(obj: IDatabase, dc: any): void // Database instance extended
262
}
263

264
interface IConnectEvent {
265
  client: IClient // Database client
266
  dc: any // Database context
267
  useCount: number // Connection use count
268
}
269

270
interface IDisconnectEvent {
271
  client: IClient // Database client
272
  dc: any // Database context
273
}
274

275
interface IReceiveEvent {
276
  data: any[] // Received data rows
277
  result: IResultExt | void // Full result object (undefined for streams)
278
  ctx: IEventContext // Event context
279
}
280

281
interface IEventContext {
282
  client: IClient // Database client
283
  cn: any // Connection configuration
284
  dc: any // Database context
285
  query: any // Query being executed
286
  params: any // Query parameters
287
  values: any // Parameter values
288
  queryFilePath?: string // Query file path (if applicable)
289
  ctx: ITaskContext // Task context
290
}
291
```
292

293
**Usage Examples:**
294

295
```javascript
296
// Global connection monitoring
297
const pgp = require('pg-promise')({
298
  connect: (e) => {
299
    console.log('Connected to database:', {
300
      processId: e.client.processID,
301
      database: e.client.database,
302
      useCount: e.useCount
303
    });
304
  },
305
  
306
  disconnect: (e) => {
307
    console.log('Disconnected from database:', {
308
      processId: e.client.processID,
309
      database: e.client.database
310
    });
311
  },
312
  
313
  query: (e) => {
314
    console.log('Executing query:', {
315
      query: e.query,
316
      duration: Date.now() - e.ctx.start.getTime()
317
    });
318
  },
319
  
320
  receive: (e) => {
321
    console.log('Received data:', {
322
      rows: e.data?.length || 0,
323
      duration: e.result?.duration || 0
324
    });
325
  },
326
  
327
  error: (err, e) => {
328
    console.error('Database error:', {
329
      error: err.message,
330
      query: e.query,
331
      client: e.client.processID
332
    });
333
  }
334
});
335

336
const db = pgp(connectionString);
337
```
338

339
### Connection Lifecycle Management
340

341
Managing connection lifecycle and cleanup.
342

343
```javascript { .api }
344
/**
345
 * Library termination and cleanup
346
 */
347
pgp.end(): void // Close all connection pools
348

349
/**
350
 * Individual pool termination
351
 */
352
db.$pool.end(): Promise<void> // Close specific connection pool
353
```
354

355
**Usage Examples:**
356

357
```javascript
358
// Application shutdown
359
process.on('SIGTERM', () => {
360
  console.log('Shutting down gracefully...');
361
  
362
  // Close all pg-promise connection pools
363
  pgp.end();
364
  
365
  // Or close specific pool
366
  // await db.$pool.end();
367
});
368

369
// Graceful server shutdown
370
async function gracefulShutdown() {
371
  try {
372
    console.log('Closing database connections...');
373
    
374
    // Wait for active connections to finish
375
    while (db.$pool.totalCount > db.$pool.idleCount) {
376
      console.log('Waiting for connections to finish...');
377
      await new Promise(resolve => setTimeout(resolve, 100));
378
    }
379
    
380
    // Close the pool
381
    await db.$pool.end();
382
    console.log('Database connections closed');
383
    
384
  } catch (error) {
385
    console.error('Error during shutdown:', error);
386
  } finally {
387
    process.exit(0);
388
  }
389
}
390

391
// Multiple database instances
392
const userDB = pgp(userConnectionString);
393
const analyticsDB = pgp(analyticsConnectionString);
394

395
// Shutdown both
396
async function shutdownAll() {
397
  await Promise.all([
398
    userDB.$pool.end(),
399
    analyticsDB.$pool.end()
400
  ]);
401
  
402
  // Or close all at once
403
  pgp.end();
404
}
405
```
406

407
### Connection Health Monitoring
408

409
Monitoring connection health and implementing retry logic.
410

411
**Usage Examples:**
412

413
```javascript
414
// Connection health check
415
async function checkDatabaseHealth() {
416
  try {
417
    await db.one('SELECT 1 as alive');
418
    return { status: 'healthy', timestamp: new Date() };
419
  } catch (error) {
420
    return { 
421
      status: 'unhealthy', 
422
      error: error.message, 
423
      timestamp: new Date() 
424
    };
425
  }
426
}
427

428
// Retry wrapper for connection issues
429
async function withRetry(operation, maxRetries = 3, delay = 1000) {
430
  for (let i = 0; i < maxRetries; i++) {
431
    try {
432
      return await operation();
433
    } catch (error) {
434
      if (i === maxRetries - 1) throw error;
435
      
436
      if (error.code === 'ECONNRESET' || error.code === 'ECONNREFUSED') {
437
        console.log(`Connection failed, retrying in ${delay}ms... (${i + 1}/${maxRetries})`);
438
        await new Promise(resolve => setTimeout(resolve, delay));
439
        delay *= 2; // Exponential backoff
440
      } else {
441
        throw error; // Re-throw non-connection errors
442
      }
443
    }
444
  }
445
}
446

447
// Usage
448
const users = await withRetry(() => db.any('SELECT * FROM users'));
449
```
450

451
## Types
452

453
```javascript { .api }
454
// Client interface (from node-postgres)
455
interface IClient {
456
  processID: number // PostgreSQL backend process ID
457
  secretKey: number // Secret key for cancellation
458
  database: string // Connected database name
459
  user: string // Connected user
460
  host: string // Database host
461
  port: number // Database port
462
  
463
  // Connection state
464
  connection: IConnection // Underlying connection
465
  
466
  // Query execution
467
  query(text: string, values?: any[]): Promise<IResult>
468
  query(config: IQueryConfig): Promise<IResult>
469
  
470
  // Connection management
471
  connect(): Promise<void>
472
  end(): Promise<void>
473
  
474
  // Event handling
475
  on(event: string, listener: Function): IClient
476
  removeListener(event: string, listener: Function): IClient
477
}
478

479
interface IConnection {
480
  stream: any // Network stream
481
  
482
  // Event handling
483
  on(event: string, listener: Function): IConnection
484
  removeListener(event: string, listener: Function): IConnection
485
}
486

487
// Query configuration
488
interface IQueryConfig {
489
  text: string // SQL query
490
  values?: any[] // Parameter values
491
  name?: string // Prepared statement name
492
  binary?: boolean // Binary result format
493
  rowMode?: 'array' // Row mode
494
  types?: ITypes // Type parsers
495
}
496

497
// Library configuration
498
interface ILibConfig {
499
  version: string // pg-promise version
500
  promise: IGenericPromise // Promise library
501
  options: IInitOptions // Initialization options
502
  pgp: IMain // Main pg-promise instance
503
  $npm: any // Internal npm modules
504
}
505

506
// Generic promise interface
507
interface IGenericPromise {
508
  (cb: (resolve: Function, reject: Function) => void): Promise<any>
509
  resolve(value?: any): Promise<any>
510
  reject(reason?: any): Promise<any>
511
  all(iterable: any): Promise<any>
512
}
513
```