Step-by-Step Migration: SQL Server to PostgreSQL for ASP.NET Core

I am primarily a .NET developer with extensive experience writing applications on the Microsoft stack. In the past, this meant being largely tied to a Windows environment. With the maturation of .NET Core, I can now develop cross-platform solutions more easily, and my final leap is to move away from SQL Server to a database solution that runs natively on macOS. This blog post describes how I migrated my ASP.NET Core project from SQL Server to PostgreSQL, focusing on the reasons for the change, the considerations I weighed beforehand, and a step-by-step overview of the migration process. Throughout, I also note specific changes from SQL Server syntax and data types to their PostgreSQL equivalents.

---

1. Before Migration: Original Stack

1. Operating System: Windows (with plans to move to macOS).
2. Framework: ASP.NET Core Web API with multiple endpoints.
3. ORM: Entity Framework (EF) Core.
4. Database: SQL Server.
5. Frontend: React app consuming the API.
6. Tools: Visual Studio Code, Azure Data Studio.
7. Hosting: Primarily Azure, though open to AWS.

---

2. Considerations Beforehand

• Cross-Platform Development: Since SQL Server runs best on Windows, continuing to use it on macOS requires running it via Docker or a VM, which adds overhead.
• Cloud Flexibility: I wanted to ensure that the backend would be easily deployable on both Azure and AWS.
• Entity Framework Core Support: Finding a database that integrates seamlessly with EF Core was essential.
• Limited Existing Data: My project’s data was mostly development or sample data, so I didn’t need a complex migration tool for massive data.
• Consumer-Agnostic API Design: One of my goals was to ensure no changes were needed in my React frontend since the API is consumer-agnostic. Testing through both Postman and the React app confirmed that the migration wouldn’t affect API consumption.
• Tools: I evaluated whether my current tools (VS Code, Azure Data Studio) would support new databases efficiently.
• Hosting Options: I needed a solution with strong managed service offerings on both Azure and AWS.

---

3. Exploring Database Alternatives

Before settling on PostgreSQL, I explored several other database options to determine the best fit for my needs:

1. MySQL/MariaDB: Popular open-source relational databases. While viable, they lacked some advanced features that I valued.
2. SQLite: Lightweight and serverless, great for development but not scalable for production environments.
3. MongoDB: NoSQL database offering flexibility with unstructured data. However, adapting my relational data model would have required significant API changes.
4. Amazon DynamoDB: Scalable and fully managed, but its NoSQL model didn’t align with my current data structure.
5. Azure Cosmos DB: Versatile with multiple APIs but complex and costly for my use case.

Ultimately, PostgreSQL stood out due to its cross-platform compatibility, strong EF Core integration, and minimal disruption to my API and frontend.

---

4. After Migration: Updated Stack

1. Operating System: Windows (ready for future macOS transition).
2. Framework: ASP.NET Core Web API with multiple endpoints.
3. ORM: Entity Framework (EF) Core with Npgsql.
4. Database: PostgreSQL.
5. Frontend: React app consuming the API.
6. Tools: Visual Studio Code, Azure Data Studio, pgAdmin.
7. Hosting: Azure Database for PostgreSQL or Amazon RDS for PostgreSQL.

---

5. The Migration Plan

5.1 Schema Setup

Since I had only a few tables with relatively little data, I opted for a manual rewrite of my SQL Server schema. For example:

In SQL Server:

CREATE TABLE [dbo].[Vendors] (
    [Id] INT IDENTITY(1,1) NOT NULL,
    [CompanyName] NVARCHAR(MAX) NOT NULL,
    PRIMARY KEY ([Id])
);

Converted to PostgreSQL:

CREATE TABLE public."Vendors" (
    "Id" SERIAL PRIMARY KEY,
    "CompanyName" TEXT NOT NULL
);

5.2 Installing PostgreSQL on Windows

I downloaded the installer from the official PostgreSQL site and installed with default settings. This gave me:

• PostgreSQL Server (running on port 5432 by default).
• pgAdmin for GUI-based database management.
• psql command-line interface.

5.3 Configuring ASP.NET Core

1. Replace EF Core Provider: I replaced Microsoft.EntityFrameworkCore.SqlServer with Npgsql.EntityFrameworkCore.PostgreSQL in my .NET project.
2. Update Connection Strings: In appsettings.json, the SQL Server connection string of the form "Data Source=...;Initial Catalog=trep;..." changed to "Host=localhost;Port=5432;Database=trep;Username=postgres;Password=...".
3. DbContext Changes: Instead of options.UseSqlServer(...), I used options.UseNpgsql(...). No major changes to model attributes were needed unless the schema changed significantly.

5.4 Re-Scaffolding or Manual Model Updates

Since I already used scaffolding, I updated my script to:

• Point at the Npgsql provider: Npgsql.EntityFrameworkCore.PostgreSQL.
• Use the new connection string.
• Reference the public schema instead of [dbo].

Running dotnet ef dbcontext scaffold pulled the PostgreSQL schema into my TrepContext and model classes.

5.5 Validation

1. Test Queries: In PostgreSQL, queries referencing table names with uppercase letters require double quotes, for example: SELECT * FROM public."VendorReferences";.
2. CRUD Operations: I tested creation, retrieval, updates, and deletions in my ASP.NET Core controllers to ensure all endpoints still worked.
3. React App Testing: Since my API is consumer-agnostic, I verified that my React frontend functioned without any modifications. This was a key indicator that the migration did not disrupt the API contract.
4. Postman Testing: Used Postman to test individual endpoints, confirming consistent behavior pre- and post-migration.

---

6. Conclusion

Migrating from SQL Server to PostgreSQL proved straightforward in my scenario, thanks in part to the small amount of data and the robust support for PostgreSQL in EF Core. By understanding the key differences—particularly around data types and naming conventions—I was able to adapt my schema and scaffold new models quickly. Best of all, this move positions my codebase to run seamlessly on both Windows and macOS, fulfilling my primary goal of building a more flexible, cross-platform environment.

Looking ahead, hosting is easy to configure on Azure or AWS through their managed PostgreSQL services, ensuring that I can choose whichever cloud is most beneficial or cost-effective without sacrificing my tech stack. If you’re contemplating a similar migration, take time to review your schema, explore different database options, and test thoroughly—once you’ve done that, you’ll likely find PostgreSQL a reliable and powerful alternative to SQL Server for ASP.NET Core projects.