Sa.Data.PostgreSql 0.20.0

dotnet add package Sa.Data.PostgreSql --version 0.20.0
                    
NuGet\Install-Package Sa.Data.PostgreSql -Version 0.20.0
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Sa.Data.PostgreSql" Version="0.20.0" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="Sa.Data.PostgreSql" Version="0.20.0" />
                    
Directory.Packages.props
<PackageReference Include="Sa.Data.PostgreSql" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add Sa.Data.PostgreSql --version 0.20.0
                    
#r "nuget: Sa.Data.PostgreSql, 0.20.0"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#:package Sa.Data.PostgreSql@0.20.0
                    
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.
#addin nuget:?package=Sa.Data.PostgreSql&version=0.20.0
                    
Install as a Cake Addin
#tool nuget:?package=Sa.Data.PostgreSql&version=0.20.0
                    
Install as a Cake Tool

Sa.Data.PostgreSql

English | Русский

Lightweight Npgsql wrapper for common PostgreSQL operations — no ORM overhead, with DI, Native AOT support, and minimal allocations.

Installation

dotnet add package Sa.Data.PostgreSql

Contents

Quick Start

// Option 1: direct creation
var dataSource = IPgDataSource.Create("Host=db;Database=mydb;Username=usr;Password=pwd");
// or from an existing NpgsqlDataSource — shares its connection pool
var dataSource = IPgDataSource.Create(new NpgsqlDataSource("Host=db;Database=mydb;Username=usr;Password=pwd"));

// Option 2: via DI — the connection string goes through the standard options pipeline
services.AddSaPostgreSqlDataSource(b => b
    .Options(ob => ob.Configure(o => o.ConnectionString = "Host=db;Database=mydb;Username=usr;Password=pwd")));

// Option 3: from a configuration section
services.AddSaPostgreSqlDataSource(b => b.FromConfiguration("Postgres"));

Registration (DI)

With DI

AddSaPostgreSqlDataSource takes the standard options callback, so configuration goes through Configure / PostConfigure / Validate like any other options type — not through a bespoke overload:

services.AddSaPostgreSqlDataSource(b => b
    .Options(ob => ob.Configure(o => o.ConnectionString = "Host=db;Database=mydb;Username=usr;Password=pwd")));

// Usage:
var dataSource = serviceProvider.GetRequiredService<IPgDataSource>();

The only option is ConnectionString. Pooling, timeout, and search path are configured inside the string itself (e.g. Host=…;Minimum Pool Size=5;Maximum Pool Size=100;Search Path=storage;Timeout=15).

From a configuration section

// appsettings.json:
// { "Postgres": { "ConnectionString": "Host=…;Database=…;Username=…;Password=…" } }
services.AddSaPostgreSqlDataSource(b => b.FromConfiguration("Postgres"));

The section binds in a fixed slot first, so an Options(...) Configure has the last word. The Options(...) actions replay after the registration's own PostConfigure and Validate, so your checks add to the built-in ones instead of replacing them.

Reusing an existing NpgsqlDataSource

When ConnectionString is empty, the data source is built from an NpgsqlDataSource registered in DI — sharing its connection pool instead of opening a second one. The wrapper never disposes it; ownership stays with the registration:

services.AddSingleton(new NpgsqlDataSource("Host=db;Database=mydb;Username=usr;Password=pwd"));
services.AddSaPostgreSqlDataSource();

On a keyed instance, an empty string likewise falls back to the NpgsqlDataSource registered with the same key.

Multiple databases (keyed)

One container can hold several independent data sources — one per key. Each key gets its own named PgDataSourceOptions instance (its own connection string) and its own keyed IPgDataSource; resolve with GetRequiredKeyedService<IPgDataSource>(key):

services.AddSaPostgreSqlDataSource("orders", b => b
    .Options(ob => ob.Configure(o => o.ConnectionString = "Host=orders;Database=orders;Username=usr;Password=pwd")));
services.AddSaPostgreSqlDataSource("billing", b => b
    .Options(ob => ob.Configure(o => o.ConnectionString = "Host=billing;Database=billing;Username=usr;Password=pwd")));

// Usage:
var orders = serviceProvider.GetRequiredKeyedService<IPgDataSource>("orders");
var billing = serviceProvider.GetRequiredKeyedService<IPgDataSource>("billing");

Named options are read through IOptionsMonitor<PgDataSourceOptions>.Get(key). The default data source (the string.Empty key, also what the keyless call registers) additionally gets an unnamed alias, so GetRequiredService<IPgDataSource>() and GetRequiredKeyedService<IPgDataSource>(string.Empty) return the same instance.

The string.Empty key is the original unnamed data source: the one-argument AddSaPostgreSqlDataSource(configure) delegates to AddSaPostgreSqlDataSource(string.Empty, configure), so existing code is unchanged.

Validation

The connection string is normalised (trimmed) in PostConfigure and then validated on the way out: a non-empty value must parse as a PostgreSQL connection string (checked with the same NpgsqlConnectionStringBuilder Npgsql uses, so a typo in Host= surfaces as an early OptionsValidationException rather than a bare FormatException from the middle of the first query); an empty value is only valid when an NpgsqlDataSource is registered in the collection.

ValidateOnStart() turns a bad configuration into an OptionsValidationException at host start, and the same check fires when IOptions<PgDataSourceOptions>.Value is first read — i.e. on the first data-source resolve — so even a bare ServiceCollection fails fast instead of deep in a query.

The validator is an explicit IValidateOptions<PgDataSourceOptions> rather than ValidateDataAnnotations(): the latter is marked RequiresUnreferencedCode (IL2026) and breaks Native AOT, which this assembly is built for.

Register the data source with configuration only once per key — a second configuring call (one carrying a configure callback — the section comes via FromConfiguration, the settings via Options(...)) for the same key throws InvalidOperationException, because both Configure callbacks would otherwise apply to the same options instance and the settings would silently merge. A call for a different key is always allowed — that is the point of keyed registration. Bare calls (no callback at all) may be repeated by design: AddSaPartitional and AddSaOutboxUsingPostgreSql each register a bare data source, and a single configuring call — wherever it sits — is the one that takes effect.


ExecuteNonQuery

Executes a SQL command that doesn't return data (INSERT / UPDATE / DELETE / DDL) and returns the number of affected rows.

// Simple query
int affected = await dataSource.ExecuteNonQuery("DELETE FROM sessions WHERE expired = true");

// With parameters
int affected = await dataSource.ExecuteNonQuery("""
    INSERT INTO users (name, age) VALUES (@p0, @p1);
    """, [
        new NpgsqlParameter { ParameterName = "p0", Value = "Tom" },
        new NpgsqlParameter { ParameterName = "p1", Value = 18 }
    ]);

ExecuteScalar / ExecuteScalarTyped

Returns the first value of the first row in the result. ExecuteScalarTyped<T> automatically casts the result, including support for Guid, DateTime, DateTimeOffset, and DateOnly → DateTime.

// object? overload
object? count = await dataSource.ExecuteScalar("SELECT COUNT(*) FROM users");

// Typed overload
int count = await dataSource.ExecuteScalarTyped<int>("SELECT COUNT(*) FROM users");
long id = await dataSource.ExecuteScalarTyped<long>("SELECT nextval('users_id_seq')");
Guid tenantId = await dataSource.ExecuteScalarTyped<Guid>("SELECT tenant_uuid FROM tenants LIMIT 1");

ExecuteReader

Streaming row reading with a callback — ideal for processing large results without loading into memory.

int processed = 0;
await dataSource.ExecuteReader("SELECT id, name FROM users", (reader, rowIndex) =>
{
    int id = reader.GetInt32(0);
    string name = reader.GetString(1);
    Console.WriteLine($"{rowIndex}: {id} → {name}");
    processed++;
});
Console.WriteLine($"Processed {processed} rows");

ExecuteReaderList

Reads all rows and collects them into List<T>.

// Simple projection
var names = await dataSource.ExecuteReaderList<string>(
    "SELECT name FROM users ORDER BY name",
    reader => reader.GetString(0));

// With parameters
var activeUsers = await dataSource.ExecuteReaderList<(int Id, string Name)>(
    """SELECT id, name FROM users WHERE active = @active ORDER BY name""",
    reader => (reader.GetInt32(0), reader.GetString(1)),
    [new NpgsqlParameter { ParameterName = "active", Value = true }]);

ExecuteReaderFirst

Returns the value of the first column of the first row. Only that row is fetched from the server — the rest of the result set is not read. Returns default(T) if the query returns no rows or the value is NULL.

Supported types: int, long, short, byte, bool, double, float, decimal, char, string, DateTime, Guid, DateTimeOffset, TimeSpan, DateOnly, TimeOnly, plus enums, any Nullable<T> of these and any other IConvertible type.

// Returns 0 if the table is empty
int errorCount = await dataSource.ExecuteReaderFirst<int>(
    "SELECT COUNT(*) FROM outbox_errors");

// Guid — automatic casting works (LIMIT is no longer required: only the first row is read)
Guid firstTenantId = await dataSource.ExecuteReaderFirst<Guid>(
    "SELECT tenant_id FROM tenants ORDER BY created_at");

// Nullable and enums are supported too
int? maybeAge = await dataSource.ExecuteReaderFirst<int?>(
    "SELECT age FROM users WHERE id = 42");

BeginBinaryImport

Fast binary data import via COPY — orders of magnitude faster than individual INSERTs.

ulong imported = await dataSource.BeginBinaryImport(
    "COPY bulk_data (id, payload, created_at) FROM STDIN BINARY",
    async (writer, ct) =>
    {
        foreach (var item in items)
        {
            writer.StartRow();
            writer.Write(item.Id, NpgsqlDbType.Integer);
            writer.Write(item.Payload, NpgsqlDbType.Bytea);
            writer.Write(item.CreatedAt.ToUnixTimeMilliseconds(), NpgsqlDbType.Timestamp);
        }
        return await writer.CompleteAsync(ct);
    },
    cancellationToken);

Console.WriteLine($"Imported {imported} rows");

PgRetryStrategy

Retry with jitter for transient Npgsql errors.

using Sa.Data.PostgreSql;

// Automatically retries on transient errors (connection reset, timeout, etc.)
var result = await PgRetryStrategy.ExecuteWithRetry(
    async ct =>
    {
        using var conn = await dataSource.OpenDbConnection(ct);
        return await conn.OpenAsync(ct);
    },
    retryCount: 5,
    initialDelay: 530);

PgTransaction (IPgDataSource.BeginTransaction)

IPgDataSource.BeginTransaction(...) opens a PostgreSQL transaction on the data source without juggling connection lifetime — the returned PgTransaction owns the connection and the transaction, and returns the connection to the pool on dispose.

using Sa.Data.PostgreSql;

IPgDataSource dataSource = services.GetRequiredService<IPgDataSource>();

await using PgTransaction sqlTx = await dataSource.BeginTransaction(ct);

// Reuse the same connection + transaction for several components of one unit of work,
// e.g. an outbox write that must commit atomically with your own inserts:
await somethingWritingInTheTransaction(sqlTx.Transaction, ct);

await sqlTx.CommitAsync(ct);        // commit → all writes visible together
// dispose without commit → rollback, connection returned to the pool
  • BeginTransaction is a regular member of the IPgDataSource contract; PgDataSource implements it on top of OpenDbConnection.
  • The transaction targets whichever data source the IPgDataSource instance represents — for the default (unnamed) data source that is the shared/default one.
  • PgTransaction.Connection — the open connection, PgTransaction.Transaction — its active transaction.
  • Dispose rolls back an uncommitted transaction (best effort) and returns the connection to the pool; CommitAsync / RollbackAsync mark it completed so dispose is then a no-op for the transaction itself.
  • IPgDataSource.GetConnectionString() returns the underlying connection string of the data source (Npgsql omits the password unless Persist Security Info is set) — used, for example, to verify that a caller-supplied transaction targets the same database.

DbCommandExtensions + INamePrefixProvider

Optimized API for adding parameterized commands with pre-cached parameter names (minimal allocations).

// Declare a prefix provider
public class UserParams : INamePrefixProvider
{
    public static string[] GetPrefixes() => ["name", "age", "email"];
    public static int MaxIndex => 10;
}

// Use — names are generated as @name0, @name1, ..., @age0, ...
var cmd = new NpgsqlCommand("SELECT * FROM users WHERE name = @name0 AND age > @age0")
    .AddParam<UserParams>("name", "Tom", 0)
    .AddParam<UserParams>("age", 18, 0);

Method Comparison

Method Return When to use
ExecuteNonQuery int (rows) INSERT / UPDATE / DELETE / DDL
ExecuteScalar object? Single value, manual cast needed
ExecuteScalarTyped<T> T Single value with auto-cast (Guid, DateTime, DateTimeOffset, DateOnly)
ExecuteReader int (rows) Streaming processing, many rows
ExecuteReaderList<T> List<T> Small result, collect everything
ExecuteReaderFirst<T> T First row, first column
BeginBinaryImport ulong (rows) Mass import via COPY BINARY

License

MIT

Product Compatible and additional computed target framework versions.
.NET net10.0 is compatible.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (2)

Showing the top 2 NuGet packages that depend on Sa.Data.PostgreSql:

Package Downloads
Sa.Configuration.PostgreSql

PostgreSQL-backed configuration source for IConfiguration

Sa.Partitional.PostgreSql

Declarative table partitioning for PostgreSQL

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
0.20.0 81 11/13/2026
0.18.0 136 11/3/2026
0.17.0 142 10/29/2026
0.16.0 138 10/14/2026
0.15.0 137 10/2/2026
0.12.0 398 7/28/2026
0.11.0 180 7/14/2026
0.10.0 191 7/2/2026
0.9.0 186 4/29/2026
0.8.1 145 3/11/2026
0.8.0 717 2/10/2026
0.7.0 199 1/27/2026
0.6.0 172 1/15/2026
0.5.0 190 12/29/2025
0.4.0 378 12/17/2025
0.3.0 256 10/17/2025
0.2.0 292 9/3/2025
0.1.0 249 9/1/2025
0.0.3 252 6/16/2025
0.0.2 356 5/15/2025
Loading failed