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.

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 service providers are configured via connection properties. Use the datasources.*.data-source-properties prefix.

For example, to configure the oracle.jdbc.provider.password property as the name of a password provider and oracle.jdbc.provider.password.vaultId as a parameter recognized by the password provider, you will use a configuration such as:

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.

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.

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.

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

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