Define Datasource validation in Thorntail

Question

In WildFly standalone configuration we can define a validation query in the datasource. In case the DB connection is lost, after the background validation milliseconds defined, the connection can be recovered. Without this validation if the connection is lost, it will not be recovered until the application is restarted.

    <datasource jndi-name="java:jboss/datasources/MyDS" pool-name="MyDS" enabled="true" use-java-context="true">
        <connection-url>jdbc:postgresql://localhost:5432/myDB</connection-url>
        <driver>postgresql</driver>
        <security>
            <user-name>dbuser</user-name>
            <password>password</password>
        </security>
        <validation>
            <check-valid-connection-sql>select 1</check-valid-connection-sql>
            <validate-on-match>false</validate-on-match>
            <background-validation>true</background-validation>
            <background-validation-millis>30000</background-validation-millis>
        </validation>
    </datasource>

How can I achieve the same in Thorntail project.yml file?

thorntail:
  datasources:
    data-sources:
      MyDS:
        driver-name: postgresql
        connection-url: jdbc:postgresql://localhost:5432/myDB
        user-name: dbuser
        password: password

I tried adding a validation node, but it didn't work

      validation:
        check-valid-connection-sql: select 1
        validate-on-match: false
        background-validation: true
        background-validation-millis: 30000

Answer 1

Here's an example PostgreSQL datasource taken from this documentation: https://docs.thorntail.io/2.7.0.Final/#_example_datasource_definitions It includes connection validation, too.

thorntail:
  datasources:
    data-sources:
      MyDS:
        driver-name: postgresql
        connection-url: jdbc:postgresql://localhost:5432/postgresdb
        user-name: admin
        password: admin
        valid-connection-checker-class-name: org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker
        validate-on-match: true
        background-validation: false
        exception-sorter-class-name: org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter

Other connection validation options, including those that you use, are described in the same documentation.