Flyway ¶
Flyway is a library to manage and execute schema migration scripts against a relational database.
Another similar tool is Liquibase. The main difference between Flyway and Liquibase is how database changes are defined. With Liquibase, you write them in an XML document, and the library translates them into the SQL dialect of the target database. This is convenient if your project targets multiple relational databases.
With Flyway, you write the changes in native SQL. The library also supports database migrations in Java classes if the changes cannot be expressed in SQL alone.
Both tools are great and do their job. I highly recommend adding a database migration library to every project. There is an initial effort to make, but over time this pays off with easier and organized database migrations. And you are going to see in this project that setting up Flyway is not that complicated, because Spring Boot already takes care of the configuration.
jOOQ ¶
jOOQ is a database access library that provides a fluent API for SQL query construction and execution. jOOQ includes generators that generate Java code from your database. This lets you build type-safe SQL queries and provides code completion in your IDE.
The library also includes code generators for Data Access Objects (DAO) and POJOs to perform CRUD operation in your application efficiently. jOOQ does not hide the underlying database like a JPA library. Instead, it embraces it and gives an application full access to all provided SQL features of your database.
Notice that jOOQ is dual-licensed. If you are using an open-source database, you can use the Open Source edition of jOOQ, which is released under the Apache 2.0 license, for free. The Open source edition supports the following databases:
- CUBRID 8.4 and later
- Derby 10.10 and later
- Firebird 2.5, 3.0
- H2 1.3, 1.4
- HSQLDB 2.2 and later
- MariaDB 5.2 and later
- MySQL 5.5 and later
- PostgreSQL 9.3 and later
- SQLite 3
If your project uses a commercial database like Oracle and MS SQL Server, you have to buy a license. Check out the pricing page for more information: https://www.jooq.org/download/
In this blog post, I'm going to use MariaDB and the Open Source edition of jOOQ. This version is also available from the Maven Central repository.
Getting started ¶
When I create a new Spring Boot application, I usually generate the skeleton application with Spring Initializr. For this tutorial I use the HTTP API of Spring Initializr and generate the application with curl:
curl https://start.spring.io/starter.zip -d dependencies=web,jooq,flyway -d name=sbjooqflyway -d groupId=ch.rasc -d artifactId=sbjooqflyway -d javaVersion=11 -d applicationName=Application --output starter.zip
This command downloads a file called starter.zip. Unzip it into any directory and import it into your favorite IDE.
Most of the heavy lifting for setting up jOOQ and Flyway is done by Spring Boot's auto configurers. Flyway can be used without any further configuration, and for jOOQ, we only have to configure the code generator, which is optional but highly recommended.
One thing we have to configure is the data source. Open src/main/resources/application.properties and insert
the database URL and, if required, username and password. My setup for MariaDB looks like this.
spring.datasource.url=jdbc:mariadb://localhost:3366/demo
spring.datasource.username=demo
spring.datasource.password=demo
Add the JDBC driver and the corresponding jOOQ library as dependencies to your project. For a Maven project with MariaDB, you add this dependency.
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-mysql</artifactId>
</dependency>
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
</dependency>
For both libraries, you don't have to specify the version because the Spring Boot POM manages the version.
We are ready to create our first Flyway migration file. This is going to be the file that describes the initial database structure of our application.
In the folder src/main/resources/db/migration create the file V0001__initial.sql. You can choose a different name,
you just have to make sure that you follow the Flyway naming pattern
To keep this demo concise and straightforward, I create only one table.
CREATE TABLE employee (
id INTEGER NOT NULL AUTO_INCREMENT,
user_name VARCHAR(100) NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
gender CHAR(1) NOT NULL,
hire_date DATE NOT NULL,
UNIQUE KEY (user_name),
PRIMARY KEY(id)
);
This is just a regular SQL script. There is nothing special going on here. You can use any SQL command that your database supports.
When you start the application, Spring Boot automatically looks for files in the src/main/resources/db/migration folder
and triggers the Flyway migration. If you don't like the default location, you can change the path in application.properties
#override default
spring.flyway.locations=classpath:migration/flyway
#multiple locations
spring.flyway.locations=classpath:migration/db1,classpath:migration/db2
Sometimes you can't migrate with SQL only. Maybe there are some complex calculations involved, or you need to access an external service to migrate a table. To support this case, Flyway allows Java-based migration files. Flyway automatically instantiates these special classes and supplies a database connection. Your code then can do whatever is necessary for migrating the database.
To demonstrate this, I created a Java migration for the initial data load.
The default location for Java based migration files is src/main/java/db/migration.
I called my migration file V0002__initial_import.java. The version V0002 ensures that this code runs after the create table in the V0001 file. The name of the Java migration files also has to follow the Flyway naming pattern.
The initial import reads a CSV file with the univocity-parsers and inserts the data into the
employee table.
All the database statements inside the migrate() method run inside a transaction
and Flyway automatically commits the transaction when the method finishes without any error.
package db.migration;
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.table;
import static org.jooq.impl.DSL.using;
import java.io.InputStream;
import java.sql.Date;
import java.util.List;
import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.jooq.DSLContext;
import org.jooq.impl.DSL;
import org.springframework.core.io.ClassPathResource;
import com.univocity.parsers.csv.CsvParser;
import com.univocity.parsers.csv.CsvParserSettings;
public class V0002__initial_import extends BaseJavaMigration {
@Override
public void migrate(Context context) throws Exception {
ClassPathResource cpr = new ClassPathResource("employees.csv");
try (InputStream is = cpr.getInputStream()) {
CsvParserSettings settings = new CsvParserSettings();
settings.getFormat().setDelimiter(',');
settings.getFormat().setQuote('"');
CsvParser parser = new CsvParser(settings);
List<String[]> rows = parser.parseAll(is);
DSLContext dsl = using(context.getConnection());
for (String[] row : rows) {
dsl.insertInto(table("employee"), field("user_name"), field("birth_date"),
field("first_name"), field("last_name"), field("gender"), field("hire_date"))
.values(row[0], DSL.cast(row[1], Date.class), row[2], row[3], row[4],
DSL.cast(row[5], Date.class))
.execute();
}
}
}
}
Since Spring Boot 2.2, it is also possible to put Java Flyway migration classes in any package you want. Spring Boot automatically picks them
up as long as these classes are configured as Spring beans. You can do this with either an annotation (for example, @Component) or
create the bean programmatically with XML or Java config. The class name still has to follow the Flyway naming pattern. The benefit
of this solution is that you can inject other Spring beans into the migration class.
@Component
public class V0005__test extends BaseJavaMigration {
@Override
public void migrate(Context context) throws Exception {
System.out.println("running migration 5");
}
}
The code in this migration uses jOOQ as a query builder and executor but does not use generated jOOQ code, which we will generate in a second. The reason for this is that over time, your database schema can and will change, and so does the generated jOOQ code.
If our migration code depends on generated classes, we run into the situation that we reference columns or tables in an older migration class that are no longer available. For instance, if we decide in a future version to remove the gender column, our migration code
would no longer compile because of the missing gender definition in the generated jOOQ classes.
Admittingly, writing queries this way has other drawbacks, it can lead to errors and problems when you misspell table or column names.
For the main application, we want to take advantage of jOOQ's code generator. It allows us to write type-safe queries, gives us code completion in the IDE, and with the generated POJOs and DAOs, we can very quickly implement CRUD functionality into our application.
The skeleton Spring Boot application did not add a code generator, because there are different ways how to generate jOOQ code.
For this demo application, I choose to generate code with the jOOQ Maven plugin using the DDLDatabase extension, which generates code from the Flyway scripts.
I added the following code to my pom.xml. I use a profile here because I don't want to run the generator each time I run a build. In my experience, the database does not change every day, but you might build an application multiple times a day.
The generator is configured this way that it reads the Flyway migration files from src/main/resources/db/migration/* and writes the code into target/generated-sources/jooq.
I also enabled POJO (<pojos>true</pojos>) and DAO (<daos>true</daos>) generation.
<profiles>
<profile>
<id>jooq</id>
<build>
<plugins>
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>${jooq.version}</version>
<executions>
<execution>
<id>generate-jooq-sources</id>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
<configuration>
<generator>
<generate>
<pojos>true</pojos>
<daos>true</daos>
<pojosEqualsAndHashCode>true</pojosEqualsAndHashCode>
<javaTimeTypes>true</javaTimeTypes>
</generate>
<database>
<name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
<inputCatalog></inputCatalog>
<inputSchema>PUBLIC</inputSchema>
<outputSchemaToDefault>true</outputSchemaToDefault>
<outputCatalogToDefault>true</outputCatalogToDefault>
<properties>
<property>
<key>sort</key>
<value>semantic</value>
</property>
<property>
<key>scripts</key>
<value>src/main/resources/db/migration/*</value>
</property>
<property>
<key>defaultNameCase</key>
<value>lower</value>
</property>
</properties>
</database>
<target>
<clean>true</clean>
<packageName>ch.rasc.sbjooqflyway.db</packageName>
<directory>target/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta-extensions</artifactId>
<version>${jooq.version}</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</profile>
</profiles>
To start the jOOQ generator, run this command:
mvnw generate-sources -P jooq
If no error occurs, you should find new classes in target/generated-sources/jooq. Add this
path to the source path in your IDE, because the application code will reference these classes.
This is just one way how to generate jOOQ code. There are other ways to do that, like generating from a database or JPA entities.
You could also write the generated code into the src folder and commit it, together with your other code, into the version control system.
Read more about code generation on the official documentation page:
https://www.jooq.org/doc/latest/manual-single-page/#code-generation
With the generated jOOQ code, we can start writing the business logic. For this demo, I created the following simple rest controller.
Two beans DSLContext dsl and Configuration jooqConfiguration are injected into this controller. Both beans are auto-created by Spring Boot during startup. The configuration instance is needed for instantiating the generated DAO classes, and the DSLContext instance is used
for writing and executing SQL queries.
@RestController
public class EmployeeController {
private final EmployeeDao employeeDao;
private final DSLContext dsl;
private final TransactionTemplate transactionTemplate;
public EmployeeController(DSLContext dsl, Configuration jooqConfiguration,
TransactionTemplate transactionTemplate) {
this.employeeDao = new EmployeeDao(jooqConfiguration);
this.dsl = dsl;
this.transactionTemplate = transactionTemplate;
}
@GetMapping("/listEmployees")
public List<Employee> employees() {
return this.employeeDao.findAll();
}
@PostMapping("/deleteEmployee")
public void delete(@RequestParam("id") int id) {
this.employeeDao.deleteById(id);
}
@PostMapping("/newEmployee")
public Employee newEmployee(@RequestBody Employee newEmployee) {
this.employeeDao.insert(newEmployee);
System.out.println(newEmployee.getId());
return newEmployee;
}
@PostMapping("/updateEmployee")
public Employee updateEmployee(@RequestBody Employee newEmployee) {
this.employeeDao.update(newEmployee);
return newEmployee;
}
All the methods above use the DAO, but you can also write and execute arbitrary SQL with the DSLContext.
@GetMapping("/findEmployees/{name}")
public Integer[] findEmployees(@PathVariable("name") String name) {
return this.dsl
.select(EMPLOYEE.ID)
.from(EMPLOYEE)
.where(EMPLOYEE.FIRST_NAME.contains(name).or(EMPLOYEE.FIRST_NAME.contains(name)))
.fetchArray(EMPLOYEE.ID);
}
@GetMapping("/countEmployees")
public int count() {
return this.dsl.fetchCount(EMPLOYEE);
}
For this example, I created the EmployeeDao instance in the constructor of the controller.
Alternatively, you can configure the EmployeeDao as Spring-managed bean and then inject that
bean into other services. This is a better approach if you need to access the DAO in multiple
Spring managed controllers or services.
import org.springframework.context.annotation.Bean;
import org.jooq.Configuration;
import ch.rasc.sbjooqflyway.db.tables.daos.EmployeeDao;
@org.springframework.context.annotation.Configuration
public class SomeConfigClass {
@Bean
public EmployeeDao employeeDao(Configuration jooqConfiguration) {
return new EmployeeDao(jooqConfiguration);
}
}
Transactions ¶
In the examples above, we used the auto-commit mode from JDBC for transaction handling. In this mode, every update sent the database is immediately made permanent.
As soon as you send multiple updates to the database and expect them to run in an all-or-nothing fashion, you have to start a transaction.
In a Spring application with jOOQ, you can achieve that in multiple ways.
With the declarative way you annotate the method that has to run inside a database transaction with @Transactional and Spring takes care of starting a transaction before it calls the method and committing the transaction after the method returns without any error or rolling back the transaction when the method throws an exception.
In the following example, nothing will be inserted because the method throws a null pointer exception after the first
insert. If you remove @Transactional, the first insert will succeed because, in that case, the JDBC auto-commit mode is used.
@GetMapping("/insertMultiple1")
@Transactional
public void insertMultiple1() {
Employee e1 = new Employee();
e1.setFirstName("1");
e1.setLastName("1");
e1.setUserName("1");
e1.setBirthDate(LocalDate.now());
e1.setGender("M");
e1.setHireDate(LocalDate.now());
this.employeeDao.insert(e1);
if (true) {
throw new NullPointerException();
}
Employee e2 = new Employee();
e2.setFirstName("2");
e2.setLastName("2");
e2.setUserName("2");
e2.setBirthDate(LocalDate.now());
e2.setGender("M");
e2.setHireDate(LocalDate.now());
this.employeeDao.insert(e2);
}
Another approach is using the programmatic approach. Either with Spring's TransactionTemplate
or with jOOQ's own transaction support. The programmatic approach allows for more fine-grained control over the transactions.
For using Spring's programmatic transaction management, you need to inject the TransactionTemplate
and then supply your code as lambda block to the transactionTemplate.execute() method. The whole
block will run within a transaction, and Spring automatically commits the transaction or rolls it back in case of an error.
private final TransactionTemplate transactionTemplate;
public EmployeeController(..., TransactionTemplate transactionTemplate) {
...
this.transactionTemplate = transactionTemplate;
}
@GetMapping("/insertMultiple2")
public void insertMultiple2() {
this.transactionTemplate.execute(txStatus -> {
Employee e1 = new Employee();
e1.setFirstName("1");
...
this.employeeDao.insert(e1);
Employee e2 = new Employee();
e2.setFirstName("2");
...
this.employeeDao.insert(e2);
return null;
});
}
The other option is using jOOQ's programmatic transaction support.
Similar to the Spring template approach you run your code as lambda block inside the
dsl.transaction() method.
Be aware that you can't use dsl and employeeDao from the outer scope.
You have to create a new DAO with the configuration object (txConf) the method passed to
the lambda and use DSL.using(...) if you need to create and execute a SQL query.
Like the other two approaches above, transaction() automatically commits or rolls back the transaction.
@GetMapping("/insertMultiple3")
public void insertMultiple3() {
this.dsl.transaction(txConf -> {
EmployeeDao txEd = new EmployeeDao(txConf);
Employee e1 = new Employee();
e1.setFirstName("1");
...
txEd.insert(e1);
int count = DSL.using(txConf).fetchCount(EMPLOYEE);
System.out.println(count);
Employee e2 = new Employee();
e2.setFirstName("2");
...
txEd.insert(e2);
});
}
Migration ¶
In the last section, we follow a typical migration workflow.
Our client sends us a new requirement that the application should manage not only employees but also departments. Additionally, each employee needs to be assigned to a department.
We start by creating a new SQL migration file: src/main/resources/db/migration/V0003__add_department.sql
In this file, we create the new department table, insert all available departments,
and create a foreign key department_id in the employee table.
We want to make the department_id column not nullable because it is a mandatory field in our application.
We can't just specify NOT NULL when we call ADD COLUMN because there are already rows stored in this table.
Therefore, we assign the artificial Unset department to all employees and then change the column definition
to NOT NULL.
CREATE TABLE department (
id INTEGER NOT NULL AUTO_INCREMENT,
no CHAR(4) NOT NULL,
name VARCHAR(40) NOT NULL,
PRIMARY KEY(id)
);
INSERT INTO department(no, name) VALUES
('d001','Marketing'),
('d002','Finance'),
('d003','Human Resources'),
('d004','Production'),
('d005','Development'),
('d006','Quality Management'),
('d007','Sales'),
('d008','Research'),
('d009','Customer Service'),
('dddd','Unset');
ALTER TABLE employee ADD COLUMN department_id INTEGER;
UPDATE employee SET department_id = (SELECT id FROM department where no = 'dddd');
ALTER TABLE employee MODIFY department_id INTEGER NOT NULL;
Our client also sent us an Excel that contains the current department assignments for each employee.
To import this Excel file, we create a Java Flyway migration class
src/main/java/db/migration/V0004__assign_department.java
The first part of the migrate method reads the contents of the Excel into a Map.
Map<String, String> userToDepartment = new HashMap<>();
ClassPathResource cpr = new ClassPathResource("employee_department.xlsx");
try (InputStream is = cpr.getInputStream();
Workbook wb = WorkbookFactory.create(is)) {
Sheet sheet = wb.getSheetAt(0);
if (sheet != null) {
Iterator<Row> rowIt = sheet.rowIterator();
while (rowIt.hasNext()) {
Row row = rowIt.next();
userToDepartment.put(row.getCell(0).getStringCellValue(),
row.getCell(1).getStringCellValue());
}
}
}
And the second part links the department_id column in the employee table to
the department. Afterward, it checks if every employee has a valid department assigned to it
and deletes the special Unset department we created earlier.
If there are still employees assigned to the Unset department, somebody has to fix them manually.
package db.migration;
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.select;
import static org.jooq.impl.DSL.table;
import static org.jooq.impl.DSL.using;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.jooq.DSLContext;
import org.springframework.core.io.ClassPathResource;
public class V0004__assign_department extends BaseJavaMigration {
@Override
public void migrate(Context context) throws Exception {
Map<String, String> userToDepartment = new HashMap<>();
ClassPathResource cpr = new ClassPathResource("employee_department.xlsx");
try (InputStream is = cpr.getInputStream();
Workbook wb = WorkbookFactory.create(is)) {
Sheet sheet = wb.getSheetAt(0);
if (sheet != null) {
Iterator<Row> rowIt = sheet.rowIterator();
while (rowIt.hasNext()) {
Row row = rowIt.next();
userToDepartment.put(row.getCell(0).getStringCellValue(),
row.getCell(1).getStringCellValue());
}
}
}
DSLContext dsl = using(context.getConnection());
for (Map.Entry<String, String> entry : userToDepartment.entrySet()) {
dsl.update(table("employee")).set(field("department_id", Integer.class),
select(field("id", Integer.class)).from(table("department"))
.where(field("no", String.class).eq(entry.getValue())).limit(1).asField())
.where(field("user_name", String.class).eq(entry.getKey())).execute();
}
int employeesWithUnsetDep = dsl.selectCount().from(table("employee"))
.innerJoin(table("department"))
.on(field("department.id", Integer.class)
.equal(field("employee.department_id", Integer.class)))
.where(field("department.no", String.class).eq("dddd"))
.fetchOne(0, Integer.class);
if (employeesWithUnsetDep == 0) {
dsl.delete(table("department")).where(field("no").eq("dddd")).execute();
}
}
}
Finally, rerun the jOOQ code generator so we can start writing code for the new department table.
mvnw generate-sources -P jooq
@RestController
public class DepartmentController {
private final DepartmentDao departmentDao;
private final DSLContext dsl;
public DepartmentController(DSLContext dsl, Configuration jooqConfiguration) {
this.departmentDao = new DepartmentDao(jooqConfiguration);
this.dsl = dsl;
}
@GetMapping("/departments")
public List<Department> departments() {
return this.departmentDao.findAll();
}
@GetMapping("/department/{no}")
public Department findDepartment(@PathVariable("no") String no) {
return this.dsl
.selectFrom(DEPARTMENT)
.where(DEPARTMENT.NO.eq(no))
.fetchOne()
.into(Department.class);
}
}
You find the complete source for this application on GitHub:
https://github.com/ralscha/blog2019/tree/master/sbjooqflyway
This concludes this blog post about Flyway and jOOQ. A powerful couple for Java database application development.
For further information, visit the homepages of the projects:
Visit the Spring Boot documentation for more information about the integration: