Aurora DSQL Dialect for Hibernate

Introduction

The Aurora DSQL dialect for Hibernate provides integration between Hibernate ORM and Aurora DSQL. This dialect enables Java applications to leverage Hibernate's powerful object-relational mapping capabilities while taking advantage of Aurora DSQL's distributed architecture and high availability.

Sample Application

There is an included sample application in examples/pet-clinic-app that shows how to use Aurora DSQL with Hibernate. To run the included example please refer to the sample README.

Prerequisites

• Java 17 or higher
• Hibernate version 6.2 or higher
• A connection to an Amazon Aurora DSQL database
• PostgreSQL JDBC driver version 42.x or higher

Setup

A dialect for Aurora DSQL is used in largely the same way as other dialects for other databases. It is added as a dependency to your Maven or Gradle application:

``` // Maven software.amazon.dsql aurora-dsql-hibernate-dialect 1.0.0 pom

// Gradle implementation("software.amazon.dsql:aurora-dsql-hibernate-dialect:1.0.0") ```

With the aurora-dsql-hibernate-dialect JAR included in your Java application, the dialect can then be configured in a few ways: - In a Hibernate.properties file: hibernate.dialect=software.amazon.dsql.hibernate.dialect.AuroraDSQLDialect - In persistence.xml: <property name="hibernate.dialect" value="software.amazon.dsql.hibernate.dialect.AuroraDSQLDialect"/> - In Spring application properties: spring.jpa.properties.hibernate.dialect=software.amazon.dsql.hibernate.dialect.AuroraDSQLDialect - Programmatically using StandardServiceRegistryBuilder, the configuration API, or in a SessionFactory

Hibernate will not automatically detect the DSQL dialect based on metadata, it must be explicitly specified or else the PostgreSQLDialect will be used instead. With the dependency in place and the property set in Hibernate, the AuroraDSQLDialect will then automatically be used in DB interactions. Depending on your logging configuration, this may be verified in logs as connections are created.

See Hibernate documentation for more information on configuring your Hibernate application, including setting the dialect.

Database Connection

Configure your database connection for DSQL as follows:

properties hibernate.connection.url=jdbc:postgresql://<cluster_endpoint>/postgres?sslMode=verify-full&sslNegotiation=direct hibernate.connection.username=<username> hibernate.connection.driver_class=org.postgresql.Driver

Best practices

Primary key generation

Hibernate supports various ways to generate primary keys for tables, but Hibernate applications using DSQL should use UUID as keys, generated by the database. Auto-incrementing integer keys using sequences or serial are not supported in DSQL. UUID key generation can be done in your entity definition as follows:

java @Id @GeneratedValue @Column(name = "id", updatable = false, nullable = false, columnDefinition = "UUID DEFAULT gen_random_uuid()") private UUID id;

Auto-DDL

Usage of an automatically generated schema with Hibernate is not recommended with DSQL. While it may be useful for experimental development or testing environments, it should not be used in production. An automatically generated schema can perform poorly, and can be destructive in unexpected ways.

Concurrency control and locking

Aurora DSQL uses optimistic concurrency control (OCC), where conflicts are presumed to be rare. Conflicts are only handled when they occur, by allowing the first transaction to commit successfully, while any later transaction commit will result in an error. This has largely the same effect as Hibernate's built in version-based OPTIMISTIC lock mode. Users should therefore not use Hibernate's OPTIMISTIC lock mode, as DSQL will always trigger an OCC error prior to committing if there is a conflict, rendering the version check unnecessary.

There are only two lock modes that should be used: NONE (which will still use standard DSQL OCC), and PESSIMISTIC_WRITE. Although DSQL will always use optimistic locking, in Hibernate PESSIMISTIC_WRITE will add the SELECT ... FOR UPDATE modifier, which adds additional read checks on selected rows, preventing commits if rows read are modified by another transaction. There are multiple examples of how DSQL's concurrency control works available here in an AWS blog, including with SELECT ... FOR UPDATE. DSQL does not support any other locking modes, and so only these two Hibernate locking modes should be used.

Dialect Features and Limitations

Dialects provide syntax and supported features to allow the Hibernate ORM to correctly handle differences between databases. As Aurora DSQL is PostgreSQL-compatible and supports most PostgreSQL features, much of the dialect is similar to that of PostgreSQL. There are some key differences however that will help ensure a seamless developer experience with Hibernate and Aurora DSQL. The list below contains some of the key differences from the PostgreSQL dialect:

• Data types: The dialect provides correct float, double and numeric precision as well as varchar size limits.
• Foreign Keys: Aurora DSQL does not support foreign key constraints. The dialect disables these constraints, but be aware that referential integrity must be maintained at the application level.
• Index creation: Aurora DSQL does not support CREATE INDEX or CREATE UNIQUE INDEX commands. The dialect instead uses CREATE INDEX ASYNC and CREATE UNIQUE INDEX ASYNC commands. See the Asynchronous indexes in Aurora DSQL page for more information.
• Locking: Aurora DSQL uses optimistic concurrency control (OCC) with support for SELECT ... FOR UPDATE. The dialect supports these two locking methods. See the Concurrency control in Aurora DSQL page for more information.
• Sequences: Aurora DSQL does not support sequence objects, SERIAL and IDENTITY columns, thus they are not supported by this dialect. For this reason, these data types are unsuitable for primary key generation with this dialect. See the Primary keys in Aurora DSQL page for more information.
• Temporary tables: Aurora DSQL does not support temporary tables. The dialect will use standard tables instead. These tables will appear with HT_ or HTE_ prefixes, and will be managed automatically by Hibernate.
• Truncate command: Aurora DSQL does not support TRUNCATE command. The dialect uses a DELETE command instead.

Developer instructions

Instructions on how to build and test the dialect are available in the Developer Instructions.

Security

See CONTRIBUTING for more information.

License

Copyright Amazon.com, Inc. or its affiliates. All Rights Reserved.

SPDX-License-Identifier: Apache-2.0 OR LGPL-2.1