Using Postgresql in a Grails Application

I've always been using MySQL with almost all my web applications, but I've read a few articles talking about Postgresql and decided to give it a try. Being so used to MySQL, I wanted to see how fast it would take to at least be able have usable knowledge of Postgresql. So, let get to it!

• Install Postgresql on Ubuntu 13.10
• Create Users and database on Postgresql
• Configure a grails application to use Postgresql
• Verify application is connecting to the Database

Install Postgresql on Ubuntu 13.10

There's a number of tutorials on how to install postgres. Some included adding a new repository, but I tend to prefer the packages that is associated with an Ubuntu version. In my case since I am using Ubuntu 13.10, the version available is postgresql 9.1

  sudo apt-get install postgresql-9.1  

If you have a different version of Ubuntu, you can search for the postgresql version using the command:

  apt-cache search postgresql  

Create Users and database on Postgresql

Login to Postgresql. Similar to MySQL, you would want to create a user for your Grails application that would have access to the Postgresql database. In order to do this, you need to login as the default Postgresql user, which is 'postgres'

 sudo -u postgres psql template1  

Change the password for the default user and exit from the psql command-line. Note that '\q' is the command to quit the psql command-line.

 template1=# ALTER USER postgres with encrypted password 'newpass';  
 template1=# \q  

Modify a configuration file called pg_hba.conf and change the line below - changing 'peer' to 'md5'.

 unix$ sudo vi /etc/postgresql/9.1/main/pg_hba.conf  

   local all postgres peer md5

Restart Postgresql.

 sudo /etc/init.d/postgresql restart  

Create the new user that would be used in your Grails application. To know what options are available for createuser.

 unix$ createuser -U postgres -d -e -E -I -P -r -s myuser<br/>  
 Enter password for new role: <br/>  
 Enter it again: <br/>  
 Password: <br/>  
 CREATE ROLE myuser ENCRYPTED PASSWORD 'md5eb6108579123bee126149054337490' SUPERUSER CREATEDB CREATEROLE NOINHERIT LOGIN;  

Restart the database again using the command above ('postgresql restart');

Verify if the new user was created, login using the username and password. You should be able to login with your new user and password and see the command-line where you can issue SQL queries.

 unix$ psql -U myuser -d mydb -W  
 Password for user myuser:  
 psql (9.1.10)  
 Type "help" for help.  
 mydb=#   

Create a new database that you would use for your Grails application.

 unix$ createdb -U myuser -W mydb  

Configure a grails application to use Postgresql

After you have successfully installed and configured Postgresql, in your Grails application, you need to modify 2 configuration files, BuildConfig.groovy and DataSource.groovy.

First, we need to add the JDBC jar for Postgresql. Add this line inside the BuildConfig.groovy file inside the dependencies. Verify the jar version that is available on maven.

 dependencies {  
     // specify dependencies here under either 'build', 'compile', 'runtime', 'test' or 'provided' scopes e.g.  
     //runtime 'mysql:mysql-connector-java:5.1.24'  
           runtime 'postgresql:postgresql:9.1-902.jdbc4'   
   }  

Next, add the following configuration in DataSource.groovy.

    dataSource {  
       driverClassName = "org.postgresql.Driver"  
      dialect = org.hibernate.dialect.PostgreSQLDialect  
      username = "myuser"  
      password = "mypassword"  
      dbCreate = "update" // one of 'create', 'create-drop','update'  
      url = "jdbc:postgresql://localhost:5432/mydb"  
     }  

Verify application is connecting to the Database

Run the Grails application (grails run-app) and verify that the tables are created. You can login again using psql and issue the command \dt to list all tables. You should be able to see all your Grails domain classes.

 unix$ psql -U myuser -d mydb -W  
 Password for user myuser:   
 psql (9.1.10)  
 Type "help" for help.  
 mydb=# \dt  
         List of relations  
  Schema |    Name    | Type |  Owner    
 --------+-------------------+-------+------------  
  public | table_one    | table | myuser  
  public | table_two    | table | myuser  
  public | table_three   | table | myuser  

So far so good. One problem I encountered is that my Grails app had a table called 'User'. Unfortunately, this is a reserved word in Postgresql. It was an easy fix by just mapping the domain class user to the table using 'static mapping'.