Use .NET and Entity Framework Core with ClickHouse Managed Postgres

Connect an ASP.NET Core minimal API to ClickHouse Managed Postgres with Entity Framework Core and Npgsql, run EF Core migrations, and query over verified TLS

Entity Framework Core (EF Core) is the ORM for .NET, and Npgsql is its PostgreSQL provider. In this guide, you build an ASP.NET Core minimal API, create its table with an EF Core migration, and serve create, read, update, and delete endpoints. Every connection uses TLS with full certificate verification.

Prerequisites

• The .NET SDK 10. This guide was tested with .NET SDK 10.0.401, EF Core 10.0.12, and Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 (Npgsql 10.0.3).
• A ClickHouse Cloud account
• psql, to create the database. You can also run the CREATE DATABASE statement in the SQL console.

Create a ClickHouse Managed Postgres service

In the ClickHouse Cloud console, click New service and select Postgres. The instance is ready in a few minutes. See the quickstart for a walkthrough.

Get your connection details

Click Connect in the left sidebar of your service. Keep Directly selected, turn on Use SSL, and copy the password and the server hostname. Then click Download CA certificate. You can also download the certificate later from Settings → CA Certificate. It’s unique to your instance, so Npgsql can use it to verify that it’s talking to your server.

This guide connects directly to Postgres on port 5432, for both the app and EF Core migrations. An ASP.NET Core app is a long-lived process, and Npgsql already pools connections in it (up to 100 per connection string by default), so it doesn’t need a second pooler. If you run many instances of the app, see Use PgBouncer.

The Connect modal has no .NET tab, and Npgsql doesn’t accept the postgresql:// URL from the url tab. It uses Keyword=Value connection strings instead. Take the values from the URL and map them to Npgsql keywords:

Connect modal (url tab)	Npgsql keyword
Host, for example your-instance.pg.clickhouse.cloud	Host=your-instance.pg.clickhouse.cloud
Port: 5432 (Directly) or 6432 (via PgBouncer)	Port=5432
Path, for example /postgres	Database=postgres
User and password	Username=postgres;Password=<PASSWORD>
sslmode=verify-full	SSL Mode=VerifyFull
sslrootcert=<service-name>-ca-certificate.pem	Root Certificate=ca-certificate.pem

Set up the project

Create an ASP.NET Core project, add the Npgsql EF Core provider and the EF Core design-time package, and install the dotnet ef command-line tool:

dotnet new web -o TodoApi
cd TodoApi
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef

Adding to an existing app?

Skip dotnet new web. If your app uses EF Core with SQLite or another provider:

• Run dotnet remove package Microsoft.EntityFrameworkCore.Sqlite and dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL. Also install Microsoft.EntityFrameworkCore.Design and the dotnet-ef tool if you don’t have them.
• In Program.cs, replace UseSqlite with UseNpgsql, and remove the SQLite connection string from appsettings.json.
• Migrations are provider-specific, so delete the Migrations folder. You create a new initial migration in Create the schema.

Then continue with Configure the connection, using your app’s connection string name instead of TodoDb, and skip Define the model.

On macOS, turn on TLS 1.3

ClickHouse Managed Postgres only accepts TLS 1.3. By default, .NET on macOS doesn’t support TLS 1.3, so every connection fails with Exception while performing SSL handshake and the inner error bad protocol version. .NET 10 can use Apple’s Network.framework instead, which supports TLS 1.3. Add this to the <Project> element of TodoApi.csproj:

<ItemGroup>
  <RuntimeHostConfigurationOption Include="System.Net.Security.UseNetworkFramework" Value="true" />
</ItemGroup>

The setting applies to the app and to dotnet ef, and has no effect on Linux or Windows. You can also set the environment variable DOTNET_SYSTEM_NET_SECURITY_USENETWORKFRAMEWORK=1 instead.

Configure the connection

Move the CA certificate you downloaded into the project directory and rename it to ca-certificate.pem.

Create a database for the app. Replace <PASSWORD> and the host with the values from the Connect modal:

psql "postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/postgres?sslmode=verify-full&sslrootcert=ca-certificate.pem" -c "CREATE DATABASE guide_dotnet;"

CREATE DATABASE

Store the connection string with the Secret Manager, so that the password stays out of appsettings.json and source control:

dotnet user-secrets init
dotnet user-secrets set "ConnectionStrings:TodoDb" "Host=your-instance.pg.clickhouse.cloud;Port=5432;Database=guide_dotnet;Username=postgres;Password=<PASSWORD>;SSL Mode=VerifyFull;Root Certificate=ca-certificate.pem"

ASP.NET Core reads user secrets in the Development environment, which dotnet run and dotnet ef use by default. In production, set the environment variable ConnectionStrings__TodoDb instead.

SSL Mode=VerifyFull makes Npgsql check that the server certificate is signed by the CA in Root Certificate and that it matches the hostname. Npgsql resolves a relative Root Certificate path against the current working directory, which is the project directory when you use dotnet run and dotnet ef. Use an absolute path if you start the app from somewhere else.

If the CA is wrong or missing, or the host doesn’t match the certificate (for example, when you connect by IP address), the connection fails with Exception while performing SSL handshake and the inner error The remote certificate was rejected by the provided RemoteCertificateValidationCallback. If the Root Certificate file doesn’t exist, the inner error is Could not find file. Also note that:

• Npgsql’s default is SSL Mode=Prefer, and SSL Mode=Require doesn’t verify the certificate either, even when you set Root Certificate. Always set SSL Mode=VerifyFull.
• Npgsql reads the PGSSLROOTCERT and PGPASSWORD environment variables, but not PGHOST or PGSSLMODE. The variables from the env tab of the Connect modal therefore aren’t enough on their own.

Define the model

Create Todo.cs with the entity class:

Todo.cs

public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public bool IsDone { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
}

Create TodoDb.cs with the DbContext:

TodoDb.cs

using Microsoft.EntityFrameworkCore;

public class TodoDb(DbContextOptions<TodoDb> options) : DbContext(options)
{
    public DbSet<Todo> Todos => Set<Todo>();
}

Replace the contents of Program.cs. It registers TodoDb with the Npgsql provider and maps one endpoint per operation. In an existing app, you only need the AddDbContext call with UseNpgsql:

Program.cs

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<TodoDb>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("TodoDb")));

var app = builder.Build();

app.MapGet("/todos", async (TodoDb db) =>
    await db.Todos.OrderBy(t => t.Id).ToListAsync());

app.MapGet("/todos/{id}", async (int id, TodoDb db) =>
    await db.Todos.FindAsync(id) is Todo todo ? Results.Ok(todo) : Results.NotFound());

app.MapPost("/todos", async (Todo todo, TodoDb db) =>
{
    db.Todos.Add(todo);
    await db.SaveChangesAsync();
    return Results.Created($"/todos/{todo.Id}", todo);
});

app.MapPut("/todos/{id}", async (int id, Todo input, TodoDb db) =>
{
    var todo = await db.Todos.FindAsync(id);
    if (todo is null) return Results.NotFound();

    todo.Title = input.Title;
    todo.IsDone = input.IsDone;
    await db.SaveChangesAsync();
    return Results.Ok(todo);
});

app.MapDelete("/todos/{id}", async (int id, TodoDb db) =>
    await db.Todos.Where(t => t.Id == id).ExecuteDeleteAsync() == 1
        ? Results.NoContent()
        : Results.NotFound());

// Reports the TLS version of the app's own database session
app.MapGet("/db/tls", async (TodoDb db) =>
    await db.Database
        .SqlQuery<string>($"SELECT coalesce(version, 'none') AS \"Value\" FROM pg_stat_ssl WHERE pid = pg_backend_pid()")
        .SingleAsync());

app.Run();

The /db/tls endpoint reads pg_stat_ssl for the app’s own database session, so you can check that the connection is encrypted.

Create the schema

Generate a migration from the model and apply it:

dotnet ef migrations add InitialCreate
dotnet ef database update

Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
Build started...
Build succeeded.
Failed executing DbCommand (53ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Applying migration '20261001144437_InitialCreate'.
Done.

dotnet ef migrations add writes the migration to the Migrations folder, and dotnet ef database update applies it and records it in the __EFMigrationsHistory table. On the first run, EF Core logs a failed SELECT from __EFMigrationsHistory before it creates the table; you can ignore it.

Before applying migrations, EF Core takes a lock so that two deployments can’t migrate the same database at the same time. With Npgsql, the lock is a LOCK TABLE on __EFMigrationsHistory inside the migration transaction, not a session-level advisory lock, so it’s released when the transaction ends.

Run and verify

Start the app on port 3115:

dotnet run --urls http://localhost:3115

info: Microsoft.Hosting.Lifetime[14]
      Now listening on: http://localhost:3115

In a second terminal, create two todos, mark the first one done, and delete the second:

curl -w "\n" -X POST localhost:3115/todos -H "Content-Type: application/json" -d '{"title": "Try EF Core"}'
curl -w "\n" -X POST localhost:3115/todos -H "Content-Type: application/json" -d '{"title": "Write a migration"}'
curl -w "\n" -X PUT localhost:3115/todos/1 -H "Content-Type: application/json" -d '{"title": "Try EF Core", "isDone": true}'
curl -w "\n" -X DELETE localhost:3115/todos/2

{"id":1,"title":"Try EF Core","isDone":false,"createdAt":"2026-10-01T14:44:52.0478356Z"}
{"id":2,"title":"Write a migration","isDone":false,"createdAt":"2026-10-01T14:44:52.8462161Z"}
{"id":1,"title":"Try EF Core","isDone":true,"createdAt":"2026-10-01T14:44:52.047835Z"}

The DELETE request returns 204 No Content, so its line is empty.

Check that the app’s database session uses TLS:

curl localhost:3115/db/tls

TLSv1.3

Open http://localhost:3115/todos in your browser to list the remaining todos:

[{"id":1,"title":"Try EF Core","isDone":true,"createdAt":"2026-10-01T14:44:52.047835Z"}]

To see the data in the console, open SQL console in the left sidebar of your service, expand guide_dotnet and then public, and click the Todos table. EF Core uses the class and property names as quoted identifiers, so the table is "Todos" and the columns are "Id", "Title", and so on. The table contains the remaining row:

Id	Title	IsDone	CreatedAt
1	Try EF Core	true	2026-10-01 14:44:52.047835+00

Use PgBouncer

Npgsql opens up to 100 connections per app instance (Maximum Pool Size). If many instances together approach the Postgres connection limit, connect the app through the bundled PgBouncer instead. Select via PgBouncer in the Connect modal, and change the port in the connection string to 6432:

dotnet user-secrets set "ConnectionStrings:TodoDb" "Host=your-instance.pg.clickhouse.cloud;Port=6432;Database=guide_dotnet;Username=postgres;Password=<PASSWORD>;SSL Mode=VerifyFull;Root Certificate=ca-certificate.pem"

The same CA certificate works. PgBouncer runs in transaction pooling mode, so each transaction can run on a different Postgres connection. With the default EF Core and Npgsql settings, you don’t need to change anything else:

• Prepared statements: EF Core doesn’t prepare statements, and Npgsql’s automatic preparation is off by default (Max Auto Prepare=0), so queries use unnamed statements, which work through PgBouncer. Keep it that way. Through PgBouncer, prepared statements from Max Auto Prepare or NpgsqlCommand.Prepare fail intermittently with 08P01: prepared statement did not exist (No Reset On Close=true prevents this), and after a migration changes a column’s type, they keep failing with cached plan must not change result type, even on new connections. If you need prepared statements, connect directly.
• Session state: settings made with SET, including search_path, don’t carry over between transactions. Use SET LOCAL inside a transaction instead. PgBouncer rejects the Search Path and Options connection string keywords with unsupported startup parameter; Timezone and Application Name work.
• Migrations: dotnet ef database update also works through PgBouncer, because the migration lock is a table lock that ends with its transaction.
• TLS check: pg_stat_ssl describes the connection between PgBouncer and Postgres, so /db/tls returns none. Your app’s connection to PgBouncer is still verified: a wrong CA certificate fails in the same way.

Next steps

• Connection: connection strings, PgBouncer, and TLS
• Settings: Postgres and PgBouncer parameters, such as max_connections
• Read replicas: send read-only queries to a replica
• Security: IP access lists and private networking