# Kanject.Core.SqlDatabase

The relational sibling to `Kanject.Core.NoSqlDatabase`. Same `[DbContext]` + `[Repository]` shape, same source generator, same testable interface — running on Amazon Aurora DSQL, the serverless, multi-region-active PostgreSQL-compatible engine. Ships as v1.0.0.

## Install

```bash
dotnet add package Kanject.Core.SqlDatabase.Provider.Dsql
```

Add the `Provider.Dsql` package plus its `Annotations` analyzer and `Annotations.Attributes` (referenced with `OutputItemType="Analyzer"`); the shared abstractions come transitively.

## Define the entity, context, and repository

```csharp
using Kanject.Core.SqlDatabase.Provider.Dsql.Abstractions.Interfaces;
using Kanject.Core.SqlDatabase.Provider.Dsql.Annotations.Attributes;

namespace Demo.Orders.Data;

// The entity. [Table] names the relation; it implements IDsqlEntity so the
// generator discovers it. [PrimaryKey] marks the key; [Index] marks columns
// you query by.
[Table("orders")]
public sealed class Order : IDsqlEntity
{
    [PrimaryKey] public Guid OrderId { get; init; }

    [Index] public string CustomerId { get; set; } = string.Empty;

    public decimal  Total    { get; set; }
    public DateTime PlacedAt { get; set; }
}

// One schema per context isolates stages/services inside a shared cluster.
[DbContext(Schema = "orders_service")]
public partial class OrderDbContext : AbstractDbContext;

// Declare the repository as an empty partial extending Repository<T>. The
// generator emits the CRUD, the typed finders, and an IOrderRepository
// interface it registers for you.
[Repository(Entity = typeof(Order), DbContext = typeof(OrderDbContext), Name = "orders")]
public partial class OrderRepository : Repository<Order>;
```

An entity implements `IDsqlEntity` and carries `[Table]` + `[PrimaryKey]`; `[Index]` marks the columns you filter by. Stage and service isolation is a PostgreSQL **schema** — `[DbContext(Schema = "…")]` — not a runtime table prefix. The `[Repository]` partial extends `Repository<T>` and is completed by the generator.

## Register

```csharp
using Kanject.Core.SqlDatabase.Provider.Dsql.Extensions;

// AddDbContext builds the Npgsql data source and signs IAM auth tokens
// against the DSQL cluster; RegisterDsqlGeneratedRepositories picks up the
// generator-emitted repositories.
builder.Services.AddDbContext<OrderDbContext>(cfg =>
{
    cfg.AwsRegion       = appSettings.AwsRegion;
    cfg.ClusterEndpoint = appSettings.DsqlClusterEndpoint;
    cfg.Database        = "postgres";
});

builder.Services.RegisterDsqlGeneratedRepositories();
```

The provider signs short-lived IAM auth tokens against the DSQL cluster — no password to manage. `RegisterDsqlGeneratedRepositories()` wires every generated repository into DI.

## Use it

```csharp
public sealed class OrderService(IOrderRepository orders)
{
    public Task AddAsync(Order order) => orders.InsertAsync(order);

    // Generated typed finder by primary key.
    public Task<Order?> GetAsync(Guid orderId) => orders.FindOrderAsync(orderId);

    // Fluent, typed query builder → parameterised SQL.
    public Task<IList<Order>> ForCustomerAsync(string customerId)
        => orders.GetAllAsync(opt => opt.WhereCustomerId(customerId));

    public async Task UpdateTotalAsync(Guid orderId, decimal total)
    {
        var order = await orders.FindOrderAsync(orderId);
        if (order is null) return;
        order.Total = total;
        await orders.AddOrUpdateAsync(order);
    }
}
```

Inject the generated `IOrderRepository`. Reads go through the typed finder `Find{Entity}Async` (primary key) or the fluent `GetAllAsync(opt => opt.Where…)` builder, which compiles to parameterised SQL. Base CRUD — `InsertAsync`, `AddOrUpdateAsync`, `UpdateAsync`, `RemoveAsync`, `GetSingleAsync` — comes from `ISqlRepository<TEntity>` / `IDsqlRepository<TEntity>`. The shape mirrors `Kanject.Core.NoSqlDatabase`, so teams already on it adopt this without learning a new pattern.

## What ships with it

- Aurora DSQL provider (v1.0.0) — serverless, multi-region-active, PostgreSQL-compatible
- Source-generated repositories — CRUD, typed finders, and an auto-registered `I<Name>Repository`, zero reflection
- `[Table]` / `[PrimaryKey]` / `[Index]` entities implementing `IDsqlEntity`; schema isolation via `[DbContext(Schema=…)]`
- Fluent, typed `GetAllAsync(opt => opt.Where…)` queries compiled to parameterised SQL
- IAM-signed auth tokens (no stored password) and transaction-level OCC with `WithRetryAsync` / `DsqlRetryPolicy`
- Analyzer diagnostics (`KANJDSQL…`) for invalid entities, keys, and queries

> **Same shape as NoSqlDatabase:** If you've used [Kanject.Core.NoSqlDatabase](https://www.kanject.com/docs/core-nosql/), you already know this: `[DbContext]`, an empty-partial `[Repository]`, generated finders. The difference is the engine underneath — relational DSQL instead of DynamoDB — and `[Index]`/`[Table]` in place of key templates.

---
_Source: https://www.kanject.com/docs/core-sql/ · Kanject Docs_