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. Query a DbDataSource (a pooled connection per call) or a DbConnection; interpolated values become parameters, and Order.Select splices the generated SELECT-list with no :raw:

var orders = await db.QueryAsync<Order>(
    $"{Order.Select} WHERE status = {status} AND customer_name IN {names}", ct);

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

Write with the same self-mapping — 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 extensions live on both DbDataSource (opens/disposes a pooled connection per call) and DbConnection (Dapper open/close semantics):

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 — is a considered future addition (see ADR-0058).

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 DbConnection extensions (QueryAsync, QueryFirstOrDefaultAsync, ExecuteAsync, ExecuteScalarAsync) pair statements with mappers and open/close closed connections around the call (Dapper semantics). They save the command/reader ceremony — nothing more.

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.