I am currently working on a Java 8 project which is a REST API deployed as a WAR file to Jetty. Our deploy process is very simple, our deploy pipeline just copies the WAR file into the Jetty directory in each environment then verifies the app is up and running with the correct version and runs some integration tests.
We wanted to be able to apply database migration scripts automatically in each environment (Dev, Test, QA, Staging, Prod) as we did our deploy, so we would no longer have to worry about manually applying scripts. In the past for Java, Scala, and .NET projects I have used several different tools, but for this project we decided to use Flyway which is very flexible and simple to setup.
The documentation for Flyway is excellent, however I decided to just post what we did in our app in case it might help someone else out. Here is our “Quick Start” setup.
1. Add the Flyway dependency
Flyway can be setup using Maven, Gradle, SBT, Ant, etc. In our project we used Maven, so all we did was add the following to our pom.xml file:
<dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> <version>3.2.1</version> </dependency>
Now, to get Flyway to check for any database changes to apply, whenever the application is started, put the following code somewhere in your applications startup sequence.
Flyway flyway = new Flyway(); //Create the dbo.schema_version table if it does not already exist flyway.setBaselineOnMigrate(true); //Where the method "getDataSource()" provides your DataSource //object that has the jdbc url, username, and password. flyway.setDataSource(getDataSource()); flyway.migrate();
3. SQL Script Location
All migrations scripts by default must go in the following folder in your app:
4. SQL Script Naming convention
Scripts are run in version number order based on their names, the default naming convention is:
For example: “V1_0_1__create_tables.sql”
All scripts must start with the letter “V”, followed by major/minor version numbers, with two underscores “__” separating the version from the description.
5. Schema Version Table
Flywaydb will automatically create a table in each database called “dbo.schema_version” which stores a log of all migration scripts that have been applied.
The table looks like:
|1||1||1||Flyway Baseline||BASELINE||Flyway Baseline||NULL||JohnSmith||2015-07-30 15:55:49.337||0||1|
|1||2||1.0.1||create tables||SQL||V1_0_1__create_tables.sql||-440358290||JohnSmith||2015-07-30 15:55:49.337||109||1|
6. Handling Failure
If a script fails, the app will fail to start and the failure information is written to our log files as well as a monitor is tripped. Flywaydb applies each script as a transaction so all changes in the script will be rolled back if any part of the script fails. This is very handy because if you commit a script with some invalid SQL syntax, all you have to do is update the script with the corrected syntax, commit it again, and let the build pipeline apply the changes from the fixed script. No messy cleanup or reset to worry about.
So that is it, you should have all you need to get your database schema changes easily synced up with the deploy of your app!