# ARX.SDK for PostgreSQL: a guide for AI assistants

These files teach an AI assistant (Claude, ChatGPT, GitHub Copilot, Cursor and the like) to write correct ARX.SDK code
for PostgreSQL. They are generated from the same verified samples, READMEs and doc comments as the documentation site:
every code example in them was compiled and run against PostgreSQL.

ARX.SDK is an entity-based data access library for .NET: one dictionary-like entity model (`ArxEntity`,
`ArxQueryExpression`) for every database, with a provider package per database. This knowledge base covers the
PostgreSQL provider, `ARX.SDK.PostgreSql`.

## Instructions for the assistant

You help a developer write C# code that uses ARX.SDK with PostgreSQL (package `ARX.SDK.PostgreSql`). Follow these
rules.

1. **Use ARX's types for database work.** Create, update, delete and read rows, call procedures and functions, and run
   transactions through `ArxPgDbService`, `ArxPgConnection` and `ArxTransaction`. Don't write Npgsql code
   (`NpgsqlConnection`, `NpgsqlCommand`) or SQL text for work ARX does.
2. **Don't invent API.** Use only the types, methods, properties and parameters listed in `arx-postgresql-types.md`. If
   something isn't there, say ARX doesn't offer it rather than guessing a name.
3. **A row is an `ArxEntity`.** `var account = new ArxEntity("account");` then values by column name:
   `account["name"] = "Northwind Traders";`. Table and column names are the database's, as strings.
4. **Declare every table's id column once, at startup,** in an `ArxSchemaCatalog`, and give it to the service:
   `ArxSchemaCatalog.Create().Table("account", ArxIdColumn.Generated<long>("id")).Build()`, then
   `new ArxPgDbService(connectionString, schema)`. Use `ArxIdColumn.Supplied<string>("code")` for an id the application
   provides and `ArxIdColumn.NoIdColumn` for a table without one; add business keys with
   `.Key("account", "account_number")`. ARX never guesses an id: an undeclared table is ARX1016. ARX never creates or changes tables: the application's scripts or migrations do, so when the user has no table yet, give its CREATE TABLE too ("Your first table" in the Using your own tables guide has one per database). A service created
   without a catalog (`new ArxPgDbService(connectionString)`) reads each table's primary key and column types from the
   database before every call instead: much slower, only for simple CRUD in small applications (set
   `RememberResolvedTables = true` to read each table once); a table without a usable primary key is then ARX1019. Reads through such a service don't know the id: rows read back have `RecordId` null and lookups come back as their plain ids, so read the id column itself (`row["book_id"]`) or give the service a catalog.
5. **Every call is asynchronous** (`CreateAsync`, `CreateMultipleAsync`, `UpdateAsync`, `UpdateMultipleAsync`,
   `DeleteAsync`, `DeleteMultipleAsync`, `RetrieveAsync`, `RetrieveMultipleAsync`, `ExecuteAsync`); await it, and pass
   the caller's `CancellationToken` where the method takes one.
6. **Create returns the id.** `ArxRecordId id = await service.CreateAsync(entity);` (also set on `entity.RecordId`);
   `CreateMultipleAsync` returns one id per entity, in list order. Point a lookup column at another record with
   `new ArxEntityReference("account", id)`.
7. **Update and Delete find rows by the id, a declared key, or columns you name:**
   `new ArxUpdateOptions { Match = ArxMatch.Key("account_number") }`, `ArxMatch.Columns(...)`. An update changes only
   the attributes that are set; setting one to `null` clears that column.
   **To change or delete every row a condition matches** (from 0.1.0-preview.5): an entity with only the values to set
   and an `ArxFilterExpression`, `int changed = await service.UpdateMultipleAsync(values, filter);`, or
   `await service.DeleteMultipleAsync("account", filter)`; one statement (binary `COPY` for a create of 5,000 rows or more of one shape), all or nothing, returning the count. The
   entity carries no id (ARX1001 with a filter); an empty filter is ARX1001 unless
   `new ArxFilterWriteOptions { AllRows = true }`. For a large insert whose ids aren't needed:
   `CreateMultipleAsync(entities, new ArxCreateMultipleOptions { ReturnIds = false })` (every id `ArxRecordId.None`).
8. **Reads.** `RetrieveAsync` returns `null` when nothing matches. `RetrieveMultipleAsync` takes an
   `ArxQueryExpression` (criteria, columns, order, paging, links to other tables) and returns the rows in `.Entities`.
   **Query cheat sheet** (the exact names; there are no others, such as `AddGroupBy` or `MoreRecords`):
   `var query = new ArxQueryExpression("book");` · columns: `query.ColumnSet = new ArxColumnSet("title", "year");`
   (empty: every column) · conditions: `query.Criteria.AddCondition("year", ArxConditionOperator.Between, 2000, 2010);`
   (Equal, NotEqual, GreaterThan, GreaterEqual, LessThan, LessEqual, Between, In, NotIn, Like, Contains, BeginsWith,
   EndsWith, Null, NotNull and their Not/DoesNot forms) · an OR group: `var either = query.Criteria.AddFilter(ArxLogicalOperator.Or);`
   then `either.AddCondition(…)` · order: `query.AddOrder("title", ArxOrderType.Ascending);`
   · a join: `var author = query.AddLink("author", "author_id", "id", ArxJoinOperator.Inner); author.EntityAlias = "a";
   author.ColumnSet = new ArxColumnSet("name");`, read as `row["a.name"]` · paging:
   `query.PageInfo = new ArxPagingInfo { Count = 20, PageNumber = 1, ReturnTotalRecordCount = true };`, then
   `result.Entities`, `result.HasNextPage`, `result.TotalRecordCount` · grouped counts:
   `query.AggregateInfo = new ArxAggregateInfo(); query.AggregateInfo.AddAggregateColumn("books", "id", ArxAggregateOperator.Count);
   query.AggregateInfo.AddGroupByColumn("author_id");` (Count reads back as a `long`). A foreign key column reads back as
   an `ArxEntityReference` (with the referenced row's name) only when the catalog declares it:
   `.Lookup("book", "author_id", "author")` and `.DisplayName("author", "name")`; otherwise it is the plain id.
9. **Values are always parameters:** pass them as .NET values and never build SQL with them. A table or column name
   that carries SQL is refused with ARX1017, but a valid name is used as given: a name typed by a user can read any
   table or column the database user can (`pg_catalog.pg_roles`, `information_schema.columns`). Never pass a name from a user unchecked: map it to one the
   application allows (an allow list), or preload the schema and set `Strict = true`.
10. **Dates:** `DateTimeOffset` or a UTC `DateTime` for a moment (`timestamptz`), a `DateTime` with Kind `Unspecified`
    for a wall-clock time (`timestamp`), `DateOnly`, `TimeOnly`, `TimeSpan` (`interval`). Never `default(DateTime)`
    (ARX1013: use `null`) and never a date as a string (ARX4004).
11. **Errors** are `ArxException`s with a stable `Code`: catch by code
    (`catch (ArxException error) when (error.Code == ArxErrorCode.UniqueViolation)`: catch the base class, since the class tells where an error was found and the code what happened) and retry only when
    `error.IsTransient` is true. `arx-postgresql-error-codes.md` lists every code.
12. **Transactions:** `await using var connection = new ArxPgConnection(connectionString);`,
    `await using var transaction = await connection.BeginTransactionAsync();`, pass `transaction` to each call, then
    `await transaction.CommitAsync();`. Disposing without committing rolls back. Code written against `IArxDataService` opens one without the provider type: `await using var transaction = await service.BeginTransactionAsync();` (from 0.1.0-preview.5; the transaction owns its connection). For many calls as one unit,
    `await service.RunBatchAsync(async batch => { … });` commits when the body returns.
13. **Base answers on the samples** in the operation files: they are verified code. Say which sample a pattern comes
    from when that helps the developer find more.
14. **Reading values and namespaces.** `entity["col"]` is the value or null; a misspelt or unread attribute
   reads null too, so take column names from the table. `GetAttributeValue<T>` of NULL is `default(T)` (0 for `int`:
   ask for `int?` to tell NULL from 0); it converts numbers when nothing is lost (a Count is always a `long`, Sum and
   Avg are decimal or double by provider: read them as `long` and `decimal`), and reads a declared lookup (an
   `ArxEntityReference`, also in a grouped query) as its id, and a UTC or Local `DateTime` as a `DateTimeOffset` (and
   back, as UTC); a number that doesn't fit is ARX4001 (1.5 as `int`), any other mismatch is ARX4004, an `ArxException`.
   Through a link a lookup column is its plain id, not an `ArxEntityReference`.
   Namespaces: `ArxEntity`, `ArxEntityReference`, `ArxColumnSet`, `ArxSchemaCatalog` and `ArxMatch` are in
   `ARX.SDK.Services`; `ArxQueryExpression`, `ArxConditionOperator`, `ArxJoinOperator`, `ArxAggregateOperator` and
   `ArxOrderType` in `ARX.SDK.Query`; `ArxException` and `ArxErrorCode` in `ARX.SDK`; `ArxPgDbService` and `ArxPgConnection` in `ARX.SDK.PostgreSql.Services`. Write every `using` a file needs.
   Names that differ from the Dynamics XRM SDK: the join operators are `ArxJoinOperator.Inner` and `ArxJoinOperator.Left` (not XRM's `LeftOuter`);
   a link's columns are `link.ColumnSet`, its alias `link.EntityAlias`, its conditions `link.LinkCriteria`; the referenced
   table of an `ArxEntityReference` is `EntityName` and its id `RecordId`. `UpdateAsync` throws ARX2002 RecordNotFound
   when no record matches (`DeleteAsync` returns false or 0 instead). A page's `TotalRecordCount` is an `int`; an aggregate
   Count is a `long`.
15. **Ids and linked columns are plain values.** `entity.RecordId` is an `ArxRecordId`: read it with
    `AsInt64()`, `AsGuid()` or `AsString()`, pass it where an id goes (`RetrieveAsync("account", id)`,
    `new ArxEntityReference("account", entity.RecordId!)`), or pass the plain value instead. `ArxRecordId` is a class, not
    a nullable struct: `RecordId` is null when no id is known, and `.Value` is the id as an `object` (pass the
    `ArxRecordId` itself, or `AsInt64()`, where a typed id goes, never `.Value`). A linked table's column reads back as `row["alias.column"]`, the plain value (no `AliasedValue`); without an alias the prefix is the table's name (`row["account.name"]`). Table names
    are matched exactly (`"Account"` isn't `account`: ARX1016); column names ignoring case.


## Packages and setup

- .NET: .NET SDK 10.0.100 or later 10.0.x (target `net10.0`); database: PostgreSQL 16 (tested with 16.15); driver: Npgsql 8.0.3 or later (the lowest version, which the provider is built on; Versions and compatibility lists the versions it is tested on).
- `ARX.SDK.PostgreSql` 1.0.0-rc.1: The PostgreSQL provider. It brings ARX.SDK and Npgsql 8.0.3 with it. Install: `dotnet add package ARX.SDK.PostgreSql --version 1.0.0-rc.1`.
- The samples read the connection string from the `ARX_SAMPLES_POSTGRESQL` environment variable, for example `Host=localhost;Port=5432;Database=arx_samples;Username=postgres;Password=your-password`. Never put real credentials in code.

## Quick reference

The first scenario of each operation's first sample: verified code.

### Create: Create one record and get its id, Id column in the schema

In scope: `connectionString` (string).

```csharp
// Declare each table's id column once, at startup. ARX never guesses it.
var schema = ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build();

var service = new ArxPgDbService(connectionString, schema);

var entity = new ArxEntity("account");
entity["name"] = "Northwind Traders";
entity["account_number"] = "ACC-0001";
entity["employee_count"] = 240;

// CreateAsync returns the id of the new record.
ArxRecordId id = await service.CreateAsync(entity);

// The account's id is a bigint column, so read it as a number.
long accountId = id.AsInt64();

Console.WriteLine($"created account {accountId}");
```

### CreateMultiple: Create many records in one call and get their ids, Id column in the schema

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build();

var service = new ArxPgDbService(connectionString, schema);

var first = new ArxEntity("account");
first["name"] = "Contoso Ltd.";
first["account_number"] = "ACC-0002";

var second = new ArxEntity("account");
second["name"] = "Fabrikam Inc.";
second["account_number"] = "ACC-0003";

var third = new ArxEntity("account");
third["name"] = "Litware Inc.";
third["account_number"] = "ACC-0004";

var accounts = new List<ArxEntity> { first, second, third };

// CreateMultipleAsync returns one id per entity, in the list's order: ids[0] is first's id, and so on.
IReadOnlyList<ArxRecordId> ids = await service.CreateMultipleAsync(accounts);

Console.WriteLine($"created accounts {string.Join(", ", ids)}");
```

### Update: Update one record, By the id Create returned

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build();

var service = new ArxPgDbService(connectionString, schema);

var account = new ArxEntity("account");
account["name"] = "Fabrikam Outdoors";
account["account_number"] = "ACC-U101";
account["employee_count"] = 40;
ArxRecordId id = await service.CreateAsync(account);

// Only the attributes you set are written: the name and account number stay as they are.
var change = new ArxEntity("account");
change.RecordId = id;
change["employee_count"] = 55;
change["website"] = "https://fabrikam.example";

int changed = await service.UpdateAsync(change);

Console.WriteLine($"account {id.AsInt64()}, {changed} row changed");
```

### UpdateMultiple: Update many records in one call, A value per row

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);
var ids = await CreateAccountsAsync(service, 1, 5);

var changes = new List<ArxEntity>();

for (var i = 0; i < ids.Count; i++)
{
    var change = new ArxEntity("account");
    change.RecordId = ids[i];
    change["employee_count"] = 100 + i;
    changes.Add(change);
}

int changed = await service.UpdateMultipleAsync(changes);

Console.WriteLine($"{changed} accounts changed in one call");
```

Defined in the sample, used above:

```csharp
private static ArxPgDbService Service(string connectionString) => new ArxPgDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build());

private static async Task<IReadOnlyList<ArxRecordId>> CreateAccountsAsync(ArxPgDbService service, int first, int count)
{
    var accounts = new List<ArxEntity>();

    for (var i = 0; i < count; i++)
    {
        var account = new ArxEntity("account");
        account["name"] = $"Basic update {first + i}";
        account["account_number"] = $"ACC-U2{first + i:00}";
        account["employee_count"] = 10;
        accounts.Add(account);
    }

    return await service.CreateMultipleAsync(accounts);
}
```

### Delete: Delete one record, By the id Create returned

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);

var account = new ArxEntity("account");
account["name"] = "Temporary account";
account["account_number"] = "ACC-D101";
ArxRecordId id = await service.CreateAsync(account);

bool deleted = await service.DeleteAsync("account", id);

Console.WriteLine($"deleted = {deleted}");
```

Defined in the sample, used above:

```csharp
private static ArxPgDbService Service(string connectionString) => new ArxPgDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Table("country", ArxIdColumn.Supplied<string>("code"))
    .Build());
```

### DeleteMultiple: Delete many records in one call, By the ids Create returned

In scope: `service` (ArxPgDbService).

```csharp
var ids = await CreateAccountsAsync(service, 1, 5);

int deleted = await service.DeleteMultipleAsync("account", ids);

Console.WriteLine($"{deleted} deleted");
```

Defined in the sample, used above:

```csharp
private static async Task<IReadOnlyList<ArxRecordId>> CreateAccountsAsync(ArxPgDbService service, int first, int count)
{
    var accounts = Enumerable.Range(first, count).Select(i =>
    {
        var account = new ArxEntity("account");
        account["name"] = $"Delete many {i}";
        account["account_number"] = $"ACC-D2{i:00}";
        return account;
    }).ToList();

    return await service.CreateMultipleAsync(accounts);
}
```

### Retrieve: Retrieve one record, By the id Create returned

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);

var account = new ArxEntity("account");
account["name"] = "Northwind Traders";
account["account_number"] = "ACC-R101";
account["employee_count"] = 120;
ArxRecordId id = await service.CreateAsync(account);

ArxEntity read = await service.RetrieveAsync("account", id, new ArxColumnSet("name", "employee_count")) ?? throw new InvalidOperationException("The account record isn't there.");

// The id is always read too, so read.RecordId can update or delete this record later.
Console.WriteLine($"{read["name"]}, {read["employee_count"]} employees, RecordId {read.RecordId}");
```

Defined in the sample, used above:

```csharp
private static ArxPgDbService Service(string connectionString) => new ArxPgDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Table("country", ArxIdColumn.Supplied<string>("code"))
    .Build());
```

### RetrieveMultiple: Retrieve many records with a query, A condition and an order

In scope: `service` (ArxPgDbService).

```csharp
var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumns("name", "employee_count");
query.Criteria.AddCondition("name", ArxConditionOperator.BeginsWith, "Basic B");
query.AddOrder("name", ArxOrderType.Ascending);

ArxEntityCollection result = await service.RetrieveMultipleAsync(query);

Console.WriteLine($"{string.Join(", ", result.Entities.Select(x => x["name"]))}");
```

### Execute: Call a stored procedure, Named arguments

In scope: `service` (ArxPgDbService), `accountId` (ArxRecordId).

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name", "New Name Ltd");
procedure.AddParameter("p_reason", "rebranding");

var result = await service.ExecuteAsync(procedure);
Console.WriteLine($"renamed; outputs: {result.Outputs.Attributes.Count()}, result sets: {result.ResultSets.Count}");
```

## The other files

- `arx-postgresql-types.md`: Every public type: its declaration, constructors, properties, methods and their parameters, with what each does.
- `arx-postgresql-sample-application.md`: The files the samples build on (the schema catalog, dbContext and its Service, the output helpers): what the samples' code refers to that isn't in the packages.
- `arx-postgresql-data-types.md`: What each .NET type is stored as in PostgreSQL, the date and time rules, and what each PostgreSQL type reads back as.
- `arx-postgresql-error-codes.md`: Every ARX error code: what it means, whether it is safe to retry, and the PostgreSQL errors it comes from.
- `arx-postgresql-create.md`: What a create does, its rules, and every Create sample: each scenario's code and the SQL that checks it.
- `arx-postgresql-create-multiple.md`: What a CreateMultiple does, its rules, and every CreateMultiple sample: each scenario's code and the SQL that checks it.
- `arx-postgresql-update.md`: What an update does, its rules, and every Update sample: each scenario's code and the SQL that checks it.
- `arx-postgresql-update-multiple.md`: What an UpdateMultiple does, its rules, and every UpdateMultiple sample: each scenario's code and the SQL that checks it.
- `arx-postgresql-delete.md`: What a delete does, its rules, and every Delete sample: each scenario's code and the SQL that checks it.
- `arx-postgresql-delete-multiple.md`: What a DeleteMultiple does, its rules, and every DeleteMultiple sample: each scenario's code and the SQL that checks it.
- `arx-postgresql-retrieve.md`: What a retrieve does, its rules, and every Retrieve sample: each scenario's code and the SQL that checks it.
- `arx-postgresql-retrieve-multiple.md`: What a RetrieveMultiple does, its rules, and every RetrieveMultiple sample: each scenario's code and the SQL that checks it.
- `arx-postgresql-execute.md`: What an execute does, its rules, and every Execute sample: each scenario's code and the SQL that checks it.
- `arx-postgresql-benchmarks.md`: How ARX compares with raw Npgsql, Dapper and EF Core, measured: the only source for performance claims.
- `arx-postgresql-all.md`: everything in one file.