Using the pg Library

Error Handling and Debugging

Proper error handling is essential when working with databases. The pg library provides detailed error information to help you diagnose and handle database errors effectively.

Common PostgreSQL Error Types

• 23505: Unique constraint violation
• 23503: Foreign key constraint violation
• 42P01: Undefined table
• 42703: Undefined column
• 42601: Syntax error
• 08003/08006: Connection exceptions
• 53300: Too many connections

Robust Error Handling

const createUser = async (userData) => {
  try {
    const query = `
      INSERT INTO users (username, email, password_hash)
      VALUES ($1, $2, $3)
      RETURNING *
    `;
    const values = [userData.username, userData.email, userData.passwordHash];
    const result = await db.query(query, values);
    
    return result.rows[0];
  } catch (err) {
    // Check for specific error types
    if (err.code === '23505') { // Unique violation
      if (err.constraint === 'users_username_key') {
        throw new Error('Username already exists');
      } else if (err.constraint === 'users_email_key') {
        throw new Error('Email already registered');
      }
    }
    
    // Log the detailed error for debugging
    console.error('Database error:', {
      message: err.message,
      code: err.code,
      detail: err.detail,
      table: err.table,
      constraint: err.constraint,
      query: err.query,
      position: err.position
    });
    
    // Throw a general error for the client
    throw new Error('Database error occurred');
  }
};
            

Custom Error Classes

Creating custom error classes can help organize and handle different types of database errors consistently:

// errors.js
class DatabaseError extends Error {
  constructor(message, pgError) {
    super(message);
    this.name = 'DatabaseError';
    this.pgError = pgError;
    this.code = pgError ? pgError.code : null;
  }
}

class NotFoundError extends Error {
  constructor(entity, id) {
    super(`${entity} with ID ${id} not found`);
    this.name = 'NotFoundError';
    this.entity = entity;
    this.entityId = id;
  }
}

class ValidationError extends Error {
  constructor(message, fields = {}) {
    super(message);
    this.name = 'ValidationError';
    this.fields = fields;
  }
}

class UniqueConstraintError extends DatabaseError {
  constructor(field, value, pgError) {
    super(`${field} "${value}" is already in use`, pgError);
    this.name = 'UniqueConstraintError';
    this.field = field;
    this.value = value;
  }
}

module.exports = {
  DatabaseError,
  NotFoundError,
  ValidationError,
  UniqueConstraintError
};

// usage in repository
const { NotFoundError, UniqueConstraintError } = require('./errors');

const updateUser = async (id, updates) => {
  try {
    const query = `
      UPDATE users
      SET username = $1, email = $2
      WHERE id = $3
      RETURNING *
    `;
    const values = [updates.username, updates.email, id];
    const result = await db.query(query, values);
    
    if (result.rowCount === 0) {
      throw new NotFoundError('User', id);
    }
    
    return result.rows[0];
  } catch (err) {
    if (err.code === '23505') { // Unique violation
      if (err.constraint === 'users_username_key') {
        throw new UniqueConstraintError('username', updates.username, err);
      } else if (err.constraint === 'users_email_key') {
        throw new UniqueConstraintError('email', updates.email, err);
      }
    }
    // Re-throw other errors
    throw err;
  }
};

// In API routes
app.put('/api/users/:id', async (req, res) => {
  try {
    const user = await updateUser(req.params.id, req.body);
    res.json(user);
  } catch (err) {
    if (err instanceof NotFoundError) {
      return res.status(404).json({ error: err.message });
    }
    
    if (err instanceof UniqueConstraintError) {
      return res.status(400).json({ 
        error: err.message,
        field: err.field
      });
    }
    
    console.error('Unexpected error:', err);
    res.status(500).json({ error: 'An unexpected error occurred' });
  }
});