Major Description
Background and Motivation
Fineract initially had only JPA as its storage mechanism. When we moved from Mifos to the Apache Software Foundation we had to give up Hibernate as the default/standard JPA implementation and used the Apache sister project OpenJPA. OpenJPA had a couple of performance and standard compliance related issues which lead some developers to use plain native SQL queries in their service implementations. Because MySQL was for a long time the only configured database in the development environment developers started to introduce database system dependent query constructs (e. g. date functions). This was one of the main reasons why Fineract got stuck for a long time with MySQL 5.7 and for a while we couldn't even upgrade to the latest MySQL 8 version. Support for PostgreSQL was requested by the community repeatedly over the years, but couldn't be provided due to the these database dependency issues. Starting with Fineract 1.6.x we replaced OpenJPA with EclipseLink and added a thin abstraction layer for database system specific functions which allowed us to support PostgreSQL.
Nevertheless, we still have a lot (and growing) number of SQL native queries that are provided as Strings. This makes refactoring at the least a very time consuming task. Our database compatibility support is very limited (only specific functions) and as we wrote the code ourselves we have an additional burden to maintain it. Supporting more database systems (SQL Server, Oracle) in this setup is not worth the effort.
Target Personas
- developers
- integrators
- BaaS
Goals
- introduce QueryDSL to generate code for type-safe SQL queries
- use QueryDSL also for complex JPA queries
- make sure all repository classes follow Spring Data best practices (aka remove homegrown boilerplate code and annotations as much as possible)
- implement any unsupported features related to database queries as QueryDSL extensions/plugins
- automatically generate most/all of the query related code
- avoid/remove all custom "@Query" annotations from repository classes
- avoid/remove all JdbcTemplate use; all queries need to be included
- avoid SQL/OQL queries as much as possible by using Spring Data query function mapping
Non-Goals
- replace all JPA related code with pure Spring Data JDBC and QueryDSL
Proposed API Changes
Spring Data JPA
Generate OQL DSL with QueryDSL and replace all custom JPA queries (either in "@Query" annotations and/or in direct use of "EntityManager").
Spring Data JDBC
Generate SQL DSL with QueryDSL and replace all (String based) SQL queries (either in "@Query" annotations and/or in direct use of "JdbcTemplate").
Business logic services
Avoid all direct use of JdbcTemplate or EntityManager. Make sure all query related data is contained only in repository classes. Optional: enforce this as a architecture rule with ArchUnit.
Risks
Effectively we'll have three different database abstraction mechanisms in place with with slightly different behavior:
- Spring Data JPA
- Spring Data JDBC
- QueryDSL
It might make sense to eliminate at least one of them in a later iteration.
ETA
We have roughly 50 feature packages/modules with database related functionality. Some are larger than others; first migrations could take a bit longer before we establish a routine. A rough calculation would be 3-4 days per feature/module which results in 150-200 development days or about 6 months to finish this task. It might make sense to introduce specific unit tests to ensure that we don't introduce regressions. This could add some overhead (maybe 1 day) to the initial estimate.
Diagrams
TBD
Attachments
Issue Links
- links to