Upgrading Lead Microservice - Use MariaDB and Flyway with Spring Boot

So far I have been using an in-memory H2 database or Mockito for testing the lead microservice. To make the transition towards using the Spring Cloud Config server, I need to upgrade the micro-application to use MariaDB. I will be adding the configuration in the application.yml  the file which in the subsequent post will move over to the config server store. I will also be using Flyway to make it easy to maintain the database schema changes in future. I will use this post to introduce Flyway in the mix. Spring Boot also provides first class integration with Flyway. I am using Flyway as its really quick and easy to get started, minimal learning curve (no DSL) and I am comfortable with it having used it in the past.

Assumptions

1. MariaDB 10 is installed
2. Basic familiarity with Flyway
3. Heidi SQL client is installed.

Step 1 - Update build.gradle to include the MariaDB JDBC and Flyway dependencies.

	compile group: 'org.mariadb.jdbc', name: 'mariadb-java-client', version: '2.0.1'
	compile "org.flywaydb:flyway-core:4.2.0"

view raw build3.gradle hosted with ❤ by GitHub

Do not forget to do a Gradle refresh on your IDE (I am using STS 3.8.4 on Java 8)

Step 2 - Rename the application.properties to application.yml and add the properties shown in listing below.

	server:
	port: 8080
	spring:
	application:
	name: lead-service
	datasource:
	url: jdbc:mariadb://localhost:3306/test
	username: root
	password: root
	driver-class-name: org.mariadb.jdbc.Driver
	tomcat:
	max-wait: 10000
	max-active: 20
	test-on-borrow: true
	jpa:
	properties:
	hibernate:
	dialect: org.hibernate.dialect.MySQLDialect
	default_schema : lead_db

view raw application-lead.yml hosted with ❤ by GitHub

The lead backend service will run on port 8080. The application now has a name - 'lead-service'. This will be required by the configuration server later. I have also setup the MariaDB data source, the default Tomcat connection pool (recommended in Spring Boot documentation and also I do not want to introduce another dependency on a connection pool like Hikari or BoneCP etc as this pool is very robust). Also, I have added the dialect configuration and default schema name for Hibernate/JPA. Since I want to use the defaults on the database, Flyway will use the test schema and the Flyway managed DDL script will create the schema for the lead backend as 'lead_db'.
Step 3 - Create the DDL scripts for Flyway.
The next step is to create the DDL scripts for Flyway. The scripts should be stored with the lead-backend codebase under src/main/resources/db/migration folder. The file that creates the database schema for the first time is named 'V1.0__init.sql'. Note there are 2 '_', between 'V1.0' and 'init'.

	CREATE DATABASE IF NOT EXISTS lead_db;
	USE lead_db;
	CREATE OR REPLACE TABLE t_lead (
	id VARCHAR(40) NOT NULL,
	created_by VARCHAR(255) NULL DEFAULT NULL,
	created_date DATETIME NULL DEFAULT NULL,
	deleted BOOLEAN NULL DEFAULT NULL,/* BIT(1) */
	last_modified_by VARCHAR(255) NULL DEFAULT NULL,
	last_modified_date DATETIME NULL DEFAULT NULL,
	version INT(11) NULL DEFAULT NULL,
	city VARCHAR(255) NULL DEFAULT NULL,
	country VARCHAR(255) NULL DEFAULT NULL,
	po_box VARCHAR(255) NULL DEFAULT NULL,
	state VARCHAR(255) NULL DEFAULT NULL,
	street VARCHAR(255) NULL DEFAULT NULL,
	zip VARCHAR(255) NULL DEFAULT NULL,
	annual_revenue DOUBLE NULL DEFAULT NULL,
	assigned_team_id VARCHAR(255) NULL DEFAULT NULL,
	assigned_user_id VARCHAR(255) NULL DEFAULT NULL,
	company_name VARCHAR(255) NULL DEFAULT NULL,
	description VARCHAR(255) NULL DEFAULT NULL,
	designation VARCHAR(255) NULL DEFAULT NULL,
	email VARCHAR(255) NULL DEFAULT NULL,
	email_opt_out BOOLEAN NULL DEFAULT NULL,
	facebook VARCHAR(255) NULL DEFAULT NULL,
	fax VARCHAR(255) NULL DEFAULT NULL,
	first_name VARCHAR(255) NULL DEFAULT NULL,
	industry_lov_id VARCHAR(255) NULL DEFAULT NULL,
	last_name VARCHAR(255) NULL DEFAULT NULL,
	leadsource_lov_id VARCHAR(255) NULL DEFAULT NULL,
	leadstatus_lov_id VARCHAR(255) NULL DEFAULT NULL,
	mobile VARCHAR(255) NULL DEFAULT NULL,
	no_of_employees INT(11) NULL DEFAULT NULL,
	phone VARCHAR(255) NULL DEFAULT NULL,
	rating_id VARCHAR(255) NULL DEFAULT NULL,
	salutation_lov_id VARCHAR(255) NULL DEFAULT NULL,
	twitter VARCHAR(255) NULL DEFAULT NULL,
	website VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
	);
	CREATE OR REPLACE TABLE t_lead_extension (
	lead_id VARCHAR(40) NOT NULL,
	extensions VARCHAR(255) NULL DEFAULT NULL,
	extensions_key VARCHAR(255) NOT NULL,
	PRIMARY KEY (lead_id, extensions_key),
	CONSTRAINT lead_extension_fk FOREIGN KEY (lead_id) REFERENCES t_lead (id)
	);

view raw V1.0__init.sql hosted with ❤ by GitHub

Step 4 - Test
Check the Tomcat console logs to verify that Flyway and JPA worked without any issues.

Figure 1 - Checking the console for Flyway and JPA

Finally, check the DB with HeidiSQL if the Flyway table and 'lead_db' schema tables are created by Flyway.

Figure 2 - HeidiSQL view