Azka.PostgreSQL 10.0.0-alpha6

Azka.PostgreSQL

PostgreSQL database provider for the Azka Framework. This package registers an IDatabaseContext backed by Npgsql and provides repository implementations for Azka entities, specifications, generated numbers, generated codes, raw SQL execution, and transaction scopes.

Features

• PostgreSQL database context using NpgsqlDataSource
• Dependency injection setup through services.AddPostgreSQL(...)
• Repository pattern through BaseRepository<T> and BaseViewRepository<T>
• Specification queries with where, include, order, skip, and take support
• Generated numeric values through [GeneratedNumber] and PostgreSQL sequences
• Generated string codes through [GeneratedCode(typeof(...))] and IGeneratedCodeGenerator<TEntity>
• Transaction scopes through IDatabaseContext.BeginTransactionScopeAsync()
• Raw SQL helpers for non-query, scalar, table, and data reader operations
• Connection-string builder options through PostgreSQLOptions

Installation

dotnet add package Azka.PostgreSQL

Requirements

• .NET 10.0 or higher
• Azka core framework
• Npgsql 10.0.1 or higher

Current package version: 10.0.0-alpha6.

Setup

Register the PostgreSQL provider in your application service collection.

using Azka.PostgreSQL;
using Azka.PostgreSQL.Common;

builder.Services.AddPostgreSQL(
    new PostgreSQLOptions
    {
        Host = "localhost",
        Port = 5432,
        Database = "app_db",
        Username = "postgres",
        Password = "postgres"
    },
    typeof(ProductSkuGenerator).Assembly);

You can also pass a complete connection string:

builder.Services.AddPostgreSQL(
    new PostgreSQLOptions
    {
        ConnectionString = builder.Configuration.GetConnectionString("PostgreSQL")
    },
    typeof(ProductSkuGenerator).Assembly);

The optional assembly parameters are scanned for implementations of IGeneratedCodeGenerator<TEntity>. If no assemblies are provided, the provider scans the currently loaded application assemblies.

PostgreSQLOptions

When ConnectionString is not provided, Host, Port, Database, and Username are required.

Entity Mapping

Azka uses attributes from Azka.Common.Annotations to map entities to database tables and columns.

Mapped entities must have:

• [Table] on the entity type.
• One [Key] property.
• [Column] on the key property.
• [Column] on every scalar property that should be persisted or loaded.

using Azka.Common.Annotations;

[Table("public", "products")]
public class Product
{
    [Key]
    [GeneratedNumber]
    [Column("id")]
    public int Id { get; set; }

    [Column("sku")]
    [GeneratedCode(typeof(ProductSkuGenerator))]
    public string Sku { get; set; } = string.Empty;

    [Column("name")]
    public required string Name { get; set; }

    [Column("price")]
    public decimal Price { get; set; }
}

[Table("products")] is also supported when you do not need to set a schema explicitly.

When schema is omitted, Azka uses public.

Database Objects

Create the table and sequences used by generated values.

CREATE TABLE public.products
(
    id integer PRIMARY KEY,
    sku text NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL
);

CREATE SEQUENCE products_id_seq START 1;
CREATE SEQUENCE products_sku_seq START 1;

[GeneratedNumber] uses the built-in sequence convention:

{table_name}_{column_name}_seq

For the Product.Id example above, the provider uses products_id_seq.

[GeneratedCode] uses the sequence name returned by your IGeneratedCodeGenerator<TEntity>.GetSequenceName(...) implementation.

Generated Numbers

Use [GeneratedNumber] on numeric properties that should be populated from PostgreSQL nextval(...).

[Key]
[GeneratedNumber]
[Column("id")]
public int Id { get; set; }

Supported property types:

• int
• long
• int?
• long?

For generated numeric primary keys, the provider inserts nextval(...) directly and reads the generated value back through RETURNING.

Generated Codes

Use [GeneratedCode(typeof(YourGenerator))] for generated string values.

using Azka.Common.Abstractions.GeneratedCode;

public sealed class ProductSkuGenerator : IGeneratedCodeGenerator<Product>
{
    public string GetSequenceName(Product entity)
    {
        return "products_sku_seq";
    }

    public string Generate(Product entity, long sequence)
    {
        return $"SKU-{sequence:D5}";
    }

    public bool ShouldGenerate(
        Product entity,
        string? currentValue,
        GeneratedCodeOperation operation)
    {
        return operation == GeneratedCodeOperation.Insert
            && string.IsNullOrWhiteSpace(currentValue);
    }
}

The generator contract is:

[GeneratedCode] properties must be string. The generator type must implement IGeneratedCodeGenerator<TEntity> for the entity that owns the property.

Repository

Create a repository by inheriting from BaseRepository<T>. The constructor must accept IDatabaseContext.

BaseRepository<T> inherits from BaseViewRepository<T>, so a repository that extends BaseRepository<T> automatically gets both write operations and specification-based read operations. Use BaseViewRepository<T> directly only when you want a read-only repository surface.

using Azka.Common.Database;
using Azka.PostgreSQL.Repositories;

public sealed class ProductRepository : BaseRepository<Product>
{
    public ProductRepository(IDatabaseContext databaseContext)
        : base(databaseContext)
    {
    }
}

Register your repository in DI:

builder.Services.AddScoped<ProductRepository>();

Use it from services, controllers, or handlers:

public sealed class ProductService
{
    private readonly ProductRepository _products;

    public ProductService(ProductRepository products)
    {
        _products = products;
    }

    public async Task<Product> CreateAsync(CancellationToken cancellationToken = default)
    {
        var product = new Product
        {
            Name = "Widget",
            Price = 29.99m
        };

        return await _products.AddAsync(product, cancellationToken);
    }
}

After AddAsync, generated properties are set on the returned entity.

Basic CRUD usage:

var created = await repository.AddAsync(
    new Product { Name = "Widget", Price = 29.99m },
    cancellationToken);

var found = await repository.GetByIdAsync(created.Id, cancellationToken);

found.Price = 34.99m;
await repository.UpdateAsync(found, cancellationToken);

await repository.DeleteAsync(found.Id, cancellationToken);

Repository API

BaseRepository<T> is a full read/write repository. It extends BaseViewRepository<T> and implements the additional write operations and primary-key lookup below.

Because BaseRepository<T> inherits from BaseViewRepository<T>, the following read methods are also available on every BaseRepository<T> implementation.

Specifications

Use specifications from Azka.Common.Abstractions.Specifications for repository queries.

using Azka.Common.Abstractions.Specifications;

public sealed class ExpensiveProductsSpec : Specification<Product>
{
    public ExpensiveProductsSpec(decimal minPrice)
    {
        Query.Where(product => product.Price >= minPrice);
        Query.OrderByDesc(product => product.Price);
        Query.Take(10);
    }
}

var spec = new ExpensiveProductsSpec(50.00m);

IEnumerable<Product> products = await repository.ListAsync(spec);
Product? first = await repository.FirstOrDefaultAsync(spec);
Product single = await repository.SingleAsync(spec);

Specifications are translated to PostgreSQL SQL with parameters.

Relationships and Include

Use [WithOne] and [WithMany] from Azka.Common.Annotations to define relationships that can be included by specifications.

[WithOne("foreign_column")] is used when the current table has a foreign-key column pointing to the referenced entity primary key.

[Table("public", "orders")]
public class Order
{
    [Key]
    [GeneratedNumber]
    [Column("id")]
    public int Id { get; set; }

    [Column("customer_id")]
    public int CustomerId { get; set; }

    [WithOne("customer_id")]
    public Customer Customer { get; set; } = null!;
}

[WithMany("foreign_column")] is used when the referenced child table has a foreign-key column pointing back to the current entity primary key.

[Table("public", "products")]
public class Product
{
    [Key]
    [GeneratedNumber]
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public required string Name { get; set; }

    [WithMany("product_id")]
    public List<ProductReview> Reviews { get; set; } = new();
}

Include relationships in a specification:

public sealed class ProductWithReviewsSpec : Specification<Product>
{
    public ProductWithReviewsSpec(int productId)
    {
        Query.Where(product => product.Id == productId);
        Query.Include(product => product.Reviews);
    }
}

Where(...) expressions that navigate through related properties can also add include expressions automatically when the navigation path is detected by Azka.

For one-to-many includes, FirstOrDefaultAsync and SingleOrDefaultAsync do not force an internal LIMIT 1 or LIMIT 2, because the mapper may need multiple joined rows to build one aggregate result.

Counting Rows

BaseRepository<T> does not expose CountAsync directly, but IDatabaseContext does.

using Azka.Common.Database;

public sealed class ProductReportService
{
    private readonly IDatabaseContext _databaseContext;

    public ProductReportService(IDatabaseContext databaseContext)
    {
        _databaseContext = databaseContext;
    }

    public Task<long> CountExpensiveProductsAsync(
        decimal minPrice,
        CancellationToken cancellationToken = default)
    {
        var spec = new ExpensiveProductsSpec(minPrice);
        return _databaseContext.CountAsync(spec, cancellationToken);
    }
}

When the specification contains a one-to-many include, the provider counts distinct root primary keys to avoid inflated counts from joined child rows.

Transactions

Use transaction scopes from IDatabaseContext. All repository and database-context operations within the same scoped IDatabaseContext instance participate in the current transaction.

using Azka.Common.Database;

public sealed class ProductImportService
{
    private readonly IDatabaseContext _databaseContext;
    private readonly ProductRepository _products;

    public ProductImportService(
        IDatabaseContext databaseContext,
        ProductRepository products)
    {
        _databaseContext = databaseContext;
        _products = products;
    }

    public async Task ImportAsync(CancellationToken cancellationToken = default)
    {
        await using var transaction =
            await _databaseContext.BeginTransactionScopeAsync(cancellationToken);

        await _products.AddAsync(
            new Product { Name = "Item 1", Price = 10m },
            cancellationToken);

        await _products.AddAsync(
            new Product { Name = "Item 2", Price = 20m },
            cancellationToken);

        await transaction.CommitAsync(cancellationToken);
    }
}

If the transaction scope is disposed before CommitAsync, the provider rolls back the active transaction.

Nested transaction scopes are tracked by the database context. The actual PostgreSQL commit happens when the root transaction scope commits.

Raw SQL

IDatabaseContext exposes raw SQL helpers for cases where repository/specification queries are not enough.

var rows = await databaseContext.ExecuteNonQueryAsync(
    "UPDATE products SET price = @price WHERE id = @id",
    new Dictionary<string, object?>
    {
        ["price"] = 34.99m,
        ["id"] = 1
    },
    cancellationToken);

Available raw SQL methods:

The context also exposes:

Generated Value Behavior

Insert flow:

1. Entity metadata is inspected for [GeneratedNumber] and [GeneratedCode].
2. [GeneratedCode] values are generated before insert by resolving the configured generator.
3. [GeneratedNumber] values use PostgreSQL nextval(...).
4. Generated numeric primary keys and other direct sequence values are read back with RETURNING.
5. The entity instance returned by AddAsync contains the generated values.

Update flow:

1. [GeneratedCode] columns are evaluated with GeneratedCodeOperation.Update.
2. The generator decides whether to generate a replacement value through ShouldGenerate(...).
3. All mapped non-primary-key columns are included in the update statement.

Sequence Troubleshooting

If a required sequence does not exist, the provider throws an InvalidOperationException with a message similar to:

Sequence 'products_id_seq' does not exist. Please create it using: CREATE SEQUENCE products_id_seq START 1;

Check that:

• The sequence exists in the schema available to the current connection.
• [GeneratedNumber] follows {table_name}_{column_name}_seq.
• [GeneratedCode] returns the correct sequence name from GetSequenceName(...).
• The database user has permission to call nextval(...) on the sequence.

Notes

• BaseRepository<T> and BaseViewRepository<T> rely on Azka entity metadata. Entities should have a [Table], [Key], and mapped columns.
• The provider registers PostgreSQLOptions as a singleton, NpgsqlDataSource through Npgsql dependency injection, and IDatabaseContext as scoped.
• Generated-code generators found during assembly scanning are registered as transient services.
• GetByIdAsync throws when the entity is not found.
• SingleAsync and SingleOrDefaultAsync use LINQ single semantics and throw when more than one row matches.

License

See LICENSE.txt for details.

Links

• Azka Framework
• NuGet Package