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:

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.

76 Responses to “A Grails Plugin for Multiple DataSources”

  1. [...] Burt Beckwith discusses his new plugin – one I’ve been needing for a while! [...]

  2. ladislao says:

    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)
    }
    }
    }

    }

  3. ladislao says:

    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)
    }
    }
    }

    }

  4. sadhna says:

    Same error as ladislao.

    No signature of method: java.util.ArrayList.push() is applicable for argument types: (java.lang.String) values: {”datasource”}

  5. sadhna says:

    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”}

  6. Burt says:

    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]“.

  7. sadhna says:

    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

  8. ladislao says:

    the previous error it was a version error on grails thanks your plug-in its excellent

  9. F.Baube says:

    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.

  10. Keith Thomas says:

    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.

  11. Burt says:

    Keith – sorry about that, I still need to document this plugin (and 2 others) and add them to the official list.

  12. Keith Thomas says:

    No problem, as long as I’m not overlooking something. Thanks for creating such a great plugin.

  13. DLC says:

    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?

  14. Andi Schweizer says:

    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 ?

  15. Burt says:

    @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.

  16. Andi Schweizer says:

    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)
    }
    }
    }
    }

  17. [...] 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 [...]

  18. Kate says:

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

  19. kenwei says:

    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.

  20. Andi Schweizer says:

    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.

  21. Daniel says:

    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

  22. Kate says:

    @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”.

  23. Burt says:

    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.

  24. Sanjay Gupta says:

    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

  25. Burt says:

    @Sanjay – I checked in the changes to the trunk, please grab the latest file at http://plugins.grails.org/grails-datasources/trunk/DatasourcesGrailsPlugin.groovy

  26. Scott Kullberg says:

    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.

  27. Gringos says:

    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)
    }
    }
    }

    }

  28. [...] 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 [...]

  29. [...] and then used the Liquibase plugin to manage structural changes in the database.We also used the Datasources plugin to manage our different [...]

  30. Krishh says:

    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.

  31. bragadesh says:

    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.

  32. Andrew says:

    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

  33. bragadesh says:

    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

  34. jim says:

    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)
    }
    }
    }

  35. jim says:

    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.

  36. andre says:

    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.

  37. grailsme says:

    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?

  38. Jeff says:

    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!

  39. tkarakai says:

    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!

  40. Burt says:

    Good question – I added this to the FAQ at http://grails.org/plugin/datasources

  41. Justin says:

    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

  42. Leo says:

    The example doesn’t work at all. Who can publish a already working example. Thank you.

  43. Prakash says:

    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

  44. bragadesh says:

    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)

  45. Jay says:

    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.

  46. Feroz says:

    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.

  47. Burt says:

    @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.

  48. Feroz says:

    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!

  49. Krish says:

    Hi I am getting No property found : com Datasources.
    What’s wrong in my configuration

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