Getting error connecting a Web API to PostgreSQL

Question

I am switching from SQL Server to PostgreSQL, and have a Web API that I have updated as follows.

In the appsettings.json file, I have:

{
    "ConnectionStrings": {
        "MPmain": "Data Source=DESKTOP-FCT8VC2;Initial Catalog=mpau;Integrated Security=True;TrustServerCertificate=true",
        "MPpostgres1": "User ID=postgres;Password=xxxxxxxx;Server=localhost;Port=5432;Database=mpau;",
        "MPpostgres": "host=localhost port=5432 dbname=mpau user=postgres password=xxxxxxxx connect_timeout=10 sslmode=prefer"
    },
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*"
}

And in the Program.cs file, I have:

using Microsoft.EntityFrameworkCore;
using Npgsql;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

//added
builder.Services.AddCors(options =>
{
    options.AddDefaultPolicy(
        policy =>
        {
            policy.AllowAnyOrigin();
            policy.AllowAnyMethod();
            policy.AllowAnyHeader();
        });
});

//added
builder.Services.AddDbContext<mpauDbContext>(options =>
{
    builder.Configuration.GetConnectionString("MPpostgres1");
    options.UseNpgsql("MPpostgres1");

});


builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

I was able to create the data migrations, however when I try update-database, I get an error stating:

Format of the initialization string does not conform to specification at index 0"

In the appsettings.json file, MPmain was the string used for SQL Server (which worked fine), and I also tried putting the computer name instead of localhost in both MPpostgres1 and MPpostgres strings, also with no change. MPpostgres was the connection string taken directly from pgAdmin4, copy/pasted.

Most search results state that it is likely caused by an error in the connection string, but I have tried multiple changes, and I followed a tutorial where the string used was almost identical (with the exception of username and password, and I will be trying Kerberos authentication later to remove this from the connection string).

UPDATE: I tested all of the above on the .NET 7 webAPI version, including the connection string MPpostgres1, and was able to complete the migration and generate the tables. The error appears to be specific to the .NET 8 version of the webAPI.

Any help in resolving this error would be greatly appreciated.

Answer 1

I was able to resolve the issue by changing the parsing of the configuration settings in the Program.cs file. The solution was to use:

options.UseNpgsql(builder.Configuration.GetConnectionString("MPpostgres1"));

instead of:

builder.Configuration.GetConnectionString("MPpostgres1");
options.UseNpgsql("MPpostgres1");

I believe this is due to the way configuration is handled differently between .NET versions. Hopefully this resolution helps anyone experiencing a similar error.