Complete source code: Click Here

Introduction

Flexibility in database selection is critical in modern application development. A Web API should support multiple database options such as SQL Server, PostgreSQL, MySQL, and SQLite without major code changes. In this article, we will build a .NET 9 Web API using Clean Architecture, Domain-Driven Design (DDD), and MediatR, while implementing dynamic database selection using Entity Framework Core (EF Core).

Why Do We Need a Dynamic Database?

1. Flexibility in Deployment

2. Cost Optimization

3. Scalability and Performance

4. Vendor Independence

5. Multi-Tenant Applications

Key Features

Prerequisites

About the Project

This project is a .NET 9 Web API designed with Clean Architecture principles and Domain-Driven Design (DDD). It follows the CQRS pattern using MediatR and supports dynamic database selection using Entity Framework Core. The project is structured into multiple layers for maintainability and scalability:

The main objective of this project is to allow users to select a database dynamically via configuration, making the API flexible and adaptable for different deployment scenarios.

Let’s start with the implementation.

Create the Solution and Projects

We can create solutions and projects from UI or through command. The project structure should be as shown below:

Solution explorer

mkdir DynamicDatabaseApp && cd DynamicDatabaseApp
dotnet new sln -n DynamicDatabaseAPI

dotnet new webapi -n WebAPI
dotnet new classlib -n Application
dotnet new classlib -n Domain
dotnet new classlib -n Infrastructure

dotnet sln add src/WebAPI src/Application src/Domain src/Infrastructure

dotnet add src/WebAPI reference src/Application src/Infrastructure

dotnet add src/Application reference src/Domain

dotnet add src/Infrastructure reference src/Domain

Add Project references

Install Required NuGet Packages

Let’s Add the necessary packages for the project.

You can add the packages from UI or command.

dotnet add src/WebAPI package Microsoft.EntityFrameworkCore.Design

dotnet add src/Application package MediatR
dotnet add src/Application package Microsoft.EntityFrameworkCore
dotnet add src/Application package Microsoft.Extensions.DependencyInjection

dotnet add src/Infrastructure package Microsoft.Extensions.DependencyInjection
dotnet add src/Infrastructure package Microsoft.EntityFrameworkCore.Design
dotnet add src/Infrastructure package Microsoft.EntityFrameworkCore
dotnet add src/Infrastructure package Microsoft.EntityFrameworkCore.Sqlite
dotnet add src/Infrastructure package Microsoft.EntityFrameworkCore.SqlServer
dotnet add src/Infrastructure package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add src/Infrastructure package Pomelo.EntityFrameworkCore.MySql
dotnet add src/Infrastructure package Microsoft.Extensions.Configuration
dotnet add src/Infrastructure package Microsoft.Extensions.DependencyInjection

We created a project structure following Clean Architecture and added the necessary references.

Implementing the Layers


Define the Domain Layer (Domain)

Entity – User.cs

namespace Domain;

public class User
{
    public Guid Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;

}

Define the Application Layer (Application)

Create IApplicationDbContext.cs class in the Common/Interfaces folder.

using Domain;
using Microsoft.EntityFrameworkCore;

namespace Application.Common.Interfaces;

public interface IApplicationDbContext
{
    DbSet<User> Users { get; }
    Task<int> SaveChangesAsync(CancellationToken cancellationToken = default);

}

Create UserDto.cs under User/Dto folder.

namespace Application.Users.Dto;

public record UserDto
{
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
}

Command – CreateUserCommand.cs in folder User/Create.

using MediatR;

namespace Application.Users.Create;

public class CreateUserCommand : IRequest<Guid>
{
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
}

Command Handler – CreateUserCommandHandler.cs in folder User/Create.

using Application.Common.Interfaces;
using Domain;
using MediatR;

namespace Application.Users.Create;

public class CreateUserCommandHandler(IApplicationDbContext dbContext) : IRequestHandler<CreateUserCommand, Guid>
{
    public async Task<Guid> Handle(CreateUserCommand request, CancellationToken cancellationToken)
    {
        var user = new User
        {
            Id = Guid.NewGuid(),
            Name = request.Name,
            Email = request.Email
        };

        dbContext.Users.Add(user);
        await dbContext.SaveChangesAsync(cancellationToken);

        return user.Id;
        
    }
}

For Dependency Injection, add DependencyInjection.cs.

using Microsoft.Extensions.DependencyInjection;

namespace Application;

public static class DependencyInjection
{
    public static IServiceCollection AddApplication(this IServiceCollection services)
    {
        services.AddMediatR(config =>
        {
            config.RegisterServicesFromAssembly(typeof(DependencyInjection).Assembly);
        });

        return services;
    }
}

The Application project should be as illustrated below.

Solution explorer- Application

Implement the Infrastructure Layer (Infrastructure)

Database Context – AppDbContext.cs (under the Persistence folder).

using Application.Common.Interfaces;
using Domain;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;

namespace Infrastructure.Persistence;

public class AppDbContext (DbContextOptions<AppDbContext> options)
    : DbContext(options), IApplicationDbContext
{
    public DbSet<User> Users { get; set; } = null!;

    public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        ChangeTracker.DetectChanges();
        return base.SaveChangesAsync(cancellationToken);
    }
}

DependencyInjection.cs

using Infrastructure.Persistence;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using Application.Common.Interfaces;

namespace Infrastructure;

public static class DependencyInjection
{
    public static IServiceCollection AddInfrastructure(this IServiceCollection services, IConfiguration configuration)
    {
        string dbType = configuration["Database:Type"] ?? string.Empty;

        services.AddDbContext<AppDbContext>(options =>
        {
            switch (dbType)
            {
                case "SQLServer":
                    options.UseSqlServer(configuration.GetConnectionString("SqlServerConnection"));
                    break;
                case "PostgreSQL":
                    options.UseNpgsql(configuration.GetConnectionString("PostgresConnection"));
                    break;
                case "MySQL":
                    options.UseMySql(configuration.GetConnectionString("MySqlConnection"), ServerVersion.AutoDetect(configuration.GetConnectionString("MySqlConnection")));
                    break;
                case "SQLite":
                    options.UseSqlite(configuration.GetConnectionString("MySqlLiteConnection"));
                    break; 
                case "InMemory":
                    options.UseInMemoryDatabase("InMemoryDbForTesting");
                    break; 
                
                default: throw new Exception("Invalid database type");
            }

        });
        
        services.AddScoped<IApplicationDbContext>(sp => sp.GetRequiredService<AppDbContext>());

        return services;
    }

}

The DependencyInjection class provides a centralized way to register different database providers (SQL Server, PostgreSQL, MySQL, SQLite, and In-Memory) based on configuration. This approach allows seamless database switching without modifying the core application logic, improving scalability, cost-effectiveness, and deployment flexibility.

Infrastructure Project

Project

Presentation layer (WebAPI)

For the above dynamic database configuration, we will add the configuration values in appsettings.json.

appsettings.json

{
  "Database": {
    "Type": "SqlServer"
  },
  "ConnectionStrings": {
    "SqlServer": "Server=.;Database=MyDb;Trusted_Connection=True;MultipleActiveResultSets=true",
    "PostgreSQL": "Host=localhost;Database=mydb;Username=myuser;Password=mypassword",
    "MySQL": "Server=localhost;Database=mydb;User=root;Password=mypassword;",
    "SQLite": "Data Source=mydatabase.db"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Add UserController.cs under the Controllers folder.

using Application.Users.Create;
using MediatR;
using Microsoft.AspNetCore.Mvc;

namespace WebAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class UserController (IMediator mediator) : ControllerBase
    {
        private readonly IMediator _mediator = mediator;

        [HttpPost]
        public async Task<ActionResult<Guid>> CreateUser(CreateUserCommand command)
        {
            var userId = await _mediator.Send(command);

            return Ok(userId);
        }

    }
}

Register services in the program.cs.

builder.Services
    .AddApplication()
    .AddInfrastructure(builder.Configuration);

....

app.MapControllers();

Complete program.cs file.

using Application;
using Infrastructure;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
// Learn more about configuring OpenAPI at https://aka.ms/aspnet/openapi
builder.Services.AddOpenApi();

builder.Services
    .AddApplication()
    .AddInfrastructure(builder.Configuration);

builder.Services.AddEndpointsApiExplorer();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.MapOpenApi();
}
app.MapControllers();

app.UseHttpsRedirection();

app.Run();

WebAPI Project

Web API

That’s all. Now, we did the dynamic database for the application. You can do configuration in the database and do migrations accordingly.

Complete Project Structure

Dependencies

Conclusion

Implementing dynamic database selection in a .NET 8 Web API using Clean Architecture, DDD, MediatR, and Entity Framework Core provides a flexible, scalable, and future-proof solution for modern applications. By leveraging appsettings.json to configure different database providers, developers can easily switch between SQL Server, PostgreSQL, MySQL, SQLite, and In-Memory databases without altering the core application logic.

This approach enhances maintainability, cost-efficiency, and adaptability, making it ideal for multi-tenant applications, microservices, and cloud-based deployments. Whether you’re developing for local testing, production environments, or multi-database support, this architecture ensures seamless database integration.

By adopting this strategy, your application remains robust, vendor-independent, and ready to scale, empowering developers to build high-quality, dynamic APIs with ease.