Switching providers

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.

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.

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(...).

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:

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.

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.

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:

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.

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).

• 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.

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.