Add Error Handling to DB module

[NicholasLe04]

Add Comprehensive Error Handling to Database Utility Module

Overview

The database utility module (backend/utils/db.py) currently lacks error handling for database operations. This makes our application vulnerable to crashes and inconsistent states when database operations fail. We need to implement proper error handling to ensure our application can gracefully handle database errors.

Current State

The database utility functions perform critical operations like:

• Database connection and initialization
• CRUD operations for snacks
• Data retrieval

However, these functions don't handle potential errors such as:

• Connection failures
• Query execution errors
• Non-existent records
• Constraint violations (e.g., duplicate SKUs)
• File system errors during initialization

Objectives

1. Implement comprehensive error handling for all database operations
2. Create custom exceptions for different error scenarios
3. Ensure functions return appropriate error responses

Detailed Tasks

1. Create Custom Exception Classes

Create a new file backend/utils/exceptions.py with custom exceptions:

class DatabaseError(Exception):
    """Base exception for database errors"""
    pass

class ConnectionError(DatabaseError):
    """Failed to connect to database"""
    pass

class RecordNotFoundError(DatabaseError):
    """Requested record does not exist"""
    pass

class DuplicateRecordError(DatabaseError):
    """Record with this identifier already exists"""
    pass

class DatabaseInitError(DatabaseError):
    """Failed to initialize database"""
    pass

2. Add Error Handling to Database Connection

Update the get_db_connection function to handle connection errors:

def get_db_connection(db_file_path:str="data/db.sqlite3"):
    """Creates and returns a SQLite database connection"""
    try:
        connection = sqlite3.connect(db_file_path)
        connection.row_factory = sqlite3.Row
        return connection
    except sqlite3.Error as e:
        raise ConnectionError(f"Failed to connect to database: {str(e)}")

3. Add Error Handling to All CRUD Functions

Implement try-except blocks in all database functions. For example:

• Handle "record not found" in get_snack
• Handle constraint violations in create_snack
• Handle update failures in update_snack

4. Update Return Types

Update function signatures to indicate they may raise exceptions.

Example Implementation

Here's an example of how to update the get_snack function:

def get_snack(sku: str) -> Snack:
    """
    Returns a single snack by SKU
    
    Args:
        sku: The unique SKU of the snack
        
    Returns:
        Snack object
        
    Raises:
        RecordNotFoundError: If no snack with the given SKU exists
        ConnectionError: If database connection fails
        DatabaseError: For other database errors
    """
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM snacks WHERE sku = ?", (sku,))
            record = cursor.fetchone()
            
            if record is None:
                raise RecordNotFoundError(f"No snack found with SKU: {sku}")
                
            return Snack(**record)
    except sqlite3.Error as e:
        raise DatabaseError(f"Database error when fetching snack {sku}: {str(e)}")

Resources

• Python Exception Handling Documentation
• SQLite Error Codes