0
# Error Handling
1

2
Comprehensive error handling and PostgreSQL error types with detailed diagnostic information and recovery strategies.
3

4
## Capabilities
5

6
### PostgresError Class
7

8
Specialized error class providing detailed PostgreSQL server error information.
9

10
```javascript { .api }
11
/**
12
 * PostgreSQL-specific error class
13
 * Extends standard Error with PostgreSQL server error details
14
 */
15
class PostgresError extends Error {
16
  /** Error severity level */
17
  severity: string;
18
  
19
  /** Localized severity level */
20
  severity_local: string;
21
  
22
  /** PostgreSQL error code (SQLSTATE) */
23
  code: string;
24
  
25
  /** Error message */
26
  message: string;
27
  
28
  /** Additional error details */
29
  detail?: string;
30
  
31
  /** Hint for resolving the error */
32
  hint?: string;
33
  
34
  /** Character position in query where error occurred */
35
  position?: string;
36
  
37
  /** Internal query position */
38
  internal_position?: string;
39
  
40
  /** Internal query that caused error */
41
  internal_query?: string;
42
  
43
  /** Where in source code error occurred */
44
  where?: string;
45
  
46
  /** Schema name related to error */
47
  schema_name?: string;
48
  
49
  /** Table name related to error */
50
  table_name?: string;
51
  
52
  /** Column name related to error */
53
  column_name?: string;
54
  
55
  /** Data type name related to error */
56
  datatype_name?: string;
57
  
58
  /** Constraint name that was violated */
59
  constraint_name?: string;
60
  
61
  /** Source file where error occurred */
62
  file?: string;
63
  
64
  /** Line number in source file */
65
  line?: string;
66
  
67
  /** Routine name where error occurred */
68
  routine?: string;
69
  
70
  /** Query that caused the error (when debug enabled) */
71
  query?: string;
72
  
73
  /** Parameters used in query (when debug enabled) */
74
  parameters?: any[];
75
}
76
```
77

78
**Usage Examples:**
79

80
```javascript
81
try {
82
  await sql`INSERT INTO users (email) VALUES (${email})`;
83
} catch (error) {
84
  if (error instanceof PostgresError) {
85
    console.log('PostgreSQL Error Details:');
86
    console.log('Code:', error.code);
87
    console.log('Message:', error.message);
88
    console.log('Detail:', error.detail);
89
    console.log('Hint:', error.hint);
90
    
91
    if (error.constraint_name) {
92
      console.log('Constraint violated:', error.constraint_name);
93
    }
94
    
95
    if (error.position) {
96
      console.log('Error at position:', error.position);
97
    }
98
  } else {
99
    console.log('Unexpected error:', error);
100
  }
101
}
102
```
103

104
### Connection Error Types
105

106
Additional error types for connection-related failures and client-side errors.
107

108
```javascript { .api }
109
/**
110
 * Connection-related error class
111
 * For network and connection pool errors
112
 */
113
interface ConnectionError extends Error {
114
  code: 
115
    | 'CONNECTION_DESTROYED'
116
    | 'CONNECT_TIMEOUT' 
117
    | 'CONNECTION_CLOSED'
118
    | 'CONNECTION_ENDED';
119
  errno: string;
120
  address: string;
121
  port?: number;
122
}
123

124
/**
125
 * Protocol or feature not supported error
126
 */
127
interface NotSupportedError extends Error {
128
  code: 'MESSAGE_NOT_SUPPORTED';
129
  name: string;
130
}
131

132
/**
133
 * Generic client-side errors
134
 */
135
interface GenericError extends Error {
136
  code:
137
    | '57014'  // canceling statement due to user request
138
    | 'NOT_TAGGED_CALL'
139
    | 'UNDEFINED_VALUE'
140
    | 'MAX_PARAMETERS_EXCEEDED'
141
    | 'SASL_SIGNATURE_MISMATCH'
142
    | 'UNSAFE_TRANSACTION';
143
  message: string;
144
}
145

146
/**
147
 * Authentication method not implemented error
148
 */
149
interface AuthNotImplementedError extends Error {
150
  code: 'AUTH_TYPE_NOT_IMPLEMENTED';
151
  type: number | string;
152
  message: string;
153
}
154
```
155

156
**Usage Examples:**
157

158
```javascript
159
try {
160
  await sql`SELECT * FROM users`;
161
} catch (error) {
162
  if (error.code === 'CONNECTION_DESTROYED') {
163
    console.log('Connection was destroyed, reconnecting...');
164
    // Handle reconnection logic
165
  } else if (error.code === 'CONNECT_TIMEOUT') {
166
    console.log('Connection timeout, retrying...');
167
    // Handle timeout retry logic
168
  } else if (error.code === 'NOT_TAGGED_CALL') {
169
    console.log('Must use tagged template literals');
170
    // Fix query syntax
171
  } else if (error.code === 'AUTH_TYPE_NOT_IMPLEMENTED') {
172
    console.log('Authentication method not supported:', error.type);
173
    // Handle auth configuration
174
  }
175
}
176
```
177

178
### Common Error Codes
179

180
Handle specific PostgreSQL error conditions with appropriate responses.
181

182
```javascript { .api }
183
/**
184
 * Common PostgreSQL error codes (SQLSTATE)
185
 */
186
const ERROR_CODES = {
187
  // Connection errors
188
  CONNECTION_EXCEPTION: '08000',
189
  CONNECTION_DOES_NOT_EXIST: '08003',
190
  CONNECTION_FAILURE: '08006',
191
  
192
  // Data errors  
193
  DATA_EXCEPTION: '22000',
194
  NUMERIC_VALUE_OUT_OF_RANGE: '22003',
195
  INVALID_DATETIME_FORMAT: '22007',
196
  DIVISION_BY_ZERO: '22012',
197
  
198
  // Integrity constraint violations
199
  INTEGRITY_CONSTRAINT_VIOLATION: '23000',
200
  RESTRICT_VIOLATION: '23001', 
201
  NOT_NULL_VIOLATION: '23502',
202
  FOREIGN_KEY_VIOLATION: '23503',
203
  UNIQUE_VIOLATION: '23505',
204
  CHECK_VIOLATION: '23514',
205
  
206
  // Transaction errors
207
  TRANSACTION_ROLLBACK: '25000',
208
  SERIALIZATION_FAILURE: '25001',
209
  DEADLOCK_DETECTED: '25P01',
210
  
211
  // System errors
212
  SYSTEM_ERROR: '58000',
213
  DISK_FULL: '58030',
214
  INSUFFICIENT_RESOURCES: '53000',
215
  OUT_OF_MEMORY: '53200',
216
  TOO_MANY_CONNECTIONS: '53300',
217
  
218
  // Security errors
219
  INSUFFICIENT_PRIVILEGE: '42501',
220
  INVALID_AUTHORIZATION: '28000',
221
  INVALID_PASSWORD: '28P01'
222
};
223
```
224

225
**Usage Examples:**
226

227
```javascript
228
async function handleDatabaseOperation() {
229
  try {
230
    await sql`INSERT INTO orders (user_id, product_id) VALUES (${userId}, ${productId})`;
231
  } catch (error) {
232
    if (error.code === '23503') {
233
      // Foreign key violation
234
      throw new Error('Invalid user or product ID');
235
    } else if (error.code === '23505') {
236
      // Unique violation
237
      throw new Error('Duplicate order detected');
238
    } else if (error.code === '23502') {
239
      // Not null violation
240
      throw new Error('Required field is missing');
241
    } else {
242
      // Unexpected error
243
      throw error;
244
    }
245
  }
246
}
247

248
// More comprehensive error handling
249
async function createUser(userData) {
250
  try {
251
    const [user] = await sql`
252
      INSERT INTO users (email, username, password_hash)
253
      VALUES (${userData.email}, ${userData.username}, ${userData.passwordHash})
254
      RETURNING *
255
    `;
256
    return user;
257
  } catch (error) {
258
    switch (error.code) {
259
      case '23505':
260
        if (error.constraint_name === 'users_email_unique') {
261
          throw new Error('Email address already registered');
262
        } else if (error.constraint_name === 'users_username_unique') {
263
          throw new Error('Username already taken');
264
        }
265
        break;
266
        
267
      case '23502':
268
        throw new Error(`Required field missing: ${error.column_name}`);
269
        
270
      case '22001':
271
        throw new Error('Input data too long for field');
272
        
273
      case '53300':
274
        throw new Error('Server too busy, please try again later');
275
        
276
      default:
277
        console.error('Unexpected database error:', error);
278
        throw new Error('User creation failed');
279
    }
280
  }
281
}
282
```
283

284
## Error Recovery Strategies
285

286
### Automatic Retry Logic
287

288
Implement smart retry logic for transient errors.
289

290
```javascript { .api }
291
/**
292
 * Retry configuration options
293
 */
294
interface RetryOptions {
295
  /** Maximum number of retry attempts */
296
  maxAttempts: number;
297
  
298
  /** Base delay between retries in milliseconds */
299
  baseDelay: number;
300
  
301
  /** Whether to use exponential backoff */
302
  exponentialBackoff: boolean;
303
  
304
  /** Maximum delay between retries */
305
  maxDelay: number;
306
  
307
  /** Error codes that should trigger retry */
308
  retryableErrors: string[];
309
}
310
```
311

312
**Usage Examples:**
313

314
```javascript
315
class DatabaseRetryHandler {
316
  constructor(options = {}) {
317
    this.options = {
318
      maxAttempts: 3,
319
      baseDelay: 1000,
320
      exponentialBackoff: true,
321
      maxDelay: 10000,
322
      retryableErrors: [
323
        '08000', // Connection exception
324
        '08003', // Connection does not exist
325
        '08006', // Connection failure
326
        '25001', // Serialization failure
327
        '25P01', // Deadlock detected
328
        '53000', // Insufficient resources
329
        '53200', // Out of memory
330
        '53300', // Too many connections
331
      ],
332
      ...options
333
    };
334
  }
335
  
336
  async executeWithRetry(operation, context = {}) {
337
    let lastError;
338
    
339
    for (let attempt = 1; attempt <= this.options.maxAttempts; attempt++) {
340
      try {
341
        return await operation();
342
      } catch (error) {
343
        lastError = error;
344
        
345
        if (!this.shouldRetry(error, attempt)) {
346
          throw error;
347
        }
348
        
349
        const delay = this.calculateDelay(attempt);
350
        console.log(`Operation failed (attempt ${attempt}), retrying in ${delay}ms:`, error.message);
351
        
352
        await this.sleep(delay);
353
      }
354
    }
355
    
356
    throw lastError;
357
  }
358
  
359
  shouldRetry(error, attempt) {
360
    // Don't retry if max attempts reached
361
    if (attempt >= this.options.maxAttempts) {
362
      return false;
363
    }
364
    
365
    // Only retry for PostgreSQL errors with retryable codes
366
    if (!(error instanceof PostgresError)) {
367
      return false;
368
    }
369
    
370
    return this.options.retryableErrors.includes(error.code);
371
  }
372
  
373
  calculateDelay(attempt) {
374
    let delay = this.options.baseDelay;
375
    
376
    if (this.options.exponentialBackoff) {
377
      delay *= Math.pow(2, attempt - 1);
378
    }
379
    
380
    return Math.min(delay, this.options.maxDelay);
381
  }
382
  
383
  sleep(ms) {
384
    return new Promise(resolve => setTimeout(resolve, ms));
385
  }
386
}
387

388
// Usage
389
const retryHandler = new DatabaseRetryHandler({
390
  maxAttempts: 5,
391
  baseDelay: 500,
392
  exponentialBackoff: true
393
});
394

395
const result = await retryHandler.executeWithRetry(async () => {
396
  return await sql`SELECT * FROM users WHERE id = ${userId}`;
397
});
398
```
399

400
### Transaction Error Handling
401

402
Handle transaction-specific errors and implement proper rollback strategies.
403

404
```javascript
405
async function performComplexTransaction() {
406
  let transaction;
407
  
408
  try {
409
    transaction = await sql.begin(async (sql) => {
410
      // Step 1: Create order
411
      const [order] = await sql`
412
        INSERT INTO orders (user_id, total_amount)
413
        VALUES (${userId}, ${totalAmount})
414
        RETURNING *
415
      `;
416
      
417
      // Step 2: Create order items
418
      for (const item of orderItems) {
419
        await sql`
420
          INSERT INTO order_items (order_id, product_id, quantity, price)
421
          VALUES (${order.id}, ${item.productId}, ${item.quantity}, ${item.price})
422
        `;
423
        
424
        // Step 3: Update inventory
425
        const [updated] = await sql`
426
          UPDATE products 
427
          SET stock_quantity = stock_quantity - ${item.quantity}
428
          WHERE id = ${item.productId} AND stock_quantity >= ${item.quantity}
429
          RETURNING stock_quantity
430
        `;
431
        
432
        if (!updated) {
433
          throw new Error(`Insufficient stock for product ${item.productId}`);
434
        }
435
      }
436
      
437
      return order;
438
    });
439
    
440
    return transaction;
441
    
442
  } catch (error) {
443
    console.error('Transaction failed:', error.message);
444
    
445
    if (error.code === '25001') {
446
      // Serialization failure - retry the entire transaction
447
      console.log('Serialization conflict detected, retrying transaction...');
448
      return performComplexTransaction();
449
    } else if (error.code === '25P01') {
450
      // Deadlock detected
451
      console.log('Deadlock detected, retrying after delay...');
452
      await new Promise(resolve => setTimeout(resolve, Math.random() * 1000));
453
      return performComplexTransaction();
454
    } else {
455
      // Non-retryable error
456
      throw error;
457
    }
458
  }
459
}
460
```
461

462
## Debug Configuration
463

464
Enable detailed error information for development and troubleshooting.
465

466
```javascript { .api }
467
/**
468
 * Debug configuration options
469
 */
470
const sql = postgres(connectionConfig, {
471
  /** Enable debug mode */
472
  debug: true,
473
  
474
  /** Custom debug function */
475
  debug: (connection, query, parameters, paramTypes) => {
476
    console.log('Query:', query);
477
    console.log('Parameters:', parameters);
478
    console.log('Connection:', connection);
479
  }
480
});
481
```
482

483
**Usage Examples:**
484

485
```javascript
486
// Debug mode with detailed logging
487
const debugSql = postgres(connectionConfig, {
488
  debug: (connection, query, parameters) => {
489
    console.log(`[Connection ${connection}] Executing query:`);
490
    console.log('SQL:', query);
491
    console.log('Parameters:', parameters);
492
    console.log('---');
493
  }
494
});
495

496
try {
497
  await debugSql`SELECT * FROM users WHERE email = ${email}`;
498
} catch (error) {
499
  // Error will include query and parameters when debug is enabled
500
  console.log('Failed query:', error.query);
501
  console.log('Query parameters:', error.parameters);
502
  console.log('Full error:', error);
503
}
504

505
// Conditional debug based on environment
506
const sql = postgres(connectionConfig, {
507
  debug: process.env.NODE_ENV === 'development' ? console.log : false
508
});
509

510
// Custom debug with filtering
511
const sql = postgres(connectionConfig, {
512
  debug: (connection, query, parameters) => {
513
    // Only log slow queries or errors
514
    if (query.toLowerCase().includes('select') && parameters.length > 5) {
515
      console.log('Complex query detected:', query);
516
    }
517
  }
518
});
519
```
520

521
## Error Monitoring and Logging
522

523
### Structured Error Logging
524

525
Implement comprehensive error logging for production monitoring.
526

527
```javascript
528
class DatabaseErrorLogger {
529
  constructor(logger) {
530
    this.logger = logger;
531
  }
532
  
533
  logError(error, context = {}) {
534
    const errorInfo = {
535
      timestamp: new Date().toISOString(),
536
      error_type: error.constructor.name,
537
      ...context
538
    };
539
    
540
    if (error instanceof PostgresError) {
541
      errorInfo.postgres_error = {
542
        code: error.code,
543
        severity: error.severity,
544
        message: error.message,
545
        detail: error.detail,
546
        hint: error.hint,
547
        position: error.position,
548
        constraint_name: error.constraint_name,
549
        table_name: error.table_name,
550
        column_name: error.column_name,
551
        query: error.query,
552
        parameters: error.parameters
553
      };
554
    } else {
555
      errorInfo.generic_error = {
556
        message: error.message,
557
        stack: error.stack
558
      };
559
    }
560
    
561
    this.logger.error('Database operation failed', errorInfo);
562
  }
563
  
564
  async wrapOperation(operation, context) {
565
    try {
566
      return await operation();
567
    } catch (error) {
568
      this.logError(error, context);
569
      throw error;
570
    }
571
  }
572
}
573

574
// Usage with structured logging
575
import winston from 'winston';
576

577
const logger = winston.createLogger({
578
  level: 'info',
579
  format: winston.format.json(),
580
  transports: [
581
    new winston.transports.File({ filename: 'database-errors.log', level: 'error' }),
582
    new winston.transports.Console({ format: winston.format.simple() })
583
  ]
584
});
585

586
const errorLogger = new DatabaseErrorLogger(logger);
587

588
// Wrap database operations
589
const user = await errorLogger.wrapOperation(
590
  () => sql`SELECT * FROM users WHERE id = ${userId}`,
591
  { operation: 'get_user', user_id: userId }
592
);
593
```
594

595
### Error Metrics and Alerting
596

597
Track error patterns and implement alerting for critical issues.
598

599
```javascript
600
class DatabaseErrorMetrics {
601
  constructor() {
602
    this.errorCounts = new Map();
603
    this.errorRates = new Map();
604
    this.lastReset = Date.now();
605
  }
606
  
607
  recordError(error) {
608
    const code = error.code || 'UNKNOWN';
609
    
610
    // Count errors by type
611
    this.errorCounts.set(code, (this.errorCounts.get(code) || 0) + 1);
612
    
613
    // Track error rates (errors per minute)
614
    const minute = Math.floor(Date.now() / 60000);
615
    const rateKey = `${code}:${minute}`;
616
    this.errorRates.set(rateKey, (this.errorRates.get(rateKey) || 0) + 1);
617
    
618
    // Check for alert conditions
619
    this.checkAlerts(code);
620
  }
621
  
622
  checkAlerts(errorCode) {
623
    const count = this.errorCounts.get(errorCode) || 0;
624
    const minute = Math.floor(Date.now() / 60000);
625
    const rateKey = `${errorCode}:${minute}`;
626
    const rate = this.errorRates.get(rateKey) || 0;
627
    
628
    // Alert on high error rates
629
    if (rate > 10) {
630
      this.sendAlert(`High error rate for ${errorCode}: ${rate} errors/minute`);
631
    }
632
    
633
    // Alert on specific critical errors
634
    if (errorCode === '53300' && count > 0) { // Too many connections
635
      this.sendAlert('Database connection pool exhausted');
636
    }
637
    
638
    if (errorCode === '58030' && count > 0) { // Disk full
639
      this.sendAlert('Database disk space critical');
640
    }
641
  }
642
  
643
  sendAlert(message) {
644
    console.error('DATABASE ALERT:', message);
645
    // Integrate with alerting system (PagerDuty, Slack, etc.)
646
  }
647
  
648
  getMetrics() {
649
    return {
650
      error_counts: Object.fromEntries(this.errorCounts),
651
      total_errors: Array.from(this.errorCounts.values()).reduce((a, b) => a + b, 0),
652
      uptime: Date.now() - this.lastReset
653
    };
654
  }
655
  
656
  reset() {
657
    this.errorCounts.clear();
658
    this.errorRates.clear();
659
    this.lastReset = Date.now();
660
  }
661
}
662

663
// Usage
664
const metrics = new DatabaseErrorMetrics();
665

666
// Wrap SQL instance to track all errors
667
const originalQuery = sql;
668
const monitoredSql = new Proxy(sql, {
669
  apply: async (target, thisArg, argumentsList) => {
670
    try {
671
      return await target.apply(thisArg, argumentsList);
672
    } catch (error) {
673
      metrics.recordError(error);
674
      throw error;
675
    }
676
  }
677
});
678

679
// Periodic metrics reporting
680
setInterval(() => {
681
  console.log('Database Error Metrics:', metrics.getMetrics());
682
}, 60000); // Every minute
683
```
684

685
## Error Prevention
686

687
### Input Validation
688

689
Prevent errors through comprehensive input validation.
690

691
```javascript
692
class DatabaseValidator {
693
  static validateEmail(email) {
694
    if (!email || typeof email !== 'string') {
695
      throw new Error('Email is required and must be a string');
696
    }
697
    
698
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
699
    if (!emailRegex.test(email)) {
700
      throw new Error('Invalid email format');
701
    }
702
    
703
    if (email.length > 254) {
704
      throw new Error('Email too long (max 254 characters)');
705
    }
706
  }
707
  
708
  static validateId(id) {
709
    if (!Number.isInteger(id) || id <= 0) {
710
      throw new Error('ID must be a positive integer');
711
    }
712
  }
713
  
714
  static validateRequired(value, fieldName) {
715
    if (value == null || value === '') {
716
      throw new Error(`${fieldName} is required`);
717
    }
718
  }
719
  
720
  static validateLength(value, fieldName, maxLength) {
721
    if (typeof value === 'string' && value.length > maxLength) {
722
      throw new Error(`${fieldName} exceeds maximum length of ${maxLength}`);
723
    }
724
  }
725
}
726

727
// Usage in database operations
728
async function createUser(userData) {
729
  // Validate inputs before database operation
730
  DatabaseValidator.validateRequired(userData.email, 'Email');
731
  DatabaseValidator.validateEmail(userData.email);
732
  DatabaseValidator.validateRequired(userData.username, 'Username');
733
  DatabaseValidator.validateLength(userData.username, 'Username', 50);
734
  
735
  try {
736
    const [user] = await sql`
737
      INSERT INTO users (email, username, password_hash)
738
      VALUES (${userData.email}, ${userData.username}, ${userData.passwordHash})
739
      RETURNING *
740
    `;
741
    return user;
742
  } catch (error) {
743
    // Handle remaining database errors
744
    throw new Error(`User creation failed: ${error.message}`);
745
  }
746
}
747
```