Database Access Layer Refactoring

[Pyatakov]

Problem description

Our current database access layer has several issues that need to be addressed:

1. Inconsistent access patterns: Some parts of the codebase bypass the DatabaseServer and directly access the DataBaseHelper, leading to poor encapsulation and potential maintenance difficulties.
2. Suboptimal database queries: The DataBaseHelper, which was quickly implemented to transition from TypeORM to MicroORM, contains inefficient and complex queries that need optimization.
3. Untyped filters in DatabaseServer: Some methods in DatabaseServer accept untyped filters as parameters, which may cause issues when changing the database or resolving queries through plugins.

These issues make our codebase harder to maintain, less efficient, and more prone to errors when making changes to the database layer.

Requirements

1. Refactor the codebase to ensure all database access goes through the DatabaseServer, eliminating direct access to DataBaseHelper.
2. Redesign the database access layer to optimize queries and improve the interface between DatabaseServer and the ORM (MicroORM):
   a. Create a well-defined interface for database operations.
   b. Implement a replaceable plugin system for ORM interactions.
   c. Consider direct use of MicroORM if it provides better performance and maintainability.
3. Implement typed query builders to replace untyped filters in DatabaseServer methods:
   a. Research existing solutions for typed query builders.
   b. Develop or adapt a query builder that integrates with our system.
   c. Replace all untyped and database-oriented filters with the new typed constructs.

Definition of done

• All direct accesses to DataBaseHelper in the codebase have been replaced with calls to DatabaseServer methods.
• A new database access layer has been implemented with:
  • A clear, well-documented interface for all database operations.
  • Optimized queries that improve performance over the current implementation.
  • A plugin system that allows easy switching between different ORMs or database technologies.
• All methods in DatabaseServer that previously accepted untyped filters now use typed query builders.
• Comprehensive unit and integration tests cover the new database access layer, ensuring all functionality works as expected.

Acceptance criteria

1. Code review shows no direct usage of DataBaseHelper outside of the database access layer.
2. Performance tests demonstrate improved query efficiency compared to the old implementation.
3. A new ORM or database technology can be integrated by implementing the defined interface, without changes to the rest of the application code.
4. All filter-based queries in the application use the new typed query builder, with no instances of untyped filters.
5. The application passes all existing and new unit and integration tests.
6. Developers can successfully use the new database access layer to perform all necessary database operations without reverting to old patterns or bypassing the new system.
7. Code maintainability metrics (e.g., cyclomatic complexity, coupling) show improvement over the previous implementation.