2

We are developing a Grails web application, where different users (customers) need to be pointed at different databases containing only their organization's data. Unfortunately, the separated databases are a requirement, and we are being asked to be able to have only 1 web application for everybody.

However, Grails expects only a single datasource pool connecting to one database.

We want to be able switch database connections, per session, based on the user that is logged in, where the different connections are read in from properties files during the BootStrap init().

So far, we have been unable to find a solution that does not seem to have race conditions, there is no plugin we can find, and it doesn't seem to be a popular issue.

Our most promising was creating a custom dynamic data source, set up in Bootstrap to define a map of organization->dataSource, and utilizing a closure defined in Bootstrap to find the appropriate dataSource before GORM behavior, but this seems to cause race condition when there is latency.

Does anyone have an idea how this switching can legitimately be performed?

Thanks

Adam Vainder
  • 65
  • 6

2 Answers2

1

Considering Grails is built upon Spring your best bet is to develop your own resolvable datasource.

  1. Dynamic datasource routing
  2. Example of datasource routing
Community
  • 1
  • 1
Joshua Moore
  • 24,706
  • 6
  • 50
  • 73
  • Hi Joshua, thanks I will pass this on and we will take a look at it. If this kind of solution can integrate with GORM then it may work out. – Adam Vainder Nov 25 '13 at 22:46
  • We used a very similar approach with Grails 1.2.x and the only issues we had with GORM was having to do database migrations ourselves because of the way we were resolving the datasource (similar to your per-user/company). – Joshua Moore Nov 26 '13 at 11:47
  • Hmmm, ok. I was told by another developer working on the issue that he had tried a similar approach but that it had not worked. I will personally try to adapt this, given time. – Adam Vainder Nov 26 '13 at 15:03
  • So finally does it work for you? I will need to do something similar – Houcem Berrayana Jan 08 '14 at 17:24
0

It's not clear in your question if you're deploying your application once, and trying to configure the DataSource used by User, or if you just want to configure by deployment.

If it's just per deployment, Grails allow you to externalize the configuration. You can set this to use a file in the classpath or in a static location.

  • Hi Sergio. We do not want to configure by deployment. We want to have multiple datasources within the same single deployment, and to be able to switch to any of them dynamically within a web service call, for the database associated to the organization of the user that is making the request. The application is JS/GSP speaking to Grails-based web services, and the web services will know the user information including what organization they belong to. – Adam Vainder Nov 25 '13 at 22:26
  • Hmm this may be something I was unaware we could do...so if we have 2 data sources "customer1" and "customer2", we could call ZipCode."customer2".get(42) and it would use customer2's data source? Am I understanding that correctly? – Adam Vainder Nov 26 '13 at 15:08
  • Yes, you can call ZipCode.customer2.get(). " is needed when you're interpreting a variable. –  Nov 26 '13 at 15:09
  • Interesting. This might be the simplest approach, if we can create the data sources dynamically. Let me try it out and see if we can get it working. – Adam Vainder Nov 26 '13 at 15:13
  • Sergio, assuming I have a domain object Emp, two configured data sources, and pass a String down to the method that actually calls the domain object's method named dataSourceName, when I try Emp."$dataSourceName".list() I get a compile error "Fatal error occurred applying query transformations: null". When I try Emp.'$dataSourceName'.list() it compiles but I get an error at runtime that there is no property $dataSourceName for class Emp. I also tried with {} around dataSourceName with similar results. What am I doing incorrectly? – Adam Vainder Nov 26 '13 at 16:24
  • `def serviceMethod2() { Repo r = new Repo(); return r.test2('dev') }` – Adam Vainder Nov 26 '13 at 16:25
  • `public List test2(String dataSourceName){ def users = Emp.'$dataSourceName'.list() return users }` – Adam Vainder Nov 26 '13 at 16:26
  • Never mind that...got the test working. Interestingly, per [http://stackoverflow.com/questions/10534238/grails-2-how-to-dynamically-call-multiple-datasources] I had to use `def e=Emp.class e."$dataSourceName".list()' to make it work! I am going to see how much farther we can take it, thank you for the input. – Adam Vainder Nov 26 '13 at 17:08