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 :

1. 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.

2. 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"
   }

3. 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));
...

4. 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

5. Update your data models and queries:

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

6. 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.