c-sharp.gen-design

A type-safe PostgreSQL database binding library generated by pGenie, targeting C# / .NET 9.

This repository sketches what a C# codegen plugin for pGenie should produce, mirroring the structure of the java_optional artifact from the pGenie demo and following idiomatic C# conventions.

Statements

• InsertAlbum
• InsertMultipleAlbums
• SelectAlbumByFormat
• SelectAlbumById
• SelectAlbumByName
• SelectAlbumFields
• SelectAlbumWithFilters
• SelectAlbumWithTracks
• SelectGenreByArtist
• UpdateAlbumRecordingReturning
• UpdateAlbumReleased

Custom Types

• AlbumFormat — PostgreSQL enumeration (album_format)
• DiscInfo — PostgreSQL composite (disc_info)
• RecordingInfo — PostgreSQL composite (recording_info)
• TrackInfo — PostgreSQL composite (track_info)

Design Decisions

IStatement<TResult> interface

Every generated statement record implements IStatement<TResult>, which is the C# counterpart to the Java Statement<R> interface. It bundles the SQL text, parameter binding, result decoding, and a default Execute(NpgsqlConnection) method — so callers only need to construct the record and call Execute:

await using var dataSource = /* see TypeMapper.Register below */;
await using var conn = await dataSource.OpenConnectionAsync();

var result = new InsertAlbum(
    Name: "Thriller",
    Released: new DateOnly(1982, 11, 30),
    Format: AlbumFormat.Vinyl,
    Recording: new RecordingInfo("Westlake", "Los Angeles", "US", new DateOnly(1982, 4, 14))
).Execute(conn);

Console.WriteLine(result.Id);

One file per statement

Each statement lives in its own file under Statements/ and declares nested Result / ResultRow record types, keeping parameter types and result types co-located and isolated from other statements.

One file per custom type — separate namespace

Custom PostgreSQL types live under Types/ in the Pgenie.Artifacts.Myspace.MusicCatalogue.Types namespace, separate from the statements namespace.

Nullable reference types instead of Optional<T>

The Java artifact uses Optional<T> throughout. C# expresses the same intent with nullable reference types (string?, RecordingInfo?, etc.) and nullable value types (DateOnly?, long?, etc.).

Npgsql type mapping

Custom enum and composite types are mapped to their C# counterparts via Npgsql's NpgsqlDataSourceBuilder. Call TypeMapper.Register once at startup:

var builder = new NpgsqlDataSourceBuilder(connectionString);
builder.Register();   // extension method from TypeMapper
await using var dataSource = builder.Build();

Building

dotnet build

Project Layout

src/
  MySpaceMusicCatalogue/
    IStatement.cs          ← IStatement<TResult> interface
    TypeMapper.cs          ← Npgsql type registration helper
    Types/
      AlbumFormat.cs
      DiscInfo.cs
      RecordingInfo.cs
      TrackInfo.cs
    Statements/
      InsertAlbum.cs
      InsertMultipleAlbums.cs
      SelectAlbumByFormat.cs
      SelectAlbumById.cs
      SelectAlbumByName.cs
      SelectAlbumFields.cs
      SelectAlbumWithFilters.cs
      SelectAlbumWithTracks.cs
      SelectGenreByArtist.cs
      UpdateAlbumRecordingReturning.cs
      UpdateAlbumReleased.cs