A common task in the automation environment is to connect a database to a vCenter Orchestrator (vCO) for various reasons. Often there is build a straightforward solution (like in this example) in an action or workflow script inside of a workflow. But in terms of a state-of-the-art programming style, a solution using the CRUD pattern is desirable to achieve encapsulation. Also it is no good coding style to repeat the code for the database connection in every single action which connects to the database.
A solution to this would be writing a vCO-plugin encapsulating the database connection and written in Java code using common solutions like spring-data. The main drawback of this solution is the separation of development platforms. The business logic you are writing is developed in the vCO but the database connection is maintained in a Java IDE. So you have to design data transfer objects (DTOs) for the communication between the business code in vCO and the database connection inside the plugin. In case of a change of the database objects in the JAVA IDE their usage in the vCO also has to be changed. This cannot be a desirable solution at all because this is a common source of bugs. Often only the DTO implementation will be changed correctly leading to wrong communication between business code and database connection. So a better solution would be writing the database connection directly inside the vCO.
Assure static type system
By that the SQL-table-schemas are introduced to the vCO without needing to write a special plugin describing the DTOs.
Afterwards the DTO fields can be wired as ConfigurationElements inside your workflow scripts or actions and used as follows
A preferable extension to this would be the possibility to send custom select queries to the database server in a similar way. The following code snippet shows a readQuery-method doing exactly this.
NOTE: This script can be used to minimize duplicated code when implementing read and readAll operations by calling it inside of their implementations after creating the query. In the same way duplicated code in the create and update operations can be prevented.
All code listings can be found on our gitHub repository.