Database Providers
This guide explains how the toolkit’s relational persistence is split between a provider-agnostic core and thin provider adapters, what is specific to each supported database, and how to add a new one.
How the layers fit together
| Package | Role |
|---|---|
Core.Persistence.Abstractions |
Unit-of-work, flow-context, storage-policy and repository contracts. No database driver. |
Infrastructure.Persistence.Db |
Everything that is not provider-specific: the IDbDialect contract, unit of work, connection pool, resilience, repository bases, health reports and the transactional outbox bases. |
Infrastructure.Persistence.Db.<Provider> |
A thin adapter closing the generic bases over the provider’s connection and dialect. |
Testing.Db and Testing.Db.<Provider> |
Test helpers, with the same split. |
Applications reference a provider package and register its module; their own code only depends on the abstractions and the repository bases.
Supported providers
PostgreSQL
Infrastructure.Persistence.Db.Postgres uses Npgsql. Register ModulePostgres(connectionOptions, resilienceOptions) and, for health checks, ModulePostgresHealthReport (or AddPostgresHealthReports()). Test with Testing.Db.Postgres.
Oracle
Infrastructure.Persistence.Db.Oracle uses ODP.NET (Oracle.ManagedDataAccess.Core). Register ModuleOracle(connectionOptions, resilienceOptions) and ModuleOracleHealthReport. OracleConnectionOptions.DbName is the service name, and the port defaults to 1521. The connection string is a full connect descriptor with an EXPIRE_TIME keepalive. Health checks run SELECT SYSDATE FROM DUAL, with tests named oracle_connection-{reader|writer}. Test with Testing.Db.Oracle.
MySQL
Infrastructure.Persistence.Db.Mysql uses MySqlConnector. Register ModuleMysql with factories for MysqlConnectionOptions and MysqlResilienceOptions; ModuleMysqlHealthReport (or AddMysqlHealthReports()) registers the reader and writer checks. The unkeyed IStoragePolicy<MySqlConnection> uses the writer; use the reader key for read-only operations.
- Hosts may be absolute URIs such as
tcp://mysql.exampleor relative host names. The port defaults to 3306, and the command timeout is in seconds. - A nonempty
TimeZoneis applied to each newly opened session. Named zones such asUTCrequire populated MySQL time-zone tables; offsets such as+00:00do not. - The adapter enables MySqlConnector’s
Ignore Command Transactionoption, because the shared repositories execute commands on the unit of work’s connection without passing its transaction explicitly. - Individual commands are not retried inside a transaction: a deadlock may invalidate the entire transaction, so retry the whole unit of work instead.
- Batch writes use Dapper’s execute-many.
Test with Testing.Db.Mysql, which provides IMysqlAppSettings in Anarchy.Toolkit.Testing.Db.Mysql.Entity.Configuration for the settings-based fixtures. Table truncation resets auto-increment and may fail on tables referenced by foreign keys.
Provider notes
Provide SQL written for the target database for your repositories and outbox implementations; the toolkit does not translate it.
Oracle SQL. SQL files must not end with ; (ODP.NET rejects it) and use :Name binds (Dapper turns on BindByName). Since Oracle cannot select from an INSERT (IDbDialect.CanSelectFromInsert), the outbox’s get-or-insert of type IDs runs Common.<Type>.Insert.IfMissing (a MERGE), then Common.<Type>.Get.ByName. Cast IDs with CAST(… AS NUMBER(9|18)) so ODP.NET returns int/long. Savepoints are never released, as Oracle has no such statement, and TruncateTableAsync uses a plain TRUNCATE TABLE (no identity restart or cascade).
Adding a database provider
A new provider only adds a thin adapter, mirroring the existing provider projects:
Infrastructure.Persistence.Db.<Provider>:- a dialect implementing
IDbDialect:Name,IsNoDataandIsUniqueViolation, plus overrides of the ANSI defaults that differ (IsServerError,IsTransient, the savepoint SQL,ToBatchParameters,HealthCheckSql,TruncateTableSql). ReturnnullfromSavepointReleaseSqlwhen the provider has noRELEASE SAVEPOINT; - a connection string (
DbConnectionStringBase<TOptions>) and a pool (DbConnectionPoolBase<TConnection>.CreateConnection); - one-line unit-of-work, policy, retry-builder and repository-base subclasses closing the generics over the provider’s connection and dialect;
- a
Module<Provider>underDependencyInjection/Module/.
- a dialect implementing
- Health, in the same project: reader and writer subclasses of
DbHealthReportBase, anAdd<Provider>HealthReports()extension and aModule<Provider>HealthReport. Testing.Db.<Provider>: helpers delegating toDbTestHelper.- The outbox, outside the toolkit: the toolkit only ships the
Db*RepositoryBase<TConnection, TDialect>outbox bases. Applications (or an adapters package) add keyed-DI implementations over them, with the provider’s SQL in aQueries/folder next to them (LoadSqlresolves from the most derived type’s namespace). OverrideIdSequenceNameif the sequences are named differently.
Then run make check_layering: provider projects belong to the Infrastructure layer and must not reference any higher layer.