Using PostgreSQL database driver within your Grails applications

By default Grails uses an in-memory database that stores data for your apps within volatile RAM. You can leave this as it is but most programmers like to know that their database is more peristent.

In order to use your own data sources you need to be looking at the DataSource.groovy file within /grails-app/conf directory. Here you specify the database that your application will use.

There are three sections within this file so you can specify different data sources for different purposes: production, test and development. In the example I will demonstrate using a different PostgreSQL database for each purpose however you can use three entirely different products if you wish.


Before you start configuring anything in the DataSource.groovy file ensure that you have completed the following:

1. Ensure PostgreSQL is installed


2. Download PostgreSQL JDBC driver and save it into your applications lib folder.

3. Ensure you have a PostgreSQL user to use for the database connection. If you do not wish to use default user, create new user with createuser command.


4. Create the relevant databases in PostgreSQL that your grails application will use. This can be done simply though PGAdmin or using createdb commands as the PostgreSQL user.

5. Return to the Datasource.groovy config file and use the following settings:

dataSource {
        pooled = true
        driverClassName = "org.postgresql.Driver"
        username = "postgres"
        password = "password"
}
hibernate {
    cache.use_second_level_cache=true
    cache.use_query_cache=true
    cache.provider_class='org.hibernate.cache.EhCacheProvider'
}
// environment specific settings
environments {
        development {
                dataSource {
                        dbCreate = "update" // one of 'create', 'create-drop','update'
                        url = "jdbc:postgresql://localhost:5432/devdb"
                }
        }
        test {
                dataSource {
                        dbCreate = "update"
                        url = "jdbc:postgresql://localhost:5432/testdb"
                }
        }
        production {
                dataSource {
                        dbCreate = "update"
                        url = "jdbc:postgresql://localhost:5432/proddb"
                }
        }
}


Please note that the following are examples of the different database purposes:

• Development db is when you use grails run-app to launch you application
• Test db is when you run grails unit and integration tests
• Production db is used when you upload the application to an app server such as glassfish etc.

Now run you application in whatever environment you wish and view data in your own PostgreSQL databases. If you not a SQL savey then learn how to use PGAdmin to view tables and database structure.