0
# Connection Management
1

2
Connection pooling, lifecycle management, and advanced connection options for production use.
3

4
## Capabilities
5

6
### Connection Pooling
7

8
Automatic connection pool management with configurable pool size and connection lifecycle.
9

10
```javascript { .api }
11
interface ConnectionOptions {
12
  /** Maximum number of connections in the pool */
13
  max?: number; // default: 10
14
  
15
  /** Connection idle timeout in seconds */
16
  idle_timeout?: number; // default: null (no timeout)
17
  
18
  /** Connection timeout in seconds */
19
  connect_timeout?: number; // default: 30
20
  
21
  /** Maximum connection lifetime in seconds */
22
  max_lifetime?: number; // default: random 30-60 minutes
23
  
24
  /** Keep-alive interval in seconds */
25
  keep_alive?: number; // default: 60
26
  
27
  /** Backoff strategy for reconnection attempts */
28
  backoff?: boolean | ((attemptNum: number) => number); // default: exponential
29
  
30
  /** Maximum pipeline requests per connection */
31
  max_pipeline?: number; // default: 100
32
  
33
  /** Session targeting for read-write/read-only connections */
34
  target_session_attrs?: 'read-write' | 'read-only' | 'primary' | 'standby' | 'prefer-standby';
35
  
36
  /** Publications for logical replication */
37
  publications?: string; // default: 'alltables'
38
  
39
  /** Custom socket implementation */
40
  socket?: any;
41
}
42
```
43

44
**Usage Examples:**
45

46
```javascript
47
// Production connection pool configuration
48
const sql = postgres({
49
  host: "localhost",
50
  database: "myapp",
51
  max: 20,                    // Up to 20 concurrent connections
52
  idle_timeout: 300,          // Close idle connections after 5 minutes
53
  connect_timeout: 10,        // Timeout connection attempts after 10 seconds
54
  max_lifetime: 3600,         // Recycle connections every hour
55
  keep_alive: 30              // Send keep-alive every 30 seconds
56
});
57

58
// Development configuration
59
const sql = postgres({
60
  host: "localhost", 
61
  database: "myapp_dev",
62
  max: 3,                     // Fewer connections for development
63
  target_session_attrs: 'read-write'  // Ensure primary connection
64
});
65

66
// High-throughput configuration
67
const sql = postgres({
68
  host: "localhost",
69
  database: "myapp",
70
  max: 50,                    // More connections for high load
71
  max_pipeline: 200,          // Higher pipeline limit
72
  max_lifetime: 1800,         // Recycle connections every 30 minutes
73
  keep_alive: 15              // More frequent keep-alive
74
});
75

76
// Read replica configuration  
77
const sql = postgres({
78
  host: ["primary.db.com", "replica.db.com"],
79
  database: "myapp",
80
  target_session_attrs: 'prefer-standby', // Prefer read replicas
81
  max: 20,
82
  idle_timeout: 60            // Shorter idle timeout
83
});
84

85
// Custom backoff strategy
86
const sql = postgres({
87
  backoff: (attemptNum) => Math.min(1000 * Math.pow(2, attemptNum), 30000)
88
});
89
```
90

91
### Reserved Connections
92

93
Reserve dedicated connections for critical operations that need guaranteed database access.
94

95
```javascript { .api }
96
/**
97
 * Reserve a dedicated connection from the pool
98
 * @returns Promise resolving to reserved SQL instance
99
 */
100
reserve(): Promise<ReservedSql>;
101

102
interface ReservedSql extends Sql {
103
  /** Release the reserved connection back to the pool */
104
  release(): void;
105
}
106
```
107

108
**Usage Examples:**
109

110
```javascript
111
// Reserve connection for critical operations
112
const reservedSql = await sql.reserve();
113

114
try {
115
  // This connection is guaranteed to be available
116
  await reservedSql`BEGIN`;
117
  
118
  const result = await reservedSql`
119
    UPDATE critical_data SET value = ${newValue} WHERE id = ${id}
120
  `;
121
  
122
  await reservedSql`
123
    INSERT INTO audit_log (action, data_id) VALUES ('update', ${id})
124
  `;
125
  
126
  await reservedSql`COMMIT`;
127
  
128
  console.log("Critical operation completed");
129
} catch (error) {
130
  await reservedSql`ROLLBACK`;
131
  throw error;
132
} finally {
133
  // Always release the connection
134
  reservedSql.release();
135
}
136

137
// Using reserved connection for long-running operations
138
async function processLargeDataset() {
139
  const reservedSql = await sql.reserve();
140
  
141
  try {
142
    for (const batch of largeDataset) {
143
      await reservedSql`
144
        INSERT INTO processed_data (batch_id, data) 
145
        VALUES (${batch.id}, ${sql.json(batch.data)})
146
      `;
147
    }
148
  } finally {
149
    reservedSql.release();
150
  }
151
}
152
```
153

154
### Connection Lifecycle
155

156
Manage the lifecycle of database connections with graceful shutdown and cleanup.
157

158
```javascript { .api }
159
/**
160
 * Close all connections gracefully
161
 * @param options - Shutdown options
162
 * @returns Promise that resolves when all connections are closed
163
 */
164
end(options?: { timeout?: number }): Promise<void>;
165

166
/**
167
 * Close all connections immediately
168
 * @returns Promise that resolves when all connections are closed
169
 */
170
close(): Promise<void>;
171
```
172

173
**Usage Examples:**
174

175
```javascript
176
// Graceful shutdown (recommended)
177
async function gracefulShutdown() {
178
  console.log("Shutting down database connections...");
179
  
180
  try {
181
    // Wait up to 10 seconds for queries to complete
182
    await sql.end({ timeout: 10 });
183
    console.log("Database connections closed gracefully");
184
  } catch (error) {
185
    console.error("Error during graceful shutdown:", error);
186
    // Force close if graceful shutdown fails
187
    await sql.close();
188
  }
189
}
190

191
// Handle process termination
192
process.on('SIGTERM', gracefulShutdown);
193
process.on('SIGINT', gracefulShutdown);
194

195
// Application shutdown
196
async function shutdownApp() {
197
  // Stop accepting new requests first
198
  server.close();
199
  
200
  // Then close database connections
201
  await sql.end({ timeout: 5 });
202
  
203
  process.exit(0);
204
}
205

206
// Immediate shutdown (force close)
207
async function emergencyShutdown() {
208
  console.log("Force closing database connections...");
209
  await sql.close();
210
  console.log("All connections closed immediately");
211
}
212
```
213

214
### Connection Configuration
215

216
Comprehensive connection configuration options for various deployment scenarios.
217

218
```javascript { .api }
219
interface ConnectionOptions {
220
  // Basic connection
221
  host?: string | string[];  // Single host or array for failover
222
  port?: number | number[];  // Port(s) corresponding to host(s)
223
  path?: string;             // Unix socket path
224
  database?: string;         // Database name
225
  user?: string;             // Username
226
  password?: string | (() => string | Promise<string>); // Password or function
227
  
228
  // SSL configuration
229
  ssl?: 'require' | 'allow' | 'prefer' | 'verify-full' | boolean | object;
230
  
231
  // Session attributes for multi-host setups
232
  target_session_attrs?: 'read-write' | 'read-only' | 'primary' | 'standby' | 'prefer-standby';
233
  
234
  // Query configuration
235
  prepare?: boolean;         // Use prepared statements (default: true)
236
  fetch_types?: boolean;     // Auto-fetch type information (default: true)
237
  
238
  // Event handlers
239
  onnotice?: (notice: Notice) => void;
240
  onparameter?: (key: string, value: any) => void;
241
  onclose?: (connectionId: number) => void;
242
  
243
  // Advanced
244
  socket?: any;              // Custom socket implementation
245
  publications?: string;     // Logical replication publications
246
}
247
```
248

249
**Usage Examples:**
250

251
```javascript
252
// Multi-host configuration with failover
253
const sql = postgres({
254
  host: ["primary.db.example.com", "replica.db.example.com"],
255
  port: [5432, 5432],
256
  database: "myapp",
257
  user: "app_user",
258
  password: process.env.DB_PASSWORD,
259
  target_session_attrs: "primary", // Prefer primary server
260
  ssl: "require"
261
});
262

263
// Password from environment or function
264
const sql = postgres({
265
  host: "localhost",
266
  database: "myapp",
267
  user: "app_user",
268
  password: async () => {
269
    // Fetch password from secret manager
270
    return await getSecretValue("db_password");
271
  }
272
});
273

274
// Unix socket connection
275
const sql = postgres({
276
  path: "/var/run/postgresql",
277
  database: "myapp",
278
  user: "app_user"
279
});
280

281
// SSL configuration
282
const sql = postgres({
283
  host: "secure.db.example.com",
284
  database: "myapp", 
285
  ssl: {
286
    rejectUnauthorized: true,
287
    ca: fs.readFileSync('./ca-certificate.crt').toString(),
288
    key: fs.readFileSync('./client-key.key').toString(),
289
    cert: fs.readFileSync('./client-certificate.crt').toString()
290
  }
291
});
292

293
// Event handlers
294
const sql = postgres({
295
  host: "localhost",
296
  database: "myapp",
297
  onnotice: (notice) => {
298
    console.log("PostgreSQL notice:", notice.message);
299
  },
300
  onparameter: (key, value) => {
301
    console.log(`Server parameter changed: ${key} = ${value}`);
302
  },
303
  onclose: (connectionId) => {
304
    console.log(`Connection ${connectionId} closed`);
305
  }
306
});
307
```
308

309
### Connection State and Monitoring
310

311
Monitor connection state and pool status for debugging and metrics.
312

313
```javascript { .api }
314
interface Sql {
315
  /** Access to parsed connection options */
316
  readonly options: ParsedOptions;
317
  
318
  /** Current connection parameters from server */
319
  readonly parameters: ConnectionParameters;
320
}
321

322
interface ConnectionParameters {
323
  application_name: string;
324
  server_version: string;
325
  server_encoding: string;
326
  client_encoding: string;
327
  is_superuser: string;
328
  session_authorization: string;
329
  DateStyle: string;
330
  TimeZone: string;
331
  [parameter: string]: string;
332
}
333
```
334

335
**Usage Examples:**
336

337
```javascript
338
// Check connection configuration
339
console.log("Database host:", sql.options.host);
340
console.log("Pool size:", sql.options.max);
341
console.log("SSL enabled:", sql.options.ssl);
342

343
// Monitor server parameters
344
console.log("Server version:", sql.parameters.server_version);
345
console.log("Timezone:", sql.parameters.TimeZone);
346
console.log("Application name:", sql.parameters.application_name);
347

348
// Custom application name
349
const sql = postgres({
350
  host: "localhost",
351
  database: "myapp",
352
  connection: {
353
    application_name: "MyApp v1.2.3"
354
  }
355
});
356

357
// Monitor parameter changes
358
const sql = postgres({
359
  onparameter: (key, value) => {
360
    if (key === 'TimeZone') {
361
      console.log(`Server timezone changed to: ${value}`);
362
    }
363
  }
364
});
365
```
366

367
### Connection Error Handling
368

369
Handle connection failures, timeouts, and recovery scenarios.
370

371
```javascript { .api }
372
// Connection-related error types
373
interface ConnectionError extends Error {
374
  code: 'CONNECTION_DESTROYED' | 'CONNECT_TIMEOUT' | 'CONNECTION_CLOSED' | 'CONNECTION_ENDED';
375
  errno: string;
376
  address: string;
377
  port?: number;
378
}
379
```
380

381
**Usage Examples:**
382

383
```javascript
384
// Handle connection errors
385
try {
386
  const result = await sql`SELECT * FROM users`;
387
} catch (error) {
388
  if (error.code === 'CONNECTION_DESTROYED') {
389
    console.error("Database connection was destroyed");
390
    // Implement reconnection logic
391
  } else if (error.code === 'CONNECT_TIMEOUT') {
392
    console.error("Database connection timed out");
393
    // Implement retry logic
394
  } else {
395
    console.error("Database error:", error);
396
  }
397
}
398

399
// Connection retry with backoff
400
async function executeWithRetry(query, maxRetries = 3) {
401
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
402
    try {
403
      return await query();
404
    } catch (error) {
405
      if (error.code?.includes('CONNECTION') && attempt < maxRetries) {
406
        const delay = Math.min(1000 * Math.pow(2, attempt - 1), 10000);
407
        console.log(`Connection failed, retrying in ${delay}ms...`);
408
        await new Promise(resolve => setTimeout(resolve, delay));
409
        continue;
410
      }
411
      throw error;
412
    }
413
  }
414
}
415

416
// Usage
417
const result = await executeWithRetry(async () => {
418
  return await sql`SELECT * FROM users WHERE active = true`;
419
});
420
```
421

422
### Connection Pool Best Practices
423

424
**Optimal Configuration:**
425

426
```javascript
427
// Production settings
428
const sql = postgres({
429
  max: Math.min(20, parseInt(process.env.DB_MAX_CONNECTIONS || "10")),
430
  idle_timeout: 300,        // 5 minutes
431
  connect_timeout: 10,      // 10 seconds  
432
  max_lifetime: 3600,       // 1 hour
433
  keep_alive: 30,           // 30 seconds
434
  
435
  // Use read replicas when possible
436
  target_session_attrs: process.env.DB_READ_ONLY === 'true' ? 'standby' : 'primary'
437
});
438

439
// Monitor pool exhaustion
440
let activeQueries = 0;
441
const originalQuery = sql.bind(sql);
442

443
sql = new Proxy(sql, {
444
  apply: function(target, thisArg, argumentsList) {
445
    activeQueries++;
446
    const query = originalQuery.apply(thisArg, argumentsList);
447
    
448
    query.finally(() => {
449
      activeQueries--;
450
      if (activeQueries > sql.options.max * 0.8) {
451
        console.warn(`High connection usage: ${activeQueries}/${sql.options.max}`);
452
      }
453
    });
454
    
455
    return query;
456
  }
457
});
458
```