Wednesday, July 16, 2008

Switchable Grails DataSource

As part of my day job I needed to write an administration app that could be easily pointed at multiple environments. To achieve this I used a technique I found a while ago based on Spring's AbstractRoutingDataSource. It basically works by using a facade to switch the connection between one to many child data sources. First off SwitchableDataSource.groovy
package com.leebutts 
import org.springframework.jdbc.datasource
import com.leebutts.Environment 
import org.springframework.context.ApplicationContextAware 
import org.springframework.context.ApplicationContext 
import javax.sql.DataSource 
import org.springframework.jdbc.datasource.DriverManagerDataSource 

class SwitchableDataSource extends AbstractRoutingDataSource 
    implements ApplicationContextAware { 
    def applicationContext 
    public void setApplicationContext(ApplicationContext 
                                           applicationContext) { 
        this.applicationContext = applicationContext 

    protected DataSource determineTargetDataSource() { 
        DriverManagerDataSource ds = 
        def env = EnvironmentHolder.getEnvironment() 
        if (env && env.passwordRequired && ds) { 
        return ds 

    protected Object determineCurrentLookupKey() { 
        def env = EnvironmentHolder.getEnvironment() 
        return env?.id ?: Environment.list()[0]?.id 
SwitchableDataSource is the facade that delegates to the list of standard DriverManager data sources. They are defined in grails-app/conf/spring/resources.groovy using the environment settings from the Environment class. Here's my spring config in resources.groovy:
import com.leebutts.SwitchableDataSource 
import com.leebutts.Environment 
import org.springframework.jdbc.datasource.DriverManagerDataSource 

beans = { 
    parentDataSource(DriverManagerDataSource) {
        bean -> bean.'abstract' = true; 
        driverClassName = 'com.mysql.jdbc.Driver' 
        username = "root" 
    Environment.list().each {env -> 
        "${env.prefix}DataSource"(DriverManagerDataSource) {bean -> 
            bean.parent = parentDataSource 
            bean.scope = 'prototype' 
            def port = env.port ?: 3306 
            url = "jdbc:mysql://${}:${port}/switchingDemo" 
            if (env.user) { 
                username = env.user 
            if (env.password) { 
                password = env.password 

    def dataSources = [:] 
    Environment.list().each {env -> 
        dataSources[] = ref(env.prefix + 'DataSource') 

    dataSource(SwitchableDataSource) { 
        targetDataSources = dataSources 
Environment currently uses a static list to hold the environment config. This could be done better via a reloadable properties file or by adding a view/controller to modify the environment settings on the fly. Environment.groovy:
package com.leebutts

class Environment {

    static environments = []

    static {
        environments << [id: 1, name: 'local', prefix: 'local', 
                          host: 'localhost']
        environments << [id: 2, name: 'UAT', prefix: 'uat',
                            host: '']
        environments << [id: 3, name: 'Testing', prefix: 'testing',
                         host: ''] 
        environments << [id: 4, name: 'Beta', prefix: 'beta',
                         host: '', 
                         passwordRequired: true] 
        environments << [id: 5, name: 'Prod', prefix: 'prod', 
                         host: '', user:'grails', 
                         port: 13306, 
                         passwordRequired: true] 

        //unique id check 
        environments.each {env ->
            assert environments
                .findAll { ==}.size() == 1}

    static list() {
        return environments
SwitchableDataSource needs a way to determine which environment the current request wishes to use. It does this via a ThreadLocal holder EnvironmentHolder.groovy:
package com.leebutts

class EnvironmentHolder {

    private static final ThreadLocal contextHolder
                                = new ThreadLocal();

    static void setEnvironment(Map environment) {

    static getEnvironment() {
        return contextHolder.get();

    static void clear() {
Now that the infrastructure is in place, the next step is to add a controller to allow users to select the environment they wish to use and a filter to set a default environment if one has not yet been chosen. The controller is called via ajax (in my application) but could be used as a standard controller just as easily. It looks up the Environment based on an ID and then tests the connection to make sure the password supplied is valid (if a password is required as specified in the environment config). If a password is being used it takes a copy of the environment config map, adds the password and stores it in the session so that the user doesn't have to re-enter the password on every screen and so that only this user has access to the password. EnvironmentController.groovy:
import com.leebutts.Environment
import com.leebutts.EnvironmentHolder
import javax.servlet.http.HttpServletResponse
import org.codehaus.groovy.grails.commons.ApplicationAttributes
import org.codehaus.groovy.grails.web.context.ServletContextHolder

class EnvironmentController {

    def change = {
        if (params.environment) {
            def env = Environment.list()
                     .find { == new Integer(params.environment)}
            if (env) {
                if (env.passwordRequired) {
                    if (params.password) {
                        //take a copy and add a pword
                        env = addPasswordToEnvCopy(params, env)
                    } else {
                        render 'PASSWORD REQUIRED'

                //test connection
                def oldEnv = EnvironmentHolder.getEnvironment()
                EnvironmentHolder.setEnvironment env
                def ds = getDataSourceForEnv()
                try {
                    def con = ds.getConnection()
                    session.environment = env
                    render 'Environment change complete.'
                } catch (e) {
                    EnvironmentHolder.setEnvironment oldEnv
                    render 'Unable to connect to database: '
                              + e.message
            } else {
                render 'No such environment'
        } else {
            render 'Missing parameter environment'

    private def getDataSourceForEnv() {
        def servletContext = ServletContextHolder.servletContext
        def ctx = servletContext
        return ctx.dataSource

    private Map addPasswordToEnvCopy(Map params, env) {
        def myEnv = [:]
        env.each {key, val ->
            myEnv[key] = val
        myEnv.password = params.password
        return myEnv
As mentioned, there is also a simple filter for defaulting the environment to the first one in the list if one has not been selected and storing it in the ThreadLocal holder. Filters.groovy:
import com.leebutts.EnvironmentHolder
import com.leebutts.Environment

class Filters {
    def filters = {
        all(uri: '/**') {
            before = {
                if (!session.environment) {
                    session.environment = Environment.list()[0]
The final step is to add an environment selection form to your layout so that users can choose their environment. views/layouts/main.gsp:
    <title>Administration System</title>
    <link rel="stylesheet"
            href="${createLinkTo(dir: 'css', file: 'main.css')}"/>
    <g:javascript library="application"/>
    <g:javascript library="prototype"/>
    <script type="text/javascript">
        function refresh()

        function loading() {
            document.getElementById('spinner').style.display = 'inline';
            document.getElementById('error').style.display = 'none';
        function showError(e) {
            var errorDiv = document.getElementById('error')
            errorDiv.innerHTML = '<ul><li>'
                     + e.responseText + '</li></ul>';
   = 'block';
<div class="logo">
    <div style="margin-left:10px;">
        <h1>Current Environment: 
               ${session.environment?.name ?: 'None'}</h1>
        <form action="">
            <g:select name="environment" 
                      optionKey="id" optionValue="name" 
            <g:passwordField name="password"/>
            <g:submitToRemote value="Select" controller="environment" 
             action="change" update="currentEnv"
             onSuccess="refresh()"/> <br/>
            <div class="errors" id="error" 

            <img id="spinner" style="display:none;"
              src="${createLinkTo(dir: 'images', file: 'spinner.gif')}"
The finished screen looks something like this:
Photobucket - Video and Image Hosting

Photobucket - Video and Image Hosting

Photobucket - Video and Image Hosting

Photobucket - Video and Image Hosting

As this is an internal admin application it has not been tested as thoroughly as standard prod code so please do so before using it in a "live" application. Please post any corrections/bug fixes.
Post a Comment