'How do I configure PostgreSQL with Grails 3.0?

I am using IntelliJ IDEA 15.0.2 as an IDE. I have created a Grails 3.0 application and changed it a bit to configure PostgreSQL.

Here is my dataSource:

dataSource:
pooled: true
jmxExport: true
driverClassName: org.postgresql.Driver
username: postgres
password: root

environments:
development:
    dataSource:
        dbCreate: update
        url: jdbc:postgresql://localhost:5432/trace_db
test:
    dataSource:
        dbCreate: update
        url: jdbc:postgresql://localhost:5432/trace_db
production:
    dataSource:
        dbCreate: update
        url: jdbc:postgresql://localhost:5432/trace_db
        properties:
            jmxEnabled: true
            initialSize: 5
            maxActive: 50
            minIdle: 5
            maxIdle: 25
            maxWait: 10000
            maxAge: 600000
            timeBetweenEvictionRunsMillis: 5000
            minEvictableIdleTimeMillis: 60000
            validationQuery: SELECT 1
            validationQueryTimeout: 3
            validationInterval: 15000
            testOnBorrow: true
            testWhileIdle: true
            testOnReturn: false
            jdbcInterceptors: ConnectionState
            defaultTransactionIsolation: 2 # TRANSACTION_READ_COMMITTED

And in my build.gradle I added runtime "postgresql:postgresql:9.4-1207.jdbc4".

But that gives errors when I run:

ERROR org.apache.tomcat.jdbc.pool.ConnectionPool - Unable to create initial connections of pool.
java.sql.SQLException: org.postgresql.Driver

What have I missed?

javapostgresqlgrailsjdbcintellij-idea


Solution 1:[1]

It looks like you have a tab missing in your configuration

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: org.postgresql.Driver
    username: postgres
    password: root

Everything after datasource needs to be indented.

Solution 2:[2]

I don't know if it was answered but I found that using the application.yml was not giving me access to the datasource configuration, instead it assumed the H2 Driver.

build.gradle (only dependencies block):

compile "org.springframework.boot:spring-boot-starter-logging"
compile "org.springframework.boot:spring-boot-autoconfigure"
compile "org.grails:grails-core"
compile "org.springframework.boot:spring-boot-starter-actuator"
compile "org.springframework.boot:spring-boot-starter-tomcat"
compile "org.grails:grails-dependencies"
compile "org.grails:grails-web-boot"
compile "org.grails.plugins:cache"
compile "org.grails.plugins:scaffolding"
compile "org.grails.plugins:hibernate4"
compile "org.hibernate:hibernate-ehcache"

runtime "postgresql:postgresql:9.4.1208-atlassian-hosted"

compile "org.grails.plugins:spring-security-core:3.0.4"

console "org.grails:grails-console"
profile "org.grails.profiles:web:3.1.6"
runtime "com.bertramlabs.plugins:asset-pipeline-grails:2.8.2"

testCompile "org.grails:grails-plugin-testing"
testCompile "org.grails.plugins:geb"
testRuntime "org.seleniumhq.selenium:selenium-htmlunit-driver:2.47.1"
testRuntime "net.sourceforge.htmlunit:htmlunit:2.18"

Erased everything related to database from application.yml and instead used the application.groovy:

dataSource{
    pooled= true
    jmxExport=true
    driverClassName= 'org.postgresql.Driver'
    username= 'xxxx'
    password= 'xxxx'    }

environments{   
    development{
        dataSource{
            dbCreate= 'create-drop'
            url= "jdbc:postgresql://localhost:5432/xxx"
            logSql= true
            hibernate.default_schema= "template_dm" 
        }
    }
    test{
        dataSource{
            dbCreate= 'create-drop'
            url= "jdbc:postgresql://localhost:5432/xxx"
            logSql= true
            hibernate.default_schema= "template_dm" 
        }
    }
    production{
        dataSource{
            dbCreate= 'create-drop'
            url= "jdbc:postgresql://localhost:5432/xxx"
            logSql= true
            hibernate.default_schema= "template_dm" 
        }
    }

}

Hope this helps everyone with the same issue.

Cheers

Solution 3:[3]

For those looking for a Grails 4/5 version of this:
implementation "org.postgresql:postgresql:42.3.2"

dataSource:
pooled: true
jmxExport: true
driverClassName: org.postgresql.Driver
username: postgres
password: ''

and finally

url: jdbc:postgresql://localhost:5432/db_name

Solution 4:[4]

Run the Grails application using grails run-app instead of running it through IntelliJ. I had this issue too with MySQL.

I still haven't figured out how to get to work properly through IntelliJ, but at least it works when you use the Grails console!

Note: this is assuming you already have the JDBC driver set up properly. I had mine set up properly for MySQL and kept digging around, thinking that I'd set it up wrong somehow.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 zak
Solution 2
Solution 3 Jerry U
Solution 4 Community

Related Questions