Database

Scripts to create the required database content are provided for the supported databases.

For customers that are upgrading, if the database scheme was already created in a previous version, check to see if there are database upgrade scripts provided for any of the intermediate versions in the Upgrade Instructions.

Datasource configuration

The datasource needs to be configured in the configuration YAML file.

blueriq:
  <service>:
    datasource:
      <datasource-name>:
        url: <url>
        username: <username>
        password: <password>
        driver-class-name: <driver-class>
        validation-query: <validation-query>
        testWhileIdle: true
        timeBetweenEvictionRunsMillis: 5000

hibernate:
  dialect: <dialect>
  hbm2ddl:
    auto: validate
  id:
    new_generator_mappings: true
  show_sql: false
  use_nationalized_character_data: true

Example configuration

Below are configuration examples which contain a JDBC url, driver-class, validation query and dialect for each supported database.

blueriq:
  <service>:
    datasource:
      <datasource-name>:
        url: jdbc:oracle:thin:@<host>:<port>/<servicename>
        username: <username>
        password: <password>
        driver-class-name: oracle.jdbc.driver.OracleDriver
        validation-query: SELECT 1 FROM DUAL
        testWhileIdle: true
        timeBetweenEvictionRunsMillis: 5000

hibernate:
  dialect: org.hibernate.dialect.OracleDialect
  hbm2ddl:
    auto: validate
  id:
    new_generator_mappings: true
  show_sql: false
  use_nationalized_character_data: true


blueriq:
  <service>:
    datasource:
      <datasource-name>:
        url: jdbc:sqlserver://<host>:<port>;databaseName=<database>;trustServerCertificate=true
        username: <username>
        password: <password>
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        validation-query: SELECT 1
        testWhileIdle: true
        timeBetweenEvictionRunsMillis: 5000
 
hibernate:
  dialect: org.hibernate.dialect.SQLServer2012Dialect
  hbm2ddl:
    auto: validate
  id:
    new_generator_mappings: true
  show_sql: false
  use_nationalized_character_data: true


blueriq:
   <service>:
    datasource:
      <datasource-name>:
        url: jdbc:postgresql://<host>:<port>/<database>
        username: <username>
        password: <password>
        driver-class-name: org.postgresql.Driver
        validation-query: SELECT 1
        testWhileIdle: true
        timeBetweenEvictionRunsMillis: 5000
 
hibernate:
  dialect: org.hibernate.dialect.PostgreSQLDialect
  hbm2ddl:
    auto: validate
  id:
    new_generator_mappings: true
  show_sql: false
  use_nationalized_character_data: true

Database query timeout

To specify the database query timeout, set the defaultQueryTimeoutSeconds property in the datasource configuration.

If multi-tenancy enabled, when needed, the value has to be set for each datasource separately.

Without setting property, the default value is 60 seconds.

blueriq:
  <service>:
    datasource:
      <datasource-name>:
        ...
        defaultQueryTimeoutSeconds: 10


For additional datasource configuration please refer to the Spring property documentation.

Database drivers

Depending on the underlying database, a specific driver needs to be installed on the application server as described on Configuring JDBC database drivers.