Search code examples
javaspringspring-bootspring-data-jpamulti-tenant

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


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.


Solution

  • 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();