Programming QuickApp

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.
  1. 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"
  1. 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));


builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(connectionString, b => b.MigrationsAssembly(migrationsAssembly));
  • Make a similar modification in the DesignTimeDbContextFactory.cs file. Replace the lines:
    b => b.MigrationsAssembly(migrationsAssembly));


    b => b.MigrationsAssembly(migrationsAssembly));
  1. 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
  1. Update your data models and queries:
  • Review your data models and queries to ensure they are compatible with PostgreSQL.
  1. 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.

You Might Also Like...

No Comments

    Leave a Reply