Most of the times when we have a service in production, we might make changes to the database schema. We want to make sure of two things here.
- Database changes are version controlled
- Changes to actual database are done in a controlled fashion.
I am not very comfortable with some CI/CD job going on its own and making changes to database schema. Deployments that need schema changes are done as part of planned maintenance cycles and need to be monitored by actual humans.
So, what do I expect from a solution that takes care of schema versioning.
- The service should not be allowed to start if the schema is not in sync with the in-memory entities and code that is designed to use them
- An easy mechanism should be provided for migration of schema version. This is generally done through different phases of development like dev/stage/test and then to production., We want to automate it in the sense that there should be scripts that are tested and validated through previous phases of development lifecycle but still has an option of actual user triggering it in production.
With above two goals, let's try to define a database schema versioning system. I am going to use flywaydb as the primary tool that does it with configurations that make it useful for my usecase. First let's define a configuration file that takes defines database connectivity option for my database.
You would remember since ours is a multi-tenant application, we have three databases and we have define configurations for all the three databases.
Here we have all the three database configurations defined. Now let's make changes to pom.xml for dependencies.
If you are just creating your application, then your life is easy, just create the initial sql file and add it to your src/resources/db/migration directory. Be sure to name it something like V1.0__Initial_Schema.sql. You need to make sure the first character is V, U, or R for version, undo, repeatable respectively. After that you have a version number, you can follow any version number strategy but it should be in the form of the dotted notation of ver.major.minor. For more details on this, please read here on how this can be effectively used. What we need to understand is that flyway will consider a version 1.1 to be a version later than version 1.0.
Now that we have the existing SQL stored in the file, we can baseline our current schema so that any future changes could be incorporated using this mechanism.
$ mvn flyway:baseline -Dflyway.configFiles=./tutorials/config/default.config
[INFO] Scanning for projects... [INFO] [INFO] -----------------< in.springframework.blog:tutorials >------------------ [INFO] Building tutorials 0.0.1-SNAPSHOT [INFO] --------------------------------[ jar ]--------------------------------- [INFO] [INFO] --- flyway-maven-plugin:6.5.5:baseline (default-cli) @ tutorials --- Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. [INFO] Flyway Community Edition 6.5.5 by Redgate [INFO] Database: jdbc:mysql://localhost:3306/tutorial (MySQL 8.0) [INFO] Creating Schema History table `tutorial`.`flyway_schema_history` with baseline ... [INFO] Successfully baselined schema with version: 1.0 [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 1.285 s [INFO] Finished at: 2020-08-25T10:14:53+05:30 [INFO] ------------------------------------------------------------------------
Make sure the pathname of the configuration file is appropriate for your scenario. Also like in my case, since I have multiple databases due to multi-tenancy, I will need to do this for each of the databases. Once done, I can check the status of schema using the info command.
$ mvn flyway:info -Dflyway.configFiles=./tutorials/config/default.config
[INFO] Scanning for projects... [INFO] [INFO] -----------------< in.springframework.blog:tutorials >------------------ [INFO] Building tutorials 0.0.1-SNAPSHOT [INFO] --------------------------------[ jar ]--------------------------------- [INFO] [INFO] --- flyway-maven-plugin:6.5.5:info (default-cli) @ tutorials --- Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. [INFO] Flyway Community Edition 6.5.5 by Redgate [INFO] Database: jdbc:mysql://localhost:3306/tutorial (MySQL 8.0) [INFO] Schema version: 1.0 [INFO] [INFO] +----------+---------+----------------+----------+---------------------+----------+ | Category | Version | Description | Type | Installed On | State | +----------+---------+----------------+----------+---------------------+----------+ | | 1.0 | Base Migration | BASELINE | 2020-08-25 10:13:12 | Baseline | +----------+---------+----------------+----------+---------------------+----------+ [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 1.247 s [INFO] Finished at: 2020-08-25T10:13:26+05:30 [INFO] ------------------------------------------------------------------------Now our existing database is baselined and we can start using the flyway versioning mechanism. We still have one issue to resolve, since we want to make sure our application doesn't start till the time we have appropriate code backing appropriate schema. The first step is to disable any kind of migration being started by our application. So we define a null migration strategy that will not do anything.
This will ensure that no automatic migration is executed. We still want to make sure that the application exits if the schema can't be validated. For this we need to go to the method where we are creating the datasource. We add the validation of the datasource there.
In our example, since we are using Multi-tenant datasource, we add this validation there.
Look at validateDatasource method, it takes a dataSource as input and raises a SpringApplication exit if the database can't be validated. Now, let's test it. We will take our entity Profile and add a new field in, let's call it maritalStatus.
We now create a sql file that alters the table.
Now if we try to run this application, let's see what happens.
2020-08-25 11:53:54.652 ERROR 40041 --- [ main] o.s.boot.SpringApplication : Application run failed org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Unsatisfied dependency expressed through method 'flywayInitializer' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.flywaydb.core.Flyway]: Factory method 'flyway' threw exception; nested exception is org.springframework.boot.context.properties.ConfigurationPropertiesBindException: Error creating bean with name 'dataSource': Could not bind properties to 'DataSource' : prefix=spring.datasource, ignoreInvalidFields=false, ignoreUnknownFields=true; nested exception is java.lang.IllegalStateException: org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext@ba2f4ec has been closed already at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:797) ~[spring-beans-5.2.7.RELEASE.jar:5.2.7.RELEASE]
Now let's run the migration for this sql.
$ mvn flyway:migrate -Dflyway.configFiles=./tutorials/config/default.config
[INFO] Scanning for projects... [INFO] [INFO] -----------------< in.springframework.blog:tutorials >------------------ [INFO] Building tutorials 0.0.1-SNAPSHOT [INFO] --------------------------------[ jar ]--------------------------------- [INFO] [INFO] --- flyway-maven-plugin:6.5.5:migrate (default-cli) @ tutorials --- Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. [INFO] Flyway Community Edition 6.5.5 by Redgate [INFO] Database: jdbc:mysql://localhost:3306/tutorial (MySQL 8.0) [INFO] Successfully validated 3 migrations (execution time 00:00.017s) [INFO] Current version of schema `tutorial`: 1.1 [INFO] Migrating schema `tutorial` to version 1.2 - Add Marital Status [INFO] Successfully applied 1 migration to schema `tutorial` (execution time 00:00.075s) [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 1.323 s [INFO] Finished at: 2020-08-25T11:56:25+05:30 [INFO] ------------------------------------------------------------------------
$ mvn flyway:info -Dflyway.configFiles=./tutorials/config/default.config
[INFO] Scanning for projects... [INFO] [INFO] -----------------< in.springframework.blog:tutorials >------------------ [INFO] Building tutorials 0.0.1-SNAPSHOT [INFO] --------------------------------[ jar ]--------------------------------- [INFO] [INFO] --- flyway-maven-plugin:6.5.5:info (default-cli) @ tutorials --- Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. [INFO] Flyway Community Edition 6.5.5 by Redgate [INFO] Database: jdbc:mysql://localhost:3306/tutorial (MySQL 8.0) [INFO] Schema version: 1.2 [INFO] [INFO] +-----------+---------+---------------------------+----------+---------------------+----------+ | Category | Version | Description | Type | Installed On | State | +-----------+---------+---------------------------+----------+---------------------+----------+ | | 1.0 | Base Migration | BASELINE | 2020-08-25 10:15:00 | Baseline | | Versioned | 1.1 | Remove AuthToken FromUser | SQL | 2020-08-25 10:49:27 | Success | | Versioned | 1.2 | Add Marital Status | SQL | 2020-08-25 11:56:25 | Success | +-----------+---------+---------------------------+----------+---------------------+----------+ [INFO] ------------------------------------------------------------------------ [INFO] BUILD SUCCESS [INFO] ------------------------------------------------------------------------ [INFO] Total time: 1.226 s [INFO] Finished at: 2020-08-25T11:56:30+05:30 [INFO] ------------------------------------------------------------------------As we can see now, the database schema is migrated. So if we make sure all the database migration SQL files are properly made part of the project, we can eliminate schema mismatch issues to a large extent. We also have an option to make it fully automated or a single command migration with manual verification.
The hibernate ORMs are just in-memory representations of actual database schema and the schema is owned by the database, ORM changes have to be done by the programmers.
The complete code supporting this tutorial is available at my spring blog tagged as v1.8
