PostgreSQL with Npgsql and Dapper: NpgsqlDataSource, Type Mapping, COPY and LISTEN/NOTIFY

Version note: The code in this post was built with .NET 10, Npgsql 10.0.3, Npgsql.DependencyInjection 10.0.3, Npgsql.OpenTelemetry 10.0.3, Dapper 2.1.89 and OpenTelemetry 1.21.0 and run on PostgreSQL 18.7 (the official postgres:18 Docker image), in the Bookstore lab we set up in post 02.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, records the shipping time sent by its Istanbul warehouse. The warehouse system sends the time as local time, without any Kind: November 14, 13:00. The code uses Dapper and the column is timestamptz. We also send the same value as an Npgsql parameter with its type set explicitly:

DateTime localNoKind = new(2026, 11, 14, 13, 0, 0);

// Dapper
await connection.ExecuteScalarAsync<string>(
    "SELECT (@value::timestamptz)::text || '  (TimeZone=' || current_setting('TimeZone') || ')'",
    new { value = localNoKind });

// Npgsql, type set explicitly to timestamptz
command.Parameters.Add(new NpgsqlParameter("value", NpgsqlDbType.TimestampTz) { Value = localNoKind });

You've probably heard that since Npgsql 6 only UTC DateTime values can be written to timestamptz. Which of the two lines throws, which one stores a value, and is the stored value correct?

Dapper, Kind=Unspecified -> 2026-11-14 13:00:00+00  (TimeZone=Etc/UTC)
TimestampTz, Kind=Unspecified -> ArgumentException: Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array, range, or multirange. (Parameter 'value')

The explicitly typed parameter threw right away, which is a good thing. Dapper didn't throw and stored 13:00 as UTC: 16:00 Istanbul time, a record shifted by three hours. Dapper sends the DateTime as DbType.DateTime, Npgsql turns that into timestamp without a time zone, and the server converts it to timestamptz using the session's TimeZone setting, UTC in the lab. In this post we cover the basics of accessing PostgreSQL with Npgsql and Dapper: NpgsqlDataSource and dependency injection, the traps of type mapping, PostgreSQL enums, bulk writes, automatic preparation, LISTEN/NOTIFY and OpenTelemetry.

NpgsqlDataSource and Dependency Injection

Since Npgsql 7 the entry point for connections is NpgsqlDataSource. It brings the connection pool, the type mapping settings and logging together in one object, and there should be exactly one for the whole application. In ASP.NET Core it's registered as a singleton with the Npgsql.DependencyInjection package:

HostApplicationBuilder builder = Host.CreateApplicationBuilder();
builder.Services.AddNpgsqlDataSource(connectionString);
using IHost host = builder.Build();

NpgsqlDataSource dataSource = host.Services.GetRequiredService<NpgsqlDataSource>();
await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync();
int[] ids = [1, 2, 3, 40000];
IEnumerable<BookRow> books = await connection.QueryAsync<BookRow>(
    "SELECT book_id AS BookId, title, price FROM books WHERE book_id = ANY(@ids) ORDER BY book_id", new { ids });

$env:BOOKSTORE_PG = "Host=localhost;Port=5432;Database=bookstore;Username=bookstore_app;Password=<app-password>"
docker exec -it bookstore-pg psql -U bookstore_owner -d bookstore -f /lab/11-npgsql.sql
dotnet run --project src/Bookstore.PgLab -- defaults

Pooling=True MinPoolSize=0 MaxPoolSize=100 ConnectionIdleLifetime=300s MaxAutoPrepare=0
info: Npgsql.Command[2001]
      Command execution completed (duration=15ms): SELECT book_id AS BookId, title, price FROM books WHERE book_id = ANY($1) ORDER BY book_id
     1 Emma #1                32.77
     2 The Hobbit #2          39.00
     3 Middlemarch #3         35.69
 40000 Dune #40000            38.36

The pool defaults: at most 100 connections, and extra connections idle for 300 seconds are closed. Think of it together with the process-per-connection model from post 01: the number of instances times Max Pool Size must not exceed the server's max_connections. The data source from DI also uses the application's logging; commands show up in the log under the Npgsql.Command category.

If you're used to WHERE id IN @ids from SQL Server, watch out: Dapper expands it into IN (@ids1, @ids2, ...), and every different list length becomes a separate query text. In PostgreSQL it's better to send the array as a single parameter and write = ANY(@ids): the query text stays the same and pg_stat_statements from post 07 groups it into one row. We covered Dapper's connection handling and async usage in the async and connection management post of the Dapper series; the same rules apply here: return the connection to the pool as early as possible with await using.

Type Mapping: timestamptz, DateOnly and jsonb

The rest of the Guess First output:

TimestampTz, Kind=Utc -> 2026-11-14 10:00 Kind=Utc
DateTimeOffset +03:00 -> ArgumentException: Cannot write DateTimeOffset with Offset=03:00:00 to PostgreSQL type 'timestamp with time zone', only offset 0 (UTC) is supported.  (Parameter 'value')
date -> DateOnly 2023-01-01
jsonb from string -> 42804: column "payload" is of type jsonb but expression is of type text
jsonb with NpgsqlDbType.Jsonb -> 1 row

The rules match the timestamptz section of post 03: the column holds an instant, and Npgsql accepts that instant only from a value without ambiguity, meaning Kind=Utc for DateTime and offset 0 for DateTimeOffset. Values are read back with Kind=Utc. Converting local time to UTC is the application's job: TimeZoneInfo.ConvertTimeToUtc, or working with DateTimeOffset/TimeProvider from the start (the time and time zone post of the Çözüm Desenleri series). If you work with Dapper, the way to avoid the silent shift from the guess is to convert the value to UTC and send it with Kind=Utc; then Npgsql sends the parameter as timestamptz.

A change that came with Npgsql 10: date columns are now read as DateOnly and time columns as TimeOnly by default; old code that expects DateTime gets a cast error. On the jsonb side, a .NET string goes out as text, and PostgreSQL doesn't convert text to jsonb on its own. Either set the parameter type to NpgsqlDbType.Jsonb or write @payload::jsonb in the SQL with Dapper.

PostgreSQL Enums and Dapper

In post 03 we compared an enum type with a lookup table and a CHECK. If you decided on an enum, you tell Npgsql to map it to a C# enum at the data source level; names are translated to snake_case by default, so Audiobook becomes audiobook:

NpgsqlDataSourceBuilder builder = new(connectionString);
builder.MapEnum<BookFormat>("book_format");
await using NpgsqlDataSource dataSource = builder.Build();

stored as 'audiobook'
Dapper with a C# enum -> 42804: column "format" is of type book_format but expression is of type integer
label as text + cast -> Ebook, Audiobook

Writing with Npgsql's own parameter (AddWithValue("format", BookFormat.Audiobook)) works fine. Dapper, however, converts enum parameters to the underlying int regardless of the provider, and PostgreSQL doesn't convert an integer to an enum. Dapper's type handlers don't kick in for enum parameters either; we tried, and got the same error. The practical fix is to send the label as text and cast it on the server: @format::book_format. Reading is no problem; thanks to the mapping, Npgsql returns the value as BookFormat.

Bulk Writes: Execute(list), NpgsqlBatch and Binary COPY

We're importing order events from another system: 100,000 rows with jsonb and timestamptz columns. We compared three methods with the same transaction structure. We saw the basics of binary COPY in the performance post of the Dapper series; here we give every column's type explicitly, because in binary format the server doesn't guess types:

await using NpgsqlBinaryImporter importer = await connection.BeginBinaryImportAsync(
    "COPY order_events (order_id, event_type, payload, occurred_at) FROM STDIN (FORMAT BINARY)");
foreach (OrderEvent e in events)
{
    await importer.StartRowAsync();
    await importer.WriteAsync(e.OrderId, NpgsqlDbType.Integer);
    await importer.WriteAsync(e.EventType, NpgsqlDbType.Text);
    await importer.WriteAsync(e.Payload, NpgsqlDbType.Jsonb);
    await importer.WriteAsync(e.OccurredAt, NpgsqlDbType.TimestampTz);
}
ulong written = await importer.CompleteAsync();

Dapper Execute(list): 12947 ms
NpgsqlBatch (1000 per batch): 858 ms
binary COPY: 90 ms, 100000 rows

The durations depend on the lab machine, but the ratios are meaningful. Dapper's Execute with a list means a separate INSERT and a separate round trip for every row. NpgsqlBatch sends 1000 commands in one round trip and is 15 times faster; since the commands still run one by one, it's a good middle ground when you want the IDs generated with RETURNING. COPY is another 10 times faster and the first choice for bulk loads. If CompleteAsync isn't called, nothing is written. COPY doesn't skip constraints and triggers, but a single bad row cancels the whole load; if you need row-by-row error reports, load into a staging table first and validate with SQL (the bulk import post of the Çözüm Desenleri series).

Automatic Preparation

PostgreSQL parses and plans every query. A prepared statement does that once and reuses it on later executions. Npgsql can do this automatically with Max Auto Prepare in the connection string; the default is 0, off. When it's on, a query text is prepared on that connection after it has run 5 times (Auto Prepare Min Usages). We ran the same query 5,000 times:

Max Auto Prepare=0 : 5000 queries in 928 ms, prepared statements on the server: 0
Max Auto Prepare=20: 5000 queries in 561 ms, prepared statements on the server: 1

A clear gain for short, frequent queries. Prepared statements belong to the connection; Npgsql keeps them when the connection goes back to the pool and is reused. If you put a PgBouncer running in transaction mode in between, check the setting for prepared statement support; that topic is in the Veritabanı Performansı ve Scaling series.

Cache Invalidation with LISTEN/NOTIFY

Book prices are cached in memory in the application; when a price changes, every instance needs to know. 11-npgsql.sql installs a trigger that calls pg_notify('catalog_changed', book_id) when books.price changes. The application listens on a separate connection:

listener.Notification += (sender, e) =>
{
    int bookId = int.Parse(e.Payload, CultureInfo.InvariantCulture);
    priceCache.TryRemove(bookId, out _);
    Console.WriteLine($"notification on '{e.Channel}': book {bookId} evicted from the cache");
};
await using (NpgsqlCommand listen = new("LISTEN catalog_changed", listener))
{
    await listen.ExecuteNonQueryAsync();
}

notification on 'catalog_changed': book 1 evicted from the cache
cache entries left: 0

We made two price updates: book 1 was committed, book 2 was rolled back. Only the first was notified; NOTIFY is sent when the transaction commits. The listening connection waits with WaitAsync; this connection shouldn't be taken from the pool and given back, it should stay open for the life of the application.

The limits are clear: a notification goes only to connections listening at that moment and is stored nowhere. If the listening instance is restarting at that time, it misses the notification. The payload must be shorter than 8000 bytes, and identical notifications on the same channel with the same payload in one transaction are folded into one. That's why LISTEN/NOTIFY fits signals like "clear the cache", where missing one isn't a disaster; clearing the whole cache on reconnect and giving cache entries a TTL close that gap. Reliable messaging needs the outbox from post 09 and a message broker.

Tracing with OpenTelemetry

The Npgsql.OpenTelemetry package publishes every command as a span. Npgsql 10 moved these spans to OpenTelemetry's stable database naming; check the attribute names in dashboards built for older versions:

using TracerProvider tracing = Sdk.CreateTracerProviderBuilder()
    .AddNpgsql()
    .AddConsoleExporter()
    .Build();

Activity.DisplayName:        postgresql
Activity.Kind:               Client
Activity.Tags:
    db.query.text: SELECT book_id AS BookId, title, price FROM books WHERE book_id = @id
    db.system.name: postgresql
    db.namespace: bookstore

In an ASP.NET Core application the same line is added with AddOpenTelemetry().WithTracing(t => t.AddNpgsql()), and every SQL command shows up as its own span under the span of the HTTP request. The query text has no parameter values; to log values you have to turn on EnableParameterLogging() on the data source, and keep it off in production because it can leak personal data.

Production Notes

Add Application Name=bookstore-api to the connection string; pg_stat_activity and the logs then show which connection belongs to which service. Choose Timeout (getting a connection, 15 seconds by default) and Command Timeout (30 seconds by default) to fit the server-side timeouts from post 09.

Pool exhaustion shows up in the application as "The connection pool has been exhausted"; don't confuse it with the server's 53300 error from post 01. The first is the application's limit, the second the server's.

A long COPY is a single transaction; loading millions of rows in one go means a long transaction and the VACUUM effect from post 08. Split very large loads into pieces.

Common Pitfalls

Sending DateTime.Now or a value with an unspecified Kind: as in the guess, it silently shifts with Dapper. Convert every time to UTC at the application boundary, such as API input or message consumption.

Creating a new NpgsqlDataSource per request: every data source carries its own pool; creating one per request makes the pool pointless and every request starts a new server process.

Treating NOTIFY as reliable messaging: it isn't durable, isn't redelivered, and is lost if nobody is listening.

Calling Write(value) in COPY without a type: if the .NET type doesn't fit the column type (double instead of decimal, string instead of jsonb), the error only shows up on the server, during the load.

A Small Experiment

★☆☆ Add Timezone=Europe/Istanbul to the connection string and run the Dapper line from the guess again. What value was stored? Does this setting solve the problem, or just move it somewhere else?

★★☆ Add a fourth method to the copy demo: send all values as four array parameters and run INSERT INTO order_events (...) SELECT * FROM unnest(@orderIds, @types, @payloads::jsonb[], @times). Compare it with NpgsqlBatch and COPY.

★★★ In the listen demo, close the listening connection on purpose (pg_terminate_backend), update a price meanwhile, and reconnect. How do you make up for the missed notification? Build something that clears the cache on reconnect.

In the next post we connect to the same database with EF Core: the Npgsql provider, snake_case naming, array and JSON mapping, enums, concurrency with xmin, and migration differences.