What is up to date method to set database in Grail

Question

How to set database in Grails 3? Manual looks obsolete here: http://grails.github.io/grails-doc/3.0.x/guide/single.html#dataSource

since it gives an example of setting with grove code like follows:

dataSource {
    pooled = true
    dbCreate = "update"
    url = "jdbc:mysql://localhost:3306/my_database"
    driverClassName = "com.mysql.jdbc.Driver"
    dialect = org.hibernate.dialect.MySQL5InnoDBDialect
    username = "username"
    password = "password"
    ...

while I have application.yml with

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: org.h2.Driver
    username: sa
    password:

What method is up to date?

Answer 1

Here's a current postgres example with multiple datasources. I found that the redundancy was necessary because there were some discrepancies in the levels that grails, spring and hibernate were expecting to see config information. this satisfied all of them. also include a runtime dependency for your jdbc driver. for postgres, it was

dependencies {
    // all other dependencies

    runtime "org.postgresql:postgresql:9.4.1208"
}

[Start of application.yml file. yes, the '---' lines need to be there. they keep the configslurper from getting confused.]

---
hibernate:
    cache:
        queries: true
        use_second_level_cache: true
        use_query_cache: true
        region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
    dialect: org.hibernate.dialect.H2Dialect

dataSources:
    hibernate:
        cache:
            queries: true
            use_second_level_cache: true
            use_query_cache: true
            region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
        dialect: org.hibernate.dialect.H2Dialect

    dataSource:
        pooled: true
        jmxExport: true
        driverClassName: org.postgresql.Driver
        driver_class: org.postgresql.Driver
        username: user
        password: pw
        dialect: org.hibernate.dialect.PostgreSQLDialect
        hibernate:
            cache:
                queries: true
                use_second_level_cache: true
                use_query_cache: true
                region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
            dialect: org.hibernate.dialect.PostgreSQLDialect
            connection.driver_class: org.postgresql.Driver
            connection.url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbone
            connection.username: user
            connection.password: pw
            connection.pool_size: 15
            connection.dialect: org.hibernate.dialect.PostgreSQLDialect
            driver_class: org.postgresql.Driver
            url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbone
            username: user
            password: pw
            pool_size: 15
            driverClassName: org.postgresql.Driver

    dataSource_two:
        pooled: true
        jmxExport: true
        driverClassName: org.postgresql.Driver
        driver_class: org.postgresql.Driver
        username: usertwo
        password: pw
        dialect: org.hibernate.dialect.PostgreSQLDialect
        hibernate:
            cache:
                queries: true
                use_second_level_cache: true
                use_query_cache: true
                region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
            dialect: org.hibernate.dialect.PostgreSQLDialect
            connection.driver_class: org.postgresql.Driver
            connection.url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbtwo
            connection.username: usertwo
            connection.password: pw
            connection.pool_size: 15
            connection.dialect: org.hibernate.dialect.PostgreSQLDialect
            driver_class: org.postgresql.Driver
            url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbtwo
            username: usertwo
            password: pw
            pool_size: 15
            driverClassName: org.postgresql.Driver

    dataSource_three:
        pooled: true
        jmxExport: true
        driverClassName: org.postgresql.Driver
        driver_class: org.postgresql.Driver
        username: userthree
        password: pw
        dialect: org.hibernate.dialect.PostgreSQLDialect
        hibernate:
            cache:
                queries: true
                use_second_level_cache: true
                use_query_cache: true
                region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
            dialect: org.hibernate.dialect.PostgreSQLDialect
            connection.driver_class: org.postgresql.Driver
            connection.url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbthree
            connection.username: userthree
            connection.password: pw
            connection.pool_size: 15
            connection.dialect: org.hibernate.dialect.PostgreSQLDialect
            driver_class: org.postgresql.Driver
            url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbthree
            username: userthree
            password: pw
            pool_size: 15
            driverClassName: org.postgresql.Driver

environments:
    local:
        hibernate:
            cache:
                queries: true
                use_second_level_cache: true
                use_query_cache: true
                region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
            dialect: org.hibernate.dialect.H2Dialect
        dataSources:
            hibernate:
                cache:
                    queries: true
                    use_second_level_cache: true
                    use_query_cache: true
                    region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
                dialect: org.hibernate.dialect.H2Dialect
            dataSource:
                dbCreate: create
                url: jdbc:postgresql://127.0.0.1:5432/dbone
                driver_class: org.postgresql.Driver
                driverClassName: org.postgresql.Driver
                username: userone
                password: pw
                pool_size: 15
                dialect: org.hibernate.dialect.PostgreSQLDialect
                logSql: true
                hibernate:
                    cache:
                        queries: true
                        use_second_level_cache: true
                        use_query_cache: true
                        region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
                    dialect: org.hibernate.dialect.PostgreSQLDialect
                    connection.driver_class: org.postgresql.Driver
                    connection.url: jdbc:postgresql://127.0.0.1:5432/dbone
                    connection.username: userone
                    connection.password: pw
                    connection.pool_size: 15
                    connection.dialect: org.hibernate.dialect.PostgreSQLDialect
                    driver_class: org.postgresql.Driver
                    url: jdbc:postgresql://127.0.0.1:5432/dbone
                    username: userone
                    password: pw
                    pool_size: 15
                    driverClassName: org.postgresql.Driver

            dataSource_two:
                dbCreate: create
                url: jdbc:postgresql://127.0.0.1:5432/dbtwo
                driver_class: org.postgresql.Driver
                driverClassName: org.postgresql.Driver
                username: usertwo
                password: pw
                pool_size: 15
                dialect: org.hibernate.dialect.PostgreSQLDialect
                logSql: true
                hibernate:
                    cache:
                        queries: true
                        use_second_level_cache: true
                        use_query_cache: true
                        region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
                    dialect: org.hibernate.dialect.PostgreSQLDialect
                    connection.driver_class: org.postgresql.Driver
                    connection.url: jdbc:postgresql://127.0.0.1:5432/dbtwo
                    connection.username: usertwo
                    connection.password: pw
                    connection.pool_size: 15
                    connection.dialect: org.hibernate.dialect.PostgreSQLDialect
                    driver_class: org.postgresql.Driver
                    url: jdbc:postgresql://127.0.0.1:5432/dbtwo
                    username: usertwo
                    password: pw
                    pool_size: 15
                    driverClassName: org.postgresql.Driver
            dataSource_three:
                dbCreate: create
                url: jdbc:postgresql://127.0.0.1:5432/dbthree
                driver_class: org.postgresql.Driver
                driverClassName: org.postgresql.Driver
                username: userthree
                password: pw
                pool_size: 15
                dialect: org.hibernate.dialect.PostgreSQLDialect
                logSql: true
                hibernate:
                    cache:
                        queries: true
                        use_second_level_cache: true
                        use_query_cache: true
                        region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
                    dialect: org.hibernate.dialect.PostgreSQLDialect
                    connection.driver_class: org.postgresql.Driver
                    connection.url: jdbc:postgresql://127.0.0.1:5432/dbthree
                    connection.username: userthree
                    connection.password: pw
                    connection.pool_size: 15
                    connection.dialect: org.hibernate.dialect.PostgreSQLDialect
                    driver_class: org.postgresql.Driver
                    url: jdbc:postgresql://127.0.0.1:5432/dbthree
                    username: userthree
                    password: pw
                    pool_size: 15
                    driverClassName: org.postgresql.Driver

    development:
        hibernate:
            cache:
                queries: true
                use_second_level_cache: true
                use_query_cache: true
                region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
            dialect: org.hibernate.dialect.H2Dialect
        dataSources:
            hibernate:
                cache:
                    queries: true
                    use_second_level_cache: true
                    use_query_cache: true
                    region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
                dialect: org.hibernate.dialect.H2Dialect
            dataSource:
                dbCreate: create-drop
                url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbone
                driver_class: org.postgresql.Driver
                driverClassName: org.postgresql.Driver
                username: userone
                password: pw
                pool_size: 15
                dialect: org.hibernate.dialect.PostgreSQLDialect
                hibernate:
                    cache:
                        queries: true
                        use_second_level_cache: true
                        use_query_cache: true
                        region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
                    dialect: org.hibernate.dialect.PostgreSQLDialect
                    connection.driver_class: org.postgresql.Driver
                    connection.url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbone
                    connection.username: userone
                    connection.password: pw
                    connection.pool_size: 15
                    connection.dialect: org.hibernate.dialect.PostgreSQLDialect
                    driver_class: org.postgresql.Driver
                    url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbone
                    username: userone
                    password: pw
                    pool_size: 15
                    driverClassName: org.postgresql.Driver

            dataSource_two:
                dbCreate: create-drop
                url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbtwo
                driver_class: org.postgresql.Driver
                driverClassName: org.postgresql.Driver
                username: usertwo
                password: pw
                pool_size: 15
                dialect: org.hibernate.dialect.PostgreSQLDialect
                hibernate:
                    cache:
                        queries: true
                        use_second_level_cache: true
                        use_query_cache: true
                        region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
                    dialect: org.hibernate.dialect.PostgreSQLDialect
                    connection.driver_class: org.postgresql.Driver
                    connection.url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbtwo
                    connection.username: usertwo
                    connection.password: pw
                    connection.pool_size: 15
                    connection.dialect: org.hibernate.dialect.PostgreSQLDialect
                    driver_class: org.postgresql.Driver
                    url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbtwo
                    username: usertwo
                    password: pw
                    pool_size: 15
                    driverClassName: org.postgresql.Driver
            dataSource_three:
                dbCreate: create-drop
                url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbthree
                driver_class: org.postgresql.Driver
                driverClassName: org.postgresql.Driver
                username: userthree
                password: pw
                pool_size: 15
                dialect: org.hibernate.dialect.PostgreSQLDialect
                hibernate:
                    cache:
                        queries: true
                        use_second_level_cache: true
                        use_query_cache: true
                        region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'
                    dialect: org.hibernate.dialect.PostgreSQLDialect
                    connection.driver_class: org.postgresql.Driver
                    connection.url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbthree
                    connection.username: userthree
                    connection.password: pw
                    connection.pool_size: 15
                    connection.dialect: org.hibernate.dialect.PostgreSQLDialect
                    driver_class: org.postgresql.Driver
                    url: jdbc:postgresql://ec2-1-2-3-4.compute-1.amazonaws.com:5432/dbthree
                    username: userthree
                    password: pw
                    pool_size: 15
                    driverClassName: org.postgresql.Driver

---
---
[ this is not the end of the application.yml, just the end of the datasource section.]

Answer 2

As of grails 3.2.2 with GORM 6, the grails documentation is current and correct and this "extra" config shown in the first answer date Apr 7 is no longer necessary. An example dataSource config is:

---
---
dataSources:
    hibernate:
        cache:
            queries: true
            use_second_level_cache: true
            use_query_cache: true
            region.factory_class: 'org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory'

    dataSource:
        pooled: true
        # pool_size: 15
        jmxExport: true
        driverClassName: org.postgresql.Driver

    dataSource_two:
        pooled: true
        # pool_size: 15
        jmxExport: true
        driverClassName: org.postgresql.Driver

#############################################################
# Spring Actuator Endpoints are Disabled by Default
#############################################################
---
---
endpoints:
    enabled: false
    jmx:
        enabled: true

---
---
grails:
    codegen:
        defaultPackage: com.healthreveal
    controllers:
        defaultScope: singleton
    converters:
        encoding: UTF-8
    mime:
        disable:
            accept:
                header:
                    userAgents:
                        - Gecko
                        - WebKit
                        - Presto
                        - Trident
        types:
            all: '*/*'
            atom: application/atom+xml
            css: text/css
            csv: text/csv
            form: application/x-www-form-urlencoded
            html:
              - text/html
              - application/xhtml+xml
            js: text/javascript
            json:
              - application/json
              - text/json
            multipartForm: multipart/form-data
            rss: application/rss+xml
            text: text/plain
            hal:
              - application/hal+json
              - application/hal+xml
            xml:
              - text/xml
              - application/xml
    profile: rest-api (or web)
    spring:
        transactionManagement:
            proxies: false
        urlmapping:
            cache:
            maxsize: 1000

---
---
# you would think you could put this in the section above, but for some reason you can't
grails.logging.jul.usebridge: true

---
---
hibernate:
    cache:
        queries: false
        use_second_level_cache: true
        use_query_cache: false
        region.factory_class: org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory

---
---
info:
    app:
        name: '@info.app.name@'
        version: '@info.app.version@'
        grailsVersion: '@info.app.grailsVersion@'

---
---
spring:
    groovy:
        template:
            check-template-location: false

#################################################################
# This second section is a set of environment-specific over-rides
# to the common parameters.  There is no need to repeat a
# common parameter unless you want to change the setting (or add
# a new setting) within the context of an environment.
#################################################################

---
---
environments:
    development:
        dataSources:
            dataSource:
                dbCreate: update
                url: jdbc:postgresql://localhost:5432/dbOne
                username: someuser
                password: somepassword
            dataSource_xyz:
                dbCreate: update
                url: jdbc:postgresql://localhost:5432/dbXYZ
                username: someuser
                password: somepassword
        grails:
            serverURL: "http://localhost:8080"
            plugin:
                forceSSL:
                    enabled: false
       server:
            port: 8080
            ssl:
                enabled: false