Grails Multitenancy with Multiple Databases (one per tenant) with 2.4.0

Question

I can't seem to find any plugin or example about Grails Multitenancy with Multiple Databases (one per tenant). I am using grails 2.4.0 . Can anyone help me?

Answer 1

In my application I needed to be able to access multiple databases and I didn't have the connection strings to the databases at startup so I couldn't preconfigure them as datasources.

The strategy I used was as follows:

1) implement an abstractroutingdatasource which stores a collection of datasources. It also registers a global closure withDataSource{}, a accepted some parameters and code block. The parameters are a name and connection information of the data source. In the withDataSource method, it either creates a new DataSource and saves it to a map using its key, or uses the saved on from the map.

2) in my client code I'd use the withDataSource closure to access whichever database I needed.

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource 
import org.springframework.context.ApplicationContextAware
import org.springframework.beans.factory.DisposableBean
import org.springframework.beans.factory.InitializingBean
import org.springframework.context.ApplicationContext 
import javax.sql.DataSource 
import org.springframework.jdbc.datasource.DriverManagerDataSource 
import org.springframework.jdbc.datasource.lookup.DataSourceLookup
import org.springframework.jdbc.datasource.SimpleDriverDataSource

class SwitchableDataSource 
              extends AbstractRoutingDataSource 
              implements ApplicationContextAware, 
                         InitializingBean,
                         DisposableBean
                         { 

  ApplicationContext applicationContext 

  Map _targetDataSources
  private ThreadLocal<String> currentDataSource = new ThreadLocal<String>() 

  void afterPropertiesSet(){
    super.afterPropertiesSet()
    // register global closure for setting the current datasource
    // ... should probably be restricted to services and controllers.
    Object.metaClass.withDataSource = this.&executeWithDataSource
  }

  public synchronized def executeWithDataSource(def params, def method){

      // if datasource doesn't exist, register new one in application context
      // set a threadlocal key for the current datasource key
      def dataSourceName = ""
      def key = params.clientId.toString()

      if( !_targetDataSources.containsKey(key) ){

        dataSourceName = "dataSource_${params.clientId}".toString()
        applicationContext.registerSingleton(dataSourceName,
              //org.apache.commons.dbcp.BasicDataSource,
              org.springframework.jdbc.datasource.DriverManagerDataSource,
              new org.springframework.beans.MutablePropertyValues(
              [
                driverClassName : 'com.mysql.jdbc.Driver',
                username : params.username.toString(),
                password : params.password.toString(),
                url : params.connectionString.toString()
              ]
            )
        )

        // add datasource to map
        _targetDataSources[key] = applicationContext.getBean(dataSourceName)
        targetDataSources = _targetDataSources
        super.afterPropertiesSet() // AbstractRoutingDataSource is lame... it converts 'targetDataSource' into 'resolvedDataSource' in afterPropertiesSet()
      }

      // set the thread local variable for the current datasource
      currentDataSource.set(params.clientId.toString())

      // call the closure that was passed in containing some data access code
      // and return whatever it returns
      return method()
  }

  void destroy(){
    // remove global closure
    Object.metaClass.withDataSource = null // may need to throw a missing method exception
  }

  protected Object determineCurrentLookupKey() { 
    (currentDataSource.get()?:"DEFAULT_DATASOURCE").toString()
  }
}

2) in client code, make calls as follows:

withDataSource( [ clientId: client.id, // client.id was my key
        connectionString: jdbcConnection.url,
        username: jdbcConnection.username,
        password: jdbcConnection.password ] ) { 

        SomeDomainObject.list()
        SomeDomainObject.delete();
    }

3) remember to register your routing datasource with spring in your resources.groovy. Notice that it assigns a default datasource so the initial datasource list isn't empty.

// Place your Spring DSL code here
beans = {

  dataSource_defaultClientDb(org.apache.commons.dbcp.BasicDataSource) { bean ->
    bean.singleton = true
    driverClassName = 'com.mysql.jdbc.Driver'
    username = '${defaultClientDB_username}'
    password = '${defaultClientDB_password}'
    url = '${defaultClientDB_url}'
  }

  dataSource_clientdb(SwitchableDataSource){ bean ->
    bean.singleton = true 
    _targetDataSources = ["DEFAULT_DATASOURCE":ref("dataSource_defaultClientDb")]
    targetDataSources = _targetDataSources
  }

  lobHandlerDetector_clientdb(org.springframework.jdbc.support.lob.DefaultLobHandler)

}

For your application, you probably don't need to use the withDataSource method as I used it - you just need to register your connections with the switchabledatasource and tell it how to lookup which datasource it should be using, for example you could assign some ID to a thread local data on each request and the switchabledatasource could use the same thread local value to determine which datasource to use.

I hope this at least gets you going in the right direction.