Multi-Database Adaptation Mechanism

1. Introduction

This project implements dynamic adaptation and seamless switching for three mainstream databases: MySQL, PostgreSQL, and SQL Server, through a modular design. Based on the ABP framework's module system and the extensibility of Entity Framework Core, the system can flexibly support multiple database backends without modifying core business logic. This article will deeply analyze its architectural design, implementation mechanism, and usage methods.

2. Project Structure and Modular Design

The project adopts a layered modular architecture, separating database adaptation logic into independent modules. Each database type (MySQL, PostgreSQL, SQL Server) has a corresponding independent project, ensuring dependency isolation and pluggability.

3. Core EF Core Module Analysis

CMSPluginEntityFrameworkCoreModule is the foundational module for the entire data access layer, responsible for defining common EF Core configurations and entity mapping rules.

This module builds the basic capabilities of the persistence layer by inheriting AbpModule and depending on CMSPluginDomainModule and AbpEntityFrameworkCoreModule. Its main responsibilities include:

• Calling CMSPluginEfCoreEntityExtensionMappings.Configure() during the PreConfigureServices phase to initialize entity extension mappings.
• Providing a unified DbContext configuration entry point.
• Defining abstract data access contracts (such as ICMSPluginDbContext).

4. Database-Specific Module Implementation

Specific adaptation modules are provided for each database: CMSPluginMySQLModule, CMSPluginPostgreSqlModule, and CMSPluginSqlServerModule. These modules all inherit from CMSPluginEntityFrameworkCoreModule and complete the registration of database providers in the ConfigureServices method.

MySQL Module

options.UseMySQL(x => { ... });

Specifies the MySQL database provider via the UseMySQL method and configures the migration history table name and schema.

PostgreSQL Module

options.UseNpgsql(x => { ... });

Specifies the PostgreSQL database provider via the UseNpgsql method, using the Npgsql driver.

SQL Server Module

options.UseSqlServer(x => { ... });

Specifies the SQL Server database provider via the UseSqlServer method.

All modules call builder.ConfigureMyEntityName() in OnModelCreating to apply entity model configurations and unifiedly manage database object naming conventions (such as table prefixes, schema names, etc.) via CMSPluginDbProperties.

5. Dependency Injection and Database Provider Replacement Mechanism

The system's database provider replacement mechanism is based on the ABP framework's dependency injection container and module lifecycle management.

In the ConfigureServices phase, each database module overrides the default database provider configuration via Configure<AbpDbContextOptions>. Due to the "last registration wins" principle of the dependency injection container, the configuration of the last loaded database module takes effect.

Key implementation points:

• All modules share the same CMSPluginDbContext type.
• Decoupling configuration by specifying the connection string name via the [ConnectionStringName] attribute.
• Registering the context using AddAbpDbContext<T>, with the lifecycle managed by the ABP framework.
• The UseXXX method of AbpDbContextOptions determines the actual database provider used.

This design makes the replacement of database providers completely transparent to upper-layer business code.

6. Runtime Dynamic Module Loading Principle

The system implements runtime dynamic loading of the target database module through the dependency relationships and startup order control of the ABP module system.

The implementation principle is as follows:

1. The main module CMSPluginModule depends on CMSPluginEntityFrameworkCoreModule.
2. Each database module depends on CMSPluginEntityFrameworkCoreModule.
3. When the application starts, the corresponding CMSPluginXXXModule is loaded based on the actual referenced database module (only one is referenced).
4. This module registers the specific database provider in ConfigureServices.
5. The dependency injection container ultimately uses the last registered configuration.

This mechanism allows changing the database backend by simply switching project references without modifying any code.

7. Configuration Example and Switching Steps

Configuration Example

Connection string configuration in appsettings.json:

{
  "ConnectionStrings": {
    "Default": "Server=127.0.0.1;Database=CMS.Plugin.MyPluginName;User ID=root;Password=123456"
  }
}

Switching Steps

1. Remove the reference to the current database module in the main project.
2. Add a reference to the target database module (e.g., switch from MySQL to SQL Server).
3. Ensure the connection string in appsettings.json matches the target database format.
4. Rebuild and deploy the application.

For example, when switching to SQL Server, the connection string should be changed to:

"Default": "Server=localhost;Database=CMSPlugin;User Id=sa;Password=your_password;"

8. Migration Scripts and Data Type Mapping Compatibility Notes

Migration Script Notes

• Each database module contains independent UpdateMigrations.bat and UpdateDB.bat scripts.
• Migration history tables are uniquely named via the MigrationsHistoryTable() method to avoid conflicts.
• CMSPluginDbProperties.DbTablePrefix is used in entity configuration to ensure table name consistency.

Data Type Mapping Compatibility

• String length mapping: MySQL uses VARCHAR, PostgreSQL uses character varying, SQL Server uses NVARCHAR.
• Text fields: Long text suggests using TEXT (MySQL), TEXT (PostgreSQL), NVARCHAR(MAX) (SQL Server).
• Boolean type: MySQL uses TINYINT(1), PostgreSQL uses BOOLEAN, SQL Server uses BIT.
• Time type: Uniformly use datetime2 (SQL Server), TIMESTAMP (MySQL), TIMESTAMP (PostgreSQL) to ensure precision.

It is recommended to avoid using database-specific types in entity configuration and prioritize EF Core's standard type mapping.

9. Conclusion

This project implements flexible adaptation for MySQL, PostgreSQL, and SQL Server through a carefully designed modular architecture. The core lies in:

1. Using CMSPluginEntityFrameworkCoreModule as the foundation to provide unified data access abstraction.
2. Each database module implements provider replacement by overriding AbpDbContextOptions.
3. Dependency injection container and module lifecycle management ensure correct runtime configuration.
4. Unified naming conventions and entity configurations guarantee cross-database compatibility.

This design not only supports dynamic switching of three databases but also provides a clear pattern for extending other databases in the future.