0
# Error Handling
1

2
Comprehensive error types for different failure scenarios with detailed error information. pg-promise provides specialized error classes for query results, file operations, and statement preparation, along with error codes and comprehensive error context.
3

4
## Capabilities
5

6
### Query Result Errors
7

8
Errors related to unexpected query result counts or data validation.
9

10
```javascript { .api }
11
/**
12
 * Query Result Error - thrown when query results don't match expectations
13
 */
14
class QueryResultError extends Error {
15
  // Standard error properties
16
  name: string // Error name: 'QueryResultError'
17
  message: string // Error message
18
  stack: string // Stack trace
19
  
20
  // Extended properties
21
  result: IResult // Full query result object
22
  received: number // Number of rows received
23
  code: queryResultErrorCode // Specific error code
24
  query: string // SQL query that caused error
25
  values: any // Parameter values used
26
  
27
  toString(): string // String representation
28
}
29

30
/**
31
 * Query Result Error Codes
32
 */
33
enum queryResultErrorCode {
34
  noData = 0,    // No data returned when some was expected
35
  notEmpty = 1,  // Data returned when none was expected  
36
  multiple = 2   // Multiple rows returned when one was expected
37
}
38
```
39

40
**Usage Examples:**
41

42
```javascript
43
try {
44
  // This will throw QueryResultError if no user found
45
  const user = await db.one('SELECT * FROM users WHERE id = $1', [999]);
46
} catch (error) {
47
  if (error instanceof pgp.errors.QueryResultError) {
48
    console.log('Query result error:', {
49
      code: error.code,
50
      received: error.received,
51
      query: error.query,
52
      values: error.values
53
    });
54
    
55
    switch (error.code) {
56
      case pgp.errors.queryResultErrorCode.noData:
57
        console.log('No user found with that ID');
58
        break;
59
      case pgp.errors.queryResultErrorCode.multiple:
60
        console.log('Multiple users found, expected one');
61
        break;
62
      case pgp.errors.queryResultErrorCode.notEmpty:
63
        console.log('Expected no results, but got data');
64
        break;
65
    }
66
  }
67
}
68

69
// Handling different result expectations
70
try {
71
  await db.none('INSERT INTO users(name, email) VALUES($1, $2) RETURNING id', 
72
    ['John', 'john@example.com']);
73
} catch (error) {
74
  if (error.code === pgp.errors.queryResultErrorCode.notEmpty) {
75
    console.log('INSERT returned data when none expected');
76
    // This happens when using RETURNING with .none()
77
  }
78
}
79

80
// Detecting multiple results
81
try {
82
  const user = await db.one('SELECT * FROM users WHERE name = $1', ['John']);
83
} catch (error) {
84
  if (error.code === pgp.errors.queryResultErrorCode.multiple) {
85
    console.log(`Found ${error.received} users named John, expected exactly 1`);
86
    // Switch to .many() or add more specific WHERE conditions
87
  }
88
}
89
```
90

91
### Query File Errors
92

93
Errors related to SQL file loading, parsing, and processing.
94

95
```javascript { .api }
96
/**
97
 * Query File Error - thrown when SQL files cannot be loaded or parsed
98
 */
99
class QueryFileError extends Error {
100
  // Standard error properties
101
  name: string // Error name: 'QueryFileError'
102
  message: string // Error message
103
  stack: string // Stack trace
104
  
105
  // Extended properties
106
  file: string // File path that caused the error
107
  options: IQueryFileOptions // File processing options used
108
  error: SQLParsingError // Underlying SQL parsing error (if applicable)
109
  
110
  toString(level?: number): string // String representation with indentation
111
}
112

113
/**
114
 * SQL Parsing Error from pg-minify
115
 */
116
interface SQLParsingError {
117
  name: string // Error name
118
  message: string // Error message
119
  position: number // Character position in SQL
120
  line: number // Line number
121
  column: number // Column number
122
}
123
```
124

125
**Usage Examples:**
126

127
```javascript
128
// File loading errors
129
const queryFile = new pgp.QueryFile('/path/to/nonexistent.sql');
130
if (queryFile.error) {
131
  console.error('File error:', {
132
    type: queryFile.error.name,
133
    message: queryFile.error.message,
134
    file: queryFile.error.file
135
  });
136
}
137

138
// SQL parsing errors (with minification)
139
const complexQuery = new pgp.QueryFile('sql/invalid-syntax.sql', { minify: true });
140
if (complexQuery.error && complexQuery.error.error) {
141
  const parseError = complexQuery.error.error;
142
  console.error('SQL parsing error:', {
143
    message: parseError.message,
144
    line: parseError.line,
145
    column: parseError.column,
146
    position: parseError.position
147
  });
148
}
149

150
// Error handling in queries
151
try {
152
  const results = await db.any(new pgp.QueryFile('sql/problematic-query.sql'));
153
} catch (error) {
154
  if (error instanceof pgp.errors.QueryFileError) {
155
    console.error('Query file error:', {
156
      file: error.file,
157
      options: error.options,
158
      message: error.message
159
    });
160
    
161
    if (error.error) {
162
      console.error('Parsing details:', {
163
        line: error.error.line,
164
        column: error.error.column,
165
        position: error.error.position
166
      });
167
    }
168
  }
169
}
170

171
// Development-time error checking
172
function validateQueryFiles(directory) {
173
  const files = pgp.utils.enumSql(directory, { recursive: true });
174
  const errors = [];
175
  
176
  for (const [name, path] of Object.entries(files)) {
177
    const queryFile = new pgp.QueryFile(path, { minify: true });
178
    if (queryFile.error) {
179
      errors.push({
180
        name,
181
        path,
182
        error: queryFile.error.message
183
      });
184
    }
185
  }
186
  
187
  return errors;
188
}
189
```
190

191
### Prepared Statement Errors
192

193
Errors related to prepared statement configuration and validation.
194

195
```javascript { .api }
196
/**
197
 * Prepared Statement Error - thrown when prepared statements are invalid
198
 */
199
class PreparedStatementError extends Error {
200
  // Standard error properties
201
  name: string // Error name: 'PreparedStatementError'
202
  message: string // Error message
203
  stack: string // Stack trace
204
  
205
  // Extended properties
206
  error: QueryFileError // Underlying QueryFile error (if statement uses QueryFile)
207
  
208
  toString(level?: number): string // String representation with indentation
209
}
210
```
211

212
**Usage Examples:**
213

214
```javascript
215
// Invalid prepared statement
216
const invalidStmt = new pgp.PreparedStatement({
217
  name: '', // Empty name will cause error
218
  text: 'SELECT * FROM users'
219
});
220

221
const result = invalidStmt.parse();
222
if (result instanceof pgp.errors.PreparedStatementError) {
223
  console.error('Prepared statement error:', result.message);
224
}
225

226
// Prepared statement with invalid QueryFile
227
const fileStmt = new pgp.PreparedStatement({
228
  name: 'get-users',
229
  text: new pgp.QueryFile('sql/invalid.sql')
230
});
231

232
try {
233
  await db.any(fileStmt);
234
} catch (error) {
235
  if (error instanceof pgp.errors.PreparedStatementError) {
236
    console.error('Prepared statement error:', error.message);
237
    
238
    if (error.error) {
239
      console.error('Underlying file error:', error.error.message);
240
    }
241
  }
242
}
243

244
// Validation before execution
245
function validatePreparedStatement(stmt) {
246
  const parsed = stmt.parse();
247
  
248
  if (parsed instanceof pgp.errors.PreparedStatementError) {
249
    return {
250
      valid: false,
251
      error: parsed.message,
252
      details: parsed.error?.message
253
    };
254
  }
255
  
256
  return {
257
    valid: true,
258
    statement: parsed
259
  };
260
}
261

262
const stmt = new pgp.PreparedStatement({
263
  name: 'test-stmt',
264
  text: 'SELECT * FROM users WHERE id = $1'
265
});
266

267
const validation = validatePreparedStatement(stmt);
268
if (validation.valid) {
269
  console.log('Statement is valid:', validation.statement.name);
270
} else {
271
  console.error('Statement is invalid:', validation.error);
272
}
273
```
274

275
### Parameterized Query Errors
276

277
Errors related to parameterized query configuration and validation.
278

279
```javascript { .api }
280
/**
281
 * Parameterized Query Error - thrown when parameterized queries are invalid
282
 */
283
class ParameterizedQueryError extends Error {
284
  // Standard error properties
285
  name: string // Error name: 'ParameterizedQueryError'
286
  message: string // Error message
287
  stack: string // Stack trace
288
  
289
  // Extended properties
290
  error: QueryFileError // Underlying QueryFile error (if query uses QueryFile)
291
  
292
  toString(level?: number): string // String representation with indentation
293
}
294
```
295

296
**Usage Examples:**
297

298
```javascript
299
// Invalid parameterized query
300
const invalidQuery = new pgp.ParameterizedQuery({
301
  text: '', // Empty text will cause error
302
  values: [1, 2, 3]
303
});
304

305
const result = invalidQuery.parse();
306
if (result instanceof pgp.errors.ParameterizedQueryError) {
307
  console.error('Parameterized query error:', result.message);
308
}
309

310
// Query with file error
311
const fileQuery = new pgp.ParameterizedQuery({
312
  text: new pgp.QueryFile('sql/broken.sql'),
313
  values: [123]
314
});
315

316
try {
317
  await db.any(fileQuery);
318
} catch (error) {
319
  if (error instanceof pgp.errors.ParameterizedQueryError) {
320
    console.error('Parameterized query error:', error.message);
321
    
322
    if (error.error && error.error.error) {
323
      const parseError = error.error.error;
324
      console.error('SQL parsing error at line', parseError.line, ':', parseError.message);
325
    }
326
  }
327
}
328

329
// Parameter count validation
330
function validateParameterCount(query, expectedParams) {
331
  const paramCount = (query.text.match(/\$/g) || []).length;
332
  const providedCount = query.values ? query.values.length : 0;
333
  
334
  if (paramCount !== providedCount) {
335
    console.warn(`Parameter mismatch: query has ${paramCount} parameters, but ${providedCount} values provided`);
336
    return false;
337
  }
338
  
339
  if (paramCount !== expectedParams) {
340
    console.warn(`Expected ${expectedParams} parameters, but query has ${paramCount}`);
341
    return false;
342
  }
343
  
344
  return true;
345
}
346
```
347

348
### Database Connection Errors
349

350
Standard PostgreSQL and node-postgres errors enhanced with context.
351

352
**Usage Examples:**
353

354
```javascript
355
// Connection error handling
356
try {
357
  const db = pgp('postgres://invalid:invalid@nonexistent:5432/db');
358
  await db.any('SELECT 1');
359
} catch (error) {
360
  console.error('Connection error:', {
361
    code: error.code,     // ECONNREFUSED, ENOTFOUND, etc.
362
    message: error.message,
363
    host: error.host,
364
    port: error.port
365
  });
366
}
367

368
// Query timeout handling
369
try {
370
  await db.any('SELECT pg_sleep(10)'); // Long-running query
371
} catch (error) {
372
  if (error.code === '57014') { // Query timeout
373
    console.error('Query timed out');
374
  }
375
}
376

377
// Constraint violation handling
378
try {
379
  await db.none('INSERT INTO users(email) VALUES($1)', ['duplicate@example.com']);
380
} catch (error) {
381
  if (error.code === '23505') { // Unique violation
382
    console.error('Email already exists:', error.detail);
383
  } else if (error.code === '23503') { // Foreign key violation
384
    console.error('Foreign key constraint violated:', error.detail);
385
  } else if (error.code === '23514') { // Check constraint violation
386
    console.error('Check constraint violated:', error.detail);
387
  }
388
}
389

390
// Transaction rollback handling
391
try {
392
  await db.tx(async t => {
393
    await t.none('INSERT INTO users(name) VALUES($1)', ['John']);
394
    await t.none('INSERT INTO invalid_table(data) VALUES($1)', ['test']); // Will fail
395
  });
396
} catch (error) {
397
  console.error('Transaction failed:', error.message);
398
  // Transaction automatically rolled back
399
}
400
```
401

402
### Error Context and Debugging
403

404
Comprehensive error context for debugging and monitoring.
405

406
**Usage Examples:**
407

408
```javascript
409
// Global error handling with context
410
const pgp = require('pg-promise')({
411
  error: (err, e) => {
412
    console.error('Database error occurred:', {
413
      error: {
414
        name: err.name,
415
        message: err.message,
416
        code: err.code,
417
        severity: err.severity,
418
        detail: err.detail,
419
        hint: err.hint,
420
        position: err.position,
421
        where: err.where,
422
        schema: err.schema,
423
        table: err.table,
424
        column: err.column,
425
        dataType: err.dataType,
426
        constraint: err.constraint
427
      },
428
      context: {
429
        query: e.query,
430
        params: e.params,
431
        values: e.values,
432
        client: e.client.processID,
433
        database: e.client.database,
434
        user: e.client.user,
435
        task: e.ctx?.tag,
436
        level: e.ctx?.level,
437
        duration: e.ctx ? Date.now() - e.ctx.start.getTime() : null
438
      }
439
    });
440
  }
441
});
442

443
// Error logging wrapper
444
async function executeWithLogging(operation, context = {}) {
445
  const start = Date.now();
446
  
447
  try {
448
    const result = await operation();
449
    console.log('Operation succeeded:', {
450
      ...context,
451
      duration: Date.now() - start,
452
      success: true
453
    });
454
    return result;
455
  } catch (error) {
456
    console.error('Operation failed:', {
457
      ...context,
458
      duration: Date.now() - start,
459
      success: false,
460
      error: {
461
        type: error.constructor.name,
462
        message: error.message,
463
        code: error.code
464
      }
465
    });
466
    throw error;
467
  }
468
}
469

470
// Usage
471
const users = await executeWithLogging(
472
  () => db.any('SELECT * FROM users WHERE active = $1', [true]),
473
  { operation: 'get-active-users', module: 'user-service' }
474
);
475
```
476

477
### Error Recovery Patterns
478

479
Common patterns for error handling and recovery.
480

481
**Usage Examples:**
482

483
```javascript
484
// Retry with exponential backoff
485
async function retryQuery(queryFunc, maxRetries = 3, baseDelay = 1000) {
486
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
487
    try {
488
      return await queryFunc();
489
    } catch (error) {
490
      const isRetryable = error.code === 'ECONNRESET' || 
491
                         error.code === 'ECONNREFUSED' ||
492
                         error.code === '40001'; // Serialization failure
493
                         
494
      if (!isRetryable || attempt === maxRetries) {
495
        throw error;
496
      }
497
      
498
      const delay = baseDelay * Math.pow(2, attempt - 1);
499
      console.log(`Query failed (attempt ${attempt}/${maxRetries}), retrying in ${delay}ms...`);
500
      await new Promise(resolve => setTimeout(resolve, delay));
501
    }
502
  }
503
}
504

505
// Graceful degradation
506
async function getUserWithFallback(userId) {
507
  try {
508
    // Try to get user with profile
509
    return await db.one(`
510
      SELECT u.*, p.bio, p.avatar_url 
511
      FROM users u 
512
      LEFT JOIN profiles p ON u.id = p.user_id 
513
      WHERE u.id = $1
514
    `, [userId]);
515
  } catch (error) {
516
    if (error instanceof pgp.errors.QueryResultError && 
517
        error.code === pgp.errors.queryResultErrorCode.noData) {
518
      return null; // User not found
519
    }
520
    
521
    // On other errors, try simpler query
522
    try {
523
      console.warn('Profile query failed, falling back to basic user data');
524
      return await db.one('SELECT * FROM users WHERE id = $1', [userId]);
525
    } catch (fallbackError) {
526
      if (fallbackError instanceof pgp.errors.QueryResultError && 
527
          fallbackError.code === pgp.errors.queryResultErrorCode.noData) {
528
        return null;
529
      }
530
      throw fallbackError; // Re-throw if not a "not found" error
531
    }
532
  }
533
}
534

535
// Circuit breaker pattern
536
class DatabaseCircuitBreaker {
537
  constructor(threshold = 5, timeout = 60000) {
538
    this.failureThreshold = threshold;
539
    this.timeout = timeout;
540
    this.failureCount = 0;
541
    this.lastFailureTime = null;
542
    this.state = 'CLOSED'; // CLOSED, OPEN, HALF_OPEN
543
  }
544
  
545
  async execute(operation) {
546
    if (this.state === 'OPEN') {
547
      if (Date.now() - this.lastFailureTime > this.timeout) {
548
        this.state = 'HALF_OPEN';
549
      } else {
550
        throw new Error('Circuit breaker is OPEN');
551
      }
552
    }
553
    
554
    try {
555
      const result = await operation();
556
      this.onSuccess();
557
      return result;
558
    } catch (error) {
559
      this.onFailure();
560
      throw error;
561
    }
562
  }
563
  
564
  onSuccess() {
565
    this.failureCount = 0;
566
    this.state = 'CLOSED';
567
  }
568
  
569
  onFailure() {
570
    this.failureCount++;
571
    this.lastFailureTime = Date.now();
572
    
573
    if (this.failureCount >= this.failureThreshold) {
574
      this.state = 'OPEN';
575
    }
576
  }
577
}
578

579
const circuitBreaker = new DatabaseCircuitBreaker();
580

581
// Usage
582
const users = await circuitBreaker.execute(() => 
583
  db.any('SELECT * FROM users WHERE active = true')
584
);
585
```
586

587
## Types
588

589
```javascript { .api }
590
// Error interfaces
591
interface IErrorContext {
592
  client: IClient // Database client
593
  cn: any // Connection configuration
594
  dc: any // Database context
595
  query: any // Query being executed
596
  params: any // Query parameters
597
  values: any // Parameter values
598
  queryFilePath?: string // Query file path (if applicable)
599
  ctx: ITaskContext // Task context
600
}
601

602
// Standard PostgreSQL error properties
603
interface IPostgreSQLError {
604
  name: string // Error name
605
  message: string // Error message
606
  severity: string // Error severity (ERROR, FATAL, etc.)
607
  code: string // PostgreSQL error code
608
  detail?: string // Error detail
609
  hint?: string // Error hint
610
  position?: string // Error position in query
611
  internalPosition?: string // Internal error position
612
  internalQuery?: string // Internal query
613
  where?: string // Error context
614
  schema?: string // Schema name
615
  table?: string // Table name  
616
  column?: string // Column name
617
  dataType?: string // Data type name
618
  constraint?: string // Constraint name
619
  file?: string // Source file name
620
  line?: string // Source line number
621
  routine?: string // Function name
622
}
623

624
// Result interface
625
interface IResult {
626
  rows: any[] // Result rows
627
  rowCount: number // Number of rows
628
  command: string // SQL command
629
  oid: number // Object ID
630
  fields: IFieldInfo[] // Field information
631
}
632

633
interface IFieldInfo {
634
  name: string // Field name
635
  tableID: number // Table OID
636
  columnID: number // Column number
637
  dataTypeID: number // Data type OID
638
  dataTypeSize: number // Data type size
639
  dataTypeModifier: number // Type modifier
640
  format: string // Format code
641
}
642
```