PgMini 1.1.0

PgMini

A lightweight, pipeline-driven PostgreSQL client for .NET 8+.

You write SQL. PgMini maps results to typed models, manages parameters, and handles transactions - nothing more.

Why PgMini?

PgMini is for you if you want full SQL control, lightweight mapping, and a structured way to compose multi-step transactions - without the ceremony of a full ORM.

PgMini is not for you if you need LINQ queries, entity relationships, or migration tooling - use EF Core instead.

Installation

dotnet add package PgMini

Requires .NET 8+.

Quick Start

1. Register

// Program.cs - single database
builder.Services.AddPgMini("Host=localhost;Database=mydb;Username=user;Password=pass");

// Or pull from config
builder.Services.AddPgMini(builder.Configuration.GetConnectionString("Default")!);

For multiple databases (multi-tenant, read replicas, etc.):

builder.Services.AddNamedPgMini("primary",   config["DB_PRIMARY"]!);
builder.Services.AddNamedPgMini("analytics", config["DB_ANALYTICS"]!);

2. Define your model

Annotate each property you want mapped with [Column("db_column_name")]:

using System.ComponentModel.DataAnnotations.Schema;
using System.Text.Json;

public class Vessel
{
    [Column("vessel_id")]                          public int           Id       { get; set; }
    [Column("vessel_name")]                        public string?       Name     { get; set; }
    [Column("imo_number")]                         public string?       Imo      { get; set; }
    [Column("gross_tonnage")]                      public int?          Tonnage  { get; set; }
    [Column("metadata", TypeName = "jsonb")]       public JsonDocument? Metadata { get; set; }
}

Properties without [Column] are silently ignored. Nullable properties are set to null when the DB value is NULL.

3. Inject and query

public class VesselService(IDbClient db)
{
    // All rows
    public Task<List<Vessel>> GetAllAsync() =>
        db.ReadAsync<Vessel>("SELECT vessel_id, vessel_name, imo_number FROM vessels");

    // First or null
    public Task<Vessel?> FindByImoAsync(string imo) =>
        db.ReadFirstOrDefaultAsync<Vessel>(
            "SELECT vessel_id, vessel_name, imo_number FROM vessels WHERE imo_number = @imo",
            [QueryParams.Of("imo", imo)]);

    // Exactly one row - throws if 0 or 2+
    public Task<Vessel> GetByIdAsync(int id) =>
        db.ReadSingleAsync<Vessel>(
            "SELECT vessel_id, vessel_name, imo_number FROM vessels WHERE vessel_id = @id",
            [QueryParams.Of("id", id)]);

    // Existence check
    public Task<bool> ImoExistsAsync(string imo) =>
        db.ExistsAsync(
            "SELECT EXISTS(SELECT 1 FROM vessels WHERE imo_number = @imo)",
            [QueryParams.Of("imo", imo)]);

    // Insert / update / delete
    public Task<int> InsertAsync(Vessel v) =>
        db.ExecuteAsync(
            "INSERT INTO vessels (vessel_name, imo_number) VALUES (@name, @imo)",
            [QueryParams.Of("name", v.Name), QueryParams.Of("imo", v.Imo)]);

    // Scalar value
    public Task<int?> CountAsync() =>
        db.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM vessels");
}

For named clients:

public class AnalyticsService([FromKeyedServices("analytics")] IDbClient db)
{
    public Task<List<VoyageStats>> GetStatsAsync() =>
        db.ReadAsync<VoyageStats>("SELECT ...");
}

Pagination

// Appends LIMIT / OFFSET automatically - do NOT include them in your query.
var page = await db.ReadPagedAsync<Vessel>(
    "SELECT vessel_id, vessel_name FROM vessels ORDER BY vessel_name",
    pageSize: 20,
    offset: 40);   // or: (pageNumber - 1) * pageSize

Streaming large result sets

Use StreamAsync<T> when you do not want to buffer all rows into a List<T> first - ideal for exports, batch processing, or very large tables:

await foreach (var vessel in db.StreamAsync<Vessel>(
    "SELECT vessel_id, vessel_name FROM vessels ORDER BY vessel_id",
    cancellationToken: ct))
{
    await writer.WriteAsync(vessel);
}

Rows are yielded as they arrive from the driver. The connection is held open for the duration of the iteration and closed when you break or exhaust the enumerator.

Batch operations

Send multiple non-query commands in a single round-trip:

var affected = await db.ExecuteBatchAsync(
[
    new BatchCommand(
        "INSERT INTO vessels (vessel_name, imo_number) VALUES (@name, @imo)",
        [QueryParams.Of("name", "MV Aurora"), QueryParams.Of("imo", "IMO1234567")]),
    new BatchCommand(
        "UPDATE fleets SET vessel_count = vessel_count + 1 WHERE fleet_id = @id",
        [QueryParams.Of("id", fleetId)]),
]);

Transactional Pipeline

The core design of PgMini. Chain typed steps inside a single PostgreSQL transaction; if any step throws, the whole transaction rolls back automatically.

Two-step example: insert then read back

var tx = new Transaction
{
    TransactionGroupName = "RegisterVessel",
    Queries =
    [
        new TransactionalQuery
        {
            // Step 1: insert and get the new ID via RETURNING
            Runnable = db.AutonomousTxExecuteScalarStep<int>(
                "INSERT INTO vessels (vessel_name, imo_number) VALUES (@name, @imo) RETURNING vessel_id",
                [QueryParams.Of("name", "MV Nordic Star"), QueryParams.Of("imo", "IMO9876543")]),

            // Forward the ID to the next step
            PostExecuteScalar = result =>
            {
                var id = (int)result;
                return Task.FromResult<(string?, List<QueryParams>?)>((
                    "SELECT vessel_id, vessel_name, imo_number FROM vessels WHERE vessel_id = @id",
                    [QueryParams.Of("id", id)]
                ));
            }
        },
        new TransactionalQuery
        {
            // Step 2: read the newly inserted row using the forwarded query + params
            Runnable = db.TxReadStep<Vessel>()
        }
    ]
};

var vessels = await db.RunTransactional<List<Vessel>>(tx);
var vessel  = vessels?.FirstOrDefault();

Controlling isolation level

var result = await db.RunTransactional<int>(tx, IsolationLevel.Serializable);

Default is ReadCommitted.

Step helpers

Use TxHelper.NoOp() / TxHelper.NoOpExecute() as PostRead / PostExecute when a step does not need to forward anything to the next step.

Three-step example: multi-entity create

var tx = new Transaction
{
    TransactionGroupName = "CreateVoyage",
    Queries =
    [
        new TransactionalQuery
        {
            Runnable    = db.AutonomousTxExecuteStep(
                "INSERT INTO voyages (vessel_id, departure_port) VALUES (@vid, @port)",
                [QueryParams.Of("vid", vesselId), QueryParams.Of("port", "NLRTM")]),
            PostExecute = TxHelper.NoOpExecute()
        },
        new TransactionalQuery
        {
            Runnable    = db.AutonomousTxExecuteStep(
                "UPDATE vessels SET active_voyage_count = active_voyage_count + 1 WHERE vessel_id = @vid",
                [QueryParams.Of("vid", vesselId)]),
            PostExecute = TxHelper.NoOpExecute()
        },
        new TransactionalQuery
        {
            Runnable = db.AutonomousTxExecuteScalarStep<int>(
                "SELECT active_voyage_count FROM vessels WHERE vessel_id = @vid",
                [QueryParams.Of("vid", vesselId)])
        }
    ]
};

var activeVoyages = await db.RunTransactional<int>(tx);

Dependency Injection

Single database

// Program.cs
builder.Services.AddPgMini(connectionString);

// Optional: customise the NpgsqlDataSource
builder.Services.AddPgMini(connectionString, dataSourceBuilder =>
{
    dataSourceBuilder.EnableDynamicJson();
    dataSourceBuilder.MapEnum<VesselStatus>("vessel_status");
});

Multiple databases (keyed services - .NET 8+)

builder.Services.AddNamedPgMini("ops",       config["DB_OPS"]!);
builder.Services.AddNamedPgMini("reporting", config["DB_REPORTING"]!);

// Injected via [FromKeyedServices]
public class ReportingService([FromKeyedServices("reporting")] IDbClient db) { ... }

API Reference

IDbClient

All methods accept an optional NpgsqlTransaction to participate in an externally-managed transaction, and an optional CancellationToken.

QueryParams

// Factory method - preferred
QueryParams.Of("vessel_id", 42)

// Record initialiser
new QueryParams { Key = "vessel_id", Value = 42 }

Pass null as Value to bind SQL NULL.

[Column] attribute

[Column("db_column_name")]               // standard column - maps by name
[Column("payload", TypeName = "jsonb")]  // JSONB column - property type must be JsonDocument / JsonDocument?

Use System.ComponentModel.DataAnnotations.Schema.ColumnAttribute. Properties without [Column] are ignored. Column name matching is case-insensitive.

Performance

PgMini uses compiled Expression Trees to map query results to your models - the same technique used by Dapper. Mappings are compiled once per CLR type and cached; subsequent calls pay zero reflection cost.

How it works

1. First query for a type - PgMini inspects the [Column] attributes once, compiles a typed setter delegate per property (reader.GetFieldValue<T>(ordinal) → direct property assignment), and caches them in a ConcurrentDictionary<Type, ColumnMapping[]>.
2. Every subsequent query - ordinals are resolved from the result schema (once per query, outside the row loop), then the compiled delegates are called directly. No PropertyInfo.SetValue, no GetValue boxing.

Benchmark projections (local PostgreSQL, .NET 8)

Read N rows from a 3-column table (int, string, int?):

Single INSERT:

These are projected estimates based on known overhead characteristics. Run the included benchmark project against your own database to get accurate numbers for your environment.

Running benchmarks

# Set a connection string pointing at an empty PostgreSQL database
$env:PGMINI_BENCH_CONNSTR = "Host=localhost;Database=pgmini_bench;Username=postgres;Password=postgres"

cd benchmarks/PgMini.Benchmarks
dotnet run -c Release                          # all scenarios
dotnet run -c Release -- --filter "*Read*"    # reads only
dotnet run -c Release -- --filter "*Write*"   # writes only

The benchmark project creates and seeds its own tables. Three scenarios are included: ReadBenchmarks (1/10/100/1000 rows), WriteBenchmarks (single INSERT), and TransactionBenchmarks (two-step pipeline).

Versioning

Versions are derived automatically from git tags via MinVer. Automated releases are managed by Release Please using conventional commits:

Push to main with conventional commits → Release Please opens a release PR. Merging the PR creates a version tag → the publish workflow pushes the package to NuGet automatically.

To trigger a manual release:

git tag v1.0.0
git push origin v1.0.0

Contributing

1. Fork and create a feature branch
2. Use conventional commit messages (feat:, fix:, perf:, etc.)
3. Ensure dotnet test passes - integration tests require Docker (Testcontainers spins up PostgreSQL automatically)
4. Open a pull request against main

License

MIT - see LICENSE.