Database Versioning

Using a Database

It makes sense to compare a database with another database/snapshot 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

An application uses JPA entities to represent the data model, including entities, associations, indexes, and other related elements, in accordance with JPA principles. 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. This is why it's preferable to compare your data model with a database/snapshot 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.

When a data model serves as the source for the current schema state, it causes the 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 the required entities. For entity selection, you have two possibilities:

• 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 difficult to obtain a database for a certain state of the model, for example, when merging changes into some earlier application version. It may be simply unfeasible to keep a database dump for each release. JPA Buddy lets you check out the required version of the application and generate a JSON snapshot based on the JPA entities, eliminating the need for a database when producing differential migration scripts.

To generate a snapshot, open the 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 changes into a state of the application other than the main branch, such as a release branch. Maintaining a database dump for each release may not be practical. JPA Buddy offers a simpler alternative:

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.

Merging statements

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

• Drop an existing value

• Add a new one

But JPA Buddy can replace such statements with a single rename or modify statement. For example, you will see two statements in the preview window after renaming a column/table/sequence or changing a 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 the changes that you want to remove 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.

Converters

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:

Custom Naming Strategy

Sometimes, the default behavior for naming strategy in a project changes. JPA Buddy can help you with that too! Once it detects a class in the project that implements org.hibernate.boot.model.naming.PhysicalNamingStrategy (or one of its successors), an additional "custom" section with the corresponding class will appear in the naming strategy dropdown menu.

Once you select this class from the dropdown menu, JPA Buddy will use this strategy when generating migration scripts. It's important to note that JPA Buddy doesn't update the class changes automatically, so if you modify your strategy code, you need to either click the refresh button or restart IntelliJ IDEA.