Separate Database Multi-tenancy using Spring Boot

These days we need to develop a lot of SAS applications and multi tenancy is key part of it. So what is multi tenancy? It is a software architecture pattern where one instance of the application serves multiple clients or tenants separating data for individual tenants. This different than multi user application as a tenant can have multiple users. Here are some of the good resources discussing the approaches

  1. Multi-Tenant Data Architecture

  2. JavaOne 2014 – Supporting Multi-tenancy Applications with Java EE

There three approaches to separate data for multiple tenants

  1. Separate Databases – Data for each tenant is stored in separate database. I think this approach gives best separation. Also you can put the databases in multiple instance to scale as other approaches requires you to have single instance.
  2. Separate Schemas – Data for each tenant is stored in separate schema in a single instance. Even though you need to vertically scale one instance, makes it easy to manage one instance etc.
  3. Shared Schema with discriminator column — All the data is stored in same set of tables with a discriminator key in the table. A lot of databases offer row level security which can be used to control access.

There are pros and cons of each approach, which you can read from the reference material linked here. I am presenting an application that uses separate databases. I have omitted a lot of details for spring boot, hibernate and liquibase to focus on key issues for multi tenancy. Please look for some of those details in my other posts.

Requirements

There are many examples that you can find on web that outlines steps you need to take to configure a multi tenant application with fixed set of datasources. This does not work as tenants are signed up over time. So there needs to be way to add tenants without bringing down the application. The application presented here has a company as tenant and multiple users. Many companies can signup for the application. The application itself displays relevant products for the company and allows to add the product. Products for each company should go in different database for the company. When the user logs in the application, the application needs to give access to products for the company that user belongs to.

How to identify tenant?

There are many approaches to identifying tenants. You can find good discussion can be found here. In the application that is presented, we are going to use authentication header to get the user name and get the company it belongs to and then use that to get the database associated with the company.

Setup and technologies used

See my other posts to setup a spring boot based application. This application uses Spring Boot, JPA, Hibernate, Liquibase, AngularJS. Also everything is built using maven.

Domain Model

There are two sets of entities here, one for managing tenants and the other for tenants. For this experiment I call them master and tenant.

Here is the master model

master-model

Company is Tenant here and companyKey is the database name that it needs to use. In an elaborate setup, you might have a better scheme for identifying datasources, credentials or keys to use to connect to tenant databases.

Here is model for tenant database. For this experiment, there is only one table here

tenant-model

All model objects are JPA entities.

Repositories

We use Spring’s CrudRepository as a base for all the domain entities which is very straight forward to use and omitted here but you can look at full source code.

Intercepting web requests

Spring provides an abstract class HandleInterceptorAdaptor for implementing interceptors for web requests.  It provides default implementation for pre and post handling of the web request. In this case we are interested in intercepting request before it reaches to Controller so we need to override preHandle method. Here is the implementation and how it is configured.

Datasource Configuration.

Setting up datasource and hibernate configuration for master database is pretty simple. The key thing to notice here are

  1. Use separate entity manager and transaction manager.
  2. Separate repository for master and tenant in different packages.

To setup multi tenancy for tenants using hibernate, you need two key components.

  • MultiTenantConnectionProvider which provides dataSource associated with the tenant. Since we are using master database to read all the tenants, we need to wait till spring is done with its auto configuration to initialize tenant datasources. We are using simple scheme to generate database URLs.

Once you have these two classes configuring JPA configuration is breeze. Note that we need to do component scan on helper classes that we defined before, set the multi tenancy strategy to be separate databases. Also we defined a separate transaction manager.

Spring Security Setup

This setup is also very simple. We are using a custom user details service to load users from master database.

User Interface

UI is based on AngularJS and we are using wro4j-maven-plugin to build the dependencies. User logs in and see a list of the products for the company and be able to add the products.  UI could have been better but wanted to show the backend and UI is just a means to that.

Running the application

You must have MySQL installed somewhere you can access. Configure its information in application.yml file and you can run the app using.

Also the app initializes data into master and tenant databases for four companies. Go to URL http://localhost:8080/ and login using the users mentioned on the page.

You can find complete source code at https://github.com/zmagdum/zama/tree/master/multitenant

Feel free to leave me comments and forward the article. Enjoy!!!

 

4 thoughts on “Separate Database Multi-tenancy using Spring Boot

  1. Hello,

    I was wondering how we will be adding new tenants here and bind them to their own database without restarting the server. Do we need to add context.refresh() to init() on ContextRefreshedEventfor the database creation?

    Please advise.

    Thanks in advance..

  2. I want to connect this on postgres. I modify application.yml:
    liquibase:
    context: production,test

    spring:
    datasource:
    driverClassName: org.postgresql.Driver
    url: jdbc:postgresql://192.168.100.64:5432/test
    poolName: SpringBootHikariCP
    username: postgres
    password: postgres
    testWhileIdle: true
    validationQuery: SELECT 1

    jpa:
    database-platform: org.hibernate.dialect.MySQL5Dialect
    openInView: false
    show_sql: false
    generate-ddl: false
    hibernate:
    ddl-auto: none
    naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy
    properties:
    hibernate.cache.use_second_level_cache: false
    hibernate.cache.use_query_cache: false
    hibernate.generate_statistics: true
    hibernate.hbm2ddl.auto: validate

    logging:
    level:
    org.springframework.security: DEBUG

    In DatabaseConfiguration have:

    @Bean(destroyMethod = “close”)
    public DataSource dataSource() {
    // LOGGER.debug(“Configuring datasource {} {} {}”, driverClassName, url, user);

    HikariConfig config = new HikariConfig();
    config.setDriverClassName(driverClassName);
    config.addDataSourceProperty(“url”, url);
    config.addDataSourceProperty(“user”, user);
    config.addDataSourceProperty(“password”, password);
    return new HikariDataSource(config);
    }

    The error is:

    [ERROR] Failed to execute goal org.springframework.boot:spring-boot-maven-plugi
    :1.2.7.RELEASE:run (default-cli) on project reddit-web: An exception occured wh
    le running. null: InvocationTargetException: Unable to start embedded container
    nested exception is org.springframework.boot.context.embedded.EmbeddedServletC
    ntainerException: Unable to start embedded Tomcat: Error creating bean with nam
    ‘org.springframework.security.config.annotation.web.configuration.WebSecurityC
    nfiguration’: Injection of autowired dependencies failed; nested exception is o
    g.springframework.beans.factory.BeanCreationException: Could not autowire metho
    : public void org.springframework.security.config.annotation.web.configuration.
    ebSecurityConfiguration.setFilterChainProxySecurityConfigurer(org.springframewo
    k.security.config.annotation.ObjectPostProcessor,java.util.List) throws java.la
    g.Exception; nested exception is org.springframework.beans.factory.BeanExpressi
    nException: Expression parsing failed; nested exception is org.springframework.
    eans.factory.BeanCreationException: Error creating bean with name ‘generalSecur
    tyConfig’: Injection of autowired dependencies failed; nested exception is org.
    pringframework.beans.factory.BeanCreationException: Could not autowire field: p
    ivate org.baeldung.security.MyUserDetailsService org.baeldung.config.web.Genera
    SecurityConfig.userDetailsService; nested exception is org.springframework.bean
    .factory.BeanCreationException: Error creating bean with name ‘myUserDetailsSer
    ice’: Injection of autowired dependencies failed; nested exception is org.sprin
    framework.beans.factory.BeanCreationException: Could not autowire field: privat
    org.baeldung.persistence.dao.UserRepository org.baeldung.security.MyUserDetail
    Service.userRepository; nested exception is org.springframework.beans.factory.N
    SuchBeanDefinitionException: No qualifying bean of type [org.baeldung.persisten
    e.dao.UserRepository] found for dependency: expected at least 1 bean which qual
    fies as autowire candidate for this dependency. Dependency annotations: {@org.s
    ringframework.beans.factory.annotation.Autowired(required=true)} -> [Help 1]
    [ERROR]
    [ERROR] To see the full stack trace of the errors, re-run Maven with the -e swi
    ch.
    [ERROR] Re-run Maven using the -X switch to enable full debug logging.
    [ERROR]

  3. Has anyone this source for Posgtres? I have problems with conversion from HikariDataSource to PGSimpleDataSource

Leave a Reply

Your email address will not be published. Required fields are marked *