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

When I moved [eCommerceMVCCore, my ASP.NET Core e-commerce project](https://rgkjobber.gumroad.com/l/zkcwon), 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:

```csharp
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:

```csharp
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:

```csharp
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](https://rgkjobber.gumroad.com/l/zkcwon) 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:

```bash
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](https://rgkjobber.gumroad.com/l/zkcwon) 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](https://rgkjobber.gumroad.com/l/zkcwon).

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