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.