Hi, I recently rised some PR's to introduce a new KIE Flyway Add-On that tries to solve some issues we found when enabling Flyway to initialize the application database in a compact setup (having runtime with other modules that require persistence in the same app like data-index, jobs-service and data-audit). In this setup, a single Flyway instance is executed for the whole application during the startup and we are configuring it to apply all the SQL scripts (sorted by the version specified in the file name) from all those modules and keeping a single index for all the scripts applied during the Flyway execution.
The main problems we identified are related to the versioning of those SQL files inside the modules. Flyway gets script information (version, description...) from the file name (Ej: V1.35.0__create_runtime_PostgreSQL.sql -> version is 1.35.0) and expects version to be unique, it doesn't discriminate script source, description. Each of our add-on uses its own versions in the SQL scripts (ej: runtime persistence goes from V1.35.0 to V10.0.1, data-index from V1.32.0 to V1.45.0.4...), in this scenario it is very likely to have collisions if different modules use the same version in the script name. Additionally, it's also possible that for the same reason, DB upgrades won't work. For example if a module is updated to bring new SQL Scripts to upgrade the DB (let's say data-index bringing V1.5.0) the same application running a Flyway again won't be able to apply those changes if in a previous run another add-on brought a script with a higher version (in this case runtime-persistence has v10). Another issue is that, in this setup, Flyway must be configured to know the locations of all the SQL scripts in the applications.properties. In general, this SQL files are in different locations, and there are modules that provide SQL's for different DB types (h2, pgsql). This can be a bit cumbersome to have it properly configured, specially for customers that are not familiar with the internals of our code. So, in order to help here, we are adding this Kie Flyway Add-On. The idea is that instead of using the default Flyway integration provided by the Platform (Quarkus/Spring-Boot), this Add-on will be in charge of running a separate instance of Flyway for each module that requires it and using an independent index for each one them, this way we can ensure that there will be no friction between SQL scripts. To make an module available for KIE Flyway, first we must add the following dependency in our module pom.xml: <dependency> <groupId>org.kie</groupId> <artifactId>kie-addons-<quarkus|springboot>-flyway</artifactId> </dependency> And make sure we include a kie-flyway.properties descriptor located at ` src/main/resources/META-INF` with the following properties: - module.name: logic name to identify the module. - module.locations.<db_type>: this property map is used to setup the script locations per each database supported by our module, (ej: module.locations.postgresql=classpath:<path-to-my-sql-files>). It's possible to use the module.locations.default key if we want to use some fallback SQL's in case the application Database type isn't supported. This locations should refer to a path (or comma-separate paths) unique to the module to avoid possible collisions and we should as much as possible using default Flyway locations like db/migrations. You'll notice that I moved the current SQL scripts from its location to src/main/resources/kie-flyway/db/<module-name>/<db-type> You can see an example of this file here (kie-flyway.properties <https://github.com/apache/incubator-kie-kogito-runtimes/blob/5037c9de5608fde136fef274effd3045c91c162f/addons/common/persistence/jdbc/src/main/resources/META-INF/kie-flyway.properties>) and the new module locations here (locations <https://github.com/apache/incubator-kie-kogito-runtimes/tree/5037c9de5608fde136fef274effd3045c91c162f/addons/common/persistence/jdbc/src/main/resources/kie-flyway/db/persistence-jdbc> ). This addon supports the following configurations in the application.properties file: - kie.flyway.enabled=true|false # Enables the execution of the KIE Flyway Add-On on startup, it's disabled by default in prod mode (Quarkus) and fully disabled in Spring-boot. - kie.flyway.<module-name>.enabled=true|false # This is used to disable specific module if we need to Some considerations: - This Add-On is intended to be used for development / test / examples and not recommended to use in production - As all our Add-Ons will use the default application datasource, so it's not possible to make it use a different - It doesn't cover all the Flyway features, it's only used to run migrations to initialize our components. - It doesn't replace the Platform Flyway in the kogito applications, the user can still use Flyway to load its own scripts without having to care of configuring anything from our modules. At this point, for simplicity I only tried to address the DB Initialization of our Add-Ons (runtime persistence, data-index, jobs-service, data-audit & user-tasks), but this feature can be also brought to the Data-Index & Jobs Service if we want to, the only reason I didn't do it is to avoid doing changes that could break the Sonataflow Operator and the examples, but I'll happily apply the changes there if there's an agreement on this. I think that's all... sorry for this long email but I think this improvement required some explanations. Thank you! Pere
