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
                 .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()
                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() {
        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:
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.

40 comments:

Unknown said...

wow, that's nice. Thanks for posting that!

bruce said...

Lee

Thanks, a switchable datasource is just what I am after. I seemed to have cocked something up however, as I can never get Grails to use the chosen environment - it will only ever use the one specified in DataSource.groovy. If I don't specify one in DataSource.groovy I get a startup error on not being able to find any datasource.

Am I barking up the wrong tree or just barking?

Any tips you have regards DataSource.groovy would be greatly appreciated.

Regards

Bruce

Lee said...

Hi Bruce,

the changes made to resources.groovy should be overriding any settings in DataSource.groovy. I think I have my DataSource.groovy in the default HSQLDB memory database state.

Check that the dataSource bean doesn't have any typos (it must have an upper case S in it's name)

cheers

Lee

Unknown said...

Thanks Lee. It is now working fine. I had Resources.groovy instead of resources.groovy and had Filters.groovy under src/groovy instead of under grails-app/conf. As you can see, I am still finding my way around Grails. The ability to have switchable datasources by client is critical for my application (I would imagine for a lot of others as well) so I am very grateful for your work.

Regards

Bruce

Unknown said...

I would like to know if its possible to use this technique but instead have the user specify on the fly the datasource and not have them statically set up in the environment class.

Lee said...

Hmmmm, you should be able to add new ones on the fly. You would need to dynamically add a new Spring datasource and then update the list of environments.

You would also need to update the targetDatasources list inside the SwitchableDataSource bean.

Harish Chander said...

Excellent. Precisely what I was looking for. Thanks for sharing.

-Harish

Anonymous said...

Great, this what i was looking for, i will use much of your good stuff instead of reinventing the wheel. thanks a ton.

kishora jois said...

Great worked perfectly.
Thanks for the post.

Rodrigo said...

Hi, I have a question:

why do you have a parameter in the function

private def getDataSourceForEnv(env) {
def servletContext = ServletContextHolder.servletContext
def ctx = servletContext
.getAttribute(
ApplicationAttributes.APPLICATION_CONTEXT)
return ctx.dataSource
}

you ar not using the parameter "env" ... is that ok? shouldnt you be using the parameter there?
Thanks

Rodrigo said...

Hi,

why do you have a parameter in the function getDataSourceForEnv(env) ?

It is not being used. Shouldnt it be used to cretae a neew Datasource with the environment Data?

Thanks!

Lee said...

Hi Rodrigo,

that's a mistake, the env param is not actually used. The spring datasource uses the EnvironmentHolder class to lookup the right data source for the current thread.

kishora jois said...

Hi,

I'm currently using this code. It is working perfectly fine.
Now, I need add another functionality to it. Can anyone help me on this?

The requirement is:
Currently it is possible to create datasources from resources.groovy file (which is one time). Bit, I should be able to add a datasource dynamically from controller call.

Please share your ideas.

- kishore

Lee said...

Your controller can implement ApplicationContextAware which will give you access to the Grails ApplicationContext. You should then be able to register a new BeanDefinition for the new datasource. You will need to create the BeanDefinition to match the settings in resources.groovy including getting the correct parent bean definition from the ApplicationContext. The rest of the code should all still work once the new datasource bean definition has been added to the ApplicationContext.

kishora jois said...

Thanks Lee.
I created controller which implements ApplicationContextAware.


import org.springframework.context.ApplicationContextAware
import org.springframework.context.ApplicationContext
import org.springframework.beans.BeansException

class DataSrcController implements ApplicationContextAware {

private static ApplicationContext ctx = null;
public static ApplicationContext getApplicationContext() {
return ctx;
}
public void setApplicationContext(ApplicationContext ctx) throws BeansException {
// Assign the ApplicationContext into a static method
this.ctx = ctx;
}

}


But, no idea how to create beans here.Could you please help

Lee said...

Take a look at the documentation for GenericApplicationContext. You can call registerBeanDefinition and override the definition for "dataSource"

kishora jois said...

I have uploaded switchable data source project source code [ https://docs.google.com/leaf?id=0B3j9inzR_LLhMzIyYzI3MmEtMjRjMS00Mjc1LTg2Y2YtMTI3NTM3MzA0Nzg0&hl=en_US ] and problem description [ https://docs.google.com/leaf?id=0B3j9inzR_LLhOTNhYTQ0OTYtNGM5Yi00YjIwLThkODMtNjU2YmE3YTk0NWE3&hl=en_US ].

Can I get help on this?

Thanks,
Kishore

Lee said...

Sorry Kishore, I'm really busy at the moment. Someone on the Grails User mailing list or StackOverflow might take a look.

CosmicRider said...

Lee - where in your structure would you add dialect for the times it is needed; such as:

dialect = "org.hibernate.dialect.Oracle10gDialect"

Lee said...

Hi,

are you trying to use a different dialect for each data source?

That isn't possible as the Hibernate plugin creates a single session factory and looks up the dialect as it bootstraps.

You would need to somehow create a new sessionFactory for each dataSource and switch them like we switch the dataSources.

If you just want to use the oracle dialect then the usual place in DataSource.groovy will still work.

cheers

Lee

Anonymous said...

Thanks for this post! Unfortunately, I get what seems to be an infinite loop, where Grails keeps calling SwitchableDataSource.determineTargetDataSource() and never moves on.

If I comment out the following in resources.groovy:
dataSource(SwitchableDataSource) {
targetDataSources = dataSources

}

Then I don't see the problem (but then of course the SwitchableDataSource is not used). Have you or anyone here seen this behavior before? Thanks again!

Lee said...

Is it a StackOverflowError? I havent' seen any problem like that before sorry,

Anonymous said...

Hi Lee, thanks for your response. No, I don't get any error - Grails just keeps calling SwitchableDataSource.determineTargetDataSource() every few seconds, and the application just seems to hang. I have fitted your code into our application though, so it is not an exact copy of your demo code.

However, the fact that Grails seems to be hung up on resources.groovy and doesn't even run the other application code made me wonder if this was a known issue.

Lee said...

Not an issue that I have seen before sorry,

cheers

Lee

kishora jois said...

Hi,

Is it possible to use springsecurity plugin for switching database?

1. Keep user/authentication/requestmap details in master database.

2. Login using spring security from database.

3. Once the login is successful, switch the database based on user.

Regards,

Kishore

Lee said...

I haven't used the spring security plugin before but if it uses the standard grails dataSource bean then it would work. You would need to find the right place in the filter chain to switch the dataSource though. You should be able to use a Filter to do that.

Anonymous said...

Hi Lee - thanks for supplying this great information. In regard to my previous posts, where it seemed that SwitchableDataSource.determineTargetDataSource() was getting called in an infinite loop, I was mistaken. I was using a couple of remote databases, and while the method is called many times (resulting in my integration tests timing out), it is not infinite, and when I point to local databases, your solution works perfectly. Thanks again!

alioune said...

We were trying during many days to use grails multi tenant plugin in singleTenant mode without success.
We will try your solution.
Is it possible to make it a plugin?

Lee said...

Hi,
I don't have any free time at the moment to do a plugin, sorry!

Anonymous said...

Hi Lee - we discovered during load testing that this solution (which does work great otherwise) does not implement database connection pooling. Is there any particular reason why the DriverManagerDataSource must be used instead of a pooling one?

I substituted BasicDataSource for it in the code (making sure the maxActive, initialSize, etc. properties were read into the bean), but Hibernate is unable to make a connection after Grails starts up. I just wanted to see if there was a compelling reason not to use a pooling datasource before I go too deep into the rabbit hole. Thanks again!

Lee said...

Can't think of any reason why a pooled data source shouldn't work. What kind of errors are you getting?

Anonymous said...

Thanks for your reply, Lee. I get the following exception after the connection attempt times out:

org.hibernate.util.JDBCExceptionReporter Cannot get a connection, pool error Timeout waiting for idle object

Nothing else interesting is logged, even when I turn on debug for all classes. MySQL general logging, interestingly, shows that grails is connecting to the database during startup, but nothing further is logged afterward.

Lee said...

Only suggestion I have is to try and debug through the DBCP code and see where it's getting too. Maybe there are some bean properties missing/wrong?

vaibhav said...

after integrating this code in my application its not running or not giving an error

please tell me what should be required in resources.groovy in spring configuration

Lee said...

I'm afraid I can't help unless there is an error message being displayed. resources.groovy should be as per the post.

vaibhav said...

is there any other way to switch database dynamically using built in datasource.groovy?

Please, let me know what are the steps required to this example.

Lee said...

There is an alternate solution at the start of this mailing list thread: http://grails.1312388.n4.nabble.com/Changing-Datasources-on-the-fly-using-Datasources-plugin-td1370176.html

Unknown said...

hi
I try to use your solution that works to get data from different bases but not work to create or update data.
You sais "You would need to somehow create a new sessionFactory for each dataSource and switch them like we switch the dataSources."
May i ask you to give us an exmple to do that.
thanks a lot for your work.

Lee said...

Hi Seb,

create and update should just work without needing multiple session factories. My comment was relating to the need for each dasta source to use a different dialect (e.g. switch between MySql and Oracle).

What error message are you encountering when creating/updating?

Unknown said...

Hi Lee
Thanks a lot for your reply, I had an error which produces the following traces :

ERROR util.JDBCExceptionReporter - ERREUR: droit refusé pour la séquence hibernate_sequence
| Error 2012-11-17 21:51:55,405 [http-bio-8080-exec-10] ERROR errors.GrailsExceptionResolver - PSQLException occurred when processing request: [GET] /testGrails/test2
ERREUR: droit refusé pour la séquence hibernate_sequence. Stacktrace follows:
Message: ERREUR: droit refusé pour la séquence hibernate_sequence


droit refusé pour la séquence hibernate_sequence => right refused for sequence hibernate_sequence

That's why I thought it was a session problem. Each database has a different hiberante_sequence index.

Besides, I commented my whole file datasource.groovy, there is no set dialect for my datasources.
Could you help me or guide me in my research?

cheers