Skip to main content

Database Support

This article provides an overview of the database engines and persistence providers we support out-of-the-box and also run automated tests against.

Overview​

We consider four different scenario's for each combination of a database engine and persistence provider. These scenario's depend on how the identifier for a DataElement is generated.

  1. Using native database sequences.
  2. Using an identity column. This is our recommended default at this time.
  3. Deprecated No options are specified. Without specifying a specific strategy, you will let the persistence provider choose, which could be subject to change.
  4. Deprecated Using the global sequence option. This was introduced for OpenJPA to mimic the behavior of Hibernate 4.

Persistence providers​

At this time, we support three different persistence providers out of the box.

  • Hibernate 5: Our recommended default for Java EE based applications.
  • Hibernate 6: Our recommended default for Jakarta EE based applications.
  • OpenJPA: While supported, we do not recommend using OpenJPA, as it is not as well supported as Hibernate in general.
caution

While we do not officially support it, our applications are known to also operate using EclipseLink. If targeted in an application, it may require additional work to deploy using this technology.

info

We support Hibernate 6 to target Jakarta EE, but we are currently still working on integrating it into our automated testing process. As such, it is not yet listed in the databases section of this post. At the moment we assume that what works with Hibernate 5 will likely also work with Hibernate 6.

There was a breaking change in the legacy default identity generation change, but our expanders resolve this by forcing it to follow the same strategy as Hibernate 5. This ensures existing databases do not have to be migrated when updating to Jakarta EE.

Databases​

Out of the box we support a broad range of databases. In theory any JPA compatible SQL database engine should work, but the database engines listed below are the ones we actively test for. Our recommended default is Postgresql.

note
  • ❌ Unsupported combination.
  • 👍 Should work and is tested daily.
  • 🔥 We were not able to get this operational and should ideally be avoided.

Each combination may include additional notes.

Postgresql​

HIBERNATE5OPENJPA
1. SEQUENCE👍👍
2. IDENTITY👍👍1
3. LEGACY👍👍2
4. GLOBALSEQ❌👍

  • 1 OpenJPA tries to execute SELECT pg_get_serial_sequence($1, $2) on the database frequently, which fails. The database returns relation "elementTableName" does not exist. This does however not seem to impede the operation of the application, but does log many errors on the database server.

  • 2 By default OpenJPA uses sequence tables, rather than native sequences.

HSQLDB​

info

Historically we used to run HSQL databases in-memory. While we still support HSQLDB today, we no longer operate it in-memory. We provide our own HSQLDB docker image to run it in server mode, which is also used in our regression tests.

docker.normalizedsystems.org/nsx/hsqldb:1.3.0
HIBERNATE5OPENJPA
1. SEQUENCE👍👍1
2. IDENTITY👍👍
3. LEGACY👍👍
4. GLOBALSEQ❌👍
  • 1 OpenJPA dislikes sequences being defined with the same name in the JPA annotations, though it does not break on this.

Microsoft SQL Server​

HIBERNATE5OPENJPA
1. SEQUENCE👍🔥2
2. IDENTITY👍1🔥3
3. LEGACY❌❌
4. GLOBALSEQ❌🔥2

  • 1 The sql-expanders generate id fields using the type BIGINT, which was introduced in SQL Server 2008. The expanders currently target the older default dialect for SQL Server, which uses NUMERIC(19) as type.

  • 2 The dictionary for SQL Server in OpenJPA does not support sequences, even though sequence support was introduced in SQL Server 2012. It should be possible to define the command to select the next sequence value in persistence.xml, as a property of the dictionary. E.g.:

    <property name="openjpa.jdbc.DBDictionary" value="sqlserver(NextSequenceQuery=&quot;SELECT NEXT VALUE FOR NS_SEQUENCE&quot;)"/>

  • 3 Only works correctly if OpenJPA is allowed to populate the schemas with tables. OpenJPA throws exceptions (gen-nofktable in OpenJPA codebase) when the database has been populated with the sql-expanders, because the OpenJPA SchemaGenerator can't find a tables involved with foreign keys.

Additional notes​

  • SQL Server does not support DROP ... CASCADE and as such all constraints, tables and schemas have to be dropped individually. It is possible to use procedures in Transact-SQL to do this, but the sql-expanders do not generate any cleaning statements because of this. If not in use, it is however possible to drop an entire database in one query.

  • Applications must be expanded using the ApplicationInstanceOption db.schema.enableEscapeTableName to avoid issues with the account components, as User is a reserved word in SQL Server.

  • Because Microsoft dropped support for TLSv1, there might be some issues when trying to connect to the database and you have not added appropriate certificates to the certificate store of the application server/flyway jvm.

    caution

    For development, a quick workaround is to trust any server by default by adding ;trustServerCertificate=true to the JDBC urls of both Flyway and the TomEE data sources. Avoid using this in production environments!

Mysql​

HIBERNATE5OPENJPA
1. SEQUENCE🔥1🔥1
2. IDENTITY🔥1🔥1
3. LEGACY❌❌
4. GLOBALSEQ❌❌
  • 1 Mysql does not allow the direct use of a table in a SELECT-query if that query is part of the WHERE-clause of an UPDATE-query on that same table. This can only be achieved using multi-table update statements in Mysql. The workflow.DataAccess element has a custom query defined which violates this requirement. It is likely that individual components which do not violate this requirement can be deployed with an individual connection to a Mysql server. More information: https://dev.mysql.com/doc/refman/8.0/en/update.html

Mariadb​

HIBERNATE5OPENJPA
1. SEQUENCE👍1🔥2,3
2. IDENTITY👍🔥2,4
3. LEGACY❌❌
4. GLOBALSEQ❌❌

  • 1 The expanders currently target the older default dialect for MariaDB, which is equivalent to MariaDB 8.2. Sequences generated by Hibernate for this dialect use tables, whereas since MariaDB 10.3 there is support for native sequences. For now the sql-expanders also provider the table sequence structure.

  • 2 The database dialect for MariaDB in OpenJPA uses rather old Mysql typing for fields, such as BIT instead of BOOLEAN.

  • 3 Unlike Hibernate 5, OpenJPA does not provide an alternative implementation for the SEQUENCE strategy if it doesn't think the database supports sequences. The dictionary for MariaDB in OpenJPA does not support sequences, even though sequence support was introduced in MariaDB 10.3. It should be possible to define the command to select the next sequence value in persistence.xml, as a property of the dictionary. E.g.:

    <property name="openjpa.jdbc.DBDictionary" value="mariadb(NextSequenceQuery=&quot;NEXTVAL(NS_SEQUENCE)=&quot;"/>

  • 4 OpenJPA does not select the correct database, even with the catalog field present on the @Table annotations.

Oracle Database​

HIBERNATE5OPENJPA
1. SEQUENCE👍🔥1
2. IDENTITY👍🔥1
3. LEGACY❌❌
4. GLOBALSEQ❌❌
  • 1 OpenJPA fails to detect existing foreign keys correctly and attempts to recreate them, resulting in duplicate constraint errors.

Additional notes​

  • Applications must be expanded using the ApplicationInstanceOption db.schema.enableEscapeTableName to avoid issues with the account components, as User is a reserved word in Oracle databases.