I have a scenario where I need to connect to multiple databases within my application. I have a custom module that reads & writes to a reporting database which is different from openmrs and runs on postgres. Has anyone any experience with this?
I have tried creating a separate sessionFactory (reporting_db) and transactionManager (hibernate) for it and it has worked with the base modules for o3 installed - (fhir2, iniz, legacyui & webservices.rest). Is this the best practice to have multiple transaction managers and/or would this be considered “correct” approach to this challenge?
When I however add a module that depends on metadatadeploy it fails because in this class, (AbstractMetadataBundle.java) there’s an autowired PlatformTransactionManager to be injected and at this point, there are two (2) defined (transactionManager by core-api & myReportingTransactionManager by my custom module) and it’s not specified/qualified which of the two to be injected.
What would be the best approach to solve this? Qualify the one in core as primary? Autowire with qualifier in the AbstractMetadataBundle class or which other option? Please help!
Is there any reason why you have not done this from a process which is completely outside of the OpenMRS instance? Some sort of standalone application which just directly connects to the OpenMRS MySQL database.
@dkayiwa this is specifically for MambaETL which is an OpenMRS ETL reporting module that needs to connect to two/mulitiple different datasources (the primary/transactional datasource) to extract data and the the target (ETL datasource) for processing hence the two datasources mentioned above.
The reason this is an OpenMRS module and not a standalone app, is to make MambaETL easy/familiar to use/deploy in the community (the good old OpenMRS omod) but also this module introduces some functionality such as a REST/Service layer that builds ontop of the capabilities of OpenMRS hence the need to have it as an omod as well. Initially we had created a simple JAR for MambaETL but we started running into limitations such as those thus we switched to the omod.
For performance reasons, i would not use hibernate at all. I would directly connect to the OpenMRS MySQL database (even within the MambaETL embedded openmrs module), through a JDBC connection and use direct SQL statements to extract data from one database to the other.
@dkayiwa thank you so much for your feedback, for now we will go with the direct JDBC connection however we recommend that the issue of not being able to use multiple datasources in OpenMRS (when certain modules are introduced) is tracked for resolution as this is a general requirement. We have some ideas on how to resolve this after digging through some of the conflicting modules.
@alaboso