LCFS - Optimize SQLAlchemy Session Management for Enhanced Database Efficiency in FastAPI Integration

Describe the Task

Review and refactor the backend Python FastAPI integration with the PostgreSQL database focusing on the SQLAlchemy session management. The current implementation is leading to redundant database commits within a single request cycle, which unnecessarily increases the number of database calls. The aim is to streamline session management to ensure optimal performance and database interaction efficiency.

Purpose

The main goal of this task is to eliminate unnecessary database commits that occur multiple times within a single request cycle. By refining the session management strategy and adhering to a more efficient pattern, we aim to reduce the load on the database, enhance the application's performance, and ensure the scalability of our backend services. This task will contribute to improving the overall reliability and efficiency of our application.

Acceptance Criteria

[ ] Review the current SQLAlchemy session management setup and identify instances of redundant database commits.
[ ] Refactor the session management to utilize get_async_db_session for creating and managing database sessions, ensuring a single commit per request cycle.
[ ] Remove the commit_to_db() method from the base repository pattern, or refactor it to align with the new session management strategy.
[ ] Ensure that the refactoring does not introduce any regressions or functionality loss in the application.
[ ] Update relevant documentation and comments in the codebase to reflect the new session management approach.
[ ] Conduct thorough testing to verify that the changes lead to the expected reduction in database calls and overall performance improvement.

Additional Context

The current setup utilizes a custom commit_to_db() method in our base repository for committing transactions, which has contributed to the issue of multiple commits within a single request cycle.
The proposed solution involves refactoring the session management to leverage the AsyncSession from SQLAlchemy and ensure that commits are handled efficiently at the end of each request cycle.
Example code snippets provided indicate how the get_async_db_session should be used to manage sessions and commits.
This task requires an understanding of asynchronous database operations and the FastAPI framework's dependency injection system.

bcgov / lcfs