BUG: Improve SQLite error handling in get_db_connection

[riyagoyal01]

Is there an existing issue for this?

• I have searched the existing issues

What happened?

Problem

The current implementation of get_db_connection in backend/app/database/connection.py catches a broad Exception when handling database operations.

Catching all exceptions can:

• Hide SQLite-specific errors
• Make debugging more difficult
• Handle unrelated runtime errors in a place meant only for database failures
• Provide no logging context when a transaction fails

Why this matters

More precise error handling improves:

• Debuggability for contributors
• Code clarity and maintainability
• Reliability of database transaction management
• Alignment with Python and database best practices

Proposed improvement

Refine the exception handling to:

• Catch sqlite3.Error instead of a generic Exception
• Log database-related failures for better visibility
• Preserve the existing rollback and re-raise behavior

This change does not affect application logic but improves robustness and developer experience.

I would be happy to open a PR with this improvement if maintainers agree.

Record

• I agree to follow this project's Code of Conduct

[github-actions]

👋 Thanks for opening this issue! The maintainers will review it shortly. Till then, do not open any PR. This is a strict rule we like to follow around here :)

[ionfwsrijan]

@riyagoyal01
Hi there,

I’m Srijan Jaiswal, and I’m eager to contribute to this project as part of my open-source contribution. I have reviewed this issue and feel confident that I can provide a solution since I have experience working with similar technologies in my past projects. Could you please assign this issue to me? I’d love to work on it and contribute to the project.

Best regards,
Srijan Jaiswal

[riyagoyal01]

Hi @rahulharpal1603 ,
I wanted to ask if I can work on this issue related to improving exception handling. Please let me know if this change aligns with the project’s direction.

[ajithhraj]

I've opened a PR to fix this issue: #1219

Changes made:

• Replaced broad Exception catch with sqlite3.Error for precise DB error handling
• Added logging to surface database failures with context
• Preserved existing rollback and re-raise behavior

Happy to make any changes based on feedback!