EF Core with SQL Server, PostgreSQL, MySQL and SQLite in one codebase

When I moved eCommerceMVCCore, my ASP.NET Core e-commerce project, to .NET 10, I wanted buyers to use whichever database they already run. Agencies often have a SQL Server licence, startups like PostgreSQL, cheap hosting usually means MySQL, and SQLite is perfect for a quick local try.

Here's how I made the provider a single config value, and the pitfalls I hit along the way.

Pick the provider from configuration

The app reads one setting, DatabaseProvider, and registers the right EF Core provider. A simplified version:

var provider = builder.Configuration["DatabaseProvider"] ?? "SqlServer";
var connectionString = builder.Configuration.GetConnectionString(provider);

builder.Services.AddDbContext<AppDbContext>(options =>
{
    switch (provider)
    {
        case "SqlServer":  options.UseSqlServer(connectionString); break;
        case "PostgreSQL": options.UseNpgsql(connectionString);    break;
        case "MySQL":      options.UseMySQL(connectionString);     break; // MySql.EntityFrameworkCore
        case "Sqlite":     options.UseSqlite(connectionString);    break;
        default: throw new InvalidOperationException($"Unknown DatabaseProvider '{provider}'.");
    }
});

Fail loudly on an unknown value. A typo that silently falls back to another database is much harder to debug.

Keep migrations per provider

Each provider generates different SQL, so one set of migrations can't serve all four. Keep a separate migrations set for each provider (for example in separate folders or assemblies), and apply the right one at startup:

using var scope = app.Services.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
db.Database.Migrate();
// then seed demo data if the database is empty

Running migrations and seeding on first start means a buyer can clone, run and see a working store without a manual setup step.

Pitfalls I hit

Decimal precision. Prices need an explicit precision, or each provider picks its own default:

modelBuilder.Entity<Product>()
    .Property(p => p.Price)
    .HasPrecision(18, 2);

SQLite and decimals. SQLite has no native decimal type. EF Core can't translate some decimal operations, such as ordering or summing in SQL, so test your sorting and totals on SQLite specifically.

PostgreSQL and DateTime. Npgsql maps DateTime to timestamp with time zone and expects UTC values. Store DateTime.UtcNow everywhere and convert for display.

Case sensitivity. PostgreSQL string comparisons are case-sensitive by default, while SQL Server's usual collation is not. A search box that works on SQL Server can miss results on PostgreSQL, so normalise the case in your search queries.

Try it yourself: the complete ASP.NET Core store ships with all four EF Core providers configured and a migration set for each.

Docker makes it easy to test all four

I added one Docker Compose profile per database, so testing a provider is one command:

docker compose --profile postgres up --build   # or sqlserver / mysql / sqlite

Each database container has a healthcheck, and the app waits for it before migrating. That removed the "app started before the database was ready" errors.

Was it worth it?

Supporting four databases costs some extra testing on every change. In return, the project fits into whatever stack a client already has, which matters a lot when you sell source code.

FAQ

Can one EF Core DbContext support multiple database providers?

Yes. The same DbContext and model work with every provider; you choose the provider at startup. What can't be shared is the migrations, because each provider generates different SQL, so keep one migrations set per provider.

Which database is best for an ASP.NET Core e-commerce site?

SQL Server fits teams already on Azure or Windows. PostgreSQL is a strong free choice for production. MySQL is what most budget hosting offers. SQLite is ideal for local development and demos, but not for a busy production store.

How do I switch EF Core from SQL Server to PostgreSQL?

Install Npgsql.EntityFrameworkCore.PostgreSQL, call UseNpgsql instead of UseSqlServer, generate PostgreSQL migrations, and store every DateTime in UTC. eCommerceMVCCore does this with a single DatabaseProvider setting.

Get the source code

The full project is eCommerceMVCCore, a production-ready ASP.NET Core 10 e-commerce platform with a storefront, admin dashboard, REST API and PayPal payments. See the live demo, documentation and source code here.

Questions about any of the pitfalls above? Ask in the comments.