How to configure multiple database in Grails application

In this tutorial, we are going to learn how to configure multiple databases in the grails 3.x application. This is necessary when you are dealing with multiple databases from the same application especially when dealing with an existing remote database. We are using different MySql databases for testing.

Let's create three MySql databases called db_default, db_one, db_two. Let's look into the application.yml file to configure the different databases and we will do it for the development environment, in other environments, the procedure will be the same.

environments:
    development:
        dataSource:
            dbCreate: update
            url: jdbc:mysql://localhost:3306/db_default
            driverClassName: com.mysql.jdbc.Driver
            dialect: org.hibernate.dialect.MySQL5InnoDBDialect
            username: root
            password: root

Here, we are setting the default database as db_default. So for this database, all the gorm queries will be the same as in a normal application.

Now, let's set up for other two databases.

        dataSources:
            first:
                dialect: org.hibernate.dialect.MySQLInnoDBDialect
                driverClassName: com.mysql.jdbc.Driver
                username: root
                password: root
                url: jdbc:mysql://localhost:3306/db_one
                dbCreate: update
            second:
                dialect: org.hibernate.dialect.MySQLInnoDBDialect
                driverClassName: com.mysql.jdbc.Driver
                username: root
                password: root
                url: jdbc:mysql://localhost:3306/db_two
                dbCreate: update

For more than two, we need to define the databases by defining dataSources and giving the corresponding names. Here the custom name is whatever name you want. We are giving the name first for db_one and second for db_two.

The overall implementation for application.yml file looks as below:

environments:
    development:
        dataSource:
            dbCreate: update
            url: jdbc:mysql://localhost:3306/db_default
            driverClassName: com.mysql.jdbc.Driver
            dialect: org.hibernate.dialect.MySQL5InnoDBDialect
            username: root
            password: root
        dataSources:
            first:
                dialect: org.hibernate.dialect.MySQLInnoDBDialect
                driverClassName: com.mysql.jdbc.Driver
                username: root
                password: root
                url: jdbc:mysql://localhost:3306/db_one
                dbCreate: update
            second:
                dialect: org.hibernate.dialect.MySQLInnoDBDialect
                driverClassName: com.mysql.jdbc.Driver
                username: root
                password: root
                url: jdbc:mysql://localhost:3306/db_two
                dbCreate: update

Now, how we can use the domain classes for the specific databases. Let's create a simple domain class called Book.groovy.

class Book {
    String title
    
    static mapping = {
        datasource 'first' 
    }
}

In the above example, the Book table is mapped to the db_one database so all operations regarding this will be in db_one database. Similarly, we can map the second database as well. We can map the single domain to multiple databases as below:

class Book {
    String title
    
    static mapping = {
        datasources([ConnectionSource.DEFAULT, 'first', 'second'])
    }
}

In the above example, the Book domain will be available in all the databases.

Now, let's look into how we can query for the specific database.

The first DataSource specified is the default when not using an explicit namespace, so in this case, the default one is used. But you can call GORM methods on the 'first' or 'second' DataSource with the DataSource name, for example:

def book = Book.first.get(1) // this will get from db_one
book.first.save() //this will save in db_one

def book = Book.second.get(1) // this will get from db_two
book.second.save() // this will save in db_two

def book = Book.get(1) // this will get from db_default
book.save() // this will save in db_default

We can use groovy native SQL as well. Let's look at the example. 

import grails.transaction.Transactional
import groovy.sql.Sql
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.beans.factory.annotation.Qualifier

import javax.sql.DataSource

@Transactional
class DbOneService {

    @Autowired
    @Qualifier('dataSource_first')
    DataSource dataSource

    def test() {
        def sql = new Sql(dataSource)
        def rows = sql.rows("select * from....")
        println "rows: "+rows
    }
}

Here, we are creating the DbOneService which is annotated with Qualifier where the db_one database name is configured as dataSource_first. This will provide the db_one database connection to do DB operation. You can simply execute the native SQL command as shown above. Similarly, we can do the same for the second database called db_two.

import grails.transaction.Transactional
import groovy.sql.Sql
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.beans.factory.annotation.Qualifier

import javax.sql.DataSource

@Transactional
class DbTwoService {

    @Autowired
    @Qualifier('dataSource_second')
    DataSource dataSource

    def test() {
        def sql = new Sql(dataSource)
        def rows = sql.rows("select * from....")
        println "rows: "+rows
    }
}

This way, we can deal with multiple databases.