0
# Utilities
1

2
The pg utilities module provides essential helper functions for SQL escaping, parameter normalization, configuration management, and safe query construction.
3

4
## Capabilities
5

6
### SQL Escaping Functions
7

8
Secure SQL string and identifier escaping to prevent injection attacks.
9

10
```javascript { .api }
11
/**
12
 * Escape a SQL identifier (table name, column name, etc.)
13
 * Wraps the identifier in double quotes and escapes internal quotes
14
 * @param str - String to escape as identifier
15
 * @returns Properly escaped SQL identifier
16
 */
17
function escapeIdentifier(str: string): string;
18

19
/**
20
 * Escape a SQL literal value
21
 * Wraps the value in single quotes and escapes internal quotes and backslashes
22
 * @param str - String to escape as literal
23
 * @returns Properly escaped SQL literal
24
 */
25
function escapeLiteral(str: string): string;
26
```
27

28
**Usage Examples:**
29

30
```javascript
31
const { escapeIdentifier, escapeLiteral } = require('pg');
32

33
// Escape table/column names
34
const tableName = escapeIdentifier('user-table');
35
console.log(tableName); // "user-table"
36

37
const columnName = escapeIdentifier('full name');
38
console.log(columnName); // "full name"
39

40
// Handle names with quotes
41
const quotedTable = escapeIdentifier('table"with"quotes');
42
console.log(quotedTable); // "table""with""quotes"
43

44
// Escape string literals
45
const userName = escapeLiteral("O'Reilly");
46
console.log(userName); // 'O''Reilly'
47

48
const description = escapeLiteral('Product with "quotes" and \\backslashes');
49
console.log(description); // E'Product with "quotes" and \\\\backslashes'
50

51
// Safe dynamic query construction
52
const table = escapeIdentifier('users');
53
const condition = escapeLiteral('admin');
54
const query = `SELECT * FROM ${table} WHERE role = ${condition}`;
55
console.log(query); // SELECT * FROM "users" WHERE role = 'admin'
56
```
57

58
### Configuration Defaults
59

60
Default configuration values for connections, pools, and client behavior.
61

62
```javascript { .api }
63
/**
64
 * Default configuration object containing standard PostgreSQL connection settings
65
 * These values are used when specific configuration is not provided
66
 */
67
interface defaults {
68
  /** Database host (default: 'localhost') */
69
  host: string;
70
  /** Database user (from environment: USERNAME on Windows, USER on Unix) */
71
  user: string;
72
  /** Database name (default: undefined) */
73
  database: string | undefined;
74
  /** Database password (default: null) */
75
  password: string | null;
76
  /** PostgreSQL connection string (default: undefined) */
77
  connectionString: string | undefined;
78
  /** Database port (default: 5432) */
79
  port: number;
80
  /** Number of rows to return from portal (default: 0 = all) */
81
  rows: number;
82
  /** Binary result mode (default: false) */
83
  binary: boolean;
84
  /** Maximum pool connections (default: 10) */
85
  max: number;
86
  /** Idle timeout in milliseconds (default: 30000) */
87
  idleTimeoutMillis: number;
88
  /** Client encoding (default: '') */
89
  client_encoding: string;
90
  /** SSL mode (default: false) */
91
  ssl: boolean;
92
  /** Application name for logging (default: undefined) */
93
  application_name: string | undefined;
94
  /** Fallback application name (default: undefined) */
95
  fallback_application_name: string | undefined;
96
  /** Connection options (default: undefined) */
97
  options: string | undefined;
98
  /** Parse input dates as UTC (default: false) */
99
  parseInputDatesAsUTC: boolean;
100
  /** Statement timeout in milliseconds (default: false = no timeout) */
101
  statement_timeout: number | false;
102
  /** Lock timeout in milliseconds (default: false = no timeout) */
103
  lock_timeout: number | false;
104
  /** Idle transaction timeout in milliseconds (default: false = no timeout) */
105
  idle_in_transaction_session_timeout: number | false;
106
  /** Query timeout in milliseconds (default: false = no timeout) */
107
  query_timeout: number | false;
108
  /** Connection timeout in milliseconds (default: 0 = no timeout) */
109
  connect_timeout: number;
110
  /** TCP keepalives enabled (default: 1) */
111
  keepalives: number;
112
  /** TCP keepalive idle time (default: 0) */
113
  keepalives_idle: number;
114
}
115
```
116

117
**Usage Examples:**
118

119
```javascript
120
const { defaults } = require('pg');
121

122
// View default values
123
console.log('Default host:', defaults.host);           // 'localhost'
124
console.log('Default port:', defaults.port);           // 5432
125
console.log('Default user:', defaults.user);           // Current system user
126
console.log('Default max connections:', defaults.max);  // 10
127

128
// Override defaults for application
129
defaults.host = 'db.example.com';
130
defaults.port = 5433;
131
defaults.max = 20;
132

133
// Use defaults in client configuration
134
const client = new Client({
135
  database: 'myapp',
136
  // host, port, user, etc. will use defaults
137
});
138

139
// Restore original defaults if needed
140
defaults.host = 'localhost';
141
defaults.port = 5432;
142
defaults.max = 10;
143
```
144

145
### BigInt Parsing Configuration
146

147
Special configuration for handling PostgreSQL bigint (int8) values.
148

149
```javascript { .api }
150
/**
151
 * Configure how bigint (int8) values are parsed
152
 * By default, bigints are returned as strings to avoid precision loss
153
 * Setting parseInt8 = true converts them to JavaScript numbers
154
 */
155
interface defaults {
156
  /**
157
   * Control bigint parsing behavior
158
   * - true: Parse int8 as JavaScript number (may lose precision)
159
   * - false: Return int8 as string (preserves precision)
160
   */
161
  parseInt8: boolean;
162
}
163
```
164

165
**Usage Examples:**
166

167
```javascript
168
const { defaults } = require('pg');
169

170
// Default behavior: bigints as strings
171
const result1 = await client.query('SELECT 9223372036854775807::bigint as big_number');
172
console.log(typeof result1.rows[0].big_number); // 'string'
173
console.log(result1.rows[0].big_number);         // '9223372036854775807'
174

175
// Enable numeric parsing for bigints
176
defaults.parseInt8 = true;
177

178
const result2 = await client.query('SELECT 123::bigint as small_number');
179
console.log(typeof result2.rows[0].small_number); // 'number'  
180
console.log(result2.rows[0].small_number);         // 123
181

182
// Warning: Large bigints may lose precision
183
const result3 = await client.query('SELECT 9223372036854775807::bigint as big_number');
184
console.log(result3.rows[0].big_number);         // May not be exact
185

186
// Disable to restore string behavior
187
defaults.parseInt8 = false;
188

189
// Also affects bigint arrays
190
const arrayResult = await client.query('SELECT ARRAY[1::bigint, 2::bigint] as numbers');
191
console.log(arrayResult.rows[0].numbers); // ['1', '2'] or [1, 2] depending on parseInt8
192
```
193

194
### Parameter Normalization
195

196
Internal utilities for normalizing query parameters and configuration.
197

198
```javascript { .api }
199
/**
200
 * Normalize query configuration from various input formats
201
 * Handles the different ways queries can be specified
202
 * @param config - Query text string or configuration object
203
 * @param values - Optional parameter values
204
 * @param callback - Optional callback function
205
 * @returns Normalized query configuration object
206
 */
207
function normalizeQueryConfig(
208
  config: string | QueryConfig, 
209
  values?: any[] | QueryCallback, 
210
  callback?: QueryCallback
211
): QueryConfig;
212

213
/**
214
 * Prepare JavaScript values for PostgreSQL transmission
215
 * Converts JS types to appropriate PostgreSQL representations
216
 * @param value - JavaScript value to prepare
217
 * @returns Value prepared for PostgreSQL
218
 */
219
function prepareValue(value: any): any;
220
```
221

222
**Usage Examples:**
223

224
```javascript
225
const { normalizeQueryConfig, prepareValue } = require('pg').utils;
226

227
// Normalize different query formats
228
const config1 = normalizeQueryConfig('SELECT * FROM users');
229
// Result: { text: 'SELECT * FROM users' }
230

231
const config2 = normalizeQueryConfig('SELECT * FROM users WHERE id = $1', [123]);
232
// Result: { text: 'SELECT * FROM users WHERE id = $1', values: [123] }
233

234
const config3 = normalizeQueryConfig('SELECT NOW()', (err, result) => {});
235
// Result: { text: 'SELECT NOW()', callback: function }
236

237
const config4 = normalizeQueryConfig({
238
  text: 'SELECT * FROM users WHERE id = $1',
239
  values: [123]
240
}, (err, result) => {});
241
// Result: { text: '...', values: [123], callback: function }
242

243
// Prepare values for PostgreSQL
244
console.log(prepareValue(null));           // null
245
console.log(prepareValue(undefined));      // null
246
console.log(prepareValue(123));            // '123'
247
console.log(prepareValue(true));           // 'true'
248
console.log(prepareValue([1, 2, 3]));      // '{1,2,3}' (PostgreSQL array)
249
console.log(prepareValue(new Date()));     // ISO timestamp string
250
console.log(prepareValue(Buffer.from('data'))); // Buffer (unchanged)
251

252
// Custom objects with toPostgres method
253
const customObj = {
254
  value: 42,
255
  toPostgres: () => JSON.stringify({ value: 42 })
256
};
257
console.log(prepareValue(customObj));      // '{"value":42}'
258
```
259

260
### Connection String Parsing
261

262
Utilities for parsing PostgreSQL connection strings.
263

264
```javascript { .api }
265
/**
266
 * Parse connection string into configuration object
267
 * Supports standard PostgreSQL connection string format
268
 * Note: Uses pg-connection-string module internally
269
 */
270
```
271

272
**Usage Examples:**
273

274
```javascript
275
// Connection strings are automatically parsed when used
276
const client = new Client({
277
  connectionString: 'postgresql://user:password@host:5432/database?ssl=true'
278
});
279

280
// Equivalent to:
281
const client2 = new Client({
282
  user: 'user',
283
  password: 'password', 
284
  host: 'host',
285
  port: 5432,
286
  database: 'database',
287
  ssl: true
288
});
289

290
// Complex connection strings
291
const connectionString = 'postgres://myuser:mypass@myhost:5433/mydb?application_name=myapp&connect_timeout=10';
292

293
// Environment variable usage
294
const client3 = new Client({
295
  connectionString: process.env.DATABASE_URL
296
});
297
```
298

299
### Error Handling Utilities
300

301
Utilities for working with PostgreSQL errors and debugging.
302

303
```javascript { .api }
304
/**
305
 * PostgreSQL error codes and utilities
306
 * Standard error codes from PostgreSQL documentation
307
 */
308
interface PostgreSQLErrorCodes {
309
  // Class 02 — No Data  
310
  NO_DATA: '02000';
311
  NO_ADDITIONAL_DYNAMIC_RESULT_SETS_RETURNED: '02001';
312
  
313
  // Class 08 — Connection Exception
314
  CONNECTION_EXCEPTION: '08000';
315
  CONNECTION_DOES_NOT_EXIST: '08003';
316
  CONNECTION_FAILURE: '08006';
317
  
318
  // Class 23 — Integrity Constraint Violation
319
  INTEGRITY_CONSTRAINT_VIOLATION: '23000';
320
  RESTRICT_VIOLATION: '23001';
321
  NOT_NULL_VIOLATION: '23502';
322
  FOREIGN_KEY_VIOLATION: '23503';
323
  UNIQUE_VIOLATION: '23505';
324
  CHECK_VIOLATION: '23514';
325
  
326
  // Class 42 — Syntax Error or Access Rule Violation
327
  SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION: '42000';
328
  SYNTAX_ERROR: '42601';
329
  INSUFFICIENT_PRIVILEGE: '42501';
330
  UNDEFINED_COLUMN: '42703';
331
  UNDEFINED_TABLE: '42P01';
332
}
333
```
334

335
**Usage Examples:**
336

337
```javascript
338
const { DatabaseError } = require('pg');
339

340
// Error handling with specific error codes
341
try {
342
  await client.query('SELECT * FROM nonexistent_table');
343
} catch (err) {
344
  if (err instanceof DatabaseError) {
345
    switch (err.code) {
346
      case '42P01': // undefined_table
347
        console.error('Table does not exist:', err.message);
348
        break;
349
      case '42703': // undefined_column  
350
        console.error('Column does not exist:', err.message);
351
        break;
352
      case '23505': // unique_violation
353
        console.error('Unique constraint violation:', err.detail);
354
        break;
355
      case '23503': // foreign_key_violation
356
        console.error('Foreign key constraint violation:', err.detail);
357
        break;
358
      default:
359
        console.error('Database error:', err.code, err.message);
360
    }
361
  } else {
362
    console.error('Non-database error:', err);
363
  }
364
}
365

366
// Extract useful error information
367
function handleDatabaseError(err) {
368
  if (err instanceof DatabaseError) {
369
    return {
370
      code: err.code,
371
      message: err.message,
372
      detail: err.detail,
373
      hint: err.hint,
374
      position: err.position,
375
      internalPosition: err.internalPosition,
376
      internalQuery: err.internalQuery,
377
      where: err.where,
378
      schema: err.schema,
379
      table: err.table,
380
      column: err.column,
381
      dataType: err.dataType,
382
      constraint: err.constraint
383
    };
384
  }
385
  return { message: err.message };
386
}
387
```
388

389
### Environment Configuration
390

391
Helper patterns for environment-based configuration.
392

393
```javascript { .api }
394
/**
395
 * Common patterns for environment-based configuration
396
 * Not part of pg core but useful patterns for configuration
397
 */
398
```
399

400
**Usage Examples:**
401

402
```javascript
403
// Environment-based configuration
404
function createDbConfig() {
405
  return {
406
    host: process.env.DB_HOST || 'localhost',
407
    port: parseInt(process.env.DB_PORT || '5432', 10),
408
    database: process.env.DB_NAME || 'postgres',
409
    user: process.env.DB_USER || 'postgres',
410
    password: process.env.DB_PASSWORD,
411
    ssl: process.env.DB_SSL === 'true',
412
    max: parseInt(process.env.DB_POOL_SIZE || '10', 10),
413
    idleTimeoutMillis: parseInt(process.env.DB_IDLE_TIMEOUT || '30000', 10),
414
    connectionTimeoutMillis: parseInt(process.env.DB_CONNECT_TIMEOUT || '2000', 10),
415
  };
416
}
417

418
// Usage
419
const pool = new Pool(createDbConfig());
420

421
// Development/production configuration
422
const config = {
423
  development: {
424
    host: 'localhost',
425
    database: 'myapp_dev',
426
    ssl: false
427
  },
428
  production: {
429
    connectionString: process.env.DATABASE_URL,
430
    ssl: { rejectUnauthorized: false }
431
  }
432
};
433

434
const dbConfig = config[process.env.NODE_ENV || 'development'];
435
const client = new Client(dbConfig);
436
```
437

438
## Utility Types
439

440
```javascript { .api }
441
/**
442
 * Type definitions for utility functions and configuration
443
 */
444
type QueryConfig = {
445
  text: string;
446
  values?: any[];
447
  name?: string;
448
  rowMode?: 'array' | 'object';
449
  types?: TypeOverrides;
450
  binary?: boolean;
451
  portal?: string;
452
  rows?: number;
453
  callback?: QueryCallback;
454
};
455

456
type QueryCallback = (err: Error | null, result: QueryResult) => void;
457

458
interface DefaultsConfig {
459
  host: string;
460
  user: string;
461
  database: string | undefined;
462
  password: string | null;
463
  port: number;
464
  connectionString: string | undefined;
465
  rows: number;
466
  binary: boolean;
467
  max: number;
468
  idleTimeoutMillis: number;
469
  client_encoding: string;
470
  ssl: boolean;
471
  application_name: string | undefined;
472
  parseInputDatesAsUTC: boolean;
473
  statement_timeout: number | false;
474
  query_timeout: number | false;
475
  connect_timeout: number;
476
  parseInt8: boolean;
477
}
478
```