# Socigy Documentation — Full Text

The complete documentation for the Socigy.OpenSource suite, concatenated into a single file.

---

# DB 0.3.2

Socigy.OpenSource.DB reads your annotated C# classes at build time and generates a fully typed PostgreSQL data layer: SELECT, INSERT, UPDATE, DELETE, joins, set operations, a unit-of-work context, and migrations, with zero boilerplate. Define your schema in C#, build, and every query method is already there. The generated code is plain source, so it runs under NativeAOT with no reflection.

---

# Check DSL

Build type-safe, composable CHECK constraint expressions with IDbCheckExpression and the DbCheck builder.

## Overview

The `[Check]` attribute accepts either a raw SQL string or a class that implements `IDbCheckExpression`. The class-based approach is strongly preferred: it is type-safe, reusable across multiple models, composable with boolean operators, and refactoring-friendly.

## IDbCheckExpression

```csharp
public interface IDbCheckExpression
{
    DbCheckExpr Build(string? columnName);
}
```

`DbCheckExpr` is a lightweight struct that wraps the raw SQL fragment. Implement `Build` and return the result of composing `DbCheck` methods. The `columnName` parameter receives the snake_case column name when the attribute is on a property, or `null` when it is on the class.

Apply it with:

```csharp
[Check(typeof(MyCheckExpression))]
public string Email { get; set; }
```

> **TIP** Share expression classes across multiple model classes. A `ValidEmailExpression` class defined once can be applied to `UserLogin.Email`, `InviteCode.Email`, and `ContactForm.Email` with a single attribute each.

## Why Use the DSL Instead of Raw SQL

| Raw SQL string | DbCheck DSL |
|----------------|-------------|
| `[Check("LENGTH(\"email\") < 200")]` | `[Check(typeof(EmailLengthExpr))]` |
| No refactoring support | Rename property → update one class |
| Duplicated across models | Defined once, referenced everywhere |
| Easy to misquote column names | `DbCheck.Value("Email")` converts automatically |

## DbCheck Builder: All Methods

All methods return a `DbCheckExpr` value that can be further composed.

### Column Reference Methods

| Method | SQL output | Notes |
|--------|------------|-------|
| `DbCheck.Value("PropertyName")` | `"snake_case_name"` | Converts the C# property name to snake_case |
| `DbCheck.Column("raw_col")` | `"raw_col"` | Uses the name as-is |

> **WARNING** `DbCheck.Value("PropertyName")` converts the argument to snake_case automatically. If the column is already in snake_case (or uses a custom `[Column("name")]`), use `DbCheck.Column("col_name")` to avoid double-conversion.

### String Methods

| Method | SQL output |
|--------|------------|
| `DbCheck.StartsWith(col, "prefix")` | `"col" LIKE 'prefix%'` |
| `DbCheck.EndsWith(col, "suffix")` | `"col" LIKE '%suffix'` |
| `DbCheck.Contains(col, "sub")` | `"col" LIKE '%sub%'` |
| `DbCheck.Regex(col, "pattern")` | `"col" ~ 'pattern'` |

### Length Method

```csharp
DbCheck.Len(col, DbCheck.Operators.LessThan, 100)
// → length("col") < 100
```

The second argument is one of the `DbCheck.Operators` constants (see below).

### Literal Values

```csharp
DbCheck.Literal("value")  // → 'value'
DbCheck.Literal(42)       // → 42
```

### Boolean Composition

| Method | SQL output |
|--------|------------|
| `DbCheck.And(expr1, expr2, ...)` | `(expr1 AND expr2 ...)` |
| `DbCheck.Or(expr1, expr2, ...)` | `(expr1 OR expr2 ...)` |
| `DbCheck.Not(expr)` | `NOT (expr)` |

### DbCheck.Operators Constants

| Constant | SQL symbol |
|----------|------------|
| `DbCheck.Operators.LessThan` | `<` |
| `DbCheck.Operators.GreaterThan` | `>` |
| `DbCheck.Operators.Equal` | `=` |
| `DbCheck.Operators.NotEqual` | `<>` |
| `DbCheck.Operators.LessOrEqual` | `<=` |
| `DbCheck.Operators.GreaterOrEqual` | `>=` |

## Implementing an Expression Class

```csharp
public class ValidUsernameExpression : IDbCheckExpression
{
    public DbCheckExpr Build(string? columnName)
    {
        var col = DbCheck.Column("username");

        return DbCheck.And(
            DbCheck.Len(col, DbCheck.Operators.GreaterOrEqual, 3),
            DbCheck.Len(col, DbCheck.Operators.LessOrEqual, 50),
            DbCheck.Regex(col, "^[a-zA-Z0-9_]+$")
        );
    }
}
```

Generated DDL:

```sql
CHECK ((length("username") >= 3 AND length("username") <= 50 AND "username" ~ '^[a-zA-Z0-9_]+$'))
```

Applied to a model:

```csharp
[Table("user_logins")]
public partial class UserLogin
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [Check(typeof(ValidUsernameExpression))]
    public string Username { get; set; }
}
```

## Composing Expressions with And / Or / Not

Expression classes can call `.Build()` on each other to layer constraints:

```csharp
public class ValidEmailExpression : IDbCheckExpression
{
    public DbCheckExpr Build(string? columnName)
    {
        var col = DbCheck.Column("email");

        return DbCheck.And(
            DbCheck.Len(col, DbCheck.Operators.LessThan, 256),
            DbCheck.Contains(col, "@")
        );
    }
}

public class NotDisposableEmailExpression : IDbCheckExpression
{
    public DbCheckExpr Build(string? columnName)
    {
        var col = DbCheck.Column("email");

        return DbCheck.And(
            new ValidEmailExpression().Build(columnName),
            DbCheck.Not(DbCheck.EndsWith(col, "@mailinator.com"))
        );
    }
}
```

```csharp
[Check(typeof(NotDisposableEmailExpression))]
public string Email { get; set; }
```

> **NOTE** Expression classes have no dependency injection. They are instantiated with `new()`. Keep them as pure SQL-building helpers with no external dependencies.

---

# DB Constants

Reference for DbDefaults and DbValues.ForeignKey constants. Portable SQL expressions for defaults and referential actions.

## Overview

`DbDefaults` and `DbValues.ForeignKey` are constant classes that provide named SQL expressions for use in attribute arguments. Using these constants instead of raw SQL strings keeps your model annotations readable, avoids typos, and makes intent explicit.

> **NOTE** These constants are cross-database design helpers. Prefer them over raw SQL strings for portability. If the library ever adds support for additional platforms, the constants emit the correct dialect automatically.

## DbDefaults Constants

All `DbDefaults` constants are used as the argument to `[Default(...)]`:

```csharp
[Default(DbDefaults.Guid.Random)]
public Guid Id { get; set; }
```

### DbDefaults.Guid

| Constant | SQL emitted | When to use |
|----------|-------------|-------------|
| `DbDefaults.Guid.Random` | `gen_random_uuid()` | General-purpose UUIDs. Fully random, no ordering. |
| `DbDefaults.Guid.Sequential` | `uuid_generate_v1mc()` | UUIDs that sort roughly in insertion order. Reduces index fragmentation on large tables. |

> **TIP** Use `DbDefaults.Guid.Sequential` for primary keys on high-write tables to reduce B-tree index fragmentation. Use `DbDefaults.Guid.Random` when sequential ordering of IDs would leak information.

> **NOTE** `uuid_generate_v1mc()` requires the `uuid-ossp` PostgreSQL extension. Add `CREATE EXTENSION IF NOT EXISTS "uuid-ossp";` to your initial migration if you use `DbDefaults.Guid.Sequential`.

### DbDefaults.Time

| Constant | SQL emitted | When to use |
|----------|-------------|-------------|
| `DbDefaults.Time.Now` | `timezone('utc', now())` | UTC timestamp. Use for `created_at`, `updated_at`, audit columns. |
| `DbDefaults.Time.NowLocal` | `now()` | Server-local timestamp (session time zone). Use only when the database is explicitly configured to a known time zone. |
| `DbDefaults.Time.Date` | `current_date` | Calendar date only (no time component). Use for date-only columns such as `birth_date` or `expiry_date`. |

> **WARNING** Prefer `DbDefaults.Time.Now` over `DbDefaults.Time.NowLocal` in most applications. `now()` returns the session time zone, which varies by connection and can produce inconsistent data if clients connect with different zone settings.

### DbDefaults.Bool

| Constant | SQL emitted | When to use |
|----------|-------------|-------------|
| `DbDefaults.Bool.True` | `TRUE` | Default a boolean column to true (e.g., `is_active`, `email_verified`). |
| `DbDefaults.Bool.False` | `FALSE` | Default a boolean column to false (e.g., `is_deleted`, `is_suspended`). |

### DbDefaults.Number

| Constant | SQL emitted | When to use |
|----------|-------------|-------------|
| `DbDefaults.Number.Zero` | `0` | Default a numeric column to zero (e.g., counters, balances). |
| `DbDefaults.Number.One` | `1` | Default a numeric column to one (e.g., version fields, minimum quantities). |

### DbDefaults.Text

| Constant | SQL emitted | When to use |
|----------|-------------|-------------|
| `DbDefaults.Text.Empty` | `''` | Default a text column to an empty string. |

## DbValues.ForeignKey Constants

All `DbValues.ForeignKey` constants are used as the `OnDelete` or `OnUpdate` argument to `[ForeignKey(...)]`:

```csharp
[ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade)]
public Guid UserId { get; set; }
```

| Constant | SQL emitted | Behaviour |
|----------|-------------|-----------|
| `DbValues.ForeignKey.Cascade` | `CASCADE` | Automatically delete or update child rows when the parent row is deleted or updated. |
| `DbValues.ForeignKey.SetNull` | `SET NULL` | Set the foreign key column to `NULL` when the parent row is deleted. The column must be nullable. |
| `DbValues.ForeignKey.SetDefault` | `SET DEFAULT` | Set the foreign key column to its `DEFAULT` value when the parent row is deleted. |
| `DbValues.ForeignKey.Restrict` | `RESTRICT` | Prevent deletion of the parent row if any child rows exist. Checked immediately within the statement. |
| `DbValues.ForeignKey.NoAction` | `NO ACTION` | Like `RESTRICT` but the check is deferred to the end of the transaction. This is the PostgreSQL default when no action is specified. |

> **TIP** Use `DbValues.ForeignKey.Cascade` for owned entities (e.g., a user's addresses) and `DbValues.ForeignKey.Restrict` or `DbValues.ForeignKey.NoAction` for shared references (e.g., a product category that must not be deleted while products exist).

---

# Generator diagnostics

Every build-time warning and error the source generator can raise, with its SCGDB id, what triggers it, and how to suppress or escalate it from .editorconfig.

## Overview

Beyond emitting code, the source generator inspects your tables and `.sql` procedure files at build time and reports problems as standard Roslyn diagnostics. Each one has a stable **`SCGDB###`** id, shows up in the IDE error list and in `dotnet build` output, and points at the offending property, attribute, or `.sql` file.

Because they are ordinary compiler diagnostics, you control their severity from `.editorconfig` exactly like any analyzer rule. See [Configuring severity](#configuring-severity). All diagnostics share the `Socigy.DB` category.

---

## Table & column definitions

These fire on the `[Table]` class or property they describe, so the squiggle lands directly on your C# source.

| Id | Severity | Triggered when |
|----|----------|----------------|
| `SCGDB001` | Error | `[AutoIncrement]` is applied to a property whose type is not `short`, `int`, or `long`. |
| `SCGDB002` | Error | `[Encrypted]` is combined with `[ValueConvertor]`, `[JsonColumn]`, or `[RawJsonColumn]` on the same property. See [Encrypted columns](/database/0.3.2/defining-models/encrypted-columns). |
| `SCGDB016` | Warning | A `[Table]` class declares no `[PrimaryKey]` column. Generated `Update()` / `Delete()` need a primary key to target rows. |
| `SCGDB017` | Warning | A `[Table]` class has no mapped columns. |
| `SCGDB018` | Error | A `[Column("")]` is given an empty or whitespace name. |

---

## SQL procedure files

These fire while processing [procedure-mapping](/database/0.3.2/advanced/procedure-mapping) `.sql` files and attach to the `.sql` file itself.

| Id | Severity | Triggered when |
|----|----------|----------------|
| `SCGDB003` | Warning | A `.sql` file contains no `{{Type}}` / `{{Type.Property}}` placeholder. Suppress per-file with `-- @ignore warning` (see [Suppressing the placeholder warning](/database/0.3.2/advanced/procedure-mapping#suppressing-the-placeholder-warning)). |
| `SCGDB004` | Error | A placeholder references a type that does not exist in the compilation. |
| `SCGDB005` | Error | A `{{Type.Property}}` placeholder references a property that does not exist on the named type. |
| `SCGDB006` | Error | A placeholder is malformed; it must be `{{TypeName}}` (table) or `{{TypeName.PropertyName}}` (column). |
| `SCGDB007` | Error | A placeholder references a type that is not a `[Table]` / `[FlagTable]`. |
| `SCGDB008` | Error | A placeholder's simple type name is ambiguous; use a fully-qualified name. |
| `SCGDB009` | Warning | A `-- @param` is declared but never referenced in the SQL body. |
| `SCGDB010` | Warning | The SQL body references `@name` but no matching `-- @param` declaration exists. |
| `SCGDB011` | Warning | The `-- @returns` type cannot be resolved in the compilation. |
| `SCGDB012` | Warning | A `-- @param` line is malformed; expected `-- @param name: CSharpType`. |
| `SCGDB013` | Warning | A `.sql` file is registered as `<AdditionalFiles>` but lives outside `Socigy/Procedures/`, so it was ignored. |
| `SCGDB014` | Warning | A `.sql` file has an empty body after its header and produced no method. |
| `SCGDB015` | Error | Two procedure files resolve to the same method name in the same namespace group. Only the first is emitted. |

> **NOTE** `SCGDB009` and `SCGDB010` analyse raw SQL text, so unusual constructs (PostgreSQL `@@` operators, casts inside string literals) can occasionally produce a false positive. They are warnings by design. Silence an individual rule with `.editorconfig` if it does not fit your SQL style.

---

## Suppressing diagnostics

### Per-file: `-- @ignore warning`

The missing-placeholder warning (`SCGDB003`) is silenced for a single `.sql` file by adding the directive to its header:

```sql
-- @ignore warning: optional free-form reason
```

See [Procedure mapping → Suppressing the placeholder warning](/database/0.3.2/advanced/procedure-mapping#suppressing-the-placeholder-warning).

### Inline: `#pragma` and `[SuppressMessage]`

Diagnostics reported on a `.cs` location (the table and column rules) can be suppressed inline like any analyzer warning:

```csharp
#pragma warning disable SCGDB016
[Table("audit_log")] // intentionally key-less, append-only table
public partial class AuditLog { /* ... */ }
#pragma warning restore SCGDB016
```

---

## Configuring severity

Set any diagnostic's severity from `.editorconfig`. The section glob must match the **file the diagnostic points at** (`.cs` for table/column rules, `.sql` for procedure rules), or use a global section:

```ini
# Promote the missing-primary-key warning to an error
[*.cs]
dotnet_diagnostic.SCGDB016.severity = error

# Make a missing schema placeholder a hard build error
[*.sql]
dotnet_diagnostic.SCGDB003.severity = error

# Silence "declared but unused parameter" everywhere
[*.sql]
dotnet_diagnostic.SCGDB009.severity = none
```

Valid severities are `error`, `warning`, `suggestion`, `silent`, `none`, and `default`.

> **NOTE** `.editorconfig` severity and the `-- @ignore warning` directive are independent and complementary. Use the directive to silence one file; use `.editorconfig` to change a rule across the whole project.

---

# Procedure Mapping

Write raw SQL files and get back strongly-typed C# methods generated at compile time.

## Overview

Procedure mapping lets you write plain SQL in `.sql` files, annotate them with a small header, and receive a generated C# method that runs the query with typed parameters and returns a typed result. No boilerplate `DbCommand` setup, no manual parameter binding.

This is the right tool for complex queries that are inconvenient to express through the fluent query builder: multi-table joins, aggregates, CTEs, window functions, or stored procedure calls.

## Project Setup

Declare your `.sql` files as `<AdditionalFiles>` in the `.csproj` so the Roslyn source generator can read them at compile time:

```xml
<ItemGroup>
  <AdditionalFiles Include="Socigy\Procedures\**\*.sql" />
</ItemGroup>
```

The path prefix `Socigy\Procedures\` is **required**, not a convention. The source generator only discovers `.sql` files located under `Socigy/Procedures/`, so the glob must be `Socigy\Procedures\**\*.sql`. Files placed in any other directory are ignored.

## Recommended File Structure

```plaintext
MyApp.DB/
└── Socigy/
    └── Procedures/
        ├── GetUserLoginByUsername.sql
        ├── Admin/
        │   ├── GetUsersByRole.sql
        │   └── DeactivateUser.sql
        └── Reporting/
            └── GetMonthlyStats.sql
```

Subdirectories translate to nested static classes in the generated output (see [Subdirectory Organisation](#subdirectory-organisation) below).

## SQL File Header Syntax

Every `.sql` file may contain a header block of `-- @` comment lines before the SQL statement. By convention, `@returns` comes first, followed by `@param` lines, but the parser accepts either order.

```sql
-- @returns: ReturnTypeName
-- @param paramName: CSharpType
```

| Header line | Required | Description |
|-------------|----------|-------------|
| `-- @param name: Type` | One per parameter | Name and fully-qualified C# type |
| `-- @returns: FullTypeName` | No | C# type mapped to each result row |
| `-- @ignore warning[: reason]` | No | Suppresses the missing-placeholder warning ([`SCGDB003`](/database/0.3.2/advanced/generator-diagnostics)) for this file. See [Suppressing the placeholder warning](#suppressing-the-placeholder-warning). |

- If `@returns` is **omitted**, the generated method returns `Task<bool>` (true on success).
- If `@returns` is **present**, the generated method returns `IAsyncEnumerable<ReturnType>`.

Parameter names used in the SQL body (`@username`) must match the `-- @param` names exactly.

> **NOTE** The generator validates the header and body at build time and reports mismatches as warnings: a `-- @param` that is never used, an `@name` in the body with no matching `-- @param`, an unresolvable `-- @returns` type, and more. See [Generator diagnostics](/database/0.3.2/advanced/generator-diagnostics) for the full list.

## Example: Void Procedure (No @returns)

A procedure that performs a write and returns no rows.

```sql
-- @param userId: System.Guid
-- @param reason: string
UPDATE "user_logins"
SET "deactivated_at" = timezone('utc', now()),
    "deactivation_reason" = @reason
WHERE "id" = @userId
```

Generated signature:

```csharp
public static Task<bool> DeactivateUser(
    DbConnection conn,
    System.Guid userId,
    string reason)
```

## Example: Query with Return Type

A procedure that returns typed rows.

```sql
-- @returns: MyApp.UserLogin
-- @param username: string

SELECT "id", "username", "password_hash"
FROM "user_logins"
WHERE "username" = @username
```

Generated signature:

```csharp
public static async IAsyncEnumerable<UserLogin> GetUserLoginByUsername(
    DbConnection conn,
    string username,
    [EnumeratorCancellation] CancellationToken cancellationToken = default)
```

## Schema Placeholders

Hard-coding table and column names in raw SQL means a renamed C# property (or a `[Table("...")]` / `[Column("...")]` override) silently drifts away from your queries until something breaks at runtime. To keep SQL tied to your schema, reference them through **optional** placeholders that the generator expands at build time to the real, double-quoted database names:

| Placeholder | Expands to | Resolves like |
|-------------|------------|---------------|
| `{{Type}}` | the **table name** (`"users"`) | the `Type.TableName` constant / `[Table("...")]` |
| `{{Type.Property}}` | the **column name** (`"username"`) | the `Type.{Property}ColumnName` constant / `[Column("...")]` |

```sql
-- @returns: MyApp.UserLogin
-- @param username: string

SELECT {{UserLogin.Id}}, {{UserLogin.Username}}, {{UserLogin.PasswordHash}}
FROM {{UserLogin}}
WHERE {{UserLogin.Username}} = @username
```

becomes, in the generated method:

```sql
SELECT "id", "username", "password_hash"
FROM "user_logins"
WHERE "username" = @username
```

How a placeholder resolves:

- **`Type`** is resolved against your compilation. Use the **simple name** (`UserLogin`) or, when that is ambiguous, a **fully-qualified name** (`MyApp.UserLogin`). It must be a `[Table]` (or `[FlagTable]`) class.
- A **single segment** (`{{UserLogin}}`) is a **table** reference; **two or more** (`{{UserLogin.Username}}`) is a **column** reference. A fully-qualified table name such as `{{MyApp.UserLogin}}` is recognised because `MyApp` is a namespace, not a type, so it still resolves to the table.
- **`Property`** is the **C# property name**. It resolves to the same value as the generated `{Property}ColumnName` constant, so a `[Column("login_name")]` override or snake_case conversion is honoured automatically.
- The expansion is always the **double-quoted** identifier (`"users"`, `"username"`), matching PostgreSQL quoting rules.

Placeholders are entirely optional and can be mixed freely with hand-written names. Any malformed or unresolvable placeholder is reported as a build error. See [`SCGDB004` to `SCGDB008`](/database/0.3.2/advanced/generator-diagnostics).

> **TIP** Because placeholders resolve through the same logic as the generated [`TableName` and `{Property}ColumnName` constants](/database/0.3.2/core-concepts/how-it-works), a rename in C# propagates to every `.sql` file that references it on the next build, with no manual find-and-replace.

### Suppressing the placeholder warning

To encourage keeping SQL in sync with the schema, the generator emits a build **warning** ([`SCGDB003`](/database/0.3.2/advanced/generator-diagnostics)) for any `.sql` file that contains **no** `{{Type.Property}}` placeholder at all. The warning is attached to the `.sql` file itself, so it surfaces in the IDE and in build output.

When a file legitimately needs no placeholders (a query over a view, a one-off maintenance script, or a table you reference by literal name on purpose), opt out per-file with the `-- @ignore warning` header directive:

```sql
-- @ignore warning: Reporting view, columns are fixed by contract
-- @returns: MyApp.MonthlyStat
SELECT "month", "total" FROM "v_monthly_stats"
```

The text after the colon is a free-form reason for your team; only the presence of `-- @ignore warning` matters to the generator.

To change the warning project-wide instead of per file, set its severity from `.editorconfig`. For example, to make a missing placeholder a hard build error:

```ini
# .editorconfig
[*.sql]
dotnet_diagnostic.SCGDB003.severity = error
```

See [Generator diagnostics → Configuring severity](/database/0.3.2/advanced/generator-diagnostics#configuring-severity) for details.

## Generated Class Name

The source generator produces a single class for the entire project:

```
{AssemblyName}.Socigy.Generated.Procedures
```

For a project with assembly name `MyApp.DB`, the full type name is `MyApp.DB.Socigy.Generated.Procedures`.

## Subdirectory Organisation

Files in subdirectories become nested static classes inside `Procedures`. The subdirectory name is used as the nested class name.

```plaintext
Procedures/Admin/GetUsersByRole.sql
```

becomes:

```csharp
Procedures.Admin.GetUsersByRole(connection, ...)
```

Multiple levels of nesting are supported:

```plaintext
Procedures/Reporting/Monthly/GetStats.sql
→ Procedures.Reporting.Monthly.GetStats(connection, ...)
```

## Calling Generated Methods

### Streaming with await foreach

```csharp
await foreach (var login in Procedures.GetUserLoginByUsername(conn, "alice"))
{
    Console.WriteLine(login.PasswordHash);
}
```

### Collecting to a list

```csharp
using System.Linq;

var logins = await Procedures.GetUserLoginByUsername(conn, "alice")
    .ToListAsync();
```

> **NOTE** `ToListAsync()` requires a reference to `System.Linq.Async` (NuGet package `System.Linq.Async`).

### Calling a void procedure

```csharp
bool ok = await Procedures.Admin.DeactivateUser(conn, userId, "Terms of service violation");
```

## Supported Parameter Types

| C# type | Example |
|---------|---------|
| `string` | names, text |
| `int` | counts, IDs |
| `bool` | flags |
| `System.Guid` | UUIDs |
| `System.DateTime` | timestamps |
| `System.DateOnly` | calendar dates |
| `System.TimeOnly` | time-of-day values |

Any type that Npgsql accepts as a command parameter value is valid.

## Full Example: Get User Login by Username

**File:** `Socigy/Procedures/GetUserLoginByUsername.sql`

```sql
-- @returns: MyApp.UserLogin
-- @param username: string

SELECT {{UserLogin.Id}}, {{UserLogin.Username}}, {{UserLogin.PasswordHash}}, {{UserLogin.CreatedAt}}
FROM {{UserLogin}}
WHERE {{UserLogin.Username}} = @username
  AND {{UserLogin.DeactivatedAt}} IS NULL
LIMIT 1
```

**Usage in an endpoint:**

```csharp
app.MapPost("/login", async (LoginRequest req, IDbConnectionFactory factory) =>
{
    await using var conn = factory.Create();
    await conn.OpenAsync();

    await foreach (var login in Procedures.GetUserLoginByUsername(conn, req.Username))
    {
        if (!PasswordHasher.Verify(req.Password, login.PasswordHash))
            return Results.Unauthorized();

        return Results.Ok(new { login.Id });
    }

    return Results.NotFound();
});
```

> **TIP** For single-row results, use `await foreach` with a `break` or `return` inside the loop body. There is no single-row overload: the generator always returns `IAsyncEnumerable<T>` for queries with `@returns`.

---

# Value Convertors

Transform values between C# types and database representations on every read and write operation.

## Overview

A value convertor sits between a C# property and the database column. It runs automatically on every INSERT, UPDATE, and SELECT. You never invoke it manually. A convertor can change both the direction and the type of the value, so the C# type and the column type do not need to match.

Common uses include encrypting sensitive columns, storing enums as strings, serializing collections to a delimited column, normalizing text casing, or adapting any C# type to a compatible database type.

## IDbValueConvertor Interface

```csharp
public interface IDbValueConvertor<TFrom>
{
    object? ConvertToDbValue(TFrom? value);    // C# → DB  (called on INSERT / UPDATE)
    TFrom? ConvertFromDbValue(object? dbValue); // DB → C# (called on SELECT)
}
```

`TFrom` is the declared C# property type. `ConvertToDbValue` receives the in-memory value and must return whatever the database driver expects. `ConvertFromDbValue` receives the raw value from the driver and must return the C# representation.

| Method | Direction | When called |
|--------|-----------|-------------|
| `ConvertToDbValue(TFrom?)` | C# → DB | INSERT, UPDATE |
| `ConvertFromDbValue(object?)` | DB → C# | SELECT |

**Requirements:**
- The convertor class must have a public, parameterless constructor. The source generator instantiates it with `new TConvertor()`.
- The convertor does not persist instance state across calls; do not rely on fields surviving between rows or queries.
- The `ConvertFromDbValue` parameter is the raw value returned by `IDataRecord.GetValue(i)`. For most column types this is the CLR type Npgsql maps to (`string`, `int`, `Guid`, etc.).

## Applying a Convertor

Place `[ValueConvertor(typeof(TConvertor))]` on the property you want to convert:

```csharp
[ValueConvertor(typeof(LowerCaseConvertor))]
public string Email { get; set; }
```

The value type of the property does not need to match the column type. For example, a `List<string>` property maps cleanly to a `TEXT` column if the convertor handles serialization and deserialization.

## Examples

### Lowercase Normalizer

Stores text in lowercase on every write; reads it back as-is (already lowercase in the database).

```csharp
using Socigy.OpenSource.DB.Core.Convertors;

public class LowerCaseConvertor : IDbValueConvertor<string>
{
    public object? ConvertToDbValue(string? value)
        => value?.ToLowerInvariant();

    public string? ConvertFromDbValue(object? dbValue)
        => dbValue?.ToString();
}
```

```csharp
[Table("user_logins")]
public partial class UserLogin
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ValueConvertor(typeof(LowerCaseConvertor))]
    public string Username { get; set; }

    public string? PasswordHash { get; set; }
}
```

### AES Encryption

Encrypts a string before persisting and decrypts on read. The database column stores `TEXT` (base-64 ciphertext).

```csharp
public class AesConvertor : IDbValueConvertor<string>
{
    public object? ConvertToDbValue(string? value)
        => value is null ? null : Encrypt(value);

    public string? ConvertFromDbValue(object? dbValue)
        => dbValue is null ? null : Decrypt(dbValue.ToString()!);

    private static string Encrypt(string plain) { /* AES encrypt + base-64 */ return plain; }
    private static string Decrypt(string cipher) { /* base-64 + AES decrypt */ return cipher; }
}
```

```csharp
[ValueConvertor(typeof(AesConvertor))]
public string SocialSecurityNumber { get; set; }
```

> **WARNING** Storing encryption keys in source code is shown here for brevity only. Use a secrets manager or environment variable in production.

### Enum as String

Stores the enum member name as text, making database values human-readable and safe across enum value reorderings.

```csharp
public class StatusConvertor : IDbValueConvertor<OrderStatus>
{
    public object? ConvertToDbValue(OrderStatus? value)
        => value?.ToString().ToLowerInvariant();

    public OrderStatus? ConvertFromDbValue(object? dbValue)
        => dbValue is null
            ? null
            : Enum.Parse<OrderStatus>(dbValue.ToString()!, ignoreCase: true);
}
```

```csharp
[ValueConvertor(typeof(StatusConvertor))]
public OrderStatus Status { get; set; } = OrderStatus.Active;
```

> **TIP** This pattern also prevents enum renaming from silently corrupting stored data. If you rename a member, old rows still contain the old string and `Enum.Parse` will throw a clear exception rather than silently mapping to a wrong value.

### List to CSV String

Maps a `List<string>` property to a single `TEXT` column by joining with a separator on write and splitting on read.

```csharp
public class CsvConvertor : IDbValueConvertor<List<string>>
{
    public object? ConvertToDbValue(List<string>? value)
        => value is null ? null : string.Join(',', value);

    public List<string>? ConvertFromDbValue(object? dbValue)
        => dbValue is null
            ? null
            : dbValue.ToString()!.Split(',').ToList();
}
```

```csharp
[ValueConvertor(typeof(CsvConvertor))]
public List<string> Tags { get; set; } = new();
```

> **NOTE** The database column type must be `TEXT` (or equivalent). The migration tool uses the column attribute for the DDL type. The convertor affects runtime values only, not schema generation.

## When Convertors Run

| Operation | Method called |
|-----------|---------------|
| INSERT    | `ConvertToDbValue` |
| UPDATE    | `ConvertToDbValue` |
| SELECT    | `ConvertFromDbValue` |

Convertors do not affect the DDL emitted by the migration tool. Define the column type to match what `ConvertToDbValue` returns, not the C# property type.

---

# Changelog

What changed in Socigy.OpenSource.DB. Runtime-named typed tables ([TableType] and DynamicTable) in 0.3.2, the database-context bulk insert plus scalar and aggregate API in 0.3.1, and 0.3.0's field encryption, rotating credentials, and HashiCorp Vault package.

## v0.3.2 (unreleased)

### Added

- **Dynamic (runtime-named) tables.** New `[TableType]` attribute: declare a typed column shape once and bind the **table name at runtime** via `WithTableName(...)`, returning the typed entity (NativeAOT-safe). Full CRUD + aggregates, both standalone (`WithConnection`/`WithTransaction`) and through a context (`db.DynamicTable<T>(name)`). See [Dynamic tables](/database/0.3.2/dynamic-tables/declaring).
- **Custom (undeclared) columns.** `WithCustomColumns(...)` captures extra runtime columns into each row, `TryGetCustomValue<T>(...)` reads them, and `DB.CustomField<T>("name")` filters on them inside a normal predicate. `MapTypeAsync(name, conn)` auto-discovers a table's extra columns once and caches the schema.
- **Runtime table lifecycle.** `InstantiateAsync()` (CREATE TABLE from the declared shape), `DeleteInstanceAsync()` (DROP TABLE), and `InstanceExistsAsync()`. `[TableType]` tables live outside the migration history, so the type manages its own DDL.
- **Extended join builders.** Joins now support **3 and 4 tables** (chain `.Join<T3>(…).Join<T4>(…)`), **`OrderBy`/`OrderByDesc`**, **client-side projection** (`.Select((a,b,…)=>…)` → a typed result), and **aggregates** (`CountAsync`/`SumAsync`/`AvgAsync`/`MinAsync`/`MaxAsync`). See [Joins](/database/0.3.2/querying/reading/joins).

### Fixed

- **Outer joins now return `null` for an unmatched side** (was a zeroed default instance). Join tuple elements are nullable, so you can distinguish "no match" from a real row of defaults.
- **`Query(pred).Join<…>(…)` now filters the driving table.** The driving predicate was previously dropped.
- **Migrations apply atomically.** Each migration's schema change and its `_scg_migrations` row now commit (or roll back) in a single transaction, so a crash can no longer leave the schema changed-but-unrecorded or recorded-but-not-changed. See [Applying migrations](/database/0.3.2/migration/applying).
- **Migration order follows the `PreviousId` chain, not id sorting.** Ids are minute-granularity timestamps; two migrations created in the same minute (or any non-sortable id) could previously apply out of order. A broken or forked chain now fails loudly.
- **Rollback-aware version detection.** The current version is computed from the full history honoring `is_rollback`, so a rolled-back migration is no longer reported as current.
- **Deterministic constraint names.** Column-less constraints (e.g. raw CHECKs) no longer get a random `Guid` name, so regenerated migrations are reproducible.

### Changed

- **Destructive and lossy migration statements are flagged.** Generated migrations prefix data-losing operations with `-- [SOCIGY:DESTRUCTIVE]` (table/column drops) or `-- [SOCIGY:LOSSY]` (narrowing/unsafe type casts), and the CLI lists them at generation time. See [Schema generation](/database/0.3.2/migration/schema-generation).

### Security

- **Field encryption: associated-data binding (automatic).** Generated code now binds every encrypted value to its `table:column` (authenticated into the HMAC, not stored), so a value cannot be relocated to a different column/row and still decrypt. `IFieldEncryptor`/`FieldCrypto` also expose the optional `associatedData` for custom use. See [Encrypted columns](/database/0.3.2/defining-models/encrypted-columns).
- **Field encryption: key zeroing & portable format.** `AesFieldEncryptor` implements `IDisposable` (zeroes key material) and now encodes values in a fixed little-endian byte order so ciphertext is portable across architectures.
- **Vault: auth token is kept alive.** A background service renews the Vault token (renew-self, or AppRole relogin at max TTL) so long-running apps no longer fail once the initial token expires.
- **Vault: credential renewal tracks the real lease TTL** (renews at ~2/3 of the lease) instead of a fixed interval that could outlast it.
- **Vault: connection strings are built with `DbConnectionStringBuilder`**, so leased passwords containing `;`, `=`, quotes or spaces are escaped correctly.
- **Vault: a warning is logged** when the Vault address uses plaintext HTTP to a non-loopback host.

## v0.3.1 (5 June 2026)

### Added

- **`InsertMultipleAsync` on the database context.** `I{Table}Set` now exposes `InsertMultipleAsync(entities, includeAutoFields, ct)`, batching a whole collection into multi-row `INSERT`s within the unit-of-work scope. See [Database context → Table-set methods](/database/0.3.2/core-concepts/database-context).
- **Auto-field control on context inserts.** `InsertAsync` and `InsertMultipleAsync` take `includeAutoFields` (default `false`); pass `true` to also write auto-increment columns (supply your own values), the context equivalent of `WithAllFields()`. Backed by a new `GetInsertPlan(bool includeAutoIncrement)`.
- **Projecting `ForEachAsync<TResult>`.** Streams matching rows, projects each through the callback, and returns the results (materialized inside the scope), so you can transform rows without a lazy enumerable escaping the connection.
- **Scalar & aggregate queries.** `CountAsync` (a real `SELECT COUNT(*)`, replacing the previous client-side drain), plus `SumAsync`/`AvgAsync`/`MinAsync`/`MaxAsync` and a single-value `ScalarAsync<T>`, on both the query builder and the database context, parameterized via the existing WHERE translation. See [Aggregates & scalars](/database/0.3.2/querying/reading/aggregates).

### Fixed

- CI now builds, packs, and publishes the optional `Socigy.OpenSource.DB.HashiCorp` package independently of the main package (each is version-checked separately, so a re-run still ships one when the other is already published).

---

# Connections & DI

How to supply database connections manually, and how the generated IDbConnectionFactory and DI extensions work in ASP.NET Core.

## Manual connections (no DI)

Every command builder accepts an open `DbConnection` directly. This is the simplest approach and works in any host type: console apps, test projects, background services, or ASP.NET Core.

```csharp
await using var conn = new NpgsqlConnection(
    "Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=secret");
await conn.OpenAsync();

// Pass the open connection to any command builder
await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteAsync();

await foreach (var u in User.Query(x => x.Username == "alice")
    .WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(u.Username);
}
```

> **NOTE** The connection returned by any factory or constructor is not pre-opened. The command builders open it automatically if it's still closed when `ExecuteAsync()` runs, so an explicit `await conn.OpenAsync()` is optional. They do not close it afterwards: only the command and reader are disposed, and the connection is left open so you can reuse it across several builders. You own its lifetime, so dispose it yourself (for example `await using var conn = factory.Create()`), which closes it and returns it to the pool. The one exception is the static `…Sequence` helpers, which restore the connection's original open/closed state.

## IDbConnectionFactory

When `generateDbConnectionFactory: true` is set in `socigy.json`, the build emits an implementation of `IDbConnectionFactory` (defined in `Socigy.OpenSource.DB.Core`):

```csharp
public interface IDbConnectionFactory
{
    DbConnection Create(string? connectionKey = null);
    Task<bool> EnsureDbExists();
}
```

`Create(connectionKey)` constructs a new (closed) `DbConnection` using the connection-string sub-key you specify. A `null` argument resolves to `"Default"`. Any other string resolves to the matching sub-key in `appsettings.json`.

`EnsureDbExists()` checks whether the target database exists and creates it if not. It returns `true` if the database already existed, `false` if it was just created.

## appsettings.json format

Connection strings are read from `ConnectionStrings.{databaseName}` using a nested object structure. Multiple named connections (for example a primary and a read replica) are supported as sub-keys.

> **NOTE** Do not include `Database=` in the connection string. The generated factory automatically appends `Database={databaseName}` (the value of `databaseName` in `socigy.json`) when opening a connection.

```json
{
  "ConnectionStrings": {
    "AuthDb": {
      "Default":  "Host=localhost;Port=5432;Username=postgres;Password=secret",
      "ReadOnly": "Host=replica.example.com;Port=5432;Username=ro_user;Password=secret"
    }
  }
}
```

`factory.Create()` or `factory.Create(null)` uses `"Default"`.
`factory.Create("ReadOnly")` uses `"ReadOnly"`.

## Generated DI extensions

After setting `databaseName: "AuthDb"` in `socigy.json` and running `dotnet build`, the following extension methods are generated and ready to use:

```csharp
// WebApplicationBuilder (ASP.NET Core minimal API / MVC)
builder.AddAuthDb();

// IServiceCollection (any host)
builder.Services.AddAuthDb();

// HostApplicationBuilder (generic host)
builder.AddAuthDb();
```

All three register the same services, keyed `"AuthDb"`:

- `IDbConnectionFactory`, reads from `ConnectionStrings.AuthDb`.
- `IMigrationManager`, applies pending migrations.

Both register under the key `"AuthDb"`. Resolve them with `GetRequiredKeyedService<IDbConnectionFactory>("AuthDb")` or inject with `[FromKeyedServices("AuthDb")]`.

### Applying migrations on startup

```csharp
var app = builder.Build();
await app.EnsureLatestAuthDbMigration();
app.Run();
```

`EnsureLatestAuthDbMigration()` is a generated extension on `WebApplication` and `IHost`. It resolves the keyed `IMigrationManager` and applies any unapplied migrations in order. If you need finer control, resolve the keyed `IMigrationManager` yourself and call `EnsureLatestVersion()`.

## Full Program.cs example

```csharp
var builder = WebApplication.CreateBuilder(args);

// Register IDbConnectionFactory + IMigrationManager keyed "AuthDb"
builder.AddAuthDb();

builder.Services.AddScoped<UserService>();

var app = builder.Build();

// Apply pending migrations before serving traffic
await app.EnsureLatestAuthDbMigration();

app.MapGet("/users/{username}", async (string username, UserService svc) =>
    await svc.FindAsync(username) is { } u ? Results.Ok(u) : Results.NotFound());

app.Run();
```

## Injecting and using the factory

`IDbConnectionFactory` is registered as a keyed service. Resolve it with `[FromKeyedServices("AuthDb")]` (or the keyed service APIs) so the right database is selected even when several are registered:

```csharp
public class UserService
{
    private readonly IDbConnectionFactory _db;

    public UserService([FromKeyedServices("AuthDb")] IDbConnectionFactory db) => _db = db;

    public async Task<User?> FindAsync(string username)
    {
        await using var conn = _db.Create();   // creates a "Default" connection
        await conn.OpenAsync();

        await foreach (var u in User.Query(x => x.Username == username)
            .WithConnection(conn).ExecuteAsync())
        {
            return u;
        }

        return null;
    }

    public async Task<IReadOnlyList<User>> ListReadOnlyAsync()
    {
        await using var conn = _db.Create("ReadOnly");  // uses the replica
        await conn.OpenAsync();

        var results = new List<User>();
        await foreach (var u in User.Query().WithConnection(conn).ExecuteAsync())
            results.Add(u);

        return results;
    }
}
```

> **TIP** For business code, prefer the [database context](/database/0.3.2/core-concepts/database-context). It acquires and disposes the connection for you and keeps services fully mockable, instead of passing a `DbConnection` around by hand.

## Rotating credentials (IDbCredentialsProvider)

The generated connection factory can source its connection string from an optional `IDbCredentialsProvider` instead of static configuration. This is useful when a secrets manager rotates your database credentials, such as HashiCorp Vault's Database secrets engine.

```csharp
public interface IDbCredentialsProvider
{
    // Called synchronously by the factory on every Create(); must return a cached value (no I/O).
    string? GetConnectionString(string database, string? connectionKey);
    // Primes/refreshes the cache (startup + the implementation's own renewal timer).
    ValueTask RefreshAsync(string database, string? connectionKey, CancellationToken ct = default);
}
```

When an `IDbCredentialsProvider` is registered in DI, the factory uses it automatically. It returns the base connection string (the factory still appends `;Database=…`) and falls back to `IConfiguration` when the provider returns `null`. Because `Create()` is synchronous, the provider must serve a cached string and refresh out-of-band; the factory primes it during startup (`EnsureDbExists`). When credentials rotate, returning the new string makes Npgsql open a fresh pool while the old one drains.

A ready-made Vault implementation ships in the optional package. See [HashiCorp Vault](/database/0.3.2/integrations/hashicorp-vault).

## Thread safety

Command builders are not thread-safe. Create a new builder per operation: the generated static factory methods (`User.Query()`, `user.Insert()`) each return a fresh builder instance.

A single `NpgsqlConnection` must not be used concurrently from multiple threads. For concurrent workloads, call `factory.Create()` separately for each concurrent path and open each connection independently.
</content>

---

# Database context & unit of work

The generated, interface-based database context. Inject ISocigyDatabaseFactory, run work inside ExecuteAsync/ExecuteTransactionAsync scopes, and never pass a DbConnection around. Fully mockable for unit tests.

## Why a context?

Passing a raw `DbConnection` into every call works, but it leaks connection lifetime into your business code and makes services hard to unit-test. The database context (new in 0.2.0) is a generated, fully interface-based facade. You inject a factory, run your work inside a scope, and the connection (plus an optional transaction) is acquired from the registered `IDbConnectionFactory` and disposed for you.

Because every seam is an interface, services that depend on it are 100% unit-testable with no database. See [Unit testing](/database/0.3.2/testing/unit-testing).

## What gets generated

For a database named `AuthDb` (the `databaseName` in `socigy.json`), the generator emits:

| Type | Role |
|------|------|
| `IAuthDb` / `AuthDbContext` | The context: one table-set accessor per table, plus `WithConnectionAsync` and `DynamicTable<T>`. |
| `IUserSet` / `UserSet`, … | A mockable data-access seam per table. |
| `AuthDbFactory` | The DI-resolvable `ISocigyDatabaseFactory<IAuthDb>`. |
| `AddAuthDbContext()` | DI registration extension. |

Table-set accessors are pluralized from the entity name: `User` becomes `Users`, `Category` becomes `Categories`, `Class` becomes `Classes`, `TaskItem` becomes `TaskItems`.

## Registration

`AddAuthDbContext()` registers the factory. It relies on `AddAuthDb()` (which registers the connection factory) having been called too:

```csharp
builder.AddAuthDb();                 // registers IDbConnectionFactory (existing)
builder.Services.AddAuthDbContext(); // registers ISocigyDatabaseFactory<IAuthDb> (new)
```

`AddAuthDbContext` takes an optional configurator for connection behavior:

```csharp
builder.Services.AddAuthDbContext(o =>
{
    o.ConnectionLifetime = ConnectionLifetime.PerScope; // default
    o.ConnectionKey = null;                              // appsettings sub-key, e.g. "ReadOnly"
});
```

## Running work

Inject `ISocigyDatabaseFactory<IAuthDb>` and run work inside a scope. Two entry points:

- `ExecuteAsync(...)`: a non-transactional scope, ideal for reads.
- `ExecuteTransactionAsync(...)`: wraps the work in a transaction. It commits when the delegate returns and rolls back if it throws.

Both have a `<TResult>` overload that returns a value, and both accept an optional `CancellationToken`.

```csharp
public class EnrollmentService(ISocigyDatabaseFactory<IAuthDb> db)
{
    public Task<Guid> EnrollAsync(Guid userId, string courseName) =>
        db.ExecuteTransactionAsync(async d =>
        {
            var course = new Course { Id = Guid.NewGuid(), Name = courseName };
            await d.Courses.InsertAsync(course);
            await d.UserCourses.InsertAsync(new UserCourse { UserId = userId, CourseId = course.Id });
            return course.Id;   // committed here; rolled back automatically if anything above throws
        });

    public Task<List<User>> FindActiveAsync() =>
        db.ExecuteAsync(d => d.Users.ToListAsync(u => u.IsActive));
}
```

## Table-set methods

Each `I{Table}Set` exposes terminal async methods (which materialize before the scope closes) plus a streaming callback:

```csharp
Task<bool>        ExistsAsync(Expression<Func<User,bool>> predicate);
Task<User?>       FirstOrDefaultAsync(Expression<Func<User,bool>> predicate);
Task<List<User>>  ToListAsync(Expression<Func<User,bool>>? predicate = null);
Task<long>        CountAsync(Expression<Func<User,bool>>? predicate = null);
Task<TResult?>    SumAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null) where TResult : struct;
Task<TResult?>    AvgAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null) where TResult : struct;
Task<TResult?>    MinAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null) where TResult : struct;
Task<TResult?>    MaxAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null) where TResult : struct;
Task<TResult?>    ScalarAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null);
Task<bool>        InsertAsync(User entity, bool includeAutoFields = false);
Task<int>         InsertMultipleAsync(IEnumerable<User> entities, bool includeAutoFields = false, CancellationToken ct = default);
Task<int>         UpdateAsync(User entity);
Task<int>         DeleteAsync(Expression<Func<User,bool>> predicate);
Task              ForEachAsync(Expression<Func<User,bool>>? predicate, Func<User,Task> onRow, CancellationToken ct = default);
Task<List<TResult>> ForEachAsync<TResult>(Expression<Func<User,bool>>? predicate, Func<User,Task<TResult>> onRow, CancellationToken ct = default);
```

`InsertAsync` and `InsertMultipleAsync` skip auto-generated columns by default (the database fills them in). Pass `includeAutoFields: true` to write them yourself: the context equivalent of the builder's `WithAllFields()`. `InsertMultipleAsync` batches the whole collection into multi-row `INSERT`s (see [INSERT → Bulk insert](/database/0.3.2/querying/writing/insert)):

```csharp
await db.ExecuteTransactionAsync(async d => await d.Users.InsertMultipleAsync(newUsers));
```

`ForEachAsync` streams rows while the connection is open. Process each row inside the callback:

```csharp
await db.ExecuteAsync(d => d.Users.ForEachAsync(u => u.IsActive, async user =>
{
    await SendWelcomeAsync(user);
}));
```

The `ForEachAsync<TResult>` overload projects each streamed row and returns the results, collected inside the scope so nothing lazy escapes the connection:

```csharp
var names = await db.ExecuteAsync(d =>
    d.Users.ForEachAsync(u => u.IsActive, async user => await ResolveDisplayNameAsync(user)));
```

> **WARNING** PostgreSQL connections do not support multiple active result sets. Issuing another command on the same context while a `ForEachAsync` stream is open (or running operations in parallel within one scope) throws a clear `InvalidOperationException`. Buffer with `ToListAsync`, apply changes after the stream completes, or use a separate scope.

> **NOTE** If a query or `ForEachAsync` stream is still active when the delegate returns (typically a missing `await`), the scope throws `InvalidOperationException` instead of letting an opaque error surface from the commit. Await every database call in the delegate: `async ctx => await ctx.Users.ForEachAsync(...)`.

## Connection lifetime

`ConnectionLifetime.PerScope` (default) opens one connection per scope and reuses it. With scoped DI that's one connection per request. `ConnectionLifetime.PerOperation` opens a fresh connection per operation, relying on Npgsql pooling.

> **NOTE** A transaction always pins a single connection regardless of this setting: a transaction cannot span connections. Nested `ExecuteTransactionAsync` calls join the ambient transaction, and only the outermost commits.

## Stored procedures & raw ADO

Generated stored-procedure methods still take a `DbConnection`. Inside a scope, reach the scope's connection through the escape hatch:

```csharp
var rows = await db.ExecuteAsync(d => d.WithConnectionAsync(conn =>
    Procedures.GetUserLoginByUsername(conn, username).ToListAsync()));
```

`WithConnectionAsync` has both a `Task<TResult>` and a void (`Task`) overload. The context also exposes `DynamicTable<T>(string tableName)` for runtime-named tables (see [Declaring dynamic tables](/database/0.3.2/dynamic-tables/declaring)).

## Diagnostics

A transaction scope opens a parent span (`TRANSACTION (postgresql)`) and every command nests under it. The context carries the `ILogger` and diagnostics options from DI, so SQL logging works without any static configuration. See [Diagnostics & OpenTelemetry](/database/0.3.2/observability/diagnostics).
</content>

---

# How it works

A deep dive into the Roslyn incremental source generator: what it reads, what it emits, and how to inspect the output.

## What a Roslyn incremental source generator is

A Roslyn incremental source generator is a .NET SDK feature that plugs into the C# compiler pipeline. It receives syntax trees and semantic models for every file in your project, runs arbitrary analysis, and writes additional `.cs` files that compile alongside your own code. "Incremental" means the generator tracks which inputs changed between builds and re-runs only the affected parts, so repeated builds stay fast even in large solutions. Nothing runs at application startup. Everything the generator produces is plain C# compiled at build time.

## What the generator does at build time

When you run `dotnet build`, the generator:

1. Scans every class in the compilation that carries a `[Table]` attribute.
2. Reads the attribute arguments (table name, schema) and inspects each property's attributes (`[PrimaryKey]`, `[Default]`, `[Column]`, `[ValueConvertor]`, `[Json]`, and so on), plus C# nullable annotations (`string?`, `int?`).
3. Emits one companion `.g.cs` file per annotated class into `obj/{Configuration}/net{version}/generated/Socigy.OpenSource.DB.SourceGenerator/Socigy.OpenSource.DB.SourceGenerator.Program/`.
4. Separately processes `<AdditionalFiles>` `.sql` files to emit procedure wrapper methods (see [Procedure mapping](/database/0.3.2/advanced/procedure-mapping)).

The compiler sees the generated files as ordinary source. No reflection, no dynamic proxy, no IL weaving.

> **NOTE** The table attributes and `DbDefaults` live in `Socigy.OpenSource.DB.Attributes`. The one exception is `[Nullable]`, which lives in `Socigy.OpenSource.DB.Core.Attributes`.

While it reads your tables and `.sql` files, the generator validates them and reports problems as standard `SCGDB###` build diagnostics: unsupported attribute combinations, missing primary keys, malformed procedure headers, unresolvable schema placeholders, and more. See [Generator diagnostics](/database/0.3.2/advanced/generator-diagnostics) for the full catalog.

## What each generated file contains

### IDbTable implementation

Every generated file makes the class implement `IDbTable`:

```csharp
public interface IDbTable
{
    string GetTableName();
    Dictionary<string, ColumnInfo> GetColumns();
    Dictionary<string, ColumnInfo> GetPrimaryColumns();
    (string Name, ColumnInfo Info)? GetColumn(string name);
    string? GetDbColumnName(string memberName);
}
```

| Method              | What it returns                                                        |
|---------------------|------------------------------------------------------------------------|
| `GetTableName()`    | The SQL table name from `[Table("...")]`                               |
| `GetColumns()`      | A `Dictionary<string, ColumnInfo>` built at call time (see below)      |
| `GetPrimaryColumns()` | A `Dictionary<string, ColumnInfo>` containing only the `[PrimaryKey]` columns |
| `GetColumn(name)`   | A `(string Name, ColumnInfo Info)?` tuple for the given C# property name, or `null` |
| `GetDbColumnName(name)` | The snake_case DB column name for a given C# property name         |

### GetColumns(): the runtime column map

`GetColumns()` is the core of the query infrastructure. Each call:

1. Reads the current value of every mapped property on `this`.
2. Applies any `[ValueConvertor]` transformation to the value.
3. Packages the result into a `ColumnInfo` struct with metadata flags.

The returned `Dictionary<string, ColumnInfo>` is keyed by snake_case DB column name.

### ColumnInfo struct

```csharp
public struct ColumnInfo
{
    public Type Type                { get; set; }   // CLR type of the column
    public object? Value            { get; set; }   // current value from the row instance (after convertor)
    public bool IsPrimaryKey        { get; set; }   // part of the primary key
    public bool IsAutoIncrement     { get; set; }   // sequence-backed; excluded from INSERT by default
    public bool HasDbDefault        { get; set; }   // has a DB DEFAULT expression from [Default]
    public bool IsJson              { get; set; }   // stored as jsonb
    public bool IsEncrypted         { get; set; }   // [Encrypted]; stored as bytea (ciphertext)
    public Action<object?>? SetValue { get; set; }  // writes a value read back from the DB into the row

    public static T? ApplyDbValue<T>(object? dbValue); // converts a raw DB value to T
}
```

`SetValue` is used by `WithValuePropagation()` to write DB-generated values (auto-UUID, server timestamp) back into the C# object after an INSERT.

### Instance builder methods

The builder methods live on the entity instance. Call them on an object you already have:

```csharp
user.Insert()                  // PostgresqlInsertCommandBuilder<User>
user.Update()                  // PostgresqlUserUpdateCommandBuilder<User>
user.Delete()                  // PostgresqlUserDeleteCommandBuilder<User>
```

### Static query and delete methods

```csharp
static TableQueryBuilder User.Query();
static TableQueryBuilder User.Query(Expression<Func<User, bool>> predicate);
static PostgresqlUserDeleteCommandBuilder<User> User.DeleteNonInstance(); // filtered delete, no instance needed
```

### Async static shorthands

One-liner shortcuts for the common case, no builder needed:

```csharp
static Task<bool> User.InsertAsync(User instance, DbConnection connection);
static Task<int>  User.UpdateAsync(User instance, DbConnection connection);
```

### Column name constants

The generator emits a `{PropertyName}ColumnName` constant directly on the class for every mapped property, using the snake_case database column name:

```csharp
User.IdColumnName         // => "id"
User.UsernameColumnName   // => "username"
User.CreatedAtColumnName  // => "created_at"
```

Property names convert to snake_case automatically. These constants are useful when building dynamic SQL fragments or constructing ORDER BY / GROUP BY clauses without hard-coded strings.

## Incremental builds

The generator participates in Roslyn's incremental pipeline. It registers syntax predicates and semantic transforms, so only classes whose declaration or attribute metadata changed get re-processed. In practice:

- Changing `Product.cs` does not re-generate `User.generated.cs`.
- Adding a new `[Table]` class triggers generation only for that class.
- Cold builds regenerate everything; warm builds regenerate only diffs.

## Inspecting generated code

Generated files are readable C# placed under the intermediate output path. Add `<EmitCompilerGeneratedFiles>true</EmitCompilerGeneratedFiles>` to your `.csproj` to make them visible on disk:

```plaintext
obj/{Configuration}/net{version}/generated/Socigy.OpenSource.DB.SourceGenerator/Socigy.OpenSource.DB.SourceGenerator.Program/
```

Opening these files is the most direct way to diagnose unexpected query behavior. You can see exactly which columns are included, how the WHERE clause is built, and which parameters are parameterized.

> **TIP** In Visual Studio and JetBrains Rider, pressing F12 (Go to Definition) on a generated method such as `user.Insert()` navigates straight to the generated file. No extra IDE plugin needed.

## Multiple database engines

The library is built around an engine-agnostic core. Everything the generator reads is independent of any single database: the `[Table]` and column attributes, the `IDbTable` contract, the `ColumnInfo` model, the WHERE expression-tree analysis, and the query/command builder abstractions. The PostgreSQL-specific pieces (the `Postgresql*CommandBuilder<T>` types, the DDL dialect, the type mapping) sit in a thin layer on top.

The core is ready for multiple engines today. The per-engine implementations simply haven't been written yet. PostgreSQL is currently the only engine with a complete implementation, which is why every generated builder you see carries the `Postgresql` prefix.

This is a deliberate design goal, not a limitation baked into the architecture. Adding another engine (MySQL, SQLite, SQL Server) means implementing that engine's builders and dialect against the existing core abstractions. It does not require touching the source generator or the core model.

> **NOTE** Multi-engine support is on the roadmap, and the groundwork is already in place. If you need an engine that isn't here yet, nothing blocks you from contributing it: the core is intentionally open for exactly this. Contributions are welcome on [GitHub](https://github.com/WailedParsley36/Socigy.OpenSource.DB).

## AOT compatibility

The generator emits code fully compatible with .NET Native AOT. Nothing at runtime uses reflection:

- `GetColumns()` is a hand-written dictionary-building method, with no `PropertyInfo` scanning.
- WHERE expression trees in `Query(x => ...)` compile to parameterized SQL in the query builder, never reflected over at runtime.
- JSON columns serialize through a `JsonSerializerContext` you provide, which is also AOT-safe.

This makes `Socigy.OpenSource.DB` suitable for publishing with `PublishAot=true` without trimmer warnings from the DB layer.
</content>
</invoke>

---

# Transactions

Wrap multiple operations in a database transaction. Use the ExecuteTransactionAsync unit-of-work scope, or manual WithTransaction() with isolation levels, savepoints, and the auto-rollback dispose pattern.

## ExecuteTransactionAsync (recommended)

If you use the [database context](/database/0.3.2/core-concepts/database-context), the simplest way to run a transaction is `ExecuteTransactionAsync`. It opens a transaction, commits when the delegate returns, and rolls back if it throws. Every operation inside auto-enlists, and you never touch a connection or transaction object.

```csharp
await db.ExecuteTransactionAsync(async d =>
{
    await d.Orders.InsertAsync(order);
    await d.LineItems.InsertAsync(item);
    await d.Inventory.UpdateAsync(inventory);
    // committed here; any exception above rolls the whole thing back
});
```

A `<TResult>` overload returns a value, and both overloads accept an optional `CancellationToken`. Nested `ExecuteTransactionAsync` calls join the ambient transaction, and only the outermost commits. The scope also opens a parent `TRANSACTION (postgresql)` span so the commands nest under it in traces. See [Diagnostics](/database/0.3.2/observability/diagnostics).

The rest of this page covers the lower-level manual API, which remains available when you manage connections yourself.

## Basic transaction

Start a transaction from an open connection, attach it to every builder with `WithTransaction()`, then commit on success or roll back on failure:

```csharp
await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();
await using var tx = await conn.BeginTransactionAsync();

try
{
    await order.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();
    await lineItem.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();
    await inventory.Update().WithTransaction(tx).ExecuteAsync();

    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}
```

All operations share the same `NpgsqlTransaction`. Either all succeed (commit) or all are undone (rollback).

## Auto-rollback with await using

`NpgsqlTransaction` implements `IAsyncDisposable`. Inside an `await using` block, the transaction rolls back automatically when the scope exits unless `CommitAsync()` was already called. This removes the need for an explicit catch/rollback in many cases:

```csharp
await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();

await using (var tx = await conn.BeginTransactionAsync())
{
    await record.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();
    await audit.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();

    await tx.CommitAsync();
    // if CommitAsync is never reached (e.g. an exception above), dispose rolls back
}
```

This pattern is equivalent to the try/catch/rollback approach but reads more concisely when you want the exception to propagate naturally.

## WithConnection vs WithTransaction

Every command builder accepts either `WithConnection()` or `WithTransaction()`, never both.

When you call `WithTransaction(tx)`, the builder uses the connection that the transaction is already bound to. You do not pass the connection separately.

```csharp
// Correct: the transaction carries the connection implicitly
await user.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();

// Also correct: no transaction, pass the connection directly
await user.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();
```

> **WARNING** Calling `WithConnection(conn)` when a transaction is active on that connection does not enlist the operation in the transaction. The operation executes outside the transaction boundary and cannot be rolled back with it. Always use `WithTransaction(tx)` when atomicity is required.

## Isolation levels

Pass an `IsolationLevel` to `BeginTransactionAsync()` to control PostgreSQL's concurrency behavior:

```csharp
using System.Data;

await using var tx = await conn.BeginTransactionAsync(IsolationLevel.RepeatableRead);
```

PostgreSQL supports the following isolation levels:

| Level              | PostgreSQL behavior                                                        |
|--------------------|----------------------------------------------------------------------------|
| `ReadCommitted`    | Default. Each statement sees only committed rows at statement start.       |
| `RepeatableRead`   | All statements in the transaction see the same snapshot of committed data. |
| `Serializable`     | Full serializability: transactions appear to execute one at a time.        |

`ReadUncommitted` is accepted by the API, but PostgreSQL treats it as `ReadCommitted`.

## Savepoints

PostgreSQL supports savepoints for partial rollbacks within a transaction. Use Npgsql's savepoint API directly. No extra abstractions are needed:

```csharp
await using var tx = await conn.BeginTransactionAsync();

// Checkpoint before a risky bulk operation
await tx.SaveAsync("before_bulk");

try
{
    foreach (var item in items)
        await item.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();
}
catch
{
    // Roll back only to the savepoint, not the entire transaction
    await tx.RollbackAsync("before_bulk");
    // The transaction is still open, so you can continue other work
}

await tx.CommitAsync();
```

Savepoints are useful when you want to attempt a batch operation optimistically and recover gracefully without aborting the surrounding transaction.

## Thread safety

A single `NpgsqlConnection` (and its associated transaction) must not be used concurrently from multiple threads. Run one operation at a time per connection. For parallel workloads, open separate connections, each with its own transaction if needed.
</content>

---

# Defining Columns

Control column names, SQL types, nullability, and exclusions with [Column] and [Ignore], plus the full C# to PostgreSQL type mapping in Socigy.OpenSource.DB v0.3.2.

## Overview

Every public property of a `[Table]`-annotated class becomes a column in the generated DDL and SQL. You can override the column name, the SQL type, nullability, and whether the property participates in SQL at all.

## Automatic snake_case Conversion

Property names convert to snake_case automatically. You do not need a `[Column]` attribute just to get the standard naming convention.

| C# property name | Generated column name |
|---|---|
| `CreatedAt` | `created_at` |
| `FirstName` | `first_name` |
| `EmailVerified` | `email_verified` |
| `Id` | `id` |
| `PhoneNumber` | `phone_number` |

## The `[Column]` Attribute

Use `[Column]` to override the column name, the SQL type, or both.

### Override Name Only

```csharp
[Column("email_address")]
public string Email { get; set; }
```

Generated DDL fragment:

```sql
"email_address" TEXT NOT NULL
```

### Override SQL Type Only

```csharp
[Column(Type = "NUMERIC(10,2)")]
public decimal Price { get; set; }

[Column(Type = "VARCHAR(200)")]
public string Description { get; set; }

[Column(Type = "JSONB")]
public string RawPayload { get; set; }
```

> **TIP** Use `[Column(Type = "JSONB")]` only when you need a raw JSONB column without the managed serialization helpers. For typed or raw JSON columns with proper serialization support, use `[JsonColumn]` or `[RawJsonColumn]` instead.

### Override Both Name and Type

```csharp
[Column("unit_price", Type = "NUMERIC(12,4)")]
public decimal UnitPrice { get; set; }
```

## Nullability

### Reference Types (`string`, `byte[]`)

Reference-type properties declared as nullable (`string?`, `byte[]?`) are detected automatically. The generated column becomes `NULL` instead of `NOT NULL`.

```csharp
public string? IconUrl { get; set; }   // → "icon_url" TEXT NULL
public string Username { get; set; }   // → "username" TEXT NOT NULL
```

### Value Types (`int`, `bool`, `Guid`, etc.)

Value types are non-nullable by C# semantics and map to `NOT NULL` columns by default. Use the standard C# nullable syntax (`?`) to make a value-type column nullable. The generator reads nullable annotations directly:

```csharp
public int? ParentId { get; set; }       // → "parent_id" INTEGER NULL
public DateTime? BirthDate { get; set; } // → "birth_date" TIMESTAMP WITHOUT TIME ZONE NULL
public Guid? ParentRef { get; set; }     // → "parent_ref" UUID NULL
```

## The `[Ignore]` Attribute

`[Ignore]` excludes a property entirely from all SQL: DDL, INSERT, UPDATE, SELECT, and WHERE. Use it for computed properties, navigation helpers, or any in-memory state that must not reach the database.

```csharp
[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }

    [Ignore]
    public string FullName => $"{FirstName} {LastName}";  // computed, not stored

    [Ignore]
    public bool IsLoggedIn { get; set; }  // transient runtime flag
}
```

## C# to PostgreSQL Type Mapping

The framework maps C# types to PostgreSQL column types automatically. All supported mappings:

| C# Type | PostgreSQL Type |
|---|---|
| `string` | `TEXT` |
| `char` | `CHARACTER(1)` |
| `bool` | `BOOLEAN` |
| `short` / `Int16` | `SMALLINT` |
| `int` / `Int32` | `INTEGER` |
| `long` / `Int64` | `BIGINT` |
| `float` / `Single` | `REAL` |
| `double` | `DOUBLE PRECISION` |
| `decimal` | `NUMERIC` |
| `Guid` | `UUID` |
| `DateTime` | `TIMESTAMP WITHOUT TIME ZONE` |
| `DateTimeOffset` | `TIMESTAMP WITH TIME ZONE` |
| `DateOnly` | `DATE` |
| `TimeOnly` | `TIME WITHOUT TIME ZONE` |
| `TimeSpan` | `INTERVAL` |
| `byte[]` | `BYTEA` |

### DateOnly and TimeOnly

`DateOnly` and `TimeOnly` require Npgsql 6 or later. Earlier versions do not include the `NpgsqlDbType.Date` and `NpgsqlDbType.Time` enum members needed for parameter binding.

```csharp
[Table("events")]
public partial class Event
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public DateOnly EventDate { get; set; }    // → "event_date" DATE NOT NULL
    public TimeOnly StartTime { get; set; }    // → "start_time" TIME WITHOUT TIME ZONE NOT NULL
}
```

### Enum Columns

A property whose type is an enum (not decorated with `[DatabaseEnum]` and not used via `[FlaggedEnum]`) is stored using the enum's underlying integer type:

```csharp
[Table("user_visibility")]
public enum UserVisibility : short { Public, CirclesOnly, CustomCircles }

[Table("profiles")]
public partial class Profile
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    // Stored as SMALLINT (enum underlying type is short).
    public UserVisibility Visibility { get; set; }
}
```

For a PostgreSQL native ENUM column, apply `[DatabaseEnum]` to the enum type (see the Tables article). For a flags-based N:M junction table, use `[FlaggedEnum]` (see the Flagged Enums article).

## Combined Example

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("products")]
public partial class Product
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [Column("product_name"), StringLength(150)]
    public string Name { get; set; }

    [Column(Type = "NUMERIC(12,4)")]
    public decimal UnitPrice { get; set; }

    public int? StockQuantity { get; set; }

    public string? Description { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }

    [Ignore]
    public bool IsInStock => StockQuantity > 0;
}
```

---

# Constraints

Every constraint attribute in Socigy.OpenSource.DB v0.3.2: unique, foreign key, check, string length, comparison constraints, and migration rename hints.

## Overview

Socigy.OpenSource.DB translates C# attributes directly into PostgreSQL constraint DDL. Constraints are emitted inside the `CREATE TABLE` statement and in subsequent `ALTER TABLE` migration steps. All constraint attribute classes live in the `Socigy.OpenSource.DB.Attributes` namespace.

## `[Unique]`, Unique Constraints

### Single-Column Unique

Place `[Unique]` on a property to emit a `UNIQUE` constraint for that column:

```csharp
[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [Unique]
    public string Email { get; set; }
}
```

Generated DDL fragment:

```sql
CONSTRAINT "uq_users_email" UNIQUE ("email")
```

### Multi-Column Unique Constraint

Place `[Unique]` at the class level and list the property names to create a composite unique constraint:

```csharp
[Table("blog_posts")]
[Unique(nameof(AuthorId), nameof(Slug))]
public partial class BlogPost
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public Guid AuthorId { get; set; }

    [StringLength(200)]
    public string Slug { get; set; }
}
```

To assign a readable name, pass it as a named property argument after the positional columns:

```csharp
[Unique(nameof(AuthorId), nameof(Slug), Name = "uq_author_slug")]
```

Generated DDL fragment (with Name specified):

```sql
CONSTRAINT "uq_author_slug" UNIQUE ("author_id", "slug")
```

> **NOTE** The multi-column form uses the positional constructor: `[Unique(nameof(Col1), nameof(Col2))]`. The `Columns` property has a private setter, so `[Unique(Columns = new[] { ... })]` does not compile. When `Name` is omitted the framework generates an opaque random name, so always specify `Name` for multi-column constraints.

## `[ForeignKey]`, Foreign Key Constraints

`[ForeignKey]` can be placed on a property (single-column FKs) or on the class (composite FKs).

### Single-Column, Auto-Matched Primary Key

The most common form: the framework looks up the primary key of the target class automatically.

```csharp
[Table("user_course")]
public partial class UserCourse
{
    [PrimaryKey, ForeignKey(typeof(User))]
    public Guid UserId { get; set; }

    [PrimaryKey, ForeignKey(typeof(Course))]
    public Guid CourseId { get; set; }
}
```

Generated DDL:

```sql
CONSTRAINT "fk_user_course_users"   FOREIGN KEY ("user_id")   REFERENCES "users"   ("id"),
CONSTRAINT "fk_user_course_courses" FOREIGN KEY ("course_id") REFERENCES "courses" ("id")
```

### Explicit Target Keys

When the target table does not use a simple single-column PK, or when you need to reference a non-PK unique column, specify `TargetKeys`:

```csharp
[ForeignKey(typeof(User), TargetKeys = new[] { nameof(User.Email) })]
public string UserEmail { get; set; }
```

### Referential Actions

Use `DbValues.ForeignKey` constants for `OnDelete` and `OnUpdate`:

| Constant | SQL keyword |
|---|---|
| `DbValues.ForeignKey.Cascade` | `CASCADE` |
| `DbValues.ForeignKey.SetNull` | `SET NULL` |
| `DbValues.ForeignKey.SetDefault` | `SET DEFAULT` |
| `DbValues.ForeignKey.Restrict` | `RESTRICT` |
| `DbValues.ForeignKey.NoAction` | `NO ACTION` |

```csharp
[ForeignKey(typeof(User),
    OnDelete = DbValues.ForeignKey.Cascade,
    OnUpdate = DbValues.ForeignKey.NoAction,
    Name = "fk_posts_user")]
public Guid AuthorId { get; set; }
```

### Named Constraint

```csharp
[ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade, Name = "fk_orders_user")]
public Guid UserId { get; set; }
```

### Class-Level Composite Foreign Key

For a composite FK referencing multiple columns, place the attribute on the class:

```csharp
[Table("user_course_agreement")]
[ForeignKey(typeof(UserCourse),
    Keys = new[] { nameof(UserId), nameof(CourseId) },
    TargetKeys = new[] { nameof(UserCourse.UserId), nameof(UserCourse.CourseId) })]
public partial class UserCourseAgreement
{
    public Guid UserId { get; set; }
    public Guid CourseId { get; set; }
}
```

Generated DDL fragment:

```sql
CONSTRAINT "fk_user_course_agreement_user_course"
    FOREIGN KEY ("user_id", "course_id")
    REFERENCES "user_course" ("user_id", "course_id")
```

## `[Check]`, CHECK Constraints

### Raw SQL Expression

```csharp
[Table("users")]
[Check("LENGTH(email) < 254")]
public partial class User
{
    public string Email { get; set; }

    [Check("LENGTH(bio) <= 500")]
    public string? Bio { get; set; }
}
```

Generated DDL fragments:

```sql
CONSTRAINT "chk_users_1"   CHECK (LENGTH(email) < 254),
CONSTRAINT "chk_users_bio" CHECK (LENGTH(bio) <= 500)
```

> **WARNING** PostgreSQL uses `LENGTH()`, not `LEN()`. Using `LEN()` causes a runtime error when the migration is applied. Always use `LENGTH(column_name)` in raw CHECK expressions.

### Type-Safe Expression via `IDbCheckExpression`

For complex or reusable CHECK logic, implement `IDbCheckExpression` and reference the type:

```csharp
using Socigy.OpenSource.DB.Checks;

public class PositivePriceCheck : IDbCheckExpression
{
    public DbCheckExpr Build(string? columnName)
        => new DbCheckExpr("\"price\" > 0");
}

[Table("products")]
public partial class Product
{
    [Check(typeof(PositivePriceCheck))]
    public decimal Price { get; set; }
}
```

> **TIP** See the Check DSL article for the full `IDbCheckExpression` API and how to compose multi-condition checks.

## `[StringLength]`, Length Constraints

`[StringLength]` changes the column type from `TEXT` to `VARCHAR(n)` and optionally adds a minimum-length `CHECK` constraint.

### Maximum Length Only

```csharp
[StringLength(200)]
public string Title { get; set; }
```

Generated DDL fragment:

```sql
"title" VARCHAR(200) NOT NULL
```

### Minimum and Maximum Length

```csharp
[StringLength(3, 50)]
public string Username { get; set; }
```

Generated DDL fragment:

```sql
"username" VARCHAR(50) NOT NULL,
CONSTRAINT "chk_users_username" CHECK (LENGTH(username) >= 3)
```

## Comparison Constraints

Seven attribute classes emit `CHECK` constraints for numeric and date comparisons. Each accepts `long`, `double`, or `string` (for SQL literals such as date strings).

### `[Min(value)]`, Greater Than or Equal

```csharp
[Min(0)]
public int Quantity { get; set; }
```

SQL: `CHECK (quantity >= 0)`

### `[Max(value)]`, Less Than or Equal

```csharp
[Max(100)]
public double Percentage { get; set; }
```

SQL: `CHECK (percentage <= 100)`

### `[Bigger(value)]`, Strictly Greater Than

```csharp
[Bigger(0)]
public decimal UnitPrice { get; set; }
```

SQL: `CHECK (unit_price > 0)`

### `[BiggerOrEqual(value)]`, Greater Than or Equal

```csharp
[BiggerOrEqual(18)]
public int MinimumAge { get; set; }
```

SQL: `CHECK (minimum_age >= 18)`

### `[Lower(value)]`, Strictly Less Than

```csharp
[Lower(1000)]
public int MaxConcurrentSessions { get; set; }
```

SQL: `CHECK (max_concurrent_sessions < 1000)`

### `[LowerOrEqual(value)]`, Less Than or Equal

```csharp
[LowerOrEqual(5)]
public int StarRating { get; set; }
```

SQL: `CHECK (star_rating <= 5)`

### `[Equal(value)]`, Equality

```csharp
[Equal(1)]
public int SchemaVersion { get; set; }
```

SQL: `CHECK (schema_version = 1)`

### Using String Values for SQL Literals

All comparison attributes accept a `string` overload that inserts the value verbatim as a SQL literal:

```csharp
[Min("'2020-01-01'")]
public DateOnly ReleaseDate { get; set; }
```

SQL: `CHECK (release_date >= '2020-01-01')`

## `[Renamed]`, Column Rename Migration Hint

`[Renamed("old_column_name")]` on a property tells the migration CLI that the column was previously named `old_column_name`. The CLI emits `ALTER TABLE ... RENAME COLUMN` instead of dropping and recreating the column, preserving all existing data.

```csharp
// The column was previously "user_name".
[Renamed("user_name")]
public string Username { get; set; }
```

Generated migration:

```sql
ALTER TABLE "users" RENAME COLUMN "user_name" TO "username";
```

> **WARNING** Remove `[Renamed]` after the migration is applied and committed. Leaving it in place causes the CLI to emit the `RENAME COLUMN` statement again on the next migration run.

## Combined Real-World Example

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("products")]
[Check("unit_price > cost_price")]
public partial class Product
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ForeignKey(typeof(Category), OnDelete = DbValues.ForeignKey.Restrict)]
    public Guid CategoryId { get; set; }

    [StringLength(6, 50), Unique(Name = "uq_products_sku")]
    public string Sku { get; set; }

    [StringLength(200)]
    public string Name { get; set; }

    [Bigger(0)]
    public decimal CostPrice { get; set; }

    [Bigger(0)]
    public decimal UnitPrice { get; set; }

    [Min(0)]
    [Max(9999)]
    public int StockQuantity { get; set; }

    [Min("'2000-01-01'")]
    public DateOnly AvailableFrom { get; set; }

    public string? Description { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}
```

Generated DDL (abbreviated):

```sql
CREATE TABLE "products" (
    "id"             UUID         NOT NULL DEFAULT gen_random_uuid(),
    "category_id"    UUID         NOT NULL,
    "sku"            VARCHAR(50)  NOT NULL,
    "name"           VARCHAR(200) NOT NULL,
    "cost_price"     NUMERIC      NOT NULL,
    "unit_price"     NUMERIC      NOT NULL,
    "stock_quantity" INTEGER      NOT NULL,
    "available_from" DATE         NOT NULL,
    "description"    TEXT         NULL,
    "created_at"     TIMESTAMP    NOT NULL DEFAULT timezone('utc', now()),

    PRIMARY KEY ("id"),

    CONSTRAINT "uq_products_sku"       UNIQUE ("sku"),
    CONSTRAINT "chk_products_1"        CHECK (unit_price > cost_price),
    CONSTRAINT "chk_products_sku"      CHECK (LENGTH(sku) >= 6),
    CONSTRAINT "chk_products_cost"     CHECK (cost_price > 0),
    CONSTRAINT "chk_products_unit"     CHECK (unit_price > 0),
    CONSTRAINT "chk_products_stock_1"  CHECK (stock_quantity >= 0),
    CONSTRAINT "chk_products_stock_2"  CHECK (stock_quantity <= 9999),
    CONSTRAINT "chk_products_avail"    CHECK (available_from >= '2000-01-01'),

    CONSTRAINT "fk_products_categories"
        FOREIGN KEY ("category_id") REFERENCES "categories" ("id") ON DELETE RESTRICT
);
```

---

# Column Defaults

Every way to specify PostgreSQL DEFAULT expressions in Socigy.OpenSource.DB v0.3.2: C# initializers, [Default], DbDefaults constants, and how defaults interact with ExcludeAutoFields().

## Overview

Socigy.OpenSource.DB provides three layered mechanisms for column defaults. Understanding all three, and how they interact, prevents the most common DDL and INSERT surprises.

1. **C# property initializer.** The migration CLI reads the initializer value and emits a `DEFAULT` expression automatically.
2. **`[Default]` with no argument.** Flags the column as having a DB-side default without emitting a DDL expression (used with `[AutoIncrement]` and similar).
3. **`[Default("expr")]` or `[Default(DbDefaults.Constant)]`.** Emits an explicit `DEFAULT` expression in the DDL.

## Section 1: C# Initializer as Default

> **NOTE** This is the feature most developers miss. You do not need `[Default]` for simple literal defaults.

When a property carries no `[Default]` attribute, the migration CLI inspects the C# property initializer. A non-zero, non-empty initializer is emitted directly as the `DEFAULT` expression in DDL.

```csharp
[Table("courses")]
public partial class Course
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    // No [Default]. CLI reads the initializer "DEFAULT NAME".
    public string Name { get; set; } = "DEFAULT NAME";

    public int Priority { get; set; } = 5;

    public bool IsPublished { get; set; } = true;
}
```

Generated DDL:

```sql
CREATE TABLE "courses" (
    "id"           UUID    NOT NULL DEFAULT gen_random_uuid(),
    "name"         TEXT    NOT NULL DEFAULT 'DEFAULT NAME',
    "priority"     INTEGER NOT NULL DEFAULT 5,
    "is_published" BOOLEAN NOT NULL DEFAULT TRUE,
    PRIMARY KEY ("id")
);
```

### What Produces a DEFAULT and What Does Not

| C# initializer | DDL `DEFAULT` emitted |
|---|---|
| `= "Hello"` | `DEFAULT 'Hello'` |
| `= 42` | `DEFAULT 42` |
| `= true` | `DEFAULT TRUE` |
| `= MyEnum.Value` | `DEFAULT 0` (underlying integer) |
| `= MyDbEnum.Value` | `DEFAULT 'Value'` (PostgreSQL enum string, see below) |
| `= 0` | *(ignored: zero value)* |
| `= false` | *(ignored: zero value)* |
| `= ""` | *(ignored: empty string)* |
| `= null` | *(ignored)* |
| `= Guid.Empty` | *(ignored)* |

Zero-values are the C# default for every value type, so the CLI cannot distinguish "you intentionally wrote `= 0`" from "you left the initializer at the default". Use `[Default(DbDefaults.Number.Zero)]` when you want an explicit `DEFAULT 0` in DDL.

### Enum Initializers

The behavior depends on whether the enum carries `[DatabaseEnum]`.

A **regular enum** (stored as `INTEGER`) emits the initializer as the underlying integer:

```csharp
public enum VersionState { Draft, Published, Archived }

[Table("project_versions")]
public partial class ProjectVersion
{
    // Emits: DEFAULT 0
    public VersionState State { get; set; } = VersionState.Draft;
}
```

Generated DDL fragment:

```sql
"state" integer DEFAULT 0
```

A **`[DatabaseEnum]` enum** (stored as a PostgreSQL `ENUM` type) emits the initializer as the quoted member name:

```csharp
[DatabaseEnum]
public enum Priority { Low, Normal, High }

[Table("tasks")]
public partial class Task
{
    // Emits: DEFAULT 'Normal'
    public Priority Priority { get; set; } = Priority.Normal;
}
```

Generated DDL fragment:

```sql
"priority" priority DEFAULT 'Normal'
```

> **NOTE** The zero-value rule still applies to enums. `= VersionState.Draft` is emitted as a `DEFAULT` only if `Draft` is **not** the first member (that is, not `0`). If `Draft = 0`, the initializer is treated as "no default" and no `DEFAULT` clause is emitted. Use `[Default("0")]` or `[Default(DbDefaults.Number.Zero)]` to force an explicit `DEFAULT 0` in that case.

> **NOTE** Combined flag values such as `= Test.First | Test.Fourth` are silently ignored and produce no `DEFAULT` clause. A combined value like `9` is not a valid single-row reference in the enum's database table. Use `[Default("9")]` to emit it verbatim if you intentionally store bitmasks in the column.

## Section 2: Naked `[Default]`, Flag Only

`[Default]` with no argument tells the framework that this column has a database-side default, but it does **not** emit any DDL expression. The column is still marked so that `ExcludeAutoFields()` skips it during INSERT.

This form is used with `[AutoIncrement]` (sequence-assigned values) or with a primary key whose value is generated outside the application:

```csharp
[Table("user_login")]
public partial class UserLogin
{
    // Naked [Default]: no DDL expression emitted.
    // ExcludeAutoFields() will skip this column on INSERT.
    [PrimaryKey, Default]
    public Guid Id { get; set; }

    public string Username { get; set; }
    public string? PasswordHash { get; set; }
}
```

The resulting DDL column is `"id" UUID NOT NULL`, with no `DEFAULT` clause, because no expression was given. The application supplies or assigns the value through some other means (a trigger, an application-side call, and so on).

> **TIP** If you want the database to generate the UUID automatically, use `[Default(DbDefaults.Guid.Random)]` instead of bare `[Default]`.

## Section 3: `[Default("expr")]`, Raw SQL Expression

Pass a raw SQL expression as a string to emit it verbatim as the `DEFAULT` clause:

```csharp
[Default("'pending'")]
public string Status { get; set; }

[Default("ARRAY[]::TEXT[]")]
public string[] Tags { get; set; } = [];

[Default("(CURRENT_DATE + INTERVAL '30 days')")]
public DateTime ExpiresAt { get; set; }
```

Generated DDL fragments:

```sql
"status"     TEXT      NOT NULL DEFAULT 'pending',
"tags"       TEXT[]    NOT NULL DEFAULT ARRAY[]::TEXT[],
"expires_at" TIMESTAMP NOT NULL DEFAULT (CURRENT_DATE + INTERVAL '30 days')
```

> **WARNING** The string you pass is inserted verbatim. You are responsible for correct SQL syntax. Strings must include their own single quotes (for example `"'pending'"`, not `"pending"`).

## Section 4: `DbDefaults` Built-in Constants

`DbDefaults` provides named constants for the most common default expressions. Using them instead of raw strings keeps your code DB-engine-agnostic and protected against typos.

| Constant | DDL expression emitted |
|---|---|
| `DbDefaults.Guid.Random` | `DEFAULT gen_random_uuid()` |
| `DbDefaults.Guid.Sequential` | `DEFAULT uuid_generate_v1mc()` |
| `DbDefaults.Time.Now` | `DEFAULT timezone('utc', now())` |
| `DbDefaults.Time.NowLocal` | `DEFAULT now()` |
| `DbDefaults.Time.Date` | `DEFAULT current_date` |
| `DbDefaults.Bool.True` | `DEFAULT TRUE` |
| `DbDefaults.Bool.False` | `DEFAULT FALSE` |
| `DbDefaults.Number.Zero` | `DEFAULT 0` |
| `DbDefaults.Number.One` | `DEFAULT 1` |
| `DbDefaults.Text.Empty` | `DEFAULT ''` |

Usage:

```csharp
[Table("test_items")]
public partial class TestItem
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Name { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }

    [Default(DbDefaults.Bool.True)]
    public bool IsActive { get; set; }

    [Default(DbDefaults.Number.Zero)]
    public int RetryCount { get; set; }
}
```

Generated DDL:

```sql
CREATE TABLE "test_items" (
    "id"          UUID      NOT NULL DEFAULT gen_random_uuid(),
    "name"        TEXT      NOT NULL,
    "created_at"  TIMESTAMP NOT NULL DEFAULT timezone('utc', now()),
    "is_active"   BOOLEAN   NOT NULL DEFAULT TRUE,
    "retry_count" INTEGER   NOT NULL DEFAULT 0,
    PRIMARY KEY ("id")
);
```

> **NOTE** `uuid_generate_v1mc()` (used by `DbDefaults.Guid.Sequential`) requires the `uuid-ossp` extension. Run `CREATE EXTENSION IF NOT EXISTS "uuid-ossp";` before applying migrations that use this constant.

## Section 5: `ExcludeAutoFields()` and Inserts

The INSERT builder's `ExcludeAutoFields()` method skips any property that carries a `[Default]` attribute in any form. This lets the database apply the `DEFAULT` expression instead of receiving an explicit value from the application.

```csharp
// Id has [Default(DbDefaults.Guid.Random)], excluded by ExcludeAutoFields().
// CreatedAt has [Default(DbDefaults.Time.Now)], excluded by ExcludeAutoFields().
// Name has no [Default], included.
var item = new TestItem { Name = "Hello" };

await item.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()   // skips Id and CreatedAt
    .ExecuteAsync();

// Resulting SQL (approximate):
// INSERT INTO "test_items" ("name") VALUES (@name)
// The DB fills "id" and "created_at" from their DEFAULT expressions.
```

The naked `[Default]` (no argument) form is designed for `[AutoIncrement]` columns where the sequence generates the value:

```csharp
[Table("test_counters")]
public partial class TestCounter
{
    [PrimaryKey]
    public Guid Id { get; set; }

    [AutoIncrement]          // implies the column has a DB-side sequence default
    public int Seq { get; set; }

    public string Label { get; set; }
}

var counter = new TestCounter { Id = Guid.NewGuid(), Label = "first" };

// Seq is excluded because [AutoIncrement] marks it as having a DB default.
// The sequence nextval() expression in DDL assigns the value.
await counter.Insert()
    .WithConnection(conn)
    .ExecuteAsync();
```

### Reading Back the DB-Assigned Value

Use `WithValuePropagation()` to execute the INSERT with `RETURNING *` and populate the instance's excluded fields from the returned row:

```csharp
var counter = new TestCounter { Id = Guid.NewGuid(), Label = "VP" };

await counter.Insert()
    .WithConnection(conn)
    .WithValuePropagation()
    .ExecuteAsync();

// counter.Seq is now populated with the sequence value assigned by PostgreSQL.
Console.WriteLine(counter.Seq);  // e.g. 42
```

## Section 6: Precedence of `[Default]` over the C# Initializer

When both a `[Default]` attribute and a C# initializer are present on the same property, the `[Default]` attribute always wins. The initializer is ignored for DDL purposes.

```csharp
// [Default] wins. DDL emits DEFAULT gen_random_uuid(), not DEFAULT 'some-fixed-guid'.
[Default(DbDefaults.Guid.Random)]
public Guid Id { get; set; } = Guid.Parse("00000000-0000-0000-0000-000000000001");

// [Default] wins. DDL emits DEFAULT timezone('utc', now()), not DEFAULT '2020-01-01'.
[Default(DbDefaults.Time.Now)]
public DateTime CreatedAt { get; set; }
```

The C# initializer is consulted only when no `[Default]` attribute is present at all.

## Complete Example

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("orders")]
public partial class Order
{
    // DbDefaults constant: gen_random_uuid()
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    // C# initializer: DEFAULT 'pending'
    public string Status { get; set; } = "pending";

    // C# initializer: DEFAULT 1
    public int Priority { get; set; } = 1;

    // C# initializer: DEFAULT TRUE
    public bool RequiresShipping { get; set; } = true;

    // C# initializer on a regular enum: DEFAULT 1 (underlying integer of Urgent).
    // (assumes: public enum OrderUrgency { Normal, Urgent, Critical })
    public OrderUrgency Urgency { get; set; } = OrderUrgency.Urgent;

    // DbDefaults constant: DEFAULT timezone('utc', now())
    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }

    // Raw SQL expression: DEFAULT (CURRENT_DATE + INTERVAL '7 days')
    [Default("(CURRENT_DATE + INTERVAL '7 days')")]
    public DateTime DueDate { get; set; }

    // Naked [Default]: no DDL expression; skipped by ExcludeAutoFields().
    [Default]
    public int InternalToken { get; set; }

    public string? Notes { get; set; }
}
```

---

# Encrypted columns

Encrypt sensitive fields at rest with [Encrypted] in Socigy.OpenSource.DB v0.3.2. Transparent encrypt-on-write and decrypt-on-read, stored as bytea, using a built-in AES encryptor or a pluggable IFieldEncryptor such as HashiCorp Vault.

## Overview

Mark any property `[Encrypted]` and Socigy transparently **encrypts it on write and decrypts it on read**. The value is stored as `bytea` ciphertext; your code keeps working with the original CLR type.

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("patients")]
public partial class Patient
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public required string Email { get; set; }   // plain. Use this to look rows up.

    [Encrypted] public required string Ssn { get; set; }
    [Encrypted] public DateTime BirthDate { get; set; }
    [Encrypted] public string? Notes { get; set; }
}
```

## How it works

- The column is generated as **`bytea`**. On INSERT and UPDATE the value is serialized to bytes, encrypted, and bound as `bytea`. On SELECT the bytes are decrypted and deserialized back to the property's type.
- `null` is never encrypted. A nullable encrypted column stays SQL `NULL`.
- Encryption uses the **ambient** `IFieldEncryptor` configured via `SocigyFieldEncryption` (see below). Generated entity code has no DI access, so the encryptor is process-wide ambient state, exactly like `SocigyDbDiagnostics`.

## Deferred decryption (`AutoDecrypt = false`)

By default every encrypted column is decrypted automatically on read. When most reads don't need the plaintext, set `AutoDecrypt = false` to skip that cost. The source generator then:

- fills a **read-only** `{Property}RawEncrypted` (`byte[]`) with the raw ciphertext, and
- adds a getter-only `{Property}Decrypted` that decrypts **on first access** and **caches** the plaintext into the property itself.

```csharp
[Table("patients")]
public partial class Patient
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [Encrypted(AutoDecrypt = false)]
    public required string Ssn { get; set; }
}

// On read, Ssn stays empty and SsnRawEncrypted holds the ciphertext:
var p = await Patient.Query(x => x.Id == id).WithConnection(conn).ExecuteAsync().FirstAsync();
byte[]? raw = p.SsnRawEncrypted;   // raw ciphertext, no decryption performed
string ssn = p.SsnDecrypted;       // decrypts once, caches into p.Ssn
// p.Ssn == ssn now (cached)
```

> **NOTE** Writes always encrypt the plaintext property (`Ssn`). For a read-modify-write of an `AutoDecrypt = false` row, access `{Property}Decrypted` first so the plaintext is populated before you save.

## Supported types

Any common CLR value type plus `string` and `byte[]`: `bool`, all integer, floating, and `decimal` types, `char`, `string`, `Guid`, `DateTime`, `DateTimeOffset`, `TimeSpan`, `byte[]`, enums, and `Nullable<>` of these. Other types throw `NotSupportedException`.

> **NOTE** `[Encrypted]` cannot be combined with `[ValueConvertor]`, `[JsonColumn]`, or `[RawJsonColumn]` in this version. The source generator reports error [`SCGDB002`](/database/0.3.2/advanced/generator-diagnostics).

## Encrypted columns are not queryable

Encryption is **non-deterministic** (a fresh random IV each time), so the same plaintext produces different ciphertext. Using an encrypted column in a `WHERE`, `ORDER BY`, `SELECT` projection, or `LIKE` clause throws a clear `NotSupportedException`:

```csharp
// Throws: Ssn is [Encrypted].
await foreach (var p in Patient.Query(x => x.Ssn == ssn).WithConnection(conn).ExecuteAsync()) { }

// Correct: filter by a non-encrypted key, then read the decrypted value.
var patient = await Patient.Query(x => x.Email == email).WithConnection(conn).ExecuteAsync().FirstAsync();
var ssn = patient.Ssn; // decrypted automatically
```

## Configuring the encryptor

### Built-in AES (no external KMS)

`AesFieldEncryptor` is an authenticated AES-256-CBC + HMAC-SHA256 encryptor (encrypt-then-MAC). Give it a **32-byte, high-entropy random key from your secret store** (never hard-code keys, and don't use a passphrase — the key is used directly) and configure it once at startup:

```csharp
using Socigy.OpenSource.DB.Core.Encryption;

byte[] key = Convert.FromBase64String(builder.Configuration["Encryption:Key"]!); // 32 bytes
SocigyFieldEncryption.Configure(new AesFieldEncryptor(key));
```

Do this **before** any encrypted column is read or written. If an `[Encrypted]` column is accessed with no encryptor configured, you get a clear `InvalidOperationException`.

The ciphertext byte format is fixed **little-endian**, so values encrypted on one machine decrypt correctly on any other architecture. `AesFieldEncryptor` is also `IDisposable` — disposing it zeroes the in-memory key material (after which it throws `ObjectDisposedException`).

### Associated data (context binding)

`[Encrypted]` columns are bound to their context **automatically**: the generated code passes `table:column` as associated data on every encrypt and decrypt, so a stored value cannot be copied into a different column or row and still decrypt — the integrity check fails. You don't need to do anything to get this.

Under the hood, `IFieldEncryptor.Encrypt`/`Decrypt` and `FieldCrypto` accept an optional `associatedData` byte array that is **authenticated but not stored**. The same associated data must be supplied on decrypt; this is what the generated code does for you, and you can use it directly for custom encryption:

```csharp
byte[] aad = System.Text.Encoding.UTF8.GetBytes("patients:ssn");
byte[] cipher = encryptor.Encrypt(plaintext, aad);
byte[] plain  = encryptor.Decrypt(cipher, aad);   // decrypting with different aad throws
```

### HashiCorp Vault

For Vault-managed keys, use the optional [`Socigy.OpenSource.DB.HashiCorp`](/database/0.3.2/integrations/hashicorp-vault) package, which installs an `IFieldEncryptor` whose key is sourced from Vault.

## Writing your own encryptor

Implement `IFieldEncryptor` (synchronous, local) and register it:

```csharp
public sealed class MyEncryptor : IFieldEncryptor
{
    public byte[] Encrypt(byte[] plaintext, byte[]? associatedData = null) => /* ... */;
    public byte[] Decrypt(byte[] ciphertext, byte[]? associatedData = null) => /* ... */;
}

SocigyFieldEncryption.Configure(new MyEncryptor());
```

> **WARNING** Rotating the key requires re-encrypting existing rows. Old ciphertext cannot be read with a new key. Plan a migration before rotating.

---

# Flagged enums

Map a C# flags enum to a many-to-many junction table with [FlaggedEnum] in Socigy.OpenSource.DB v0.3.2.

## Overview

A flags enum represents a set of values, for example a user's roles. Instead of storing a bitmask integer, `[FlaggedEnum]` instructs the generator to manage a **junction table**: one row per enum flag that is set. This lets you query all users with a specific role efficiently.

## Declare the enum table

The enum carries `[Table]` to make it a reference table:

```csharp
[Table("roles")]
public enum Role
{
    Reader    = 1,
    Writer    = 2,
    Moderator = 4,
    Admin     = 8,
}
```

The migration tool seeds this table with all declared enum values. Values must be powers of two; they are stored as the `id` column.

## Declare the owner model

Add a `[FlaggedEnum]` property on the model that holds the combined flags value. The property type must be the flags enum:

```csharp
[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }

    [FlaggedEnum]
    public Role Roles { get; set; }
}
```

## Generated junction table

The source generator creates a junction table named `{main_table}_{enum_table}` with two foreign key columns:

```sql
CREATE TABLE "users_roles" (
    "users_id"  UUID    NOT NULL,
    "roles_id"  INTEGER NOT NULL,
    PRIMARY KEY ("users_id", "roles_id"),
    FOREIGN KEY ("users_id")  REFERENCES "users"("id")  ON DELETE CASCADE,
    FOREIGN KEY ("roles_id")  REFERENCES "roles"("id")
);
```

Column naming convention:
- Main table FK: `{main_table}_{pk_column}`, giving `users_id`.
- Enum FK: `{enum_table}_id`, giving `roles_id`.

You can override individual mappings by passing alternating `(localPropertyName, junctionColumnName)` pairs: `[FlaggedEnum(nameof(Id), "user_id")]`. Override the junction table name with the `TableName` property.

## Reading and writing

When you insert or query a `User`, the `Roles` property is treated as a combined flags bitmask. The generated code decomposes the bitmask into individual flag values and syncs the junction table rows.

```csharp
var user = new User
{
    Username = "alice",
    Roles    = Role.Reader | Role.Writer,
};
await user.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();
```

When a user is read back, the junction table rows are aggregated into the `Roles` bitmask:

```csharp
await foreach (var u in User.Query(x => x.Username == "alice")
    .WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(u.Roles.HasFlag(Role.Writer)); // True
}
```

## DDL setup

You must create the enum table and seed it before using the junction table. The CLI tool does this automatically; see [Schema generation](/database/0.3.2/migration/schema-generation). For manual setup:

```sql
CREATE TABLE "roles" (
    "id"          INTEGER NOT NULL,
    "value"       TEXT    NOT NULL,
    "description" TEXT,
    PRIMARY KEY ("id")
);
INSERT INTO "roles" ("id", "value", "description") VALUES
    (1, 'Reader', NULL), (2, 'Writer', NULL), (4, 'Moderator', NULL), (8, 'Admin', NULL);

CREATE TABLE "users_roles" (
    "users_id" UUID    NOT NULL,
    "roles_id" INTEGER NOT NULL,
    PRIMARY KEY ("users_id", "roles_id"),
    FOREIGN KEY ("users_id") REFERENCES "users"("id") ON DELETE CASCADE,
    FOREIGN KEY ("roles_id") REFERENCES "roles"("id")
);
```

> **NOTE** When the junction table needs extra columns (an `AssignedAt` timestamp, for instance), declare an explicit `[FlagTable]` class and point to it with `[FlaggedEnumTable(typeof(...))]` instead of `[FlaggedEnum]`. See the Tables article.

> **WARNING** Enum member values must be exact powers of two (1, 2, 4, 8, and so on). Arbitrary integers break the bitmask decomposition.

---

# JSON columns

Store structured data in PostgreSQL JSONB columns using [RawJsonColumn] or [JsonColumn] in Socigy.OpenSource.DB v0.3.2.

## Two column kinds

| Attribute | Storage | Use when |
|-----------|---------|----------|
| `[RawJsonColumn]` | Raw JSON string | You serialize and deserialize manually, or you only need to read the text. |
| `[JsonColumn(typeof(TContext))]` | Typed serialization via `JsonSerializerContext` | You want strongly-typed reads and writes with AOT compatibility. |

Both produce a `JSONB` column in PostgreSQL.

## `[RawJsonColumn]`

The property type must be `string?`. The value is stored and read back as a plain JSON string.

```csharp
[Table("events")]
public partial class Event
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [RawJsonColumn]
    public string? Metadata { get; set; }
}
```

```csharp
var ev = new Event
{
    Metadata = """{"source": "web", "ip": "127.0.0.1"}""",
};
await ev.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();
```

## `[JsonColumn]`

`[JsonColumn(typeof(TContext))]` takes a `JsonSerializerContext` subclass. The source generator emits calls to `JsonSerializer.Serialize` and `JsonSerializer.Deserialize` using the given context, keeping the code AOT-safe.

### Define the context

```csharp
[JsonSerializable(typeof(UserProfile))]
public partial class MyDbJsonContext : JsonSerializerContext { }
```

### Annotate the property

```csharp
[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [JsonColumn(typeof(MyDbJsonContext))]
    public UserProfile? Profile { get; set; }
}

public class UserProfile
{
    public string? AvatarUrl { get; set; }
    public string? Bio { get; set; }
}
```

### Read and write

```csharp
var user = new User
{
    Profile = new UserProfile { AvatarUrl = "https://...", Bio = "Hello" },
};
await user.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();

await foreach (var u in User.Query().WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(u.Profile?.Bio);
}
```

## Migration DDL

Both attributes produce the same DDL:

```sql
"metadata" JSONB
```

If the property is not declared nullable (`T?`), the column becomes `JSONB NOT NULL`.

> **NOTE** The migration tool does not emit a `DEFAULT` for JSON columns unless you also add `[Default]`. A non-nullable JSON column without a default rejects `INSERT` statements that omit the column.

---

# Sequences

Bind a column to a PostgreSQL sequence with [AutoIncrement] in Socigy.OpenSource.DB v0.3.2.

## `[AutoIncrement]`

Marks a property as a sequence-backed column. The migration tool creates a named PostgreSQL sequence and sets `nextval('...')` as the column default.

```csharp
[Table("log_entries")]
public partial class LogEntry
{
    [PrimaryKey]
    public Guid Id { get; set; }

    [AutoIncrement]
    public int Seq { get; set; }

    public string Message { get; set; }
}
```

## Sequence naming convention

The generated sequence name follows the pattern `{table}_{column}_seq`:

| Table | Column | Sequence name |
|-------|--------|---------------|
| `log_entries` | `seq` | `log_entries_seq_seq` |
| `orders` | `order_number` | `orders_order_number_seq` |

Pass a name to the attribute to override the convention: `[AutoIncrement("custom_seq_name")]`.

## Generated DDL

```sql
CREATE SEQUENCE IF NOT EXISTS "log_entries_seq_seq" AS INTEGER;

CREATE TABLE "log_entries" (
    "id"      UUID    NOT NULL,
    "seq"     INTEGER NOT NULL DEFAULT nextval('log_entries_seq_seq'),
    "message" TEXT    NOT NULL,
    PRIMARY KEY ("id")
);
```

## Usage

Because `[AutoIncrement]` is effectively a database-side default, use `ExcludeAutoFields()` to let PostgreSQL assign the sequence value:

```csharp
var entry = new LogEntry
{
    Id      = Guid.NewGuid(),
    Message = "Application started",
};

await entry.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteAsync();
```

After the insert, `entry.Seq` reflects the value PostgreSQL assigned only if you re-fetch the row or use `WithValuePropagation()`. The plain insert does not return the generated value inline.

## Notes

- `[AutoIncrement]` does **not** imply `[PrimaryKey]`. Primary key and sequence are orthogonal: you can keep a UUID primary key alongside a sequence counter column, as shown above.
- Sequences are `INTEGER` by default. For a `BIGINT` sequence, declare the property as `long` (the sequence type follows the property type).
- `[AutoIncrement]` is valid only on `short`, `int`, or `long` properties. Any other type is reported as error [`SCGDB001`](/database/0.3.2/advanced/generator-diagnostics).

---

# Defining Tables

Map C# classes and enums to PostgreSQL tables using [Table], [FlagTable], and [DatabaseEnum] in Socigy.OpenSource.DB v0.3.2.

## Overview

Every entity that gets its own SQL table must carry a `[Table]` attribute. The source generator reads it to produce all DDL, query builders, insert builders, and migration helpers for that entity. Without it, the class is invisible to the framework.

## The `[Table]` Attribute

`[Table("sql_name")]` maps a C# class or enum to a PostgreSQL table. The string argument becomes the literal table name used in every generated SQL statement.

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }
    public string? Email { get; set; }
}
```

### Classes Must Be `partial`

The source generator augments your class with generated methods. If the class is not declared `partial`, the build fails: the generator cannot emit the second part of the class.

```csharp
// CORRECT
[Table("users")]
public partial class User { ... }

// WRONG. Build error: cannot add members to a non-partial type.
[Table("users")]
public class User { ... }
```

### Enum as Reference Table

Applying `[Table]` to a plain (non-flags) enum tells the migration CLI to create a reference table with three columns: `id` (the underlying integer value), `value` (the enum member name as a string), and `description` (populated from `[Description("...")]` attributes on the members, or `NULL` if absent). The CLI seeds the rows automatically.

```csharp
[Table("user_visibility")]
public enum UserVisibility : short
{
    [Description("Visible to everyone")]
    Public,
    CirclesOnly,
    CustomCircles
}
```

The generated DDL produces a table equivalent to:

```sql
CREATE TABLE "user_visibility" (
    "id"          SMALLINT NOT NULL,
    "value"       TEXT     NOT NULL,
    "description" TEXT,
    PRIMARY KEY ("id")
);

INSERT INTO "user_visibility" ("id", "value", "description") VALUES
    (0, 'Public',        'Visible to everyone'),
    (1, 'CirclesOnly',   NULL),
    (2, 'CustomCircles', NULL);
```

A class that stores a `UserVisibility` column holds the `id` value (a `SMALLINT`) and can join to the reference table when needed.

## The `[FlagTable]` Attribute

`[FlagTable("junction_table_name")]` marks an explicit junction class for a flags-enum relationship. It is the alternative to the auto-generated junction table that `[FlaggedEnum]` creates when no explicit class is provided.

Use `[FlagTable]` when you need extra columns on the junction table, for example an `AssignedAt` timestamp:

```csharp
[Flags]
[Table("user_role")]
public enum UserRole : short
{
    User      = 1,
    Admin     = 2,
    Developer = 4,
    Reviewer  = 8
}

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }

    // Points to the explicit junction class.
    [FlaggedEnumTable(typeof(UserParentRole))]
    public UserRole ParentRole { get; set; }
}

// Explicit junction. Carries an extra column.
[FlagTable("users_parent_role")]
public partial class UserParentRole
{
    [PrimaryKey, ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid UserId { get; set; }

    [PrimaryKey, ForeignKey(typeof(UserRole), OnDelete = DbValues.ForeignKey.Cascade)]
    public short UserRoleId { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime AssignedAt { get; set; }
}
```

`[FlagTable]` classes must also be `partial`.

## The `[DatabaseEnum]` Attribute

`[DatabaseEnum]` on an enum instructs the migration CLI to create a PostgreSQL `ENUM` type instead of a reference table. A column of that type stores the enum member name directly as a PostgreSQL enumeration value.

```csharp
[DatabaseEnum]
public enum OrderStatus
{
    Pending,
    Processing,
    Shipped,
    Delivered,
    Cancelled
}

[Table("orders")]
public partial class Order
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public OrderStatus Status { get; set; }
}
```

The migration CLI emits:

```sql
CREATE TYPE "order_status" AS ENUM (
    'Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled'
);

CREATE TABLE "orders" (
    "id"     UUID         NOT NULL DEFAULT gen_random_uuid(),
    "status" order_status NOT NULL,
    PRIMARY KEY ("id")
);
```

> **NOTE** `[DatabaseEnum]` and `[Table]` on an enum are mutually exclusive. Use `[DatabaseEnum]` for a PostgreSQL native ENUM type. Use `[Table]` for a reference table seeded with integer-to-name pairs.

## The `[Renamed]` Attribute on a Class

`[Renamed("old_table_name")]` on a class tells the migration CLI that the table was previously named `old_table_name`. The CLI emits `ALTER TABLE ... RENAME TO ...` instead of `DROP` plus `CREATE`, preserving all existing rows.

```csharp
// Previously the table was called "app_users".
[Renamed("app_users")]
[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }
}
```

Generated migration SQL:

```sql
ALTER TABLE "app_users" RENAME TO "users";
```

> **WARNING** Remove `[Renamed]` after the migration has been applied and committed. Leaving it in place causes the migration CLI to emit the `RENAME` statement again on the next run.

## Complete Example

The following class combines several table-level attributes:

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("blog_posts")]
[Check("LENGTH(slug) > 0")]
[Unique(nameof(AuthorId), nameof(Slug), Name = "uq_author_slug")]
public partial class BlogPost
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid AuthorId { get; set; }

    [StringLength(200)]
    public string Title { get; set; }

    [StringLength(200)]
    public string Slug { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime PublishedAt { get; set; }

    public bool IsPublished { get; set; }
}
```

---

# Declaring table types

Declare a column shape once with [TableType], bind the table name at runtime, and get fully typed results. The right tool for per-tenant, sharded, and time-partitioned tables.

Some tables are created at runtime: per tenant, per period, or sharded (`audit_2026_06`, `tenant_42_orders`). Their **names** are not known at build time, but their **column shape** usually is. A `[TableType]` class lets you declare that shape once and bind the **name at runtime**, returning the **typed** entity. Only the name is dynamic, so the whole feature stays NativeAOT-safe.

## Declaring a table type

```csharp
using Socigy.OpenSource.DB.Attributes;

[TableType]
public partial class AuditEntry          // the generator implements IDbTableType<AuditEntry>
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }
    public Guid UserId { get; set; }
    public required string Action { get; set; }
    public int Amount { get; set; }
    public DateTime At { get; set; }
}
```

Columns use the same attributes as `[Table]` (`[PrimaryKey]`, `[Column]`, `[JsonColumn]`, `[Encrypted]`, and the rest). A class may carry **both** `[Table("default")]` and `[TableType]`: a fixed default name plus a runtime override.

The generator emits two static entry points on the class:

| Method | Returns | Use |
|--------|---------|-----|
| `AuditEntry.WithTableName(string name)` | `DynamicTable<AuditEntry>` | bind a runtime name |
| `AuditEntry.MapTypeAsync(string name, DbConnection conn, bool force = false, CancellationToken ct = default)` | `Task<DynamicTable<AuditEntry>>` | bind and auto-discover extra columns (see [Runtime operations](/database/0.3.2/dynamic-tables/runtime-operations)) |

## Querying and writing without a context

Bind the runtime name with `WithTableName`, attach a connection, then use the full typed API. `DynamicTable<T>` lives in `Socigy.OpenSource.DB.Core.Dynamic`.

```csharp
await using var conn = factory.Create();
await conn.OpenAsync();

var t = AuditEntry.WithTableName("audit_2026_06");

List<AuditEntry> rows = await t.WithConnection(conn).Query(x => x.UserId == id).ToListAsync();
AuditEntry? first    = await t.WithConnection(conn).Query(x => x.Amount > 100).FirstOrDefaultAsync();
long count           = await t.WithConnection(conn).Query(x => x.Action == "login").CountAsync();
int sum              = await t.WithConnection(conn).SumAsync<int>(x => x.Amount) ?? 0;

await t.WithConnection(conn).InsertAsync(new AuditEntry { UserId = id, Action = "login", At = DateTime.UtcNow });
await t.WithConnection(conn).InsertMultipleAsync(batch);                 // batched multi-row INSERT
await t.WithConnection(conn).UpdateAsync(changed, x => x.Id == changed.Id);
await t.WithConnection(conn).DeleteAsync(x => x.At < cutoff);
```

> **NOTE** Each `WithTableName(...)` returns a fresh handle. Build one per query rather than reusing a handle that already has a predicate.

The handle exposes the full surface: `Query`/`Where`, `OrderBy`, `Limit`/`Offset`, `WithCustomColumns`; the readers `ExecuteAsync` (stream), `ToListAsync`, `FirstOrDefaultAsync`, `ExistsAsync`, `CountAsync`; the aggregates `SumAsync`/`AvgAsync`/`MinAsync`/`MaxAsync`/`ScalarAsync`; the writers `InsertAsync`/`InsertMultipleAsync`/`UpdateAsync`/`DeleteAsync`; and the lifecycle methods covered in [Runtime operations](/database/0.3.2/dynamic-tables/runtime-operations).

## Through the context

The generated context exposes a generic `DynamicTable<T>(name)` that joins the unit-of-work scope, so it enlists the ambient transaction and shares the connection:

```csharp
await db.ExecuteTransactionAsync(async d =>
{
    await d.DynamicTable<AuditEntry>("audit_2026_06").InsertAsync(entry);
    long count = await d.DynamicTable<AuditEntry>("audit_2026_06")
        .Query(x => x.UserId == id)
        .CountAsync();
});
```

> **WARNING** The runtime table name is a SQL identifier you supply (quoted automatically). Treat it like a `[Table]` name, never as untrusted user input.

---

# Runtime operations

Read undeclared columns, auto-discover a live schema, order and paginate, and create or drop a dynamic table at runtime, all from the declared [TableType].

This page covers the runtime-only operations on a `DynamicTable<T>`: undeclared columns, schema auto-mapping, ordering, and table lifecycle. See [Declaring table types](/database/0.3.2/dynamic-tables/declaring) for the declaration and CRUD basics.

## Custom (undeclared) columns

A runtime table may carry extra columns beyond the declared shape. Opt in per query with `WithCustomColumns(...)`, filter on them with the `DB.CustomField<T>("name")` marker inside a normal predicate, and read them back with `TryGetCustomValue<T>(...)` on each row:

```csharp
using static Socigy.OpenSource.DB.Core.SyntaxHelper.DB;

var rows = await AuditEntry.WithTableName("audit_2026_06")
    .WithCustomColumns("region", "score")        // capture extra columns into each row
    .Query(x => CustomField<int>("score") > 10)  // filter on an undeclared column
    .WithConnection(conn)
    .ToListAsync();

foreach (var r in rows)
    if (r.TryGetCustomValue<string>("region", out var region))
        Console.WriteLine(region);
```

`CustomField<T>` is a translation marker, rewritten into SQL at build time; it never runs at runtime. Custom values are dictionary-backed, so they cost slightly more than a declared property. `TryGetCustomValue<T>` converts the stored value to `T` and returns `false` when the column was not captured.

### Auto-mapping a live schema

Skip naming the extras: `MapTypeAsync(name, conn)` introspects the live table **once**, discovers every column beyond the declared shape, binds them, and caches the resolved schema per (type, table name):

```csharp
var t = await AuditEntry.MapTypeAsync("audit_2026_06", conn);   // discovers + binds extras, then cached
var rows = await t.Query(x => x.UserId == id).ToListAsync();     // declared props + every extra column
```

## Ordering and pagination

On a dynamic table `OrderBy` takes a **raw SQL fragment** (the column shape is bound late, so there is no lambda form). Pair it with `Limit`/`Offset`:

```csharp
var page = await AuditEntry.WithTableName("audit_2026_06")
    .WithConnection(conn)
    .Query(x => x.UserId == id)
    .OrderBy("\"at\" DESC")
    .Limit(20).Offset(40)
    .ToListAsync();
```

Use the generated `{Property}ColumnName` constants to keep raw fragments in sync with the schema, for example `OrderBy($"{AuditEntry.AtColumnName} DESC")`.

## Table lifecycle (create and drop)

Because `[TableType]` tables are not part of the migration history, the type can create and drop its own table at runtime, with DDL derived from the declared shape:

```csharp
var t = AuditEntry.WithTableName("audit_2026_06").WithConnection(conn);

await t.InstantiateAsync();           // CREATE TABLE IF NOT EXISTS "audit_2026_06" (...declared columns...)
bool exists = await t.InstanceExistsAsync();
await t.DeleteInstanceAsync();        // DROP TABLE IF EXISTS "audit_2026_06"
```

## Notes and limits

- Results are **typed `T`**, not `dynamic`, so dynamic tables work under **NativeAOT**. Only custom-column access goes through a dictionary.
- `InstantiateAsync` maps the common CLR types (`Guid`, `int`/`long`/`short`, `string`, `bool`, `decimal`/`double`/`float`, `DateTime`, `byte[]`), `[Encrypted]` to `bytea`, and JSON to `jsonb`. `[Default]` expressions are **not** emitted in the runtime DDL, so provide those values yourself or create the table out of band. Enum columns fall back to `text`.
- `[TableType]`-only classes are never touched by the migration runner.
- `UpdateAsync` requires a predicate and excludes the primary key (and, by default, auto-increment columns) from the SET list.

---

# Configuration

Full reference for socigy.json, the project-level file that controls migration naming, DI code generation, and connection factory output.

## Where socigy.json lives

Place `socigy.json` in the **project root of your DB class library**, the same directory as its `.csproj` file. It is read by the migration CLI tool and by the build target that triggers code generation.

```filetree
MyApp.DB/
├── MyApp.DB.csproj
├── socigy.json          <- here
├── Users/
│   └── User.cs
└── Socigy/
    └── Procedures/
```

> **NOTE** Do not place `socigy.json` in the API project or at the solution root. The tooling looks for it relative to the DB class library, not the entry-point project.

If the file is missing when the CLI tool first runs, it creates one with default values. You can also create it manually.

---

## Minimal example

In practice only `platform` and `databaseName` are needed, the rest carry sensible defaults:

```json
{
  "database": {
    "platform": "postgresql",
    "databaseName": "AuthDb"
  }
}
```

## Full example

```json
{
  "database": {
    "platform": "postgresql",
    "databaseName": "AuthDb",
    "migrationNameTemplate": "${Name}",
    "generateDbConnectionFactory": true,
    "generateWebAppExtensions": true
  },
  "shouldShowMessageOnEmptyMigrationGeneration": true
}
```

---

## Field reference

### database.platform

**Type:** string
**Required:** yes

The target database engine. The tooling is built to support multiple engines later, only PostgreSQL is supported in this release.

| Value          | Notes                          |
|----------------|--------------------------------|
| `"postgresql"` | Canonical value                |
| `"postgres"`   | Alias for `"postgresql"`       |
| `"postgre"`    | Alias for `"postgresql"`       |
| `"npgsql"`     | Recognized by the migration tool only (see note) |

Other values are rejected with a build error.

> **NOTE** Prefer the canonical `"postgresql"` (or `"postgres"`/`"postgre"`). The `"npgsql"` value is accepted by the migration CLI tool when generating DDL, but the source generator does **not** recognize it, so the keyed `IDbConnectionFactory`, the `I{Db}` context, and the DI extension methods are not emitted for it.

---

### database.databaseName

**Type:** string
**Required:** yes

A logical name for the database. This value drives code generation in three places:

1. **DI registration names**, where the generated extensions become `Add{databaseName}()` and `Add{databaseName}Context()`, and the `IDbConnectionFactory` is keyed by this name.
2. **Context types**, where the generator emits `I{databaseName}` and `{databaseName}Context` plus the migration helper `EnsureLatest{databaseName}Migration()`.
3. **Connection string lookup key**, where the section `ConnectionStrings:{databaseName}` in `appsettings.json` is read by the generated factory.

Example: `"databaseName": "AuthDb"` produces `AddAuthDb()`, `AddAuthDbContext()`, `IAuthDb`, and reads `ConnectionStrings:AuthDb`.

---

### database.migrationNameTemplate

**Type:** string
**Default:** `"${Name}"`

Controls how migration file names are generated. Two placeholders are supported:

| Placeholder    | Replaced with                                    |
|----------------|--------------------------------------------------|
| `${Name}`      | The migration name you supply at generation time |
| `${Timestamp}` | A UTC timestamp at generation time               |

Examples:

```json
"migrationNameTemplate": "${Name}"
```

```json
"migrationNameTemplate": "${Timestamp}_${Name}"
```

```json
"migrationNameTemplate": "${Timestamp}"
```

Including `${Timestamp}` keeps migrations sorted chronologically in the file system.

---

### database.generateDbConnectionFactory

**Type:** boolean
**Default:** `true`

When `true`, the build emits:

- An `IDbConnectionFactory` implementation keyed to `databaseName`.
- An `Add{DatabaseName}()` DI extension on `IServiceCollection`.

Set to `false` only if you manage connections entirely outside the generated DI infrastructure.

---

### database.generateWebAppExtensions

**Type:** boolean
**Default:** `true`

When `true` (and `generateDbConnectionFactory` is also `true`), additional extension methods are generated for:

- `WebApplicationBuilder`, giving `builder.AddAuthDb()`.
- `HostApplicationBuilder`, giving `builder.AddAuthDb()`.

Set to `false` if you use a non-web host and do not need `WebApplicationBuilder` extensions.

---

### shouldShowMessageOnEmptyMigrationGeneration

**Type:** boolean
**Default:** `true`

When `true`, the CLI tool reports that no schema changes were detected and aborts without writing a migration file. Set to `false` to suppress this message in CI pipelines where it would add noise.

---

## appsettings.json connection string format

The generated `IDbConnectionFactory` reads connection strings from the standard .NET configuration system. The expected structure nests under `ConnectionStrings:{databaseName}`:

```json
{
  "ConnectionStrings": {
    "AuthDb": {
      "Default": "Host=localhost;Port=5432;Username=postgres;Password=secret",
      "ReadOnly": "Host=replica.example.com;Port=5432;Username=ro_user;Password=secret"
    }
  }
}
```

> **WARNING** Do **not** include `Database=` in the connection string. The generated factory appends `Database={databaseName}` when it creates a connection.

Multiple connection keys are supported within the same named database. The `"Default"` key is used when `IDbConnectionFactory.Create()` is called with no argument (or `null`). Any other key can be requested by name:

```csharp
// Uses the "Default" connection string
var conn = factory.Create();

// Uses the "ReadOnly" connection string
var readConn = factory.Create("ReadOnly");
```

> **NOTE** The connection returned by `Create()` is not pre-opened. The query builders open it automatically if it is still closed at `ExecuteAsync()` time, so calling `conn.OpenAsync()` yourself is optional. Doing so makes the connection's lifetime explicit and lets you reuse one open connection across several builders.

---

## DI registration at startup

After adding `socigy.json` with `databaseName: "AuthDb"` and running `dotnet build`, register both the connection factory and the context:

```csharp
using Socigy.OpenSource.DB.AuthDb.Extensions;

// WebApplicationBuilder or HostApplicationBuilder
builder.AddAuthDb();                  // registers IDbConnectionFactory (keyed "AuthDb")
builder.Services.AddAuthDbContext();  // registers ISocigyDatabaseFactory<IAuthDb>
```

`AddAuthDb()` registers:

- `IDbConnectionFactory` (keyed `"AuthDb"`), which creates connections from `appsettings.json`.
- `IMigrationManager` (keyed `"AuthDb"`), which applies pending migrations.

`AddAuthDbContext()` registers the context factory `ISocigyDatabaseFactory<IAuthDb>`, the recommended way to run data access. See [Database context](/database/0.3.2/core-concepts/database-context).

To apply all pending migrations on startup:

```csharp
var app = builder.Build();
await app.EnsureLatestAuthDbMigration();
```

---

# Installation

Add Socigy.OpenSource.DB to a .NET project, verify the source generator ran, and confirm every prerequisite is met.

## Requirements

| Component  | Minimum version | Notes                                                                               |
|------------|-----------------|-------------------------------------------------------------------------------------|
| .NET       | 10.0            | Required by the Roslyn 4.x incremental source generator                             |
| PostgreSQL | 14.0            | Currently the only supported database engine, required for `gen_random_uuid()` and similar |
| Npgsql     | 6.0             | Transitive dependency when `platform` is `"postgresql"`, needed for `DateOnly` and `TimeOnly` |

> **NOTE** `platform` in `socigy.json` selects the target database engine. PostgreSQL is the only engine supported in this release. The tooling is built to add more engines later.

> **NOTE** Npgsql arrives as a transitive dependency on the PostgreSQL platform, so you rarely add it yourself. If your project already references Npgsql at a different version, pin it in your `.csproj` to avoid conflicts.

---

## Add the NuGet package

```bash
dotnet add package Socigy.OpenSource.DB
```

Or reference it directly in your `.csproj`:

```xml
<ItemGroup>
  <PackageReference Include="Socigy.OpenSource.DB" Version="0.3.2" />
</ItemGroup>
```

### What the package contains

`Socigy.OpenSource.DB` is a single reference that installs three components together:

```infotabs
## Core runtime DLL
- The base classes and interfaces shared across all DB operations.
- Manages the Npgsql connection lifecycle for you.

## Roslyn source generator
- Runs during `dotnet build`, with no runtime reflection.
- Reads `[Table]` and `[Column]` attributes and emits typed C# methods.

## Migration CLI tool
- Bundled inside the same package, invoked through an MSBuild target.
- Build with `-c DB_Migration` to generate DDL from your models.
```

One package reference covers all three. Adding `Socigy.OpenSource.DB` is the only install step.

---

## Make your class partial and build

The source generator augments your model classes by adding members in a companion generated file. Each class that maps to a table must be declared `partial`:

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; } = "anonymous";

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}
```

> **NOTE** Schema attributes and the `DbDefaults` constants live in `Socigy.OpenSource.DB.Attributes`. A single using covers `[Table]`, `[Column]`, `[PrimaryKey]`, `[Default]`, and the rest. The one exception is `[Nullable]`, which lives in `Socigy.OpenSource.DB.Core.Attributes`.

Then build:

```bash
dotnet build
```

A successful build means the source generator ran. Inspect the output under `obj/{Configuration}/net{version}/generated/Socigy.OpenSource.DB.SourceGenerator/` to find a `User.g.cs` file containing the generated `Insert()`, `Query()`, `Update()`, and `Delete()` methods.

> **TIP** Add `obj/` to your `.gitignore`. Generated files are recreated on every build and should never be committed.

---

## Verify the generated output

To write the generated files to disk, add `<EmitCompilerGeneratedFiles>true</EmitCompilerGeneratedFiles>` to your `.csproj`:

```xml
<PropertyGroup>
  <EmitCompilerGeneratedFiles>true</EmitCompilerGeneratedFiles>
</PropertyGroup>
```

After building, confirm the generated files exist:

```bash
ls obj/Debug/net10.0/generated/Socigy.OpenSource.DB.SourceGenerator/Socigy.OpenSource.DB.SourceGenerator.Program/
```

You will see one `.g.cs` file per annotated class. The exact path varies by configuration (`Debug`, `Release`, `DB_Migration`) and target framework. Opening a generated file is a useful debugging technique: it shows exactly what SQL the builder produces.

In Visual Studio and JetBrains Rider, "Go to Definition" on a generated method navigates straight to the generated file.

---

## Npgsql as a standalone reference

Because Npgsql is a transitive dependency, most projects do not need an explicit reference. If you call Npgsql APIs directly (for example `NpgsqlDataSource` or `NpgsqlConnection`), add it explicitly:

```bash
dotnet add package Npgsql
```

```csharp
await using var conn = new NpgsqlConnection(
    "Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=secret");
await conn.OpenAsync();
```

Never hard-code credentials in source files. Read connection strings from environment variables or `appsettings.json` in production.

---

## Alternative download: GitHub Packages

If you cannot reach NuGet.org, the package is also published to GitHub Packages. Add the GitHub NuGet source first:

```bash
dotnet nuget add source https://nuget.pkg.github.com/WailedParsley36/index.json \
  --name github-socigy \
  --username YOUR_GITHUB_USERNAME \
  --password YOUR_GITHUB_PAT
```

Then install as normal:

```bash
dotnet add package Socigy.OpenSource.DB --source github-socigy
```

> **NOTE** GitHub Packages requires authentication even for public packages. Generate a Personal Access Token with the `read:packages` scope at [github.com/settings/tokens](https://github.com/settings/tokens).

---

## Dependencies

The package declares two dependencies used by the diagnostics pipeline:

- `System.Diagnostics.DiagnosticSource` for the OpenTelemetry `ActivitySource` and `Meter`.
- `Microsoft.Extensions.Logging.Abstractions` for `ILogger` support.

On .NET 6.0 and later these ship in the shared framework, so they add nothing to a typical web or console app. They are declared so non-framework consumers also resolve them. No action is required on your part.

---

## Next steps

- Walk through a complete example in [Quickstart](/database/0.3.2/getting-started/quickstart).
- Build a real application end to end in the [Tutorial](/database/0.3.2/tutorial/overview).
- Understand the recommended directory layout in [Project structure](/database/0.3.2/getting-started/project-structure).
- Configure `socigy.json` for migrations and DI code generation in [Configuration](/database/0.3.2/getting-started/configuration).

---

# Project structure

Recommended layout for a project that uses Socigy.OpenSource.DB, covering the DB class library, SQL procedure files, and generated output.

## Recommended layout

There are no hard requirements on directory structure, but isolating DB models in a dedicated class library is strongly recommended. It lets multiple consumers (API, background workers, test projects) reference the same typed models without pulling in unrelated dependencies.

```filetree
MyApp.sln
├── MyApp.DB/                          <- class library for DB models
│   ├── MyApp.DB.csproj
│   ├── socigy.json                    <- migration and codegen config (project root)
│   ├── Users/
│   │   ├── User.cs                    <- [Table] partial class
│   │   └── UserRole.cs                <- [Flags] enum mapped with [Table]
│   ├── Products/
│   │   └── Product.cs
│   └── Socigy/
│       └── Procedures/                <- .sql files for procedure mapping
│           └── GetUserById.sql
└── MyApp.API/
    └── MyApp.API.csproj
```

---

## The DB project file

The DB class library references `Socigy.OpenSource.DB`. If you use [Procedure mapping](/database/0.3.2/advanced/procedure-mapping), declare `.sql` files as `<AdditionalFiles>` so the source generator can read them at build time:

```xml
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Socigy.OpenSource.DB" Version="0.3.2" />
  </ItemGroup>

  <!-- Only needed for procedure mapping -->
  <ItemGroup>
    <AdditionalFiles Include="Socigy\Procedures\**\*.sql" />
  </ItemGroup>

  <!-- Optionally emit generated files into the project tree for inspection -->
  <PropertyGroup>
    <EmitCompilerGeneratedFiles>true</EmitCompilerGeneratedFiles>
  </PropertyGroup>
</Project>
```

> **NOTE** The `AdditionalFiles` glob is required only if you use procedure mapping. Omit it for projects that use table-based queries alone.

> **TIP** Setting `<EmitCompilerGeneratedFiles>true</EmitCompilerGeneratedFiles>` writes generated files into `$(IntermediateOutputPath)/generated/` so IDEs can index them more reliably. It is optional.

---

## Model class requirements

Every class that maps to a database table must meet two conditions:

1. **Decorated with `[Table("table_name")]`**, which tells the generator the SQL table this class represents.
2. **Declared `partial`**, which lets the generator add members (query methods, `IDbTable` implementation, column constants) in a companion generated file.

```csharp
using Socigy.OpenSource.DB.Attributes;

// Correct: the generator augments this class
[Table("products")]
public partial class Product { /* ... */ }

// Wrong: the generator skips this class, no query methods are emitted
[Table("products")]
public class Product { /* ... */ }
```

The class can live in any namespace. Nesting inside another class is not supported.

> **NOTE** Schema attributes and the `DbDefaults` constants resolve from `Socigy.OpenSource.DB.Attributes`. The only attribute outside that namespace is `[Nullable]`, in `Socigy.OpenSource.DB.Core.Attributes`.

---

## Generated files

The source generator writes one `.g.cs` file per annotated class under the intermediate output path:

```plaintext
obj/{Configuration}/net{version}/generated/Socigy.OpenSource.DB.SourceGenerator/Socigy.OpenSource.DB.SourceGenerator.Program/
```

For example, a `Debug` build targeting `net10.0` uses:

```plaintext
obj/Debug/net10.0/generated/Socigy.OpenSource.DB.SourceGenerator/Socigy.OpenSource.DB.SourceGenerator.Program/
```

To have the IDE index these files reliably, add `<EmitCompilerGeneratedFiles>true</EmitCompilerGeneratedFiles>` to your `.csproj`.

Never edit these files directly, they are recreated on every build. Each generated file contains:

- The `IDbTable` interface implementation (`GetTableName()`, `GetColumns()`, `GetPrimaryColumns()`, `GetColumn()`, `GetDbColumnName()`).
- Static factory methods: `Query()`, `Insert()`, `Update()`, `Delete()`.
- Instance shortcut methods: `user.Insert()`, `user.Update()`, `user.Delete()`.
- Column name constants (see below).

When a `databaseName` is configured, the generator also emits the database context (`IAppDb` / `AppDbContext`), the per-table sets, and the DI extensions. See [Database context](/database/0.3.2/core-concepts/database-context).

---

## Static column name constants

The generator emits a `{PropertyName}ColumnName` constant on each model class for every mapped property, using the snake_case database column name:

```csharp
User.UsernameColumnName   // => "username"
User.CreatedAtColumnName  // => "created_at"
User.IdColumnName         // => "id"
```

These help when building dynamic SQL fragments or logging query details without hard-coding string literals.

---

## Keeping obj/ out of source control

Generated files should never be committed. Add `obj/` to your `.gitignore`:

```plaintext
obj/
bin/
```

The generator recreates all output on the next `dotnet build`, so nothing is lost by excluding it.

---

# Quickstart

Declare a table, wire up the generated context, then insert, read, update, and delete rows through ISocigyDatabaseFactory.

## Prerequisites

- .NET 10 SDK installed and on your PATH.
- A running PostgreSQL 14+ instance, currently the only supported engine.
- `Socigy.OpenSource.DB` added to your project. See [Installation](/database/0.3.2/getting-started/installation).

This quickstart uses the recommended context API: you inject a factory, run work inside a scope, and the connection is managed for you. For raw connection access see [Connections & DI](/database/0.3.2/core-concepts/connections-and-di).

---

## 1. Declare a table

Annotate a `partial` class with `[Table]` and column attributes. The class **must** be `partial` so the source generator can augment it with query methods in a companion file.

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; } = "anonymous";

    public string? Email { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}
```

> **NOTE** A C# property initializer such as `= "anonymous"` is more than a C# default. The generator reads it and emits `DEFAULT 'anonymous'` in the DDL, so the database enforces the same default even for rows inserted outside your application.

> **NOTE** All schema attributes and the `DbDefaults` constants come from `Socigy.OpenSource.DB.Attributes`, a single using.

Build the project once to run the source generator:

```bash
dotnet build
```

---

## 2. Generate the schema

Build the model project in the `DB_Migration` configuration to produce the PostgreSQL DDL from your annotated classes:

```bash
dotnet build MyApp.DB/MyApp.DB.csproj -c DB_Migration
```

The migration for the `User` class above resolves to:

```sql
CREATE TABLE IF NOT EXISTS "users" (
    "id"         UUID      NOT NULL DEFAULT gen_random_uuid(),
    "username"   TEXT      NOT NULL DEFAULT 'anonymous',
    "email"      TEXT,
    "created_at" TIMESTAMP NOT NULL DEFAULT timezone('utc', now()),
    PRIMARY KEY ("id")
);
```

See [CLI tool](/database/0.3.2/migration/cli-tool) and [Applying migrations](/database/0.3.2/migration/applying) for the full workflow.

---

## 3. Register the database and context

With `databaseName: "AppDb"` in `socigy.json`, the generator emits `AddAppDb()`, `AddAppDbContext()`, and `EnsureLatestAppDbMigration()`. Wire them up at startup:

```csharp
using Socigy.OpenSource.DB.AppDb.Extensions;

var builder = WebApplication.CreateBuilder(args);

builder.AddAppDb();                  // registers IDbConnectionFactory
builder.Services.AddAppDbContext();  // registers ISocigyDatabaseFactory<IAppDb>

var app = builder.Build();

await app.EnsureLatestAppDbMigration();  // applies pending migrations on startup
```

The connection string lives in `appsettings.json` under `ConnectionStrings:AppDb:Default`. Do not include `Database=`, the factory appends it from `databaseName`:

```json
{
  "ConnectionStrings": {
    "AppDb": {
      "Default": "Host=localhost;Port=5432;Username=postgres;Password=secret"
    }
  }
}
```

See [Configuration](/database/0.3.2/getting-started/configuration) for every field.

---

## 4. Inject the factory

Inject `ISocigyDatabaseFactory<IAppDb>` and run work inside a scope. `ExecuteAsync` opens a non-transactional scope, `ExecuteTransactionAsync` commits when the callback returns and rolls back if it throws. Table sets are pluralized: `User` becomes `db.Users`.

```csharp
using Socigy.OpenSource.DB.AppDb.Context;
using Socigy.OpenSource.DB.Core.Context;

public class UserService(ISocigyDatabaseFactory<IAppDb> db)
{
    // ...
}
```

---

## 5. Insert a row

`InsertAsync` skips columns with a `[Default]` attribute or a C# initializer by default, letting PostgreSQL generate `Id` and `CreatedAt`:

```csharp
await db.ExecuteAsync(d => d.Users.InsertAsync(
    new User { Username = "alice", Email = "alice@example.com" }));
```

`InsertAsync` returns `Task<bool>`. To write the auto fields yourself (for example a pre-generated primary key), pass `includeAutoFields: true`:

```csharp
await db.ExecuteAsync(d => d.Users.InsertAsync(
    new User { Id = Guid.NewGuid(), Username = "bob" },
    includeAutoFields: true));
```

Batch many rows with `InsertMultipleAsync`, which emits multi-row `INSERT` statements:

```csharp
await db.ExecuteTransactionAsync(d => d.Users.InsertMultipleAsync(newUsers));
```

---

## 6. Read rows

`ToListAsync` materializes matching rows, and `FirstOrDefaultAsync` returns a single row or `null`. Both run inside the scope, so nothing lazy escapes the connection:

```csharp
var alice = await db.ExecuteAsync(d =>
    d.Users.FirstOrDefaultAsync(u => u.Username == "alice"));

var recent = await db.ExecuteAsync(d =>
    d.Users.ToListAsync(u => u.CreatedAt > DateTime.UtcNow.AddDays(-7)));
```

Omit the predicate on `ToListAsync` to fetch every row. Use `ExistsAsync` and `CountAsync` for existence and tallies:

```csharp
bool taken  = await db.ExecuteAsync(d => d.Users.ExistsAsync(u => u.Username == "alice"));
long total  = await db.ExecuteAsync(d => d.Users.CountAsync());
```

For streaming large result sets row by row, see `ForEachAsync` in [Database context](/database/0.3.2/core-concepts/database-context). For richer reads, see [SELECT](/database/0.3.2/querying/reading/select) and [WHERE](/database/0.3.2/querying/reading/where).

---

## 7. Update a row

`UpdateAsync` matches on the primary key and returns the number of affected rows:

```csharp
alice.Username = "alice-v2";
int updated = await db.ExecuteAsync(d => d.Users.UpdateAsync(alice));
```

See [UPDATE](/database/0.3.2/querying/writing/update) for partial-column updates.

---

## 8. Delete rows

`DeleteAsync` takes a predicate and returns the number of deleted rows:

```csharp
int deleted = await db.ExecuteAsync(d =>
    d.Users.DeleteAsync(u => u.Username == "alice-v2"));
```

See [DELETE](/database/0.3.2/querying/writing/delete) for more.

---

## Next steps

- Build the same concepts into a full application in the [Tutorial](/database/0.3.2/tutorial/overview).
- Learn every modeling attribute in [Tables](/database/0.3.2/defining-models/tables) and [Columns](/database/0.3.2/defining-models/columns).
- Group multiple writes atomically with [Transactions](/database/0.3.2/core-concepts/transactions).
- Explore joins and set operations in [Joins](/database/0.3.2/querying/reading/joins) and [Set operations](/database/0.3.2/querying/reading/set-operations).
- Solve common problems with [Recipes](/database/0.3.2/recipes/audit-columns).

---

# ASP.NET Core

Integrate Socigy.OpenSource.DB into an ASP.NET Core application using the generated DI extension methods.

## Prerequisites

- `socigy.json` configured in your DB class library with `generateDbConnectionFactory: true` and `generateWebAppExtensions: true`
- At least one successful `dotnet build -c DB_Migration` run (generates the DI extension methods)
- Your API project references the DB class library

## Registering the database

Call the generated `AddAuthDb()` extension in `Program.cs`. Three overloads are available depending on your host builder type:

```csharp
// WebApplicationBuilder (most common for ASP.NET Core)
builder.AddAuthDb();

// IServiceCollection (generic host, test fixtures, etc.)
builder.Services.AddAuthDb();

// HostApplicationBuilder (generic host)
builder.AddAuthDb();
```

Replace `AuthDb` with whatever `databaseName` you set in `socigy.json`.

## What gets registered

| Service | Lifetime | Key |
|---------|----------|-----|
| `IDbConnectionFactory` | Singleton | `"AuthDb"` |
| `IMigrationManager` | Singleton | `"AuthDb"` |

Both services are registered as [keyed services](https://learn.microsoft.com/en-us/dotnet/core/extensions/dependency-injection#keyed-services). If you have multiple databases, call `AddAuthDb` and `AddUserDb` (and so on) independently. Each registers under its own key.

## appsettings.json

The generated factory reads the connection string from `ConnectionStrings.{databaseName}`:

```json
{
  "ConnectionStrings": {
    "AuthDb": {
      "Default": "Host=localhost;Port=5432;Username=app;Password=secret;Pooling=true;Minimum Pool Size=2;Maximum Pool Size=20"
    }
  }
}
```

> **NOTE** Do not include `Database=`. The factory appends it automatically using `databaseName` from `socigy.json`.

Add `appsettings.Development.json` for local overrides and keep production secrets out of source control.

## Applying migrations at startup

Call the generated `EnsureLatestAuthDbMigration()` extension after building the app:

```csharp
var app = builder.Build();

await app.EnsureLatestAuthDbMigration();  // runs pending migrations before serving traffic

app.MapControllers();
app.Run();
```

This is safe to call on every startup. It applies only migrations that are not yet recorded in `_scg_migrations`.

## Full Program.cs example

```csharp
using Socigy.OpenSource.DB.AuthDb.Extensions;  // generated extension methods

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

// Register the DB; reads ConnectionStrings:AuthDb from appsettings.json
builder.AddAuthDb();

var app = builder.Build();

// Apply any pending schema migrations
await app.EnsureLatestAuthDbMigration();

app.MapControllers();
app.Run();
```

## Injecting IDbConnectionFactory

Inject `IDbConnectionFactory` into services, controllers, or minimal API handlers using the `[FromKeyedServices]` attribute:

```csharp
public class UserService(
    [FromKeyedServices("AuthDb")] IDbConnectionFactory db)
{
    public async Task<User?> FindByUsernameAsync(string username)
    {
        await using var conn = db.Create();  // gets the "Default" connection
        await conn.OpenAsync();

        await foreach (var user in User.Query(x => x.Username == username)
            .WithConnection(conn).ExecuteAsync())
        {
            return user;
        }
        return null;
    }
}
```

## Multiple connection keys

If your `appsettings.json` defines multiple sub-keys (e.g. `Default` and `ReadOnly`), pass the sub-key name to `Create()`:

```csharp
// Read-heavy query: use the read replica
await using var conn = db.Create("ReadOnly");
await conn.OpenAsync();

await foreach (var report in Report.Query()
    .WithConnection(conn).ExecuteAsync())
{ ... }
```

## Multiple databases

Run the generator for each DB class library with its own `databaseName`. Register each in `Program.cs`:

```csharp
builder.AddAuthDb();        // registers IDbConnectionFactory keyed "AuthDb"
builder.AddUserDb();        // registers IDbConnectionFactory keyed "UserDb"
builder.AddAnalyticsDb();
```

Inject the one you need by key:

```csharp
public class ReportService(
    [FromKeyedServices("AnalyticsDb")] IDbConnectionFactory analyticsDb,
    [FromKeyedServices("UserDb")]      IDbConnectionFactory userDb)
{ ... }
```

## Minimal API example

```csharp
app.MapGet("/users/{username}", async (
    string username,
    [FromKeyedServices("AuthDb")] IDbConnectionFactory db) =>
{
    await using var conn = db.Create();
    await conn.OpenAsync();

    await foreach (var user in User.Query(x => x.Username == username)
        .WithConnection(conn).ExecuteAsync())
    {
        return Results.Ok(user);
    }

    return Results.NotFound();
});
```

> **TIP** Each `db.Create()` call constructs a new connection from Npgsql's connection pool. This is efficient for most workloads. For extreme throughput scenarios, create an `NpgsqlDataSource` manually and register it as a singleton alongside the factory (see [Manual connections](../integrations/manual-connections)).

## Database context & observability (v0.2.0)

Register the testable context factory alongside the existing connection factory, and wire OpenTelemetry to the library's source/meter:

```csharp
var builder = WebApplication.CreateBuilder(args);

builder.AddAuthDb();                  // IDbConnectionFactory + IMigrationManager (existing)
builder.Services.AddAuthDbContext();  // ISocigyDatabaseFactory<IAuthDb> (new)

builder.Services.AddOpenTelemetry()
    .WithTracing(t => t.AddSource("Socigy.OpenSource.DB").AddOtlpExporter())
    .WithMetrics(m => m.AddMeter("Socigy.OpenSource.DB").AddOtlpExporter());

var app = builder.Build();

// SQL logging (parameter values stay off unless explicitly enabled)
SocigyDbDiagnostics.Configure(o =>
{
    o.LoggerFactory = app.Services.GetRequiredService<ILoggerFactory>();
    o.LogLevel = LogLevel.Information;
});

await app.EnsureLatestAuthDbMigration();
app.Run();
```

Inject `ISocigyDatabaseFactory<IAuthDb>` into your services and run work inside `ExecuteAsync` / `ExecuteTransactionAsync`. See [Database context & unit of work](/database/0.3.2/core-concepts/database-context).

---

# HashiCorp Vault

Optional Socigy.OpenSource.DB.HashiCorp package: field encryption keyed from Vault and rotating PostgreSQL credentials from Vault's Database secrets engine, wired with one DI call each.

The optional **`Socigy.OpenSource.DB.HashiCorp`** package integrates Socigy with [HashiCorp Vault](https://www.vaultproject.io/) for two things:

1. **Field encryption.** It supplies the `IFieldEncryptor` for [`[Encrypted]` columns](/database/0.3.2/defining-models/encrypted-columns), keyed from Vault.
2. **Rotating DB credentials.** It supplies an `IDbCredentialsProvider` that leases short-lived PostgreSQL credentials from Vault's Database secrets engine, which the generated connection factory consumes automatically.

```bash
dotnet add package Socigy.OpenSource.DB.HashiCorp
```

> **NOTE** This package depends on the main `Socigy.OpenSource.DB` package and `VaultSharp`. The two Vault features are independent: register either or both.

## Field encryption

The data-encryption key is read from a **Vault KV-v2 secret at startup** and used for fast, local AES-256-CBC + HMAC encryption. Per-field crypto stays synchronous with no Vault round-trip per row. Store a Base64-encoded 32-byte key in Vault:

```bash
vault kv put secret/socigy/db-encryption-key key="$(openssl rand -base64 32)"
```

Then register it:

```csharp
builder.Services.AddSocigyVaultEncryption(o =>
{
    o.Address = "https://vault.example.com:8200";
    o.Token = builder.Configuration["Vault:Token"];     // or AppRoleId + AppRoleSecretId
    o.KvMountPoint = "secret";
    o.KeySecretPath = "socigy/db-encryption-key";
    o.KeyField = "key";
});
```

At host start the package reads the key and installs the ambient `SocigyFieldEncryption` encryptor, so every `[Encrypted]` column just works.

> **NOTE** Key material is fetched into process memory for local crypto. Rotating the key means updating the KV secret and re-encrypting existing rows. (A Transit data-key envelope mode that keeps old rows readable across rotations is a planned enhancement.)

## Rotating database credentials

Configure Vault's Database secrets engine with a role per database, then map each Socigy database name to its Vault role:

```csharp
builder.Services.AddSocigyVaultCredentials(o =>
{
    o.Address = "https://vault.example.com:8200";
    o.AppRoleId = builder.Configuration["Vault:RoleId"];
    o.AppRoleSecretId = builder.Configuration["Vault:SecretId"];
    o.DatabaseMountPoint = "database";
    o.BaseConnectionString = "Host=db.internal;Port=5432;Pooling=true";   // no user/pass
    o.DatabaseRoles["AuthDb"] = "auth-db-role";
    o.DatabaseRoles["UserDb"] = "user-db-role";
    o.RefreshInterval = TimeSpan.FromMinutes(30);   // fallback only; renewal normally tracks the lease TTL
});

builder.AddAuthDb();   // the generated factory picks up IDbCredentialsProvider automatically
builder.AddUserDb();
```

How it fits together:

- At startup the package leases credentials for each configured database and composes a connection string (`BaseConnectionString` + the leased `Username`/`Password`), cached in memory. The string is built with `DbConnectionStringBuilder`, so leased passwords containing `;`, `=`, quotes or spaces are escaped correctly.
- The generated connection factory calls the provider **synchronously** for the current connection string each time it opens a connection. See [Connections & DI](/database/0.3.2/core-concepts/connections-and-di).
- A background timer renews credentials at **~2/3 of the actual lease TTL** (`LeaseDurationSeconds` returned by Vault), so they are refreshed before they expire even when the role issues short leases. `RefreshInterval` is only a fallback used when the lease TTL is unknown. When credentials rotate, new connections use the new string and Npgsql's old pool drains naturally.

## Authentication

Both `Add…` calls accept either a **token** (`Token`) or **AppRole** (`AppRoleId` + `AppRoleSecretId`). AppRole is recommended for production workloads.

The Vault **auth token is kept alive automatically** by a background service: it renews the token (renew-self) before it expires, and when renewal can no longer extend it (max TTL) and AppRole credentials are configured, it re-logs-in for a fresh token. A **static, non-renewable token cannot be kept alive** — for long-running services use AppRole, a periodic token, or a renewable token. The library logs a clear error if it detects a token it cannot keep alive.

> **WARNING** Use an `https://` Vault `Address` in production. If the address is plaintext `http://` to a non-loopback host, the library logs a warning — tokens, keys, and leased credentials would otherwise travel unencrypted. Plain `http://` to loopback is fine for local development.

## Diagnostics

All background actions are observable so admins can track what the library does:

- **OpenTelemetry spans** under the existing `Socigy.OpenSource.DB` ActivitySource: `vault.encryption.key.fetch`, `vault.credentials.lease` (with `db.name`, `vault.database.role`, and lease-duration tags), and `vault.token.renew` / `vault.token.relogin` for background auth-token upkeep. Subscribe with `AddSource("Socigy.OpenSource.DB")` (see [Diagnostics & OpenTelemetry](/database/0.3.2/observability/diagnostics)).
- **`ILogger` messages** under categories `Socigy.OpenSource.DB.Vault.Encryption` and `Socigy.OpenSource.DB.Vault.Credentials`: key load, each credential lease (database, role, user, lease seconds), renewal ticks, and failures. The connection factory also logs when it refreshes rotating credentials, and `SocigyFieldEncryption.Configure` logs when an encryptor is installed.

---

# Manual connections

Use Socigy.OpenSource.DB without DI by managing NpgsqlConnection and NpgsqlDataSource directly.

## When to use manual connections

The generated DI extensions (`AddAuthDb`) are optional. Every query builder, insert, update, delete, join, and set operation works with a raw Npgsql connection, no DI container required. This is useful for:

- Console tools and scripts
- Unit and integration tests
- Non-ASP.NET Core hosts (AWS Lambda, Azure Functions, gRPC services)
- Projects that already have their own connection management

## Opening a connection directly

```csharp
using Npgsql;

await using var conn = new NpgsqlConnection(
    "Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=secret");
await conn.OpenAsync();

// Use it with any builder
await foreach (var user in User.Query(x => x.Username == "alice")
    .WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(user.Id);
}
```

> **NOTE** The builder does not open or close the connection. Pass an already-open `DbConnection`. A closed connection throws when the command executes.

## Using NpgsqlDataSource (recommended for pooling)

`NpgsqlDataSource` manages the connection pool and is the recommended approach for applications that make many short-lived queries. Create it once and reuse it:

```csharp
// Create once (e.g. as a singleton or static field)
await using var dataSource = NpgsqlDataSource.Create(connectionString);

// Lease a connection from the pool for each operation
await using var conn = await dataSource.OpenConnectionAsync();

var user = new User { Username = "alice" };
await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteAsync();
```

`OpenConnectionAsync()` returns a connection that is already open, so no separate `OpenAsync()` call is needed.

## Console application pattern

```csharp
using Npgsql;

var connectionString = Environment.GetEnvironmentVariable("DB_CONN")
    ?? "Host=localhost;Database=dev;Username=postgres;Password=secret";

await using var dataSource = NpgsqlDataSource.Create(connectionString);

// Seed some data
var user = new User { Username = "seed-user" };
await using (var conn = await dataSource.OpenConnectionAsync())
{
    await user.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();
    Console.WriteLine($"Created user {user.Username}");
}

// Query it back
await using (var conn = await dataSource.OpenConnectionAsync())
{
    await foreach (var u in User.Query().WithConnection(conn).ExecuteAsync())
        Console.WriteLine($"  {u.Id}: {u.Username}");
}
```

## Integration test pattern

```csharp
public class UserTests : IAsyncLifetime
{
    private NpgsqlConnection _conn = null!;

    public async Task InitializeAsync()
    {
        _conn = new NpgsqlConnection(
            "Host=localhost;Database=testdb;Username=postgres;Password=1234");
        await _conn.OpenAsync();

        // Bootstrap schema
        await using var cmd = _conn.CreateCommand();
        cmd.CommandText = CreateTableSql;
        await cmd.ExecuteNonQueryAsync();
    }

    public async Task DisposeAsync()
    {
        await _conn.DisposeAsync();
    }

    [Fact]
    public async Task Insert_and_query_roundtrip()
    {
        var user = new User { Username = "test-alice" };
        await user.Insert().WithConnection(_conn).ExcludeAutoFields().ExecuteAsync();

        await foreach (var found in User.Query(x => x.Username == "test-alice")
            .WithConnection(_conn).ExecuteAsync())
        {
            Assert.Equal("test-alice", found.Username);
            return;
        }
        Assert.Fail("User not found");
    }
}
```

## Connection string format

The standard Npgsql keyword-value format:

```plaintext
Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=secret;
Pooling=true;Minimum Pool Size=1;Maximum Pool Size=20;Connection Idle Lifetime=15;
SSL Mode=Require;Include Error Detail=true
```

All standard Npgsql connection string parameters are supported. `Include Error Detail=true` is useful in development because it includes the PostgreSQL server error detail in exception messages.

## Without connection pooling

If your application opens one long-lived connection (CLIs, scripts), disable pooling to avoid holding idle pool connections:

```csharp
var cs = new NpgsqlConnectionStringBuilder
{
    Host     = "localhost",
    Database = "myapp",
    Username = "postgres",
    Password = "secret",
    Pooling  = false,
};

await using var conn = new NpgsqlConnection(cs.ConnectionString);
await conn.OpenAsync();
```

> **WARNING** Disabling pooling in a server application means every request opens a new TCP connection to PostgreSQL, which is expensive. Only disable pooling for scripts and one-shot tools.

---

# Applying migrations

How Socigy.OpenSource.DB tracks and applies schema migrations at runtime using the generated migration manager.

## How migrations are tracked

Every time a migration is generated and applied, the tool creates a tracking table named `_scg_migrations` in your database. This table records every migration that has been successfully applied, identified by the migration's unique `Id` string.

```sql
CREATE SEQUENCE IF NOT EXISTS "_scg_migrations_id_seq" AS BIGINT;
CREATE TABLE "_scg_migrations" (
    "id"          bigint    DEFAULT nextval('_scg_migrations_id_seq'),
    "human_id"    text,
    "applied_at"  timestamp without time zone,
    "is_rollback" boolean   DEFAULT false,
    "executed_by" text,
    CONSTRAINT "PK__scg_migrations" PRIMARY KEY ("id")
);
```

On the next run, the migration manager compares the list of generated migration classes against the rows in this table and applies only the ones that are not yet recorded.

> **NOTE** Each migration is applied **atomically**: its schema change and its `_scg_migrations` row are written inside a single transaction, so a failure or crash mid-migration rolls the whole step back — the database is never left changed-but-unrecorded (which would re-apply the migration) or recorded-but-not-changed.

The "current version" is resolved from the **full** history, honoring `is_rollback`: a rolled-back migration is correctly treated as not applied, so re-running after a rollback re-applies it as expected.

## Applying via the generated extension method

The simplest way to apply migrations is to call the generated `EnsureLatest{DbName}Migration()` extension on `WebApplication` in `Program.cs`. This runs all pending migrations synchronously during startup, before the app begins accepting requests.

```csharp
var app = builder.Build();

// Apply any pending migrations before serving traffic
await app.EnsureLatestAuthDbMigration();

app.MapControllers();
app.Run();
```

This method:
1. Resolves `IMigrationManager` keyed `"AuthDb"` from the DI container
2. Creates the `_scg_migrations` table if it does not exist
3. Walks the migration list baked into the generated `MigrationManager` (every `ILocalMigration` in the project is collected by the source generator at build time)
4. Applies pending migrations in order, recording each in `_scg_migrations`

> **NOTE** `EnsureLatest{DbName}Migration()` is generated only when `generateWebAppExtensions: true` in `socigy.json`.

## Applying via IMigrationManager directly

For more control, for example in a background service, a worker process, or a test fixture, resolve `IMigrationManager` from the DI container directly:

```csharp
// Resolve the keyed service
var migrationManager = app.Services
    .GetRequiredKeyedService<IMigrationManager>("AuthDb");

// EnsureLatestVersion() creates the DB if needed and applies all pending migrations
await migrationManager.EnsureLatestVersion();
```

### In a hosted service

```csharp
public class DatabaseStartupService(
    [FromKeyedServices("AuthDb")] IMigrationManager migrations) : IHostedService
{
    public async Task StartAsync(CancellationToken ct)
    {
        await migrations.EnsureLatestVersion();
    }

    public Task StopAsync(CancellationToken ct) => Task.CompletedTask;
}
```

## Migration order

Migrations are ordered by the `PreviousId` chain recorded in each generated `.g.cs` file. Each migration stores the `Id` of the migration that preceded it, forming a linked list. The manager walks this chain from the earliest unapplied migration to the latest.

> **WARNING** Do not delete or modify generated `.g.cs` migration files after they have been applied to any environment. The `Id` chain drives ordering. Breaking it causes the manager to misidentify which migrations are pending.

## The _scg_migrations table

| Column | Type | Description |
|--------|------|-------------|
| `id` | BIGINT | Auto-increment primary key |
| `human_id` | TEXT | The migration `Id` string (e.g. `"202605011518_Added_procedures_..."`) |
| `applied_at` | TIMESTAMP | UTC timestamp when the migration was applied |
| `is_rollback` | BOOLEAN | `true` if this entry records a DOWN migration |
| `executed_by` | TEXT | OS user and machine name that ran the migration |

You can query this table to audit which migrations have been applied and when:

```sql
SELECT human_id, applied_at, is_rollback, executed_by
FROM _scg_migrations
ORDER BY applied_at DESC;
```

## Rollbacks

Rollback SQL is generated into each migration class as `DownSql`. Rollback is not applied automatically. Invoke it manually when needed:

```csharp
// Access the generated migration class directly
var migration = new M_abc123();
await using var conn = factory.Create();
await conn.OpenAsync();

await using var cmd = conn.CreateCommand();
cmd.CommandText = migration.DownSql;
await cmd.ExecuteNonQueryAsync();

// Record the rollback in the tracking table
cmd.Parameters.Clear();
cmd.CommandText = """
    INSERT INTO "_scg_migrations" ("human_id", "applied_at", "is_rollback", "executed_by")
    VALUES (@humanId, NOW(), true, @executedBy)
    """;
cmd.Parameters.AddWithValue("humanId", migration.Id);
cmd.Parameters.AddWithValue("executedBy", $"{Environment.UserName} - {Environment.MachineName}");
await cmd.ExecuteNonQueryAsync();
```

> **TIP** For production rollback procedures, test the `DownSql` in a staging environment before applying to production. Rollbacks that DROP columns are destructive and cannot recover data.

## CI / CD considerations

In CI and CD pipelines migration generation produces no interactive prompts. The migration name is derived automatically from the schema diff. Apply order does **not** depend on file names or timestamps — each migration records its `PreviousId`, and the manager applies them along that chain (ids are only minute-granularity, so name/timestamp sorting would be ambiguous). A broken or forked chain fails loudly rather than guessing an order.

Run the migration step as part of your deployment pipeline before the application starts:

```bash
dotnet build MyApp.DB/MyApp.DB.csproj -c DB_Migration
```

The generated `.g.cs` files are committed to source control and compiled into the assembly. The source generator collects every `ILocalMigration` at build time and bakes the ordered list into the generated `MigrationManager`, which the runtime then applies.

---

# CLI tool

The bundled migration CLI generates migration classes from your C# models, no global tool install required.

## Overview

`Socigy.OpenSource.DB` ships a CLI tool (`Socigy.OpenSource.DB.Tool`) inside the NuGet package. It loads your compiled assembly, reads every `[Table]` and `[FlagTable]` annotated type through reflection metadata, and generates PostgreSQL DDL.

The tool runs through an MSBuild target that the package registers automatically. There is no `dotnet tool install` step.

## Running the tool

Build your model project in the `DB_Migration` configuration to trigger the migration target:

```bash
dotnet build MyApp.AppDb/MyApp.AppDb.csproj -c DB_Migration
```

The target invokes the tool with the compiled assembly and project directory. The tool analyses the assembly and writes migration files into the project.

## What gets generated

For each annotated type the tool emits DDL covering:

- `CREATE TABLE IF NOT EXISTS` with all columns, types, and `DEFAULT` expressions
- `PRIMARY KEY` constraints
- `FOREIGN KEY` constraints (from `[ForeignKey]`)
- `UNIQUE` constraints (from `[Unique]`)
- `CHECK` constraints (from `[Check]`, `[StringLength]`, `[Min]`, `[Max]`)
- `CREATE SEQUENCE IF NOT EXISTS` for `[AutoIncrement]` columns
- `INSERT ... ON CONFLICT DO UPDATE` seed data for `[Table]`-annotated enums and `[FlaggedEnum]` reference tables

The DDL is wrapped in a generated `.g.cs` migration class that implements `ILocalMigration`, ready to be applied at runtime by the migration manager.

## Output files

The tool writes to `Socigy/Migrations/` in the project directory. Each run produces a timestamped C# file:

```plaintext
Socigy/
├── structure.json
└── Migrations/
    └── 202605011200_Initial_Migration_abc123.g.cs
```

A working snapshot of the model (`Socigy/structure.json`) is kept alongside the migrations so later runs can diff against it.

> **NOTE** The tool only generates migration classes. It never connects to your database. Migrations are applied at runtime by the generated extension or the migration manager, see [Applying migrations](/database/0.3.2/migration/applying).

## Incremental migrations

On the first run the tool emits a full `CREATE` baseline. On every later run it compares the current model against the previous `structure.json` baseline and emits only the statements needed to bring the schema up to date.

Supported drift types:

- New columns (`ALTER TABLE ... ADD COLUMN`)
- Removed columns (`ALTER TABLE ... DROP COLUMN`)
- Type changes (`ALTER TABLE ... ALTER COLUMN ... TYPE`)
- New or removed constraints

## Column renames

Use `[Renamed("old_name")]` to tell the tool a column was renamed rather than dropped and re-added:

```csharp
[Renamed("user_name")]
public string Username { get; set; }
```

Without `[Renamed]`, a renamed column is treated as a drop followed by an add, and the data is lost.

## Supported platforms

The tool ships two builds inside the package:

| Path in package | Target | Runs on |
|----------------|--------|---------|
| `tools/any/` | `net10.0` (framework-dependent) | Any OS with .NET 10 installed |
| `tools/win/` | `net10.0-windows` | Windows only |

The Windows build adds an interactive prompt for the migration name. On Linux and CI the `tools/any/` build is selected and the name is generated automatically.

---

# Configuration

Full reference for socigy.json, the project-level config file that drives migration generation and DI code generation.

## What is socigy.json?

`socigy.json` is a small JSON file placed in the root of your DB class library project. The migration tool reads it on every run to learn which database platform to target, what to name the database, and which helper code to generate alongside the migrations.

If the file does not exist the first time you run `dotnet build -c DB_Migration`, the tool creates it for you with PostgreSQL defaults.

## Location

Place `socigy.json` next to your `.csproj` file. The tool receives the project directory as its `--project-dir` argument and looks for the file there.

```filetree
MyApp.AppDb/
├── MyApp.AppDb.csproj
├── socigy.json              ← lives here
├── Users/
│   └── User.cs
└── Socigy/
    ├── structure.json
    └── Migrations/
        └── 202605011200_Initial_Migration_abc123.g.cs
```

## Full Schema

```json
{
  "database": {
    "platform": "postgresql",
    "databaseName": "AuthDb",
    "migrationNameTemplate": "${Name}",
    "generateDbConnectionFactory": true,
    "generateWebAppExtensions": true
  },
  "shouldShowMessageOnEmptyMigrationGeneration": true
}
```

## Field Reference

### `database.platform`

The target database engine. PostgreSQL is the only supported engine in this release. The value is matched against a fixed set of aliases; anything outside this set means no DI context or migration code is generated.

| Accepted value | Notes |
|----------------|-------|
| `"postgresql"` | Standard (recommended) |
| `"postgres"` | Alias |
| `"postgre"` | Alias |
| `"npgsql"` | Recognized by the migration tool only, but the source generator does not emit the DI/context code for it, so prefer `"postgresql"` |

### `database.databaseName`

The identifier for this database. It defaults to `"UnnamedDb"` when omitted, so set it explicitly. The value is used verbatim as the `{Db}` prefix everywhere:

- The key under `ConnectionStrings` in `appsettings.json`. The generated factory looks up `ConnectionStrings:{databaseName}:Default`.
- The suffix on generated DI methods. `databaseName: "AuthDb"` produces `AddAuthDb()` and `EnsureLatestAuthDbMigration()`.
- The [key](https://learn.microsoft.com/en-us/dotnet/core/extensions/dependency-injection#keyed-services) used to register `IDbConnectionFactory` and `IMigrationManager` in the DI container.
- The name of the PostgreSQL database itself. The factory creates and connects to a database named `{databaseName}`.

```json
"databaseName": "AuthDb"
```

Results in:

```csharp
// Generated extension methods
builder.AddAuthDb();
await app.EnsureLatestAuthDbMigration();

// DI key for manual resolution
var factory = sp.GetRequiredKeyedService<IDbConnectionFactory>("AuthDb");
```

### `database.migrationNameTemplate`

Controls the filename (and embedded class name) of generated migration files on Windows, where the migration name is entered interactively. Two placeholders are available:

| Placeholder | Value |
|-------------|-------|
| `${Name}` | The migration name you type in the prompt |
| `${Timestamp}` | Current UTC timestamp as `yyyyMMddHHmm` |

Every generated file is also prefixed with a UTC timestamp and suffixed with a short content hash automatically, so names stay unique and chronologically sortable regardless of template:

```json
"migrationNameTemplate": "${Name}"
// → 202605011200_Initial_Migration_abc123.g.cs

"migrationNameTemplate": "${Timestamp}_${Name}"
// → 202605011200_202605011200_Initial_Migration_abc123.g.cs
```

The default is `"${Name}"`.

> **NOTE** On Linux and CI runs there is no interactive prompt. The migration name is derived automatically from the schema diff and the template is not applied. See [Applying migrations](/database/0.3.2/migration/applying).

### `database.generateDbConnectionFactory`

Default `true`. When true, the source generator emits:

- An implementation of `IDbConnectionFactory` keyed to `databaseName`
- `AddAuthDb()` extensions on `WebApplicationBuilder`, `IServiceCollection`, and `HostApplicationBuilder`
- Keyed `IDbConnectionFactory` and `IMigrationManager` registrations in DI

Set it to `false` only if you manage connections entirely by hand and do not want the generated DI code.

### `database.generateWebAppExtensions`

Default `true`. When true, the generator also emits:

- `app.EnsureLatestAuthDbMigration()` on `WebApplication` and `IHost`, which applies all pending migrations at startup

This requires `generateDbConnectionFactory: true`.

### `shouldShowMessageOnEmptyMigrationGeneration`

Default `true`. When true, the tool reports that no schema changes were detected (and therefore no migration file was written). Set it to `false` to silence the message in CI pipelines where no-op builds are expected.

## Connection String Format

The generated factory reads the connection string from `appsettings.json` under `ConnectionStrings:{databaseName}`:

```json
{
  "ConnectionStrings": {
    "AuthDb": {
      "Default": "Host=localhost;Port=5432;Username=app;Password=secret;Pooling=true",
      "ReadOnly": "Host=replica.internal;Port=5432;Username=app_ro;Password=secret"
    }
  }
}
```

The sub-key (`"Default"`, `"ReadOnly"`) is the `connectionKey` argument to `IDbConnectionFactory.Create(connectionKey)`. Passing `null` (the default) resolves to `"Default"`.

> **NOTE** Do not include `Database=`. The factory appends it automatically using `databaseName` from `socigy.json`.

> **TIP** Add `appsettings.Development.json` with local connection strings, and keep `appsettings.json` in version control with placeholder values. Never commit real passwords.

---

# Custom migrations

Implement ILocalMigration to run arbitrary C# code alongside generated DDL migrations.

## When to use custom migrations

The generated DDL migration covers structural changes: `CREATE TABLE`, `ALTER TABLE`, `ADD COLUMN`, and so on. Custom migrations let you run SQL that goes beyond DDL, such as:

- Seeding reference data after a new table is created (put the INSERT in `UpSql`)
- Backfilling a new column with computed values (put the UPDATE in `UpSql`)
- Running any SQL the DDL generator cannot express automatically

Custom migrations and generated migrations share the same `ILocalMigration` contract and the same `PreviousId` chain, so they apply in one ordered sequence.

## ILocalMigration interface

Implement `ILocalMigration` (namespace `Socigy.OpenSource.DB.Migrations`) in your DB project:

```csharp
public interface ILocalMigration
{
    string Id { get; }
    string? PreviousId { get; }
    string UpSql { get; }
    string DownSql { get; }
}
```

| Member | Description |
|--------|-------------|
| `Id` | Unique string identifier for this migration (must be globally unique) |
| `PreviousId` | `Id` of the migration that directly precedes this one in the chain, or `null` for the first migration |
| `UpSql` | SQL to apply on upgrade. May be `""` when only data changes are needed |
| `DownSql` | SQL to apply on rollback. Always provide a value (use `""` when irreversible) |

## Example: seed data after creating a table

```csharp
public class SeedRolesMigration : ILocalMigration
{
    public string Id => "seed-roles-2026-05-01";
    public string? PreviousId => "202605011200_Initial_Migration_abc123";

    // Roles table already exists from the generated migration; only seed data here
    public string UpSql => """
        INSERT INTO "roles" ("id", "value", "description") VALUES
            (1, 'Reader', NULL), (2, 'Writer', NULL), (4, 'Moderator', NULL), (8, 'Admin', NULL)
        ON CONFLICT ("id") DO UPDATE SET "value" = EXCLUDED."value";
        """;

    public string DownSql => "DELETE FROM \"roles\" WHERE \"id\" IN (1, 2, 4, 8);";
}
```

## Example: backfill a new column

```csharp
public class BackfillDisplayNameMigration : ILocalMigration
{
    public string Id => "backfill-display-name-2026-05-15";
    public string? PreviousId => "seed-roles-2026-05-01";

    // The ADD COLUMN is done in a generated migration; this only backfills the data
    public string UpSql => "UPDATE \"users\" SET \"display_name\" = \"username\" WHERE \"display_name\" IS NULL;";

    public string DownSql => "UPDATE \"users\" SET \"display_name\" = NULL;";
}
```

## Ordering custom migrations

The migration manager uses the `PreviousId` chain to determine execution order. Set `PreviousId` to the `Id` of the last generated migration (found in `Socigy/structure.json` as the top-level `"id"` field) or the `Id` of the custom migration that should run immediately before it.

> **WARNING** If two migrations (generated or custom) share the same `Id`, only one is applied and the other is silently skipped. Use descriptive, globally unique `Id` values that include a date or timestamp.

## Discovery

The source generator collects every `ILocalMigration` implementation at build time and bakes the ordered list into the generated `MigrationManager`. You do not register them anywhere. Implement the interface in your DB project, rebuild, and the migration is included in the generated manager automatically.

## Combining with generated migrations

Generated migrations (`.g.cs` files) also implement `ILocalMigration`. The manager sees all of them together and applies them in `PreviousId` order regardless of whether they are hand-written or generated.

> **TIP** To anchor a custom migration to the latest generated migration, open the most recent `.g.cs` file in `Socigy/Migrations/` and use its id as your `PreviousId`. Each generated class exposes the value as `public const string _Id` near the top.

---

# Schema generation

How the migration tool derives DDL from annotated C# models, and what each attribute produces.

## Generation pipeline

When the tool runs it follows these steps:

1. **Load the assembly.** It opens the compiled DLL in a metadata-only reflection context (`MetadataLoadContext`).
2. **Find the tables.** It selects every concrete class and enum carrying a `[Table]` or `[FlagTable]` attribute, then reads each column's metadata from its attributes.
3. **Map C# types to SQL types** using the built-in type mapping (see [Column types](/database/0.3.2/defining-models/columns)).
4. **Emit DDL.** It writes `CREATE TABLE`, `CREATE SEQUENCE`, constraint clauses, and seed `INSERT` statements.
5. **Diff against the baseline.** If a previous `structure.json` exists, it computes the delta and emits `ALTER TABLE` statements for the changed parts only.

---

## Destructive and lossy changes

Data-losing statements are flagged in the generated migration so they are obvious in code review, and the CLI prints a summary of them at generation time.

| Marker | Emitted for | Notes |
|--------|-------------|-------|
| `-- [SOCIGY:DESTRUCTIVE]` | `DROP TABLE` (removed table), `DROP COLUMN` (removed column) | The DOWN script restores schema and seed data only — **runtime rows are not recoverable**. `DROP TABLE` uses `CASCADE`, which also drops dependent objects. |
| `-- [SOCIGY:LOSSY]` | `ALTER COLUMN ... TYPE` that is not a known-safe widening | The in-place `USING col::newtype` cast may fail or truncate on existing rows. Safe widenings (e.g. `smallint`→`integer`→`bigint`, `real`→`double precision`, any→`text`) are not flagged. The marker is emitted per direction, so a safe UP may still flag its narrowing DOWN. |

```sql
-- [SOCIGY:DESTRUCTIVE] Drops column "users"."legacy_note"; its data cannot be recovered by the DOWN script.
ALTER TABLE "users" DROP COLUMN "legacy_note";
```

Search a migration for `[SOCIGY:` before applying it to review every data-losing step. These markers are SQL comments and have no runtime effect.

> **TIP** Grep migrations in CI (`grep -R "\[SOCIGY:DESTRUCTIVE\]" Socigy/Migrations`) to require human sign-off on data-losing changes.

---

## Column DDL rules

| Condition | DDL emitted |
|-----------|-------------|
| Property with no attributes | `"col_name" TYPE NOT NULL` (no DEFAULT) |
| `T?` nullable annotation | `... NULL` instead of `NOT NULL` |
| `[Default(DbDefaults.Guid.Random)]` | `DEFAULT gen_random_uuid()` |
| `[Default(DbDefaults.Time.Now)]` | `DEFAULT timezone('utc', now())` |
| `[Default("expr")]` | `DEFAULT expr` |
| `[Default]` (no argument) | No `DEFAULT` clause; `ExcludeAutoFields()` still skips it |
| C# initializer `= "value"` (non-zero/non-empty) | `DEFAULT 'value'` |
| `[AutoIncrement]` | `DEFAULT nextval('table_col_seq')` |

---

## Primary key DDL

```sql
PRIMARY KEY ("id")

-- Composite
PRIMARY KEY ("user_id", "course_id")
```

---

## Foreign key DDL

```sql
FOREIGN KEY ("user_id") REFERENCES "users"("id")

-- With referential action
FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE
```

---

## Constraint DDL

```sql
-- [Unique]
UNIQUE ("email")

-- [Unique(nameof(Col1), nameof(Col2), Name = ...)]
CONSTRAINT "uq_post_slug" UNIQUE ("author_id", "slug")

-- [Check("expr")]
CHECK (LENGTH("email") < 100)

-- [StringLength(200, MinLength = 1)]
CHECK (LENGTH("title") <= 200)
CHECK (LENGTH("title") >= 1)

-- [Min(0)]
CHECK ("quantity" >= 0)
```

---

## Enum reference table DDL

For a `[Table]`-annotated enum the CLI creates a three-column table: `id` (the underlying integer), `value` (the member name), and `description` (from `[Description("...")]` attributes, or `NULL`):

```sql
CREATE TABLE IF NOT EXISTS "roles" (
    "id"          INTEGER NOT NULL,
    "value"       TEXT    NOT NULL,
    "description" TEXT,
    PRIMARY KEY ("id")
);
INSERT INTO "roles" ("id", "value", "description") VALUES
    (1, 'Reader', NULL), (2, 'Writer', NULL), (4, 'Moderator', NULL), (8, 'Admin', NULL)
ON CONFLICT ("id") DO UPDATE SET "value" = EXCLUDED."value", "description" = EXCLUDED."description";
```

---

## FlaggedEnum junction table DDL

For a `[FlaggedEnum]` property on a model:

```sql
CREATE TABLE IF NOT EXISTS "users_roles" (
    "users_id"  UUID    NOT NULL,
    "roles_id"  INTEGER NOT NULL,
    PRIMARY KEY ("users_id", "roles_id"),
    FOREIGN KEY ("users_id") REFERENCES "users"("id") ON DELETE CASCADE,
    FOREIGN KEY ("roles_id") REFERENCES "roles"("id")
);
```

---

## Sequence DDL

For an `[AutoIncrement]` column:

```sql
CREATE SEQUENCE IF NOT EXISTS "table_col_seq" AS INTEGER;
```

The sequence is always created before the table that references it.

---

## Full example

Given this model:

```csharp
[Table("posts")]
[Unique(nameof(AuthorId), nameof(Slug), Name = "uq_post_slug")]
public partial class Post
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid AuthorId { get; set; }

    [StringLength(200, MinLength = 1)]
    public string Title { get; set; }

    [Column("slug"), Unique]
    public string Slug { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime PublishedAt { get; set; }

    public DateTime? DeletedAt { get; set; }
}
```

The tool emits:

```sql
CREATE TABLE IF NOT EXISTS "posts" (
    "id"           UUID      NOT NULL DEFAULT gen_random_uuid(),
    "author_id"    UUID      NOT NULL,
    "title"        VARCHAR(200) NOT NULL,
    "slug"         VARCHAR(200) NOT NULL,
    "published_at" TIMESTAMP NOT NULL DEFAULT timezone('utc', now()),
    "deleted_at"   TIMESTAMP,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("author_id") REFERENCES "users"("id") ON DELETE CASCADE,
    UNIQUE ("slug"),
    CONSTRAINT "uq_post_slug" UNIQUE ("author_id", "slug"),
    CONSTRAINT "chk_posts_title" CHECK (LENGTH("title") <= 200),
    CONSTRAINT "chk_posts_title_1" CHECK (LENGTH("title") >= 1)
);
```

---

# Diagnostics & OpenTelemetry

Every executed SQL statement automatically emits an OpenTelemetry activity (span) with database semantic-convention tags and records a duration histogram and command/error counters. Subscribe with a single AddSource/AddMeter.

## Overview

As of 0.2.0 the library instruments every SQL statement it executes: queries, inserts, updates, deletes, joins, set operations, stored procedures, migrations and DDL. For each command it emits:

- an OpenTelemetry **activity** (span) with database [semantic-convention](https://opentelemetry.io/docs/specs/semconv/database/) tags,
- a **duration histogram** plus command and error **counters**,
- one structured **log** message (see [Logging](/database/0.3.2/observability/logging)).

Tracing and metrics need no configuration beyond subscribing. The library exposes a single source/meter name.

## Enable tracing & metrics

Both the `ActivitySource` and `Meter` are named **`Socigy.OpenSource.DB`**. Register them with OpenTelemetry:

```csharp
builder.Services.AddOpenTelemetry()
    .WithTracing(t => t
        .AddSource("Socigy.OpenSource.DB")
        .AddOtlpExporter())
    .WithMetrics(m => m
        .AddMeter("Socigy.OpenSource.DB")
        .AddOtlpExporter());
```

The constants are also available in code as `SocigyDbInstrumentation.ActivitySourceName` and `SocigyDbInstrumentation.MeterName`.

## Span shape

Each command produces a client span named `"<OPERATION> (postgresql)"` (e.g. `SELECT (postgresql)`) with tags:

| Tag | Value |
|-----|-------|
| `db.system` | `postgresql` |
| `db.operation.name` | `SELECT` / `INSERT` / `UPDATE` / `DELETE` / `PROC` / `MIGRATE` / `DDL` / `SEQUENCE` |
| `db.query.text` | the executed SQL (unless command-text capture is disabled) |
| `db.query.parameters` | parameter names + types (values only when explicitly enabled; see [Logging](/database/0.3.2/observability/logging)) |
| `db.response.affected_rows` | rows affected (writes) |
| `db.response.returned_rows` | rows read (streamed reads) |

For reads the span stays open for the whole enumeration, so its duration reflects time-to-drain and `db.response.returned_rows` is accurate.

When you use [ExecuteTransactionAsync](/database/0.3.2/core-concepts/database-context), a parent `TRANSACTION (postgresql)` span wraps the commands, giving you a clean BEGIN…COMMIT trace.

## Metrics

| Instrument | Type | Unit | Meaning |
|-----------|------|------|---------|
| `db.client.operation.duration` | histogram | `s` | Duration of each command. |
| `socigy.db.commands` | counter | `{command}` | Commands executed. |
| `socigy.db.command.errors` | counter | `{error}` | Commands that threw. |

Each measurement is tagged with `db.operation.name`.

## Errors

When a command throws, its span status is set to `Error`, an exception event is recorded, and `socigy.db.command.errors` is incremented before the exception propagates.

> **NOTE** These types come from `System.Diagnostics.DiagnosticSource`, which is part of the .NET shared framework on net6.0+. The package declares it (and `Microsoft.Extensions.Logging.Abstractions`) as dependencies so non-web consumers also resolve them.

---

# Logging

Emit one structured ILogger message per executed SQL statement, with the SQL text, parameters and duration. Parameter value capture is opt-in and redactable to protect PII and secrets.

## How logging is wired

When you use the [database context](/database/0.3.2/core-concepts/database-context), the `ILogger` is taken from DI automatically, with no per-call wiring. For code paths that execute SQL through the static API (or to set logging globally), configure the ambient diagnostics once at startup:

```csharp
var app = builder.Build();

SocigyDbDiagnostics.Configure(o =>
{
    o.LoggerFactory = app.Services.GetRequiredService<ILoggerFactory>();
    o.LogLevel = LogLevel.Information;       // default: Debug
    o.CaptureCommandText = true;             // default: true
    o.CaptureParameterValues = false;        // default: false (see below)
});
```

Messages are logged to the category **`Socigy.OpenSource.DB.Sql`** and include the operation, duration in milliseconds, row count, SQL text and parameters as structured properties.

## Parameter value capture is opt-in

By default only parameter **names and DB types** are logged, never the values, because values often contain PII or secrets (passwords, tokens, emails). This mirrors EF Core's `EnableSensitiveDataLogging`.

Enable values explicitly, ideally with a redaction hook and length cap:

```csharp
SocigyDbDiagnostics.Configure(o =>
{
    o.LoggerFactory = loggerFactory;
    o.CaptureParameterValues = true;          // turn values on (non-prod / with redaction)
    o.MaxParameterValueLength = 256;          // truncate long values (default 256)
    o.RedactParameter = (name, value) =>
        name.Contains("password", StringComparison.OrdinalIgnoreCase) ? "***" : value?.ToString();
});
```

> **WARNING** Turning on `CaptureParameterValues` writes raw parameter values to your logs and to the `db.query.parameters` span tag. Only do this in trusted environments and prefer a `RedactParameter` hook.

## Options reference

| Option | Default | Meaning |
|--------|---------|---------|
| `LoggerFactory` | `null` | Source of the logger. `null` disables logging (tracing/metrics unaffected). |
| `CaptureCommandText` | `true` | Include the SQL text in spans and logs. |
| `CaptureParameterValues` | `false` | Include parameter **values** (sensitive). |
| `MaxParameterValueLength` | `256` | Per-value truncation length. |
| `LogLevel` | `Debug` | Level of the per-command message. |
| `RedactParameter` | `null` | `(name, value) => masked` hook, applied when values are captured. |

> **NOTE** A per-context override is also supported: the generated context builds a `DbDiagnosticsContext` from DI, which takes precedence over the ambient `SocigyDbDiagnostics` options for work run through that context.

---

# Benchmarks

Socigy.OpenSource.DB delivers Dapper-class speed, hand-written-ADO.NET allocations, and full NativeAOT support, measured against Dapper and EF Core. Reproduce every number yourself.

## Dapper-class. AOT-native. Zero ceremony.

Socigy.OpenSource.DB is a **source-generated** data layer. Your queries become real C# at compile time,
so there's no reflection, no runtime IL emit, and nothing to warm up. The result is performance that
matches hand-written Dapper, with a fully typed API.

> **The headline, in three numbers** (PostgreSQL, .NET 10, 1 000-row read)
> - ⚡ **Matches hand-written Dapper on speed** (statistically tied at every size), with a fully typed API instead of hand-written SQL.
> - 🪶 **~120 KB allocated: on par with hand-written ADO.NET, 1.6× less than Dapper, and up to 8× less than EF Core.**
> - 🚀 **Runs under NativeAOT with no performance penalty**, something **Dapper and EF Core cannot do at all.**

You get the ergonomics of an ORM, micro-ORM-class speed, lower read allocations than Dapper, far less
memory than EF Core, and the only typed query layer that survives a NativeAOT publish.

---

## Why it's fast

| | Socigy | Dapper | EF Core |
|--|:--:|:--:|:--:|
| Row materialization | **Source-generated** | `Reflection.Emit` (runtime IL) | Runtime codegen |
| LINQ→SQL translation | **Compiled once, cached per shape** | n/a (you write SQL) | Cached |
| Boxing on read | **None** (`GetFieldValue<T>`) | None | Varies |
| **NativeAOT** | ✅ **Runs** | ❌ | ❌ |

Column ordinals are resolved **once per result set** and values are read with the allocation-free
`GetFieldValue<T>`, so materialization is as lean as a hand-written reader loop: by ordinal, no boxing.
Predicates are translated to SQL **once per query shape and cached**, so repeat calls skip translation
entirely and just re-bind the parameter values, with no per-call reflection or string building. (That
first-time translation costs ~450 ns; after that the shape is served from cache.)

---

## The numbers

> Environment: BenchmarkDotNet v0.15.8 · Intel Core Ultra 9 275HX · Windows 11 · .NET 10.0.8.
> Each call opens a pooled connection (a realistic per-request pattern), so connection acquisition is part
> of every measurement. Run them yourself (below) for figures on your own hardware.

> **How to read these tables:** the best result per metric in each group is highlighted in **green**
> (lower is better: less time, less memory), Socigy's own rows are tinted, and the bars show relative
> cost within a group. *Within noise* means two results' 99.9% confidence intervals overlap: the gap is
> smaller than normal run-to-run variance, so neither is reliably faster.

### Reads: typed `Query`, vs Dapper & EF Core

```benchmark
| Method | Rows | Mean | Allocated | vs Socigy |
|--------|-----:|-----:|----------:|----------:|
| Socigy (typed Query) | 1 | 351 µs | 4.6 KB | 1.00× |
| Dapper (raw SQL) | 1 | 340 µs | 3.1 KB | 0.97× |
| EF Core (no tracking) | 1 | 424 µs | 56 KB | 1.21× |
| EF Core (tracking) | 1 | 441 µs | 56 KB | 1.26× |
| Socigy (typed Query) | 100 | 375 µs | 16.7 KB | 1.00× |
| Dapper (raw SQL) | 100 | 365 µs | 22.9 KB | 0.97× |
| EF Core (no tracking) | 100 | 474 µs | 87 KB | 1.26× |
| EF Core (tracking) | 100 | 527 µs | 148 KB | 1.41× |
| Socigy (typed Query) | 1000 | 611 µs | 122 KB | 1.00× |
| Dapper (raw SQL) | 1000 | 593 µs | 199 KB | 0.97× |
| EF Core (no tracking) | 1000 | 762 µs | 361 KB | 1.25× |
| EF Core (tracking) | 1000 | 1166 µs | 979 KB | 1.91× |
```

**Socigy matches Dapper on speed and beats it on memory.** Queries are translated once and cached per
shape, so typed `Query` lands within ~3% of hand-written Dapper at every size (their confidence
intervals overlap, a statistical tie). It also **allocates less than Dapper throughout**: at 1 000
rows **122 KB vs Dapper's 199 KB (1.6× less), and 8× less than EF Core tracking's 979 KB**, while
running up to **1.9× faster than EF Core.**

### Reads: `.sql` procedure, vs Dapper & EF Core

A fixed-SQL Socigy procedure (no LINQ translation at all) against Dapper raw SQL and EF Core `FromSqlRaw`.

```benchmark
| Method | Rows | Mean | Allocated | vs Socigy |
|--------|-----:|-----:|----------:|----------:|
| Socigy (.sql procedure) | 1 | 314 µs | 3.5 KB | 1.00× |
| Dapper (raw SQL) | 1 | 304 µs | 3.1 KB | 0.97× |
| EF Core (FromSqlRaw, no tracking) | 1 | 408 µs | 59 KB | 1.30× |
| Socigy (.sql procedure) | 100 | 384 µs | 15.6 KB | 1.00× |
| Dapper (raw SQL) | 100 | 371 µs | 22.9 KB | 0.97× |
| EF Core (FromSqlRaw, no tracking) | 100 | 506 µs | 90 KB | 1.32× |
| Socigy (.sql procedure) | 1000 | 649 µs | 121 KB | 1.00× |
| Dapper (raw SQL) | 1000 | 593 µs | 199 KB | 0.91× |
| EF Core (FromSqlRaw, no tracking) | 1000 | 803 µs | 364 KB | 1.24× |
```

**Within ~3-9% of Dapper, and well ahead of EF Core**, at the same allocation as the cached typed
`Query` (≈121 KB at 1 000 rows) and ~3× lighter than EF's `FromSqlRaw`.

### Reads: defined vs dynamic table

The same filtered read + materialization on `bench_users`, comparing the typed [`Query`](/database/0.3.2/querying/reading/select)
(table fixed at build time) against a [`DynamicTable`](/database/0.3.2/dynamic-tables/declaring) (table name
bound at runtime), with Dapper and EF Core for reference.

```benchmark
| Method | Rows | Mean | Allocated | vs Socigy |
|--------|-----:|-----:|----------:|----------:|
| Socigy (typed Query, defined) | 1 | 359 µs | 4.6 KB | 1.00× |
| Socigy (DynamicTable, runtime name) | 1 | 333 µs | 5.7 KB | 0.93× |
| Dapper (raw SQL, runtime name) | 1 | 325 µs | 3.3 KB | 0.91× |
| EF Core (static model) | 1 | 465 µs | 57 KB | 1.30× |
| Socigy (typed Query, defined) | 100 | 395 µs | 16.7 KB | 1.00× |
| Socigy (DynamicTable, runtime name) | 100 | 390 µs | 17.9 KB | 0.99× |
| Dapper (raw SQL, runtime name) | 100 | 373 µs | 23.2 KB | 0.94× |
| EF Core (static model) | 100 | 506 µs | 87 KB | 1.28× |
| Socigy (typed Query, defined) | 1000 | 603 µs | 122 KB | 1.00× |
| Socigy (DynamicTable, runtime name) | 1000 | 697 µs | 123 KB | 1.16× |
| Dapper (raw SQL, runtime name) | 1000 | 696 µs | 199 KB | 1.16× |
| EF Core (static model) | 1000 | 834 µs | 362 KB | 1.39× |
```

**Dynamic binding costs almost nothing on the hot path.** A `DynamicTable` rebuilds its SQL on each call
(the typed `Query` caches it per shape), yet it shares the exact same materialization, so allocations track
the typed path almost perfectly (123 KB vs 122 KB at 1 000 rows). At small result sets the two are within
noise, with the `DynamicTable` even a touch ahead at a single row (333 µs vs 359 µs). At 1 000 rows the
per-call SQL rebuild and higher run-to-run variance put it about **16% behind** the cached path (697 µs vs
603 µs). Both Socigy paths stay **~1.3–1.4× faster than EF Core** and allocate far less (EF: 57 KB to
362 KB). Dapper, writing raw SQL by hand, ties on the hot path but gives up the typed API. **EF Core can't
actually bind a runtime table name** (it needs a build-time model); it's shown reading its statically-mapped
table purely as a reference point.

### Reads: two-table join, vs Dapper & EF Core

A 1:1 inner join (`bench_users` to `bench_logins`) filtered to `age < Rows`, materializing both sides:
Socigy's typed [`Join`](/database/0.3.2/querying/reading/joins) builder vs Dapper multi-mapping vs EF Core's
no-tracking `Join`.

```benchmark
| Method | Rows | Mean | Allocated | vs Socigy |
|--------|-----:|-----:|----------:|----------:|
| Socigy (typed Join) | 1 | 487 µs | 14.4 KB | 1.00× |
| Dapper (multi-map join) | 1 | 491 µs | 3.9 KB | 1.01× |
| EF Core (no tracking join) | 1 | 593 µs | 61 KB | 1.22× |
| Socigy (typed Join) | 100 | 542 µs | 34.5 KB | 1.00× |
| Dapper (multi-map join) | 100 | 557 µs | 37.5 KB | 1.03× |
| EF Core (no tracking join) | 100 | 694 µs | 101 KB | 1.28× |
| Socigy (typed Join) | 1000 | 994 µs | 218 KB | 1.00× |
| Dapper (multi-map join) | 1000 | 904 µs | 340 KB | 0.91× |
| EF Core (no tracking join) | 1000 | 1179 µs | 460 KB | 1.19× |
```

**Joins materialize both sides through the same fast path as `Query`** (ordinals resolved once, `GetFieldValue<T>`,
no per-row dictionary or boxing). The result: the **lowest allocations at scale** (218 KB at 1 000 rows vs
Dapper's 340 KB and EF's 460 KB) and **~1.2× faster than EF Core**. Dapper is ~9% faster at 1 000 rows
because its mapper here returns a single value, while Socigy builds two typed entities per row; at a single
row Dapper's flat map also allocates less (3.9 KB vs 14.4 KB of fixed join setup). Across 100–1 000 rows
Socigy and Dapper are otherwise within noise.

### Writes: INSERT, vs Dapper & EF Core

```benchmark
| Method | Mean | Allocated | vs Socigy |
|--------|-----:|----------:|----------:|
| Socigy (insert builder) | 2.03 ms | 4.2 KB | 1.00× |
| Dapper (ExecuteAsync) | 2.06 ms | 3.7 KB | 1.01× |
| EF Core (Add + SaveChanges) | 2.74 ms | 66 KB | 1.35× |
```

Single-row writes are dominated by the database commit (fsync), so times are close and noisy. Socigy
and Dapper are **tied within noise.** Socigy's insert builder caches its statement and column plan, so it
allocates just **4.2 KB**, a hair above Dapper's 3.7 KB and **~16× less than EF Core's 66 KB.**

### Writes: bulk INSERT (`InsertMultipleAsync`), vs a per-row loop, Dapper & EF Core

[`InsertMultipleAsync`](/database/0.3.2/querying/writing/insert) batches a whole collection into one multi-row
`INSERT … VALUES (…),(…)` command (auto-chunked under PostgreSQL's 65,535-parameter limit) instead of a
command per row.

```benchmark
| Method | Rows | Mean | Allocated | vs Socigy |
|--------|-----:|-----:|----------:|----------:|
| Socigy (InsertMultipleAsync) | 100 | 2.88 ms | 136 KB | 1.00× |
| Socigy (per-row loop, 1 tx) | 100 | 30.9 ms | 233 KB | 10.81× |
| Dapper (ExecuteAsync over list) | 100 | 141.8 ms | 154 KB | 49.68× |
| EF Core (AddRange + SaveChanges) | 100 | 7.0 ms | 707 KB | 2.45× |
| Socigy (InsertMultipleAsync) | 1000 | 6.67 ms | 1432 KB | 1.00× |
| Socigy (per-row loop, 1 tx) | 1000 | 262 ms | 2314 KB | 39.54× |
| Dapper (ExecuteAsync over list) | 1000 | 1527 ms | 1525 KB | 230.23× |
| EF Core (AddRange + SaveChanges) | 1000 | 23.9 ms | 6295 KB | 3.60× |
```

**Batched multi-row INSERT is in a different league.** Because the whole batch becomes a single command,
`InsertMultipleAsync` is **10–40× faster than a per-row loop** and far ahead of anything that issues one
command per row. Dapper's `ExecuteAsync` over a list does exactly that (N round-trips), landing
**~50–230× slower**. EF Core batches its inserts so it stays within **2–4×**, but allocates **~5× more**
(707 KB / 6.3 MB vs Socigy's 136 KB / 1.4 MB). Socigy is both the **fastest and the leanest** at every size.

### Writes: UPDATE (by primary key), vs Dapper & EF Core

```benchmark
| Method | Mean | Allocated | vs Socigy |
|--------|-----:|----------:|----------:|
| Socigy (update builder) | 1.57 ms | 6.0 KB | 1.00× |
| Dapper (ExecuteAsync) | 1.58 ms | 3.2 KB | 1.01× |
| EF Core (ExecuteUpdate) | 1.66 ms | 58 KB | 1.06× |
```

**Dead-even with Dapper**, both fsync-bound; EF Core trails slightly and allocates ~10× more.

---

## NativeAOT: the one nobody else can match 🚀

Publish your app with NativeAOT and **Socigy just works**, running as fast as it does under the JIT:

| Socigy workload | JIT | NativeAOT | Delta |
|--|--:|--:|--:|
| typed `Query`, 1 000 rows | **616 µs** | 641 µs | within noise |
| typed `Query`, 1 row | 358 µs | **322 µs** | NativeAOT *faster* |
| `.sql` procedure, 1 000 rows | **586 µs** | 643 µs | within noise |
| INSERT, 1 row | 2.15 ms | **1.91 ms** | NativeAOT *faster* |

Allocations are identical across JIT and NativeAOT (≈122 KB at 1 000 rows). The parity is no accident:
materialization is **entirely source-generated**, so there's no runtime codegen to fall back to an interpreter.

And here's the floor: under NativeAOT, Socigy against a **hand-written, ordinal-based ADO.NET reader
loop** (`GetInt32`/`GetGuid` by ordinal, no abstraction). This is as fast as managed data access gets:

| Workload (NativeAOT) | Socigy Mean | ADO.NET Mean | Socigy Alloc | ADO.NET Alloc |
|--|--:|--:|--:|--:|
| typed `Query`, 1 row | 322 µs | **301 µs** | 4.9 KB | **2.6 KB** |
| typed `Query`, 1 000 rows | 641 µs | **600 µs** | 123 KB | **120 KB** |
| `.sql` procedure, 1 000 rows | 643 µs | **591 µs** | 121 KB | **120 KB** |
| INSERT, 1 row | 1.91 ms | **1.87 ms** | 4.7 KB | **3.2 KB** |

Raw ADO.NET is the theoretical floor, fastest on every row (the green cells), yet Socigy stays within
**~9%** of it while giving you a fully typed API, and **matches it on allocations at scale** (≈121 KB vs
120 KB at 1 000 rows; the per-row gap is essentially gone).

No other typed library even gets to play this game. The alternatives **don't run under NativeAOT at all:**

| Library | NativeAOT | Why |
|---------|:---------:|-----|
| **Socigy.OpenSource.DB** | ✅ **Runs** | Source-generated materialization, no runtime codegen |
| Dapper | ❌ Fails | Materializes via `System.Reflection.Emit` (runtime IL) |
| EF Core | ❌ Fails | Query pipeline relies on runtime code generation |

**In an AOT-published service, Socigy is the typed data layer, because it's the only one that boots.**

---

## Run it yourself

Don't take our word for it. Every number above is reproducible in minutes.

### 1. Prerequisites
- **.NET 10 SDK** (`dotnet --version` ≥ 10).
- **A PostgreSQL instance** for every suite except `ParseBenchmarks`.
- **NativeAOT only:** a C/C++ toolchain for the native link step. On Windows, the *Desktop development
  with C++* workload (or Build Tools); on Linux, `clang` + `zlib`. See the
  [NativeAOT prerequisites](https://learn.microsoft.com/dotnet/core/deploying/native-aot/#prerequisites).
  BenchmarkDotNet pulls the `Microsoft.DotNet.ILCompiler` package automatically.

> **NOTE** The NativeAOT job needs **BenchmarkDotNet ≥ 0.15** to recognise the `net10.0` target. Older
> versions fail with `Invalid TFM: 'net10.0'`. The repo pins a compatible version, so a fresh clone works.

### 2. Start a PostgreSQL (example: Docker)
```bash
docker run --rm -d --name socigy-bench -p 5432:5432 \
  -e POSTGRES_PASSWORD=1234 -e POSTGRES_DB=postgres postgres:16
```

### 3. Point the benchmarks at it
The connection string is read from the `BENCH_DB` environment variable; if unset it defaults to
`Host=localhost;Port=5432;Username=postgres;Password=1234;Database=postgres`.

```powershell
# PowerShell
$env:BENCH_DB = "Host=localhost;Port=5432;Username=postgres;Password=1234;Database=postgres"
```
```bash
# bash
export BENCH_DB="Host=localhost;Port=5432;Username=postgres;Password=1234;Database=postgres"
```

The `bench_users` (1000 rows) and `bench_writes` tables are created and seeded automatically on the first
run, with no manual SQL needed.

### 4. Run a suite (Release only)
```bash
# Socigy vs Dapper vs EF Core
dotnet run -c Release --project Benchmarks                                    # everything
dotnet run -c Release --project Benchmarks -- --filter *QueryBenchmarks*
dotnet run -c Release --project Benchmarks -- --filter *ProcedureBenchmarks*
dotnet run -c Release --project Benchmarks -- --filter *InsertBenchmarks*
dotnet run -c Release --project Benchmarks -- --filter *UpdateBenchmarks*
dotnet run -c Release --project Benchmarks -- --filter *ParseBenchmarks*      # no database needed
```

### 5. Run the NativeAOT comparison
Publishes a native build per benchmark and runs the suites under **JIT and NativeAOT** side by side
(Socigy vs raw ADO.NET):
```bash
dotnet run -c Release --project Benchmarks.Aot
```

### 6. Find the results
BenchmarkDotNet prints a summary table and **writes report files** to `BenchmarkResults/results/` next
to the project: GitHub-flavoured Markdown (`*-report-github.md`), JSON (`*-report-full.json`), CSV and
HTML.

---

> **TIP** The benchmark source lives in the `Benchmarks/` and `Benchmarks.Aot/` projects, each with a
> `README.md` documenting every suite.

---

# Aggregates & scalars

Run real SQL COUNT, SUM, AVG, MIN, MAX, and single-value scalar reads. Server-side, parameterized, and fully instrumented, from the query builder or the database context.

Aggregates run as a single server-side scalar query (`SELECT COUNT(*) …`, `SELECT SUM("col") …`), reusing
the same WHERE translation as the rest of the query API. They are available both on the **query builder**
(when you hold a `DbConnection`) and on the **database context** (`I{Table}Set`).

## Query builder

Build a query with an optional `Where(...)`, then call a scalar terminal instead of `ExecuteAsync()`:

```csharp
await using var conn = factory.Create();

long active   = await User.Query(u => u.IsActive).WithConnection(conn).CountAsync();
decimal? rev  = await Order.Query(o => o.Paid).WithConnection(conn).SumAsync<decimal>(o => o.Total);
double?  avg  = await User.Query().WithConnection(conn).AvgAsync<double>(u => u.Age);
DateTime? max = await Login.Query(l => l.UserId == id).WithConnection(conn).MaxAsync<DateTime>(l => l.At);
Guid?    minId= await User.Query().WithConnection(conn).MinAsync<Guid>(u => u.Id);

// A single column's value from the first matching row:
string? email = await User.Query(u => u.Id == id).WithConnection(conn).ScalarAsync<string>(u => u.Email);
```

| Method | SQL | Returns |
|---|---|---|
| `CountAsync()` | `SELECT COUNT(*) …` | `long` |
| `SumAsync<T>(x => x.Col)` | `SELECT SUM("col") …` | `T?` |
| `AvgAsync<T>(x => x.Col)` | `SELECT AVG("col") …` | `T?` |
| `MinAsync<T>(x => x.Col)` | `SELECT MIN("col") …` | `T?` |
| `MaxAsync<T>(x => x.Col)` | `SELECT MAX("col") …` | `T?` |
| `ScalarAsync<T>(x => x.Col)` | `SELECT "col" …` (first row) | `T?` |

- The `Where(...)` predicate is translated and **parameterized** exactly like a normal query.
- `SUM`, `AVG`, `MIN`, and `MAX` return **`null` when no rows match** (the aggregate is SQL `NULL`), so the result type is `T?`.
- `SumAsync`, `AvgAsync`, `MinAsync`, and `MaxAsync` are constrained to value types (`where T : struct`), meaning numeric and date/time columns. For other shapes, use `ScalarAsync<T>` or a [`.sql` procedure](/database/0.3.2/advanced/procedure-mapping).
- The selector must be a **single mapped column** (`x => x.Amount`). Anything else throws `NotSupportedException`. An `[Encrypted]` column also cannot be aggregated, because encryption is non-deterministic.
- Every call is reported through the diagnostics pipeline as a `SELECT` (see [Diagnostics](/database/0.3.2/observability/diagnostics)).

## Database context

The same operations live on each `I{Table}Set`, take an optional predicate, and run inside the unit-of-work
scope (enlisting the ambient transaction automatically):

```csharp
long active = await db.ExecuteAsync(d => d.Users.CountAsync(u => u.IsActive));

await db.ExecuteTransactionAsync(async d =>
{
    decimal? total = await d.Orders.SumAsync<decimal>(o => o.Total, o => o.UserId == userId);
    DateTime? last = await d.Logins.MaxAsync<DateTime>(l => l.At, l => l.UserId == userId);
    // ...
});
```

`I{Table}Set` exposes `CountAsync(predicate?)`, `SumAsync<T>(selector, predicate?)`, `AvgAsync`, `MinAsync`,
`MaxAsync`, and `ScalarAsync<T>(selector, predicate?)`.

> **NOTE** `CountAsync` is a real `SELECT COUNT(*)`. It does **not** stream and count rows client-side.

## See also

- [WHERE expressions](/database/0.3.2/querying/reading/where) for the predicate syntax these reuse
- [Procedure mapping](/database/0.3.2/advanced/procedure-mapping) for arbitrary scalar SQL (`string` MIN/MAX, multi-column aggregates)

---

# Joins

Query across up to four tables with Join / LeftJoin / RightJoin / FullOuterJoin / NaturalJoin / CrossJoin, with typed tuples, OrderBy, projection, and aggregates.

## Overview

The generated `Query()` builder extends into a join against another table. The result is an async stream of
typed tuples: `(T1? , T2?)` for two tables, `(T1?, T2?, T3?)` for three, up to **four** tables. Elements are
**nullable**: an outer-join miss yields `null` for the unmatched side (see [Outer joins](#outer-joins)).

```csharp
await foreach (var (user, login) in User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{user!.Username} last login: {login!.LastSeenAt}");
}
```

Attach the connection or transaction with `.WithConnection()` / `.WithTransaction()`, and materialize with
`ExecuteAsync()` (stream), `ToListAsync()`, or `FirstOrDefaultAsync()`.

## Join types

| Method | SQL |
|--------|-----|
| `.Join<TJoin>(on)` | `INNER JOIN` |
| `.LeftJoin<TJoin>(on)` | `LEFT JOIN` |
| `.RightJoin<TJoin>(on)` | `RIGHT JOIN` |
| `.FullOuterJoin<TJoin>(on)` | `FULL OUTER JOIN` |
| `.NaturalJoin<TJoin>()` | `NATURAL JOIN` (no ON clause) |
| `.CrossJoin<TJoin>()` | `CROSS JOIN` (no ON clause) |

Each `on` is an `Expression<Func<…, bool>>` with one parameter per table joined so far.

## Inner join

```csharp
await foreach (var (user, login) in User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{ ... }
```

Generated SQL. Every column of every table is selected and aliased `a{N}_column`, with each table aliased `a0`, `a1`, and so on:

```sql
SELECT
    a0."id" AS a0_id, a0."username" AS a0_username, ...,
    a1."id" AS a1_id, a1."user_id" AS a1_user_id, ...
FROM "users" a0
INNER JOIN "user_logins" a1 ON a0."id" = a1."user_id"
```

## Outer joins

`LeftJoin` / `RightJoin` / `FullOuterJoin` return **`null`** for the side that didn't match (not a
zeroed-out instance), so you can tell "no match" from a real row of defaults. (Detection uses the table's
primary key: a row whose PK comes back `NULL` is a miss.)

```csharp
await foreach (var (user, profile) in User.Query()
    .LeftJoin<UserProfile>((u, p) => u.Id == p.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine(profile is null ? $"{user!.Username}: (no profile)" : $"{user!.Username}: {profile.Bio}");
}
```

> **NOTE** Because elements are nullable, null-check (or `!`) the side that can be absent. Inner/cross joins
> never produce nulls, but the tuple type is nullable for all join kinds.

## Filtering the driving table

A predicate on `Query()` filters the driving table *before* the join:

```csharp
await foreach (var (user, login) in User.Query(u => u.IsActive)
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{ ... }
```

## Filtering across tables with WHERE

`.Where(...)` takes one parameter per joined table and references any of them:

```csharp
await foreach (var (user, login) in User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .Where((u, l) => u.IsActive && l.LastSeenAt > cutoff)
    .WithConnection(conn)
    .ExecuteAsync())
{ ... }
```

## Three and four tables

Chain `.Join` (and the outer/cross variants) for a third and fourth table; each ON receives a parameter per
table joined so far:

```csharp
var rows = await Order.Query()
    .Join<User>((o, u) => o.UserId == u.Id)
    .Join<Product>((o, u, p) => o.ProductId == p.Id)
    .Where((o, u, p) => p.InStock)
    .WithConnection(conn)
    .ToListAsync();   // List<(Order?, User?, Product?)>
```

**Four tables is the maximum.** For five or more, use a [`.sql` procedure](/database/0.3.2/advanced/procedure-mapping).

## Sorting

`.OrderBy` / `.OrderByDesc` take an `object?[]` of columns from any joined table:

```csharp
var rows = await User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .OrderByDesc((u, l) => new object?[] { l.LastSeenAt })
    .WithConnection(conn)
    .ToListAsync();
```

## Projection to a typed result

`.Select(...)` maps each tuple into your own type instead of returning `(T1?, T2?, …)`:

```csharp
var summaries = await User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .Select((u, l) => new LoginSummary(u!.Username, l!.LastSeenAt))
    .ToListAsync();
```

The projection runs **client-side** (a compiled delegate, AOT-safe) over each materialized tuple. It gives
you typed results but still fetches every column. The projector's parameters are nullable, so outer-join
misses surface as `null` inside `Select` too.

## Aggregates over a join

Aggregate without materializing rows. `CountAsync()` returns `long`; `SumAsync`/`AvgAsync`/`MinAsync`/`MaxAsync`
take a column selector and return `T?` (`null` when no rows match):

```csharp
long n = await User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .Where((u, l) => u.IsActive)
    .WithConnection(conn)
    .CountAsync();

decimal? total = await Order.Query()
    .Join<User>((o, u) => o.UserId == u.Id)
    .WithConnection(conn)
    .SumAsync<decimal>((o, u) => o.Total);
```

## Limit, Offset, and transactions

`.Limit()` / `.Offset()` are available on every join builder, and `.WithTransaction(tx)` enlists the join in a
transaction instead of `.WithConnection()`.

## Limitations

- **Up to four tables.** For more, use a [`.sql` procedure](/database/0.3.2/advanced/procedure-mapping).
- **Projection is client-side.** `.Select(...)` gives typed results but doesn't reduce the columns fetched
  (all columns of all tables are still selected).

---

# SELECT / Query

Query rows from a table with the generated Query() builder. Fluent, parameterized, and streamed row-by-row as an IAsyncEnumerable.

## Overview

Every generated entity class exposes a static `Query()` method that returns a fluent `TableQueryBuilder`. You attach optional modifiers, provide a connection or transaction, then execute. Results arrive as an `IAsyncEnumerable<T>` and stream row-by-row, so the full result set is never buffered into memory unless you ask for it.

## Fetching All Rows

Call `Query()` with no arguments to fetch every row in the table.

```csharp
// fetch all users
await foreach (var user in User.Query()
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine(user.Name);
}
```

## Filtered Query

Pass a lambda predicate to `Query()` to apply a `WHERE` clause immediately.

```csharp
// fetch only active users with a score of at least 10
await foreach (var user in User.Query(x => x.Status == "active" && x.Score >= 10)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine(user.Name);
}
```

The predicate is translated to parameterized SQL at build time. Values are captured as SQL parameters, never string-interpolated into the query.

## Builder Method Reference

All modifiers are optional and can appear in any order before the execution call.

| Method | SQL equivalent | Notes |
|---|---|---|
| `.Where(x => ...)` | `WHERE ...` | Sets the filter predicate, replacing any predicate passed to `Query()` or a previous `.Where()` |
| `.OrderBy(x => new object?[] { x.Col1, x.Col2 })` | `ORDER BY col1, col2 ASC` | Multi-column ascending sort |
| `.OrderByDesc(x => new object?[] { x.Col1 })` | `ORDER BY col1 DESC` | Multi-column descending sort |
| `.Limit(n)` | `LIMIT n` | Maximum rows returned (alias `.Top(n)`) |
| `.Offset(n)` | `OFFSET n` | Rows to skip, paired with `Limit` for pagination |
| `.Select(x => new object?[] { x.Id, x.Name })` | `SELECT id, name` | Project specific columns only |
| `.WithConnection(conn)` | (none) | Provide an open `DbConnection` (required if no transaction) |
| `.WithTransaction(tx)` | (none) | Provide an active `DbTransaction` (required if no connection) |

> **NOTE** `.WithConnection(conn)` and `.WithTransaction(tx)` are mutually exclusive. Provide exactly one before calling `ExecuteAsync()`.

## Column Projection

Use `.Select()` to fetch a subset of columns and cut data transfer.

```csharp
// select only Id and Name
await foreach (var user in User.Query()
    .Select(x => new object?[] { x.Id, x.Name })
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{user.Id}: {user.Name}");
}
```

Columns not listed in the projection are left at their default CLR value in the returned instance.

## Executing a Query

### Streaming with ExecuteAsync

`ExecuteAsync()` returns `IAsyncEnumerable<T>`. Rows are yielded one at a time as they arrive from the database driver, which is ideal for large result sets.

```csharp
// stream rows
await foreach (var user in User.Query(x => x.Score > 100)
    .WithConnection(conn)
    .ExecuteAsync())
{
    await ProcessAsync(user);
}
```

### Materializing with ToListAsync

`ToListAsync()` is not a builder method. It is a [System.Linq.Async](https://www.nuget.org/packages/System.Linq.Async) extension over the `IAsyncEnumerable<T>` that `ExecuteAsync()` returns. It buffers every row into a `List<T>`. Use it when you need random access or want to hand the result to code that expects a list.

```csharp
// materialize to a List<T>
using System.Linq; // System.Linq.Async

List<User> topUsers = await User.Query(x => x.Score > 100)
    .OrderByDesc(x => new object?[] { x.Score })
    .Limit(10)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

> **TIP** `ToListAsync()` and `FirstOrDefaultAsync()` come from the System.Linq.Async package and apply to the stream, not the builder. Prefer `await foreach` over `ExecuteAsync()` for large tables, and materialize only when you truly need all rows in memory.

## Where replaces the predicate

`.Where()` sets the builder's single WHERE predicate. Calling it after `Query(predicate)` (or calling it more than once) replaces the predicate rather than AND-ing them. To combine conditions, express them in a single lambda with `&&`.

```csharp
// combine conditions in one predicate with &&
List<User> results = await User.Query(x => x.Status == "active" && x.Score > 50)
    .OrderBy(x => new object?[] { x.Name })
    .Limit(25)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

## Full Example

```csharp
// paginated, sorted, projected, filtered query
List<User> page2 = await User.Query(x => x.Status == "active" && x.Score >= 10)
    .OrderByDesc(x => new object?[] { x.Score })
    .Limit(20)
    .Offset(20)
    .Select(x => new object?[] { x.Id, x.Name, x.Score })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

## See Also

- [WHERE Operators](/database/0.3.2/querying/reading/where) for the full list of supported predicate operators
- [Sorting and Pagination](/database/0.3.2/querying/reading/sorting-pagination) for ordering and paging patterns
- [Aggregates and Scalars](/database/0.3.2/querying/reading/aggregates) for COUNT, SUM, AVG, MIN, MAX

---

# Set operations

Combine two queries with UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, or EXCEPT ALL.

## Overview

Set operations combine the result sets of two compatible `Query()` builders. Both queries must return the same model type `T`.

## Syntax

Call `.Union()`, `.UnionAll()`, `.Intersect()`, `.IntersectAll()`, `.Except()`, or `.ExceptAll()` on a query builder, passing a second `TableQueryBuilder` as the right operand:

```csharp
var activeQuery  = User.Query(x => x.IsActive == true);
var premiumQuery = User.Query(x => x.IsPremium == true);

await foreach (var user in activeQuery
    .Union(premiumQuery)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine(user.Username);
}
```

## Available operations

| Method | SQL |
|--------|-----|
| `.Union(other)` | `UNION` (deduplicated) |
| `.UnionAll(other)` | `UNION ALL` (includes duplicates) |
| `.Intersect(other)` | `INTERSECT` (deduplicated) |
| `.IntersectAll(other)` | `INTERSECT ALL` (includes duplicates) |
| `.Except(other)` | `EXCEPT` (deduplicated) |
| `.ExceptAll(other)` | `EXCEPT ALL` (includes duplicates) |

## UNION ALL example

```csharp
var recentQuery = Order.Query(x => x.CreatedAt > DateTime.UtcNow.AddDays(-7));
var pendingQuery = Order.Query(x => x.Status == "pending");

await foreach (var order in recentQuery
    .UnionAll(pendingQuery)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{order.Id}: {order.Status}");
}
```

## Parameter handling

Both sub-queries share a single `DbCommand`. Parameters from the left and right queries are offset automatically, so there are no conflicts. You can safely reuse the same parameter values in both queries.

## Compiled query interface

Every generated `Query()` builder implements `ICompiledQuery` internally, which is how its compiled SQL and parameters are merged into the combined command. Any builder can serve as either the left or right operand:

```csharp
// either query can be on either side
await foreach (var row in leftQuery.Intersect(rightQuery).WithConnection(conn).ExecuteAsync())
{
    // ...
}
```

## Limitations

- Both operands must return the same model type `T`.
- Set operations do not currently support chaining a `JOIN` builder on either operand.
- The set-operation builder supports `Limit()` and `Offset()` on the combined result. `ORDER BY` is not available. Sort in C# after collecting results if ordering is required.

---

# Sorting and Pagination

Sort results and page through large datasets with OrderBy, OrderByDesc, Limit, and Offset.

## Overview

The query builder exposes `.OrderBy()`, `.OrderByDesc()`, `.Limit()`, and `.Offset()` for sort order and result paging. `.OrderBy()` and `.OrderByDesc()` are available on regular queries only. `.Limit()` and `.Offset()` work on regular queries and JOIN queries. Set-operation builders support `.Limit()` and `.Offset()` on the combined result but not sorting.

## Sorting

### Ascending Order

Pass an array of columns to `.OrderBy()`. PostgreSQL sorts every listed column in ascending order.

```csharp
// sort by Name ascending, then by CreatedAt ascending
List<User> users = await User.Query()
    .OrderBy(x => new object?[] { x.Name, x.CreatedAt })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "name" ASC, "created_at" ASC
```

### Descending Order

```csharp
// sort by Score descending
List<User> topScorers = await User.Query()
    .OrderByDesc(x => new object?[] { x.Score })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "score" DESC
```

### Combining Ascending and Descending

The builder holds a single ORDER BY clause: `.OrderBy()` and `.OrderByDesc()` each replace it, and the call's direction applies to every column in the array. Calling them more than once does not append; the last call wins. To sort columns in mixed directions, pass one array to a single `.OrderBy()` call and wrap individual columns with the `OrderBy.Desc(...)` / `OrderBy.Asc(...)` helpers (from `Socigy.OpenSource.DB.Core.SyntaxHelper.DB`).

```csharp
using static Socigy.OpenSource.DB.Core.SyntaxHelper.DB;

// sort by Score DESC, then by Name ASC
List<User> results = await User.Query()
    .OrderBy(x => new object?[] { OrderBy.Desc(x.Score), x.Name })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "score" DESC, "name"
```

## Pagination with Limit and Offset

### Limit

`.Limit(n)` emits a SQL `LIMIT` clause and caps the number of rows returned. `.Top(n)` is an alias.

```csharp
// fetch at most 20 rows
List<User> page = await User.Query()
    .Limit(20)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

### Offset

`.Offset(n)` emits a SQL `OFFSET` clause and skips the first `n` rows. It is almost always used with `.Limit()`.

```csharp
// fetch page 3 (zero-based pages of 20)
int pageSize = 20;
int pageNumber = 2; // 0-indexed, page 3

List<User> page3 = await User.Query()
    .OrderBy(x => new object?[] { x.CreatedAt })
    .Limit(pageSize)
    .Offset(pageNumber * pageSize)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "created_at" ASC LIMIT 20 OFFSET 40
```

> **WARNING** Always pair `Offset` with `OrderBy`. Without a deterministic sort order the database may return rows in any sequence, making page boundaries unpredictable and risking rows that appear on multiple pages or none at all.

## Cursor-Based Pagination

Offset pagination slows down on large tables because PostgreSQL scans and skips the first `n` rows on every request. Cursor-based pagination avoids this by filtering on the last-seen key value.

```csharp
// cursor-based next page using the last seen Id
Guid lastSeenId = previousPage.Last().Id;

List<User> nextPage = await User.Query(x => x.Id > lastSeenId)
    .OrderBy(x => new object?[] { x.Id })
    .Limit(20)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

> **TIP** Cursor-based pagination needs a stable, sortable, unique column (an auto-incrementing integer or a time-ordered UUID). It cannot jump to arbitrary pages, but it scales to billions of rows where `OFFSET` would time out.

## Full Pagination Example

```csharp
using static Socigy.OpenSource.DB.Core.SyntaxHelper.DB;

// typical paged API endpoint
async Task<List<User>> GetPageAsync(DbConnection conn, int page, int pageSize)
{
    return await User.Query(x => x.DeletedAt == null)
        .OrderBy(x => new object?[] { OrderBy.Desc(x.CreatedAt), x.Id })
        .Limit(pageSize)
        .Offset(page * pageSize)
        .WithConnection(conn)
        .ExecuteAsync()
        .ToListAsync();
}
```

## Pagination on Set Operations

`.Limit()` and `.Offset()` are available on set-operation builders (Union, Intersect, Except, and so on) and apply to the combined result. Sorting on the combined result is not supported. Apply `OrderBy` to each sub-query before combining, or sort in C# after materializing.

```csharp
// paginate a UNION result
var lhs = User.Query(x => x.Active == true);
var rhs = User.Query(x => x.IsPremium == true);

List<User> combined = await lhs.Union(rhs)
    .Limit(20)
    .Offset(0)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
```

## See Also

- [SELECT / Query](/database/0.3.2/querying/reading/select) for the full query builder reference
- [WHERE Operators](/database/0.3.2/querying/reading/where) for filtering rows before sorting
- [Set Operations](/database/0.3.2/querying/reading/set-operations) for Union, Intersect, and Except with pagination

---

# WHERE Operators

Complete reference for the predicate operators the WHERE clause builder translates to parameterized SQL.

## Overview

Predicates are C# lambda expressions translated to parameterized SQL at build time. Supply a predicate directly to `Query()` or pass it to `.Where()`. The builder holds a single predicate, so `.Where()` replaces any predicate set earlier; combine conditions inside one lambda with `&&` / `||`. Every value is captured as a SQL parameter, never string-interpolated into the query text.

## Operator Reference

### Comparison Operators

| C# expression | Generated SQL |
|---|---|
| `x.Name == "value"` | `"name" = $1` |
| `x.Name != "value"` | `"name" != $1` |
| `x.Count > 5` | `"count" > $1` |
| `x.Count >= 5` | `"count" >= $1` |
| `x.Count < 5` | `"count" < $1` |
| `x.Count <= 5` | `"count" <= $1` |

```csharp
// comparison examples
User.Query(x => x.Score >= 100)
User.Query(x => x.Age < 18)
User.Query(x => x.Name != "system")
```

### NULL Checks

| C# expression | Generated SQL |
|---|---|
| `x.Name == null` | `"name" IS NULL` |
| `x.Name != null` | `"name" IS NOT NULL` |

```csharp
// null checks
User.Query(x => x.DeletedAt == null)       // active (not soft-deleted)
User.Query(x => x.VerifiedAt != null)      // email verified
```

### Logical Operators

| C# expression | Generated SQL |
|---|---|
| `cond1 && cond2` | `... AND ...` |
| `cond1 \|\| cond2` | `... OR ...` |

Logical operators compose freely and nest to any depth.

```csharp
// AND
User.Query(x => x.Status == "active" && x.Score >= 10)

// OR
User.Query(x => x.Role == "admin" || x.Role == "moderator")

// nested AND / OR
User.Query(x => (x.Role == "admin" || x.Role == "moderator") && x.DeletedAt == null)
```

### String Methods

String matching translates to SQL `LIKE` patterns. The method names map directly to their wildcard positions.

| C# expression | Generated SQL |
|---|---|
| `x.Name.Contains("sub")` | `"name" LIKE '%sub%'` |
| `x.Name.StartsWith("pre")` | `"name" LIKE 'pre%'` |
| `x.Name.EndsWith("suf")` | `"name" LIKE '%suf'` |

```csharp
// string matching
User.Query(x => x.Email.EndsWith("@example.com"))
User.Query(x => x.Username.StartsWith("admin_"))
User.Query(x => x.Bio.Contains("developer"))
```

> **NOTE** `LIKE` is case-sensitive in PostgreSQL for non-`citext` columns. For case-insensitive matching, store data normalized, use a `citext` column, or use the `ToLower()` form below.

### HasFlag for FlaggedEnum Properties

`HasFlag` is supported on properties annotated for junction-table flag storage. It translates to an `EXISTS` subquery against the junction table.

| C# expression | Generated SQL |
|---|---|
| `x.Roles.HasFlag(Role.Admin)` | `EXISTS (SELECT 1 FROM junction WHERE owner_id = x.id AND role_id = 8)` |

```csharp
// check for a specific flag/role
User.Query(x => x.Roles.HasFlag(Role.Admin))
```

The junction table name and ID values come from your schema attributes. Multiple `HasFlag` calls combine with `&&` or `||`.

## Closed-Over Variables

Any local variable referenced inside a predicate lambda is captured as a SQL parameter, never embedded as a literal in the query string.

```csharp
// closed-over variable
int minScore = 5;
string status = "active";

List<User> users = await User.Query(x => x.Score > minScore && x.Status == status)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: WHERE "score" > $1 AND "status" = $2
// Parameters: $1 = 5, $2 = 'active'
```

This applies equally to properties on objects, results of method calls, and any expression that does not reference the lambda parameter itself.

```csharp
// object property captured as parameter
var filter = new FilterOptions { MinScore = 50 };
User.Query(x => x.Score >= filter.MinScore)
```

## Combining conditions

The builder keeps a single WHERE predicate: a predicate passed to `Query()` and any later `.Where()` call do not stack; the last one wins. Combine conditions inside one lambda with `&&` / `||`.

```csharp
// combine in a single predicate
List<User> results = await User.Query(x => x.Status == "active" && x.Score > 50 && x.DeletedAt == null)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: WHERE "status" = $1 AND "score" > $2 AND "deleted_at" IS NULL
```

## Extended Operators

The translator also handles these patterns.

| Pattern | Translates to |
|---------|---------------|
| `x => x.IsActive` / `x => !x.IsActive` | boolean column predicate |
| `x.Age.HasValue` / `!x.Age.HasValue` | `IS NOT NULL` / `IS NULL` |
| `x.Age.Value > 5` | column comparison |
| `x.Name.ToLower().Contains("ab")` | `"name" ILIKE @p ESCAPE '\'` (case-insensitive) |
| `x.Name.Equals("ab")` | `"name" = @p` |
| `string.IsNullOrEmpty(x.Name)` | `("name" IS NULL OR "name" = '')` |
| `x.Qty + 1 > 10` | arithmetic `+ - * / %` |
| `(x.Age ?? 0) > 5` | `COALESCE("age", @p) > @p` |

**LIKE escaping:** `Contains`, `StartsWith`, and `EndsWith` escape `%`, `_`, and `\` and append `ESCAPE '\'`. A value such as `"50%"` matches literally rather than as a wildcard.

## Limitations

The translator covers the operators above. These SQL features have no direct C# expression equivalent in the current version:

- **BETWEEN**: use `x.Score >= lo && x.Score <= hi`.
- **IN list**: use chained `||` comparisons (`x.Status == "a" || x.Status == "b"`), or a raw SQL procedure for large lists.
- **Sub-selects in WHERE**: use a JOIN or procedure mapping for correlated subqueries.

> **WARNING** Unsupported expressions (nested navigation `x.A.B`, subqueries, aggregates, or unknown methods) throw `NotSupportedException` at translation time rather than silently producing invalid SQL.

> **TIP** For predicates the operators above cannot express, use [Procedure Mapping](/database/0.3.2/advanced/procedure-mapping) with a raw `.sql` file.

## See Also

- [SELECT / Query](/database/0.3.2/querying/reading/select) for using `Query()` and chaining modifiers
- [Sorting and Pagination](/database/0.3.2/querying/reading/sorting-pagination) for ordering and paging patterns

---

# DELETE

Delete rows with the instance builder (by primary key) or the static DeleteNonInstance() builder (filtered). Always parameterized, never string-interpolated.

## Overview

The library offers two ways to delete rows: an instance builder that deletes by primary key, and a static non-instance builder for filtered deletes. Both produce parameterized SQL, so no values are string-interpolated.

## Delete by Primary Key (Instance Method)

Call `.Delete()` on a loaded instance, attach a connection or transaction, and execute. The `WHERE` clause is built automatically from the PK column(s) on the instance.

```csharp
// delete the row matching user.Id
await user.Delete()
    .WithConnection(conn)
    .ExecuteAsync();
// SQL: DELETE FROM "users" WHERE "id" = $1
```

The instance is not nulled out or modified after the delete. The database row is gone; the C# object remains in memory as a plain object.

## Filtered Delete (Non-Instance)

When you do not have a loaded instance, use the static `DeleteNonInstance()` builder. Supply a `.Where()` predicate (always do, see the warning below) and it returns the number of rows deleted.

```csharp
// delete all rows matching a filter
int deleted = await User.DeleteNonInstance()
    .WithConnection(conn)
    .Where(x => x.CreatedAt < cutoff)
    .ExecuteAsync();

Console.WriteLine($"Removed {deleted} stale users.");
```

The full [WHERE operator set](/database/0.3.2/querying/reading/where) is available, including logical operators, string methods, and null checks.

> **WARNING** `DeleteNonInstance()` with no `Where` produces an unfiltered `DELETE FROM "table"` that removes **every** row and is not guarded. Always supply a `.Where()` predicate. (The only built-in guard is on an *instance* delete: if the entity has no primary-key column, `ExecuteAsync()` throws rather than deleting the whole table.)

## Return Values

| Builder | Return type | Value |
|---|---|---|
| `instance.Delete().ExecuteAsync()` | `Task<int>` | Number of rows deleted (usually `1`) |
| `User.DeleteNonInstance()...ExecuteAsync()` | `Task<int>` | Number of rows deleted |

## Composite Primary Keys

When an entity has multiple `[PrimaryKey]` columns, all of them are included in the `WHERE` clause of an instance delete automatically.

```csharp
// enrollment has [PrimaryKey] on both UserId and CourseId
await enrollment.Delete()
    .WithConnection(conn)
    .ExecuteAsync();
// SQL: DELETE FROM "user_course" WHERE "user_id" = $1 AND "course_id" = $2
```

## Cascade Deletes via Foreign Keys

If a related table defines a foreign key with `ON DELETE CASCADE`, PostgreSQL removes the dependent rows automatically when the parent row is deleted. No extra code is needed.

```csharp
// deleting a user cascades to all user_course rows (FK = ON DELETE CASCADE)
await user.Delete()
    .WithConnection(conn)
    .ExecuteAsync();
// PostgreSQL also deletes rows in "user_course" where user_id = user.Id
```

> **WARNING** Cascade behavior is defined in your schema DDL or schema generator attributes, not in the builder. Verify your FK constraints before relying on cascades in production.

## Bulk Delete Pattern

For deleting a set of known instances, share a single transaction to keep the operation atomic and cut round-trip overhead.

```csharp
// bulk delete in a transaction
await using var tx = await conn.BeginTransactionAsync();

foreach (var user in usersToRemove)
{
    await user.Delete()
        .WithTransaction(tx)
        .ExecuteAsync();
}

await tx.CommitAsync();
```

For set-based bulk deletes (every row matching a condition rather than a list of instances), prefer `DeleteNonInstance()` with a `Where()` predicate. It issues a single SQL statement instead of N statements.

```csharp
// single-statement bulk delete
int deleted = await User.DeleteNonInstance()
    .WithConnection(conn)
    .Where(x => x.Status == "inactive" && x.LastLoginAt < cutoff)
    .ExecuteAsync();
```

## Using With Transactions

Both delete approaches accept `.WithTransaction(tx)` in place of `.WithConnection(conn)`.

```csharp
// coordinated delete inside a transaction
await using var tx = await conn.BeginTransactionAsync();

await post.Delete().WithTransaction(tx).ExecuteAsync();
await User.DeleteNonInstance()
    .WithTransaction(tx)
    .Where(x => x.Id == post.AuthorId && x.PostCount == 0)
    .ExecuteAsync();

await tx.CommitAsync();
```

## See Also

- [INSERT](/database/0.3.2/querying/writing/insert) for adding new rows
- [UPDATE](/database/0.3.2/querying/writing/update) for modifying existing rows
- [WHERE Operators](/database/0.3.2/querying/reading/where) for the full predicate operator reference

---

# INSERT

Insert rows with the generated Insert() builder. Choose your fields, propagate DB-generated values back, and batch thousands of rows in a few round-trips.

## Overview

Every generated entity instance exposes an `.Insert()` method that returns a `PostgresqlInsertCommandBuilder<T>`. You choose which fields to include, optionally request that DB-generated values be propagated back to the instance, then execute. `ExecuteAsync()` returns `Task<bool>`.

## Basic Insert

The simplest insert includes all columns, including any `[Default]` columns (those whose values the database generates on insert, such as `id`, `created_at`, or sequence numbers).

```csharp
// insert a new user, including all fields
var user = new User { Id = Guid.NewGuid(), Name = "Alice", Status = "active", Score = 0 };

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();
```

## Field Selection Strategies

The builder supports five mutually exclusive strategies for choosing which columns appear in the `INSERT`. Pick exactly one per insert.

### ExcludeAutoFields: Skip All [Default] Columns

Omits every column marked `[Default]` (columns whose value the database supplies automatically). This is the most common strategy, because it lets PostgreSQL handle `id`, `created_at`, sequences, and so on.

```csharp
// let the DB generate Id and CreatedAt
var user = new User { Name = "Bob", Status = "active", Score = 0 };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteAsync();
```

### ExcludeAutoFields With Exceptions

Skips all `[Default]` columns **except** those you list explicitly. Useful when you want to override one default (supply your own `Id`, say) while still letting the database control the others.

```csharp
// supply Id manually, let DB handle CreatedAt
var user = new User { Id = Guid.NewGuid(), Name = "Carol", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields(include => new object?[] { include.Id })
    .ExecuteAsync();
```

### WithAllFields: Include Everything

Forces all columns (including `[Default]` ones) into the `INSERT`. Use when you want to supply every value yourself.

```csharp
// insert with all fields, including Id and timestamps
var user = new User
{
    Id = Guid.NewGuid(),
    Name = "Dave",
    Status = "active",
    CreatedAt = DateTimeOffset.UtcNow
};

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();
```

> **WARNING** `WithAllFields()` and `ExcludeAutoFields()` cannot appear in the same chain. Combining them is a programming error and throws at runtime.

### WithFields: Include Only Listed Columns

Inserts only the columns you specify. Every other column is omitted from the statement.

```csharp
// insert only Name and Status
var user = new User { Name = "Eve", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithFields(x => new object?[] { x.Name, x.Status })
    .ExecuteAsync();
```

### ExcludeFields: Exclude Listed Columns

Includes all columns except those you specify. The inverse of `WithFields`.

```csharp
// insert everything except AuditLog
bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeFields(x => new object?[] { x.AuditLog })
    .ExecuteAsync();
```

## Value Propagation (RETURNING *)

`.WithValuePropagation()` appends `RETURNING *` to the statement. The database returns the complete row after insert, generated values filled in, and the library writes those values back onto the instance. This is the cleanest way to obtain `Id`, `CreatedAt`, sequence numbers, and any other DB-generated columns.

```csharp
// write generated values back to the instance
var user = new User { Name = "Frank", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .WithValuePropagation()
    .ExecuteAsync();

// user.Id and user.CreatedAt are now populated from the database
Console.WriteLine(user.Id);
Console.WriteLine(user.CreatedAt);
```

## Returning a Specific Column Value

When you only need one DB-generated value (most often the new primary key), use `ExecuteReturningAsync<T>()` instead of `ExecuteAsync()`. It returns `Task<T?>` and **requires the database column name** as a string argument.

```csharp
// return the generated Id
var user = new User { Name = "Grace", Status = "active" };

Guid? newId = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteReturningAsync<Guid>("id");

if (newId is not null)
    Console.WriteLine($"Created user {newId}");
```

The string argument is the column name in the database (snake_case), not the C# property name.

## Static Shorthand

`User.InsertAsync(instance, conn)` is a static convenience that inserts the row over a plain connection and returns `Task<bool>`. It is the quickest path when you do not need field selection or value propagation.

```csharp
// static shorthand
bool ok = await User.InsertAsync(user, conn);
```

> **NOTE** The static shorthand exposes no field selection or value propagation. If you need `ExcludeAutoFields`, `WithValuePropagation`, or any other option, use the instance builder.

## Using With Transactions

Pass a `DbTransaction` instead of a `DbConnection` when the insert is part of a larger unit of work.

```csharp
// insert inside a transaction
await using var tx = await conn.BeginTransactionAsync();

bool ok = await user.Insert()
    .WithTransaction(tx)
    .ExcludeAutoFields()
    .WithValuePropagation()
    .ExecuteAsync();

await tx.CommitAsync();
```

## Bulk Insert with InsertMultipleAsync

To insert a whole collection, use the static `InsertMultipleAsync` instead of a command per row. It builds **batched multi-row `INSERT ... VALUES (…),(…),…` commands**, one command per chunk, which is dramatically faster than looping. It returns `Task<int>`, the total number of rows inserted.

```csharp
// insert many rows in one (or a few) round-trips
var users = new List<User>
{
    new() { Name = "Alice", Status = "active" },
    new() { Name = "Bob",   Status = "active" },
    // ... thousands more ...
};

int inserted = await User.InsertMultipleAsync(users, conn);
```

The signature is `InsertMultipleAsync(rows, conn, transaction = null, includeAutoFields = false, cancellationToken = default)`.

- Returns the **total number of rows inserted**.
- By default (`includeAutoFields = false`), **`[Default]` and sequence columns are skipped** so the database generates them, the same as `ExcludeAutoFields()`. Pass `includeAutoFields: true` to send those columns yourself.
- Automatically **chunks** so each command stays under PostgreSQL's 65,535-parameter limit (`maxRowsPerBatch ≈ 65535 / columnCount`). A 10,000-row insert of a 6-column table becomes a handful of commands rather than 10,000.
- `[Encrypted]` and JSON columns are handled (bound as `bytea` / `jsonb`).
- Each command is traced through the diagnostics pipeline as a single `INSERT`.

Pass a transaction to make the whole batch atomic with other work:

```csharp
await using var tx = await conn.BeginTransactionAsync();
await User.InsertMultipleAsync(users, conn, tx);
await tx.CommitAsync();
```

> **NOTE** `InsertMultipleAsync` uses the default insert plan (no per-row field selection or value propagation). If you need `WithValuePropagation` or custom field selection, use the per-instance `.Insert()` builder.

> **TIP** For extreme volumes (hundreds of thousands of rows) PostgreSQL's binary `COPY` protocol via `NpgsqlBinaryImporter` is still the throughput champion. `InsertMultipleAsync` is the sweet spot for everyday batches up to tens of thousands.

## See Also

- [UPDATE](/database/0.3.2/querying/writing/update) for updating existing rows
- [DELETE](/database/0.3.2/querying/writing/delete) for removing rows
- [SELECT / Query](/database/0.3.2/querying/reading/select) for reading rows

---

# UPDATE

Update rows with the generated Update() builder. Targets the row by primary key, narrows the column set, and adds extra WHERE conditions.

## Overview

Every generated entity instance exposes an `.Update()` method that returns a fluent builder. By default it updates every mapped column and targets the row whose PK matches the instance. You can narrow or change the set of columns updated and add extra `WHERE` conditions on top of the PK filter.

## Default Behavior

Without any field-selection modifier, `Update()` generates a `SET` clause for every mapped column (the primary-key columns are included too, set to their current values). The `WHERE` clause is always built from the PK column(s) using the values currently on the instance.

```csharp
// update all non-PK columns for the matching row
user.Name = "Alice Renamed";
user.Score = 42;

int rows = await user.Update()
    .WithConnection(conn)
    .ExecuteAsync();
// SQL: UPDATE "users" SET "name" = $1, "score" = $2, ... WHERE "id" = $n
```

`ExecuteAsync()` returns `Task<int>`, the number of rows affected. A return value of `0` means no row matched the PK (the row does not exist or was already deleted).

## Field Selection Strategies

### WithAllFields: Explicit All Columns

Explicitly includes every column, including any the default behavior might exclude.

```csharp
// update all columns explicitly
int rows = await user.Update()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();
```

### WithAllFields().ExceptFields(): All Columns Except Listed

Updates all columns but skips the ones you specify. Useful for preserving immutable audit columns.

```csharp
// update everything except CreatedAt
int rows = await user.Update()
    .WithConnection(conn)
    .WithAllFields()
    .ExceptFields(x => new object?[] { x.CreatedAt })
    .ExecuteAsync();
```

### WithFields: Only Listed Columns

Updates only the columns you specify. Every other column is left unchanged in the database.

```csharp
// update only Name and Email
int rows = await user.Update()
    .WithConnection(conn)
    .WithFields(x => new object?[] { x.Name, x.Email })
    .ExecuteAsync();
// SQL: UPDATE "users" SET "name" = $1, "email" = $2 WHERE "id" = $3
```

### ExceptFields: All Except Listed

Updates all columns except the ones you specify. The inverse of `WithFields`.

```csharp
// update all columns except UpdatedAt (managed elsewhere)
int rows = await user.Update()
    .WithConnection(conn)
    .ExceptFields(x => new object?[] { x.UpdatedAt })
    .ExecuteAsync();
```

## Custom WHERE Clause

`.Where()` replaces the default PK-based `WHERE` clause entirely with your predicate. When you supply `.Where()`, the PK filter is **not** added automatically, so include any PK condition yourself if you still need it.

```csharp
// target by PK and an extra condition, expressed in one predicate
int rows = await user.Update()
    .WithConnection(conn)
    .WithFields(x => new object?[] { x.Score })
    .Where(x => x.Id == user.Id && x.Status == "active")
    .ExecuteAsync();
// SQL: UPDATE "users" SET "score" = $1 WHERE "id" = $2 AND "status" = $3
```

## Static Shorthand

`User.UpdateAsync(instance, conn)` is a static convenience that updates every mapped column for the matching row (it uses `WithAllFields`) and returns `Task<int>`.

```csharp
// static shorthand
int rows = await User.UpdateAsync(user, conn);
```

> **NOTE** The static shorthand supports no field selection or extra WHERE conditions. Use the instance builder when you need those.

## Composite Primary Keys

When an entity has a composite PK (multiple properties marked `[PrimaryKey]`), all PK columns are automatically included in the `WHERE` clause. No extra work is needed.

```csharp
// UserCourse has [PrimaryKey] on both UserId and CourseId
enrollment.CompletedAt = DateTimeOffset.UtcNow;

int rows = await enrollment.Update()
    .WithConnection(conn)
    .WithFields(x => new object?[] { x.CompletedAt })
    .ExecuteAsync();
// SQL: UPDATE "user_course" SET "completed_at" = $1
//      WHERE "user_id" = $2 AND "course_id" = $3
```

## Update With a Transaction

Pass a `DbTransaction` instead of a `DbConnection` when the update is part of a larger unit of work.

```csharp
// update inside a transaction
await using var tx = await conn.BeginTransactionAsync();

user.Status = "suspended";

int rows = await user.Update()
    .WithTransaction(tx)
    .WithFields(x => new object?[] { x.Status })
    .ExecuteAsync();

await tx.CommitAsync();
```

## Interpreting the Return Value

| Return value | Meaning |
|---|---|
| `> 0` | That many rows were updated (usually `1` for PK-based updates) |
| `0` | No matching row found; the instance's PK does not exist in the table |

```csharp
// detect a missing row
int rows = await user.Update()
    .WithConnection(conn)
    .ExecuteAsync();

if (rows == 0)
    throw new InvalidOperationException($"User {user.Id} not found.");
```

> **NOTE** UPDATE uses `ExceptFields` to exclude columns. It has no `ExcludeAutoFields`, `WithValuePropagation`, or `ExecuteReturningAsync`; those are INSERT-only.

## See Also

- [INSERT](/database/0.3.2/querying/writing/insert) for adding new rows
- [DELETE](/database/0.3.2/querying/writing/delete) for removing rows
- [WHERE Operators](/database/0.3.2/querying/reading/where) for the predicate syntax of `.Where()`

---

# Audit columns

Track when rows are created and last changed with server-side defaults and a small update hook.

Most tables benefit from `created_at` and `updated_at` columns. The created timestamp is a pure database default; the updated timestamp needs a touch on each write.

## Created timestamp

`[Default(DbDefaults.Time.Now)]` stamps the row server-side on insert, so the application never sets it:

```csharp
[Default(DbDefaults.Time.Now)]
public DateTime CreatedAt { get; set; }
```

`DbDefaults.Time.Now` is portable: the generator emits the platform's UTC-now expression in the DDL. See [DB constants](/database/0.3.2/advanced/db-constants).

## Updated timestamp

Set `UpdatedAt` before every update. Centralize it so no call site forgets:

```csharp
[Default(DbDefaults.Time.Now)]
public DateTime UpdatedAt { get; set; }

// in your service / repository
public Task<int> SaveAsync(TaskItem task) =>
    db.ExecuteAsync(d =>
    {
        task.UpdatedAt = DateTime.UtcNow;
        return d.Tasks.UpdateAsync(task);
    });
```

`UpdateAsync` writes every non-key column by default, so `updated_at` is included with no extra configuration.

## Who changed it

To record an actor, add a column and set it from your request context:

```csharp
public Guid? UpdatedBy { get; set; }
```

```csharp
task.UpdatedAt = DateTime.UtcNow;
task.UpdatedBy = currentUserId;
await d.Tasks.UpdateAsync(task);
```

> **TIP** If you want the timestamp enforced even for writes that bypass the application, keep the C# touch for convenience and add a database trigger as the source of truth. The `[Default]` expression handles inserts; a `BEFORE UPDATE` trigger handles updates. A [value convertor](/database/0.3.2/advanced/value-convertors) is the place to normalize an audit value (for example forcing UTC) on the way to the column.

---

# Keyset pagination

Page through large tables with a stable cursor instead of OFFSET, using the generated query builder for ORDER BY and LIMIT.

`OFFSET` pagination re-scans every skipped row and can drift when data changes between pages. **Keyset** (cursor) pagination filters on the last value you saw, so each page is a fast index range scan with a stable boundary.

The context sets cover predicate reads; ordering and `LIMIT` come from the generated query builder, so reach for the builder here. Borrow a connection from the context with `WithConnectionAsync`:

```csharp
using System.Linq;   // ToListAsync() over the async stream (System.Linq.Async)

// First page: the 20 newest tasks in a project.
List<TaskItem> page = await db.ExecuteAsync(d => d.WithConnectionAsync(conn =>
    TaskItem.Query(x => x.ProjectId == projectId)
        .OrderByDesc(x => new object?[] { x.CreatedAt })
        .Limit(20)
        .WithConnection(conn)
        .ExecuteAsync()
        .ToListAsync()));
```

For the next page, use the last row's sort value as the cursor and ask for rows beyond it:

```csharp
DateTime cursor = page[^1].CreatedAt;

List<TaskItem> next = await db.ExecuteAsync(d => d.WithConnectionAsync(conn =>
    TaskItem.Query(x => x.ProjectId == projectId && x.CreatedAt < cursor)
        .OrderByDesc(x => new object?[] { x.CreatedAt })
        .Limit(20)
        .WithConnection(conn)
        .ExecuteAsync()
        .ToListAsync()));
```

> **WARNING** A single column is only a stable cursor if its values are unique. When timestamps can collide, add a tiebreaker (the primary key) to both the `ORDER BY` and the predicate so no row is skipped or repeated.

```csharp
.Query(x => x.ProjectId == projectId
    && (x.CreatedAt < cursorCreatedAt
        || (x.CreatedAt == cursorCreatedAt && x.Id < cursorId)))
.OrderByDesc(x => new object?[] { x.CreatedAt, x.Id })
```

Index the sort columns (`CREATE INDEX ON tasks (project_id, created_at DESC, id DESC)`) so each page stays an index range scan. See [Sorting & pagination](/database/0.3.2/querying/reading/sorting-pagination) for the builder methods and the `OFFSET` form.

---

# Multi-tenant tables

Two patterns for serving many tenants, a shared table keyed by tenant or a table per tenant, and when to reach for each.

There are two common ways to isolate tenant data. Pick by your isolation and scale needs; the library supports both directly.

## Shared table, tenant column

The simplest model: one table for everyone, with a `tenant_id` on every row and on every query. Best for many small tenants.

```csharp
[Table("documents")]
public partial class Document
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public Guid TenantId { get; set; }

    public required string Title { get; set; }
}
```

Every read and write carries the tenant filter:

```csharp
public Task<List<Document>> ListAsync(Guid tenantId) =>
    db.ExecuteAsync(d => d.Documents.ToListAsync(x => x.TenantId == tenantId));
```

> **WARNING** Isolation is only as strong as your discipline: a query that forgets `TenantId` leaks across tenants. Funnel all access through a per-tenant repository, and back it up with PostgreSQL row-level security so the database enforces the boundary too.

## Table per tenant, with dynamic tables

For strong isolation or very large tenants, give each tenant its own table (`documents_tenant_42`) and bind the name at runtime with a `[TableType]`. One declared shape serves every tenant, and results stay fully typed.

```csharp
[TableType]
public partial class Document
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }
    public required string Title { get; set; }
}
```

```csharp
static string TableFor(Guid tenantId) => $"documents_{tenantId:N}";

public Task<List<Document>> ListAsync(Guid tenantId) =>
    db.ExecuteAsync(d => d.DynamicTable<Document>(TableFor(tenantId))
        .Query(x => x.Title != "")
        .ToListAsync());

public Task ProvisionAsync(Guid tenantId) =>
    db.ExecuteAsync(d => d.DynamicTable<Document>(TableFor(tenantId)).InstantiateAsync());
```

`InstantiateAsync` creates a tenant's table on demand from the declared shape, and the table name is a SQL identifier you control, never user input. See [Dynamic tables](/database/0.3.2/dynamic-tables/declaring) for the full runtime API.

## Choosing

| | Shared table + `tenant_id` | Table per tenant (`[TableType]`) |
|---|---|---|
| Isolation | logical, app-enforced | physical, per table |
| Tenant count | thousands of small tenants | fewer, larger tenants |
| Provisioning | none | `InstantiateAsync` per tenant |
| Cross-tenant reports | one query | union across tables |

---

# Soft deletes

Keep deleted rows for audit and recovery by marking them with a timestamp instead of removing them, then filter them out of every read.

A soft delete marks a row as removed instead of issuing `DELETE`, which preserves history and makes recovery trivial. The pattern is a nullable timestamp column plus a predicate on every read.

## Add the column

```csharp
[Table("tasks")]
public partial class TaskItem
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public required string Title { get; set; }

    public DateTime? DeletedAt { get; set; }   // null = live, set = soft-deleted
}
```

A nullable property maps to a nullable column, so existing rows default to `NULL` (live).

## Delete by stamping, read by filtering

"Deleting" is an update that sets the timestamp:

```csharp
public Task SoftDeleteAsync(Guid id) =>
    db.ExecuteAsync(async d =>
    {
        var task = await d.Tasks.FirstOrDefaultAsync(t => t.Id == id);
        if (task is null) return;
        task.DeletedAt = DateTime.UtcNow;
        await d.Tasks.UpdateAsync(task);
    });
```

Every read filters out the deleted rows:

```csharp
public Task<List<TaskItem>> LiveTasksAsync(Guid projectId) =>
    db.ExecuteAsync(d => d.Tasks.ToListAsync(t => t.ProjectId == projectId && t.DeletedAt == null));
```

Restoring a row is the same update with `DeletedAt = null`.

> **TIP** Centralize the `DeletedAt == null` predicate in a repository or helper so no query forgets it. A partial index, `CREATE INDEX ON tasks (project_id) WHERE deleted_at IS NULL`, keeps live-row lookups fast while the dead rows stay out of the index.

To purge old soft-deleted rows for good, run a real delete on a schedule:

```csharp
public Task<int> PurgeAsync(DateTime olderThan) =>
    db.ExecuteAsync(d => d.Tasks.DeleteAsync(t => t.DeletedAt != null && t.DeletedAt < olderThan));
```

---

# Unit testing

Unit-test your data-access code with no database by mocking the generated context interfaces ISocigyDatabaseFactory, I{Db} and I{Table}Set.

## The mockable seam

Because the [database context](/database/0.3.2/core-concepts/database-context) is entirely interface-based, services that depend on `ISocigyDatabaseFactory<I{Db}>` can be unit-tested with no database. You mock three things:

- `ISocigyDatabaseFactory<IAuthDb>`: make `ExecuteAsync`/`ExecuteTransactionAsync` invoke the delegate inline,
- `IAuthDb`: return mocked table sets,
- `IUserSet` (etc.): return canned data and verify calls.

## Example (Moq)

The service under test depends only on the interface:

```csharp
public class EnrollmentService(ISocigyDatabaseFactory<IAuthDb> db)
{
    public Task<Guid> EnrollAsync(string courseName) =>
        db.ExecuteTransactionAsync(async d =>
        {
            var course = new Course { Id = Guid.NewGuid(), Name = courseName };
            await d.Courses.InsertAsync(course);
            return course.Id;
        });
}
```

The test needs no PostgreSQL:

```csharp
[Test]
public async Task EnrollAsync_inserts_course()
{
    var courses = new Mock<ICourseSet>();
    courses.Setup(s => s.InsertAsync(It.IsAny<Course>())).ReturnsAsync(true);

    var ctx = new Mock<IAuthDb>();
    ctx.SetupGet(c => c.Courses).Returns(courses.Object);

    var factory = new Mock<ISocigyDatabaseFactory<IAuthDb>>();
    factory
        .Setup(f => f.ExecuteTransactionAsync(It.IsAny<Func<IAuthDb, Task<Guid>>>(), It.IsAny<CancellationToken>()))
        .Returns((Func<IAuthDb, Task<Guid>> work, CancellationToken _) => work(ctx.Object)); // run inline

    var sut = new EnrollmentService(factory.Object);
    var id = await sut.EnrollAsync("Intro");

    Assert.That(id, Is.Not.EqualTo(Guid.Empty));
    courses.Verify(s => s.InsertAsync(It.Is<Course>(c => c.Name == "Intro")), Times.Once);
}
```

The key trick is the `.Returns(...)` that **invokes the delegate** with the mocked context, so the lambda body actually runs and your `InsertAsync` expectations are exercised.

## Integration tests

For end-to-end coverage against a real database, resolve the factory from a real `ServiceCollection` (with `AddAuthDb()` + `AddAuthDbContext()`) and assert on actual rows, including transaction commit and rollback. See the library's own `UnitTest.DB.Tests` for a working example.

> **NOTE** Keep `IAsyncEnumerable` streaming (`ForEachAsync`) out of pure unit tests. Terminal methods (`ToListAsync`, `FirstOrDefaultAsync`, `ExistsAsync`, `CountAsync`) are the natural mocking surface.

---

# Wire up the context

Register the generated unit-of-work context in dependency injection and inject the typed factory into your services and endpoints.

The generator emits a typed **context** for the database: an interface `IAppDb` with one table set per table (`Projects`, `Tasks`), plus a factory `ISocigyDatabaseFactory<IAppDb>` that opens connections, manages transactions, and scopes a unit of work. You never touch a `DbConnection` directly.

## Register it

Two calls wire everything up. `AddAppDb()` registers the keyed connection factory and migration manager; `AddAppDbContext()` registers the factory you inject:

```csharp
// TaskApi.Web/Program.cs
using Socigy.OpenSource.DB.Core.Context;
using Socigy.OpenSource.DB.AppDb.Extensions;
using Socigy.OpenSource.DB.AppDb.Context;

var builder = WebApplication.CreateBuilder(args);

builder.AddAppDb();                  // keyed IDbConnectionFactory + IMigrationManager
builder.Services.AddAppDbContext();  // ISocigyDatabaseFactory<IAppDb>

var app = builder.Build();
await app.EnsureLatestAppDbMigration();
```

The generated extensions live in `Socigy.OpenSource.DB.AppDb.Extensions`; the context interface lives in `Socigy.OpenSource.DB.AppDb.Context`.

## Inject it

Inject `ISocigyDatabaseFactory<IAppDb>` anywhere: a minimal-API handler, a controller, or a service. The factory hands you the context inside a scoped callback:

```csharp
public sealed class ProjectService(ISocigyDatabaseFactory<IAppDb> db)
{
    public Task<List<Project>> AllAsync() =>
        db.ExecuteAsync(d => d.Projects.ToListAsync());

    public Task<Project?> FindAsync(Guid id) =>
        db.ExecuteAsync(d => d.Projects.FirstOrDefaultAsync(p => p.Id == id));
}
```

`ExecuteAsync` opens one connection, runs the callback, and closes it. The `d` parameter is the `IAppDb` context, and `d.Projects` / `d.Tasks` are the typed sets. Table-set names are pluralized from the class name (`Project` to `Projects`, `TaskItem` to `Tasks`).

> **NOTE** Set names follow English pluralization, not a bare `+ "s"`: `Category` becomes `Categories`, `Class` becomes `Classes`. See [The database context](/database/0.3.2/core-concepts/database-context).

With the context injected, you can read and write. Continue to [Read and write data](/database/0.3.2/tutorial/data-access).

---

# Read and write data

Build the endpoints. Insert, query, and stream rows through the typed context sets with predicates instead of SQL.

With the context injected, the endpoints are a few lines each. Every set exposes typed reads and writes that take expression predicates, so the compiler checks your queries.

## Create a project

`InsertAsync` defaults `includeAutoFields` to `false`, so the `[Default]` columns (`Id`, `CreatedAt`) are left for the database to fill:

```csharp
app.MapPost("/projects", (Project project, ISocigyDatabaseFactory<IAppDb> db) =>
    db.ExecuteAsync(d => d.Projects.InsertAsync(project)));
```

## List a project's tasks

`ToListAsync` takes an optional predicate and materializes the rows inside the scope:

```csharp
app.MapGet("/projects/{id:guid}/tasks", (Guid id, ISocigyDatabaseFactory<IAppDb> db) =>
    db.ExecuteAsync(d => d.Tasks.ToListAsync(t => t.ProjectId == id)));
```

## Add a task

```csharp
app.MapPost("/projects/{id:guid}/tasks", (Guid id, TaskItem task, ISocigyDatabaseFactory<IAppDb> db) =>
{
    task.ProjectId = id;
    return db.ExecuteAsync(d => d.Tasks.InsertAsync(task));
});
```

## The set API

Each table set (`d.Projects`, `d.Tasks`) exposes:

| Method | Returns | Notes |
|--------|---------|-------|
| `ToListAsync(predicate?)` | `Task<List<T>>` | all rows, or those matching the predicate |
| `FirstOrDefaultAsync(predicate)` | `Task<T?>` | first match or `null` |
| `ExistsAsync(predicate)` | `Task<bool>` | existence check |
| `CountAsync(predicate?)` | `Task<long>` | `SELECT COUNT(*)` |
| `InsertAsync(entity, includeAutoFields = false)` | `Task<bool>` | one row |
| `InsertMultipleAsync(entities, ...)` | `Task<int>` | batched multi-row insert |
| `UpdateAsync(entity)` | `Task<int>` | update by primary key |
| `DeleteAsync(predicate)` | `Task<int>` | delete matching rows |
| `ForEachAsync(predicate, onRow)` | `Task` | stream rows without buffering |

To process many rows without loading them all into memory, stream with `ForEachAsync`:

```csharp
app.MapPost("/projects/{id:guid}/complete-all", (Guid id, ISocigyDatabaseFactory<IAppDb> db) =>
    db.ExecuteAsync(d => d.Tasks.ForEachAsync(t => t.ProjectId == id, async task =>
    {
        task.Done = true;
        await d.Tasks.UpdateAsync(task);
    })));
```

> **NOTE** The set methods (`d.Tasks.ToListAsync(...)`) are distinct from the static query builder (`TaskItem.Query(...)`). The builder is for standalone use with your own connection and is covered under [Querying](/database/0.3.2/querying/reading/select); the set methods run inside the context scope.

The last endpoint, creating a project and its tasks atomically, needs a transaction. Continue to [Transactions and tests](/database/0.3.2/tutorial/transactions-and-tests).

---

# Generate and apply migrations

Configure the database, generate PostgreSQL DDL from the annotated models with the bundled CLI, and apply it at startup with one generated call.

The generator turns your classes into typed C#. A second tool, the bundled migration CLI, turns the same classes into PostgreSQL DDL. Both read the identical `[Table]` metadata, so the schema and the code never drift.

## Configure the database

Add `socigy.json` to the model assembly. It names the database and selects the platform, which is what tells the generator to emit the context, factory, and DI extensions.

```json
// TaskApi.AppDb/socigy.json
{
  "database": {
    "platform": "postgresql",
    "databaseName": "AppDb"
  }
}
```

`databaseName` becomes the prefix for every generated type: `IAppDb`, `AddAppDb()`, `EnsureLatestAppDbMigration()`. See [Configuration](/database/0.3.2/getting-started/configuration) for every field.

## Generate the migration

The migration CLI runs automatically as part of the build. There is no separate command to invoke: building the model project in the `DB_Migration` configuration triggers it through an MSBuild target the package registers.

```bash
dotnet build TaskApi.AppDb/TaskApi.AppDb.csproj -c DB_Migration
```

Each run writes a timestamped `.g.cs` migration class into `Socigy/Migrations/` and records a `Socigy/structure.json` snapshot of the model. The DDL lives inside the migration class as up and down SQL. The first run emits a full `CREATE TABLE` baseline for `projects` and `tasks` (primary keys, the unique constraint, and the foreign key); later builds diff against `structure.json` and emit only the `ALTER TABLE` statements needed to catch up. Because each migration is plain C#, it compiles into the assembly where the runtime migration manager can apply it. See [Schema generation](/database/0.3.2/migration/schema-generation) and the [CLI tool](/database/0.3.2/migration/cli-tool) for details.

## Apply on startup

Point a connection string at your database in the host project. The factory appends `Database=AppDb` for you, so omit the database name:

```json
// TaskApi.Web/appsettings.json
{
  "ConnectionStrings": {
    "AppDb": { "Default": "Host=localhost;Username=app;Password=secret" }
  }
}
```

After `builder.Build()`, the generated `EnsureLatestAppDbMigration()` extension creates the database if needed and applies every pending migration before the app serves traffic:

```csharp
var app = builder.Build();
await app.EnsureLatestAppDbMigration();
app.Run();
```

> **TIP** In tests or worker processes where there is no `WebApplication`, resolve the keyed `IMigrationManager` and call `EnsureLatestVersion()` directly. See [Applying migrations](/database/0.3.2/migration/applying).

The database now matches the model. Next, wire the typed context into the app: [Wire up the context](/database/0.3.2/tutorial/context).

---

# What you'll build

A guided, end-to-end build of a typed Projects and Tasks HTTP API on PostgreSQL, from schema to migrations to a tested data layer, with zero hand-written SQL.

This tutorial builds a small but complete HTTP API on top of `Socigy.OpenSource.DB`. By the end you will have modelled a schema in C#, generated and applied a migration, wired the generated unit-of-work context into dependency injection, and read and written data through it without writing a line of SQL.

## What we're building

A **Projects and Tasks** API with two tables:

- `projects`, a named project with a creation timestamp.
- `tasks`, a task that belongs to a project (foreign key), with a title and a done flag.

Endpoints:

| Method | Route | Action |
|--------|-------|--------|
| `POST` | `/projects` | create a project |
| `GET`  | `/projects/{id}/tasks` | list a project's tasks |
| `POST` | `/projects/{id}/tasks` | add a task |
| `POST` | `/projects` (seeded) | create a project and its first tasks in one transaction |

## Prerequisites

- .NET 8 or later and a reachable PostgreSQL instance.
- Basic familiarity with ASP.NET Core minimal APIs and dependency injection.

## Project layout

A clean split keeps the model assembly (which the generator processes) separate from the web host:

```filetree
TaskApi/
├── TaskApi.AppDb/              ← model assembly: [Table] classes + socigy.json
│   ├── Project.cs
│   ├── TaskItem.cs
│   └── socigy.json
└── TaskApi.Web/                ← ASP.NET host: references TaskApi.AppDb
    └── Program.cs
```

The generator runs inside `TaskApi.AppDb` at build time and emits the typed context, the connection factory, and the DI extensions into that assembly. The web host just references it and calls the generated `AddAppDb()` / `AddAppDbContext()` methods.

Start with [Model the schema](/database/0.3.2/tutorial/schema).

---

# Model the schema

Define the projects and tasks tables as annotated C# classes. Attributes map them to PostgreSQL columns, defaults, keys, and a foreign-key relationship.

In `Socigy.OpenSource.DB` the C# class **is** the schema. Each `[Table]` class is a partial class so the generator can add the typed query methods to it at build time.

## The project table

```csharp
// TaskApi.AppDb/Project.cs
using Socigy.OpenSource.DB.Attributes;

[Table("projects")]
public partial class Project
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [Unique]
    public required string Name { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}
```

- `[Table("projects")]` sets the SQL table name. The class must be `partial`.
- `[PrimaryKey, Default(DbDefaults.Guid.Random)]` makes `Id` the primary key and tells the database to generate a random UUID, so callers never set it.
- `[Unique]` adds a unique constraint on `name`.
- `[Default(DbDefaults.Time.Now)]` stamps `created_at` server-side.

Property names map to `snake_case` columns automatically: `CreatedAt` becomes `created_at`. All of these attributes live in `Socigy.OpenSource.DB.Attributes`.

## The task table

```csharp
// TaskApi.AppDb/TaskItem.cs
using Socigy.OpenSource.DB.Attributes;

[Table("tasks")]
public partial class TaskItem
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ForeignKey(typeof(Project), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid ProjectId { get; set; }

    public required string Title { get; set; }

    public bool Done { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}
```

`[ForeignKey(typeof(Project), ...)]` on `ProjectId` references `Project`'s primary key automatically. `OnDelete = DbValues.ForeignKey.Cascade` deletes a project's tasks when the project is removed. See [Constraints](/database/0.3.2/defining-models/constraints) for composite keys and other referential actions.

> **NOTE** Nothing here is reflection or runtime configuration. The generator reads these attributes at compile time and emits plain C#. See [How it works](/database/0.3.2/core-concepts/how-it-works).

With the schema modelled, the next step turns it into a real database. Continue to [Generate and apply migrations](/database/0.3.2/tutorial/migrations).

---

# Transactions and tests

Commit several writes atomically with ExecuteTransactionAsync, then unit-test the data layer with no database by mocking the generated context interfaces.

The last endpoint creates a project and its first tasks together. Either all of it lands or none of it does, which is exactly what `ExecuteTransactionAsync` provides.

## One atomic unit of work

`ExecuteTransactionAsync` opens a transaction, runs the callback, **commits when it returns**, and **rolls back if it throws**. Move the multi-write logic into a service:

```csharp
public sealed class ProjectService(ISocigyDatabaseFactory<IAppDb> db)
{
    public Task<Guid> CreateWithTasksAsync(string name, IEnumerable<string> titles) =>
        db.ExecuteTransactionAsync(async d =>
        {
            var project = new Project { Id = Guid.NewGuid(), Name = name };
            await d.Projects.InsertAsync(project, includeAutoFields: true);

            foreach (var title in titles)
                await d.Tasks.InsertAsync(new TaskItem { ProjectId = project.Id, Title = title });

            return project.Id;   // committed here; any throw above rolls the whole thing back
        });
}
```

Generating the `Id` client-side and passing `includeAutoFields: true` writes that value, so the foreign key on each task is known before the rows are inserted. Register the service and wire the endpoint:

```csharp
builder.Services.AddScoped<ProjectService>();

app.MapPost("/projects/seeded", (SeedRequest body, ProjectService svc) =>
    svc.CreateWithTasksAsync(body.Name, body.Tasks));

record SeedRequest(string Name, string[] Tasks);
```

> **NOTE** Nested `ExecuteAsync` / `ExecuteTransactionAsync` calls join the outermost scope, so a service that calls another service still commits once. See [Transactions](/database/0.3.2/core-concepts/transactions).

## Test it without a database

Because the context is a set of generated interfaces (`ISocigyDatabaseFactory<IAppDb>`, `IAppDb`, `IProjectSet`, `ITaskItemSet`), the service is testable with a mocking library and no PostgreSQL. Wire the mock factory to invoke the callback against a mock context:

```csharp
[Fact]
public async Task CreateWithTasksAsync_inserts_project_and_each_task()
{
    var projects = new Mock<IProjectSet>();
    var tasks = new Mock<ITaskItemSet>();

    var ctx = new Mock<IAppDb>();
    ctx.SetupGet(c => c.Projects).Returns(projects.Object);
    ctx.SetupGet(c => c.Tasks).Returns(tasks.Object);

    var db = new Mock<ISocigyDatabaseFactory<IAppDb>>();
    db.Setup(f => f.ExecuteTransactionAsync(It.IsAny<Func<IAppDb, Task<Guid>>>(), It.IsAny<CancellationToken>()))
      .Returns((Func<IAppDb, Task<Guid>> work, CancellationToken _) => work(ctx.Object));

    var svc = new ProjectService(db.Object);
    await svc.CreateWithTasksAsync("Launch", new[] { "Design", "Build", "Ship" });

    projects.Verify(p => p.InsertAsync(It.IsAny<Project>(), true), Times.Once);
    tasks.Verify(t => t.InsertAsync(It.IsAny<TaskItem>(), false), Times.Exactly(3));
}
```

The mocked factory runs your real `CreateWithTasksAsync` logic, so the test verifies the unit of work itself: one project insert, one task insert per title. For tests against a real database, point a connection string at a disposable PostgreSQL instance and run migrations in a fixture. See [Unit testing](/database/0.3.2/testing/unit-testing).

## Where to go next

You have modelled a schema, migrated it, and read, written, and tested a typed data layer. From here:

- [Querying](/database/0.3.2/querying/reading/select) for the full standalone query builder, joins, and set operations.
- [Dynamic tables](/database/0.3.2/dynamic-tables/declaring) for per-tenant and time-partitioned tables.
- [Recipes](/database/0.3.2/recipes/keyset-pagination) for production patterns like pagination, soft deletes, and multi-tenancy.