Database Versioning

Introduction

Working with relational databases it is important to keep them synchronized with the data model represented in the source code. There are two popular approaches to this challenge.

Database first

This approach takes the database as the first-class citizen, while data model classes (POJOs or JPA entities) are derived from the database schema via code generation, aka "Database reverse engineering". Following this scenario, you should not touch the generated classes, because they can be re-generated at any moment, and all changes made in the source code will be lost. Also, this way doesn't save you from the migration scripts generation, because they will be required to upgrade the existing installations to the newest version.

Source code first

This is an opposite approach having the single source of truth in the data model classes. Hence, the database gets modified in accordance with changes in the data model. To handle database update, the difference between the old state of the database and up-to-date state of the data model classes should be represented in migration scripts of any format (Flyway SQL migrations or Liquibase changelogs).

JPA Buddy provides convenient tools that help developers to proceed with each of the described scenarios. This guide shows how JPA Buddy can save a lot of time for differential update scripts generation.

Database Connection

The first thing you need to do to use the database versioning features is to create a DB connection. The correct way to do it and possible issues are described in the separate documentation. Check it out to learn more.

Library Support

JPA Buddy supports two most used solutions that are often used in Java applications along with JPA: Flyway and Liquibase. However, there is an option to obtain DDL scripts for your JPA entities even if none of these are used in the project.

If you would like to see a short overview and practical examples of using Flyway or Liquibase you can use the following links:

• https://www.baeldung.com/database-migrations-with-flyway
• https://www.baeldung.com/liquibase-refactor-schema-of-java-app

To be certain that JPA Buddy is ready to help, make sure that the project contains right maven dependencies:

<dependency> 
 <groupId>org.liquibase</groupId> 
 <artifactId>liquibase-core</artifactId> 
 <version>4.4.3</version> 
</dependency> 

Or:

<dependency> 
 <groupId>org.flywaydb</groupId> 
 <artifactId>flyway-core</artifactId> 
 <version>7.15.0</version> 
</dependency> 

JPA Buddy scans the project dependencies and enables the corresponding features.

General Differential Scripts Generation Flow

The general path of the diff scripts generation for both of frameworks is nearly the same. However, there are some differences which are also highlighted in this guide. To avoid repetition, we will call both Liquibase changelogs and Flyway versioned migrations "migration scripts".

To generate a differential migration script with JPA Buddy, right-click on the desired folder and select New -> Liquibase -> Diff Changelog or New -> Flyway -> Diff Versioned Migration. Alternatively, click the Plus button on top of the JPA Structure tool window and select the corresponding item there.

In the opened dialog select source (the desired state of the data model) and target (the old state of the data model).

Resulting migration script(s) = Current State (Source) – Previous State (Target).

In other words, JPA Buddy will generate the resulting migration script for upgrading the target database to the state of the source.

As a source you can choose between the following options:

• DB — should be used in case you have an up-to-date database and would like to generate migration scripts for updating another DB to the same state.
• Model — use it to generate migration scripts representing the difference between the current state of the entity relationship model (JPA entities) and old (target) state.

The target can be set to:

• DB — target DB with older version of the schema.
• Snapshot — use this option in case you have the desired state stored in a data model snapshot. It can be generated by JPA Buddy as well.

Click OK to proceed further. JPA Buddy will analyze the difference between Source and Target and show the Preview dialog to enable fine tuning for the resulting migration script. Click Save to add the new script to the project or to append an existing one.

Differential Migration Scripts Generation Options

Using a Database

Comparing a database with another database/snapshot makes sense if you have a source database already synchronized with your data model. There are two popular approaches for keeping a database in accordance with JPA entities:

• using schema auto-generators (Hibernate and EclipseLink provide their own implementations).
  Note, that even Hibernate documentation warns against using this way beyond prototyping or testing reasons.
• applying changes in JPA entities over the database schema manually.
  This approach may appear to be too laborious, especially for early development stages when the data model is being changed frequently.

Using a Data Model

Following JPA principles, an application represents the data model (entities, associations, indexes, etc.) via the declared JPA entities. In other words, it already contains sufficient information about the database schema. So, your source code is the only point of truth, which represents the up-to-date (source) schema in the first place. This is why comparing your data model with a database/snapshot is the preferable option for generating differential changelogs.

JPA Buddy scans all JPA objects, compares them with a target database or a snapshot and generates a differential migration script.

Using a data model as a source of the current schema state triggers selection of a persistence unit. Following the documentation:

A persistence unit defines a set of all entity classes that are managed by EntityManager instances in an application. This set of entity classes represents the data contained within a single data store.

Effectively this means that if your application uses multiple data stores, you will need to generate migration scripts separately for each of them, specifying corresponding persistence units.

To configure a new persistence unit, click on the plus button in the JPA Structure tab and choose "Persistence Unit". In the opened window, you can define the persistence unit name, the default DB connection and select required entities. For entity selection, there are two possibilities that you can combine:

• You can scan the needed package, and all entities located in it will be added automatically
• You can manually select entities from the project

Using a Data Model Snapshot

JPA Buddy allows using a data model snapshot as the target of the comparison. Sometimes, it is impossible or hard to obtain a database for a certain state of the model, for example, when merging changes into some older version of the application. It may be simply impossible to keep a database dump for each release. JPA Buddy lets you checkout the required version of the application and generate a JSON snapshot based on the JPA entities, so no database will be required for differential migration scripts generation.

To generate a snapshot, open JPA Buddy panel and click the Plus button. Then, select the Data model snapshot item.

This allows you to capture the state of the data model at some older point, so that you can create a diff migration script describing all the modifications that happened from that point until now.

For example, you have been working in a feature branch and modified the model. Before merging, it is necessary to create a diff changelog describing the changes in this branch only.

Depending on the setup, there might be no DB that is always in sync with the main branch. Things get even more complicated when you need to merge not into the main branch, but some other state of the application (for example, into a release branch). It may be simply impossible to keep a database dump for each release. JPA Buddy offers a simpler solution:

1. Checkout the target branch (for example, main or release)
2. Create a snapshot of the model in that branch
3. Checkout the feature branch
4. Generate a diff migration script by comparing the model and the snapshot you created in step 2.

In four simple steps you get a migration script that describes the changes between the current branch and the target branch.

Init Schema

For both Liquibase and Flyway, JPA Buddy provides action to generate initialization script for you schema. Once you select this action from JPA Structure tab, the corresponding window will appear:

To generate the DDL script based on the data model you will need to specify the corresponding persistence unit, scope and one of the supported DB types. In case you compare two databases, you will need to choose one of the existing connections for both of them.

Preview Window

Click OK to proceed to the preview window of the migration script. The preview window for Liquibase looks like this (Flyway preview window is slightly different):

Some types of changes have custom fields in the preview window. For example, "add not null constraint" change allows you to replace all existing NULL values in the DB with a specified value:

Each change type is color-coded according to its danger level: green for SAFE, yellow for CAUTION and red for DANGER. SAFE operations are the ones that cannot cause loss of data in any way, for example, adding a column does not affect the existing data. Operations marked with CAUTION are generally safe but require your attention: for instance, adding a NOT NULL constraint can fail if there are null values in the column. DANGER operations can cause loss of data, for example, dropping a column or modifying data type.

The danger levels can be customized in the plugin preferences in JPA Buddy -> Database Versioning -> Diff Changes:

You can also configure whether each change type is placed in the primary/secondary location or ignored completely. The ignored changes will be excluded from all newly generated migration scripts by default, instead they will be displayed in the "Ignored" section during preview so that they can be added back manually. For Liquibase, you can also set the context and labels that should be used for each change type.

Merging statements

Basically, renaming schema elements, such as table name, column name, etc., leads to the two statements:

• Drop an existing value
• Add the new one

But JPA Buddy can replace such statements with the single rename or modify statement. For example, you will see two statements in the preview window after renaming column/table/sequence name or changing column type. But by choosing any of the related statements, you can merge them:

After the merge, drop statements may be irrelevant. You can choose changes that should be removed from the migration scripts. For example, after renaming an id column (rather than dropping an old value and adding a new one), there is no need to add a new primary key for it:

Liquibase Support

Changelog Preview Window

If you want to save the changelog as a regular file, then the following configuration options will be available:

• "Directory" and "File name" fields are responsible for configuring the location of the generated changelog. If a changelog with the specified name already exists, you will be prompted with a warning, after which the changes will be appended to that changelog.
• "Include to", "include folder" and "include context" allows you to configure whether the changelog should be included in some other changelog. If checked, the "include folder" box will generate the include statement for the whole folder, not only the current changelog.
• From the "File type" drop-down list, you can choose one of the four supported by Liquibase file type (YAML, JSON, SQL, XML), in which JPA Buddy will generate the changelog.

If you want to save the changelog as a scratch file, then you can configure only its name and type.

On the left of the window, there is a preview of the actual changesets to be generated. You can see what each change is going to look like by clicking on them. To combine several changes into one changeset or to ignore them, simply drag them around. The top left corner of the preview window contains various actions to modify the resulting changelog:

The following actions are provided:

• Add Changelog — create a secondary changelog
• Add Change Set — create a new changeset in the selected changelog
• Remove from Changelog with options:
  • Remove from Changelog — simply remove the changes from the current changelog
  • Remove and Ignore — remove the changes and add them to "Ignored", so they are excluded from future changesets too
  • Restore from Ignored — move the changes from "Ignored" to the changelog
• Set Context (for changesets)
• Set Labels (for changesets)
• Show Other Actions — select all changes based on the danger level, expand/collapse all changes

Primary and Secondary Changelogs

JPA Buddy lets you put the changes into two types of changelogs: Primary and Secondary. One use case for this is separating safe changes that can be run automatically and changes that require your attention and need to be run manually.

The changes can be separated automatically by their type (in Settings -> Diff Change Types). Alternatively, you can create a secondary changelog manually in the Preview window using the "Add Changelog" action. Then you can simply drag the desired changesets into the new changelog.

By default, Primary and Secondary changelogs are generated in separate directories, which can be customized in the plugin settings. Read more in the Settings -> Database Versioning -> Liquibase section.

Creating and Modifying Changelogs

JPA Buddy also offers tools for viewing, creating and modifying changelogs by hand. It adds additional UI elements to IntelliJ IDEA.

JPA Structure displays everything that JPA Buddy knows about the project. For Liquibase changelogs, it shows their hierarchy and contents:

To create a new changelog, right-click the desired folder in the project tree and select New -> Liquibase -> Empty Changelog. Or click the "plus" button in the top-left corner of the JPA Structure tab:

JPA Designer tab lets you generate and modify code. This tab is context-dependent, and displays options available for the opened file type. This tab splits into two parts: JPA Palette (1) and JPA Inspector (2). JPA Palette is used for code generation, and JPA Inspector for code modification.

Editor Toolbar provides fast access to relevant actions. It includes:

1. An action to select the opened file in JPA Structure
2. All actions from JPA Palette
3. Init Schema Changelog action
4. Diff Changelog action

JPA Buddy understands your data model and prefills the changesets as much as possible. And with the help of JPA Inspector you can explore the attributes of each changelog element:

JPA Buddy also makes writing code by hand easier by providing code completion based on the JPA data model:

Running Liquibase changelogs/previewing SQL without Gradle/Maven plugins

The JPA Structure tab also offers a way to run Liquibase updates and preview SQL. To run an update, click the "Liquibase Update" button:

The Liquibase Update window will open with configuration options:

• the path to the changelog file
• which DB connection to use
• Liquibase context and labels
• the log level of the operation

Clicking "Update" runs the Liquibase update command with the configured options. Clicking "Show SQL" displays a preview window:

Liquibase Settings

Base Settings

JPA Buddy allows you to specify:

• Liquibase version that you want to use;
• Changeset author name;
• The default file type. The following 4 file types are supported:
  • XML
  • SQL
  • YAML
  • JSON

Changelog Templates

Whenever an empty or differential Liquibase changelog is created, JPA Buddy generates the file name based on the templates. You can configure primary/secondary directory and name of the changelogs.

The following variables and macros are available for the precise configuration:

• #date([format]) – the current system date in the specified SimpleDateFormat. For example, #date(\"yyyy-MM-dd\") returns the date formatted as 2020-12-31.
• #increment([start], [step], [decimalFormat]) — a number that is used to keep the name unique. start value is used for the first file and is incremented by step for each next file. decimalFormat parameter specifies the DecimalFormat of the number. For example, #increment(1.0, 0.1, \"#.0\") returns the value formatted as 1.1, 1.2, 1.3, etc.
• semVer — semantic version of the project (aka SemVer), is a widely adopted version scheme that uses a sequence of three digits (Major.Minor.Patch), an optional pre-release tag and optional build meta tag. The object contains the following methods (the full version in the examples is 1.2.3-SNAPSHOT+meta):
  • ${semVer.getRawVersion()}: 1.2.3-SNAPSHOT
  • ${semVer.getMajor()}: 1
  • ${semVer.getMinor()}: 2
  • ${semVer.getPatch()}: 3
  • ${semVer.getPreRelease()}: SNAPSHOT
  • ${semVer.getMeta()}: meta

DB Types

Sometimes software must provide support for a few DBMS types. In this case, Liquibase is the best choice, as it offers a cross-DB solution to declare DDL modifications. JPA Buddy supports this solution as well. When generating cross-DB changelogs, it uses Liquibase properties to specify correct data types for each DBMS:

<property name="string.type" value="varchar" dbms="postgresql"/>
<property name="string.type" value="nvarchar" dbms="mssql"/>
<changeSet id="1622118750064-2" author="jpa-buddy">
  <createTable tableName="owners">
     <column autoIncrement="true" name="id" type="INT">
        <constraints nullable="false" primaryKey="true" primaryKeyName="PK_OWNERS"/>
     </column>
     <column name="first_name" type="${string.type}(255)"/>
     <column name="last_name" type="${string.type}(255)"/>
     <column name="address" type="${string.type}(255)"/>
     <column name="city" type="${string.type}(255)"/>
  </createTable>
</changeSet>

Therefore, there is no need to create separate changelogs for different DBMSes.

Flyway Support

Migration Preview Window

JPA Buddy allows you to select the place where to store the generated script: you can choose a file, scratch file in the IDE or clipboard.

"Directory" and "File name" fields are responsible for configuring the location of the generated migration. If a migration with the specified name already exists, you will be prompted with a warning, after which the changes will be appended to that migration.

On the left of the window, there is a preview of the actual changes to be generated. You can see what each change is going to look like by clicking on them.

Above the list of changes, there is a buttons panel with the following actions:

• Add Versioned Migration— create a secondary versioned migration.
• Remove from Versioned Migration with options:
  • Remove from Versioned Migration — to remove the changes from the current migration.
  • Remove and Ignore — to remove the changes and add them to the "Ignored" section, so they are excluded from future migrations too.
• Restore from Ignored — move the changes from "Ignored" to the migration.
• Move to Another Versioned Migration — by default, a single migration script is created on each diff generation with all the changes. This action lets you move a change to another migration file.
• Show Other Actions — This button will help you to conveniently interact with a large number of changes in the migration files:
  • "Select all ..."
  • "Expand/collapse all"

To combine several changes into one migration file or to ignore them, drag them around.

Java Migration

Java-based migrations are a great fit for all changes that can not easily be expressed using SQL. These migrations represent java classes that implement the JavaMigration interface or inherit from the BaseJavaMigration class. JPA Buddy follows the second option and generates the class name according to Flyway's default naming convention. This enables Flyway to automatically extract the version and the description from the class name.

To generate java migration, press the plus button in the JPA Structure tab and choose the corresponding item.

In the opened window, you can set class name, source root and package name:

After that, the following java class will be generated:

public class V4__ extends BaseJavaMigration {

   @Override
   public void migrate(Context context) {
      new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true))
         .execute("");
   }
}

Flyway Callbacks

While migrations are sufficient for most needs, certain situations require you to execute the same action over and over again. With the help of JPA Buddy, you can generate all events that Flyway supports. To generate SQL or java callbacks, press the plus button in the JPA Structure tab and choose the corresponding item.

SQL Callbacks

JPA Buddy provides Flyway SQL Callback window, with the following fields:

• "Source root" and "Directory" fields are responsible for the location of the generated file:

  

• "Callback event" field allows you to choose one of the events that Flyway supports:

• Optionally the callbacks may also include a description. The value in the "Description" filed will be appended along with the separator to the callback name.

Java Callbacks

If SQL Callbacks aren't flexible enough for you, flyway supports Java Callbacks. JPA Buddy provides Flyway Java Callback window, with the following fields:

• "Class" and "Class name" these fields are responsible for configuring corresponding values for generated java-class.

• "Callback event" field allows you to choose several events that Flyway supports:

  

• "Can handle in transaction" checkbox defines whether true or false will return the canHandleInTransaction overridden method from the Callback interface.

• "Source root" and "Directory" fields are responsible for the location of the generated file.

  public class TestCallback implements Callback { 

  @Override 
    public boolean supports(Event event, Context context) { 
        return event.equals(Event.*BEFORE_EACH_MIGRATE_STATEMENT*) || 
      event.equals(Event.*AFTER_EACH_MIGRATE_ERROR*) || 
      event.equals(Event.*AFTER_EACH_UNDO_STATEMENT*); 
    } 

    @Override 
    public boolean canHandleInTransaction(Event event, Context context) { 
        return true; 
    } 
   
    @Override 
    public void handle(Event event, Context context) { 
        *//**TODO handle logic...* 
    } 
   
    public String getCallbackName() { 
        return "Test"; 
    } 

}

Flyway Settings

Whenever an empty or differential Flyway migration is created, JPA Buddy generates the file name based on the flyway naming pattern. In plugin settings you can configure following values for name generation:

• Migration prefix. The default value is V.
• Version pattern. After the hyphen, an example of the generated sequence is presented.
• Migration separator. The default value is "_".
• Migration description.

The following variables and macros are available in the templates:

• #date([format]) – the current system date in the specified SimpleDateFormat. For example, #date(\"yyyy-MM-dd\") returns the date formatted as 2020-12-31.
• #increment([start], [step], [decimalFormat]) — a number that is used to keep the name unique. start value is used for the first file and is incremented by step for each next file. decimalFormat parameter specifies the DecimalFormat of the number. For example, #increment(1.0, 0.1, \"#.0\") returns the value formatted as 1.1, 1.2, 1.3, etc.
• semVer — semantic version of the project (aka SemVer), is a widely adopted version scheme that uses a sequence of three digits (Major.Minor.Patch), an optional pre-release tag and optional build meta tag. The object contains the following methods (the full version in the examples is 1.2.3-SNAPSHOT+meta):
  • ${semVer.getRawVersion()}: 1.2.3-SNAPSHOT
  • ${semVer.getMajor()}: 1
  • ${semVer.getMinor()}: 2
  • ${semVer.getPatch()}: 3
  • ${semVer.getPreRelease()}: SNAPSHOT
  • ${semVer.getMeta()}: meta

DDL By Entities

Generate DDL by Entities action allows developers to convert entities into DDL statements in a couple of clicks. It can generate:

• Initialization scripts to create a database schema from scratch;
• Differential DDL to update the already existing database to the valid state in accordance with JPA entities.

Also, this feature is extremely useful if we want to avoid using the automatic scripts generation enabled by hbm2ddl or ddl-auto properties. By using the JPA Buddy action, you can fully control DDL before execution, setup proper Java -> DB types mapping, map fields with attribute converters and Hibernate types, generate drop statements, and many more.

Resolve SchemaManagementException Automatically

If you stumble upon SchemaManagementException on the application startup, it means you have the ddl-auto property set to validate and Hibernate couldn't properly map JPA entities to your database tables. JPA Buddy allows you to generate DDL to fill up the difference between JPA entities and the database right from the stack trace!

Show DDL Action

JPA Buddy provides the action to generate DDL statements for only one specific entity. To see the DDL, hover the cursor over the class name and call the action from the IntelliJ IDEA Context Actions menu or JPA Inspector. Also, you can call this action from the Project panel and JPA Structure tab: just right-click on the target entity.

Next, choose which for which database you need a script and click OK:

SQL Visual Designer

In some cases, it's useful to have SQL scripts for the JPA data model, especially when you need to quickly set up a fresh database. JPA Buddy can generate a wide range of SQL statements via JPA Palette or Editor Toolbar. For each statement, there is a corresponding window that allows you to configure the statement:

Hibernate 6 Support

Before Hibernate 6, the Hibernate Types library or @Type/@Converter annotations were commonly used to map non-standard SQL types to Java types. With Hibernate 6, new annotations for mapping are available, allowing for the elimination of numerous Hibernate types and JPA converters. Currently, JPA Buddy supports these new mapping annotations:

• @JdbcType, @JdbcTypeCode & @JdbcTypeRegistration

• @Type, @JavaType & @JavaTypeRegistration

• @TimeZoneStorage & @TimeZoneColumn

Custom Type Mappings

There is no generic way to automatically map custom Java types to the SQL/Liquibase types. That's why you will need to define the target type manually for those attributes. If such attributes exist in your project, after Liquibase or Flyway script generation actions call, JPA Buddy will show you the following window:

You can change the saved mapping configuration at any time from Tools -> JPA Buddy -> Database Versioning -> Type Mappings.

Also, it may be helpful when the application works with databases from different vendors. In this case, your schema might have slightly different data types for each of them.

Let's say the application needs to support both PostgreSQL and MS SQL. And you want to store Unicode characters in your strings. PostgreSQL supports Unicode chars in VARCHAR, but MS SQL has a separate NVARCHAR data type for it.

JPA Buddy lets you specify type mappings for each DBMS. It is also possible to set mappings for JPA Converters and Hibernate Types:

Convertors

In order to simplify type mapping, JPA Buddy introduces DB-agnostic SQL types, that are transformed into the DB-specific type. For example, "varchar" is transformed into "varchar2" for Oracle DB and left as "varchar" for PostgreSQL.

Each DB-agnostic type has a set of aliases (for example, "java.sql.Types.VARCHAR" or "character varying"), which in most cases are interchangeable. Unknown types are used as is without any transformations. Check out the full list of aliases in the table:

Hibernate Envers Support

Hibernate Envers provides an easy auditing solution for entity classes. JPA Buddy, in its turn, allows you to define the prefix and postfix for audit tables. If these values are configured in the .properties file via org.hibernate.envers.audit_table_prefix and org.hibernate.envers.audit_table_suffix, Buddy will automatically apply them. These values will be considered while the script generation.

For example, if you have the following entity:

@Audited
@Entity(name = "Customer")
public static class Customer {

	@Id
	private Long id;

	private String firstName;

	private String lastName;

	@Temporal(TemporalType.TIMESTAMP)
	@Column(name = "created_on")
	@CreationTimestamp
	private Date createdOn;

	//Getters and setters are omitted for brevity

}

In the database, it will be mapped to the two, and not one table:

create table Customer (
    id bigint not null,
    created_on timestamp,
    firstName varchar(255),
    lastName varchar(255),
    primary key (id)
)

create table Customer_AUD (
    id bigint not null,
    REV integer not null,
    REVTYPE tinyint,
    created_on timestamp,
    firstName varchar(255),
    lastName varchar(255),
    primary key (id, REV)
)

When generating migration scripts, JPA Buddy will understand that the Customer_AUD table doesn't have to have a corresponding JPA entity, and will not generate a drop statement for it.

Naming Strategy and Max Identifier Settings

Since JPA Buddy supports six databases at once, this is important to have the ability to configure naming strategies and max identifier length.

By default, Spring Boot configures the physical naming strategy with SpringPhysicalNamingStrategy. This implementation generates all table names in lower case separated by underscores. For example, a TelephoneNumber entity is mapped to the telephone_number table. Even if you annotate the entity with @Table(name = "TelephoneNumber"). The same names must be used in the migration scripts, so JPA Buddy also applies a physical naming strategy to all names during script generation.

The following strategies are supported:

• SpringPhysicalNamingStrategy – the default option
• PhysicalNamingStrategyStandardlmpl
• CamelCaseToUnderscoresNamingStrategy (only for projects with Hibernate 6 and later versions)

To learn more about naming strategies, you can check out our article.

RDBMSs have their own limitations. For example, table names for OracleDatabase earlier than 12.1 version are limited to 30 bytes. To avoid problems with versioning scripts, you can limit table names:

Also, you can define:

• whether to create an index for the association foreign key constraint or not;
• primary key constraint name.

Sharing Settings via Version Control

JPA Buddy settings define conventions that are supposed to be shared among the team members: file naming rules, how to mark or separate change types, which data types to use for DBMSs etc. To make sharing easier, all the plugin settings are stored in the .jpb folder in the root of the project. This folder is supposed to be pushed to the version control, which automatically keeps the settings in sync across the development team.