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