This project includes modules to support SQL database access in Micronaut.

Available integrations include:

For this project, you can find a list of releases (with release notes) here:

https://github.com/micronaut-projects/micronaut-sql/releases

Java data sources can be configured for one of five currently provided implementations. Apache DBCP2, Hikari, Tomcat, Oracle Universal Connection Pool, and an unpooled DriverManager-backed datasource are supported by default.

To get started, simply add a dependency to one of the JDBC configurations that corresponds to the implementation you would like to use. Choose one of the following:

You also need to add a JDBC driver dependency to your classpath. For example to add the H2 In-Memory Database:

To configure Micronaut-managed JDBC transactions without Spring, see Transaction Management.

You can disable Micronaut Data Sources, for example in a test, by setting datasources.<datasource-name>.enabled to false.

Please note that such test should have @MicronautTest(transactional = false) because required transaction beans won’t be available because there is no datasource and connection available in the context.

All of the implementation specific parameters can be configured. Effort was made to allow basic configuration to be consistent across the implementations.

If you want Micronaut to avoid connection pooling entirely, add the micronaut-jdbc-unpooled module instead. It uses the same common datasource settings, creates a new physical JDBC connection for each getConnection() call, and closes that physical connection when the caller closes it.

Several configuration options will be calculated if they are not provided.

For example:

The above configuration will result in a single DataSource bean being registered with the named qualifier of default.

If for example, the H2 driver is on the classpath, it is equivalent to the following:

To use Oracle UCP, provide a configuration similar to the following:

The Oracle UCP is managed by the UniversalConnectionPoolManager. The manager can be disabled by setting ucp-manager.enabled to false. Additionally, you can enable the JMX-Based Management by setting the ucp-manager.jmx.enabled to true. When using current Oracle JDBC 23ai driver versions, the extra ojdbc*dms.jar driver artifacts are not required.

The micronaut-jdbc-ucp module uses Oracle’s ojdbc17 and ucp17 artifacts by default. Applications that need the JDK 11 driver line must exclude those default transitive dependencies and add direct dependencies on Oracle’s ojdbc11 and ucp11 artifacts instead. The ojdbc11 and ucp11 artifacts remain managed, so after excluding ojdbc17 and ucp17, you can rely on Micronaut’s managed versions for the replacement dependencies.

In case when exception Universal Connection Pool already exists in the Universal Connection Pool Manager is thrown, there is oracle.ucp.destroyOnReload configuration that can be set to true to avoid this error. In that case, when creating new connection pool, if there is the one with the same name the old one will be destroyed first.

There is also property oracle.ucp.createConnectionInBorrowThread that can control connection creation using background threads. The default configuration since UCP 23.x is that connections are created using background threads instead of user threads. Adding configuration oracle.ucp.createConnectionInBorrowThread=true will switch to the old behavior. More about it can be read here Borrowing Connections from UCP.

UCP lifecycle settings are configured like any other pool property. For example, to recycle connections before a firewall idle timeout and to enable connection harvesting:

Some UCP settings take objects rather than values, such as a connection creation consumer, a connection labeling callback or a custom access token supplier. Configure these with a bean that implements PoolDataSourceCustomizer. OCI IAM and Azure AD tokens need no code: use the token providers of the Oracle JDBC Driver Extensions. Every customizer is applied to every UCP data source, in Ordered order, after configuration and before the pool starts. Use the data source name to target one:

To use Database Resident Connection Pooling (DRCP), append :POOLED to the service name of the URL (or add (SERVER=POOLED) to a long connect descriptor) and optionally name the connection class. UCP uses the pool name as the connection class when none is set. Add (POOL_NAME=<name>) to the connect descriptor to use one of the named pools of a multi-pool DRCP.

Oracle Database 23ai and later also support implicit connection pooling, which releases the pooled server at statement or transaction boundaries without application calls. It works with DRCP or Proxy Resident Connection Pooling, so the connect descriptor needs (SERVER=POOLED) as well as (POOL_BOUNDARY=STATEMENT) or (POOL_BOUNDARY=TRANSACTION):

STATEMENT releases the server whenever the session is stateless, and TRANSACTION when a transaction ends or the session is stateless. When the connect descriptor sets POOL_BOUNDARY, it takes precedence over the pooling APIs: a release call on a session that was already released at a boundary does nothing, and a release call on a session that still holds state releases it.

Oracle can also reset the session state at the end of each request with the RESET_STATE service attribute. A database administrator sets it on the service, with DBMS_SERVICE or srvctl. With LEVEL1, Oracle cancels cursors, clears PL/SQL package state and truncates session temporary tables. With LOGIN, it also restores the login-time settings. The default is NONE.

Which values are available depends on the release:

RESET_STATE needs explicit request boundaries. UCP marks them whenever a connection is borrowed and returned. With other pools, Micronaut Data 5.3.0 and later marks them when datasources.<name>.request-boundaries is true. RESET_STATE does not apply to the implicit boundaries of POOL_BOUNDARY, nor to the implicit request boundaries that Transparent Application Continuity adds. With UCP, set validateConnectionOnBorrow: true when the service combines RESET_STATE=LEVEL1 with Application Continuity. Without DRCP or CMAN-TDM, LOGIN is an alternative; with either, it behaves like LEVEL1, so set validateConnectionOnBorrow: true there as well. LEVEL1 cannot be combined with UCP connection labeling.

To connect to an Oracle sharded database without passing sharding keys, enable the JDBC sharding data source. When it can derive the sharding key from a SQL statement, it routes the statement directly to that shard, except in the cases described after this example:

The sharding data source works with the JDBC Thin driver only. It runs PL/SQL on the shard catalog, and so does every statement while auto-commit is off, which includes the statements of Micronaut transactions. Setting oracle.jdbc.allowSingleShardTransactionSupport to true runs those transactions on a shard instead, so each transaction must stay within a single shard.

Fast Connection Failover (FCF) lets UCP react to Fast Application Notification (FAN) events from Oracle RAC or Data Guard: it removes connections to a failed instance and rebalances connections when an instance comes back. FCF is off by default. It needs Oracle’s ONS client libraries on the runtime classpath, whose versions are managed by Micronaut:

Without ons-configuration, UCP reads the ONS configuration from the database (auto-ONS), which is enough for most RAC and Data Guard setups. onsconfiguration, the key of the UCP setter setONSConfiguration, also works.

To pool XA connections for a transaction manager that enlists them in global transactions, enable xa and set the connection factory class to an XADataSource, such as oracle.jdbc.xa.client.OracleXADataSource:

The data source is then a UCP PoolXADataSource, and a javax.sql.XADataSource bean of the same name returns XA connections from that pool. The XADataSource bean is not a DataSource, so the pool remains the only DataSource bean of its name. UCP does not hand out plain connections from an XA pool, so the DataSource bean borrows an XA connection and returns its local connection, which takes part in local transactions such as @Transactional methods, and closing that connection returns the XA connection to the pool. The pool’s createConnectionBuilder(), which passes sharding keys and labels, returns connections the same way. These connections wrap UCP’s connection, so they aren’t an OracleConnection, a ValidConnection or a LabelableConnection themselves: use connection.unwrap(…​) rather than a cast to reach those interfaces. Startup fails if xa is enabled and the connection factory class is not an XADataSource.

For a list of other properties able to be configured, simply refer to the implementation that is being used. All setter methods are candidates for configuration.

To register more than one data source, simply configure them under different names.

When injecting DataSource beans, the one with the name "default" will be injected unless the injection is qualified with the configured name. If no configuration is named "default", none of the beans will be primary and thus all injections must be qualified. For example:

Spring is not required for transaction management with plain JDBC access or other integrations built on top of a JDBC DataSource. To enable Micronaut-managed transactions for those JDBC-backed integrations, add the following dependency:

This module configures the JDBC transaction manager and includes the Jakarta Transactions API.

If you are using Hibernate/JPA, do not add micronaut-data-tx-jdbc for transaction management. Use the Hibernate transaction module instead:

You can annotate transactional methods with either jakarta.transaction.Transactional or io.micronaut.transaction.annotation.Transactional. For read-only operations, you can also use io.micronaut.transaction.annotation.ReadOnly.

To use jakarta.transaction.Transactional, the Micronaut Data annotation processor, which maps it to Micronaut’s transactional advice, must be on the annotation processor path:

If you need Micronaut-specific options such as readOnly, transactionManager, or propagation, use io.micronaut.transaction.annotation.Transactional.

This transaction support works with plain JDBC access and with integrations built on top of a JDBC DataSource, such as Jdbi and JDBC-backed jOOQ.

When using direct JDBC access, obtain connections from the injected DataSource inside a transactional method, or from a method that is invoked within a transaction boundary, so Micronaut can associate the connection with the active transaction.

Spring transaction management remains optional and should only be added if your application specifically needs Spring transaction APIs.

Once you have configured a JDBC DataSource the JdbcIndicator is activated resulting in the /health endpoint and CurrentHealthStatus interface resolving the health of the JDBC connection.

See the section on the Health Endpoint for more information.

JDBC Service Provider Extensions

Oracle JDBC Driver Extensions are service providers that the Oracle JDBC driver loads from the classpath. There are two kinds:

Micronaut SQL manages the versions of the provider artifacts, so you can add them without a version:

The managed artifacts are ojdbc-provider-common, ojdbc-provider-oci, ojdbc-provider-azure, ojdbc-provider-aws, ojdbc-provider-gcp, ojdbc-provider-hashicorp, ojdbc-provider-jackson-oson and ojdbc-provider-observability. The providers depend on the Oracle wallet library, com.oracle.database.security:oraclepki, which is managed at the version of the driver.

To use SQLite, add the following dependency:

The module provides connection capabilities configuration for SQLite and adds the SQLite JDBC driver as a transitive dependency.

If you do not provide an explicit JDBC URL, Micronaut SQL configures automatically the following JDBC URL jdbc:sqlite:file:%s?mode=memory&cache=shared&foreign_keys=on&busy_timeout=5000, when you set datasources.*.db-type to sqlite.

For production, you will probably want to configure SQLite explicitly with a file-backed JDBC URL and something like this:

Useful SQLite documentation links:

You can disable Micronaut Hibernate JPA, for example in a test, by setting jpa.enabled to false.

To disable a single JPA configuration and keep the others, set jpa.<name>.enabled to false, for example jpa.default.enabled. No SessionFactory is created for a disabled configuration, so Hibernate does not connect to its database at startup.

You can use the jakarta.persistence.PersistenceContext annotation to inject an EntityManager (or Hibernate Session):

For Hibernate/JPA transaction management, use micronaut-data-tx-hibernate as described in Setting up a Hibernate/JPA EntityManager.

There are several different ways you can customize and configure how the SessionFactory is built. The easiest way is via configuration. The following configuration demonstrates an example:

The above example configures the packages to be scanned and sets properties to be passed to Hibernate. As you can see these are done on a per DataSource basis. Refer to the JpaConfiguration configuration class for the possible options.

If you need even further control over how the SessionFactory is built then you can register BeanCreatedEventListener beans that listen for the creation of the SessionFactoryBuilder, MetadataSources etc. and apply your custom configuration in the listener.

You may also optionally create beans of type Integrator and Interceptor and these will be picked up and injected automatically.

Since 1.2 of this library Entity scan configuration is more flexible and it is possible to do reflection free scanning that works on GraalVM substrate.

The default configuration will look for all classes compiled by Micronaut that include the @Entity annotation.

If you wish to limit the packages to include in a particular JPA entity manager you can do so with the entity-scan configuration option:

The above configuration limits the search to only classes in the foo.bar package. Note that if classes are not compiled by Micronaut they will not be found. There are two ways to resolve this, one is to generate introspection metadata for the external classes. For example you can place in this on your Application class:

If this option doesn’t work for you, you can instead enable full classpath scanning using the classpath property:

Note that this approach has the following disadvantages:

To run Hibernate on GraalVM native image you need to configure more than entity scanning alone. The Hibernate runtime normally depends on reflective model inspection and runtime proxy generation, both of which need extra setup in a native image.

For Hibernate applications on GraalVM, make sure all of the following are true:

It’s possible to use Hibernate Reactive by adding following dependency:

and the Micronaut Data Transaction Hibernate dependency:

To enable reactive session factory JPA configuration needs to have reactive property set to true. The reactive implementation doesn’t use traditional JDBC drivers, but instead it uses Vertx Drivers.

You can add one of:

And configure it using properties:

The other option is to include one of the existing Micronaut SQL support libraries:

And configure the client:

The integration will automatically integrate Vertx driver instance of io.vertx.sqlclient.Pool found in the bean context.

Hibernate uses a proxy object to implement lazy loading with a default implementation generating a proxy during the runtime.

This has a few disadvantages:

If you wish to use lazy entity associations and avoid runtime proxies you can enable compile-time proxies:

Compile-time proxies require every entity type that Hibernate may proxy to be annotated with @GenerateProxy:

For example:

The entity Owner needs to be annotated with @GenerateProxy to have a proxy generated at compile time.

For the full set of Hibernate native image requirements, including entity scanning and reflective access for embeddable identifier types, see the GraalVM native image section in this chapter.

Micronaut is built on Netty which is based on a non-blocking, event loop model. JDBC and Hibernate are blocking APIs and hence when they are used in a Micronaut application the work is shifted to a blocking I/O thread pool.

When using jakarta.transaction.Transactional (or io.micronaut.transaction.annotation.Transactional) the Hibernate Session will only be open for the duration of this method execution and then will automatically be closed. This ensures that the blocking operation is kept as short as possible.

There is no notion of OpenSessionInView (OSIV) in Micronaut and never will be, since it is sub-optimal and not recommended. You should optimize the queries that you write to return all the necessary data Micronaut will need to encode your objects into JSON either by using the appropriate join queries or using a data transfer object (DTO).

If you encounter a LazyInitializationException when returning a Hibernate entity from a method it is an indication that your query is suboptimal and you should perform a join.

Hibernate maps vector columns, such as the Oracle Database VECTOR type and PostgreSQL pgvector, with the hibernate-vector module. Micronaut SQL manages its version along with Hibernate:

With the module on the classpath, a vector attribute is mapped with @JdbcTypeCode(SqlTypes.VECTOR) and its dimensions with @Array(length = …​), and vector distance functions such as cosine_distance and euclidean_distance are available in HQL and criteria queries. See the Hibernate ORM documentation for the mapping, the supported databases and the functions.

Micronaut supports asynchronous access to PostgreSQL and MySQL using jasync-sql, allowing to handle many database connections with a single thread.

To configure the Jasync client you should first add jasync-sql module to your classpath:

You should then configure the PoolOptions of the database server you wish to communicate with:

Once you have the above configuration in place then you can inject the com.github.jasync.sql.db.Connection bean. The following is the simplest way to connect:

For more information on running queries on using the client please read the "Running queries" section in the documentation of jasync-sql.

To use Jasync query interceptors register beans of type com.github.jasync.sql.db.interceptor.QueryInterceptor.

When the jasync-sql module is activated a JasyncHealthIndicator is activated resulting in the /health endpoint and CurrentHealthStatus interface resolving the health of the connection.

The only configuration option supported is to enable or disable the indicator by the endpoints.health.jasync.enabled key.

See the section on the Health Endpoint for more information.

Micronaut supports automatically configuring jOOQ library for fluent, typesafe SQL query construction.

To configure jOOQ library you should first add jooq module to your classpath:

You should then either configure one or many DataSources or configure one or many R2DBC ConnectionFactory instances. For each registered DataSource or ConnectionFactory, Micronaut will configure the following jOOQ beans using JooqConfigurationFactory or R2dbcJooqConfigurationFactory:

For JDBC-backed jOOQ applications that use Micronaut transaction management, configure Transaction Management.

If Spring transaction management is in use, it will additionally create the following beans :

Micronaut also configures jOOQ for each named ConnectionFactory when R2DBC is on the classpath. Use the same logical data source name for both r2dbc.datasources. and jooq.r2dbc-datasources..

The sql-dialect setting is optional. When it is not configured, jOOQ falls back to DEFAULT.

Micronaut will attempt to detect database SQLDialect automatically.

If this does not work as desired, SQL dialect can be provided manually via configuration properties. The following example configures dialect for default datasource:

You can define additional beans which will be used when jOOQ Configuration is created. Only beans with the same name qualifier as the data source name will be used.

Micronaut will look for the following bean types:

If you don’t register bean of type ConverterProvider and provide following configuration, JsonConverterProvider will be used, which uses Micronaut configured JsonMapper for converting from and to types JSON and JSONB.

To use JOOQ in a native image it is necessary to declare the Record classes for reflection. The easiest way to do it is configure jOOQ to annotate the generated classes with the JPA annotations enabling the option jpaAnnotations. This way Micronaut will be able to detect them and automatically generate the reflection configuration that GraalVM needs.

For example, if you are using this gradle plugin you can add the following:

There is also built-in support for using SimpleFlatMapper with jOOQ in a native-image. No additional configuration is needed, just adding the SimpleFlatMapper dependency:

Micronaut supports automatically configuring Jdbi library for convenient, idiomatic access to relational data.

To configure the Jdbi library you should first add the jdbi module to your classpath:

If you need additional Jdbi modules such as jdbi3-sqlobject, Gradle consumers can declare them without an explicit version because micronaut-jdbi publishes the Jdbi BOM through Gradle module metadata:

For Maven, import micronaut-sql-bom in dependencyManagement before declaring extra Jdbi modules, because Maven does not consume transitive BOM imports from regular dependencies:

You should then configure one or many DataSources. For each registered DataSource, Micronaut will configure the following Jdbi beans using JdbiFactory:

For Jdbi applications that use Micronaut transaction management, configure Transaction Management.

If Spring transaction management is in use, it will additionally create the following beans :

You can define additional beans which will be used when the Jdbi object is created. Only beans with a Named qualifier name with the same name as the data source name will be used.

Micronaut will look for the following bean types:

Micronaut supports automatically configuring MyBatis for SQL mappers built on top of JDBC.

To configure MyBatis you should first add the mybatis module to your classpath:

MyBatis support builds on Micronaut JDBC. In addition to micronaut-mybatis, add one of the JDBC runtime modules and a JDBC driver dependency.

Once those dependencies are present, configure one or many DataSources. For example:

For each registered DataSource, Micronaut will configure the following MyBatis beans using MyBatisFactory:

If no MyBatis transaction factory is provided for a DataSource, Micronaut uses JdbcTransactionFactory.

When multiple datasources are configured, Micronaut qualifies each MyBatis bean with the datasource name. You can then inject the Configuration, SqlSessionFactory, or SqlSessionManager for a specific datasource with @Named.

Mapper interfaces are not registered automatically. You can register mappers and apply additional configuration through customizer beans, then obtain them from the corresponding SqlSessionManager.

For a complete application example, see the Micronaut Data access MyBatis guide.

Once a datasource is configured, you can inject the MyBatis runtime beans that Micronaut creates for it.

For a single datasource application, injecting MyBatisFactory output beans requires no qualifier.

If you configure multiple datasources, inject the matching bean with @Named annotation.

The SqlSessionManager is the typical entry point for mapper usage. Retrieve mapper implementations with getMapper(..) after registering the mapper interface through a customizer.

You can define additional beans which will be used when the MyBatis Configuration is created. Only beans of type MyBatisConfigurationCustomizer with an @Named qualifier matching the datasource name will be applied.

For example, you can register mapper interfaces and tune MyBatis settings for a specific datasource:

This hook is also the right place to configure other MyBatis features that belong on the Configuration, such as type aliases, type handlers, interceptors, or additional mapper registrations.

Micronaut supports reactive and non-blocking client to connect to MySQL using vertx-mysql-client, allowing to handle many database connections with a single thread.

To configure the MySQL Vertx client you should first add vertx-mysql-client module to your classpath:

You should then configure the URI or MySQLConnectOptions,PoolOptions of the MySQL server you wish to communicate with in application.yml:

Once you have the above configuration in place then you can inject the io.vertx.reactivex.mysqlclient.MySQLPool bean. The following is the simplest way to connect:

For more information on running queries on MySQL using the reactive client please read the "Running queries" section in the documentation of vertx-mysql-client.

When the vertx-mysql-client module is activated a MySQLClientPoolHealthIndicator is activated resulting in the /health endpoint and CurrentHealthStatus interface resolving the health of the MySQL connection.

The only configuration option supported is to enable or disable the indicator by the endpoints.health.vertx.mysql.client.enabled key.

See the section on the <Health Endpoint for more information.

Micronaut supports reactive and non-blocking client to connect to PostgreSQL using vertx-pg-client, allowing to handle many database connections with a single thread.

To configure the PostgreSQL Vertx client you should first add vertx-pg-client module to your classpath:

You should then configure the URI or PgConnectOptions,PoolOptions of the PostgreSQL server you wish to communicate with:

When you need to trust a PostgreSQL or CockroachDB CA certificate for SSL connections, configure PEM trust certificates with pem-trust-options:

Once you have the above configuration in place then you can inject the core io.vertx.sqlclient.Pool bean. If io.vertx:vertx-rx-java3 is on the classpath, Micronaut also exposes the RxJava3 io.vertx.rxjava3.sqlclient.Pool wrapper bean. The following is the simplest way to connect:

For more information on running queries on Postgres using the reactive client please read the "Running queries" section in the documentation of vertx-pg-client.

When the vertx-pg-client module is activated a PgClientPoolHealthIndicator is activated resulting in the /health endpoint and CurrentHealthStatus interface resolving the health of the Postgres connection.

The only configuration option supported is to enable or disable the indicator by the endpoints.health.vertx.pg.client.enabled key.

See the section on the <Health Endpoint for more information.

Micronaut supports the reactive and non-blocking Vert.x Oracle client, which runs on the asynchronous API of the Oracle JDBC driver.

To use it, add the vertx-oracle-client module to your classpath:

The module depends on the same Oracle JDBC driver (ojdbc17) as the other Micronaut SQL modules, instead of the older ojdbc11 release the Vert.x Oracle client declares.

Then configure the OracleConnectOptions and PoolOptions of the database:

You can also set uri instead of the host, port and service name, for example oracle:thin:@the-host:1521/FREEPDB1, a JDBC URL such as jdbc:oracle:thin:@the-host:1521/FREEPDB1, or a TNS alias such as oracle:thin:@mydb_high. The URI replaces the configured host, port, service name, SID and TNS alias, and the other configured options, such as user, password and tns-admin, still apply unless the URI sets them. Connection properties in the URI are added to the configured properties.

The Oracle client connects through the Oracle JDBC driver, so Vert.x network options such as connect timeouts and TLS stores don’t apply: configure those through the connection properties or the Oracle Net configuration instead. When there is no io.vertx.core.Vertx bean, the module creates a Vert.x instance for the pool and closes it with the application context.

The module then provides an io.vertx.sqlclient.Pool bean, which you can inject to run queries:

For more information on running queries with the reactive client, read the vertx-oracle-client documentation.

When the vertx-oracle-client module is configured, a OracleHealthIndicator is activated, and the /health endpoint and the CurrentHealthStatus interface report the health of the Oracle connection, with the database version as a detail.

The only configuration option is to enable or disable the indicator with the endpoints.health.vertx.oracle.client.enabled key.

See the section on the Health Endpoint for more information.

You can find the source code of this project in this repository:

https://github.com/micronaut-projects/micronaut-sql