Archive for September 4th, 2008

A Grails Plugin for Multiple DataSources

Thursday, September 04th, 2008

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.


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.


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:

  • 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:

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.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.