How do you write a Spring Boot backend which supports all the SQL databases known to mankind. I’m just kidding. I have only tried this with H2, MySQL, PostgreSQL and Oracle in one single application.
I am sure by now you are familiar with how Version Control works. A Version Control like Git will help you keep track of your source code. How do you keep track of your database changes the same way you track your source code?
Liquibase
Liquibase is an open- source solution to manage your database revisions. I am not going to go into minor details about liquibase as its documentation is quite exhaustive.
This is how you get Liquibase to work.
First add your dependency.
Create db.changelog-master.yaml inside src/main/resources/db/changelog. This is the master file to which you need to keep adding the changes. To effectively track changes to the database keep adding the changelog files to this.
Now create your Change Logs db.changelog-1.0.0.yaml, db.changelog-1.1.0.yaml and db.changelog-1.2.0.yaml inside the same directory.
Now let’s see what’s inside db.changelog-1.0.0.yaml. The DSL here creates a table with the name bank with two columns if the database is MySQL. The id column is auto- incremented.
If the database is H2, PostgreSQL or Oracle, the DSL creates a table with two columns and a sequence to generate the primary key. The id column here does not have auto- increments enabled as H2, PostgreSQL and Oracle can support database sequences.
Here is a good article on Primary Key Generation.
Overriding the Primary Key Generation Strategy
Here is the Bank entity in my Spring Project.
GenerationType.SEQUENCE will only work for databases that support sequences. For MySQL I need to override this.
Now I will create a mysql-orm.xml file inside /src/main/resources/db/orm/.
mysql-orm.xml file activates GenerationType.IDENTITY. To override the Java configuration I will point spring.jpa.mapping-resources to mysql-orm.xml. With this configuration in application-mysql.yaml, I can activate mysql profile to get my application working with MySQL.
Source Code
Sadly I do not have any source code to demonstrate this in my GitHub right now. If I get a chance to create a demo project I will post an update with its link.
