How to programtically insert versionized initial data into Spring Boot applications

One of the common required tasks for an application using a persistence store is to initialize the underlying database with basic data sets. Most of the time this contains something like admin users or default roles.

Setting the stage

To give a proper example, we have the database table role with two columns id (primary key) as an internal ID and uuid (primary key) as an external key.
In Liquibase, our changeset for this table has the following definition:

	<changeSet author="schakko" id="schema-core">
		<createTable tableName="role">
			<column name="id" type="BIGSERIAL" autoIncrement="true">
				<constraints nullable="false" primaryKey="true" unique="true"
					uniqueConstraintName="unq_role_id" />
			</column>
			<column name="uuid" type="UUID">
				<constraints nullable="false" primaryKey="true" unique="true"
					uniqueConstraintName="unq_role_uuid" />
			</column>
			<column name="name" type="varchar(255)">
				<constraints nullable="false" unique="true" />
			</column>
		</createTable>
	</changeSet>

My requirements are:

  • I want to add multiple custom roles into this table
  • The uuid field must be randomly generated
  • The schema definition must work on H2 and PostgreSQL without the uuid-ossp module. Our application backend is responsible for the generation of UUIDs.

Initializing databases with Spring Boot’s native features

With Java, specifically Spring Boot, there are two ways to initialize the database:

  1. Hibernate, and therefore Spring Boot with JPA, checks for a file named import.sql in the root of the classpath. This file is executed on startup when Hibernate creates the schema.
  2. The file data.sql, respectively data-${platform}.sql for concrete DBMS’, are used for importing SQL data by using the pure JDBC datasource without using any JPA stuff.

For simple tasks, both options are feasible. But in our case it can’t fulfil the requirements: A common SQL UUID generator function like generate_uuid() does not exist and probably won’t ever be standardized in SQL. So we need two separate data.sql files, one for each database management system. In addition to that, we still don’t have access to the OSSP module for generating a UUID in PostgreSQL.

Inserting data programtically

Why not using a simple ApplicationListener to generate the roles during the startup of the Spring framework?

@RequiredArgsConstructor
@Component
@Order(Ordered.HIGHEST_PRECEDENCE)
public class InsertRoleStamdata implements ApplicationListener<ApplicationReadyEvent> {
	@NonNull
	private final RoleRepository roleRepository;

	public void onApplicationEvent(ApplicationReadyEvent event) {
		if (roleRepository.count() > 0) {
			return;
		}

		roleRepository.save(new Role("ADMIN", java.util.UUID.randomUUID()));
	}
}

This does obviously work and is executed on every application’s startup. With the if condition, we ensure that we only insert a role if there is no role present yet.
But what happens if the role ADMIN has to be renamed to ADMINISTRATOR? If you think about it, the code above can rapidly change into some ugly monster with various condition checkings and edge cases. In the case you want to refactor it to split a migration into different classes, you have to retain the order of the executed listener and so on.
And besides of this, we need some traceable versionining.

Using a schema migration tool

For obvious reasons, a schema migration tool like Liquibase or Flyway should be the way to go. But how can it fulfil our requirements?

In Liquibase we can define a changeset which uses the insert tag:

    <changeSet author="schakko" id="role-stamdata">
        <insert tableName="role">
            <column name="uuid" value="${random_uuid_function}"/>
            <column name="name" value="ADMIN"/>
        </insert>
    </changeSet>

This is fine, but as already mentioned:

Neither Flyway nor Liquibase are able to interpolate a variable placeholder (like ${random_uuid_function}) with a function callback defined in Java.

Using a schema migration tool programatically

Fortunately, Flyway and Liquibase both support programatically defined changesets: You can write Java code which executes the SQL statement. In Liquibase you have to use the customChange tag. The following code snippet describes the required definition in YAML:

databaseChangeLog:
     - changeSet:
         id: create-default-roles
         author: schakko
         changes:
             - customChange:
                 class: de.schakko.sample.changeset.DefaultRoles20171107

The class de.schakko.sample.changeset.DefaultRoles20171107 must implement the interface CustomTaskChange:

public class DefaultRoles20171107 implements CustomTaskChange {

	@Override
	public String getConfirmationMessage() {
		return null;
	}

	@Override
	public void setUp() throws SetupException {
	}

	@Override
	public void setFileOpener(ResourceAccessor resourceAccessor) {
	}

	@Override
	public ValidationErrors validate(Database database) {
		return null;
	}

	@Override
	public void execute(Database database) throws CustomChangeException {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(new SingleConnectionDataSource(((JdbcConnection)database.getConnection()).getUnderlyingConnection(), false));
		jdbcTemplate.update("INSERT INTO role (uuid, name) VALUES(?, ?,)", new Object[] { java.util.UUID.randomUUID(), "ADMIN" });
	}

}

Liquibase’s Spring Boot auto-configuration is executed in an early stage in which Hibernate is not loaded. Because of this we can’t inject any Spring Data JPA repositories by default. Even accessing the Spring context is not so easy. You need to provide the application context through a static attribute and so on.
With Flyway the Spring integration is much better.

Conclusion

This blog post demonstrated how initial data can be inserted into a Spring Boot application’s database. In addition to that we discussed how this data can be versionized in a database-independent manner.