CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/npm-pg-promise

PostgreSQL interface for Node.js built on top of node-postgres, offering automatic connection management, promise-based API, automatic transaction handling with support for nested transactions and savepoints, advanced query formatting with named parameters and filtering capabilities, task and transaction management with conditional execution, query file loading and processing, custom type formatting support, and comprehensive error handling.

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Overview
Eval results
Files

error-handling.mddocs/

Error Handling

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.

Capabilities

Query Result Errors

Errors related to unexpected query result counts or data validation.

/**
 * Query Result Error - thrown when query results don't match expectations
 */
class QueryResultError extends Error {
  // Standard error properties
  name: string // Error name: 'QueryResultError'
  message: string // Error message
  stack: string // Stack trace
  
  // Extended properties
  result: IResult // Full query result object
  received: number // Number of rows received
  code: queryResultErrorCode // Specific error code
  query: string // SQL query that caused error
  values: any // Parameter values used
  
  toString(): string // String representation
}

/**
 * Query Result Error Codes
 */
enum queryResultErrorCode {
  noData = 0,    // No data returned when some was expected
  notEmpty = 1,  // Data returned when none was expected  
  multiple = 2   // Multiple rows returned when one was expected
}

Usage Examples:

try {
  // This will throw QueryResultError if no user found
  const user = await db.one('SELECT * FROM users WHERE id = $1', [999]);
} catch (error) {
  if (error instanceof pgp.errors.QueryResultError) {
    console.log('Query result error:', {
      code: error.code,
      received: error.received,
      query: error.query,
      values: error.values
    });
    
    switch (error.code) {
      case pgp.errors.queryResultErrorCode.noData:
        console.log('No user found with that ID');
        break;
      case pgp.errors.queryResultErrorCode.multiple:
        console.log('Multiple users found, expected one');
        break;
      case pgp.errors.queryResultErrorCode.notEmpty:
        console.log('Expected no results, but got data');
        break;
    }
  }
}

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

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

Query File Errors

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

/**
 * Query File Error - thrown when SQL files cannot be loaded or parsed
 */
class QueryFileError extends Error {
  // Standard error properties
  name: string // Error name: 'QueryFileError'
  message: string // Error message
  stack: string // Stack trace
  
  // Extended properties
  file: string // File path that caused the error
  options: IQueryFileOptions // File processing options used
  error: SQLParsingError // Underlying SQL parsing error (if applicable)
  
  toString(level?: number): string // String representation with indentation
}

/**
 * SQL Parsing Error from pg-minify
 */
interface SQLParsingError {
  name: string // Error name
  message: string // Error message
  position: number // Character position in SQL
  line: number // Line number
  column: number // Column number
}

Usage Examples:

// File loading errors
const queryFile = new pgp.QueryFile('/path/to/nonexistent.sql');
if (queryFile.error) {
  console.error('File error:', {
    type: queryFile.error.name,
    message: queryFile.error.message,
    file: queryFile.error.file
  });
}

// SQL parsing errors (with minification)
const complexQuery = new pgp.QueryFile('sql/invalid-syntax.sql', { minify: true });
if (complexQuery.error && complexQuery.error.error) {
  const parseError = complexQuery.error.error;
  console.error('SQL parsing error:', {
    message: parseError.message,
    line: parseError.line,
    column: parseError.column,
    position: parseError.position
  });
}

// Error handling in queries
try {
  const results = await db.any(new pgp.QueryFile('sql/problematic-query.sql'));
} catch (error) {
  if (error instanceof pgp.errors.QueryFileError) {
    console.error('Query file error:', {
      file: error.file,
      options: error.options,
      message: error.message
    });
    
    if (error.error) {
      console.error('Parsing details:', {
        line: error.error.line,
        column: error.error.column,
        position: error.error.position
      });
    }
  }
}

// Development-time error checking
function validateQueryFiles(directory) {
  const files = pgp.utils.enumSql(directory, { recursive: true });
  const errors = [];
  
  for (const [name, path] of Object.entries(files)) {
    const queryFile = new pgp.QueryFile(path, { minify: true });
    if (queryFile.error) {
      errors.push({
        name,
        path,
        error: queryFile.error.message
      });
    }
  }
  
  return errors;
}

Prepared Statement Errors

Errors related to prepared statement configuration and validation.

/**
 * Prepared Statement Error - thrown when prepared statements are invalid
 */
class PreparedStatementError extends Error {
  // Standard error properties
  name: string // Error name: 'PreparedStatementError'
  message: string // Error message
  stack: string // Stack trace
  
  // Extended properties
  error: QueryFileError // Underlying QueryFile error (if statement uses QueryFile)
  
  toString(level?: number): string // String representation with indentation
}

Usage Examples:

// Invalid prepared statement
const invalidStmt = new pgp.PreparedStatement({
  name: '', // Empty name will cause error
  text: 'SELECT * FROM users'
});

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

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

try {
  await db.any(fileStmt);
} catch (error) {
  if (error instanceof pgp.errors.PreparedStatementError) {
    console.error('Prepared statement error:', error.message);
    
    if (error.error) {
      console.error('Underlying file error:', error.error.message);
    }
  }
}

// Validation before execution
function validatePreparedStatement(stmt) {
  const parsed = stmt.parse();
  
  if (parsed instanceof pgp.errors.PreparedStatementError) {
    return {
      valid: false,
      error: parsed.message,
      details: parsed.error?.message
    };
  }
  
  return {
    valid: true,
    statement: parsed
  };
}

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

const validation = validatePreparedStatement(stmt);
if (validation.valid) {
  console.log('Statement is valid:', validation.statement.name);
} else {
  console.error('Statement is invalid:', validation.error);
}

Parameterized Query Errors

Errors related to parameterized query configuration and validation.

/**
 * Parameterized Query Error - thrown when parameterized queries are invalid
 */
class ParameterizedQueryError extends Error {
  // Standard error properties
  name: string // Error name: 'ParameterizedQueryError'
  message: string // Error message
  stack: string // Stack trace
  
  // Extended properties
  error: QueryFileError // Underlying QueryFile error (if query uses QueryFile)
  
  toString(level?: number): string // String representation with indentation
}

Usage Examples:

// Invalid parameterized query
const invalidQuery = new pgp.ParameterizedQuery({
  text: '', // Empty text will cause error
  values: [1, 2, 3]
});

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

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

try {
  await db.any(fileQuery);
} catch (error) {
  if (error instanceof pgp.errors.ParameterizedQueryError) {
    console.error('Parameterized query error:', error.message);
    
    if (error.error && error.error.error) {
      const parseError = error.error.error;
      console.error('SQL parsing error at line', parseError.line, ':', parseError.message);
    }
  }
}

// Parameter count validation
function validateParameterCount(query, expectedParams) {
  const paramCount = (query.text.match(/\$/g) || []).length;
  const providedCount = query.values ? query.values.length : 0;
  
  if (paramCount !== providedCount) {
    console.warn(`Parameter mismatch: query has ${paramCount} parameters, but ${providedCount} values provided`);
    return false;
  }
  
  if (paramCount !== expectedParams) {
    console.warn(`Expected ${expectedParams} parameters, but query has ${paramCount}`);
    return false;
  }
  
  return true;
}

Database Connection Errors

Standard PostgreSQL and node-postgres errors enhanced with context.

Usage Examples:

// Connection error handling
try {
  const db = pgp('postgres://invalid:invalid@nonexistent:5432/db');
  await db.any('SELECT 1');
} catch (error) {
  console.error('Connection error:', {
    code: error.code,     // ECONNREFUSED, ENOTFOUND, etc.
    message: error.message,
    host: error.host,
    port: error.port
  });
}

// Query timeout handling
try {
  await db.any('SELECT pg_sleep(10)'); // Long-running query
} catch (error) {
  if (error.code === '57014') { // Query timeout
    console.error('Query timed out');
  }
}

// Constraint violation handling
try {
  await db.none('INSERT INTO users(email) VALUES($1)', ['duplicate@example.com']);
} catch (error) {
  if (error.code === '23505') { // Unique violation
    console.error('Email already exists:', error.detail);
  } else if (error.code === '23503') { // Foreign key violation
    console.error('Foreign key constraint violated:', error.detail);
  } else if (error.code === '23514') { // Check constraint violation
    console.error('Check constraint violated:', error.detail);
  }
}

// Transaction rollback handling
try {
  await db.tx(async t => {
    await t.none('INSERT INTO users(name) VALUES($1)', ['John']);
    await t.none('INSERT INTO invalid_table(data) VALUES($1)', ['test']); // Will fail
  });
} catch (error) {
  console.error('Transaction failed:', error.message);
  // Transaction automatically rolled back
}

Error Context and Debugging

Comprehensive error context for debugging and monitoring.

Usage Examples:

// Global error handling with context
const pgp = require('pg-promise')({
  error: (err, e) => {
    console.error('Database error occurred:', {
      error: {
        name: err.name,
        message: err.message,
        code: err.code,
        severity: err.severity,
        detail: err.detail,
        hint: err.hint,
        position: err.position,
        where: err.where,
        schema: err.schema,
        table: err.table,
        column: err.column,
        dataType: err.dataType,
        constraint: err.constraint
      },
      context: {
        query: e.query,
        params: e.params,
        values: e.values,
        client: e.client.processID,
        database: e.client.database,
        user: e.client.user,
        task: e.ctx?.tag,
        level: e.ctx?.level,
        duration: e.ctx ? Date.now() - e.ctx.start.getTime() : null
      }
    });
  }
});

// Error logging wrapper
async function executeWithLogging(operation, context = {}) {
  const start = Date.now();
  
  try {
    const result = await operation();
    console.log('Operation succeeded:', {
      ...context,
      duration: Date.now() - start,
      success: true
    });
    return result;
  } catch (error) {
    console.error('Operation failed:', {
      ...context,
      duration: Date.now() - start,
      success: false,
      error: {
        type: error.constructor.name,
        message: error.message,
        code: error.code
      }
    });
    throw error;
  }
}

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

Error Recovery Patterns

Common patterns for error handling and recovery.

Usage Examples:

// Retry with exponential backoff
async function retryQuery(queryFunc, maxRetries = 3, baseDelay = 1000) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await queryFunc();
    } catch (error) {
      const isRetryable = error.code === 'ECONNRESET' || 
                         error.code === 'ECONNREFUSED' ||
                         error.code === '40001'; // Serialization failure
                         
      if (!isRetryable || attempt === maxRetries) {
        throw error;
      }
      
      const delay = baseDelay * Math.pow(2, attempt - 1);
      console.log(`Query failed (attempt ${attempt}/${maxRetries}), retrying in ${delay}ms...`);
      await new Promise(resolve => setTimeout(resolve, delay));
    }
  }
}

// Graceful degradation
async function getUserWithFallback(userId) {
  try {
    // Try to get user with profile
    return await db.one(`
      SELECT u.*, p.bio, p.avatar_url 
      FROM users u 
      LEFT JOIN profiles p ON u.id = p.user_id 
      WHERE u.id = $1
    `, [userId]);
  } catch (error) {
    if (error instanceof pgp.errors.QueryResultError && 
        error.code === pgp.errors.queryResultErrorCode.noData) {
      return null; // User not found
    }
    
    // On other errors, try simpler query
    try {
      console.warn('Profile query failed, falling back to basic user data');
      return await db.one('SELECT * FROM users WHERE id = $1', [userId]);
    } catch (fallbackError) {
      if (fallbackError instanceof pgp.errors.QueryResultError && 
          fallbackError.code === pgp.errors.queryResultErrorCode.noData) {
        return null;
      }
      throw fallbackError; // Re-throw if not a "not found" error
    }
  }
}

// Circuit breaker pattern
class DatabaseCircuitBreaker {
  constructor(threshold = 5, timeout = 60000) {
    this.failureThreshold = threshold;
    this.timeout = timeout;
    this.failureCount = 0;
    this.lastFailureTime = null;
    this.state = 'CLOSED'; // CLOSED, OPEN, HALF_OPEN
  }
  
  async execute(operation) {
    if (this.state === 'OPEN') {
      if (Date.now() - this.lastFailureTime > this.timeout) {
        this.state = 'HALF_OPEN';
      } else {
        throw new Error('Circuit breaker is OPEN');
      }
    }
    
    try {
      const result = await operation();
      this.onSuccess();
      return result;
    } catch (error) {
      this.onFailure();
      throw error;
    }
  }
  
  onSuccess() {
    this.failureCount = 0;
    this.state = 'CLOSED';
  }
  
  onFailure() {
    this.failureCount++;
    this.lastFailureTime = Date.now();
    
    if (this.failureCount >= this.failureThreshold) {
      this.state = 'OPEN';
    }
  }
}

const circuitBreaker = new DatabaseCircuitBreaker();

// Usage
const users = await circuitBreaker.execute(() => 
  db.any('SELECT * FROM users WHERE active = true')
);

Types

// Error interfaces
interface IErrorContext {
  client: IClient // Database client
  cn: any // Connection configuration
  dc: any // Database context
  query: any // Query being executed
  params: any // Query parameters
  values: any // Parameter values
  queryFilePath?: string // Query file path (if applicable)
  ctx: ITaskContext // Task context
}

// Standard PostgreSQL error properties
interface IPostgreSQLError {
  name: string // Error name
  message: string // Error message
  severity: string // Error severity (ERROR, FATAL, etc.)
  code: string // PostgreSQL error code
  detail?: string // Error detail
  hint?: string // Error hint
  position?: string // Error position in query
  internalPosition?: string // Internal error position
  internalQuery?: string // Internal query
  where?: string // Error context
  schema?: string // Schema name
  table?: string // Table name  
  column?: string // Column name
  dataType?: string // Data type name
  constraint?: string // Constraint name
  file?: string // Source file name
  line?: string // Source line number
  routine?: string // Function name
}

// Result interface
interface IResult {
  rows: any[] // Result rows
  rowCount: number // Number of rows
  command: string // SQL command
  oid: number // Object ID
  fields: IFieldInfo[] // Field information
}

interface IFieldInfo {
  name: string // Field name
  tableID: number // Table OID
  columnID: number // Column number
  dataTypeID: number // Data type OID
  dataTypeSize: number // Data type size
  dataTypeModifier: number // Type modifier
  format: string // Format code
}

Install with Tessl CLI

npx tessl i tessl/npm-pg-promise

docs

configuration-utilities.md

connection-management.md

database-operations.md

error-handling.md

index.md

query-files.md

query-formatting.md

tasks-transactions.md

tile.json