# Using your own tables

ARX works on tables you already have; it never creates or changes them. This page covers which names it accepts, how
to declare each table's id, and what MongoDB and DynamoDB data needs to look like.

## Names

Table, column and attribute names must be ASCII letters, digits and underscores, starting with a letter or an
underscore, and no longer than the database allows: 63 characters on PostgreSQL, 128 on SQL Server, 64 on MySQL, 120 on
MongoDB, 255 on DynamoDB. Anything else is refused before the database: ARX1003 InvalidIdentifier, or ARX1017
SuspectedInjection when the name carries SQL or query syntax.

The rule is part of ARX's injection defence: a name reaches SQL as an identifier, not as a parameter, so ARX accepts only
names that can't break out of their quotes.

On PostgreSQL, column names are folded to lower case, as unquoted SQL does; table names are used as given, so write them
as the database stores them (lower case, unless the table was created with a quoted name).

### Columns with other names: aliases

From 0.1.0-preview.2, a column named `first-name`, `First Name`, `order.total` or `préférence` is used through an alias
declared in the schema catalog. Code uses the alias, a valid name; ARX sends the column's real name:

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("customer", ArxIdColumn.Supplied<string>("customer_code"))
    .Alias("customer", "customer_code", "Customer Code")
    .Alias("customer", "first_name", "first-name")
    .Alias("customer", "order_total", "order.total")
    .Key("customer", "first_name")
    .Build();

var customer = new ArxEntity("customer");
customer["customer_code"] = "C-1001";
customer["first_name"] = "Ana";      // written to "first-name"
customer["order_total"] = 120.50m;   // written to "order.total"
await service.CreateAsync(customer);
```

- **Where aliases work:** writes, reads (a read of every column gives each aliased column back under its alias), filters,
  order, matches (`ArxMatch.Key`, `ArxMatch.Columns`), and the table's id and keys, which are declared by their
  aliases. `ValidateSchemaAsync()` checks that each aliased column exists.
- **Only declared names pass.** The real name never comes from a call: `customer["first-name"]` is still ARX1003. Each
  provider quotes the declared name by its own rules (`"…"` with `"` doubled on PostgreSQL, `[…]` with `]` doubled on SQL
  Server, `` `…` `` with `` ` `` doubled on MySQL); on MongoDB and DynamoDB it is the field or attribute name.
- **What keeps the identifier rule in this version:** the columns of a link entity (a join).
- **MongoDB:** a field name can't start with `$` or contain `.` (MongoDB reads those as an operator or a path): such an
  alias is ARX1003 when used. The id is always `_id`, so an alias on it changes nothing.
- **DynamoDB:** an alias is for ordinary attributes. One on an attribute the model names (a key, an index key, a key
  template's attribute) or on the version column is ARX8001: name those in the model as the table stores them. A `.` in
  the real name is one attribute, not a path.
- **Strict** (a preloaded schema used as an allow list) accepts a declared alias as a known column.

On a SQL database, a view that renames the columns still works too.

## Declare each table's id

ARX never guesses ids. Declare each table once, in the schema catalog:

| The table's key | Declaration |
|---|---|
| An identity, `serial` or `AUTO_INCREMENT` integer | `.Table("account", ArxIdColumn.Generated<long>("id"))` (or `Generated<int>`) |
| A `uuid`/`uniqueidentifier` the database generates (PostgreSQL, SQL Server) | `.Table("document", ArxIdColumn.Generated<Guid>("id"))` |
| A key the application gives: a code, an email, a uuid it makes | `.Table("country", ArxIdColumn.Supplied<string>("code"))` |
| No single-column key (several columns identify a row, or none do) | `.Table("price_list", ArxIdColumn.NoIdColumn)`, plus `.Key("price_list", "region", "sku")` for the columns that identify a row |

The id comes back in the type you declare. `Generated<long>`, `Generated<int>` and `Generated<short>` are integers
(`id.AsInt64()` reads any of them; `id.AsInt32()` an int), `Generated<Guid>` and `Supplied<Guid>` are Guids (`AsGuid()`),
`Supplied<string>` is text (`AsString()`); MongoDB also makes `Generated<string>` ids (ObjectIds, as text). Read the id
attribute of a row with any integer type that holds it: `GetAttributeValue<long>` of an `int` id converts (a value that
doesn't fit is ARX4001 ValueOutOfRange).

Other columns that identify a row (an account number, an email) can be declared as business keys too:
`.Key("account", "account_number")`. Update, Delete and Retrieve can then find rows by them (`ArxMatch.Key`).

A column that holds the id of a row in another table is a lookup: `.Lookup("contact", "account_id", "account")`, and
`.DisplayName("account", "name")` names what a lookup reads back with.

### Or let ARX read them (SQL providers)

For a small application or a script, a PostgreSQL, SQL Server or MySQL service created with only the connection
string reads each table's primary key and column types from the database before the call:

```csharp
var service = new ArxPgDbService(connectionString);   // no catalog
ArxRecordId id = await service.CreateAsync(account);  // the id column comes from account's primary key
```

It is much slower (one more round trip per call; `RememberResolvedTables = true` reads each table once per service),
and a table with no primary key, or a compound one, is ARX1019 TableNotResolvable when the call needs the id: declare
it as above. Each operation's "Without a catalog" sample shows it.

### Or read them once, at startup (the preload)

The preload reads the ids, keys, lookups and column types of the tables you name once, when the application starts,
and gives you a catalog to create the service with; calls never read the database for it again:

```csharp
var schema = await new ArxPreLoadTable(new ArxPreLoadTableRegister()
        .PreLoadTable("account")
        .PreLoadTable("contact"))
    .LoadAsync(connectionString);

var service = new ArxPgDbService(connectionString, schema);
```

`LoadAsync` is an extension method of each provider (`ArxPgPreLoad`, `ArxMsSqlPreLoad`, `ArxMySqlPreLoad`,
`ArxMongoPreLoad`, `ArxDynamoPreLoad`). An application that references two providers and imports both namespaces gets
CS0121 (an ambiguous call) from `preload.LoadAsync(connectionString)`: call the provider's class by name instead,
`await ArxPgPreLoad.LoadAsync(preload, connectionString)`. Each operation's "Dynamic table schema" advanced sample shows
the preload.

Then check the declarations against the database once, at startup:

```csharp
await service.ValidateSchemaAsync();
```

It reports a table or column that isn't there, an id of the wrong type, or an id column that doesn't identify one row.
Run it before the first write: a wrong id column isn't always caught by the write itself. On MySQL a generated id comes
back from `LAST_INSERT_ID()`, which doesn't name the column, so a catalog that declares `id` on a table whose key is
`customer_id` inserts the row and returns an id, and only the reads and deletes by that id fail (ARX5002).

## Your first table

ARX doesn't create tables: the application's database scripts or migrations do. To try ARX on a table of your own:

1. Create the table in the database, with its tool (psql, SQL Server Management Studio or `sqlcmd`, the `mysql`
   shell, or any SQL client). The statements below make the Quick Start's `account` table.
2. Declare it in the schema catalog with its id: `.Table("account", ArxIdColumn.Generated<long>("id"))`.
3. Create a record: `entity["name"] = "Northwind Traders"; await service.CreateAsync(entity);`.

The Quick Start's `account` table, on each SQL database:

```sql
-- PostgreSQL
CREATE TABLE account (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL,
                      account_number text UNIQUE, employee_count int);
-- SQL Server
CREATE TABLE account (id bigint IDENTITY PRIMARY KEY, name nvarchar(200) NOT NULL,
                      account_number nvarchar(50) UNIQUE, employee_count int);
-- MySQL
CREATE TABLE account (id bigint AUTO_INCREMENT PRIMARY KEY, name varchar(200) NOT NULL,
                      account_number varchar(50) UNIQUE, employee_count int);
```

## Values the column changes

ARX sends each value as a parameter of its own .NET type; the column stores what its type can hold, without an error:

- **Decimal places**: a `decimal` with more places than the column's scale is rounded by the database (`12.345` into
  `numeric(10, 2)` is `12.35`).
- **Dates without a time**: a `DateTime` written to a `date` column keeps only the date.
- **Offsets**: a `DateTimeOffset` keeps its moment, not its offset, on every provider: ARX writes it as UTC (SQL Server
  `datetimeoffset` and `datetime2` alike, PostgreSQL `timestamptz`, MySQL `TIMESTAMP`/`DATETIME`). A local `DateTime` is
  converted to UTC first on SQL Server and MySQL. Store the offset in a column of its own if the application needs it.
- **Local times**: on MySQL, a `DateTime` of Kind Local is converted to UTC before it goes into a `DATETIME` column, so
  `DateTime.Now` at 15:00 in UTC+8 is stored as 07:00. Use UTC times, or `DateTime` of Kind Unspecified for wall-clock
  times.
- **DynamoDB**: a `DateOnly` is stored as text (`2026-11-10`) and reads back as a string.

Each provider's Data types page lists what every .NET type is stored as and reads back as.

## MongoDB

The declared id is the document's `_id`: a generated string id is an ObjectId ARX makes, a generated `Guid` is
version 7, and supplied ids are written as given. Field names keep their case. A collection's unique indexes act as its
business keys' guarantee: declare the keys in the catalog and create the indexes yourself. Transactions and writes of
several documents need a replica set.

## DynamoDB

You describe your tables in an `ArxDynamoModel`, the same layout the tables were created with:

- **A table per entity**: `.Table("product", partitionKey: "sku")`. Items are stored as they are, so existing
  tables work as long as their attribute names follow the rules above, or have an alias.
- **The single-table design**: `.SingleTable("crm", "PK", "SK", t => t.Entity("customer", pk: "CUSTOMER#{customer_id}", sk: "PROFILE"))`.
  ARX writes and owns `PK`, `SK`, the index key attributes and `entity_type`, from the templates. Existing single-table
  data works only if it was written with the same templates and an `entity_type` attribute.

Ids are supplied: DynamoDB generates none.

The model and the schema catalog answer different questions. The model says where items live: their keys, templates and
indexes. The catalog says how ARX names them as entities: the id, business keys, columns, versions and lookups. The
preload builds the catalog from the model (`ArxPreLoadTable` with `LoadAsync(client, model)`): each entity's key
attributes become its business key, with no separate id column, so items are found by their key
(`RetrieveAsync(entity)` with the key attributes set). A catalog you write yourself must agree with the model: an id that
isn't part of the table's key is ARX5003 when the service is created. So declare the keys once, in the model, and let
the preload derive the catalog.