Grails Multi-datasource

Learn how to consume and handle transactions to multiple data sources from a Grails application.

Authors: Sergio del Amo

Grails Version: 3.3.1

1 Training

Grails Training - Developed and delivered by the folks who created and actively maintain the Grails framework!.

2 Getting Started

In this guide you are going to…

2.1 What you will need

To complete this guide, you will need the following:

Some time on your hands
A decent text editor or IDE
JDK 1.8 or greater installed with JAVA_HOME configured appropriately

2.2 How to complete the guide

To get started do the following:

Download and unzip the source

Clone the Git repository:
git clone https://github.com/grails-guides/grails-multi-datasource.git

The Grails guides repositories contain two folders:

initial Initial project. Often a simple Grails app with some additional code to give you a head-start.
complete A completed example. It is the result of working through the steps presented by the guide and applying those changes to the initial folder.

To complete the guide, go to the initial folder

cd into grails-guides/grails-multi-datasource/initial

and follow the instructions in the next sections.

You can go right to the completed example if you cd into grails-guides/grails-multi-datasource/complete

3 Writing the Application

We are writing a Grails Application using the rest-profile which connects to two data sources.

In previous versions of Grails for multiple data sources a best effort transaction chain was used to attempt to manage a transaction across all configured data sources. As of Grails 3.3 this is disabled as it caused confusion since it isn’t a true XA implementation and also impacts performance as for every transaction you have a transaction for each data source bound regardless if that is the actual requirement.

3.1 Configuration

Wire-up two data sources in application.yml

grails-app/conf/application.yml

dataSource:
    pooled: true
    jmxExport: true
    driverClassName: org.h2.Driver
    username: sa
    password: ''
environments:
    development:
        dataSource:
            dbCreate: create-drop
            url: jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
        dataSources:
            books:
                dbCreate: create-drop
                url: jdbc:h2:mem:bookDevDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
    test:
        dataSource:
            dbCreate: create-drop
            url: jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
        dataSources:
            books:
                dbCreate: create-drop
                url: jdbc:h2:mem:bookTestDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE

3.2 Domain Classes

Create a Movie domain class. If we don’t specify any data source, it gets associated with the default data source.

grails-app/domain/demo/Movie.groovy

package demo

class Movie {
    String title
    static hasMany = [keywords: Keyword]
}

Create a Book domain class.

grails-app/domain/demo/Book.groovy

package demo

class Book {
    String title

    static hasMany = [keywords: Keyword]

    static mapping = {
        datasource 'books' (1)
    }
}

1	The `Book` domain class is associated with the `books` data source.

Create a Keyword domain class.

grails-app/domain/demo/Keyword.groovy

package demo

import org.grails.datastore.mapping.core.connections.ConnectionSource

class Keyword {
    String name

    static mapping = {
        datasources([ConnectionSource.DEFAULT, 'books']) (1)
    }
}

1	The `Keyword` domain class is associated with to both data sources (dataSource - default one - and `books`).

3.3 Services

Create a DataService for Movie domain class.

grails-app/services/demo/MovieDataService.groovy

package demo

import grails.gorm.services.Join
import grails.gorm.services.Service
import groovy.transform.CompileStatic

@CompileStatic
@Service(Movie)
interface MovieDataService {

    void deleteByTitle(String title)

    @Join('keywords') (1)
    List<Movie> findAll()
}

1	you can specify query joins using the `@Join` annotation.

If you used @ReadOnly instead of @ReadOnly('books') you will get the exception: org.hibernate.HibernateException: No Session found for current thread

Add a regular service:

grails-app/services/demo/MovieService.groovy

package demo

import grails.gorm.transactions.ReadOnly
import grails.gorm.transactions.Transactional
import groovy.transform.CompileStatic
import groovy.util.logging.Slf4j

@Slf4j
@CompileStatic
class MovieService {

    @Transactional
    Movie addMovie(String title, List<String> keywords) {
        Movie movie = new Movie(title: title)
        if ( keywords ) {
            for ( String keyword : keywords ) {
                movie.addToKeywords(new Keyword(name: keyword))
            }
        }
        if ( !movie.save() ) {
            log.error 'Unable to save movie'
        }
        movie
    }



}

grails-app/services/demo/BookDataService.groovy

package demo

import grails.gorm.services.Join
import grails.gorm.services.Service
import grails.gorm.transactions.ReadOnly
import grails.gorm.transactions.Transactional
import groovy.transform.CompileStatic

@CompileStatic
@Service(Book)
interface BookDataService {

    @Join('keywords') (2)
    @ReadOnly('books') (1)
    List<Book> findAll()

    @Transactional('books') (1)
    void deleteByTitle(String title)
}

1	Specify the data source name in `@Transactional` and `@ReadOnly` annotations.
2	you can specify query joins using the `@Join` annotation.

Add a regular service:

grails-app/services/demo/BookService.groovy

package demo

import grails.gorm.transactions.Transactional
import groovy.transform.CompileStatic

@CompileStatic
class BookService {

    @Transactional('books') (1)
    Book addBook(String title, List<String> keywords) {
        Book book = new Book(title: title)
        if ( keywords ) {
            for ( String keyword : keywords ) {
                book.addToKeywords(new Keyword(name: keyword))
            }
        }
        if ( !book.save() ) {
            log.error 'Unable to save book'
        }
        book
    }
}

1	Specify the data source name in `@Transactional` annotation.

Add a Keyword services which works with multiple data sources.

The first data source specified in a domain class is the default when not using an explicit namespace. For Keyword, the data source ConnectionSource.DEFAULT, the first specified, is used by default.

grails-app/services/demo/KeywordService.groovy

package demo

import grails.gorm.DetachedCriteria
import grails.gorm.transactions.ReadOnly
import groovy.transform.CompileStatic

@CompileStatic
class KeywordService {

    @ReadOnly('books')
    List<Keyword> findAllBooksKeywords() {
        booksQuery().list()
    }

    @ReadOnly
    List<Keyword> findAllDefaultDataSourceKeywords() {
        defaultDataSourceQuery().list()
    }

    private DetachedCriteria<Keyword> booksQuery() {
        Keyword.where {}.withConnection('books') (1)
    }

    private DetachedCriteria<Keyword> defaultDataSourceQuery() {
        Keyword.where {}
    }
}

1	Specify the data source name with `withConnection` for a query.

You could have written the books query with a dynamic finder or a criteria query instead of a where query.

Where query: Keyword.where {}.withConnection('books').list()

Dynamic finder: Keyword.books.findAll()

Criteria: Keyword.books.createCriteria().list { }

3.4 Controllers

Create BookController and MovieController. Those consume the services previously implemented.

grails-app/controllers/demo/SaveBookCommand.groovy

package demo

import grails.compiler.GrailsCompileStatic
import grails.validation.Validateable

@GrailsCompileStatic
class SaveBookCommand implements Validateable {
    String title
    List<String> keywords

    static constraints = {
        title nullable: false
        keywords nullable: true
    }
}

grails-app/controllers/demo/BookController.groovy

package demo

import groovy.transform.CompileStatic

@CompileStatic
class BookController {

    static allowedMethods = [save: 'POST', index: 'GET']

    static responseFormats = ['json']

    BookService bookService

    KeywordService keywordService

    BookDataService bookDataService

    def save(SaveBookCommand cmd) {
        bookService.addBook(cmd.title, cmd.keywords)
        render status: 201
    }

    def index() {
        [bookList: bookDataService.findAll()]
    }

    def delete(String title) {
        bookDataService.deleteByTitle(title)
        render status: 204
    }

    def keywords() {
        render view: '/keyword/index',
               model: [keywordList: keywordService.findAllBooksKeywords()]
    }
}

grails-app/controllers/demo/SaveBookCommand.groovy

package demo

import grails.compiler.GrailsCompileStatic
import grails.validation.Validateable

@GrailsCompileStatic
class SaveBookCommand implements Validateable {
    String title
    List<String> keywords

    static constraints = {
        title nullable: false
        keywords nullable: true
    }
}

grails-app/controllers/demo/MovieController.groovy

package demo

import groovy.transform.CompileStatic

@CompileStatic
class MovieController {

    static allowedMethods = [save: 'POST', index: 'GET']

    static responseFormats = ['json']

    MovieService movieService

    MovieDataService movieDataService

    KeywordService keywordService

    def save(SaveMovieCommand cmd) {
        movieService.addMovie(cmd.title, cmd.keywords)
        render status: 201
    }

    def index() {
        [movieList: movieDataService.findAll()]
    }

    def delete(String title) {
        movieDataService.deleteByTitle(title)
        render status: 204
    }

    def keywords() {
        render view: '/keyword/index',
               model: [keywordList: keywordService.findAllDefaultDataSourceKeywords()]
    }
}

3.5 Views

Add multiple JSON Views to render the output.

grails-app/views/book/_book.gson

import demo.Book

model {
    Book book
}

json {
    title book.title
    keywords book.keywords*.name
}

grails-app/views/book/index.gson

import demo.Book

model {
    Iterable<Book> bookList
}

json tmpl.book(bookList)

grails-app/views/movie/_movie.gson

import demo.Movie

model {
    Movie movie
}

json {
    title movie.title
    keywords movie.keywords*.name
}

grails-app/views/movie/index.gson

import demo.Movie

model {
    Iterable<Movie> movieList
}

json tmpl.movie(movieList)

grails-app/views/keyword/index.gson

import demo.Keyword

model {
    Iterable<Keyword> keywordList
}

json {
    keywords keywordList*.name.unique().sort()
}

3.6 Functional Test

Add grails-datastore-rest-client as a testCompile dependency:

build.gradle

testCompile "org.grails:grails-datastore-rest-client"

Add a functional test which verifies Grails handles Multiple data sources as expected:

src/integration-test/groovy/demo/MultipleDataSourceSpec.groovy

package demo

import grails.plugins.rest.client.RestBuilder
import grails.plugins.rest.client.RestResponse
import grails.testing.mixin.integration.Integration
import spock.lang.Shared
import spock.lang.Specification

@Integration
class MultipleDataSourceSpec extends Specification {

    @Shared
    RestBuilder rest = new RestBuilder()

    private RestResponse saveResource(String resource, String itemTitle, List<String> itemKeywords) {
        rest.post("http://localhost:${serverPort}/${resource}") {
            accept('application/json')
            contentType('application/json')
            json {
                title = itemTitle
                keywords = itemKeywords
            }
        }
    }

    private RestResponse deleteResource(String resource, String itemTitle) {
        rest.delete("http://localhost:${serverPort}/${resource}") {
            accept('application/json')
            contentType('application/json')
            json {
                title = itemTitle
            }
        }
    }

    private RestResponse fetchResource(String resource) {
        rest.get("http://localhost:${serverPort}/${resource}") {
            accept('application/json')
        }
    }

    private RestResponse resourceKeywords(String resource) {
        rest.get("http://localhost:${serverPort}/${resource}/keywords") {
            accept('application/json')
        }
    }

    def "Test Multi-Datasource support saving and retrieving books and movies"() {
        when:
        RestResponse resp = saveResource('book', 'Change Agent', ['dna', 'sci-fi'])

        then:
        resp.statusCode.value() == 201

        when:
        resp = saveResource('book', 'Influx', ['sci-fi'])

        then:
        resp.statusCode.value() == 201

        when:
        resp = saveResource('book', 'Kill Decision', ['drone', 'sci-fi'])

        then:
        resp.statusCode.value() == 201

        when:
        resp = saveResource('book', 'Freedom (TM)', ['sci-fi'])

        then:
        resp.statusCode.value() == 201

        when:
        resp = saveResource('book', 'Daemon', ['sci-fi'])

        then:
        resp.statusCode.value() == 201

        when:
        resp = saveResource('movie', 'Pirates of Silicon Valley', ['apple', 'microsoft', 'technology'])

        then:
        resp.statusCode.value() == 201

        when:
        resp = saveResource('movie', 'Inception', ['sci-fi'])

        then:
        resp.statusCode.value() == 201

        when:
        resp = fetchResource('book')

        then:
        resp.statusCode.value() == 200
        resp.json.collect { it.title }.sort() == ['Change Agent', 'Daemon', 'Freedom (TM)', 'Influx', 'Kill Decision']

        when:
        resp = fetchResource('movie')

        then:
        resp.statusCode.value() == 200
        resp.json.collect { it.title }.sort() == ['Inception', 'Pirates of Silicon Valley']

        when:
        resp = resourceKeywords('book')

        then:
        resp.statusCode.value() == 200
        resp.json.keywords == ['dna', 'drone', 'sci-fi']

        when:
        resp = resourceKeywords('movie')

        then:
        resp.statusCode.value() == 200
        resp.json.keywords == ['apple', 'microsoft', 'sci-fi', 'technology']


        when:
        resp = deleteResource('book', 'Change Agent')

        then:
        resp.statusCode.value() == 204

        when:
        resp = deleteResource('book', 'Influx')

        then:
        resp.statusCode.value() == 204

        when:
        resp = deleteResource('book', 'Kill Decision')

        then:
        resp.statusCode.value() == 204

        when:
        resp = deleteResource('book', 'Freedom (TM)')

        then:
        resp.statusCode.value() == 204

        when:
        resp = deleteResource('book', 'Daemon')

        then:
        resp.statusCode.value() == 204

        when:
        resp = deleteResource('movie', 'Pirates of Silicon Valley')

        then:
        resp.statusCode.value() == 204

        when:
        resp = deleteResource('movie', 'Inception')

        then:
        resp.statusCode.value() == 204
    }
}

4 Testing the Application

To run the tests:

./grailsw
grails> test-app
grails> open test-report

./gradlew check
open build/reports/tests/index.html

5 Help with Grails

Object Computing, Inc. (OCI) sponsored the creation of this Guide. A variety of consulting and support services are available.

OCI is Home to Grails

Meet the Team