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
DomainClassPlugin have done their work. Then it uses a configuration defined in
grails-app/conf/Datasources.groovy and creates one or more extra
TransactionManager, etc. and re-runs the
doWithDynamicMethods closure for the appropriate subset of domain classes. This way when you call a magic GORM method (e.g.
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
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.
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||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
|domainClasses||List<String> or List<Class>||yes||the domain classes that should use this
|services||List<String>||no||short names of the services that should use this
See the sample app (link below) for a usage example.
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.
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.
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.
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:
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.