In this post, I will take you on a journey through our experience of transforming Virto Commerce into a DB Agnostic platform, supporting multiple database providers like MS SQL, PostgreSQL, and MySQL.
Our story showcases how we managed to migrate to a database-agnostic architecture without causing any breaking changes during updates. We believe that our approach, backed by examples of C# code and Entity Framework features, will be valuable to other developers seeking a similar level of flexibility in their solutions.
Chapter 1: Understanding the Problem and Setting the Goal
Our primary challenge was to make Virto Commerce seamlessly work with different database providers, offering users the freedom to switch between systems without disruption. We set the goal to achieve a true DB Agnostic architecture without any breaking changes, making it easier for developers to customize the solution as per their database requirements.
Chapter 2: Design Principles
Before implementation, we outlined the principles guiding our solution.
- No Breaking Changes: Updates must be seamless, preserving existing functionality.
- Use Entity Framework: We explored how Entity Framework offered features essential for our migration.
- Database Provider Isolation: Isolating database-specific code in separate projects allowed each module to use its preferred provider without affecting the core project.
- Support Customization: By allowing provider-level customization, developers could utilize unique DB features while maintaining DB Agnostic benefits.
Chapter 3: The Architecture of Virto Commerce and Entity Framework
By design, Virto Commerce module has Web, Data, and Core layers. This approach and Entity Framework help us to handle the migration process smoothly.
We split Data project into 4 projects:
- CartModule.Data
- CartModule.Data.MySql
- CartModule.Data.PostgreSql
- CartModule.Data.SqlServer
if you need to support a new database, you can create a new VC module and using Virto Commerce Extensibility Framework add a new database provider.
vc-module-cart/src at dev · VirtoCommerce/vc-module-cart (github.com)
These projects allow your module to be database agnostic, meaning it can be easily adapted to work with different database systems without significant code changes.
Each project provides a specific implementation for working with a particular database system, while the CartModule.Data project serves as the common base for all database-related functionality.
This architecture was designed to promote code reusability, maintainability, and scalability.
Chapter 4: Data.[Provider] Projects Structure
Data.[Provider] project has the following clean structure:
- Migrations folder - contains the migration files for the database system.
- DbContextOptionsBuilderExtensions class - contains the helper method for configuring the DbContextOptionsBuilder for specific database provider.
- [Provider]DbContextFactory class - contains implementation of IDesignTimeDbContextFactory for for specific database provider.
- Readme.md file - contains the instructions for configuring and migration creation the specific database provider.
- Model Customization files (optional) - entity type configuration to change schema for DB specific needs.
Here you can see an example of DbContextOptionsBuilderExtensions, you can see that we call native extension and then call MigrationsAssembly to store your migrations in a different project. Read more about Using a Separate Migrations Project - EF Core | Microsoft Learn
public static class DbContextOptionsBuilderExtensions
{
/// <summary>
/// Configures the context to use SqlServer.
/// </summary>
public static DbContextOptionsBuilder UseSqlServerDatabase(this DbContextOptionsBuilder builder, string connectionString)
{
return builder.UseSqlServer(connectionString, db => db
.MigrationsAssembly(typeof(SqlServerDbContextFactory).Assembly.GetName().Name));
}
}
and CartDbContext initialization depends on DatabaseProvider:
var databaseProvider = Configuration.GetValue("DatabaseProvider", "SqlServer");
serviceCollection.AddDbContext<CartDbContext>(options =>
{
var connectionString = Configuration.GetConnectionString(ModuleInfo.Id) ?? Configuration.GetConnectionString("VirtoCommerce");
switch (databaseProvider)
{
case "MySql":
options.UseMySqlDatabase(connectionString);
break;
case "PostgreSql":
options.UsePostgreSqlDatabase(connectionString);
break;
default:
options.UseSqlServerDatabase(connectionString);
break;
}
});
For SqlServer, we just move code from Data project. For others, we created them from scratch.
Chapter 5: Model Customization
One of the features of Virto Commerce is that it allows you to configure various aspects of the entity type required for a specific database provider needs. You can specify the properties, keys, indexes, relationships, etc. of your entities using a fluent API. For example, MySql doesn’t support money.
To provide the ability to customize entity type mapping for specific databases we use ApplyConfigurationsFromAssembly feature. Virto Commerce Module calls ApplyConfigurationsFromAssembly from OnModelCreating in DbContext implementation.
...
// Allows configuration for an entity type for different database types.
// Applies configuration from all <see cref="IEntityTypeConfiguration{TEntity}" in VirtoCommerce.CartModule.Data.XXX project. />
switch (this.Database.ProviderName)
{
case "Pomelo.EntityFrameworkCore.MySql":
modelBuilder.ApplyConfigurationsFromAssembly(Assembly.Load("VirtoCommerce.CartModule.Data.MySql"));
break;
case "Npgsql.EntityFrameworkCore.PostgreSQL":
modelBuilder.ApplyConfigurationsFromAssembly(Assembly.Load("VirtoCommerce.CartModule.Data.PostgreSql"));
break;
case "Microsoft.EntityFrameworkCore.SqlServer":
modelBuilder.ApplyConfigurationsFromAssembly(Assembly.Load("VirtoCommerce.CartModule.Data.SqlServer"));
break;
}
...
To use this feature, you need to implement the IEntityTypeConfiguration interface in your entity configuration classes. This interface defines a method called Configure that takes an EntityTypeBuilder parameter and configures various aspects of the entity type.
The following code snippet shows an example of how to implement this interface for a DiscountEntity entity:
public class DiscountEntityConfiguration : IEntityTypeConfiguration<DiscountEntity>
{
public void Configure(EntityTypeBuilder<DiscountEntity> builder)
{
builder.Property(x => x.DiscountAmount).HasColumnType("decimal").HasPrecision(18, 4);
builder.Property(x => x.DiscountAmountWithTax).HasColumnType("decimal").HasPrecision(18, 4);
}
}
Chapter 6: Collaboration with the Community
We emphasized the importance of sharing experiences and knowledge with the Virto Commerce community. By collaborating and addressing concerns, we fostered a supportive ecosystem, helping others embark on a similar migration journey. It helped us implement and test DB Agnostic faster.
Chapter 7: Conclusion
Our migration to a DB Agnostic architecture was a resounding success, delivering greater flexibility and maintainability without breaking changes. The experience gained from leveraging Entity Framework and providing customization options is a valuable resource for other developers seeking to achieve similar goals in their .NET solutions.
References
Using a Separate Migrations Project - EF Core | Microsoft Learn
Creating and Configuring a Model - EF Core | Microsoft Learn
DB Agnostic Architecture - Overview
DB Agnostic Architecture - Creating Custom Module with DB Agnostic Approach
DB Agnostic Architecture - Transforming Custom Module to Support DB Agnostic Approach
Virto Commerce Cart Module.
Epilogue
We hope our journey and examples of C# code inspire and assist developers in adopting DB Agnostic principles. By sharing our experience, we aim to contribute to a community of resilient, flexible, and forward-thinking solutions in the world of ecommerce and beyond
