A Grails Plugin for Multiple DataSources
There have been a few requests on the Grails user mailing list about using multiple data sources in a Grails app, i.e. some domain classes use one data source and database and others use another. Grails doesn’t directly support this – there’s only one DataSource and one SessionFactory, and all domain classes use them. But it turns out it’s not that difficult to support this (and it doesn’t involve too many ugly hacks …)
There are some implications of the approach I took. This doesn’t provide XA transactions, 2PC, etc. It’s just a partitioning of classes between two or more datasources. The way it works is to run after the HibernateGrailsPlugin and DomainClassPlugin have done their work. Then it uses a configuration defined in grails-app/conf/Datasources.groovy and creates one or more extra DataSource, SessionFactory, TransactionManager, etc. and re-runs the HibernateGrailsPlugin‘s doWithDynamicMethods closure for the appropriate subset of domain classes. This way when you call a magic GORM method (e.g. list(), get(), findByNameAndDate(), etc.) it will use the correct underlying datasource. Any domain class not defined as using a secondary datasource will use the ‘core’ datasource defined in DataSource.groovy.
Another issue is that all domain classes stay defined in the core datasource/SessionFactory – the existing behavior isn’t changed, other than redefining the metaclass methods to use another datasource. The only effect of this is that if you use dsCreate = ‘create-drop’ or ‘create’ or ‘update’ for the core datasource, all tables will be created in the core database even though some won’t be used.
Datasources DSL
The DSL used in Datasources.groovy is very similar to the format of DataSource.groovy. One difference is that the ‘hibernate’ section is inside the ‘datasource’ section, and there are a few extra attributes.
| Name | Type | Required | Description |
|---|---|---|---|
| name | String | yes | datasource name, used as a Spring bean suffix, e.g. ‘ds2’ |
| readOnly | boolean | no, defaults to false | if true, the datasource and corresponding transactional services will be read-only |
| driverClassName | String | yes | same as in DataSource |
| url | String | yes | same as in DataSource |
| username | String | no | same as in DataSource |
| password | String | no | same as in DataSource |
| dbCreate | String | yes | same as in DataSource |
| dialect | String or Class | yes (no autodetect) | same as in DataSource |
| jndiName | String | no | same as in DataSource |
| pooled | boolean | no, defaults to false | same as in DataSource |
| loggingSql | boolean | no, defaults to false | same as in DataSource |
| logSql | boolean | no, defaults to false | same as in DataSource |
| environments | List<String> | no, defaults to [‘development’, ‘test’, ‘production’] | list of environments this DataSource should be active in |
| domainClasses | List<String> or List<Class> | yes | the domain classes that should use this DataSource |
| services | List<String> | no | short names of the services that should use this DataSource (same as Spring bean without ‘Service’, e.g. ‘user’ for UserService) |
See the sample app (link below) for a usage example.
OpenSessionInView
An OpenSessionInViewInterceptor is defined for each datasource, so the features that it provides are available to all domain classes. For example you can load a domain instance and set a property, and it will be detected as dirty and pushed to the database. Also, lazy loaded collections will load since there’s an active session available.
Further, if you save, create, load, etc. domain instances from multiple datasources in one controller method, all will work fine.
Transactional Services
By default, any service defined as transactional will use the core datasource. If you want a service to use a specific datasource, add its name to the ‘services’ attribute for a datasource definition. If there’s no one datasource for a particular service, you can still define programmatic transactions using withTransaction on any domain class for the appropriate datasource for each method or code block.
HibernateTemplate
I can’t think of non-contrived reasons to do so, but it’s possible to use a domain class in two or more datasources. The problem here is that the metaclass methods will end up mapped to the last declared datasource, so there’s no way to use GORM for the other datasource(s). However you can use Spring’s HibernateTemplate yourself – it has a lot of the functionality of GORM (GORM uses it under the hood). You can use the convenience method DatasourcesUtils.newHibernateTemplate(String dsName) to create a HibernateTemplate configured with the SessionFactory for the named datasource.
Usage
You might want to use this plugin even if you have only one database. Since you can define a datasource as being read-only and point read-only domain classes at it, your prevent yourself from accidentally creating, deleting, or updating instances.
I’ve created a basic (and rather contrived) test application. It has three datasources and five domain classes:
Country,StateLibrary,BookVisit
Country and State are just lookup tables, so they use a read-only datasource. Visit has a weak foreign key to Library, but this is not enforced since they’re stored in two databases. It’s the responsibility of the application to ensure that the library id for each visit is valid. This is to simulate having a second database for auditing.
To test the app:
- Create the databases using the scripts (ddl_core.sql, ddl2.sql, and ddl3.sql)
- run ‘grails run-app’
- Create a new Library at http://localhost:8080/ds_test/library/create
- Create a new Book at http://localhost:8080/ds_test/book/create
- check the database to ensure they ended up in the correct database
- Create a new Visit at http://localhost:8080/ds_test/visit/create
- check the database to ensure it ended up in the correct database
- edit a State at http://localhost:8080/ds_test/state/edit/1, save, and note that change didn’t take effect due to read-only datasource
- create a State at http://localhost:8080/ds_test/state/create, save you should see the error page due to read-only datasource
- test transaction failure at http://localhost:8080/ds_test/transactionTest/fail – you should see the error page and if you check the database nothing should have been inserted
- test transaction success at http://localhost:8080/ds_test/transactionTest/succeed – you should see a simple success message and if you check the database the inserts should have succeeded
If you want to manually manage the tables in each database instead of letting Hibernate do it for you, you can use the schema-export script from here to capture the DDL for all the tables. Then you can execute the DDL statements for each database separately.
The plugin is in the Grails repository, so to use it just run “grails install-plugin datasources”. You can download the test application here.
Hi Burt,
Is there a possibility to auto-inject datasources defined in DataSources.groovy?
I would like to use something like this for a read-only database access without domain classes
class MyFancySqlController {
def ds2 // like Spring-Bean? “ds2” is auto-injected
def list = {
sql = “select * from test_table”
def db = new Sql(ds2)
def result = db.rows(sql)
[ result: result ]
}
}
This will end up in a null pointer exception because ds2 has not been auto-injected as excpected by me
regards rene
@rene the bean name would be ‘dataSource_ds2’, so use ‘def dataSource_ds2’. You can also use DatasourcesUtils.getDataSource(‘ds2’)
Hi Burt,
thank you for your quick reply. It works perfectly!
Greetings from Switzerland
rene
Hi Burt,
thank you for your quick reply. It works just perfectly!
Greetings from Switzerland
rene
Hello Burt,
Thanks for your blog, amazing source of grails info.
I’m using your DataSources plugin with an application built using Grails and Mule ESB.
I’ve used a technique similar to the BackgroundThread and Executor Plugins to ensure a hibernate session is bound to the various threads that mule creates to perform it’s tasks. i.e.:
http://github.com/basejump/grails-executor/blob/master/src/groovy/grails/plugin/executor/SessionBinderUtils.groovy
However, I’m running into an issue where UniqueConstraint throws a null pointer exception at line 141 – i.e. the application context is null.
I believe that I’m not setting up the threads correctly so that they will work with your plugin.
It is enough to bind sessions created with the original grails session or do you think I need to do more?
Tom
Hi great plug-in. What i need also is to dynamically change a jndi name from a user selecting a system from a gsp page that is associated with a jndi name.
Would i try to amend the datasources URL dynamically?
Hi Burt
nice plugin
btw i have a question
You said that
‘There are some implications of the approach I took. This doesn’t provide XA transactions, 2PC, etc’
So how to rollback the first datasource sql statement if the second one throwx exception?
regards
Hi Burt,
i ve used your plugin in my app. But i have following problems:
1. I have 2 Datasources, they named db1 and db2.
2. I defined db1’s config in Datasource.groovy and db2 in Datasources.groovy.
3. I have 3 Domain Classes, they named DM1.groovy, DM2.groovy and DM3.groovy. DM1.groovy and DM2.groovy are from db1. And DM3.groovy from db2
4. I started my app and DM3.groovy is created in db1. That is my problem.
My Datasources.groovy:
datasources = {
datasource(name: ‘db2’) {
domainClasses([DM3])
readOnly(true)
driverClassName(‘com.mysql.jdbc.Driver’)
url(‘jdbc:mysql://localhost:3306/abc?zeroDateTimeBehavior=convertToNull’)
username(‘u1’)
password(‘p1’)
dbCreate(‘update’)
logSql(true)
//dialect(org.hibernate.dialect.MySQL5InnoDBDialect)
hibernate {
cache {
use_second_level_cache(false)
use_query_cache(false)
}
}
}
}
thx for your help!
Hi,
did you figure it out?
It has another side effect. I can list the objects from the second db but, since objects point to the first db have references to objects in the second it throws “Cannot add or update a child row: a foreign key constraint fails”
My app has a Lead domain object in the main db and it points to Region in another one.
Is there a way to get it to work?
Eduardo
Hi all,
I would like to know if there is a way to separate the writes and the
reads in the database, in the same class.
How the plugin handle the situation when in the same clase, we want to
write in one datasource, and the reads directed to another, if possible.
Thanks for your help,
From looking at the code it looks like you are using HibernateTransactionManager. Could you make this configurable so we can use something like JOTM instead to give us XA ability?
I’m in the process of merging the plugin into Grails core for 1.4 and in the process will add XA support.
Hi Burt,
I have deployed an application which have 10 domains. My application have many customers. And I do not want to use same database for all customers.
So, I will create different database (with same domains) as many number of customers.
I don’t think I can use datasources plugin for this.
Can you suggest any solution for this?
Regards,
kishore
Hi,
We are having the same problem as Tom.
We are using the Datasources plugin with a Grails 1.3.7 application that is using Executors to distribute the processing and DB save of about 2000 items using 10 threads. Each of the items gets its own job using executor.submit where executor is an ExecutorService. For several hundred of the items, the Grails library file UniqueConstraint.java throws a null pointer exception at line 141 – i.e., the application context is null. The other items are processed without problems.
The DB save in this case is to our main DB so the plugin is not required for the save. If we remove the plugin from our project the problem goes away. Or if we keep the plugin but stop using threads, the problem goes away. We’d like to keep the plugin and keep using threads, though.
Any suggestions?
Thanks!
David
Hi Burt, I’m using the datasources plugin in my grails 1.3.7 application, my problem is in the domain classes wich uses the default datasource (the datasource defined in Datasource.groovy), when I try to call a hql query like Domainclasss.findAll(“from DomainCalss d where d.property = :mapEntry”,[mapEntry: someObject]) the application crashes and I get an exception like “org.springframework.orm.hibernate3.HibernateQueryException: DomainClass is not mapped …” when I disable the datasources defined in Datasources.groovy the application work’s fine. I don’t know why the app is not mapping the domain classes only when I use the hql querys, by the othre hand if I use dynamyc finders like DomainClass.findAllByProperty(someObject) it work’s fine and even if I use deprecated querys like DomainClass.findAll(“from DomainClass d where d = ?”,someObject) it works fine. Any Idea?
I’m working with the Datasources Plugin in a Grails application so I can access a mySQL and a Microsoft SQL Server database in this application and it’s working perfectly, you really made a great job here.
However, the server where the SQL Server database is located can be, sometimes, down. So what I have to do is somehow make my application be independent of it, I mean, even if the server is down, the application has to start. In order to make this I simply did a try-catch statement:
datasources = {
try{
datasource(name:’tri’){
readOnly(true)
driverClassName(‘net.sourceforge.jtds.jdbc.Driver’)
url(‘jdbc:jtds:sqlserver://:1433/TRI’)
username(‘XX’)
password(‘XX’)
logSql(true)
hibernate {
cache {
use_second_level_cache(false)
use_query_cache(false)
}
}
}
}
catch(Exception e){
println(“no connection”)
}
}
The problem is I can only get the application running if the SQL server is up. How can I solve this problem? Is there a way to start the program even if the datasource inside the Datasources.groovy is down?
Hi,
I came across the plugin, but I’m getting the following exception (see below) when I use it. It looks like it’s having problems with two transaction managers.
Any help much appreciated.
Paul
Running Grails application..
methods for plugin [hibernate:1.4.0.M1]: No unique bean of type [org.springframework.orm.hibernate3.HibernateTransactionManager] is defined: expected single bean but found 2: transactionManager,transactionManager_additional_datasource
org.springframework.beans.factory.NoSuchBeanDefinitionException: No unique bean of type [org.springframework.orm.hibernate3.HibernateTransactionManager] is defined: expected single bean but found 2: transactionManager,transactionManager_additional_datasource
The Datasources plugin doesn’t work in 1.4 or 2.0, but it’s been merged into core for 2.0M1, so you can switch to that since you’re already at 1.4M1.
Hi,
I see there is a Datasources plugin patch from a user which might fix the problem Tom and I reported above when using Datasources with Executor.
http://jira.grails.org/browse/GPDATASOURCES-26
I haven’t tested this plugin and I don’t know what the performance implications of the synchronization are.
Hi, great work with the plugin.
I need to develop a workaround to get the users from one application to another, just to read and authenticate, but I don’t wanna replicate the entire db.
The application A is at a database, and B application at another, but not at the same host.
Is there a way to do this using this plugin? Or do you know or think of some solution to this particular case?
I’m newbie at grails.
Hi Burt,
Great job with the plugin!
Our application will have 1 master write/read database, and several read-only databases (which get replicated from the master). For our services when we do any write methods we want to use the write/read datasource with our transaction, otherwise use the read-only datasource. I’m hoping we can achieve this with your plugin – do you have any suggestions? Guess one way is we could have separate read and write services – but would be nice to have them all in one service.
Still pretty new to grails so sorry for any stupid questions.
Thanks!
I have an easy question. How can I set the properties for pool size etc? I’ve tried a few things and can’t stumble across how to hook into it despite all my googling.
rick
I learned today that Grails 2.0 has built-in multiple data source support which hopefully fixes the problem Tom and I reported above. That’s good because I’m wary of the potential performance implications of the patch I linked to.
Right – although the multiple datasources support in 2.0 is pretty much just a port of the plugin into the core, the unique constraint handling is done differently and won’t be affected by that issue.
Hi Burt,
I have a requirement where I have to add data sources dynamically and then query them. As per my understanding DataSource plugin only works for static data sources configured in your data source files. Is there a way to add the data sources dynamically and use the GORM methods on the domain classes?