Show Navigation

Single Database Multi-Tenancy - Discriminator Column

Learn how to leverage Multi-Tenancy features of GORM 6.1 to build an application which uses a single database, but it partitions its data using a discriminator column.

Authors: Sergio del Amo

Grails Version: 3.3.0

1 Grails 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 build a Multi-Tenant application which partitions its data using a discriminator column with Grails and GORM 6.1.

Using a discriminator column allows you handle different tenants (users) with a unique tenant identifier within the same database.

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.7 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/discriminator-per-tenant/initial

and follow the instructions in the next sections.

You can go right to the completed example if you cd into grails-guides/discriminator-per-tenant/complete

3 Writing the Application

The application requires GORM 6.1.x. include::/home/travis/build/grails-guides/discriminator-per-tenant/build/resources/grails-guides-master/src/main/docs/common-changegormversion.adoc[].

gradle.properties
grailsVersion=3.3.0
gormVersion=6.1.6.RELEASE
gradleWrapperVersion=3.5

3.1 Setup Multi-Tenancy

3.1.1 The Multi-Tenancy Mode

In order to use Multi-Tenancy you need to setup the Multi-Tenancy mode that GORM uses, given that three distinct modes are supported:

  • DATABASE - Use a distinct database connection per tenant

  • SCHEMA - Use a single database, but different physical schemas per tenant

  • DISCRIMINATOR - Use a single database, but partition the data using a discriminator column

Generally DATABASE and SCHEMA modes can both be considered to be physically separated, whilst DISCRIMINATOR mode requires more care since different tenants' data is stored in the same physical database:

multi tenancy modes

In this case the required Multi-Tenancy mode is DISCRIMINATOR and it can be configured using the grails.gorm.multiTenancy.mode setting:

grails-app/conf/application.yml
grails:
    gorm:
        multiTenancy:
            mode: DISCRIMINATOR
            tenantResolverClass: org.grails.datastore.mapping.multitenancy.web.SessionTenantResolver

3.1.2 The TenantResolver

Note that, in addition to the mode, the above example configures the tenantResolverClass to use to resolve the tenant.

The tenantResolverClass is a class that implements the TenantResolver interface.

Included within GORM there are several built-in TenantResolver implementations including:

Table 1. Available TenantResolver Implementations
Type Description

SessionTenantResolver

Resolves the tenant id from the HTTP session using an attribute called gorm.tenantId

CookieTenantResolver

Resolves the tenant id from the HTTP cookie using an attribute called gorm.tenantId

SubDomainTenantResolver

Resolves the tenant id from the current sub-domain. For example if the subdomain is foo.mycompany.com, the tenant id would be foo

SystemPropertyTenantResolver

Resolves the tenant id from a system property called gorm.tenantId. Mainly useful for testing

The above implementations are useful to have out-of-the-box, however GORM is flexible and you can implement your own strategy by implementing the TenantResolver interface.

For example if you are using Spring Security you could write a TenantResolver that resolves the tenant id from the currently logged in user.

For this example we are going to be using SessionTenantResolver and storing the tenant id within the current user session.

3.2 Creating the Domain Classes

When creating domain classes for your application you will typically have domain classes that are Multi-Tenant and others that are not.

For domain classes which won’t be using Multi-Tenancy simply define them as you would normally do.

For this example, the Manufacturer will be the provider of tenant ids. The name of the Manufacturer will be used as the tenant identifier.

grails-app/domain/example/Manufacturer.groovy
package example

import grails.compiler.GrailsCompileStatic

@GrailsCompileStatic
class Manufacturer {
    String name

    static constraints = {
        name blank: false
    }
}

Next step is to define domain classes that can only be accessed by a given tenant:

grails-app/domain/example/Engine.groovy
package example

import grails.gorm.MultiTenant

class Engine implements MultiTenant<Engine> { (1)
    Integer cylinders
    String manufacturer  (2)

    static constraints = {
        cylinders nullable: false
    }

    static mapping = {
        tenantId name:'manufacturer'  (2)
    }
}
grails-app/domain/example/Vehicle.groovy
package example

import grails.gorm.MultiTenant

class Vehicle implements MultiTenant<Vehicle> { (1)
    String model
    Integer year
    String manufacturer (2)

    static hasMany = [engines: Engine]
    static constraints = {
        model blank:false
        year min:1980
    }

    static mapping = {
        tenantId name:'manufacturer'  (2)
    }
}
1 Both domain classes implement the MultiTenant trait
2 manufacturer property is used as the tenant id discriminator column.

The Vehicle and Engine domain classes both implement the MultiTenant trait. GORM resolves the entities to use from the resulting tenant id returned from the configured TenantResolver by using the tenant identifier discriminator column.

3.3 Setup Test Data

To setup some test data you can modify the Application class to implement the ApplicationRunner interface to run transactional logic on startup:

grails-app/controllers/example/Application.groovy
package example

import grails.boot.GrailsApp
import grails.boot.config.GrailsAutoConfiguration
import org.springframework.boot.ApplicationArguments
import org.springframework.boot.ApplicationRunner
import grails.gorm.transactions.Transactional
import groovy.transform.CompileStatic

@CompileStatic
class Application extends GrailsAutoConfiguration implements ApplicationRunner { (1)

    static void main(String[] args) {
        GrailsApp.run(Application, args)
    }

    @Override
    @Transactional
    void run(ApplicationArguments args) throws Exception { (2)
        Manufacturer.saveAll( (3)
            new Manufacturer(name: 'Audi'),
            new Manufacturer(name: 'Ford')
        )
    }
}
1 Implement the ApplicationRunner interface
2 Mark the run method as transactional with @Transactional
3 Use saveAll to save two Manufacturer instances

In the example about two Manufacturer instances are saved that will correspond to the two tenants supported by this application.

3.4 Implementing Tenant Selection

The first step to supporting Multi-Tenancy in your application is implementing some form of tenant selection. This could be to resolve the tenant via a DNS subdomain, or it could be part of your applications registration process if you are using authentication with Spring Security.

To keep things simple for the example we are going to implement a simple mechanism that provides a UI to store the tenantId within the users HTTP session.

First create a new ManufacturerController use create-controller or your preferred IDE:

$ grails create-controller Manufacturer

Next modify the UrlMappings.groovy file to map the root of the application to the index action:

grails-app/controllers/example/UrlMappings.groovy
'/'(controller: 'manufacturer')

Then define an index action that lists of all the Manufacturers and renders the grails-app/views/index.gsp view.

grails-app/controllers/example/ManufacturerController.groovy
package example

import org.grails.datastore.mapping.multitenancy.web.SessionTenantResolver
import grails.gorm.transactions.ReadOnly
import groovy.transform.CompileStatic

@CompileStatic
class ManufacturerController {
    @ReadOnly
    def index() {
        render view: '/index', model: [manufacturers: Manufacturer.list()]
    }
}

Within the grails-app/views/index.gsp file, simply iterate through each result and create a link to the select action

grails-app/views/index.gsp
<div id="controllers" role="navigation">
    <h2>Available Manufacturers:</h2>
    <ul>
        <g:each var="m" in="${manufacturers}">
            <li class="controller">
                <g:link controller="manufacturer" action="select" id="${m.name}">${m.name}</g:link>
            </li>
        </g:each>
    </ul>
</div>

The select action, selects the current tenant and stores the tenant within the current user’s HTTP session:

grails-app/controllers/example/ManufacturerController.groovy
@ReadOnly
def select(String id) {
    Manufacturer m = Manufacturer.where {
        name == id
    }.first() (1)
    if ( m ) {
        session.setAttribute(SessionTenantResolver.ATTRIBUTE, m.name.toLowerCase()) (2)
        redirect controller: 'vehicle' (3)
    }
    else {
        render status: 404
    }
}
1 Fetches a Manufacturer identified by the supplied id
2 The selected tenant is stored within a session attribute.

The select action will find a Manufacturer and store the name of the Manufacturer in lower case as the current tenant within the HTTP session.

This causes SessionTenantResolver to resolve the correct tenant id from the HTTP session.

Finally, to improve error handling you can map every occurrence of TenantNotFoundException to redirect back to the list of manufacturers:

grails-app/controllers/example/UrlMappings.groovy
'500' (controller: 'manufacturer', exception: TenantNotFoundException)

With these changes in place you will able to select each tenant from the homepage:

available tenants

Now that it is possible to select a tenant, lets create a logic that is able to use the currently active tenant.

3.5 Writing Multi-Tenant Aware Data Logic

GORM features a set of Multi-Tenancy transformations which facilitate the resolution of the tenant and the binding of a Hibernate Session for that particular tenant in the scope of a method.

Table 1. Multi Tenancy Transformations
Type Description

CurrentTenant

Resolves the current tenant and binds a Hibernate session for the scope of the method

Tenant

Resolves a specific tenant and binds a Hibernate session for the scope of the method

WithoutTenant

Execute some logic within a method without a tenant present

These should generally be applied to services in a Grails application and they work really well when combined with the GORM Data Services concept introduced in GORM 6.1.

To implement the logic to save and retrieve Vehicle instances create a new
grails-app/services/example/VehicleService.groovy file and annotate it within the CurrentTenant and Service annotations:

grails-app/services/example/VehicleService.groovy
import grails.gorm.multitenancy.CurrentTenant
import grails.gorm.services.Service
import grails.gorm.transactions.Transactional
import groovy.transform.CompileStatic

@Service(Vehicle) (1)
@CurrentTenant (2)
@CompileStatic
abstract class VehicleService {
}
1 The Service transformation will ensure any abstract methods that can be implemented by GORM are implemented
2 The CurrentTenant transformation will ensure any method that is executed on the service resolves the current tenant first and binds a Hibernate session.
The class is abstract because many of the methods will be implemented for you by GORM.

Now lets take a look at how to implement querying logic for a Multi-Tenant application.

3.5.1 Executing Multi-Tenant Aware Queries

To implement Multi-Tenant queries in a GORM Data Service simply add abstract methods that correspond to one of the supported conventions in GORM:

grails-app/services/example/VehicleService.groovy
abstract List<Vehicle> list(Map args ) (1)

abstract Integer count() (2)

abstract Vehicle find(Serializable id) (3)
1 The list method returns a list of Vehicle instances and takes optional arguments as a map to perform pagination
2 The count method counts the number of Vehicle instances
3 The find method finds a single Vehicle by id

Now it is time to write a controller that can use these newly defined methods. First, create a new grails-app/controllers/example/VehicleController.groovy class with either the create-controller command or your preferred IDE.

The VehicleController should define a property referencing the previously created VehicleService:

grails-app/controllers/example/VehicleController.groovy
import static org.springframework.http.HttpStatus.NOT_FOUND
import grails.gorm.multitenancy.CurrentTenant
import grails.validation.ValidationException
import groovy.transform.CompileStatic

@CompileStatic
class VehicleController  {

    static allowedMethods = [save: 'POST', update: 'PUT', delete: 'DELETE']

    VehicleService vehicleService
    ...
}

Now run grails generate-views to generate some default GSP views that can render the Vehicle instances:

$ grails generate-views example.Vehicle

Next add an entry into the UrlMappings.groovy file to map the /vehicles URI:

grails-app/controllers/example/UrlMappings.groovy
'/vehicles'(resources: 'vehicle')

Now you are ready to add the query logic to read Vehicle instances for each tenant. Update VehicleController with the following read operations:

grails-app/controllers/example/VehicleController.groovy
def index(Integer max) {
    params.max = Math.min(max ?: 10, 100)
    respond vehicleService.list(params), model: [vehicleCount: vehicleService.count()]
}

def show(Long id) {
    Vehicle vehicle = id ? vehicleService.find(id) : null
    respond vehicle
}

Both the find and show actions use the VehicleService to locate Vehicle instances. The VehicleService will ensure the correct tenant is resolved and the correct data returned for each tenant.

3.5.2 Executing Multi-Tenant Updates

To add logic to perform write operations you can simply modify the VehicleService and add new abstract methods for save and delete:

grails-app/services/example/VehicleService.groovy
abstract Vehicle save(String model,
                        Integer year)

abstract Vehicle delete(Serializable id)

The above save and delete methods will be implemented automatically for you.

GORM Data Services are smart about adding appropriate transaction semantics to each method (for example, readOnly for read operations). However you can override the transaction semantics by adding the @Transactional annotation yourself.

To implement updates you can add a new method that calls the existing abstract find method:

grails-app/services/example/VehicleService.groovy
@Transactional
Vehicle update( Serializable id, (5)
                String model,
                Integer year) {
    Vehicle vehicle = find(id)
    if (vehicle != null) {
        vehicle.model = model
        vehicle.year = year
        vehicle.save(failOnError:true)
    }
    vehicle
}

This demonstrates an important concept of GORM Data Services: It is possible to easily mix methods you define with ones that are automatically implemented for you by GORM.

The corresponding controller actions to call VehicleService and expose these write operations are also trivial:

grails-app/controllers/example/VehicleController.groovy
def save(String model, Integer year) {
    try {
        Vehicle vehicle = vehicleService.save(model, year)
        flash.message = 'Vehicle created'
        redirect vehicle
    } catch (ValidationException e) {
        respond e.errors, view: 'create'
    }
}

def update(Long id, String model, Integer year) {
    try {
        Vehicle vehicle = vehicleService.update(id, model, year)
        if (vehicle == null) {
            notFound()
        } else if ( vehicle.hasErrors() ) {
            redirect action: 'edit', params: [id: id]
        }
        else {
            flash.message = 'Vehicle updated'
            redirect vehicle
        }
    } catch (ValidationException e) {
        respond e.errors, view: 'edit'
    }
}

protected void notFound() {
    flash.message = 'Vehicle not found'
    redirect uri: '/vehicles', status: NOT_FOUND
}

def delete(Long id) {
    Vehicle vehicle = vehicleService.delete(id)
    if (vehicle == null) {
        notFound()
    }
    else {
        flash.message = 'Vehicle Deleted'
        redirect action: 'index', method: 'GET'
    }
}

4 Multi-Tenancy Unit Testing

Testing controller logic that uses Multi-Tenancy requires special considerations.

Luckily GORM 6.1 makes it relatively simple to write unit tests.

To write a unit test for the VehicleController class create a new
src/test/groovy/example/VehicleControllerSpec.groovy Spock specification:

src/test/groovy/example/VehicleControllerSpec.groovy
class VehicleControllerSpec extends HibernateSpec implements ControllerUnitTest<VehicleController> {
        ...
}

As you can see above the test extends HibernateSpec.

To make testing simpler override the tenantResolverClass by overriding the getConfiguration() method of HibernateSpec:

src/test/groovy/example/VehicleControllerSpec.groovy
@Override
Map getConfiguration() {
    [(Settings.SETTING_MULTI_TENANT_RESOLVER_CLASS): SystemPropertyTenantResolver]
}

This will allow you to use SystemPropertyTenantResolver for changing the tenant id within the test.

Next step is to provide a setup method that configures the VehicleService for the controller:

src/test/groovy/example/VehicleControllerSpec.groovy
VehicleService vehicleService (1)

def setup() {
    System.setProperty(SystemPropertyTenantResolver.PROPERTY_NAME, 'audi') (2)
    vehicleService = hibernateDatastore.getService(VehicleService) (3)
    controller.vehicleService = vehicleService (4)
}
1 Define a vehicleService as a property of the unit test
2 Set the tenant id to audi for the purposes of the test
3 Lookup the VehicleService implementation from GORM
4 Assign the VehicleService to the controller under test

To ensure proper cleanup you should also clear the tenant id in a cleanup method:

src/test/groovy/example/VehicleControllerSpec.groovy
def cleanupSpec() {
    System.setProperty(SystemPropertyTenantResolver.PROPERTY_NAME, '')
}

With that done it is trivial to test the controller logic, for example to test the index action with no data:

src/test/groovy/example/VehicleControllerSpec.groovy
void 'Test the index action returns the correct model'() {

    when: 'The index action is executed'
    controller.index()

    then: 'The model is correct'
    !model.vehicleList
    model.vehicleCount == 0
}

You can also write tests to test the case where no tenant id is present by clearing the tenant id:

src/test/groovy/example/VehicleControllerSpec.groovy
void 'Test the index action with no tenant id'() {
    when: 'there is no tenant id'
    System.setProperty(SystemPropertyTenantResolver.PROPERTY_NAME, '')
    controller.index()

    then:
    thrown(TenantNotFoundException)
}

Testing more complex interactions like saving data is possible too:

src/test/groovy/example/VehicleControllerSpec.groovy
    void 'Test the save action correctly persists an instance'() {

        when: 'The save action is executed with an invalid instance'
        request.contentType = FORM_CONTENT_TYPE
        request.method = 'POST'
        controller.save('', 1900)

        then: 'The create view is rendered again with the correct model'
        model.vehicle != null
        view == 'create'

        when: 'The save action is executed with a valid instance'
        controller.save('A5', 2011)

        then: 'A redirect is issued to the show action'
        controller.flash.message != null
        vehicleService.count() == 1
        response.redirectedUrl == '/vehicles/1'

        when: 'The show action is executed with a null domain'
        controller.show(null)

        then: 'A 404 error is returned'
        response.status == 404

        when: 'Update is called for a domain instance that doesn\'t exist'
        response.reset()
        request.contentType = FORM_CONTENT_TYPE
        request.method = 'PUT'
        controller.update(999, 'A5', 2011)

        then: 'A 404 error is returned'
        response.redirectedUrl == '/vehicles'
        flash.message != null

        when: 'An invalid domain instance is passed to the update action'
        response.reset()
        controller.update(1, 'A5', 1900)

        then: 'The edit view is rendered again with the invalid instance'
        view == 'edit'
        model.vehicle instanceof Vehicle

        when: 'A valid domain instance is passed to the update action'
        response.reset()
        controller.update(1, 'A5', 2012)

        then: 'A redirect is issued to the show action'
        response.redirectedUrl == '/vehicles/1'
        flash.message != null

        when: 'The delete action is called for a null instance'
        response.reset()
        request.contentType = FORM_CONTENT_TYPE
        request.method = 'DELETE'
        controller.delete(null)

        then: 'A 404 is returned'
        response.redirectedUrl == '/vehicles'
        flash.message != null
        vehicleService.count() == 1

        when: 'A domain instance is created'
        response.reset()
        controller.delete(1)

        then: 'The instance is deleted'
        vehicleService.count() == 0
        response.redirectedUrl == '/vehicles'
        flash.message != null
    }
}

Note that within the assertions of the above test we use the vehicleService which makes sure the correct tenant is used when making the assertion.

5 Functional Tests

We map the CRUD pages with the help of Geb Pages:

src/integration-test/groovy/example/ManufacturersPage.groovy
package example

import geb.Page

class ManufacturersPage extends Page {

    static at = { $('h2').text().contains('Available Manufacturers') }

    static content = {
        audiLink { $('a', text: 'Audi') }
        fordLink { $('a', text: 'Ford') }
    }

    void selectAudi() {
        audiLink.click()
    }

    void selectFord() {
        fordLink.click()
    }
}
src/integration-test/groovy/example/NewVehiclePage.groovy
package example

import geb.Page

class NewVehiclePage extends Page {

    static at = { title.contains('Create Vehicle') }

    static content = {
        inputModel { $('input', name: 'model') }
        inputYear { $('input', name: 'year') }
        createButton { $('input', name: 'create') }
    }

    void newVehicle(String model, int year) {
        inputModel << model
        inputYear = year
        createButton.click()
    }
}
src/integration-test/groovy/example/ShowVehiclePage.groovy
package example

import geb.Page

class ShowVehiclePage extends Page {

    static at = { title.contains('Show Vehicle') }

    static content = {
        listButton { $('a', text: 'Vehicle List') }
    }

    void vehicleList() {
        listButton.click()
    }
}
src/integration-test/groovy/example/VehiclesPage.groovy
package example

import geb.Page

class VehiclesPage extends Page {

    static at = { title.contains('Vehicle List') }

    static content = {
        newVehicleLink { $('a', text: 'New Vehicle') }
        vehiclesRows { $('tbody tr') }
    }

    void newVehicle() {
        newVehicleLink.click()
    }

    int numberOfVehicles() {
        vehiclesRows.size()
    }
}

We test tenant selection with the help of a functional test:

src/integration-test/groovy/example/TenantSelectionFuncSpec.groovy
package example

import geb.spock.GebSpec
import grails.testing.mixin.integration.Integration

@Integration
class TenantSelectionFuncSpec extends GebSpec {

    def "it is possible to change tenants and get different lists of vehicles"() {

        when:
        go '/'

        then:
        at ManufacturersPage

        when:
        selectAudi()

        then:
        at VehiclesPage

        when:
        newVehicle()

        then:
        at NewVehiclePage

        when:
        newVehicle('A5', 2000)

        then:
        at ShowVehiclePage

        when:
        vehicleList()

        then:
        at VehiclesPage
        numberOfVehicles() == 1

        when:
        newVehicle()

        then:
        at NewVehiclePage

        when:
        newVehicle('A3', 2001)

        then:
        at ShowVehiclePage

        when:
        vehicleList()

        then:
        at VehiclesPage
        numberOfVehicles() == 2

        when:
        go '/'

        then:
        at ManufacturersPage

        when:
        selectFord()

        then:
        at VehiclesPage

        when:
        newVehicle()

        then:
        at NewVehiclePage

        when:
        newVehicle('KA', 1996)

        then:
        at ShowVehiclePage

        when:
        vehicleList()

        then:
        at VehiclesPage
        numberOfVehicles() == 1
    }

}

6 Running the Application

To run the application use the ./gradlew bootRun command which will start the application on port 8080.

Now perform the following steps:

  1. Navigate to the home page and select "Audi"

  2. Enter data for a Vehicle to create a new Vehicle

  3. Note that the data will be created with audi as the value for the discriminator column manufacturer in the vehicle table.

If you then navigate back to the homepage and select "Ford" the current tenant is switched. The webapp displays the data for the tenant ford. The discriminator column effectively isolates the data between the two tenants.

7 Do you need help with Grails?

OCI sponsored the creation of this Guide. OCI offers several Grails services:

Free consultation

The OCI Grails Team includes Grails co-founders, Jeff Scott Brown and Graeme Rocher. Check our Grails courses and learn from the engineers who developed, matured and maintain Grails.

Grails OCI Team