IntelliJ IDEA 2024.1 Help

Database Versioning

To ensure the data model in the source code matches the relational databases, it's crucial to keep them synchronized. There are two commonly used methods to tackle this challenge.

Database first

In this approach, the database takes precedence over data model classes (POJOs or JPA entities), which are generated from the database schema through code generation, also known as "Database reverse engineering." It's essential to avoid altering the generated classes since they can be regenerated at any time, and any changes made in the source code will be lost. However, this approach does not eliminate the need for migration script generation, as they are necessary for upgrading existing installations to the latest version.

Source code first

This is the opposite approach, here the data model classes serve as the single source of truth. Hence, the database gets modified by changes made in the data model. To update the database, migration scripts must represent the changes between the outdated database state and the current state of the data model classes in any format, such as Flyway SQL migrations or Liquibase changelogs.

IntelliJ IDEA provides convenient tools to help developers proceed with each of these two scenarios. This guide shows how IntelliJ IDEA can save a lot of time for differential update scripts generation.

Library Support

IntelliJ IDEA supports two popular solutions that are often used in Java applications along with JPA:

General Differential Scripts Generation Flow

This section basic principles of migration script generation. For specific details on how to generate migration scripts in Liquibase and Flyway, refer to Liquibase and Flyway.

To generate a differential migration script, right-click a database connection in the Database and select Create Liquibase Changelog or Create Flyway Versioned Migration. The actions are available if JPA and Liquibase or Flyway dependencies are added in your build files.

In the opened dialog, select a 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).

IntelliJ IDEA will generate the resulting migration script for upgrading the target database to the source's state.

You can choose between the following source 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 the old (target) state.

The target can be set to:

  • DB — target DB with an 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

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.

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

Preview Window

The preview window for Liquibase looks like this (The Flyway preview window is slightly different):

changelog-preview

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:

changelog-preview-update-null

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 a data type.

The danger levels can be customized in the IDE settings in JPA Buddy | Database Versioning | Diff Changes:

diff-changes-preferences

You can configure the location of each change type, either in the primary or secondary location, or ignore it altogether. By default, newly generated migration scripts will exclude ignored changes but display them in the Ignored section during preview so that they can be added back manually. For Liquibase, you can also set the context and labels to use for each change type.

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.

unnecessary-changes

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.

show-ddl

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

show-ddl-dbs
sql-preview

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

Hibernate Envers Support

Hibernate Envers is a module that facilitates entity auditing in a database. JPA Buddy can generate all the required tables for Hibernate Envers to function correctly. This includes audit tables for entities marked with the @Audited annotation and a revision table for the entity annotated with the @RevisionEntity annotation. Check out how it works in action:

JPA Buddy offers flexible settings for Hibernate Envers. For more details, refer to the corresponding section.

Settings

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:

undefined-mapping-detected

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:

type-mappings

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:

Name

Aliases

Liquibase class

bigint

java.sql.Types.BIGINT, java.math.BigInteger, java.lang.Long, integer8, bigserial, serial8, int8

liquibase.datatype.core.BigIntType

blob

longblob, longvarbinary, java.sql.Types.BLOB, java.sql.Types.LONGBLOB, java.sql.Types.LONGVARBINARY, java.sql.Types.VARBINARY, java.sql.Types.BINARY, varbinary, binary, image, tinyblob, mediumblob

liquibase.datatype.core.BlobType

boolean

java.sql.Types.BOOLEAN, java.lang.Boolean, bit, bool

liquibase.datatype.core.BooleanType

char

java.sql.Types.CHAR, bpchar

liquibase.datatype.core.CharType

clob

longvarchar, text, longtext, java.sql.Types.LONGVARCHAR, java.sql.Types.CLOB, nclob, longnvarchar, ntext, java.sql.Types.LONGNVARCHAR, java.sql.Types.NCLOB, tinytext, mediumtext

liquibase.datatype.core.ClobType

currency

money, smallmoney

liquibase.datatype.core.CurrencyType

function

liquibase.statement.DatabaseFunction

liquibase.datatype.core.DatabaseFunctionType

datetime

java.sql.Types.DATETIME, java.util.Date, smalldatetime, datetime2

liquibase.datatype.core.LiquibaseDataType

date

java.sql.Types.DATE, java.sql.Date

liquibase.datatype.core.DateType

decimal

java.sql.Types.DECIMAL, java.math.BigDecimal

liquibase.datatype.core.DecimalType

double

java.sql.Types.DOUBLE, java.lang.Double

liquibase.datatype.core.DoubleType

float

java.sql.Types.FLOAT, java.lang.Float, real, java.sql.Types.REAL

liquibase.datatype.core.FloatType

int

integer, java.sql.Types.INTEGER, java.lang.Integer, serial, int4, serial4

liquibase.datatype.core.IntType

mediumint

liquibase.datatype.core.MediumIntType

nchar

java.sql.Types.NCHAR, nchar2

liquibase.datatype.core.NCharType

number

numeric, java.sql.Types.NUMERIC

liquibase.datatype.core.NumberType

nvarchar

java.sql.Types.NVARCHAR, nvarchar2, national

liquibase.datatype.core.NVarcharType

smallint

java.sql.Types.SMALLINT, int2

liquibase.datatype.core.SmallIntType

timestamp

java.sql.Types.TIMESTAMP, java.sql.Types.TIMESTAMP_WITH_TIMEZONE, java.sql.Timestamp, timestamptz

liquibase.datatype.core.TimestampType

time

java.sql.Types.TIME, java.sql.Time, timetz

liquibase.datatype.core.TimeType

tinyint

java.sql.Types.TINYINT

liquibase.datatype.core.TinyIntType

uuid

uniqueidentifier, java.util.UUID

liquibase.datatype.core.UnknownType

varchar

java.sql.Types.VARCHAR, java.lang.String, varchar2, character varying

liquibase.datatype.core.VarcharType

xml

xmltype, java.sql.Types.SQLXML

liquibase.datatype.core.XMLType

Hibernate Envers

hibernate-envers-settings

Hibernate Envers offers various customization options. You can specify the desired configuration in the JPA Buddy settings (2), or let JPA Buddy read the existing settings from .properties files automatically (1).

Naming Strategy

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.

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.

custom-naming-strategy.png

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.

reload-custom-naming-strategy.png

Max Identifier

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:

max-db-identifier

Also, you can define:

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

  • primary key constraint name.

Last modified: 17 June 2024