Show Navigation

Configure Datasources dynamically while using DATABASE Multi-tenancy

Learn how to use Grails Multi-Tenancy capabilities DATABASE mode while creating a new datasource connection per registered user dynamically.

Authors: Sergio del Amo

Grails Version: 4.0.1

1 Grails Training

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

2 Getting Started

This guide starts where the Custom Tenant Resolver by JWT left off. Please, complete that guide before starting this guide to get a better understanding.

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-dynamic-multiple-datasources/initial

and follow the instructions in the next sections.

You can go right to the completed example if you cd into grails-guides/grails-dynamic-multiple-datasources/complete

3 Writing the Application

This guide uses Multi-Tenancy DATABASE mode. To learn more, read Database per Tenant Multi-Tenancy Guide.

This guides shows a typical flow you will see when creating a SaaS ( Software as a Service ) application using Multi-Tenancy DATABASE mode.

To simplify this guide, the Database provising process is simplified.

Create two MySQL databases with the following schema.

CREATE TABLE `plan` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT primary key,
  `version` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The first database should be called gru and the second vector.

In the guide application, when a user registers, a database with identical name as the username is provisioned for the user, thus each registered user will have their own unique database.

In a real-world app, you will probably have a more complicated setup which may involve creating the database, creating the schema, saving the database url and credentials of each user’s database in a secured way in a database table of the default datasource etc.

3.1 Configuration

In this guide we’ll be using MySQL, so the MySQL dependency should be added to build.gradle.

build.gradle
    runtime 'mysql:mysql-connector-java:5.1.48'

Within the MySQL database create a database named minions.

In application.yml configure just the default datasource to point to this database. The schema of this database will be generated by Hibernate because of the configuration dbCreate: update.

The settings for the dataSource of each user of the application will be configured dynamically and inherit from the default one.

Domain classes dealing with security User, UserRole, Role are mapped to the default dataSource.

grails-app/conf/application.yml
hibernate:
    cache:
        queries: false
        use_second_level_cache: false
        use_query_cache: false
dataSource:
    pooled: true
    jmxExport: true
    driverClassName: com.mysql.jdbc.Driver
    dialect: org.hibernate.dialect.MySQL5InnoDBDialect
    username: root (1)
    password: root (1)
    dbCreate: update
    url: jdbc:mysql://127.0.0.1:8889/minions  (1)
1 Configure these settings according to your MySQL installation.

Change Multi-Tenancy mode to DATABASE within application.yml

grails-app/conf/application.yml
    gorm:
        multiTenancy:
            mode: DATABASE (1)
            tenantResolverClass: demo.CurrentUserByJwtTenantResolver (2)
1 Define Multi-Tenancy mode as DATABASE
2 Set the Tenant Resolver class to a custom class which you wrote in the Custom Tenant Resolver by JWT guide.

3.2 Domain Class

Since this guide utilizes DATABASE Multi-Tenancy where each tenants has a separate database, you no longer need to configure a column to deal with tenantId, hence the tenantId property can be removed:

grails-app/domain/demo/Plan.groovy
package demo

import grails.gorm.MultiTenant

class Plan implements MultiTenant<Plan> { (1)
    String title
}
1 Implement MultiTenant trait to regard this domain class as multi tenant.

3.3 GORM Events

Create a class named UserInsertedListener. It creates a new connection source when a new user is inserted.

It uses the @Listener annotation to listen synchronously to Events from GORM.

src/main/groovy/demo/UserInsertedListener.groovy
package demo

import grails.events.annotation.gorm.Listener
import groovy.transform.CompileStatic
import groovy.util.logging.Slf4j
import org.grails.datastore.mapping.engine.event.PostInsertEvent
import org.grails.orm.hibernate.HibernateDatastore
import org.springframework.beans.factory.annotation.Autowired

@CompileStatic
@Slf4j
class UserInsertedListener {

    @Autowired
    HibernateDatastore hibernateDatastore

    @Autowired
    DatabaseProvisioningService databaseProvisioningService

    @Listener(User) (1)
    void onUserPostInsertEvent(PostInsertEvent event) { (2)
        String username = event.getEntityAccess().getPropertyValue("username")
        DatabaseConfiguration databaseConfiguration = databaseProvisioningService.findDatabaseConfigurationByUsername(username) (3)
        hibernateDatastore.getConnectionSources().addConnectionSource(databaseConfiguration.dataSourceName, databaseConfiguration.configuration) (4)
    }
}
1 Listen Synchrounsly to GORM events of the domain class User
2 Listen to PostInsertEvent events
3 Use a collaborator to retrieve a DatabaseConfiguration object (see below).
4 Use ConnectionSources API to configure the new dataSource dynamically.

Define the UserInsertedListener as a Bean

grails-app/conf/spring/resources.groovy
...
import demo.UserInsertedListener
...
beans = {
...
    userInsertedListener(UserInsertedListener)
...
}

The previous listener uses a couple of classes as collaborators:

src/main/groovy/demo/DatabaseConfiguration.groovy
package demo

import groovy.transform.CompileStatic

@CompileStatic
class DatabaseConfiguration {
    String dataSourceName
    Map configuration
}
grails-app/services/demo/DatabaseProvisioningService.groovy
package demo

import groovy.transform.CompileStatic

@CompileStatic
class DatabaseProvisioningService {

    UserRoleService userRoleService

    List<DatabaseConfiguration> findAllDatabaseConfiguration() {
        List<String> usernames = userRoleService.findAllUsernameByAuthority(VillainService.ROLE_VILLAIN)
        usernames.collect { findDatabaseConfigurationByUsername(it) }
    }

    DatabaseConfiguration findDatabaseConfigurationByUsername(String username) {
        new DatabaseConfiguration(dataSourceName: username, configuration: configurationByUsername(username))
    }

    Map<String, Object> configurationByUsername(String username) {
        [
                'hibernate.hbm2ddl.auto':'none', (1)
                'username': 'root', (2)
                'password': 'root', (2)
                'url':"jdbc:mysql://127.0.0.1:8889/$username" (2)
        ] as Map<String, Object>
    }
}
1 Equivalent of dbCreate: none
2 Change these configuration settings to match your system.
The previous settings do not specify the MySQL Driver or Dialect. Those are inherited from the default Datasource which was configured in application.yml

3.4 Functional Test

We have modified slightly the functional test of Custom Tenant Resolver by JWT Guide to verify that a new connection source is created when a new User is inserted.

src/integration-test/groovy/demo/PlanControllerSpec.groovy
package demo

import grails.gorm.multitenancy.Tenants
import grails.testing.mixin.integration.Integration
import grails.testing.spock.OnceBefore
import io.micronaut.http.HttpRequest
import io.micronaut.http.HttpResponse
import io.micronaut.http.HttpStatus
import io.micronaut.http.client.HttpClient
import org.grails.orm.hibernate.HibernateDatastore
import org.springframework.beans.factory.annotation.Autowired
import spock.lang.Shared
import spock.lang.Specification
import spock.lang.IgnoreIf

@IgnoreIf( { System.getenv('CI') as boolean } )
@Integration
class PlanControllerSpec extends Specification {
    PlanService planService
    UserService userService
    VillainService villainService
    RoleService roleService

    @Autowired
    HibernateDatastore hibernateDatastore

    @Shared HttpClient client

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

    String accessToken(String u, String p) {
        HttpRequest request = HttpRequest.POST('/api/login', [username: u, password: p])
        HttpResponse<Map> resp = client.toBlocking().exchange(request, Map)
        if ( resp.status == HttpStatus.OK ) {
            return resp.body().access_token
        }
        null
    }

    def "Plans for current logged user are retrieved"() {
        when:
        User vector = villainService.saveVillain('vector', 'secret')

        then:
        hibernateDatastore.connectionSources.size() == old(hibernateDatastore.connectionSources.size()) + 1 (1)

        when:
        User gru = villainService.saveVillain('gru', 'secret')

        then:
        hibernateDatastore.connectionSources.size() == old(hibernateDatastore.connectionSources.size()) + 1 (1)

        Tenants.withId("gru") {
            planService.save('Steal the Moon')
        }
        Tenants.withId("vector") {
            planService.save('Steal a Pyramid')
        }

        when: 'login with the gru'
        String gruAccessToken = accessToken('gru', 'secret')

        then:
        gruAccessToken

        when:
        HttpRequest request = HttpRequest.GET('/plan').bearerAuth(gruAccessToken)
        HttpResponse<String> resp = client.toBlocking().exchange(request, String)

        then:
        resp.status == HttpStatus.OK
        resp.body() == '[{"title":"Steal the Moon"}]'

        when: 'login with the vector'
        String vectorAccessToken = accessToken('vector', 'secret')

        then:
        vectorAccessToken

        when:
        request = HttpRequest.GET('/plan').bearerAuth(vectorAccessToken)
        resp = client.toBlocking().exchange(request, String)


        then:
        resp.status == HttpStatus.OK
        resp.body() == '[{"title":"Steal a Pyramid"}]'

        cleanup:
        Tenants.withId("gru") {
            planService.deleteByTitle('Steal the Moon')
        }
        Tenants.withId("vector") {
            planService.deleteByTitle('Steal a Pyramid')
        }
        userService.deleteUser(gru)
        userService.deleteUser(vector)
        roleService.delete(VillainService.ROLE_VILLAIN)
    }
}
1 Verify a new connection source exists

3.5 Add Connection Source on Start-up

When the app restarts, we want to wire-up a dataSource for every registered user. Modify BootStrap.groovy to achieve that:

grails-app/init/demo/BootStrap.groovy
package demo

import groovy.transform.CompileStatic
import org.grails.orm.hibernate.HibernateDatastore

@CompileStatic
class BootStrap {

    HibernateDatastore hibernateDatastore
    DatabaseProvisioningService databaseProvisioningService

    def init = { servletContext ->
        for (DatabaseConfiguration databaseConfiguration : databaseProvisioningService.findAllDatabaseConfiguration() ) { (1)
            hibernateDatastore.getConnectionSources().addConnectionSource(databaseConfiguration.dataSourceName, databaseConfiguration.configuration)
        }
    }

    def destroy = {
    }
}

4 Run the tests

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