Using multiple datasources in a Grails project: Datasources plugin

Grails only supports the use of one Datasource and one SessionFactory and all classes use them, which might cause some problems when you have the need to work with multiple databases in the same project. This can be solved thanks to Burt Beckwith, he developed the Datasources plug in that allows you to configure multiple datasources in the same Grails project letting you define a set of classes to use one datasource and another set to use another datasource, for instance. In this article will review some benefits, issues and some tips and tricks that might help you get the best out of this plug in.

Benefits
There are multiple benefits when using the Datasources plug in:

• You can connect to multiple databases and use GORM to manage your data through domain classes, which gives you all the flexibility and ease-of-use that GORM offers.
• You can set up your datasources to be read-only.
• You can customize your datasources configuration per environment, which gives you a lot of flexibility.
• The datasources created by the plugin are regular Spring beans, which allows you to override their properties if you need to.
• You can specify a custom Hibernate configuration for each datasource by placing a Hibernate config file on the classpath.

Issues

• In Grails you can set up the different settings for you application and your main Datasource in an external configuration file and Grails will overwrite those settings during startup. This is very helpful when you need to run the same WAR file in different servers that require different configuration. However, there is no easy way to externalize the configuration for the Datasources plug in since the plug in doesn't overwrite the Datasources configuration if an external config file exists.
• You cannot specify a config location for the Hibernate custom configuration file, so for the Datasources plug in to pick it up you need to place the config file in the classpath and with a specific name, something like [datasource_name.hibernate.cfg.xml], otherwise it won't get picked up. This is problematic because if you want to use a custom config file for Hibernate that is different for each environment (dev, test, prod) you cannot easily do it since there is no way to specify in the datasource configuration a config location like you would do in the Datasource configuration file used by Grails by default.
• There are other issues mentioned in the plug in page that I am not going to repeat here.

Tips and Tricks

Externalizing the configuration of the Datasources

As mentioned before, the project configuration can be externalized so you can separate your configuration from your project WAR file, however this functionality is not included as part of the Datasources plug in, so here is a tip on how externalize the configuration of each different datasource you have. (You can find more information on how the externalized configuration works in the Grails documentation)

In order to do this, you need to overwrite the settings of the datasource bean on the Spring resources file, for example lets say you have the following configuration in your Datasources.groovy file:

datasources = {
 datasource(name: 'testDbReadOnly') {
  domainClasses([com.test.Person,
   com.test.Company,
   com.test.Deparment
  ])
  driverClassName('org.postgresql.Driver')
  readOnly(true)
  url('jdbc:postgresql://[server-name]:5432/test')
  pooled(true)
  username('test')
  password('test')
  logSql(false)
  dialect(org.hibernate.dialect.PostgreSQLDialect)

  hibernate {
   cache {
    use_query_cache(true)
    use_second_level_cache(true)
    provider_class('net.sf.ehcache.hibernate.EhCacheProvider')
   }
  }
 }
}

Then in your spring resources file you would need something like this:

 dataSource_testDbReadOnly(BasicDataSource) {bean ->
  bean.destroyMethod = 'close'
  username = GrailsConfig.get("testDbReadOnly.datasource.username", "sa")
  password = GrailsConfig.get("testDbReadOnly.datasource.password", "")
  driverClassName = GrailsConfig.get("testDbReadOnly.datasource.driver.name", "org.hsqldb.jdbcDriver")
  url = GrailsConfig.get("testDbReadOnly.datasource.url", "jdbc:h2:mem:testDb")
  maxWait = -1
  minEvictableIdleTimeMillis = (1000 * 60 * 5) // 5 min
  timeBetweenEvictionRunsMillis = (1000 * 60 * 5) // 5 min
  numTestsPerEvictionRun = 3
  testOnBorrow = true
  testWhileIdle = false
  testOnReturn = false
  validationQuery = "SELECT 1"
 }

As you can see you need to specify the name of the bean as dataSource_nameOfYourDataSource, in our example would be dataSource_testDbReadOnly.

What we are doing here is changing the Datasource bean properties and setting them based on values defined in the Config.groovy file, values that will be defined differently depending on the environment that we are running. So for example in our Config.groovy we will have:

environments {
 production {
  testDbReadOnly {
   datasource {
    driver.name = 'org.postgresql.Driver'
    url = 'jdbc:postgresql://[production-server]:5432/[production-db]'
    username = '[production-username]'
    password = '[production-password]'
   }
  }
 }
 development {
   testDbReadOnly {
    datasource {
     driver.name = 'org.postgresql.Driver'
     url = 'jdbc:postgresql://[dev-server]:5432/[dev-db]'
     username = '[dev-username]'
     password = '[dev-password]'
   }
   }
 }
 test {
   testDbReadOnly {
    datasource {
     driver.name = 'org.postgresql.Driver'
     url = 'jdbc:postgresql://[test-server]:5432/[test-db]'
     username = '[test-username]'
     password = '[test-password]'
    }
   }
 }
}

You are probably thinking that you can already do this in the Datasources.groovy file by specifying the configuration in different environments. However, since the Datasource configuration now depends on the configuration defined in Config.groovy file, you can overwrite these settings in your external configuration files, which give us what we want, move the configuration of the datasources to an external file.

You would need to add the datasource properties in your external config file as:

testDbReadOnly.datasource.driver.name=org.postgresql.Driver
testDbReadOnly.datasource.url=jdbc:postgresql://test.production.server:5432/testDb
testDbReadOnly.datasource.username=test
testDbReadOnly.datasource.password=test

So for example, if you need to deploy the same WAR file in different servers, you can have an external properties files with a different set of settings in each one of those servers without having to generate a different WAR for each.

As discussed, the Datasources plug ins have some issues, but none of them are things that you cannot find a workaround for. It offers great benefits and it is very easy to use. Hopefully it will get integrated as part of Grails core one day, so you can manage different datasources without having to install a plug in to accomplish it.

JavaZone Trailer: Java 4-ever

Very funny!! :D

RSA Animate - Drive: The surprising truth about what motivates us

This video is simply amazing and worth watching!

Using more than one Datasource in a Grails project

Grails is designed to work with one DataSource and one SessionFactory which are the ones used by all domain classes to execute all the operations in the database, so there is no easy way to set up a second or more datasources to allow different domain objects to connect to different databases to read and store data.

One approach is to inject a second datasource and use it with Groovy Sql to retrieve data. For this what we do is create the datasource in the Spring resources file so we can get it later using dependency injection either in a Grails service, a controller or a domain class.

My example is simple, in my database I have a table user that is related to a person table, but the person table is not in my database is in another database and I need to read some person information like the name and email to display on my system, so I am going to create a service that is going to be the one in charge of connecting to this other database using the injected dataSource. This service is then going to be injected in my domain class to set up the data I need into specific transient properties.

So the first thing we need to do is to create my new dataSource in my Spring resources file, something like this:

import org.apache.commons.dbcp.BasicDataSource

// Place your Spring DSL code here
beans = {

  dataSourcePostgresql(BasicDataSource) {
    driverClassName = "org.postgresql.Driver"
    url = "jdbc:postgresql://testserver:5432/test"
    username = "username"
    password = "password"
  }
}

Then, I need to create a Grails service class to connect to the database and get me the data I need:

import groovy.sql.Sql

class MyPostgreSQLService {

  def dataSourcePostgresql

  boolean transactional = true

  def getPersonData(long personId) {
    if (dataSourcePostgresql) {
      def sql = Sql.newInstance(dataSourcePostgresql)
      def row = sql.firstRow("select fname, lname, email from person where id = ${personId}")
      if (row) {
        return [firstName: row.fname, lastName: row.lname, email: row.email]
      }
    }
    return null
  }
}

As you can see I am connecting to the PostgreSQL database using my new dataSource using Groovy Sql and executing a direct query to the "person" table.

Finally in my domain class I inject the service and populate the properties firstName, lastName and email with what the service is providing me. I am setting these properties transient because I don't really need to store them in my database, I just need to have them for display.

class User {

  def myPostgreSQLService

  String firstName
  String lastName
  String email
  String userName
  Date createdDate
  Date lastUpdated

  static transients = ["firstName", "lastName", "email"]

  def String getFirstName() {
    getPersonData()
    return this.firstName
  }

  def void setFirstName(String name) {}

  def String getLastName() {
    getPersonData()
    return this.lastName
  }

  def void setLastName(String name) {}

  def String getEmail() {
    getPersonData()
    return this.email
  }

  def void setEmail(String email) {}

  private def getPersonData() {
    // Make the service call only if these values haven't been set yet
    if (!firstName || !lastName || !email) {
      def personData = myPostgreSQLService.getPersonData(id)
      if (personData) {
        this.firstName = personData.firstName
        this.lastName = personData.lastName
        this.email = personData.email
      }
    }
  }
  ...
}

And that is it, next time you do user.firstName, the service will query the database and get the person first name for display and that operation would be totally transparent for the controller or whenever you are using this domain class.

The downside here is that we cannot integrate this DataSource with GORM and use all its advantages, so i would say that this is an easy way to retrieve read-only data, specially if the amount of data you are retrieving is not much, you could use it as well to insert data into this other database, however if you need to read and write data I would recommend you to take a look at the Grails DataSources plug in that allows you to use multiple data sources with GORM. You can check it out here

Grails - How to execute the parameters data binding process in an integration test

When executing a request in a web application in Grails there is a process that binds the request parameters with the controller "params" object. Grails have different listeners that are notified and then execute the binding.

The class UrlMappingsFilter, which is a Servlet filter that uses the Grails UrlMappings to match and forward requests to a relevant controller and action, is the one in charge of notifying these listeners so they execute the data binding. However, this step is not executed in an integration test by default, therefore if you need to test a controller action in an integration test, you need to notify these listeners manually so the binding is done and you can access the controller "params" object inside your test. The way to do this notification is by executing the following line of code:

controller.request.getAttribute("org.codehaus.groovy.grails.WEB_REQUEST").informParameterCreationListeners()

So for example if you were using a JSON content type the listener JSONParsingParameterCreationListener would be notified and then it would do the request parameters parsing allowing you to use the controller.params object on your test.

It is important to execute this code before actually calling the controller action method that you want to test so the "params" object is available when you need it. Also, to set the controller content-type to JSON doing something like:

controller.request.content-type = "text/json"

Where controller is the instance of your controller class.