How to implement a Multi Tenant Spring Boot application ( where each user has its own Database)

Question

I am building a REST-API with spring boot and I would like to implement a Multi-Tenant Structure to handle data. I want to have one Database called Main that will have the User table, which will hava data about users (Username, password ... and a field database that will denote which database is appointed to this user). Everytime a user signs up his respective DB will be created (This is one of the points where I am facing difficulties). I have read different Tutorials and they all are with predefined Datasources in the application.properties file. Clearly this is not the case here, since the DB for each user will be created "on the fly", or accessed if it is already created.

The workflow is like this ( explained as simple as possible ):

1. User signs-up
2. The app creates the User Entity saves it to Main DB and creates the respective DB for the user
3. The app checks for each call if the user is authenticated, if he is, then go and fetch data from his DB

Then there are a lot of questions regarding filling the DBs when they are automatically created. But first things first :)

My stack : POSTGRESQL, Spring Boot

Thank you in advance.

Reformatory

Answer 1

I found the complete solution to my problem here:

Multi-tenancy: Managing multiple datasources with Spring Data JPA

Big thanks to the author @Cepr0.

The only thing that is missing is creating the DB on the fly. I will update the answer here when I finish my implementation.

UPDATE

I created the Database with the following code, it was recommended by @Milind Barve. So thank you.

 Class.forName("org.postgresql.Driver");
 Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/","postgres", "password");
 Statement smt = con.createStatement();

 smt.executeUpdate("CREATE DATABASE [name_of_db_here] WITH OWNER DEFAULT");

UPDATE : Initializing the schema of each newly created DB, I created a .sql file with all the table creations and use FlyWay to initialize each newly created DB

// INITIALIZE THE DB
            Flyway flyway = Flyway.configure()
                    .dataSource(dataSource)
                    .target(MigrationVersion.LATEST)
                    .load();

            flyway.migrate();

Reformatory

How do you initialize with flyway? Can you share code? – Flavio

@Flavio the second code snippet shows how to do that – Reformatory

Answer 2

multi-tenancy can be achieved as you required by following steps.

1. Add 2 configuration classes one for shared database and one for tenant database, which configurs LocalContainerEntityManagerFactoryBean. This bean should set the required multitenancy properties for LocalContainerEntityManagerFactoryBean e.g.

 Map<String, Object> properties = hibernateProperties.determineHibernateProperties(
        this.properties.getProperties(), new HibernateSettings());


    properties.put(Environment.MULTI_TENANT, MultiTenancyStrategy.SCHEMA);
    properties.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER, this.connectionProvider);
    properties.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER, this.resolver);
    properties.put(Environment.DIALECT, "org.hibernate.dialect.MySQLDialect");

This class should also implement named bean transactionManager for each type. e.g.

 @Bean(name = "tenantTransactionManager")
  public PlatformTransactionManager transactionManager() {
    JpaTransactionManager tm = new JpaTransactionManager();
    tm.setEntityManagerFactory(this.entityManagerFactory().getObject());
    return tm;
  }

2. implement interface CurrentTenantIdentifierResolver and method resolveCurrentTenantIdentifier. This should return the database name of the tenant based on the current logged-in user. Or default database name if no user is logged-in

3. A thread safe context holder to remember the current tenant name

4. Annotate the services implementations for the entity classes with @Transactional annotation and pass the bean name of appropriate entity manager e.g.

@Transactional("tenantTransactionManager") // for tenant database

and

@Transactional("transactionManager") // for shared database.

5. Setup a database schema creation method when a new users signs up. and maintain the tenant database name as one of the column in user table in shared schema.

6. If you are using spring security, implement UserDetailsService interface and implement method loadUserByUsername such that it returns an object of TenantUser class which contains additional information ( tenant database name) for the user logging in.

public class TenantUser extends org.springframework.security.core.userdetails.User {
 

  /** The tenand id. */
  private String tenantId;

Hope these steps help you to achieve what you want. There are many articles available which explains all these steps in detail. My implementation is deep embedded in my project hence it is not in a state which can be shared as working example.

Happy to answer any further questions

Choate

First of all, Thank you for taking the time to respond. 1) Why should I use LocalContainerEntityManagerFactoryBean, when I can use application.properties file for different DataSources ? (Since I am using boot) (p.s my Dialect is Postgres ) 2) This is clear, It's just a class that checks if the user is logged in, if yes then give me the respective DB, else return a string that corresponds to the Main DB. 5) How can I create a Database "on the fly" with Spring boot and Postgresql? There is no ?createIfExists=true as in MySql – Reformatory

Also this shows a lot, but the steps when the Database connection changes are not clear to me. If you could explain further I would be very thankful. Thank you once again. @Milind Barve – Reformatory

For creating database on the fly , I have used following steps Statement stmt = null; stmt = connection.createStatement(); stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS " + tenantId + " CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"); and I am using Liquibase for schema management. So I use liquibase methods to populate schema ( tables etc) in newly created database – Choate

I will try to share my code , till that time you can refer to tech.asimio.net/2017/01/17/… which hopefully gives answers to your questions. – Choate

I appreciate you're trying to help me, Keep me updated please. In the meanwhile I'll take a look at the above link Thank you :) – Reformatory

Your idea was the same as the solution that I found, Thanks. Can you show me some good resouces on how i can populate the schemas of these new DBs as you do with Liquibase? Thank you. – Reformatory