Migrating an ASP.NET Core Web App / Web API from SQL Server to PostgreSQL

To convert an ASP.NET Core Web API application that uses an SQL Server database to a PostgreSQL database, you can follow these steps. We’ll use the QuickApp ASP.Net Core web API project template as a concrete example for this migration :

Install the required PostgreSQL NuGet packages:

In your ASP.NET Core project, install the Npgsql.EntityFrameworkCore.PostgreSQL package using the NuGet Package Manager or the dotnet add package command.
This package provides the necessary PostgreSQL provider for Entity Framework Core.

Update the database configuration:

In your appsettings.json file, update the connection string to point to your PostgreSQL database instead of the SQL Server database.
The connection string for PostgreSQL might look something like this:

   "ConnectionStrings": {
     "DefaultConnection": "Host=localhost;Database=your_database_name;Username=your_username;Password=your_password"
   }

Update the DbContext configuration:

In your Program.cs (or Startup.cs) file, configure the DbContext to use the PostgreSQL provider for Entity Framework Core.
Replace the following line:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString, b => b.MigrationsAssembly(migrationsAssembly));
    ...
});

with:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseNpgsql(connectionString, b => b.MigrationsAssembly(migrationsAssembly));
    ...
});

Make a similar modification in the DesignTimeDbContextFactory.cs file. Replace the lines:

builder.UseSqlServer(configuration["ConnectionStrings:DefaultConnection"],
    b => b.MigrationsAssembly(migrationsAssembly));
...

with:

builder.UseNpgsql(configuration["ConnectionStrings:DefaultConnection"],
    b => b.MigrationsAssembly(migrationsAssembly));
...

Migrate the database schema:

If you have existing migrations for the SQL Server database, you’ll need to update them to work with PostgreSQL.
You can do this by running the following commands in the Package Manager Console or the CLI:

   # Remove the existing migrations
   dotnet ef migrations remove

   # Add a new migration for PostgreSQL
   dotnet ef migrations add InitialPostgreSQLMigration

   # Update the database to the new PostgreSQL schema
   dotnet ef database update

Update your data models and queries:

Review your data models and queries to ensure they are compatible with PostgreSQL.

Test your application:

Run your ASP.NET Core Web API application and ensure that it’s properly connecting to the PostgreSQL database and performing CRUD operations as expected.