Using multiple databases in jmix as multi-tenant approach

Support
#10

Hello,
regarding the requirements, I would like two examples, let’s call them “simple example” and “bpm example”.

Simple example:

  • lets say we have one Jmix 2.2.x application using PostgreSQL database, that is being used by several customers (organizations from CUBA) and they have their data stored into separate tenant-specific databases, to satisfy security requirements compliance - users are not asked to choose their database as we know which tenant they belong to
  • user data is stored in the Main, admin user can add new tenant organizations (TenantAdmin role), and also add tenant manager (TenantManager) who can additionally manage the users from his tenant only
  • tenant databases have the same structure
  • bonus: tenant databases can have a common structure and additionally different tables not present in other, and this differential to not to be interfered with by liquibase
  • liquibase updates can be done upon application restart, using the list of registered databases

BPM example
BPM addon has access to all of the underlying data as it contains elements that can execute SQL or scripts, so if the user has access to those features, he has the access too. Awesome Apache Superset integration that is coming may have the same security problem.
Therefore, would it be possible to have BPM and Flowable tables stored also in the tenant specific database? Or maybe we should solve this with external BPM engines as you describe in the Jmix roadmap?

Kind regards,
Mladen

#11

Hi and thanks for your answer,

For your first question about updating specific databases.
Now I have a flag into a specific table on each database and when app starts checks for that specific flag and do some specific sql updatates if necessary so the database for tenant1,2,3 is up to date.
After the update is done with success the flag is put on false and that’s it, next time it will not checking anything.

Now about my business logic.

I have an erp with accounting, salary, sales, etc.
This erp it is used in different scenarious:

One is by accounting companies that have in custody from one company to deal with or ten’s of them or even more then a hundred in their portofolio.

Another scenarion is with companies that are not accounting but use the erp for their business processes. Some of them have also one or 2,3,4 companies that are own by same person/entity and have all the accounting in their location for all their companies. Some of these databases can be very big.

Another scenario is with one company that have different locations where they deal with erp and for some of them we have made separate servers and databases and instalations with replica scenarious due to their specific demands.
For example they do not want to depend on internet if something happens on their main locations so we have separate servers for their locations. Also for some of them this was our decision to have all the data in small databases for every location due to scalability, speed or other reasons.

So for all these scenarious we have separate tenant databases with same structure.
Having a single database with tenant_id column can work for some scenarious but not for all situations. There are pro and cons and for us cons where bigger then pros.

One reason is for some databases the grow very big and having separate databases is more scalable and easy to manage. If I need to backup one specific database it is much faster to have one small backup then to have a big bulk backup with all messed together that take long time.
Sometimes you need to bring customer data to your server to have some specific tests or dealing with bugs.

Also with one very big database all the queries, indexes are big and will make app slower with the passing of time.

Regards,
Camil Ghircoias

#12

Hi everyone,

Thank you for your input.

I’ve prepared a minimalistic example following mostly the “simple” requirements from Mladen’s post above:

Please take a look and share your thoughts on what should be improved and whether this approach can be used in real life applications.

Regards,
Konstantin

3 Likes
#13

It looks promising, we need some time to test it and keep you up to date.

#14

Can y provide what is wrong when running the example

  • Execute the Recreate action for Main Data Store - Done, no errors, database is created
    But on tenant data store - there is no recreate option in context menu on tenant data store.
    So we got this error:
    Liquibase failed to start because no changelog could be found at ‘classpath:/db/changelog/db.changelog-master.yaml’.
#15

Try to switch the DB Schema Management mode to Create and Update:
image

1 Like
#16

As far as I see it works and it is a good point to start with.

About logical structure you provide, in my opinion one user should have rights for more than one tennant database so it will be better a mapping structure one to many not one user to one tennant database.

In this case the user connection form should have also database name prompted after user and password textbox. And the database to connect to should be from a list of databases tennants the user is mapped to.

Browser will keep session to the user and may be it will be possible to have multiple pages opened in the same browser, one for tenant 1 and one for tennant 2 as it will be same user and not different ones as with one user to one tennant approach ? What is your opinion ?

Thank you,
Camil Ghircoias

#17

If you associate a user with multiple tenants, it immediately raises a problem of detecting what tenant is chosen for the user in the current session. So the DataSourceRepository should get the tenant not directly from the user, but from the session.

I will modify the example in such a way that the tenant will be stored in the session upon login, and DataSourceRepository will get it from there.

Regards,
Konstantin

#18

Thanks.

#19

I’ve updated jmix-projects/multidb-multitenancy-sample to set current tenant in session.

Please take a look and share your thoughts.

#20

Hello,

I’ve looked at the example and I understand the logic, it works with this method setCurrentTenantInSession() so it it can be done a solution with one user login and multiple tenant databases.
We’ll give some more feedback when we’ll implement in a more complex solution, hope it will do it’s job.
I think this example should be in jmix documentation as it will help others also.
Thank you.

#21

Thanks, looking forward to it.

Most probably we’ll include this example in the collection at GitHub - jmix-framework/jmix-samples-2: Examples of solving typical problems in Jmix applications.

1 Like
#22

Hi,

We have changed the project a little bit as folowing:

  • remove tenant field from user entity.
  • created many to many relation user<->tenant.
  • on user screen posibility to add more than one tenant to which it has rights to see.
  • after login screen another screen will appear where you can choose tenant database and where it changes the current database with method you provided.

Here it is the modified version:
https://github.com/sorinfederiga/multidbmt

It works as expected but here are some of the problems I think we have to deal with later and all of them are related :
1.You cannot set the rights on screens separatly by tenant database, only by all of them. I suppose all the rights works the same so we have to make another layer of managining user rights separately. I am not sure if this can be done other than by doing in a inhouse solution.
2.This one is a little bit more tricky as on search box if you save one filter it will remain on all databases. This one it will raise some problems as it mess together different filters that are not suitable from one database tenant to another. I am not sure how it can be done yet.
3. Same is on reports but you can use directly with jasper and do it custom without the jmix addon so this is not so big deal.

What do you think ?

Regards Camil Ghircoias

#23

Hi Camil,

All these issues stem from the fact that entities used by these features (ResourcePolicyEntity, ResourceRoleEntity, FilterConfiguration, Report and so on) are located in the main data store, which is shared. And there is no way to put them in the additional one, which is tenant-specific. So all configuration data is now shared between tenants.

I have an idea to rework the application in such a way that the Main Data Store will become tenant-specific, and an additional shared data store will contain users and tenants. So all built-in configuration entities will be tenant-specific. And if you need a shared entity, you will create it in the additional data store.

Is there any potential issue you foresee with this solution?

Regards,
Konstantin

#24

I beleive this would be the right solution.
Users and tenants in one shared database and the rest database specific. That’s how i deal with specific database now in our winform app. One database for users and some general configuration like rights for one tenant database or another - and the rest like reports, rights on forms and menus all are set in specific database.
If you can make it this way it will solve the tenant problems.

#25

Done. Now the main data store is used for tenant data and the additional shared one stores the lists of users and tenants.

Looking forward to your review and comments!

Regards,
Konstantin

#26

Hi,
I’ve run the app and from what i saw it looks good.
Flow ui data is stored in tenant1,2,3 database not in main database. That is perfect.
Users are stored in shared database.
What method did you used to change where user data like flowui is saved ( from main database in specific tenant database 1,2,3 ) ?

Will do some more tests after the Easter vacation but things looks good so far.

Regards,
Camil

#27

Interesting solution. I have a question, @krivopustov if I wanted to share the information of any table should be marked as @Store(name = "shared") to be used by all tenants?

Another thing, how would REST services work?

#28

Nothing special. All standard Jmix subsystems (the core modules and add-ons) store data in the main data store. So as soon as it became tenant-specific (with the help of RoutingDatasource), the Flow UI settings and filters are now stored in tenant databases.

#29

Yes. Then the entity will be stored in the additional data store shared between tenants.

Generic REST won’t work with tenant databases, all requests will go to the default main database. The proper handling of REST requests needs further R&D. We’ll address it later.

1 Like