In Micronaut SQL 5.7.0, you could disable data sources if you set datasources.enabled to false. Since Micronaut SQL 5.8.0, it is no longer supported. If you have multiple data sources, you have to disable them all individually. For example if you had two data sources named a and b, you have to set datasources.a.enabled=false and datasources.b.enabled=false.

4 Configuring JDBC

Java data sources can be configured for one of four currently provided implementations. Apache DBCP2, Hikari, Tomcat, and Oracle Universal Connection Pool are supported by default.

Using the CLI

If you are creating your project using the Micronaut CLI, supply one of the jdbc-tomcat, jdbc-hikari, jdbc-dbcp, or jdbc-ucp features to preconfigure a simple JDBC connection in your project, along with a default H2 database driver:

$ mn create-app my-app --features jdbc-tomcat

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:

runtimeOnly("io.micronaut.sql:micronaut-jdbc-tomcat")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-tomcat</artifactId>
    <scope>runtime</scope>
</dependency>

runtimeOnly("io.micronaut.sql:micronaut-jdbc-hikari")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-hikari</artifactId>
    <scope>runtime</scope>
</dependency>

runtimeOnly("io.micronaut.sql:micronaut-jdbc-dbcp")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-dbcp</artifactId>
    <scope>runtime</scope>
</dependency>

runtimeOnly("io.micronaut.sql:micronaut-jdbc-ucp")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbc-ucp</artifactId>
    <scope>runtime</scope>
</dependency>

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

runtimeOnly("com.h2database:h2")

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

4.1 Disable Micronaut JDBC Data Sources

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.

Micronaut SQL 5.7.0 allowed disabling datasources via setting datasources.enabled to `false. That it is no longer supported. If you have multiple dataousrces you have to disable them all individually.

4.2 Configuring JDBC Connection Pools

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

Hikari: The URL is able to be configured through url in addition to jdbcUrl. The JNDI name can be configured through jndiName in addition to dataSourceJNDI.
Tomcat: The JNDI name can be configured through jndiName in addition to dataSourceJNDI.

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

URL

The classpath will be searched for an embedded database driver. If found, the URL will be set to the default value for that driver.

Driver Class

If the URL is configured, the driver class will be derived from the URL, otherwise the classpath will be searched for an embedded database driver. If found, the default class name for that driver will be used.

Username

If the configured database driver is embedded, the username will be set to "sa"

Password

If the configured database driver is embedded, the password will be set to an empty string.

For example:

datasources.default: {}

["datasources.default"]

datasources.default {
}

{
  "datasources.default" {
  }
}

{
  "datasources.default": {
  }
}

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:

datasources.default.url=jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
datasources.default.username=sa
datasources.default.password=
datasources.default.driverClassName=org.h2.Driver

datasources:
    default:
        url: jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
        username: sa
        password: ""
        driverClassName: org.h2.Driver

[datasources]
  [datasources.default]
    url="jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"
    username="sa"
    password=""
    driverClassName="org.h2.Driver"

datasources {
  'default' {
    url = "jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"
    username = "sa"
    password = ""
    driverClassName = "org.h2.Driver"
  }
}

{
  datasources {
    default {
      url = "jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE"
      username = "sa"
      password = ""
      driverClassName = "org.h2.Driver"
    }
  }
}

{
  "datasources": {
    "default": {
      "url": "jdbc:h2:mem:default;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE",
      "username": "sa",
      "password": "",
      "driverClassName": "org.h2.Driver"
    }
  }
}

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

datasources.default.url=null
datasources.default.connectionFactoryClassName=oracle.jdbc.pool.OracleDataSource
datasources.default.username=null
datasources.default.password=null
datasources.default.minPoolSize=1
datasources.default.maxPoolSize=10

datasources:
  default:
    url:
    connectionFactoryClassName: oracle.jdbc.pool.OracleDataSource
    username:
    password:
    minPoolSize: 1
    maxPoolSize: 10

[datasources]
  [datasources.default]
    connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
    minPoolSize=1
    maxPoolSize=10

datasources {
  'default' {
    url = null
    connectionFactoryClassName = "oracle.jdbc.pool.OracleDataSource"
    username = null
    password = null
    minPoolSize = 1
    maxPoolSize = 10
  }
}

{
  datasources {
    default {
      url = null
      connectionFactoryClassName = "oracle.jdbc.pool.OracleDataSource"
      username = null
      password = null
      minPoolSize = 1
      maxPoolSize = 10
    }
  }
}

{
  "datasources": {
    "default": {
      "url": null,
      "connectionFactoryClassName": "oracle.jdbc.pool.OracleDataSource",
      "username": null,
      "password": null,
      "minPoolSize": 1,
      "maxPoolSize": 10
    }
  }
}

The Oracle UCP is managed by the UniversalConnectionPoolManager. The manager can be disabled byt setting ucp-manager.enabled to false. Additionally you can enable the JMX-Based Management by setting the ucp-manager.jmx.enabled to true and providing the ojdbc8dms.jar and dms.jar dependencies.

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.

Tomcat

Hikari

Apache DBCP

Oracle UCP

4.3 Configuring Multiple Data Sources

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

datasources.default=...
datasources.warehouse=...

datasources:
    default:
        ...
    warehouse:
        ...

[datasources]
  default="..."
  warehouse="..."

datasources {
  'default' = "..."
  warehouse = "..."
}

{
  datasources {
    default = "..."
    warehouse = "..."
  }
}

{
  "datasources": {
    "default": "...",
    "warehouse": "..."
  }
}

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:

@Inject DataSource dataSource // "default" will be injected
@Inject @Named("warehouse") DataSource dataSource // "warehouse" will be injected

4.4 JDBC Health Checks

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.

5 Configuring Hibernate

Setting up a Hibernate/JPA EntityManager

Using the CLI

If you are creating your project using the Micronaut CLI, supply the hibernate-jpa feature to include a Hibernate JPA configuration in your project:

$ mn create-app my-app --features hibernate-jpa

Micronaut features built in support for configuring a Hibernate / JPA EntityManager that builds on the SQL DataSource support.

Once you have configured one or many DataSources to use Hibernate, you will need to add the hibernate-jpa dependency to your build configuration:

implementation("io.micronaut.sql:micronaut-hibernate-jpa")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-hibernate-jpa</artifactId>
</dependency>

and the Micronaut Data Transaction Hibernate dependency:

implementation("io.micronaut.data:micronaut-data-tx-hibernate")

<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-tx-hibernate</artifactId>
</dependency>

And that is it. For each registered SQL DataSource, Micronaut will configure the following beans using EntityManagerFactoryBean:

StandardServiceRegistry - The Hibernate StandardServiceRegistry
MetadataSources - The Hibernate MetadataSources
SessionFactoryBuilder - The Hibernate SessionFactoryBuilder
SessionFactory - The Hibernate SessionFactory bean which also implements the JPA EntityManagerFactory interface.

5.1 Disable Micronaut Hibernate JPA

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

5.2 Injecting an EntityManager or Hibernate Session

You can use the javax.persistence.PersistenceContext annotation to inject an EntityManager (or Hibernate Session). To do so you need to make sure the JPA annotations are on the annotationProcessor path in your build:

Adding the JPA dependency to annotationProcessor in Gradle

annotationProcessor "jakarta.persistence:jakarta.persistence-api:2.2"

Using @PersistenceContext

@PersistenceContext
EntityManager entityManager;

@PersistenceContext(name = "other")
EntityManager otherManager;

Micronaut will inject a compile time scoped proxy that retrieves the EntityManager associated with the current transaction when using @Transactional (see "Using Spring Transaction Management" below).

Note the examples above use field injection, since the @PersistenceContext annotation does not support declaration on a parameter of a constructor or method argument. Therefore if you wish to instead use constructor or method injection you must use the @CurrentSession instead:

Using @CurrentSession for constructor injection

public MyService(@CurrentSession EntityManager entityManager) {
     this.entityManager = entityManager;
}

5.3 Customizing Hibernate/JPA Configuration

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:

Configuring Hibernate Properties

datasources.default.name=mydb
jpa.default.entity-scan.packages[0]=foo.bar
jpa.default.entity-scan.packages[1]=foo.baz
jpa.default.properties.hibernate.hbm2ddl.auto=update
jpa.default.properties.hibernate.show_sql=true

datasources:
    default:
        name: 'mydb'
jpa:
    default:
        entity-scan:
            packages:
                - 'foo.bar'
                - 'foo.baz'
        properties:
            hibernate:
                hbm2ddl:
                    auto: update
                show_sql: true

[datasources]
  [datasources.default]
    name="mydb"
[jpa]
  [jpa.default]
    [jpa.default.entity-scan]
      packages=[
        "foo.bar",
        "foo.baz"
      ]
    [jpa.default.properties]
      [jpa.default.properties.hibernate]
        show_sql=true
        [jpa.default.properties.hibernate.hbm2ddl]
          auto="update"

datasources {
  'default' {
    name = "mydb"
  }
}
jpa {
  'default' {
    entityScan {
      packages = ["foo.bar", "foo.baz"]
    }
    properties {
      hibernate {
        hbm2ddl {
          auto = "update"
        }
        show_sql = true
      }
    }
  }
}

{
  datasources {
    default {
      name = "mydb"
    }
  }
  jpa {
    default {
      entity-scan {
        packages = ["foo.bar", "foo.baz"]
      }
      properties {
        hibernate {
          hbm2ddl {
            auto = "update"
          }
          show_sql = true
        }
      }
    }
  }
}

{
  "datasources": {
    "default": {
      "name": "mydb"
    }
  },
  "jpa": {
    "default": {
      "entity-scan": {
        "packages": ["foo.bar", "foo.baz"]
      },
      "properties": {
        "hibernate": {
          "hbm2ddl": {
            "auto": "update"
          },
          "show_sql": true
        }
      }
    }
  }
}

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.

5.4 Entity Scan Configuration

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:

Limiting Entity Scan

jpa.default.entity-scan.packages[0]=foo.bar

jpa:
    default:
        entity-scan:
            packages:
                - 'foo.bar'

[jpa]
  [jpa.default]
    [jpa.default.entity-scan]
      packages=[
        "foo.bar"
      ]

jpa {
  'default' {
    entityScan {
      packages = ["foo.bar"]
    }
  }
}

{
  jpa {
    default {
      entity-scan {
        packages = ["foo.bar"]
      }
    }
  }
}

{
  "jpa": {
    "default": {
      "entity-scan": {
        "packages": ["foo.bar"]
      }
    }
  }
}

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:

Generating Introspection Metadata for External Classes

@Introspected(packages="foo.bar")

This will generate introspection metadata for all classes in the foo.bar package.

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

Enabling Full Classpath Scanning

jpa.default.entity-scan.classpath=true
jpa.default.entity-scan.packages[0]=foo.bar

jpa:
    default:
        entity-scan:
            classpath: true
            packages:
                - 'foo.bar'

[jpa]
  [jpa.default]
    [jpa.default.entity-scan]
      classpath=true
      packages=[
        "foo.bar"
      ]

jpa {
  'default' {
    entityScan {
      classpath = true
      packages = ["foo.bar"]
    }
  }
}

{
  jpa {
    default {
      entity-scan {
        classpath = true
        packages = ["foo.bar"]
      }
    }
  }
}

{
  "jpa": {
    "default": {
      "entity-scan": {
        "classpath": true,
        "packages": ["foo.bar"]
      }
    }
  }
}

Note that this approach has the following disadvantages:

It is slower, since Micronaut has to search through JAR files and scan class files with ASM
It does not work in GraalVM substrate.

5.5 Configuring Hibernate Reactive

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

implementation("io.micronaut.sql:micronaut-hibernate-reactive")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-hibernate-reactive</artifactId>
</dependency>

and the Micronaut Data Transaction Hibernate dependency:

implementation("io.micronaut.data:micronaut-data-tx-hibernate")

<dependency>
    <groupId>io.micronaut.data</groupId>
    <artifactId>micronaut-data-tx-hibernate</artifactId>
</dependency>

Hibernate Reactive requires Java 11

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:

implementation("io.vertx:vertx-mysql-client")

<dependency>
    <groupId>io.vertx</groupId>
    <artifactId>vertx-mysql-client</artifactId>
</dependency>

implementation("io.vertx:vertx-pg-client")

<dependency>
    <groupId>io.vertx</groupId>
    <artifactId>vertx-pg-client</artifactId>
</dependency>

implementation("io.vertx:vertx-mssql-client")

<dependency>
    <groupId>io.vertx</groupId>
    <artifactId>vertx-mssql-client</artifactId>
</dependency>

implementation("io.vertx:vertx-oracle-client")

<dependency>
    <groupId>io.vertx</groupId>
    <artifactId>vertx-oracle-client</artifactId>
</dependency>

And configure it using properties:

jpa.default.reactive=true
jpa.default.properties.hibernate.connection.url=jdbc:postgresql:database
jpa.default.properties.hibernate.connection.username=myUsername
jpa.default.properties.hibernate.connection.password=myPassword

jpa:
  default:
    reactive: true
    properties:
      hibernate:
        connection:
          url: jdbc:postgresql:database # Use JDBC style url
          username: myUsername
          password: myPassword

[jpa]
  [jpa.default]
    reactive=true
    [jpa.default.properties]
      [jpa.default.properties.hibernate]
        [jpa.default.properties.hibernate.connection]
          url="jdbc:postgresql:database"
          username="myUsername"
          password="myPassword"

jpa {
  'default' {
    reactive = true
    properties {
      hibernate {
        connection {
          url = "jdbc:postgresql:database"
          username = "myUsername"
          password = "myPassword"
        }
      }
    }
  }
}

{
  jpa {
    default {
      reactive = true
      properties {
        hibernate {
          connection {
            url = "jdbc:postgresql:database"
            username = "myUsername"
            password = "myPassword"
          }
        }
      }
    }
  }
}

{
  "jpa": {
    "default": {
      "reactive": true,
      "properties": {
        "hibernate": {
          "connection": {
            "url": "jdbc:postgresql:database",
            "username": "myUsername",
            "password": "myPassword"
          }
        }
      }
    }
  }
}

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

implementation("io.micronaut.sql:micronaut-vertx-mysql-client")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-vertx-mysql-client</artifactId>
</dependency>

implementation("io.micronaut.sql:micronaut-vertx-pg-client")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-vertx-pg-client</artifactId>
</dependency>

And configure the client:

vertx.pg.client.port=5432
vertx.pg.client.host=the-host
vertx.pg.client.database=the-db
vertx.pg.client.user=user
vertx.pg.client.password=secret
vertx.pg.client.max-size=10

vertx:
  pg:
    client:
      port: 5432
      host: 'the-host'
      database: 'the-db'
      user: 'user'
      password: 'secret'
      max-size:  10

[vertx]
  [vertx.pg]
    [vertx.pg.client]
      port=5432
      host="the-host"
      database="the-db"
      user="user"
      password="secret"
      max-size=10

vertx {
  pg {
    client {
      port = 5432
      host = "the-host"
      database = "the-db"
      user = "user"
      password = "secret"
      maxSize = 10
    }
  }
}

{
  vertx {
    pg {
      client {
        port = 5432
        host = "the-host"
        database = "the-db"
        user = "user"
        password = "secret"
        max-size = 10
      }
    }
  }
}

{
  "vertx": {
    "pg": {
      "client": {
        "port": 5432,
        "host": "the-host",
        "database": "the-db",
        "user": "user",
        "password": "secret",
        "max-size": 10
      }
    }
  }
}

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

5.6 Using compile-time Hibernate proxies

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

This has a few disadvantages:

Runtime class generation can affect startup and runtime performance
Environments like GraalVM don’t support it

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

jpa:
  default:
    compile-time-hibernate-proxies: true

Compile-time proxies require for an entity which needs to have a proxy to be annotated with @GenerateProxy:

For example:

@Entity
public class Pet {

    @ManyToOne(fetch = FetchType.LAZY)
    private Owner owner;

    //...
}

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

@Entity
@GenerateProxy
public class Owner {
    //...
}

Compile-time proxies are enabled by default for GraalVM environment.

5.7 Understanding LazyInitializationException

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

6 Configuring JAsync SQL

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

6.1 Configuring jasync-sql Client

Using the CLI

If you are creating your project using the Micronaut CLI, supply the jasync-sql feature to configure the Jasync PostgreSQL and MySQL client in your project:

$ mn create-app my-app --features jasync-sql

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

implementation("io.micronaut.sql:micronaut-jasync-sql")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jasync-sql</artifactId>
</dependency>

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

jasync.client.port=5432
jasync.client.host=the-host
jasync.client.database=the-db
jasync.client.username=test
jasync.client.password=test
jasync.client.maxActiveConnections=5

jasync:
    client:
        port: 5432
        host: the-host
        database: the-db
        username: test
        password: test
        maxActiveConnections: 5

[jasync]
  [jasync.client]
    port=5432
    host="the-host"
    database="the-db"
    username="test"
    password="test"
    maxActiveConnections=5

jasync {
  client {
    port = 5432
    host = "the-host"
    database = "the-db"
    username = "test"
    password = "test"
    maxActiveConnections = 5
  }
}

{
  jasync {
    client {
      port = 5432
      host = "the-host"
      database = "the-db"
      username = "test"
      password = "test"
      maxActiveConnections = 5
    }
  }
}

{
  "jasync": {
    "client": {
      "port": 5432,
      "host": "the-host",
      "database": "the-db",
      "username": "test",
      "password": "test",
      "maxActiveConnections": 5
    }
  }
}

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:

result = client.sendQuery('SELECT * FROM pg_stat_database').thenApply({ QueryResult resultSet -> (1)
    return "Size: ${resultSet.rows.size()}"
}).get()

1	`client` is an instance of the `com.github.jasync.sql.db.Connection` bean.

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.

6.2 Database Health Checks

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.

7 Configuring jOOQ

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:

implementation("io.micronaut.sql:micronaut-jooq")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jooq</artifactId>
</dependency>

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

Configuration - jOOQ Configuration
DSLContext - jOOQ DSLContext

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

JooqExceptionTranslatorProvider for each DataSource
SpringTransactionProvider for each Spring PlatformTransactionManager

7.1 Configuring SQL dialect

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:

Configuring SQL dialect

jooq.datasources.default.sql-dialect=POSTGRES

jooq:
    datasources:
        default:
            sql-dialect: 'POSTGRES'

[jooq]
  [jooq.datasources]
    [jooq.datasources.default]
      sql-dialect="POSTGRES"

jooq {
  datasources {
    'default' {
      sqlDialect = "POSTGRES"
    }
  }
}

{
  jooq {
    datasources {
      default {
        sql-dialect = "POSTGRES"
      }
    }
  }
}

{
  "jooq": {
    "datasources": {
      "default": {
        "sql-dialect": "POSTGRES"
      }
    }
  }
}

7.2 Configuring additional provider beans

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:

7.3 Using JsonMapper to convert JSON(B) 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.

Configuring Micronaut Json converter

jooq:
    datasources:
        default:
            json-converter-enabled: true

7.4 GraalVM native image

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:

jooq {
    devDb(sourceSets.main) {
        ...
        generator {
            ...
            generate {
                jpaAnnotations = true (1)
            }
        }
    }
}

1	Configure jOOQ to generate the JPA annotations.

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:

implementation("org.simpleflatmapper:sfm-jdbc:8.2.3")

<dependency>
    <groupId>org.simpleflatmapper</groupId>
    <artifactId>sfm-jdbc</artifactId>
    <version>8.2.3</version>
</dependency>

Find more information in the jOOQ documentation.

8 Configuring Jdbi

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:

implementation("io.micronaut.sql:micronaut-jdbi")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-jdbi</artifactId>
</dependency>

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

Jdbi - the Jdbi instance

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

SpringTransactionHandler for each Spring PlatformTransactionManager

8.1 Configuring additional provider 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:

9 Configuring Reactive MySQL Client

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.

Using the CLI

If you are creating your project using the Micronaut CLI, supply the vertx-mysql-client feature to configure the MySQL Vertx client in your project:

$ mn create-app my-app --features vertx-mysql-client

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

implementation("io.micronaut.sql:micronaut-vertx-mysql-client")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-vertx-mysql-client</artifactId>
</dependency>

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

application.yml

vertx:
	mysql:
	    client:
            port: 3306
            host: the-host
            database: the-db
            user: test
            password: test
            maxSize: 5

You can also connect to MySQL using uri instead of the other properties.

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:

result = client.query('SELECT * FROM foo').rxExecute().map({ RowSet<Row> rowSet -> (1)
    RowIterator<Row> iterator = rowSet.iterator()
    int id = iterator.next().getInteger("id")
    return "id: ${id}"
}).blockingGet()

1	`client` is an instance of the `io.vertx.reactivex.mysqlclient.MySQLPool` bean.

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.

9.1 MySQL Health Checks

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.

10 Configuring Reactive PostgreSQL Client

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.

Using the CLI

If you are creating your project using the Micronaut CLI, supply the vertx-pg-client feature to configure the PostgreSQL Vertx client in your project:

$ mn create-app my-app --features vertx-pg-client

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

implementation("io.micronaut.sql:micronaut-vertx-pg-client")

<dependency>
    <groupId>io.micronaut.sql</groupId>
    <artifactId>micronaut-vertx-pg-client</artifactId>
</dependency>

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

vertx.pg.client.port=3306
vertx.pg.client.host=the-host
vertx.pg.client.database=the-db
vertx.pg.client.user=test
vertx.pg.client.password=test
vertx.pg.client.maxSize=5

vertx:
  pg:
    client:
      port: 3306
      host: the-host
      database: the-db
      user: test
      password: test
      maxSize: 5

[vertx]
  [vertx.pg]
    [vertx.pg.client]
      port=3306
      host="the-host"
      database="the-db"
      user="test"
      password="test"
      maxSize=5

vertx {
  pg {
    client {
      port = 3306
      host = "the-host"
      database = "the-db"
      user = "test"
      password = "test"
      maxSize = 5
    }
  }
}

{
  vertx {
    pg {
      client {
        port = 3306
        host = "the-host"
        database = "the-db"
        user = "test"
        password = "test"
        maxSize = 5
      }
    }
  }
}

{
  "vertx": {
    "pg": {
      "client": {
        "port": 3306,
        "host": "the-host",
        "database": "the-db",
        "user": "test",
        "password": "test",
        "maxSize": 5
      }
    }
  }
}

You can also connect to PostgreSQL using uri instead of the other properties.

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

result = client.query('SELECT * FROM pg_stat_database').rxExecute().map({ RowSet<Row> rowSet -> (1)
    int size = 0
    RowIterator<Row> iterator = rowSet.iterator()
    while (iterator.hasNext()) {
        iterator.next()
        size++
    }
    return "Size: ${size}"
}).blockingGet()

1	`client` is an instance of the `io.vertx.reactivex.pgclient.PgPool` bean.

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.

10.1 PostgreSQL Health Checks

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.

11 Repository

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

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

Table of Contents

Micronaut SQL Libraries

1 Introduction

2 Release History

3 Breaking Changes

Micronaut SQL 5.8.0

4 Configuring JDBC

4.1 Disable Micronaut JDBC Data Sources

4.2 Configuring JDBC Connection Pools

4.3 Configuring Multiple Data Sources

4.4 JDBC Health Checks

5 Configuring Hibernate

Setting up a Hibernate/JPA EntityManager

5.1 Disable Micronaut Hibernate JPA

5.2 Injecting an EntityManager or Hibernate Session

5.3 Customizing Hibernate/JPA Configuration

5.4 Entity Scan Configuration

5.5 Configuring Hibernate Reactive

5.6 Using compile-time Hibernate proxies

5.7 Understanding LazyInitializationException

6 Configuring JAsync SQL

6.1 Configuring jasync-sql Client

6.2 Database Health Checks

7 Configuring jOOQ

7.1 Configuring SQL dialect

7.2 Configuring additional provider beans

7.3 Using JsonMapper to convert JSON(B) types

7.4 GraalVM native image

8 Configuring Jdbi

8.1 Configuring additional provider beans

9 Configuring Reactive MySQL Client

9.1 MySQL Health Checks

10 Configuring Reactive PostgreSQL Client

10.1 PostgreSQL Health Checks

11 Repository