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
,State
Library
,Book
Visit
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.
[…] Burt Beckwith discusses his new plugin – one I’ve been needing for a while! […]
i have a error when i use it..
No signature of method: java.util.ArrayList.push() is applicable for argument types: (java.lang.String) values: {“datasource”}
datasources = {
datasource(name: ‘ds2’) {
domainClasses([OITM])
readOnly(true)
driverClassName(‘net.sourceforge.jtds.jdbc.Driver’)
url(‘jdbc:jtds:sqlserver://localhost:1433/prueba_desarrollo’)
username(‘sa’)
password(’12’)
dbCreate(‘update’)
logSql(true)
dialect(org.hibernate.dialect.MySQL5InnoDBDialect)
hibernate {
cache {
use_second_level_cache(false)
use_query_cache(false)
}
}
}
}
i Have an error when i use it.
No signature of method: java.util.ArrayList.push() is applicable for argument types: (java.lang.String) values: {“datasource”}
datasources = {
datasource(name: ‘ds2’) {
domainClasses([OITM])
readOnly(true)
driverClassName(‘net.sourceforge.jtds.jdbc.Driver’)
url(‘jdbc:jtds:sqlserver://10.194.18.173:1433/prueba_desarrollo’)
username(‘sa’)
password(’12’)
dbCreate(‘update’)
logSql(true)
dialect(org.hibernate.dialect.MySQL5InnoDBDialect)
hibernate {
cache {
use_second_level_cache(false)
use_query_cache(false)
}
}
}
}
Same error as ladislao.
No signature of method: java.util.ArrayList.push() is applicable for argument types: (java.lang.String) values: {”datasource”}
I m also getting the same error.
No signature of method: java.util.ArrayList.push() is applicable for argument types: (java.lang.String) values: {”datasource”}
That’s weird, it’s just the regular push() and pop() that are in the Groovy JDK for the List class.
I’ll keep looking at it, but meanwhile just replace the 3 instances of “_stack.push name” with “_stack << name" and the 1 instance of "_stack.pop()" with "_stack.remove _stack[-1]".
Oh! Doesn’t worked after changing these variables too.
I hope my Datasources.groovy is ok-
datasources = {
datasource(name: ‘dataSourceivr’) {
domainClasses([Clients])
readOnly(true)
driverClassName(‘net.sourceforge.jtds.jdbc.Driver’)
url(‘jdbc:jtds:sqlserver://10.0.0.148:1433;DatabaseName=ivr_guardian;tds=8.0;lastupdatecount=false’)
username(‘gdxwebuser’)
password(‘n0\$bugs’)
dbCreate(‘update’)
hibernate {
cache {
use_second_level_cache(false)
use_query_cache(false)
}
}
}
Thanks
Sadhna
the previous error it was a version error on grails thanks your plug-in its excellent
[…] Datasources Plugin released […]
It would be good to have a variation on this plugin that could perform the same basic GORM operations on multiple DBs _in_parallel_ (for example, MYSQL, PostgreSQL, and [soon] JCR).
Then multiple DBs could all be exercised, tested, and performance-checked at the same time.
I am having trouble locating this plugin on grails.org, I know I will kick myself when someone shows me where the link to this plugin is located. I do see it under the Grails target ‘list-plugins’. I just can’t find the docs on grails.org.
Keith – sorry about that, I still need to document this plugin (and 2 others) and add them to the official list.
No problem, as long as I’m not overlooking something. Thanks for creating such a great plugin.
Hi, great plugin, just wondered if it was possible to configure DataSources.groovy to use different connection settings (url, username, password) for each environment as in DataSource.groovy?
Hi, Thanks for writing such a usefull and great plugin!
Is is possible to have different settings in different environments, as it is possible with default db in DataSource.groovy ?
@DLC,@Andi – You can restrict a datasource to a specific environment with the ‘environments’ attribute. By default a datasource applies to all 3 but you can change that if you want.
i could not make it work with a second environment. only the first datasource() { … } section is beeing used, but only if i am in development environment. if i create a web archive the production section should be used, but it isn’t.
but if i put the second section (for production) at the beginning of datasources (before the one for development) and create the web archive again, everything works fine.
am i missing something?
here is my Datasources.groovy that i have used:
datasources = {
datasource(name: ‘sql-ledger-dev’) {
domainClasses([sql_ledger.Customer, sql_ledger.Ar])
driverClassName(‘org.postgresql.Driver’)
environments([‘development’])
url(‘jdbc:postgresql://192.168.1.2/livenet_dev’)
username(‘…’)
password(‘…’)
readOnly(true)
dbCreate(‘update’)
logSql(true)
dialect(‘org.hibernate.dialect.PostgreSQLDialect’)
hibernate {
cache {
use_second_level_cache(false)
use_query_cache(false)
}
}
}
datasource(name: ‘sql-ledger-prod’) {
domainClasses([sql_ledger.Customer, sql_ledger.Ar])
driverClassName(‘org.postgresql.Driver’)
environments([‘production’])
url(‘jdbc:postgresql://192.168.1.3/livenet_prod’)
username(‘…’)
password(‘…’)
readOnly(true)
dbCreate(‘update’)
logSql(false)
dialect(‘org.hibernate.dialect.PostgreSQLDialect’)
hibernate {
cache {
use_second_level_cache(false)
use_query_cache(false)
}
}
}
}
[…] mapping (GORM) – only allowing connections to a single datasource – has been resolved with a new plugin from Burt Beckwith. This feature which allows application architects to design systems that can […]
Great plugin, Burt, especially the read-only param.
Howeer, the domain classes that use the secondary datasource don’t appear to obey the information in static mapping. Any ideas on how to get this working would be appreciated 🙂
Following code fragment in the DatasourcesGrailsPlugin.groovy would cause the plugin skip all processes if the first datasource isn’t declared to be used in current environment:
if (!ds.environments.contains(GrailsUtil.environment)) {
return
}
For example, if I declare the first datasource only be used in development environment, and the second datasource for test environment.
If I run my application in test mode, the plugin would skip to check the second datasource definition cause the it is already returned by above checking statement.
I found the solution to the problem I outlined above in my post from Oct. 14. The bug is in the DatasourcesGrailsPlugin class in the two closures doWithSpring and doWithoutDynamicMethodes:
The code to check whether a dataSource for the current environment exists, just returns if the first dataSource does not “belong” to the current environment. This behaviour never verifys the other dataSources that may have been defined, like i have, in the example avove.
I changed this bit of code and introduced a private methode, since its used in both methodes: doWith…
replaced:
for (ds in dataSources) {
if (!ds.environments.contains(GrailsUtil.environment)) {
return
}
….
by:
def ds = getCurrentDataSource(dataSources)
if (!ds) {
return // no ds for current environment found
}
….
and added:
private Object getCurrentDataSource(def dataSources) {
def dsToUse = null
for (ds in dataSources) {
if (ds.environments.contains(GrailsUtil.environment)) {
dsToUse = ds
break
}
}
return dsToUse
}
With this changes, always all defined dataSources will be searched for the one that defines the current environment, and not just the first one.
I hope this clarifys what i was trying to explain in my previous post.
Again, thanks very much for this exellent plugin, Burt! If you think my changes are correct, it would be nice to incoporate them into your code, so others may use this functionality as well.
I’ve made these changes, but still only consult properly with the secondary datasource, when I try to keep the main datasource record with regard to some of the other side datasource.
I have tables of primary datasource related tables in the main datasource
@Andi, @kenwei – I ran into the same bug with the datasources not working in enviroments other than the first listed one.
All I did to fix it was change the “return” in lines 60 and 187 to a “continue”.
Thanks, Kate – that loop was originally a closure/each loop but when I changed it to a regular for loop I forgot to change from return to continue.
I’m working on supporting hbm-mapped and annotated classes and once that’s ready I’ll release v0.2 of the plugin.
Hi,
I tried code changes suggested by kake and andi but it’s not working for me. Could you please post the changed source file
Thanks
@Sanjay – I checked in the changes to the trunk, please grab the latest file at http://plugins.grails.org/grails-datasources/trunk/DatasourcesGrailsPlugin.groovy
Thanks for this excellent plugin- it’s helping me solve a pretty big problem. (I’m basically doing for real what your sample app does- making an app that looks at three DBs and presents data from them all simultaneously.
Did the HBM mapping and annotations make it into 0.2? I’m having problems where when I try to do a get(id) call on one of my domain classes, and I get a MappingException, with my domain class as an “Unknown Entity”. I’m not sure if the mapping or the class aren’t found, or something I don’t understand is going on. I have the class fully qualified in my Datasources.groovy file, and the whole thing works if I just have the plugin handle no classes.
The classes are Java with hibernate annotations.
When I use your plugin, I have a problem. I have 2 domain class Book and Users.
When I start my app and I create a Users, it create my new users in my ds2, but my list page shows the record which result from my datasource in Datasource.groovy.
My datasouces files…
Datasource.groovy
dataSource {
pooled = true
driverClassName = “oracle.jdbc.OracleDriver”
username = “db1”
password = “db1”
}
hibernate {
cache.use_second_level_cache=true
cache.use_query_cache=true
cache.provider_class=’com.opensymphony.oscache.hibernate.OSCacheProvider’
}
// environment specific settings
environments {
development {
dataSource {
dbCreate = “update”
url = “jdbc:oracle:thin:@serveur:1521:dev”
}
}
test {
dataSource {
dbCreate = “update”
url = “jdbc:oracle:thin:@serveur:1521:dev”
}
}
}
Datasources.groovy
datasources = {
datasource(name: ‘ds2’) {
domainClasses([Users])
driverClassName(‘oracle.jdbc.OracleDriver’)
dbCreate(‘update’)
url(‘jdbc:oracle:thin:@server:1521:dev’)
username(‘db2’)
password(‘db2’)
logSql(true)
pooled(true)
dialect(org.hibernate.dialect.Oracle10gDialect)
environments([‘development’,’test’])
hibernate {
cache {
use_second_level_cache(true)
use_query_cache(true)
}
}
}
}
[…] I was pulling in data from an existing product database, I used Burt Beckwith’s excellent Datasources plugin to connect proxy domain objects that I wrote to manage product information. Here is a sample of the […]
[…] and then used the Liquibase plugin to manage structural changes in the database.We also used the Datasources plugin to manage our different […]
I have made some changes to the plugin code to use GrailsAnnotationConfiguration for loading annotation based java domainClasses along with the default grails domain classes for usage with multiple datasources. How do I send code for review.
Hi,
I want to keep the Datasources.groovy out of the application/conf directory. I want to externalize my Datasources.groovy file. Please suggest how to set classpath as well as filepath.
Hi Burt
Thanks for all the good work you are doing in the community. I am using this plugin v0.3. It is giving me fits if I use jndi in either DataSource or Datasources. Typically it returns
org.hibernate.HibernateException: Missing table: ContentRepurposeSwirl. It seemed to work a little better if I removed the static mapping closures but not all tables. I hope you are still willing to assist with this plugin because I am not sure I want to rewire the application to use REST
Thanks and respect
Andrew
Hi ,
Thanks for your contribution towards Mutli-Datasource plugin. I need to externalize my Datasources.groovy file.
currently I externalized the DataSource.groovy file and it works perfectly fine. But Datasources.groovy file cannot be externalized.
It doesn’t pick the external file datasource, it always connects to the datasource mentioned under appname/conf/Datasources.groovy
Please advice me how to externalize the Datasources.groovy file
I have added the below settings in Config.groovy
grails.config.locations = [ “classpath:${appName}-config.properties”, “classpath:${appName}-config.groovy”, “classpath:${appName}-DataSource.groovy”, “classpath:${appName}-Datasources.groovy”, “file:${userHome}/.grails/config.properties”, “file:${userHome}/.grails/config.groovy”, “file:${userHome}/.grails/Datasources.groovy”, “file:${userHome}/.grails/DataSource.groovy”]
Thanks
Bragadesh
Hi,
I have been trying to use this plugin but to no avail. Is there a tutorial that shows exactly how to set this up with a new project?
my environment:
db01 {
dataSource {
pooled = true
driverClassName = “oracle.jdbc.driver.OracleDriver”
username = “groovytests”
password = “groovytests”
dbCreate = “update”
url = “jdbc:oracle:thin:@localhost:1521:xe”
dialect=”org.hibernate.dialect.Oracle10gDialect”
}
}
my extra datasource:
datasource(name: ‘db02’) {
domainClasses([Kiwis])
readOnly(false)
driverClassName(‘org.hsqldb.jdbcDriver’)
environments([‘db01’])
url(‘jdbc:hsqldb:file:prodDb;shutdown=true’)
username = “sa”
password = “”
dbCreate(‘update’)
hibernate {
cache {
use_second_level_cache(false)
use_query_cache(false)
}
}
}
Never mind got it working.
You have to use a default environment e.g. development, test or production in order to make it work properly.
Hello,
I’m trying to build an app with 4 legacy databases. I would like to use gorm dynamic finders, but transactions are imperative. I don’t have to use legacy hibernate mappings for the old databases.
The plugin currently does not support XA trans. between the databases. Is there a way to achieve my goal?
thanks for advance, and congratulations for the very good plugin.
Does anyone have a SIMPLE working example?
1. how do you format the dataSources.groovy?
2. how do you call this new datasources from within a service?
Hi,
Looks like a great plugin and we’ll def need this in our next project. Although it doesn’t seem compatible with Hibernate 1.1.1 unless some hacking is performed. When do you plan releasing a version that has this problem corrected?
Thanks!
Excellent plugin! Is there a way to use c3p0 with individually configured connection pools for each datasource? If so, can you provide an example? Thank you!
Good question – I added this to the FAQ at http://grails.org/plugin/datasources
I’m trying to use this plugin with Grails 1.1.1, but am experiencing some odd behavior. Saves don’t work unless I explicitly flush the session.
What happens is, the object appears to save (I get back an ID), but when I view the database nothing was actually persisted.
Here’s some code…
Datasources.groovy:
datasources = {
datasource(name: ‘drinks’) {
domainClasses([Drink])
driverClassName(‘oracle.jdbc.driver.OracleDriver’)
url(‘jdbc:oracle:thin:@localhost:1521/xe’)
username(‘drink’)
password(‘drunk’)
dbCreate(‘update’)
logSql(true)
dialect(org.hibernate.dialect.Oracle10gDialect)
environments([‘development’,’test’])
hibernate {
cache {
use_second_level_cache(false)
use_query_cache(false)
}
}
}
}
Datasource.groovy: (only datasource block shown)
environments {
development {
dataSource {
dbCreate = “update”
url = “jdbc:sybase:Tds:IT2-JUS-390:5000/myfoods”
driverClassName = “com.sybase.jdbc2.jdbc.SybDriver”
dialect = “org.hibernate.dialect.SybaseDialect”
}
}
test {
dataSource {
dbCreate = “update”
url = “jdbc:hsqldb:mem:testDb”
}
}
production {
dataSource {
dbCreate = “update”
url = “jdbc:hsqldb:file:prodDb;shutdown=true”
}
}
Drink.groovy (simple domain object)
class Drink {
String name
static constraints = {}
}
Any ideas??
Thank you.
—
Justin
The example doesn’t work at all. Who can publish a already working example. Thank you.
Can we externalize the Datasources.groovy, which supports multiple datasources? Has anyone tried it? If someone can details the steps, it will be very helpful.
Thanks,
Prakash
Prakash,
We can externalize the Datasources.groovy. You can set all the necessary parameters in the external config file ( see externalization)
In external config file
——————–
DW_DB_USERNAME=’test’
DW_DB_PASSWORD=’test_pass’
DW_DB_URL= ( PLEASE PROVIDE THE CONNECTION STRING)
In Datasources.groovy file
———————
url(ConfigurationHolder.config.DW_DB_URL) username(ConfigurationHolder.config.DW_DB_USERNAME) password(ConfigurationHolder.config.DW_DB_PASSWORD)
The plugin works. However, when I tried to use it in a GANT script, it does not work. Not only it does not work, it seems to break most of the gant scripts that need hibernate session factory.
Burt I’m using this plugin. The configuration seems very straight forward. I am currently having a problem and wondered if you have seen this before or might have any suggestions
I have the folling configuration :
I have 3 datasources
Example:
ds1 is configured with domain and service
domain [OldUser]
service [‘OldUser’]
ds2
domain [Msg, Thread]
service [‘migrationRun’]
ds3
domain [Comment, Asset]
service [‘comment’, ‘asset’]
I’ve generated the scaffolding and can see the datasources are working correct results displayed.
But I am also calling my services using a quartz job and this gives me errors.
So quartz does all the session binding for me
This is the hierarchy of calls
job -calls-> migrationRunService -calls-> commentService + assetService -calls-> oldUserService
At the moment I am getting LazyInitializationExceptions
hibernate.LazyInitializationException failed to lazily initialize a collection of role: Asset.comments, no session or session was closed
Any ideas please? Services are transactional.
@Feroz if your services are transactional you shouldn’t see that exception. If you’re using disconnected objects you will though, so you’d need to reassociate them with the current session via “asset.attach()” or by reloading via “asset = Asset.get(asset.id)”. You can also try wrapping the code (including the initial get() or find from the database) in a withTransaction {} block.
Thank you Burt. In the end I set my services to transactional = false and used withTransaction {} blocks and that solved it. Datasources plugin is brillant!
Hi I am getting No property found : com Datasources.
What’s wrong in my configuration
@Krish it’d be helpful to know what your configuration is. Please send a descriptive email to the grails-user mailing list (http://www.grails.org/Mailing+lists) or create a JIRA issue at http://jira.codehaus.org/browse/GRAILSPLUGINS