skip to content

In an ASP.NET Core 10 API using Npgsql without EF Core, how do you register a data source and run a query from a handler?

level: juniorimportance: must knowfreq 60%

answer

  1. package Npgsql.DependencyInjection
  2. AddNpgsqlDataSource(connectionString)
  3. data source owns the pool, Singleton
  4. OpenConnectionAsync returns ValueTask<NpgsqlConnection>
  5. await using on connection, command, reader

basics

~10 s

Add Npgsql.DependencyInjection, call builder.Services.AddNpgsqlDataSource(connectionString), inject NpgsqlDataSource into the handler, and open a pooled connection per query with await using var conn = await dataSource.OpenConnectionAsync(); disposing it hands the connection back to the pool.

solid answer

~40 s

I add the `Npgsql.DependencyInjection` package and call `builder.Services.AddNpgsqlDataSource(connectionString)` in `Program.cs`, with the string read from configuration. That registers one `NpgsqlDataSource` as a Singleton (also exposed as the abstract `DbDataSource`), and it owns the connection pool. A handler injects `NpgsqlDataSource`, opens a connection as late as possible with `await using var conn = await dataSource.OpenConnectionAsync()`, runs a parameterized `NpgsqlCommand`, reads the result with `await using` on the reader, and lets the `await using` blocks hand the physical connection back to the pool. For a single statement, `dataSource.CreateCommand(sql)` is shorter still: it borrows a connection when it executes and returns it when its reader closes. I never build a data source per request and never keep an open connection in a field.

code

xml · 3 lines
xml
<ItemGroup>
  <PackageReference Include="Npgsql.DependencyInjection" Version="10.0.3" />
</ItemGroup>

go deeper

for a junior

Write the AddNpgsqlDataSource line, inject NpgsqlDataSource into a handler and open a connection with await using; say that dispose returns it to the pool.

for a middle

List what AddNpgsqlDataSource registers and with which lifetimes, and explain why the data source must be one per database while connections are short-lived.

for a senior

Show how open-late, dispose-early discipline protects a 100-connection pool under load, and when a data-source command beats an explicit connection.

for a principal

Set the team's data-access convention: one registered data source per database, no hand-built pools, and where raw ADO.NET is allowed next to an ORM.

## The registration Without EF Core, an ASP.NET Core app talks to PostgreSQL through the **Npgsql** ADO.NET driver. The `Npgsql.DependencyInjection` package adds one extension method that does the plumbing: ```csharp var builder = WebApplication.CreateBuilder(args); builder.Services.AddNpgsqlDataSource( builder.Configuration.GetConnectionString("Meters") ?? throw new InvalidOperationException("Connection string 'Meters' not found.")); var app = builder.Build(); ``` A **data source** (`NpgsqlDataSource`, which derives from the BCL's abstract `DbDataSource`) is the object that knows the connection string, owns the **connection pool** and hands out connections. You want exactly one per database for the life of the app. ## What AddNpgsqlDataSource puts in the container | Service | Default lifetime | How it is built | |---|---|---| | `NpgsqlDataSource` | Singleton (`dataSourceLifetime`) | `NpgsqlDataSourceBuilder` with the app's `ILoggerFactory` | | `DbDataSource` | Singleton | forwards to the same `NpgsqlDataSource` | | `NpgsqlConnection` | Transient (`connectionLifetime`) | `dataSource.CreateConnection()`, not yet open | | `DbConnection` | Transient | forwards to the `NpgsqlConnection` registration | An optional `Action<NpgsqlDataSourceBuilder>` overload configures the driver beyond the connection string, and a `serviceKey` argument registers a second database as a keyed service. ## Why a data source rather than a bare connection string Older code passed a connection string around and called `new NpgsqlConnection(cs)` wherever it needed one. That still works, because the driver keeps an internal pool per connection string, but a registered data source is the better shape for a web app: - **one owner** for the pool and every driver setting, configured once at startup instead of in each class; - **logging wired in**: the registration hands the app's `ILoggerFactory` to the `NpgsqlDataSourceBuilder`, so driver logs flow into ASP.NET Core logging without extra code; - **an abstraction for tests and other providers**: code that depends on `DbDataSource` does not care which driver sits behind it; - **several databases side by side**: a second call with a `serviceKey` registers a keyed data source, injected with `[FromKeyedServices]`. ## Running a query in an energy-meter API ```csharp app.MapGet("/meters/{meterId}/latest", async (string meterId, NpgsqlDataSource dataSource) => { await using var conn = await dataSource.OpenConnectionAsync(); await using var cmd = new NpgsqlCommand( "SELECT read_at, kwh FROM meter_readings WHERE meter_id = $1 ORDER BY read_at DESC LIMIT 1", conn); cmd.Parameters.AddWithValue(meterId); await using var reader = await cmd.ExecuteReaderAsync(); return await reader.ReadAsync() ? Results.Ok(new { ReadAt = reader.GetDateTime(0), Kwh = reader.GetDecimal(1) }) : Results.NotFound(); }); ``` `OpenConnectionAsync()` returns a `ValueTask<NpgsqlConnection>` that is already open. The value is bound as a positional parameter (`$1`), never concatenated into the SQL text. ## Open late, dispose early The pool has a fixed number of physical connections (`Max Pool Size`, 100 by default), shared by every concurrent request. So each handler: 1. opens the connection immediately before the first statement, not at the top of a long method; 2. disposes the reader, the command and the connection with `await using`, so an exception path returns the connection too; 3. does not hold the connection across unrelated slow work, such as an outgoing HTTP call. Disposing a pooled `NpgsqlConnection` does **not** close the socket to PostgreSQL; it puts the physical connection back in the pool, and the driver resets the session state before the next borrower's first command. ## The one-statement shortcut `dataSource.CreateCommand(sql)` returns a command bound to the data source rather than to a connection. When it executes, it takes a connection from the pool and adds `CommandBehavior.CloseConnection`, so the connection goes back when the reader is disposed. Such a command cannot be prepared and does not expose its `Connection` or `Transaction`; touching them throws `NotSupportedException`. Use it for single statements, and an explicit connection when several statements or a transaction must share one. ## Mistakes interviewers listen for - building a data source with `NpgsqlDataSource.Create(cs)` inside a handler, which creates and abandons a pool per request; - storing one open `NpgsqlConnection` in a static field or a singleton, which concurrent requests then share; - forgetting `await using` on the reader or connection: `NpgsqlConnection` suppresses finalization, so the garbage collector never hands a forgotten connection back to the pool; - registering the data source with a shorter lifetime than Singleton, which multiplies pools.

  • AddNpgsqlDataSource also registers NpgsqlConnection. When would a handler inject that instead of the data source?
    The registered `NpgsqlConnection` is Transient and created closed by `dataSource.CreateConnection()`; you call `OpenAsync()` yourself, and the request scope disposes it at the end of the request. It suits code that wants exactly one connection object, such as a repository taking a `DbConnection`. Injecting the data source is better when you want to control exactly when a connection is held, or need two at once.
  • Why can't a command from dataSource.CreateCommand(sql) take part in a transaction?
    It owns its connection: it borrows one from the pool when it executes and returns it when its reader closes, so you never see the connection a transaction would be started on. Its `Connection` and `Transaction` properties throw `NotSupportedException`. For a transaction, open a connection with `OpenConnectionAsync()`, call `BeginTransactionAsync()` on it, and create the commands from that connection.

  • Call NpgsqlDataSource.Create(cs) inside each handler to get a fresh connection.
  • Keep one open NpgsqlConnection in a singleton and reuse it for every request.
  • Disposing a pooled NpgsqlConnection closes the socket to PostgreSQL each time.
  • AddNpgsqlDataSource registers the data source as Scoped, one per request.
  • Skip await using on the reader; the garbage collector returns the connection soon enough.