Error Handling

/**
 * PostgreSQL-specific error class
 * Extends standard Error with PostgreSQL server error details
 */
class PostgresError extends Error {
  /** Error severity level */
  severity: string;
  
  /** Localized severity level */
  severity_local: string;
  
  /** PostgreSQL error code (SQLSTATE) */
  code: string;
  
  /** Error message */
  message: string;
  
  /** Additional error details */
  detail?: string;
  
  /** Hint for resolving the error */
  hint?: string;
  
  /** Character position in query where error occurred */
  position?: string;
  
  /** Internal query position */
  internal_position?: string;
  
  /** Internal query that caused error */
  internal_query?: string;
  
  /** Where in source code error occurred */
  where?: string;
  
  /** Schema name related to error */
  schema_name?: string;
  
  /** Table name related to error */
  table_name?: string;
  
  /** Column name related to error */
  column_name?: string;
  
  /** Data type name related to error */
  datatype_name?: string;
  
  /** Constraint name that was violated */
  constraint_name?: string;
  
  /** Source file where error occurred */
  file?: string;
  
  /** Line number in source file */
  line?: string;
  
  /** Routine name where error occurred */
  routine?: string;
  
  /** Query that caused the error (when debug enabled) */
  query?: string;
  
  /** Parameters used in query (when debug enabled) */
  parameters?: any[];
}

try {
  await sql`INSERT INTO users (email) VALUES (${email})`;
} catch (error) {
  if (error instanceof PostgresError) {
    console.log('PostgreSQL Error Details:');
    console.log('Code:', error.code);
    console.log('Message:', error.message);
    console.log('Detail:', error.detail);
    console.log('Hint:', error.hint);
    
    if (error.constraint_name) {
      console.log('Constraint violated:', error.constraint_name);
    }
    
    if (error.position) {
      console.log('Error at position:', error.position);
    }
  } else {
    console.log('Unexpected error:', error);
  }
}

/**
 * Connection-related error class
 * For network and connection pool errors
 */
interface ConnectionError extends Error {
  code: 
    | 'CONNECTION_DESTROYED'
    | 'CONNECT_TIMEOUT' 
    | 'CONNECTION_CLOSED'
    | 'CONNECTION_ENDED';
  errno: string;
  address: string;
  port?: number;
}

/**
 * Protocol or feature not supported error
 */
interface NotSupportedError extends Error {
  code: 'MESSAGE_NOT_SUPPORTED';
  name: string;
}

/**
 * Generic client-side errors
 */
interface GenericError extends Error {
  code:
    | '57014'  // canceling statement due to user request
    | 'NOT_TAGGED_CALL'
    | 'UNDEFINED_VALUE'
    | 'MAX_PARAMETERS_EXCEEDED'
    | 'SASL_SIGNATURE_MISMATCH'
    | 'UNSAFE_TRANSACTION';
  message: string;
}

/**
 * Authentication method not implemented error
 */
interface AuthNotImplementedError extends Error {
  code: 'AUTH_TYPE_NOT_IMPLEMENTED';
  type: number | string;
  message: string;
}

try {
  await sql`SELECT * FROM users`;
} catch (error) {
  if (error.code === 'CONNECTION_DESTROYED') {
    console.log('Connection was destroyed, reconnecting...');
    // Handle reconnection logic
  } else if (error.code === 'CONNECT_TIMEOUT') {
    console.log('Connection timeout, retrying...');
    // Handle timeout retry logic
  } else if (error.code === 'NOT_TAGGED_CALL') {
    console.log('Must use tagged template literals');
    // Fix query syntax
  } else if (error.code === 'AUTH_TYPE_NOT_IMPLEMENTED') {
    console.log('Authentication method not supported:', error.type);
    // Handle auth configuration
  }
}

/**
 * Common PostgreSQL error codes (SQLSTATE)
 */
const ERROR_CODES = {
  // Connection errors
  CONNECTION_EXCEPTION: '08000',
  CONNECTION_DOES_NOT_EXIST: '08003',
  CONNECTION_FAILURE: '08006',
  
  // Data errors  
  DATA_EXCEPTION: '22000',
  NUMERIC_VALUE_OUT_OF_RANGE: '22003',
  INVALID_DATETIME_FORMAT: '22007',
  DIVISION_BY_ZERO: '22012',
  
  // Integrity constraint violations
  INTEGRITY_CONSTRAINT_VIOLATION: '23000',
  RESTRICT_VIOLATION: '23001', 
  NOT_NULL_VIOLATION: '23502',
  FOREIGN_KEY_VIOLATION: '23503',
  UNIQUE_VIOLATION: '23505',
  CHECK_VIOLATION: '23514',
  
  // Transaction errors
  TRANSACTION_ROLLBACK: '25000',
  SERIALIZATION_FAILURE: '25001',
  DEADLOCK_DETECTED: '25P01',
  
  // System errors
  SYSTEM_ERROR: '58000',
  DISK_FULL: '58030',
  INSUFFICIENT_RESOURCES: '53000',
  OUT_OF_MEMORY: '53200',
  TOO_MANY_CONNECTIONS: '53300',
  
  // Security errors
  INSUFFICIENT_PRIVILEGE: '42501',
  INVALID_AUTHORIZATION: '28000',
  INVALID_PASSWORD: '28P01'
};

async function handleDatabaseOperation() {
  try {
    await sql`INSERT INTO orders (user_id, product_id) VALUES (${userId}, ${productId})`;
  } catch (error) {
    if (error.code === '23503') {
      // Foreign key violation
      throw new Error('Invalid user or product ID');
    } else if (error.code === '23505') {
      // Unique violation
      throw new Error('Duplicate order detected');
    } else if (error.code === '23502') {
      // Not null violation
      throw new Error('Required field is missing');
    } else {
      // Unexpected error
      throw error;
    }
  }
}

// More comprehensive error handling
async function createUser(userData) {
  try {
    const [user] = await sql`
      INSERT INTO users (email, username, password_hash)
      VALUES (${userData.email}, ${userData.username}, ${userData.passwordHash})
      RETURNING *
    `;
    return user;
  } catch (error) {
    switch (error.code) {
      case '23505':
        if (error.constraint_name === 'users_email_unique') {
          throw new Error('Email address already registered');
        } else if (error.constraint_name === 'users_username_unique') {
          throw new Error('Username already taken');
        }
        break;
        
      case '23502':
        throw new Error(`Required field missing: ${error.column_name}`);
        
      case '22001':
        throw new Error('Input data too long for field');
        
      case '53300':
        throw new Error('Server too busy, please try again later');
        
      default:
        console.error('Unexpected database error:', error);
        throw new Error('User creation failed');
    }
  }
}

/**
 * Retry configuration options
 */
interface RetryOptions {
  /** Maximum number of retry attempts */
  maxAttempts: number;
  
  /** Base delay between retries in milliseconds */
  baseDelay: number;
  
  /** Whether to use exponential backoff */
  exponentialBackoff: boolean;
  
  /** Maximum delay between retries */
  maxDelay: number;
  
  /** Error codes that should trigger retry */
  retryableErrors: string[];
}

class DatabaseRetryHandler {
  constructor(options = {}) {
    this.options = {
      maxAttempts: 3,
      baseDelay: 1000,
      exponentialBackoff: true,
      maxDelay: 10000,
      retryableErrors: [
        '08000', // Connection exception
        '08003', // Connection does not exist
        '08006', // Connection failure
        '25001', // Serialization failure
        '25P01', // Deadlock detected
        '53000', // Insufficient resources
        '53200', // Out of memory
        '53300', // Too many connections
      ],
      ...options
    };
  }
  
  async executeWithRetry(operation, context = {}) {
    let lastError;
    
    for (let attempt = 1; attempt <= this.options.maxAttempts; attempt++) {
      try {
        return await operation();
      } catch (error) {
        lastError = error;
        
        if (!this.shouldRetry(error, attempt)) {
          throw error;
        }
        
        const delay = this.calculateDelay(attempt);
        console.log(`Operation failed (attempt ${attempt}), retrying in ${delay}ms:`, error.message);
        
        await this.sleep(delay);
      }
    }
    
    throw lastError;
  }
  
  shouldRetry(error, attempt) {
    // Don't retry if max attempts reached
    if (attempt >= this.options.maxAttempts) {
      return false;
    }
    
    // Only retry for PostgreSQL errors with retryable codes
    if (!(error instanceof PostgresError)) {
      return false;
    }
    
    return this.options.retryableErrors.includes(error.code);
  }
  
  calculateDelay(attempt) {
    let delay = this.options.baseDelay;
    
    if (this.options.exponentialBackoff) {
      delay *= Math.pow(2, attempt - 1);
    }
    
    return Math.min(delay, this.options.maxDelay);
  }
  
  sleep(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
  }
}

// Usage
const retryHandler = new DatabaseRetryHandler({
  maxAttempts: 5,
  baseDelay: 500,
  exponentialBackoff: true
});

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

async function performComplexTransaction() {
  let transaction;
  
  try {
    transaction = await sql.begin(async (sql) => {
      // Step 1: Create order
      const [order] = await sql`
        INSERT INTO orders (user_id, total_amount)
        VALUES (${userId}, ${totalAmount})
        RETURNING *
      `;
      
      // Step 2: Create order items
      for (const item of orderItems) {
        await sql`
          INSERT INTO order_items (order_id, product_id, quantity, price)
          VALUES (${order.id}, ${item.productId}, ${item.quantity}, ${item.price})
        `;
        
        // Step 3: Update inventory
        const [updated] = await sql`
          UPDATE products 
          SET stock_quantity = stock_quantity - ${item.quantity}
          WHERE id = ${item.productId} AND stock_quantity >= ${item.quantity}
          RETURNING stock_quantity
        `;
        
        if (!updated) {
          throw new Error(`Insufficient stock for product ${item.productId}`);
        }
      }
      
      return order;
    });
    
    return transaction;
    
  } catch (error) {
    console.error('Transaction failed:', error.message);
    
    if (error.code === '25001') {
      // Serialization failure - retry the entire transaction
      console.log('Serialization conflict detected, retrying transaction...');
      return performComplexTransaction();
    } else if (error.code === '25P01') {
      // Deadlock detected
      console.log('Deadlock detected, retrying after delay...');
      await new Promise(resolve => setTimeout(resolve, Math.random() * 1000));
      return performComplexTransaction();
    } else {
      // Non-retryable error
      throw error;
    }
  }
}

/**
 * Debug configuration options
 */
const sql = postgres(connectionConfig, {
  /** Enable debug mode */
  debug: true,
  
  /** Custom debug function */
  debug: (connection, query, parameters, paramTypes) => {
    console.log('Query:', query);
    console.log('Parameters:', parameters);
    console.log('Connection:', connection);
  }
});

// Debug mode with detailed logging
const debugSql = postgres(connectionConfig, {
  debug: (connection, query, parameters) => {
    console.log(`[Connection ${connection}] Executing query:`);
    console.log('SQL:', query);
    console.log('Parameters:', parameters);
    console.log('---');
  }
});

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

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

// Custom debug with filtering
const sql = postgres(connectionConfig, {
  debug: (connection, query, parameters) => {
    // Only log slow queries or errors
    if (query.toLowerCase().includes('select') && parameters.length > 5) {
      console.log('Complex query detected:', query);
    }
  }
});

class DatabaseErrorLogger {
  constructor(logger) {
    this.logger = logger;
  }
  
  logError(error, context = {}) {
    const errorInfo = {
      timestamp: new Date().toISOString(),
      error_type: error.constructor.name,
      ...context
    };
    
    if (error instanceof PostgresError) {
      errorInfo.postgres_error = {
        code: error.code,
        severity: error.severity,
        message: error.message,
        detail: error.detail,
        hint: error.hint,
        position: error.position,
        constraint_name: error.constraint_name,
        table_name: error.table_name,
        column_name: error.column_name,
        query: error.query,
        parameters: error.parameters
      };
    } else {
      errorInfo.generic_error = {
        message: error.message,
        stack: error.stack
      };
    }
    
    this.logger.error('Database operation failed', errorInfo);
  }
  
  async wrapOperation(operation, context) {
    try {
      return await operation();
    } catch (error) {
      this.logError(error, context);
      throw error;
    }
  }
}

// Usage with structured logging
import winston from 'winston';

const logger = winston.createLogger({
  level: 'info',
  format: winston.format.json(),
  transports: [
    new winston.transports.File({ filename: 'database-errors.log', level: 'error' }),
    new winston.transports.Console({ format: winston.format.simple() })
  ]
});

const errorLogger = new DatabaseErrorLogger(logger);

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

class DatabaseErrorMetrics {
  constructor() {
    this.errorCounts = new Map();
    this.errorRates = new Map();
    this.lastReset = Date.now();
  }
  
  recordError(error) {
    const code = error.code || 'UNKNOWN';
    
    // Count errors by type
    this.errorCounts.set(code, (this.errorCounts.get(code) || 0) + 1);
    
    // Track error rates (errors per minute)
    const minute = Math.floor(Date.now() / 60000);
    const rateKey = `${code}:${minute}`;
    this.errorRates.set(rateKey, (this.errorRates.get(rateKey) || 0) + 1);
    
    // Check for alert conditions
    this.checkAlerts(code);
  }
  
  checkAlerts(errorCode) {
    const count = this.errorCounts.get(errorCode) || 0;
    const minute = Math.floor(Date.now() / 60000);
    const rateKey = `${errorCode}:${minute}`;
    const rate = this.errorRates.get(rateKey) || 0;
    
    // Alert on high error rates
    if (rate > 10) {
      this.sendAlert(`High error rate for ${errorCode}: ${rate} errors/minute`);
    }
    
    // Alert on specific critical errors
    if (errorCode === '53300' && count > 0) { // Too many connections
      this.sendAlert('Database connection pool exhausted');
    }
    
    if (errorCode === '58030' && count > 0) { // Disk full
      this.sendAlert('Database disk space critical');
    }
  }
  
  sendAlert(message) {
    console.error('DATABASE ALERT:', message);
    // Integrate with alerting system (PagerDuty, Slack, etc.)
  }
  
  getMetrics() {
    return {
      error_counts: Object.fromEntries(this.errorCounts),
      total_errors: Array.from(this.errorCounts.values()).reduce((a, b) => a + b, 0),
      uptime: Date.now() - this.lastReset
    };
  }
  
  reset() {
    this.errorCounts.clear();
    this.errorRates.clear();
    this.lastReset = Date.now();
  }
}

// Usage
const metrics = new DatabaseErrorMetrics();

// Wrap SQL instance to track all errors
const originalQuery = sql;
const monitoredSql = new Proxy(sql, {
  apply: async (target, thisArg, argumentsList) => {
    try {
      return await target.apply(thisArg, argumentsList);
    } catch (error) {
      metrics.recordError(error);
      throw error;
    }
  }
});

// Periodic metrics reporting
setInterval(() => {
  console.log('Database Error Metrics:', metrics.getMetrics());
}, 60000); // Every minute

class DatabaseValidator {
  static validateEmail(email) {
    if (!email || typeof email !== 'string') {
      throw new Error('Email is required and must be a string');
    }
    
    const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
    if (!emailRegex.test(email)) {
      throw new Error('Invalid email format');
    }
    
    if (email.length > 254) {
      throw new Error('Email too long (max 254 characters)');
    }
  }
  
  static validateId(id) {
    if (!Number.isInteger(id) || id <= 0) {
      throw new Error('ID must be a positive integer');
    }
  }
  
  static validateRequired(value, fieldName) {
    if (value == null || value === '') {
      throw new Error(`${fieldName} is required`);
    }
  }
  
  static validateLength(value, fieldName, maxLength) {
    if (typeof value === 'string' && value.length > maxLength) {
      throw new Error(`${fieldName} exceeds maximum length of ${maxLength}`);
    }
  }
}

// Usage in database operations
async function createUser(userData) {
  // Validate inputs before database operation
  DatabaseValidator.validateRequired(userData.email, 'Email');
  DatabaseValidator.validateEmail(userData.email);
  DatabaseValidator.validateRequired(userData.username, 'Username');
  DatabaseValidator.validateLength(userData.username, 'Username', 50);
  
  try {
    const [user] = await sql`
      INSERT INTO users (email, username, password_hash)
      VALUES (${userData.email}, ${userData.username}, ${userData.passwordHash})
      RETURNING *
    `;
    return user;
  } catch (error) {
    // Handle remaining database errors
    throw new Error(`User creation failed: ${error.message}`);
  }
}