Azka.PostgreSQL 10.0.0-alpha1

Azka.PostgreSQL

PostgreSQL database provider for the Azka Framework. Provides a complete repository implementation with PostgreSQL-specific features like sequence-based value generation.

Features

• PostgreSQL Repository - Full IRepository<T> implementation using Npgsql
• Sequence Support - Native PostgreSQL sequence integration with nextval()
• Auto-Generated Numbers - [GeneratedNumber] attribute for sequence-based IDs
• Auto-Generated Values - [GeneratedValue] attribute for custom string generation
• Transaction Support - Full transaction management via NpgsqlTransaction
• RETURNING Clause - Efficient retrieval of auto-generated values after INSERT
• Specification Pattern - Query with specifications (Where, Include, OrderBy, Skip, Take)

Installation

dotnet add package Azka.PostgreSQL

Quick Start

1. Define Your Entity

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

[Table("products")]
public class Product
{
    [Key]
    [GeneratedNumber]  // Auto-generated from sequence
    public int Id { get; set; }

    [Column("sku")]
    [GeneratedValue]   // Auto-generated string value
    public string Sku { get; set; } = string.Empty;

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

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

2. Create PostgreSQL Sequences

Before using [GeneratedNumber] or [GeneratedValue], create the required sequences:

-- For [GeneratedNumber] on Id column
CREATE SEQUENCE products_id_seq START 1;

-- For [GeneratedValue] on Sku column
CREATE SEQUENCE products_sku_seq START 1;

Sequence naming convention: {table_name}_{column_name}_seq

3. Create Repository

using Azka.PostgreSQL.Repositories;
using Npgsql;

public class ProductRepository : BaseRepository<Product>
{
    public ProductRepository(NpgsqlConnection connection) : base(connection)
    {
    }
}

4. Use Repository

await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();

var repository = new ProductRepository(connection);

// Add - Id and Sku will be auto-generated
var product = new Product { Name = "Widget", Price = 29.99m };
product = await repository.AddAsync(product);
// product.Id = 1
// product.Sku = "Sku-20250119120000-00001"

// Get by ID
var found = await repository.GetByIdAsync(1);

// Update
product.Price = 34.99m;
await repository.UpdateAsync(product);

// Delete
await repository.DeleteAsync(1);

5. Use with Specifications

using Azka.Common.Abstractions.Specifications;

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

// Query with specification
var spec = new ExpensiveProductsSpec(50.00m);
var expensiveProducts = await repository.ListAsync(spec);
var firstExpensive = await repository.FirstOrDefaultAsync(spec);

6. Use with Transactions

await using var transaction = await connection.BeginTransactionAsync();
repository.SetTransaction(transaction);

try
{
    await repository.AddAsync(new Product { Name = "Item 1", Price = 10m });
    await repository.AddAsync(new Product { Name = "Item 2", Price = 20m });

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Attributes

Custom GeneratedValue Format

Override GenerateValueHandler in your repository to customize the generated string format:

public class ProductRepository : BaseRepository<Product>
{
    public ProductRepository(NpgsqlConnection connection) : base(connection) { }

    protected override async Task<string> GenerateValueHandler(
        SequenceMetadata metadata,
        Product model,
        CancellationToken cancellationToken = default)
    {
        var sequenceName = $"{_metadata.TableName}_{metadata.ColumnName}_seq";
        var nextValue = await GetNextSequenceValueAsync(sequenceName, cancellationToken);

        // Custom format: PRD-00001
        return $"PRD-{nextValue:D5}";
    }
}

Repository API

Requirements

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

License

See LICENSE.txt for details.

Links

• Azka Framework
• NuGet Package