# Switching providers

What stays the same when an application moves from one database to another, what changes, and what to check.

## What stays the same

The entity (`ArxEntity`), the query (`ArxQueryExpression`), the schema catalog, the options (`ArxUpdateOptions`,
`ArxDeleteOptions`, `ArxRetrieveOptions`, `ArxMatch`), the operations' names and parameters, and the error codes. Code
that builds entities and queries, and code that handles errors by code, compiles unchanged against every provider.

It doesn't always return the same rows. What a provider can't do is refused (ARX8001), but some things every provider
does are done differently by each database: text comparison, null ordering, and comparisons between values of different
types. The sections below list them; test the queries that matter on each database you move to.

Between the SQL providers and MongoDB, most programs move whole. DynamoDB is different: it reads by key and index, so
a query a SQL program sends (a join, an order on any column, a page number) is refused there. What carries over to
DynamoDB is the vocabulary (entities, conditions, matches), the error handling and the batch scopes, not every query:
plan its access patterns as DynamoDB asks, then write them with the same types.

## What changes

Each provider is its own package, with its own namespace and types:

| Database | Package | Namespace | Service | Connection |
|---|---|---|---|---|
| PostgreSQL | `ARX.SDK.PostgreSql` | `ARX.SDK.PostgreSql.Services` | `ArxPgDbService` | `ArxPgConnection` |
| SQL Server | `ARX.SDK.SQL` | `ARX.SDK.SQL.Services` | `ArxMsSqlDbService` | `ArxMsSqlConnection` |
| MySQL | `ARX.SDK.MySQL` | `ARX.SDK.MySQL.Services` | `ArxMySqlDbService` | `ArxMySqlConnection` |
| MongoDB | `ARX.SDK.MongoDb` | `ARX.SDK.MongoDb.Services` | `ArxMongoDbService` | `ArxMongoConnection` |
| DynamoDB | `ARX.SDK.DynamoDb` | `ARX.SDK.DynamoDb.Services` | `ArxDynamoDbService` | `ArxDynamoConnection` |

The services are made differently:

- PostgreSQL, SQL Server and MySQL: `new ArxXxxDbService(connectionString, schema)`.
- MongoDB: `new ArxMongoDbService(connectionString, schema)` with the database named in the connection string, or
  `new ArxMongoDbService(connectionString, database, schema)`.
- DynamoDB: `new ArxDynamoDbService(client, model, schema)`: an `IAmazonDynamoDB` client you configure, and an
  `ArxDynamoModel` of the tables (a table per entity, or the single-table design).

From 0.1.0-preview.2, every service implements `IArxDataService`: the operations, `Capabilities`, `CheckServerAsync`,
`ValidateSchemaAsync` and batch scopes. Code that depends on it moves between providers by changing one registration
(`services.AddSingleton<IArxDataService>(new ArxMySqlDbService(connectionString, schema))`), and can be mocked in tests.
What is a provider's own (its connection and transaction types, its options) stays on the provider's type.

## Ids that work on every provider

Code that should run unchanged on several databases needs ids every one of them can make and find:

- **Declare the same id column, with the same name and type, on every database.** The samples don't (their SQL tables
  use `id`, their MongoDB collections name ids after the table), so don't copy that part when one data layer serves
  several databases.
- **Choose an id kind every target makes.** `Supplied<Guid>` (the application makes `Guid.CreateVersion7()`) and
  `Supplied<string>` work on all five providers. `Generated<long>` works on the SQL providers only: DynamoDB ids are
  always supplied, and MongoDB generates text (ObjectId) or Guid ids. `Generated<Guid>` works everywhere except MySQL and
  DynamoDB (ARX8001).
- **A record found by more than one column has no single id.** On DynamoDB an item whose key has two parts (a partition
  and a sort key, such as `customer_id` and `order_number`) can't be found again by the `ArxRecordId` Create returns
  (ARX1001 names the key it needs). Find such records by their whole key, which every provider accepts: declare it
  (`.Key("customer_order", "customer_id", "order_number")` in the catalog of a SQL or MongoDB service) and match by it:
  `RetrieveAsync(match, new ArxRetrieveOptions { Match = ArxMatch.Key("customer_id", "order_number") })`, and the same
  `Match` in `ArxUpdateOptions` and `ArxDeleteOptions`. On DynamoDB the model declares that key, so `ArxMatch.Key` works there without a
  `.Key(...)`; the SQL providers and MongoDB refuse a key the catalog doesn't declare (ARX1001), so code that moves
  from DynamoDB adds the `.Key(...)`.

## What each provider can do

Not every database can do everything, and ARX never pretends: a call a provider can't do is refused with ARX8001
NotSupported. The main differences:

- **Execute** (procedures and functions): PostgreSQL, SQL Server and MySQL.
- **Joins, aggregates, page numbers and totals**: not on DynamoDB, which reads a partition at a time and pages by cookie.
- **Paging by cookie**: MongoDB and DynamoDB.
- **Optimistic concurrency** (a version column, `.Version(table, column)`): every provider from 0.1.0-preview.4; DynamoDB
  only in earlier versions.
- **Writes by a filter** (`UpdateMultipleAsync(values, filter)`, `DeleteMultipleAsync(table, filter)`, from
  0.1.0-preview.5): one statement on the SQL databases, `updateMany`/`deleteMany` in a transaction on MongoDB (a
  standalone server is ARX8001). On DynamoDB the filter must give a partition key, of the table or an index (ARX8001
  otherwise: a write never scans), and match at most 100 items (one transaction); an item written twice in one
  transaction or batch scope is ARX1001.
- **CreateMultiple without ids** (`ReturnIds = false`): binary `COPY` on PostgreSQL for a group of 5,000 rows or more of
  one shape, `SqlBulkCopy` on SQL Server from 1,000 (smaller groups take the ordinary statement, faster below those sizes);
  the ordinary insert on MySQL, MongoDB and DynamoDB. The result is the same everywhere: `ArxRecordId.None` for each.
- **Generated ids**: integers on the SQL databases; a `Guid` on PostgreSQL and SQL Server but not MySQL (ARX8001);
  on MongoDB a string (an ObjectId) or a `Guid`; DynamoDB takes supplied ids only.
- **Full-text search** (`ArxFullTextSearch` in `ARX.SDK.Models`, given as a condition's value:
  `AddCondition("body", ArxConditionOperator.Equal, new ArxFullTextSearch("quick fox"))`): each database runs its own.
  PostgreSQL uses `to_tsvector('english', …) @@ websearch_to_tsquery('english', …)`, with English stemming and no index
  required (add a GIN index on the expression for large tables). SQL Server uses `FREETEXT` and MySQL
  `MATCH … AGAINST`, both needing a full-text index on the column (ARX8001 without one). MongoDB uses `$text`, which
  searches the collection's text index whatever field you name, and only at the top of the criteria. DynamoDB has none
  (ARX8001). The operator is `Equal` or `Contains` (`Like` too on the SQL providers). The matching rules are the
  database's, so the same words can find different rows on each provider.

`service.Capabilities` says what a provider supports, and the Capabilities page compares them all.

What the database enforced moves into the application on MongoDB and DynamoDB: they have no foreign keys, NOT NULL or
CHECK constraints (MongoDB has validators and unique indexes, which you create yourself). An order that refers to a
customer who doesn't exist is ARX3002 ForeignKeyViolation on a SQL database and is written on DynamoDB; check such rules
in your code before the write, or keep them in one place that every write goes through.

A misspelt name is caught on a SQL database and not on MongoDB or DynamoDB, which have no columns to check it against.
On PostgreSQL, SQL Server and MySQL, `entity["nmae"]` in a write, a filter or an order is ARX5002 UndefinedAttribute.
On MongoDB and DynamoDB the write stores a new field `nmae`, a filter on it matches nothing, and a read of it gives
`null`. To keep that safety net there, declare the columns and make the service Strict: with
`service.Strict = true`, a name the catalog doesn't list is ARX1018 NotPreloaded before anything is sent. On MongoDB the
preload reads the columns from a collection's `$jsonSchema` validator; on DynamoDB declare them in the preload's
`configure` (`builder.Column("product", new ArxColumnDefinition("name", typeof(string), "S", true))`). Declared types
are checked too: text for a `DateOnly` column is ARX4004.

Letter case is the same kind of difference. On the SQL databases `entity["Name"]` and `entity["name"]` reach the same
column. MongoDB and DynamoDB field names are case-sensitive: a field the catalog declares (the id, a key, the version, the
display name, a preloaded or declared column) is always written and read with its declared spelling, but an undeclared
field is matched by its exact spelling, so an update with `entity["name"]` on a document that holds `Name` adds a second
field, and the document then can't be read with all columns (ARX1004, the two names differ only in case). Keep each
name in one constant, and declare the fields (with `Strict`, a name the catalog doesn't list is refused).

A loop over every page that works on every provider ends each page with `ArxPagingInfo.Next` (from 0.1.0-preview.5),
which moves by the cookie where the provider returns one (MongoDB, DynamoDB) and by the page number where it doesn't
(PostgreSQL, SQL Server, MySQL), and is null after the last page:

```csharp
query.PageInfo = new ArxPagingInfo { Count = 100 };

while (query.PageInfo != null)
{
    var page = await service.RetrieveMultipleAsync(query);
    // … use page.Entities …

    query.PageInfo = query.PageInfo.Next(page);
}
```

A loop that only passes the cookie on (`while (page.HasNextPage) { … PagingCookie = page.PagingCookie … }`), as a
DynamoDB-only loop does, reads the first page again forever on the SQL providers, which return no cookie: a null cookie
means "the first page". Use `Next` in code that may move.

Keep an order on the query: pages of an unordered query aren't guaranteed to be the same rows twice. On DynamoDB the
order is the sort key's.
`CheckServerAsync()` says what the server you reach allows: a standalone MongoDB, for one, has no transactions.

## Values written

Give each column a value of its own type and every provider behaves the same. Text given for a number or date column
is where they differ, because each database decides whether to convert it:

| Text for a number column | What happens |
|---|---|
| PostgreSQL | Refused whatever it holds: ARX4004 DataTypeMismatch (PostgreSQL doesn't convert a text parameter) |
| SQL Server, MySQL | Converted when it reads as a number (`"42"` is stored as 42); otherwise ARX4003 InvalidValueFormat |
| MongoDB, DynamoDB | Stored as text: documents have no column types (a validator you add can refuse it) |

Catch ARX4003 and ARX4004 together as the caller's bad value (both are in the Value category, 4xxx), and convert input
to the column's type before the call so every provider stores the same thing.

A number with more decimal places than its column keeps is rounded by the database, not refused: 1.239 written to
`numeric(5,2)` (PostgreSQL), `decimal(5,2)` (SQL Server, MySQL) is stored as 1.24, as an INSERT of your own would be.
MongoDB and DynamoDB have no column precision: the value is stored as given. Round the value yourself
where the rule matters. The same holds for a number with a fraction written to an integer column whose type the catalog
doesn't declare: the database rounds or truncates it (7.9 stored as 8 or 7, depending on the database). With the
column types preloaded (`ArxPreLoadMode.DataTypes`), ARX refuses such a write before the database, ARX4001
ValueOutOfRange; a condition in a read, such as `qty < 7.5`, isn't refused.

### Dates and times

Write each kind of value into the column made for it and every provider stores it the same way:

| You have | `DateTime` kind or type | Column (PostgreSQL / SQL Server / MySQL) |
|---|---|---|
| An instant | `Kind = Utc`, or a `DateTimeOffset` | `timestamptz` / `datetimeoffset` (or `datetime2` holding UTC) / `TIMESTAMP(6)` |
| A wall-clock time with no zone | `Kind = Unspecified` | `timestamp` / `datetime2` / `DATETIME(6)` |
| A date, a time of day | `DateOnly`, `TimeOnly` | `date`, `time` / `date`, `time` / `DATE`, `TIME(6)` |

On PostgreSQL, SQL Server and MySQL, ARX converts a mismatch without an error, by fixed rules. ARX's own connections run
in a UTC session, so the result doesn't depend on the server's time zone.

- A `Local` `DateTime` is converted to UTC first, everywhere. That includes a `date` column: `DateTime.Today`
  (Kind Local) written to a date column on a machine ahead of UTC stores the day before. Pass a `DateOnly` for a date
  column (`DateOnly.FromDateTime(DateTime.Today)`), and a `Kind = Unspecified` `DateTime` for a wall-clock column.
- An `Unspecified` `DateTime` written to an instant column is taken as UTC.
- A UTC `DateTime` or a `DateTimeOffset` written to a wall-clock column is stored as its UTC clock time; the offset
  is dropped.
- `default(DateTime)`, `default(DateTimeOffset)` and `default(DateOnly)` are refused (ARX1013). ARX never parses text as a
  date, but the databases differ: PostgreSQL refuses text for a date column (ARX4004), SQL Server and MySQL convert
  text they can read (`"2026-11-11"`) and refuse the rest (ARX4003), and MongoDB and DynamoDB store it as text unless
  the catalog declares the column a date (then ARX4004). Pass `DateOnly`, `DateTime` or `DateTimeOffset`.

A connection you pass in keeps its own session time zone, which then decides these conversions. Npgsql on its own refuses
some of these mismatches; ARX converts them so the same code works on every provider. When the kind matters, set it
(`DateTime.SpecifyKind`) before the write.

## Values read back

The same column type can read back as different .NET types. A timestamp is a `DateTimeOffset` from SQL Server's
`datetimeoffset` but a `DateTime` (Kind Utc) from MySQL's `TIMESTAMP`, for example. `GetAttributeValue<T>` converts
numbers when nothing is lost (an `int` column read as `long`, a Count read as `int`, a version that is an `Int64` on
MongoDB and an `Int32` on SQL Server), and, from 0.1.0-preview.5, a point in time into the other point-in-time type when
its instant is known: a UTC or Local `DateTime` read as `DateTimeOffset`, and a `DateTimeOffset` read as `DateTime`
(its UTC time, Kind Utc). An `Unspecified` `DateTime` names no instant, so reading it as `DateTimeOffset` is ARX4004
DataTypeMismatch, as is one kind of value read as another. Where a column can read back Unspecified (a wall-clock
column, or MySQL's `DATETIME`) and your code means UTC, say so:

```csharp
DateTimeOffset orderedOn = order["ordered_on"] switch
{
    DateTimeOffset value => value,
    DateTime value => new DateTimeOffset(DateTime.SpecifyKind(value, DateTimeKind.Utc)),
    _ => throw new InvalidOperationException("ordered_on is missing.")
};
```

An aggregate's type differs too. Count is a `long` everywhere; a Sum of an integer column is a `long` on PostgreSQL,
the column's own type on SQL Server (an `int`), a `decimal` on MySQL, and an `int` or a `long` on MongoDB (whichever
holds it): read it with `Convert.ToDecimal(row["total"])`, or `GetAttributeValue<long>`, in code that runs on several.
Each provider's Retrieve page has the full table.

Each provider's Data types page lists what every database type reads back as. A declared lookup reads back as an
`ArxEntityReference` on every provider, also as a group-by key (from 0.1.0-preview.5; MongoDB returned the raw id
before).

Text comparison differs too: MySQL's default collation ignores case and accents, PostgreSQL compares exactly, SQL Server
follows the column's collation. A filter or a unique key that relies on one behaviour can behave differently on another.

## Queries that return different rows

The same query, through `IArxDataService`, against the same eight rows on PostgreSQL 16, MySQL 8.4 (default collation)
and MongoDB 7. SQL Server isn't in the table: with its default collation (`SQL_Latin1_General_CP1_CI_AS`) it compares
text ignoring case, as MySQL does, and sorts nulls first ascending and last descending.

| Query | PostgreSQL | MySQL | MongoDB |
|---|---|---|---|
| `Equal "alice"` (rows `alice` and `Alice`) | `alice` only | both | `alice` only |
| `In ("alice", "BOB")` | exact matches only | ignores case | exact matches only |
| `BeginsWith "e"` on `Émile` | no match | matches (ignores accents) | no match |
| `Like`, `Contains`, `BeginsWith`, `EndsWith` | ignore case | ignore case | ignore case (`Like` and `NotLike` from 0.1.0-preview.4; case-sensitive in preview.3) |
| Order by a column with nulls, ascending | nulls last | nulls first | nulls first |
| Order by a column with nulls, descending | nulls first | nulls last | nulls last |
| Order by text | the database's collation | ignores case | binary (upper case first) |
| `GreaterThan "3"` on an integer column (a string value) | ARX4004 DataTypeMismatch | compares as numbers | no rows, no error |
| `Like "100\%%"` (a backslash before `%`) | the backslash escapes `%`: matches `100% juice` | escapes, as PostgreSQL | not an escape: no match (use `BeginsWith "100%"` for literal text on every provider) |
| A field never written, read back | `null` | `null` | read with every field: the attribute is absent (`Contains` is false; the indexer gives `null`). Named in the column set: present, as `null` (`Contains` is true), as a column is on SQL. DynamoDB does the same |

What holds on all three: nulls never match `Equal`, `NotEqual` or `In` (use `Null` and `NotNull`; an `Equal` given
`null` as its value is refused, ARX1001, rather than sent as SQL's `= NULL`, which matches nothing), `%` and `_` in a
`Contains` value are literal, dates compare the same whether you pass a `DateTime` (Utc or Unspecified) or a `DateOnly`,
and a `Null` condition finds missing fields on MongoDB as well as nulls.

To keep a query portable: give values the column's own type, add an order on a non-null column after one that can be
null, and don't rely on case or accents in `Equal` and `In` (store a normalised copy of the text if you need that).

## Transactions and batch scopes

- **Isolation.** ARX uses each database's default isolation (READ COMMITTED on PostgreSQL, SQL Server and MySQL; snapshot
  reads in a MongoDB transaction). What a read does when another transaction holds the row differs: PostgreSQL, MySQL
  and MongoDB return the last committed value; SQL Server waits for the lock (and with a short command timeout fails
  with ARX7003). Turn on `READ_COMMITTED_SNAPSHOT` on a SQL Server database to get the same behaviour as the others.

- MongoDB runs transactions only on a replica set or sharded cluster; a standalone server refuses them (ARX8001).
- DynamoDB sends a transaction's or a batch scope's writes together at commit (at most 100), so a read before the commit
  doesn't see them: it reads what is committed.
- A batch scope has no savepoints by default, on every provider: a failed call rolls the whole scope back, the same
  everywhere. Ask for them (`new ArxBatchOptions { Savepoints = true }`) where one failed call should be undone alone and
  the rest kept: PostgreSQL, SQL Server and MySQL have them (capability `Savepoints`); on MongoDB and DynamoDB `true` is
  ARX8001, so code that asks for them doesn't move there.
- What a failed call leaves differs between the two ways of grouping writes, on every provider. In a **batch scope**
  with savepoints (asked for), a failure your code catches is undone alone and the rest still commits; without them (the default) the scope
  is rolled back, and `RunBatchAsync` throws ARX1015 when the body returns rather than return as if the work were kept.
  An **`ArxTransaction`** (an `ArxConnection` you open) never commits part of a unit of work: after a call the
  database refused it can only be rolled back (`CommitAsync` is ARX1015). A call ARX refuses before the database (an
  `ArxValidationException`, ARX1xxx) sends nothing and leaves the transaction usable. Choose the batch scope when some calls may fail and the rest
  should still be saved; the transaction when everything must succeed together.
- An `ArxTransaction` opens the same way on every provider: `await using var transaction = await
  service.BeginTransactionAsync();` on `IArxDataService` (from 0.1.0-preview.5). The transaction has a connection of its
  own and closes it when it's disposed. The provider's connection types (`new ArxPgConnection(cs)`,
  `new ArxMongoConnection(service)`, …) remain for running several transactions on one connection. Batch scopes are on
  the same interface too: `IArxDataService` extends `IArxBatchDataService`, so one registration is enough.

## Before switching

1. Call `ValidateSchemaAsync()` on the new database: it checks every declared table and id column.
2. Check every capability your code relies on in `service.Capabilities`.
3. Search your code for casts of attribute values, and for text comparisons that depend on case or accents.
4. Run the queries your application depends on against both databases with the same data, and compare the rows and
   their order.