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.

IntelliJ IDEA 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.

Using a Data Model Snapshot

IntelliJ IDEA 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 unfeasible to keep a database dump for each release. IntelliJ IDEA 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.

Generate Data Model Snapshot

1. Open the Persistence tool window.

2. Right-click an element and select New | Data Model Snapshot.

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. IntelliJ IDEA 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, IntelliJ IDEA 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, IntelliJ IDEA will use this strategy when generating migration scripts. Note that IntelliJ IDEA doesn't update the class changes automatically, so if you modify your strategy code, you need to either click Reload custom physical naming strategy class or restart IntelliJ IDEA.