SaaS multi-tenant [database isolation]

0. Extremely fast experience

① Clone the https://gitee.com/zhijiantianya/ruoyi-vue-pro (opens new window) repository and switch to the feature/dev-yunai branch.

② Create ruoyi-vue-pro-master, ruoyi-vue-pro-tenant-a, ruoyi-vue-pro-tenant-b Three databases.

③ Download multi-tenant multi-db.zip and unzip it, and import SQL into the corresponding database.

friendly reminder:

SQL scripts may become outdated as versions are released. If you encounter any problems, you can give me feedback on Planet.

④ Start the front-end and back-end projects and have a pleasant experience.

#1. Implementation Principle

DATASOURCE mode, extended and implemented based on dynamic-datasource (opens new window).

Core: Each time the database is operated, dynamically switch to the data source where the tenant is located, and then execute the SQL statement.

#2. Function Demonstration

Let’s add a new tenant using the DATASOURCE mode.

① Click the [Infrastructure -> Data Source Configuration] menu, click the [New] button, and add a new data source named tenant-a.

Then, manually copy the following table to the ruoyi-vue-pro main library and copy the following table to the ruoyi-vue-pro-tenant-a library. As shown below:

system_dept
system_login_log
system_notice
system_notify_message
system_operate_log
system_post
system_role
system_role_menu
system_social_user
system_social_user_bind
system_user_post
system_user_role
system_users

friendly reminder:

As the version iterates, more tables may need to be copied. If you encounter any problems, you can give me feedback on Planet.

② Click the [Infrastructure->Tenant Management] menu, click the [Add] button, add a tenant named Tudou Tenant, and use the tenant-a data source . As shown below:

At this time, in the ruoyi-vue-pro-tenant-a library, you can query the corresponding tenant administrator, role and other information. As shown below:

③ Exit the system and log in to the newly created tenant.

At this point, we have completed the creation of the tenant.

Additional instructions:

When using it later, it is recommended to delete the tables copied to other tenant databases from the ruoyi-vue-pro main database.

The purpose is that the main database only keeps global tables shared by all tenants. For example, menu table, scheduled task table, etc.

#3. Create table

When using DATASOURCE mode, the database can be divided into two types: main database and tenant database.

#3.1 Main Library

① Store tables shared by all tenants. For example: menu table, scheduled task table, etc. As shown below:

② Corresponds to the master data source, configured in the application-{env}.yaml configuration file. As shown below:

③ The Mapper corresponding to each main library must add the @Master (opens new window) annotation. for example:

#3.2 Tenant Library

① Store the table of each tenant. For example: user table, role table, etc.

② In the [Infrastructure -> Data Source Configuration] menu, configure the data source.

③ The @TenantDS annotation must be added to the Mapper corresponding to each main library. for example:

#3.3 Tenant Field

① Considering scalability, when using the DATASOURCE mode, the COLUMN mode will be superimposed by default, that is, there is also the tenant_id tenant field:

• During the INSERT operation, the tenant number is automatically recorded in the tenant_id field.
• During the SELECT operation, the WHERE tenant_id = ? query condition is automatically added.

If you don’t need it, you can directly delete the TenantDatabaseInterceptor (opens new window) class and its Bean automatic configuration.

Scalability refers to the fact that some [large] tenants have independent databases and some [small] tenants share data.

② Because the COLUMN mode is superimposed, the table of main database needs to add the tenant_id field according to the situation.

• Scenario 1: There is no need to add the tenant_id field. For example: menu table, scheduled task table, etc. Note that the table name needs to be added to the yudao.tenant.ignore-tables configuration item.
• Case 2: The tenant_id field is required. For example: access log table, exception log table, etc. The purpose is to check which tenant’s system-level logs are generated.

#4. Multiple data source transactions

After using DATASOURCE mode, one operation may involve multiple data sources. For example: when creating a tenant, you need to operate both the main database and the tenant database.

Considering the data consistency of multiple data, we will use transactions. However, when using Spring transactions, there will be a problem that multiple databases cannot be switched. For fat friends who don’t understand, you can read the article “MyBatis Plus’s multi-data source @DS switching does not work, whose fault is it” (opens new window) article.

Transaction solutions for multiple data sources are a common problem. The more mainstream ones include the following two, both of which are relatively heavyweight solutions:

1. Using Atomikos (opens new window) to implement JTA distributed transactions has complex configuration and poor performance.
2. Use Seata (opens new window) to implement distributed transactions. It is simple to use and has good performance, but it requires the introduction of additional Seata Server services.

#4.1 Local Affairs

Considering that the project has a monolithic architecture and is not suitable for heavyweight transactions, the “local transaction” lightweight solution provided by dynamic-datasource (opens new window) is used.

Its implementation principle is: customize @DSTransactional (opens new window) transaction annotation to replace Spring @Transactional transaction annotation.

• When the logic execution is successful, the transaction for each data source is committed in a loop.
• When logic execution fails, the transaction for each data source is rolled back in a loop.

But there is a risk point. If the database is abnormal (for example, downtime), then local transactions may have data inconsistencies. for example:

• ① Transaction submission of main database
• ② An exception occurred in the tenant database and the tenant’s transaction submission failed.
• Result: The data in the main database has been submitted, but the data in the tenant database has not been submitted, which will lead to data inconsistency.

Therefore, if your system has high data consistency requirements, please use the Seata solution.

#4.2 Usage Example

On the outermost Service method, add the @DSTransactional annotation. For example, create a tenant’s Service method:

Note that Spring’s own transactions cannot be nested in it. That is, the Service method [yellow circle] in the above figure cannot use the Spring @Transactional annotation, otherwise the data source will not be switched.

If the [Yellow Circle] Service itself also needs transactions, you can use the @DSTransactional annotation.