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
                 .lookup.AbstractRoutingDataSource 
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 = 
                        super.determineTargetDataSource(); 
        def env = EnvironmentHolder.getEnvironment() 
        if (env && env.passwordRequired && ds) { 
            ds.setPassword(env.password) 
        } 
        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://${env.host}:${port}/switchingDemo" 
            if (env.user) { 
                username = env.user 
            } 
            if (env.password) { 
                password = env.password 
            } 
        } 
    } 

    def dataSources = [:] 
    Environment.list().each {env -> 
        dataSources[env.id] = 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: 'uat.leebutts.com']
        environments << [id: 3, name: 'Testing', prefix: 'testing',
                         host: 'testing.leebutts.com'] 
        environments << [id: 4, name: 'Beta', prefix: 'beta',
                         host: 'beta.leebutts.com', 
                         passwordRequired: true] 
        environments << [id: 5, name: 'Prod', prefix: 'prod', 
                         host: 'db.leebutts.com', user:'grails', 
                         port: 13306, 
                         passwordRequired: true] 

        //unique id check 
        environments.each {env ->
            assert environments
                .findAll {it.id == env.id}.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) {
        contextHolder.set(environment);
    }

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

    static void clear() {
        contextHolder.remove();
    }
}

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 {it.id == 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'
                        response.setStatus(
                          HttpServletResponse.SC_UNAUTHORIZED)
                        return
                    }
                }

                //test connection
                def oldEnv = EnvironmentHolder.getEnvironment()
                EnvironmentHolder.setEnvironment env
                def ds = getDataSourceForEnv(env)
                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
                    response.setStatus(
                       HttpServletResponse.SC_UNAUTHORIZED)
                    return
                }
            } else {
                render 'No such environment'
                response.setStatus(
                        HttpServletResponse.SC_BAD_REQUEST)
            }
        } else {
            render 'Missing parameter environment'
            response.setStatus(HttpServletResponse.SC_BAD_REQUEST)
        }
    }

    private def getDataSourceForEnv(env) {
        def servletContext = ServletContextHolder.servletContext
        def ctx = servletContext
                    .getAttribute(
                         ApplicationAttributes.APPLICATION_CONTEXT)
        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]
                }
                EnvironmentHolder.setEnvironment(session.environment)
            }
        }
    }
}

The final step is to add an environment selection form to your layout so that users can choose their environment.
views/layouts/main.gsp:

<html>
<head>
    <title>Administration System</title>
    <link rel="stylesheet"
            href="${createLinkTo(dir: 'css', file: 'main.css')}"/>
    <g:layoutHead/>
    <g:javascript library="application"/>
    <g:javascript library="prototype"/>
    <script type="text/javascript">
        function refresh()
        {
            window.location.reload(false);
        }

        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>';
            errorDiv.style.display = 'block';
        }
    </script>
</head>
<body>
<div class="logo">
    <div style="margin-left:10px;">
        <h1>Current Environment: 
               ${session.environment?.name ?: 'None'}</h1>
        <form action="">
            <g:select name="environment" 
                   from="${com.leebutts.Environment.list()}" 
                      optionKey="id" optionValue="name" 
                       value="${session.environment?.id}"/>
            <g:passwordField name="password"/>
            <g:submitToRemote value="Select" controller="environment" 
             action="change" update="currentEnv"
             onLoading="loading();"
             onComplete
               ="document.getElementById('spinner').style.display='none';"
             onFailure="showError(e)"
             onSuccess="refresh()"/> <br/>
            <div class="errors" id="error" 
              style="display:none;width:500px;">

            </div>
            <img id="spinner" style="display:none;"
              src="${createLinkTo(dir: 'images', file: 'spinner.gif')}"
              alt="Spinner"/>
        </form>
    </div>
</div>
<g:layoutBody/>
</body>
</html>

The finished screen looks something like this:














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.