Show Navigation

Grails Multi-datasource

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

Authors: Sergio del Amo

Grails Version: 4.0.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:

or

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.

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

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.
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/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 grails.gorm.transactions.Transactional
import groovy.transform.CompileStatic

@CompileStatic
@Transactional
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.unique().sort()
}
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.unique().sort()
}
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 micronaut-http-client as a testCompile dependency:

build.gradle
testCompile "io.micronaut:micronaut-http-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.testing.mixin.integration.Integration
import grails.testing.spock.OnceBefore
import io.micronaut.core.type.Argument
import io.micronaut.http.HttpRequest
import io.micronaut.http.HttpResponse
import io.micronaut.http.HttpStatus
import io.micronaut.http.client.HttpClient
import spock.lang.Shared
import spock.lang.Specification

@Integration
class MultipleDataSourceSpec extends Specification {

    @Shared
    HttpClient client

    @OnceBefore
    void init() {
        String baseUrl = "http://localhost:$serverPort"
        this.client  = HttpClient.create(baseUrl.toURL())
    }

    private HttpResponse<Map> saveResource(String resource, String itemTitle, List<String> itemKeywords) {
        HttpRequest request = HttpRequest.POST("/$resource", [title: itemTitle, keywords: itemKeywords])
        client.toBlocking().exchange(request, Map)
    }

    private HttpResponse deleteResource(String resource, String itemTitle) {
        HttpRequest request = HttpRequest.DELETE("/$resource", [title: itemTitle])
        client.toBlocking().exchange(request)
    }

    private HttpResponse<List<Map>> fetchResource(String resource) {
        HttpRequest request = HttpRequest.GET("/$resource")
        client.toBlocking().exchange(request, Argument.of(List, Map))
    }

    private HttpResponse<Map> resourceKeywords(String resource) {
        HttpRequest request = HttpRequest.GET("/$resource/keywords")
        client.toBlocking().exchange(request, Map)
    }

    def "Test Multi-Datasource support saving and retrieving books and movies"() {
        given:
        List<Map> books = [
                [title: 'Change Agent', tags: ['dna', 'sci-fi']],
                [title: 'Influx', tags: ['sci-fi']],
                [title: 'Kill Decision', tags: ['drone', 'sci-fi']],
                [title: 'Freedom (TM)', tags: ['sci-fi']],
                [title: 'Daemon', tags: ['sci-fi']],
        ]
        List<Map> movies = [
                [title: 'Pirates of Silicon Valley', tags: ['apple', 'microsoft', 'technology']],
                [title: 'Inception', tags: ['sci-fi']],
        ]
        books.each { book ->
            HttpResponse<Map> resp = saveResource('book', book.title as String, book.tags as List<String>)
            assert resp.status == HttpStatus.CREATED
        }
        movies.each { movie ->
            HttpResponse<Map> resp = saveResource('movie', movie.title as String, movie.tags as List<String>)
            assert resp.status == HttpStatus.CREATED
        }

        when:
        HttpResponse<List<Map>> resourceResp = fetchResource('book')

        then:
        resourceResp.status == HttpStatus.OK
        resourceResp.body().collect { it.title }.sort() == books.collect { it.title }.sort()

        when:
        resourceResp = fetchResource('movie')

        then:
        resourceResp.status == HttpStatus.OK
        resourceResp.body().collect { it.title }.sort() == movies.collect { it.title }.sort()

        when:
        HttpResponse<Map> resp = resourceKeywords('book')

        then:
        resp.status == HttpStatus.OK
        (resp.body().keywords as List<String>).sort() == books.collect { it.tags }.flatten().unique().sort()

        when:
        resp = resourceKeywords('movie')

        then:
        resp.status == HttpStatus.OK
        (resp.body().keywords as List<String>).sort() == movies.collect { it.tags }.flatten().unique().sort()

        cleanup:
        books.each { book ->
            assert deleteResource('book', book.title as String).status() == HttpStatus.NO_CONTENT
        }
        movies.each { movie ->
            assert deleteResource('movie', movie.title as String).status() == HttpStatus.NO_CONTENT
        }
    }
}

4 Testing the Application

To run the tests:

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

or

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