0
# Configuration and Utilities
1

2
Library initialization options, utility functions, and transaction modes. pg-promise provides extensive configuration options for customizing behavior, utility functions for common tasks, and transaction mode management.
3

4
## Capabilities
5

6
### Library Initialization
7

8
Main initialization function with comprehensive configuration options.
9

10
```javascript { .api }
11
/**
12
 * Initialize pg-promise library with configuration options
13
 * @param options - Library initialization options
14
 * @returns Configured pg-promise instance
15
 */
16
function pgPromise(options?: IInitOptions): IMain
17

18
interface IInitOptions {
19
  // Query formatting options
20
  pgFormatting?: boolean // Use pg driver formatting instead of pg-promise
21
  capSQL?: boolean // Capitalize SQL keywords
22
  
23
  // Connection options
24
  pgNative?: boolean // Use native PostgreSQL bindings
25
  schema?: ValidSchema | ((dc: any) => ValidSchema) // Default database schema(s)
26
  noWarnings?: boolean // Disable diagnostic warnings
27
  
28
  // Event handlers
29
  connect?(e: IConnectEvent): void // Client connected event
30
  disconnect?(e: IDisconnectEvent): void // Client disconnected event
31
  query?(e: IEventContext): void // Query execution event
32
  receive?(e: IReceiveEvent): void // Data received event
33
  task?(e: IEventContext): void // Task started event
34
  transact?(e: IEventContext): void // Transaction started event
35
  error?(err: any, e: IEventContext): void // Error occurred event
36
  extend?(obj: IDatabase, dc: any): void // Database instance extension
37
}
38

39
type ValidSchema = string | string[] | null | void
40
```
41

42
**Usage Examples:**
43

44
```javascript
45
// Basic initialization
46
const pgp = require('pg-promise')();
47

48
// With query formatting options
49
const pgp = require('pg-promise')({
50
  capSQL: true, // Capitalize SQL keywords
51
  pgFormatting: false // Use pg-promise advanced formatting
52
});
53

54
// With schema configuration
55
const pgp = require('pg-promise')({
56
  schema: ['app', 'public'], // Search in app schema first, then public
57
  // Or dynamic schema:
58
  // schema: (dc) => dc.userType === 'admin' ? ['admin', 'public'] : ['public']
59
});
60

61
// With native bindings (requires pg-native)
62
const pgp = require('pg-promise')({
63
  pgNative: true, // Use native PostgreSQL bindings for performance
64
  noWarnings: true // Disable warnings
65
});
66

67
// With comprehensive event handling
68
const pgp = require('pg-promise')({
69
  connect: (e) => {
70
    console.log('Connected:', e.client.processID, 'use count:', e.useCount);
71
  },
72
  
73
  disconnect: (e) => {
74
    console.log('Disconnected:', e.client.processID);
75
  },
76
  
77
  query: (e) => {
78
    console.log('Query:', e.query.slice(0, 50) + '...');
79
  },
80
  
81
  receive: (e) => {
82
    console.log('Received:', e.data.length, 'rows');
83
  },
84
  
85
  error: (err, e) => {
86
    console.error('Error:', err.message, 'Query:', e.query);
87
  }
88
});
89
```
90

91
### Event System
92

93
Comprehensive event system for monitoring and extending functionality.
94

95
```javascript { .api }
96
// Event context interfaces
97
interface IConnectEvent {
98
  client: IClient // Database client instance
99
  dc: any // Database context
100
  useCount: number // Connection usage count
101
}
102

103
interface IDisconnectEvent {
104
  client: IClient // Database client instance
105
  dc: any // Database context
106
}
107

108
interface IReceiveEvent {
109
  data: any[] // Received data rows
110
  result: IResultExt | void // Full result object (undefined for streams)
111
  ctx: IEventContext // Query execution context
112
}
113

114
interface IEventContext {
115
  client: IClient // Database client
116
  cn: any // Connection parameters
117
  dc: any // Database context
118
  query: any // Query being executed
119
  params: any // Query parameters
120
  values: any // Parameter values
121
  queryFilePath?: string // Query file path (if QueryFile used)
122
  ctx: ITaskContext // Task/transaction context
123
}
124
```
125

126
**Usage Examples:**
127

128
```javascript
129
// Performance monitoring
130
const pgp = require('pg-promise')({
131
  query: (e) => {
132
    e.ctx.startTime = Date.now();
133
  },
134
  
135
  receive: (e) => {
136
    const duration = Date.now() - e.ctx.startTime;
137
    if (duration > 1000) { // Log slow queries
138
      console.log('Slow query detected:', {
139
        duration: duration,
140
        query: e.ctx.query,
141
        rows: e.data.length
142
      });
143
    }
144
  }
145
});
146

147
// Query logging with context
148
const pgp = require('pg-promise')({
149
  query: (e) => {
150
    console.log(`[${e.ctx.tag || 'query'}] ${e.query}`, e.params || e.values);
151
  }
152
});
153

154
// Connection pool monitoring
155
let activeConnections = 0;
156

157
const pgp = require('pg-promise')({
158
  connect: (e) => {
159
    activeConnections++;
160
    console.log(`Connection pool: ${activeConnections} active connections`);
161
  },
162
  
163
  disconnect: (e) => {
164
    activeConnections--;
165
    console.log(`Connection pool: ${activeConnections} active connections`);
166
  }
167
});
168

169
// Error aggregation
170
const errorStats = new Map();
171

172
const pgp = require('pg-promise')({
173
  error: (err, e) => {
174
    const errorType = err.code || err.name;
175
    const count = errorStats.get(errorType) || 0;
176
    errorStats.set(errorType, count + 1);
177
    
178
    console.error(`Error [${errorType}] occurred ${count + 1} times:`, err.message);
179
  }
180
});
181
```
182

183
### Database Extension
184

185
Extending database instances with custom methods and properties.
186

187
```javascript { .api }
188
/**
189
 * Database extension handler
190
 * @param obj - Database instance to extend
191
 * @param dc - Database context
192
 */
193
interface ExtendHandler {
194
  (obj: IDatabase, dc: any): void
195
}
196
```
197

198
**Usage Examples:**
199

200
```javascript
201
// Adding custom methods
202
const pgp = require('pg-promise')({
203
  extend: (obj, dc) => {
204
    // Add custom query methods
205
    obj.findUser = function(email) {
206
      return this.oneOrNone('SELECT * FROM users WHERE email = $1', [email]);
207
    };
208
    
209
    obj.createUser = function(userData) {
210
      return this.one(`
211
        INSERT INTO users(name, email, created_at) 
212
        VALUES($1, $2, NOW()) 
213
        RETURNING *
214
      `, [userData.name, userData.email]);
215
    };
216
    
217
    obj.getUserWithProfile = function(userId) {
218
      return this.one(`
219
        SELECT u.*, p.bio, p.avatar_url
220
        FROM users u
221
        LEFT JOIN profiles p ON u.id = p.user_id
222
        WHERE u.id = $1
223
      `, [userId]);
224
    };
225
    
226
    // Add context-aware methods
227
    obj.log = function(message) {
228
      console.log(`[${dc.environment || 'dev'}] ${message}`);
229
    };
230
  }
231
});
232

233
const db = pgp(connectionString, { environment: 'production' });
234

235
// Use custom methods
236
const user = await db.findUser('john@example.com');
237
const newUser = await db.createUser({ name: 'Jane', email: 'jane@example.com' });
238
db.log('Custom method executed');
239

240
// Adding repository pattern
241
const pgp = require('pg-promise')({
242
  extend: (obj, dc) => {
243
    obj.users = {
244
      findById: (id) => obj.oneOrNone('SELECT * FROM users WHERE id = $1', [id]),
245
      findByEmail: (email) => obj.oneOrNone('SELECT * FROM users WHERE email = $1', [email]),
246
      create: (data) => obj.one('INSERT INTO users(${this:name}) VALUES(${this:csv}) RETURNING *', data),
247
      update: (id, data) => obj.oneOrNone('UPDATE users SET ${this:name} = ${this:csv} WHERE id = $1 RETURNING *', [data, id]),
248
      delete: (id) => obj.none('DELETE FROM users WHERE id = $1', [id])
249
    };
250
    
251
    obj.orders = {
252
      findByUser: (userId) => obj.any('SELECT * FROM orders WHERE user_id = $1', [userId]),
253
      create: (orderData) => obj.tx(async t => {
254
        const order = await t.one('INSERT INTO orders(user_id, total) VALUES($1, $2) RETURNING *', 
255
          [orderData.userId, orderData.total]);
256
        
257
        if (orderData.items) {
258
          const items = orderData.items.map(item => 
259
            t.none('INSERT INTO order_items(order_id, product_id, quantity) VALUES($1, $2, $3)', 
260
              [order.id, item.productId, item.quantity])
261
          );
262
          await Promise.all(items);
263
        }
264
        
265
        return order;
266
      })
267
    };
268
  }
269
});
270

271
// Usage with repository pattern
272
const user = await db.users.findById(123);
273
const orders = await db.orders.findByUser(123);
274
const newOrder = await db.orders.create({
275
  userId: 123,
276
  total: 99.99,
277
  items: [{ productId: 1, quantity: 2 }]
278
});
279
```
280

281
### Utility Functions
282

283
General-purpose utility functions for common operations.
284

285
```javascript { .api }
286
/**
287
 * Utility functions namespace
288
 */
289
interface IUtils {
290
  camelize(text: string): string // Convert text to camelCase
291
  camelizeVar(text: string): string // Convert text to valid camelCase variable name
292
  enumSql(dir: string, options?: IEnumSqlOptions, cb?: EnumSqlCallback): object // Enumerate SQL files
293
  taskArgs(args: Arguments): ITaskArguments // Normalize task/transaction arguments
294
}
295

296
interface IEnumSqlOptions {
297
  recursive?: boolean // Include subdirectories
298
  ignoreErrors?: boolean // Ignore access/naming errors
299
}
300

301
type EnumSqlCallback = (file: string, name: string, path: string) => any
302

303
interface ITaskArguments {
304
  options: any // Parsed options object
305
  cb: Function // Callback function
306
  [0]: any // Options (same as options property)
307
  [1]: Function // Callback (same as cb property)
308
}
309
```
310

311
**Usage Examples:**
312

313
```javascript
314
// Text utilities
315
console.log(pgp.utils.camelize('user_full_name')); // 'userFullName'
316
console.log(pgp.utils.camelize('first-name')); // 'firstName'
317
console.log(pgp.utils.camelizeVar('123_invalid-var')); // 'invalidVar'
318

319
// SQL file enumeration
320
const sql = pgp.utils.enumSql('./sql', { recursive: true }, (file, name, path) => {
321
  return new pgp.QueryFile(file, { minify: true });
322
});
323

324
// Usage: sql.users.getById, sql.orders.create, etc.
325

326
// Task argument parsing (for custom extensions)
327
const pgp = require('pg-promise')({
328
  extend: (obj) => {
329
    obj.customTx = function() {
330
      const args = pgp.utils.taskArgs(arguments);
331
      
332
      // Add default transaction mode if not specified
333
      if (!args.options.mode) {
334
        args.options.mode = new pgp.txMode.TransactionMode({
335
          tiLevel: pgp.txMode.isolationLevel.readCommitted
336
        });
337
      }
338
      
339
      return obj.tx.apply(this, args);
340
    };
341
  }
342
});
343

344
// Directory structure utilities
345
function createSqlRepository(baseDir) {
346
  const queries = {};
347
  
348
  const files = pgp.utils.enumSql(baseDir, { recursive: true }, (file, name, path) => {
349
    const queryFile = new pgp.QueryFile(file, { minify: true });
350
    if (queryFile.error) {
351
      console.warn(`Failed to load ${file}: ${queryFile.error.message}`);
352
      return null;
353
    }
354
    return queryFile;
355
  });
356
  
357
  // Convert flat structure to nested object
358
  function buildNested(obj, keys, value) {
359
    if (keys.length === 1) {
360
      obj[keys[0]] = value;
361
    } else {
362
      const key = keys.shift();
363
      obj[key] = obj[key] || {};
364
      buildNested(obj[key], keys, value);
365
    }
366
  }
367
  
368
  Object.entries(files).forEach(([path, queryFile]) => {
369
    if (queryFile) {
370
      const keys = path.split('.');
371
      buildNested(queries, keys, queryFile);
372
    }
373
  });
374
  
375
  return queries;
376
}
377
```
378

379
### Transaction Modes
380

381
Transaction isolation level and mode configuration.
382

383
```javascript { .api }
384
/**
385
 * Transaction Mode namespace
386
 */
387
interface ITxMode {
388
  isolationLevel: typeof isolationLevel // Isolation level enum
389
  TransactionMode: typeof TransactionMode // Transaction mode class
390
}
391

392
/**
393
 * Transaction isolation levels
394
 */
395
enum isolationLevel {
396
  none = 0,           // No specific isolation
397
  serializable = 1,   // Serializable isolation
398
  repeatableRead = 2, // Repeatable read isolation
399
  readCommitted = 3   // Read committed isolation (default)
400
}
401

402
/**
403
 * Transaction Mode configuration class
404
 */
405
class TransactionMode {
406
  constructor(options?: ITransactionModeOptions)
407
  
408
  begin(cap?: boolean): string // Generate BEGIN statement
409
}
410

411
interface ITransactionModeOptions {
412
  tiLevel?: isolationLevel // Transaction isolation level
413
  readOnly?: boolean // Read-only transaction
414
  deferrable?: boolean // Deferrable transaction (only with serializable + readOnly)
415
}
416
```
417

418
**Usage Examples:**
419

420
```javascript
421
// Basic transaction modes
422
const readOnlyMode = new pgp.txMode.TransactionMode({
423
  readOnly: true
424
});
425

426
const serializedMode = new pgp.txMode.TransactionMode({
427
  tiLevel: pgp.txMode.isolationLevel.serializable
428
});
429

430
const deferrableMode = new pgp.txMode.TransactionMode({
431
  tiLevel: pgp.txMode.isolationLevel.serializable,
432
  readOnly: true,
433
  deferrable: true
434
});
435

436
// Using transaction modes
437
await db.tx({ mode: readOnlyMode }, async t => {
438
  // Read-only transaction - no modifications allowed
439
  const users = await t.any('SELECT * FROM users');
440
  const stats = await t.one('SELECT COUNT(*) as count FROM orders', [], r => r.count);
441
  return { users, orderCount: stats };
442
});
443

444
await db.tx({ mode: serializedMode }, async t => {
445
  // Serializable transaction - highest isolation level
446
  const balance = await t.one('SELECT balance FROM accounts WHERE id = $1', [accountId], r => r.balance);
447
  
448
  if (balance >= amount) {
449
    await t.none('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, accountId]);
450
    await t.none('INSERT INTO transactions(account_id, amount) VALUES($1, $2)', [accountId, -amount]);
451
  } else {
452
    throw new Error('Insufficient funds');
453
  }
454
});
455

456
// Dynamic transaction mode selection
457
function getTransactionMode(operationType) {
458
  switch (operationType) {
459
    case 'report':
460
      return new pgp.txMode.TransactionMode({
461
        readOnly: true,
462
        tiLevel: pgp.txMode.isolationLevel.readCommitted
463
      });
464
      
465
    case 'financial':
466
      return new pgp.txMode.TransactionMode({
467
        tiLevel: pgp.txMode.isolationLevel.serializable
468
      });
469
      
470
    case 'bulk_import':
471
      return new pgp.txMode.TransactionMode({
472
        tiLevel: pgp.txMode.isolationLevel.readCommitted
473
      });
474
      
475
    default:
476
      return null; // Use default
477
  }
478
}
479

480
// Usage
481
const mode = getTransactionMode('financial');
482
await db.tx({ mode }, async t => {
483
  // Financial operations with serializable isolation
484
});
485
```
486

487
### Advanced Configuration
488

489
Advanced configuration patterns and best practices.
490

491
**Usage Examples:**
492

493
```javascript
494
// Environment-specific configuration
495
function createPgPromise(environment) {
496
  const config = {
497
    development: {
498
      capSQL: false,
499
      noWarnings: false,
500
      query: (e) => console.log('DEV Query:', e.query),
501
      error: (err, e) => console.error('DEV Error:', err.message)
502
    },
503
    
504
    production: {
505
      capSQL: true,
506
      noWarnings: true,
507
      error: (err, e) => {
508
        // Log to external service
509
        logger.error('Database error', {
510
          error: err.message,
511
          query: e.query,
512
          client: e.client.processID
513
        });
514
      }
515
    },
516
    
517
    test: {
518
      noWarnings: true
519
    }
520
  };
521
  
522
  return require('pg-promise')(config[environment] || {});
523
}
524

525
// Schema-aware configuration
526
const pgp = require('pg-promise')({
527
  schema: (dc) => {
528
    // Dynamic schema based on context
529
    if (dc.tenant) {
530
      return [`tenant_${dc.tenant}`, 'shared', 'public'];
531
    } else if (dc.userRole === 'admin') {
532
      return ['admin', 'public'];
533
    }
534
    return 'public';
535
  }
536
});
537

538
// Multi-tenant database instances
539
const tenantDbs = new Map();
540

541
function getTenantDb(tenantId) {
542
  if (!tenantDbs.has(tenantId)) {
543
    const db = pgp(connectionString, { tenant: tenantId });
544
    tenantDbs.set(tenantId, db);
545
  }
546
  return tenantDbs.get(tenantId);
547
}
548

549
// Configuration with custom promise library
550
const Bluebird = require('bluebird');
551

552
const pgp = require('pg-promise')({
553
  promiseLib: Bluebird, // Use Bluebird instead of native promises
554
  capSQL: true
555
});
556

557
// Library termination
558
process.on('exit', () => {
559
  pgp.end(); // Close all connection pools
560
});
561
```
562

563
## Types
564

565
```javascript { .api }
566
// Main library interface
567
interface IMain {
568
  // Database factory function
569
  (cn: string | IConnectionParameters, dc?: any): IDatabase
570
  
571
  // Static properties and classes
572
  readonly PreparedStatement: typeof PreparedStatement
573
  readonly ParameterizedQuery: typeof ParameterizedQuery
574
  readonly QueryFile: typeof QueryFile
575
  readonly queryResult: typeof queryResult
576
  readonly minify: typeof pgMinify
577
  readonly spex: ISpex
578
  readonly errors: typeof errors
579
  readonly utils: IUtils
580
  readonly txMode: ITxMode
581
  readonly helpers: IHelpers
582
  readonly as: IFormatting
583
  readonly pg: typeof pg
584
  
585
  // Library termination
586
  end(): void
587
}
588

589
// Library configuration
590
interface ILibConfig {
591
  version: string // pg-promise version
592
  promise: IGenericPromise // Promise library used
593
  options: IInitOptions // Initialization options
594
  pgp: IMain // Main pg-promise instance
595
  $npm: any // Internal npm modules
596
}
597

598
// Generic promise interface
599
interface IGenericPromise {
600
  (cb: (resolve: (value?: any) => void, reject: (reason?: any) => void) => void): Promise<any>
601
  resolve(value?: any): Promise<any>
602
  reject(reason?: any): Promise<any>
603
  all(iterable: any): Promise<any>
604
}
605

606
// Spex library interface for batch operations
607
interface ISpex {
608
  batch: typeof spexBatch // Batch processing
609
  page: typeof spexPage // Page processing  
610
  sequence: typeof spexSequence // Sequence processing
611
  errors: {
612
    BatchError: typeof BatchError
613
    PageError: typeof PageError
614
    SequenceError: typeof SequenceError
615
  }
616
}
617
```