Grails dynamic database connections?

Question

Where I'm working, we have multiple database we need to be able to query. Some of them are predefined and we're using Datasources to access. Others are named after the customer id #. For instance _2. We have hundreds of customers and some customers can pose as other customers and depending on which customer is using the interface at that time, we would need to connect to their specific database for their specific data.

I've read this is not possible with Grails but I'm having a hard time believe that there is no way to do this at all. Is there some possible way to write a plugin or mess with hibernate that would allow a dynamic connection like this? Has anyone come across this issue yet and what have you done to deal with it?

Any information will be helpful. We've hit a dead end with our development due to this crazy database design!

Answer 1

The DataSources Plugin might come in handy. Not sure how you would want to shard with this, but You'd definitely be a step closer to what you want to achieve.

Answer 2

I think it's possible, some implementation ideas:

I'd use DataSources Plugin + a custom DataSource class. You can use the DataSources Plugin to separate domain classes pointing to the master database and the customer specific database.

For routing to the customer database, you could create your own DataSource class. I'd recommend extending the custom class from org.springframework.jdbc.datasource.DelegatingDataSource . The custom DataSource decides what target DataSource to use. You should make a pool of datasources, you could use commons.apache.org/pool/ to implement the pool. The pool of datasource can take care of closing the datasource (connection pool) after some inactivity time.

You can use a ThreadLocal to tell the "pool of datasources" pool which customer is logged in and that information can be used to choose (or create on the fly) the correct target datasource.

You'll have to turn off all Hibernate caching for customer specific classes unless you implement some specific Hibernate cache implementation (that should be possible using the same "routing idea" as in the DataSource).

You can override the default dataSource beans in your resources.groovy with your custom versions, see DataSource Plugin faq (grails.org/plugin/datasources#faqTab).

I'd recommend Tomcat JDBC Pool (JDBC-Pool Grails plugin: grails.org/plugin/jdbc-pool) as the DataSource / database pool implementation. BoneCP (jolbox.com/) looks promising too, but I haven't tested it.

Answer 3

Actually MultiTenant plugin's "SingleTenant" mode is what you are looking for.