SQL mapping

Elarion's persistence story is EF-model-first, and an EF application keeps EF Core — that default is not in question. The gap is the EF-free host: an app published NativeAOT on raw Npgsql still needs to turn DbDataReader rows into records and records into parameters, per row type, by hand. Elarion.Sql is that tier's access half (ADR-0058), completing the pair with SQL migrations (ADR-0057, the schema half). It is chosen by tier, not preference:

A runnable end-to-end host — NativeAOT publish, embedded migrations (TimescaleDB hypertable included), generated mappers, ~120–160 ms start to first response — is samples/EdgeTelemetry.

The core property: if it builds, it maps. A [SqlRecord] type gets an explicit generated mapper; a type without one has no mapper to call. There is no reflection twin to fall back to — an unmapped type, an unsupported property, a duplicate column are compile errors (ELSQL001–ELSQL011), never a runtime surprise.

Quick start

A [SqlRecord] type is partial (the generator adds its self-mapping half; else ELSQL010):

using Elarion.Sql;

[SqlRecord("orders")]
public sealed partial record Order {
    public required Guid Id { get; init; }
    public required string CustomerName { get; init; }
    public string? Note { get; init; }
    public required OrderStatus Status { get; init; }   // enums map via their underlying type
    public DateTimeOffset CreatedAt { get; init; }
}

The generator emits OrderSqlMapper : ISqlRowMapper<Order> and makes Order self-mapping, so the query extensions resolve its mapper from the type — no mapper argument. In a handler, inject the scoped ISqlSession — the transaction-aware entry point (see Transactions); interpolated values become parameters, and Order.Select splices the generated SELECT-list with no :raw:

[Handler]
public sealed class ListOrders(ISqlSession db) {
    public async ValueTask<Result<List<Order>>> HandleAsync(ListOrdersQuery query, CancellationToken ct) {
        List<Order> orders = await db.QueryAsync<Order>(
            $"{Order.Select} WHERE status = {query.Status} AND customer_name IN {query.Names}", ct);
        return orders;
    }
}

The other read shapes take the same interpolated SQL on the same session:

Order? one = await db.QueryFirstOrDefaultAsync<Order>($"{Order.Select} WHERE id = {id}", ct);

Write with the same self-mapping on the same injected session — no command/transaction/BindParameters ceremony:

await db.InsertAsync(order, ct);                                   // one row
await db.InsertManyAsync(orders, " ON CONFLICT DO NOTHING", ct);   // batch, one transaction
long open = await db.ExecuteScalarAsync<long>($"SELECT count(*) FROM {Order.Table} WHERE status = {open}", ct);

What the generator emits

Per [SqlRecord] type, a sealed partial {Type}SqlMapper and a self-mapping partial on the row:

One generated AddElarionSqlMappers() per assembly registers every mapper as an ISqlRowMapper<T> singleton — a mapper is still a value you pass around (the explicit-mapper overloads remain for hand-written mappers of non-[SqlRecord] shapes). For JSON-column assemblies it also installs the canonical JSON accessor at startup (a hosted service), so a [SqlJson] host wires nothing extra.

Conventions and knobs:

• Names default to snake_case (property CreatedAt → created_at; type OrderLine → table order_line, no pluralization guessed). Override with [SqlRecord("orders")] / [SqlColumn("…")].
• [SqlIgnore] excludes a property; get-only (derived) members are skipped automatically.
• Positional records construct through their primary constructor; nominal records with required/init members through an object initializer — no parameterless-constructor requirement.
• [SqlJson] maps a property as a JSON column through the canonical accessor's JsonTypeInfo<T> (serialization, ADR-0023) — AOT-strict, one JSON config everywhere. Under [assembly: UseElarionSql(Provider = SqlProvider.Npgsql)] JSON parameters bind as jsonb.
• Supported column types: the numeric/text/boolean primitives, Guid, DateTime, DateTimeOffset, DateOnly, TimeOnly, TimeSpan, char, byte[], enums (underlying type), Nullable<T> of all of these, and nullable reference types. Anything else: [SqlJson] or [SqlIgnore] — or ELSQL001.

Safe SQL interpolation

new SqlStatement($"…") (and every query extension's $"…" overload) is the C#-native equivalent of jOOQ's plain-SQL templating tier: full SQL stays full SQL — window functions, CTEs, ON CONFLICT, any PostgreSQL feature — with injection safety enforced by the compiler, not by review:

SqlStatement where = new($"WHERE status = {status} AND id IN {ids}");
var page = await db.QueryAsync<Order>($"{Order.Select} {where} LIMIT {limit}", ct);

• A scalar hole binds as a @pN parameter — there is no string concatenation to get wrong.
• A collection hole expands to a parenthesized parameter list for IN. An empty collection throws at build time: no SQL spelling keeps both IN (match nothing) and NOT IN (match everything) correct for the empty set, so guard the query instead of shipping silently wrong rows.
• A SqlStatement hole splices as a fragment with its parameters renumbered — a reusable WHERE piece is just a value. A pure-literal fragment (Verbatim, Order.Select) inlines with no cost.
• Trusted identifiers (a validated sort column) splice via SqlStatement.Verbatim(col) — never a raw string. There is no :raw format and no SqlStatement(string) constructor: a plain string interpolated into a query always binds as a parameter, so injection is structurally impossible.

Optional filters without a query DSL

SqlWhere is the answer to the dominant call-site shape — a list with optional filters — without a query builder. Accumulate parenthesized predicate fragments; it renders WHERE (a) AND (b) or nothing, so WHERE 1=1 disappears and the same accumulator drives a page query and its count(*):

var where = new SqlWhere();
where.And($"org_id = {orgId}");
if (status is not null) where.And($"status = {status}");
if (since is not null)  where.And($"created_at >= {since}");

var page  = await db.QueryAsync<Order>($"{Order.Select} {where} ORDER BY created_at DESC LIMIT {take}", ct);
var total = await db.ExecuteScalarAsync<long>($"SELECT count(*) FROM {Order.Table} {where}", ct);

Each predicate's interpolated values become parameters — the obvious thing to type is the safe thing. SqlWhere knows only the WHERE/AND joiners; OR, grouping, and joins are hand-written SQL inside a predicate fragment. Its build-path cost is a small per-call constant (~2.4 KB for three predicates, zero per row); there is no cheaper safe way to build a dynamic WHERE.

Reads and writes

The query/write surface lives on one receiver: ISqlSession — a connection paired with its transaction intent. There is deliberately no raw-DbConnection or DbDataSource twin of these methods: a look-alike surface that skips transaction enlistment is exactly the silent-footgun class this tier removes. How you obtain the session states the semantics:

• In a handler, inject the scoped ISqlSession — writes join the framework unit of work (Transactions).
• In a singleton-eligible handler (no scoped dependencies, deliberately no unit of work), inject the singleton ISqlDatabase handle: OpenSessionAsync for per-call auto-commit, BeginTransactionAsync when several statements must commit together.
• Owning your own connection — DI-free / NativeAOT hosts, tooling, tests — bridge with AsSqlSession, deciding the transaction once at wrap time:

// The default: a scoped handler injects ISqlSession — every read and write below runs on the
// request's connection and joins its unit of work; the decorator commits or rolls back by Result.
[Handler]
public sealed class CloseOrder(ISqlSession db) {
    public async ValueTask<Result> HandleAsync(CloseOrderCommand command, CancellationToken ct) {
        await db.ExecuteAsync(
            $"UPDATE {Order.Table} SET status = {OrderStatus.Closed} WHERE id = {command.Id}", ct);
        return Result.Success();
    }
}

// Singleton-eligible handler: one call through the seam, autonomous per-call semantics.
// The receiver is ISqlDatabase — OpenSessionAsync lives on the handle, NOT on a data source.
public sealed class ListOpenOrders(ISqlDatabase db) {
    public async ValueTask<Result<List<Order>>> HandleAsync(Query query, CancellationToken ct) {
        await using var session = await db.OpenSessionAsync(ct);
        return await session.QueryAsync<Order>($"{Order.Select} WHERE status = {query.Status}", ct);
    }
}

// Atomic multi-write outside the unit of work: an owning transactional session — commit on it,
// dispose without commit rolls back. No hand-assembled connection/transaction/wrap.
await using var tx = await db.BeginTransactionAsync(ct);
await tx.ExecuteAsync($"UPDATE {Account.Table} SET session_key = {key} WHERE id = {id}", ct);
await tx.ExecuteAsync($"UPDATE {Ticket.Table} SET ticket = NULL WHERE account_id = {id}", ct);
await tx.CommitAsync(ct);

// Read first, then transact — on the SAME connection: OpenSessionAsync returns an ISqlOwnedSession,
// and only that owning session can begin a deferred transaction (the scoped session's belong to the
// unit of work). After commit/rollback the session continues autonomously.
await using var session = await db.OpenSessionAsync(ct);
var account = await session.QueryFirstOrDefaultAsync<Account>($"{Account.Select} WHERE id = {id}", ct);
await using (var deferred = await session.BeginTransactionAsync(cancellationToken: ct)) {
    await deferred.ExecuteAsync($"UPDATE {Account.Table} SET session_key = {key} WHERE id = {id}", ct);
    await deferred.CommitAsync(ct);
}

// Only when you already own the connection (and maybe its transaction): wrap once — every write enlists.
var owned = connection.AsSqlSession(transaction);

Migrating a handler that injected NpgsqlDataSource/DbDataSource directly: the whole change is the injected type — replace it with ISqlDatabase and open sessions instead of connections (dataSource.OpenConnectionAsync → db.OpenSessionAsync). OpenSessionAsync deliberately does not exist on DbDataSource: going through the handle is what lets tenant/replica routing apply.

The wrap is a cheap non-owning view (disposing it disposes nothing); because the transaction is bound when the session is created — wrap, BeginTransactionAsync, or the framework unit of work — there is no per-call transaction: parameter anywhere: a write can never accidentally run outside the transaction its scope declared. Commit lives only on ISqlTransaction (and the unit-of-work scope), never on ISqlSession itself, so a scoped handler cannot commit the framework's transaction out from under the decorator.

InsertManyAsync is a convenience batch (Npgsql auto-prepare), not bulk COPY — for high-throughput bulk load on the EF tier, use the binary-COPY path (bulk operations, ADR-0051). That path is EF-only (its entry is DbSet<T>, its metadata comes from the EF model) and is not AOT-compatible (it compiles per-column writers at runtime via System.Linq.Expressions), so the EF-free/AOT tier has no binary-COPY path today. A source-generated COPY for this tier — the [SqlRecord] generator emitting per-column NpgsqlBinaryImporter writes the same way it emits BindParameters, AOT-clean and reflection-free, with the EF tier's upsert vocabulary — is proposed in ADR-0068.

QueryUnbufferedAsync streams client-side: Npgsql reads rows off the socket incrementally, so client memory stays bounded (roughly one row at a time) regardless of result size — there is no JDBC/Hibernate-style fetchSize because Npgsql does not buffer the whole result set the way JDBC does. Bounding work on the server (incremental production) is a DECLARE CURSOR … FETCH n concern, which this thin API deliberately does not wrap — hand-write the cursor SQL and map each fetch through the generated mapper if you ever need it.

The explicit-mapper overloads (db.QueryAsync(mapper, $"…"), db.QueryFirstOrDefaultAsync(mapper, $"…")) remain the escape hatch for hand-written mappers of non-[SqlRecord] shapes. Under the hood a closed connection is opened for the call and closed afterwards (Dapper semantics); an already-open connection — the scoped session's, or one you wrapped — is left open.

Transactions (unit of work)

Because a handler injects ISqlSession — one connection pinned for the request scope — several statements can run in one transaction. That is what makes a command that writes more than once atomic. This tier supplies the same IUnitOfWork seam the EF tier does, so the framework TransactionDecorator wraps a command handler in one commit/rollback with no per-handler transaction code. On PostgreSQL, Elarion.Sql.PostgreSql registers one central data source — the shared core, EF Core's DbContext analogue — that the access tier and migrations both draw from, so the database is configured once:

// Pick the provider once — one NpgsqlDataSource shared by every subsystem (command logging auto-wired):
builder.Services.AddElarionPostgreSql(connectionString);
// ...then the neutral registrations name no provider:
builder.Services.AddElarionSqlUnitOfWork();
builder.Services.AddElarionMigrations(o => o.AddScripts(typeof(Program).Assembly, "MyApp.Migrations."));

AddElarionSqlUnitOfWork() registers the scoped ISqlSession and replaces the default no-op IUnitOfWork with SqlUnitOfWork, which opens the transaction on the session's shared connection. The handler calls the convenience surface on the session; every read and write runs on that connection and enlists the open transaction automatically. (A read-only host that never writes can register just AddElarionSqlSession() — the session with per-call auto-commit, no transaction machinery.)

ISqlDatabase is the application's database handle — the tier's DbContext/IDbContextFactory counterpart, and the only thing the session resolves, so there is no ambient DbDataSource the tier assumes. Elarion.Sql itself stays Npgsql-free; the Npgsql binding lives in Elarion.Sql.PostgreSql. Register it with one of:

Beyond feeding the scoped session, the handle is directly usable: db.OpenSessionAsync(ct) opens an owning one-shot session over a fresh pooled connection (autonomous per-call semantics, disposed with the session), and db.BeginTransactionAsync(ct) opens the transactional variant — an ISqlTransaction whose statements commit or roll back together (commit explicitly; dispose without commit rolls back) — which upgrades the singleton-eligible pattern from three steps to one:

public sealed class WorldTick(ISqlDatabase db) {          // singleton-eligible: no scoped dependencies
    public async ValueTask<Result> HandleAsync(Tick tick, CancellationToken ct) {
        await using var session = await db.OpenSessionAsync(ct);   // through the seam — routing still applies
        var rows = await session.QueryAsync<Reading>($"{Reading.Select} WHERE cell = {tick.Cell}", ct);
        // …
    }
}

[Handler]
public sealed class PlaceOrder(ISqlSession db) {
    public async ValueTask<Result> HandleAsync(PlaceOrderCommand command, CancellationToken ct) {
        await db.InsertAsync(command.Order, ct);
        await db.ExecuteAsync($"UPDATE {Customer.Table} SET order_count = order_count + 1 WHERE id = {command.Order.CustomerId}", ct);
        return Result.Success();   // both writes commit together; a failure/error result rolls both back
    }
}

• Atomicity by result. The decorator commits on a successful Result and rolls back otherwise — the two writes above are one transaction. Reads on the session inside the scope observe its own uncommitted writes (they share the connection); an outside connection sees nothing until commit.
• Nested handlers join, never nest physically. A transactional command that invokes another through IHandlerSender on the same scope joins the ambient transaction via a savepoint (commit releases it, rollback discards only the inner writes) — PostgreSQL forbids a second physical transaction on one connection.
• Lock timeout. UnitOfWorkOptions.LockTimeout applies SET LOCAL lock_timeout on PostgreSQL (how [Idempotent] fast-fails a concurrent duplicate to a 409 instead of blocking); other ADO.NET providers ignore it, the closest semantics they can offer.
• No change tracker. Unlike EF there is no pending-change buffer to flush — the handler's statements have already executed on the connection inside the transaction, so commit only commits.
• Without it. With neither AddElarionSqlUnitOfWork() nor AddElarionSqlSession() registered, a command handler that injects ISqlSession fails to resolve at activation; and if only the session is registered, the framework's IUnitOfWork stays the core no-op (commit/rollback do nothing, logged once) so a failed multi-write command is not rolled back. Register AddElarionSqlUnitOfWork() on any EF-free host whose handlers write.

A write cannot silently escape its transaction. Every write on this surface enlists the session's transaction automatically (InsertManyAsync without one opens its own — a batch is atomic on its own), and there is no raw-connection twin of these methods to reach for by accident: the only way to run against your own connection is AsSqlSession(transaction?), which makes the transaction decision explicit at wrap time. What used to be a documented footgun — a record-level write on a raw connection running outside the handler's unit of work — is now unrepresentable.

Why not Dapper.AOT

Dapper.AOT is actively maintained and its emitted code is excellent. The difference is architectural, not maturity: Dapper.AOT works by call-site interception of the reflection-based Dapper API. Only direct, inline Dapper calls are intercepted — a call the interceptor cannot statically see (your own helper wrapping Query<T>, a generic utility) compiles cleanly and silently executes classic reflection Dapper, which under NativeAOT is a runtime failure. The one guarantee an AOT-first framework must give — "if it builds, it maps" — is exactly the one interception cannot make.

Elarion.Sql inverts the consumption model: the mapper is an explicit generated contract, not a recognized call pattern. Indirection is free (the mapper is a value), private/internal types work, and the failure mode for an unmapped type is a compile error. What Dapper.AOT does better remains true and documented: it accepts arbitrary result shapes per query (Elarion maps declared [SqlRecord] types), and it drops into an existing Dapper codebase unchanged. The benchmark suite keeps an honest comparison column for both classic Dapper and Dapper.AOT.

Performance

The benchmark gate (tests/Elarion.Benchmarks, --filter "*SqlMapping*", real PostgreSQL via Testcontainers — the ADR-0051 discipline): the generated read path must sit at parity with a hand-written ADO.NET reader in time and allocations, with classic Dapper, Dapper.AOT, and EF Core (no-tracking) as comparison columns, on both the many-rows path (1k/100k rows → List<T>) and the single-row path (per-call overhead: statement build, parameter binding, command setup).

The generated mapper emits the same code you would write by hand — an ordinal struct and typed GetFieldValue<T> calls — so parity is by construction, and the benchmark exists to keep it that way. Representative numbers (Apple M4 Pro, .NET 10, PostgreSQL 17 in a container; 2026-07):

Observability

Elarion.Sql deliberately ships no telemetry of its own: a mapper at hand-written parity has nothing worth measuring, and a per-query wrapper span would only duplicate the provider's. The story is the two layers around it — and every EF Core observability feature has a direct equivalent:

That last row is the part EF does not give you by default: because data access runs inside handlers, every command span nests under an Elarion.Handlers span — the dashboard reads HTTP → handle IngestReadings → INSERT INTO readings …, so a slow query is attributed to its use case without any extra wiring. The samples/EdgeTelemetry host wires all of it (OTLP-gated) and its e2e test asserts the span nesting.

Non-goals

No change tracking, no LINQ or query translation, no relationship/graph mapping, no query generation — SQL stays hand-written; the generated constants (columns and the clause-free Insert/Select statements) remove the boilerplate, not the SQL: nothing generated ever contains a predicate. No query-builder DSL (rejected as LINQ-to-SQL-by-another-name, ADR-0058). The planned follow-up is the schema-derived metamodel and drift verification: a design-time tool introspects a real PostgreSQL brought to schema by any means and writes a committed elarion-sql-schema.json; the generator then validates every [SqlRecord] against the actual schema at build time.