CodeLogic.MySQL2 4.9.98

This package has a SemVer 2.0.0 package version: 4.9.98+32aff20.

dotnet add package CodeLogic.MySQL2 --version 4.9.98

NuGet\Install-Package CodeLogic.MySQL2 -Version 4.9.98

This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.

<PackageReference Include="CodeLogic.MySQL2" Version="4.9.98" />

For projects that support PackageReference, copy this XML node into the project file to reference the package.

<PackageVersion Include="CodeLogic.MySQL2" Version="4.9.98" />
                    

                            Directory.Packages.props

<PackageReference Include="CodeLogic.MySQL2" />
                    

                            Project file

For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.

paket add CodeLogic.MySQL2 --version 4.9.98

The NuGet Team does not provide support for this client. Please contact its maintainers for support.

#r "nuget: CodeLogic.MySQL2, 4.9.98"

#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.

#:package CodeLogic.MySQL2@4.9.98

#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package.

#addin nuget:?package=CodeLogic.MySQL2&version=4.9.98
                    

                            Install as a Cake Addin

#tool nuget:?package=CodeLogic.MySQL2&version=4.9.98
                    

                            Install as a Cake Tool

The NuGet Team does not provide support for this client. Please contact its maintainers for support.

CodeLogic.MySQL2

A typed MySQL data layer for CodeLogic 4: repositories, LINQ-shaped SQL, cursor paging, schema synchronization, migrations, caching, resilience, and operational diagnostics in one library.

CodeLogic.MySQL2 sits between a micro-ORM and a lightweight application data platform. Map ordinary C# classes with attributes, then use repositories or a fluent query builder while the library handles parameterized SQL, compiled row materialization, schema drift, cache invalidation, retries, health checks, and events.

It is built on MySqlConnector and supports MySQL, MariaDB, and Percona. Fallible operations return CodeLogic Result<T> values so expected failures can be handled without exception-driven control flow.

What the library covers

Area	Capabilities
Entity persistence	CRUD repositories, batch insert, upsert, batch upsert, insert-or-increment, atomic counters, soft and hard delete.
Querying	Typed filters, string and collection predicates, ordering, offset paging, cursor paging, subqueries, typed and raw joins, projections, grouping, aggregates, and set-based writes.
Schema management	Attribute-driven tables and columns, type inference, keys, foreign keys, indexes, covering indexes, column renames, schema diffing, and three synchronization modes.
Migrations	Versioned up/down migrations, discovery, pending plans, checksums, rollback preflight, migration tracking, and a cross-node schema lock.
Data lifecycle	Soft-delete filtering, retention-based background purging, schema backup, and schema restore.
Performance	Compiled materializers, projection pushdown, batched writes, result caching, single-flight misses, warm smart-cache pools, and time-quantized cache keys.
Reliability	Connection pooling, named databases, explicit transactions, deadlock/lock-timeout retry, command cancellation, and health checks.
Operations	Query timing, slow-query detection, cache statistics, pool statistics, and CodeLogic events.
Escape hatches	Parameterized raw queries, commands, scalar reads, direct connection access, and pluggable cache/coordinator interfaces.

Install

dotnet add package CodeLogic.MySQL2

Quick start

using CL.MySQL2;
using CL.MySQL2.Models;
using CL.MySQL2.Services;
using CodeLogic;
using CodeLogic.Core.Results;

[Table(Name = "users")]
public class User
{
    [Column(Name = "id", DataType = DataType.BigInt, Primary = true, AutoIncrement = true)]
    public long Id { get; set; }

    [Column(Name = "email", DataType = DataType.VarChar, Size = 160, NotNull = true, Unique = true, Index = true)]
    public string Email { get; set; } = "";

    [Column(Name = "created_utc", DataType = DataType.DateTime, NotNull = true)]
    public DateTime CreatedUtc { get; set; } = DateTime.UtcNow;
}

await Libraries.LoadAsync<MySQL2Library>();
await CodeLogic.ConfigureAsync();
await CodeLogic.StartAsync();

var mysql = Libraries.Get<MySQL2Library>()!;

// Reconcile the table with the mapped model.
Result<SyncResult> sync = await mysql.SyncTableAsync<User>();

// Repository persistence.
Repository<User> users = mysql.GetRepository<User>();
Result<User> inserted = await users.InsertAsync(
    new User { Email = "ada@example.com" });

// Typed query translated and executed in MySQL.
Result<List<User>> recent = await mysql.Query<User>()
    .Where(u => u.CreatedUtc >= DateTime.UtcNow.AddDays(-7))
    .OrderByDescending(u => u.CreatedUtc)
    .Take(20)
    .WithCache(TimeSpan.FromMinutes(1))
    .ToListAsync();

Configuration files are generated on first run. Add the connection details to config.mysql.json, restart, and the named connection is ready for repositories, queries, schema sync, and migrations.

Entity mapping and schema

The model is the schema source of truth. CL.MySQL2.Models provides attributes for the table shape and lifecycle:

[Table] controls the table name, engine, charset, collation, and comment.
[Column] controls the physical name, type, length, precision, scale, primary/auto-increment flags, nullability, uniqueness, indexing, defaults, unsigned values, charset, comments, and binary storage.
[ForeignKey], [Index], and [CompositeIndex] describe constraints and single, composite, unique, or covering indexes.
[Ignore] excludes a property from persistence.
[SoftDelete] marks a nullable timestamp used for automatic read filtering and repository soft deletes.
[RetainDays] enables scheduled batch deletion of expired rows (first pass 5 minutes after start, then every 24 hours).
PreviousName on [Column] performs an in-place column rename so existing data is preserved.

Properties without [Column] use CLR type inference and their property name as the column name. When [Column] is used for explicit mapping, its DataType selects from the MySQL integer, decimal, floating point, bit, character, text, binary/blob, date/time, enum/set, JSON, and geometry families. StorageType.Binary can store a Guid as BINARY(16); SequentialGuid.NewId() creates time-ordered UUIDv7 values suitable for indexed primary keys.

Schema synchronization modes

Mode	Intended environment	Behavior
`Production`	Normal production operation	Additive reconciliation; never drops. Destructive drift is reported through `DriftPending`.
`Developer`	Local and disposable environments	Fully reconciles the model, including removed columns, indexes, and foreign keys.
`Migration`	Deliberate production maintenance	Performs a backed-up, one-shot destructive reconciliation, then becomes a no-op once current.

Result<Dictionary<string, SyncResult>> schema = await mysql.SyncSchemaAsync(
    typeof(User), typeof(Order), typeof(Customer));

mysql.SetSyncMode(SyncMode.Production, connectionId: "Default");

Indexes are matched by name. If an index keeps its name but its definition changes (columns, their order, or uniqueness), sync logs a drift warning. Production leaves the existing index as it is and reports DriftPending; Developer and Migration rebuild it.

Every desired schema is hashed into __schema_state. An unchanged model takes the CRC fast path and skips information_schema inspection and DDL entirely. SyncResult reports operations, errors, duration, CRC, whether work was skipped, and whether destructive drift remains pending.

Repository API

mysql.GetRepository<T>(connectionId) provides the conventional persistence surface:

Operation	Purpose
`InsertAsync` / `InsertManyAsync`	Insert one row with its generated key populated, or insert chunked batches and return the affected count.
`InsertIgnoreAsync` / `InsertManyIgnoreAsync`	Insert one row or batches while ignoring duplicate-key conflicts; returns `InsertOutcome` (`Inserted` with generated Id populated, or `Duplicate`) or the inserted count. Duplicates are a normal outcome, not an error. Any other warning (truncation, missing NOT NULL value) fails the call and rolls back everything it wrote.
`UpsertAsync` / `UpsertManyAsync`	Insert or update on duplicate key.
`UpsertWithIncrementsAsync`	Insert a seed or atomically accumulate selected numeric columns.
`GetByIdAsync` / `GetByColumnAsync` / `GetAllAsync` / `FindAsync`	Typed entity retrieval.
`GetPagedAsync`	Page-number/offset paging with totals.
`CountAsync`	Count table rows. Applies the `[SoftDelete]` filter like the other reads, so it agrees with `GetAllAsync`.
`UpdateAsync`	Update an entity by its mapped primary key.
`IncrementAsync` / `DecrementAsync` / `AdjustAsync`	Atomic server-side counter changes.
`DeleteAsync`	Soft delete when `[SoftDelete]` is present; otherwise physically delete.
`HardDeleteAsync`	Always physically delete.

Idempotent writes (for example re-sent event batches keyed by a unique client id) should use InsertIgnoreAsync / InsertManyIgnoreAsync. Callers that keep InsertAsync can set DistinguishDuplicateKeyError (database config or Repository<T> property) to get the error code mysql.duplicate_key, logged as a Warning, instead of the generic mysql.insert_failed Error for MySQL error 1062. It is off by default, so existing behavior is unchanged.

Query builder

mysql.Query<T>() translates supported expression trees into parameterized SQL. Filtering and materialization remain server-side; the library does not load rows and apply LINQ in memory.

string[] countries = ["DK", "SE", "NO"];

Result<List<Order>> orders = await mysql.Query<Order>()
    .Where(o => o.Status == "open")
    .Where(o => o.Total >= 100 && countries.Contains(o.Country))
    .Where(o => o.Reference.StartsWith("WEB-"))
    .OrderByDescending(o => o.CreatedUtc)
    .ToListAsync();

Supported filters include comparisons, boolean composition, negation, null checks, captured values, string Contains/StartsWith/EndsWith, and collection Contains translated to IN (...).

Subqueries

var shipped = await mysql.Query<Order>()
    .WhereExists<Shipment>((o, s) => s.OrderId == o.Id && s.Status == "sent")
    .ToListAsync();

var vipOrders = await mysql.Query<Order>()
    .WhereIn<Customer, long>(o => o.CustomerId, c => c.Id, c => c.IsVip)
    .ToListAsync();

WhereExists, WhereNotExists, WhereIn, and WhereNotIn generate SQL subqueries and compose with normal filters.

Ordering and pagination

Offset paging returns totals and page-number metadata:

Result<PagedResult<Order>> page = await mysql.Query<Order>()
    .Where(o => o.Status == "open")
    .OrderByDescending(o => o.CreatedUtc)
    .ToPagedListAsync(page: 1, pageSize: 25);

Cursor paging performs a keyset seek without OFFSET or COUNT(*), making it the better fit for deep or frequently changing result sets:

Result<CursorPagedResult<Order>> first = await mysql.Query<Order>()
    .Where(o => o.Status == "open")
    .OrderByDescending(o => o.CreatedUtc)
    .ToCursorPagedListAsync(pageSize: 25);

Result<CursorPagedResult<Order>> next = await mysql.Query<Order>()
    .Where(o => o.Status == "open")
    .OrderByDescending(o => o.CreatedUtc)
    .After(first.Value!.NextCursor)
    .ToCursorPagedListAsync(pageSize: 25);

Cursor ordering supports multiple ASC/DESC and nullable columns. A mapped primary key is appended automatically as a stable tie-breaker. Continuation tokens are versioned Base64URL JSON bound to the entity/table and exact ordering; they are opaque paging state, but they are not signed or encrypted. Encoded tokens longer than 4,096 characters are rejected before decoding.

Joins, projections, grouping, and aggregates

Typed joins support inner, left, and right equi-joins, composite keys, two-entity filters and ordering, and compiled projection into a DTO:

Result<List<OrderView>> views = await mysql.Query<Order>()
    .Where(o => o.Total > 100)
    .Join<Customer, long, OrderView>(
        o => o.CustomerId,
        c => c.Id,
        (o, c) => new OrderView { OrderId = o.Id, Customer = c.Name, Total = o.Total },
        JoinType.Left)
    .Where((o, c) => c.IsVip)
    .OrderByDescending((o, c) => o.Total)
    .ToListAsync();

Projection pushdown selects only referenced columns, while grouped projections translate aggregate operations to SQL:

Result<List<DailyTotal>> totals = await mysql.Query<Order>()
    .Where(o => o.CreatedUtc >= DateTime.UtcNow.AddDays(-30))
    .GroupBy(o => o.Day)
    .Select(g => new DailyTotal
    {
        Day = g.Key,
        Count = g.Count(),
        Revenue = g.Sum(o => o.Total),
        Average = g.Average(o => o.Total)
    })
    .ToListAsync();

Single-value terminals include CountAsync, MinAsync, MaxAsync, SumAsync, and AverageAsync. Aggregates (MaxAsync, MinAsync, SumAsync) accept nullable selectors such as r => (DateTime?)r.CreatedUtc or r => (int?)r.Score, returning null on an empty set while non-nullable selectors preserve their standard defaults (DateTime.MinValue, 0). Select(...) also supports anonymous or DTO projections without first materializing the entity.

Set-based updates and deletes

Result<int> updated = await mysql.Query<Order>()
    .Where(o => o.Status == "draft")
    .UpdateAsync(o => new Order
    {
        Status = "open",
        UpdatedUtc = DateTime.UtcNow
    });

Result<int> deleted = await mysql.Query<Order>()
    .Where(o => o.CreatedUtc < DateTime.UtcNow.AddYears(-3))
    .DeleteAsync();

These operations issue one server-side statement and do not materialize matching rows.

Raw SQL, transactions, and multiple databases

Use the raw SQL APIs when a query is outside the typed builder's scope. Values remain parameterized and executions still flow through retries and observability:

Result<List<User>> rows = await mysql.SqlQueryAsync<User>(
    "SELECT * FROM users WHERE email LIKE @pattern",
    new Dictionary<string, object?> { ["@pattern"] = "%@example.com" });

Result<int> affected = await mysql.ExecuteSqlAsync(
    "UPDATE users SET verified = 1 WHERE id = @id",
    new Dictionary<string, object?> { ["@id"] = 42L });

Result<long> count = await mysql.SqlScalarAsync<long>("SELECT COUNT(*) FROM users");

BeginTransactionAsync returns an async-disposable transaction that rolls back automatically unless committed. Pass the scope to GetRepository<T> or Query<T> to enlist that work in it:

await using TransactionScope tx = await mysql.BeginTransactionAsync();
var accounts = mysql.GetRepository<Account>(tx);

await accounts.AdjustAsync(1L, a => a.Balance, -100m);
await accounts.AdjustAsync(2L, a => a.Balance, 100m);
await mysql.Query<Audit>(tx).Where(a => a.Stale).DeleteAsync();
await tx.CommitAsync();

Configure multiple named database connections and select one through GetRepository<T>(connectionId), Query<T>(connectionId), the raw SQL connectionId argument, or .WithConnection(connectionId).

Migrations, backups, and data lifecycle

Declarative sync handles structural model drift. Imperative IMigration implementations handle seeds, backfills, data transforms, and semantic changes that a schema diff cannot infer.

public sealed class SeedRoles() : Migration("1.4.0", 1, "Seed default roles")
{
    public override Task UpAsync(IMigrationContext context, CancellationToken ct) =>
        context.ExecuteAsync(
            "INSERT INTO roles (name) VALUES ('admin'), ('user')", ct: ct);

    public override Task DownAsync(IMigrationContext context, CancellationToken ct) =>
        context.ExecuteAsync(
            "DELETE FROM roles WHERE name IN ('admin', 'user')", ct: ct);
}

mysql.RegisterMigration(new SeedRoles())
     .RegisterMigrationsFrom(typeof(Program).Assembly);

IReadOnlyList<MigrationPlanItem> pending = await mysql.GetPendingMigrationsAsync();
Result<MigrationRunResult> applied = await mysql.MigrateAsync();

Migrations run in version/order sequence, are tracked in __migrations, verify checksums, and execute under the same cross-node lock as schema sync. RollbackAsync(target) preflights the complete range before running DownAsync newest-first.

Before destructive schema reconciliation, the backup manager writes DDL snapshots. RestoreSchemaAsync can replay the latest or a named snapshot and then clears the CRC state so the next sync performs a full comparison. These are schema backups only; they do not preserve table rows.

For row lifecycle management, [SoftDelete] changes repository deletion into a timestamp update and filters ordinary reads by default. .IncludeDeleted() opts a query back into those rows. [RetainDays] registers an entity for background batch purging based on its timestamp column; the worker's entry list is live, so an entity reconciled by SyncTableAsync / SyncSchemaAsync after CodeLogic.StartAsync() is still picked up. RunRetentionOnceAsync() triggers a pass on demand.

Caching and performance

Cache-aside queries

Result<List<User>> cached = await mysql.Query<User>()
    .Where(u => u.CreatedUtc >= DateTime.UtcNow.AddDays(-30))
    .WithCache(TimeSpan.FromMinutes(5))
    .ToListAsync();

Cache keys include the connection, SQL, and parameters.
Table version stamps invalidate cached results after repository or query-builder mutations.
Concurrent misses for one key collapse into a single database execution.
DateTime parameters within 365 days of now are quantized to a TimeQuantizeSeconds bucket (60s by default) so rolling-window queries reuse cache entries.
ICacheStore and ICacheCoordinator provide seams for shared stores, cross-node invalidation, and refresh leases.

Smart cache pools

SmartCachePool dashboard = mysql.RegisterCachePool(
    name: "dashboard",
    refreshEvery: TimeSpan.FromSeconds(30),
    maxIdleFires: 10);

Result<List<User>> warm = await mysql.Query<User>()
    .Where(u => u.CreatedUtc >= DateTime.UtcNow.AddDays(-1))
    .SmartCache("dashboard")
    .ToListAsync();

await mysql.RefreshCachePoolAsync("dashboard");
QueryCacheStats cacheStats = mysql.GetCacheStats();
IReadOnlyList<SmartCachePoolStats> poolStats = mysql.GetCachePoolStats();

Smart pools refresh registered queries in the background, retire idle entries, and optionally coordinate a single refresh owner across nodes. Compiled materializers, projection pushdown, chunked insert/upsert operations, and connection pooling apply independently of result caching.

Reliability and observability

Deadlocks (1213) and lock-wait timeouts (1205) on individual non-transactional statements are retried with exponential backoff and jitter.
TestConnectionAsync and the CodeLogic library health check expose connection health.
SlowQueryEvent reports the SQL and elapsed milliseconds when the configured threshold is exceeded. With CaptureExplainOnSlowQuery on (default off), it also carries the EXPLAIN FORMAT=JSON plan in ExplainJson, captured best-effort on a separate connection.
QueryExecutedEvent reports SQL, duration, row count, connection, and cache-hit status.
CacheHitEvent, CacheMissEvent, DatabaseConnectedEvent, DatabaseDisconnectedEvent, TableSyncedEvent, and HealthChangedEvent integrate with the CodeLogic event bus. N1QueryDetectedEvent fires when one query template repeats N1DetectorThreshold times within a second (0, the default, disables detection).
GetCacheStats() and GetCachePoolStats() expose cache entries, versions, refreshes, failures, and activity.

Important behavior boundaries

Cursor pagination is forward-only and applies to plain entity queries, not joined, projected, or grouped result shapes.
Continuation tokens are encoded and validated but are not signed, encrypted, or bound to filter values.
Typed joins and subquery-filtered queries are not result-cacheable because their dependencies span tables.
Explicit transactions disable result caching, smart caching, and per-statement transient retry; retry the complete transaction at the application boundary.
Repository.DeleteAsync honors [SoftDelete]; query-builder DeleteAsync is always a hard, set-based delete.
Query-builder bulk updates/deletes intentionally bypass the soft-delete read filter so deleted rows can be restored or purged.
Schema backups contain DDL only. Use a database backup strategy for row-level recovery.

Configuration

The library generates config.mysql.json (mysql) and config.mysql.cache.json (mysql.cache). A minimal database entry looks like this:

{
  "Databases": {
    "Default": {
      "Enabled": true,
      "Host": "localhost",
      "Port": 3306,
      "Database": "myapp",
      "Username": "app",
      "Password": "",
      "SyncMode": "Production",
      "MaxPoolSize": 100,
      "SlowQueryThresholdMs": 1000,
      "TransientRetryCount": 3,
      "DistinguishDuplicateKeyError": false
    }
  }
}

Applied database settings include endpoint and credentials, pooling, connection and command timeouts, SSL mode and CA path, connection charset, sync mode, the transient retry policy, the slow-query threshold and its optional EXPLAIN capture, the query timeout, the insert batch size, the backup directory, the N+1 detector threshold, the default string size, and the per-database cache override. Two fields are [Obsolete] and ignored: PreparedStatementCacheSize (configure statement caching on the connection string) and the cache section's MaxMemoryMb (the store evicts by entry count — use MaxEntries). MaxInClauseValues is advisory: an oversized generated IN (...) list warns rather than chunking. Collation remains informational. See the overview for the per-field status.

The cache configuration's global switch, entry limit, DateTime quantization window, default TTL (used by the parameterless .WithCache()), and hit/miss event switch are all applied at startup.

Changing config while running

Both sections apply live — no restart — when changed through Libraries.UpdateConfigAsync, Libraries.ReloadConfigAsync, or the CodeLogic config file watcher (CodeLogic 4.9+). Only the databases that changed are touched:

Change to a database	What happens
Connection settings (host, credentials, pooling, timeouts, SSL, charset)	Its idle pool is cleared, the connection is tested, and its schema-state tables are ensured. In-flight queries finish on their old connections.
Other settings (slow-query threshold, batch size, cache override, N+1 threshold, …)	Updated in place; no reconnect.
Added / enabled	Connects.
Removed / disabled	Its pool is cleared and the ID is unregistered.
Untouched	Nothing — its connections stay open.

If a changed database cannot be reached, the change stays saved, the other databases are still applied, and the result reports Applied = false (the library shows Degraded until a later change connects). A sync mode set at runtime with SetSyncMode survives unrelated changes. Enabling the first database when the library started with none still requires a restart.

Main entry points

Member	Purpose
`GetRepository<T>(connectionId)`	Create a CRUD repository.
`Query<T>(connectionId)`	Start a typed entity query.
`SqlQueryAsync<T>` / `ExecuteSqlAsync` / `SqlScalarAsync<T>`	Execute parameterized raw SQL.
`BeginTransactionAsync`	Start an explicit transaction scope.
`SyncTableAsync<T>` / `SyncSchemaAsync`	Reconcile mapped schemas.
`RegisterMigration` / `MigrateAsync` / `RollbackAsync`	Manage imperative migrations.
`RestoreSchemaAsync`	Restore a DDL snapshot.
`RegisterCachePool` / `RefreshCachePoolAsync`	Manage warm query pools.
`GetCacheStats` / `GetCachePoolStats`	Inspect cache behavior.
`TestConnectionAsync`	Verify a named database connection.

Documentation

Requirements

.NET 10
CodeLogic 4.9+
MySqlConnector 2.x
MySQL 5.7+, MariaDB 10.3+, or a compatible Percona release

License

MIT — see LICENSE.

Product	Compatible and additional computed target framework versions.
.NET	net10.0 is compatible. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed.

Compatible target framework(s)

Included target framework(s) (in package)

Learn more about Target Frameworks and .NET Standard.

net10.0
- CodeLogic (>= 4.9.0 && < 5.0.0)
- MySqlConnector (>= 2.6.2)

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version	Downloads	Last Updated
4.9.98	0	10/25/2026
4.8.97	32	10/23/2026
4.8.93	60	9/23/2026
4.8.88	61	9/13/2026
4.8.87	61	9/13/2026
4.8.85	59	9/12/2026
4.6.81	79	8/7/2026
4.6.80	69	8/7/2026
4.6.72	206	6/20/2026
4.6.70-preview	65	6/20/2026
4.6.69-preview	71	6/20/2026
4.5.4	165	6/5/2026
4.5.4-preview.59	104	5/24/2026
4.5.3	137	6/5/2026
4.5.3-preview.58	78	5/24/2026
4.5.2	142	5/24/2026
4.5.2-preview.68	95	6/20/2026
4.5.2-preview.57	95	5/24/2026
4.5.1	202	5/24/2026
4.5.1-preview.60	85	5/24/2026

# CL.MySQL2 — Changelog

All notable changes to **CodeLogic.MySQL2** are documented here. Versions follow
[Semantic Versioning](https://semver.org/). The version listed here matches the
NuGet package version of `CodeLogic.MySQL2`.

## 2026-10-24

### Added

- **Live config changes per database** (requires CodeLogic 4.9+). `MySQL2Library` implements
`IConfigReloadable`: a change to `config.mysql.json` made through `Libraries.UpdateConfigAsync`,
`Libraries.ReloadConfigAsync` or the config file watcher is applied without a restart and touches
only the databases that changed. Changed connection settings clear that database's idle pool,
re-test it and ensure its schema-state tables; other settings update in place; added/removed
databases connect/disconnect; untouched databases keep their connections. `mysql.cache` changes
apply immediately. An unreachable database leaves the change saved, still applies the rest, and
is reported as not applied. A runtime `SetSyncMode` survives unrelated changes.
- `ConnectionManager.ReplaceConfigurationAsync` and `ConnectionManager.UnregisterConfigurationAsync`
swap or remove one connection's configuration, clearing its idle pool when the connection string
changes.

### Changed — behaviour

- **Index drift is checked for every model index and rebuilt where drops are allowed** (aligned
with CL.MSSQL and CL.PostgreSQL). The drift check now covers `[Index]`, `Column.Index` and
`Column.Unique` as well as `[CompositeIndex]`, and compares uniqueness as well as the columns
and their order. The Warning is logged at every level. `Production` (Safe) still never touches
the index, but the table is now flagged `DriftPending`; `Developer` and `Migration` rebuild it
with one atomic `ALTER TABLE … DROP INDEX x, ADD … x (…)`. Previously a drifted index was never
rebuilt at any level.
- An index on a column renamed with `PreviousName` in the same sync is no longer reported as drift.

## 2026-10-23

### Added

- **`Repository<T>.InsertIgnoreAsync` and `InsertManyIgnoreAsync`**:
- `Task<Result<InsertOutcome>> InsertIgnoreAsync(T entity, CancellationToken ct = default)` inserts a single entity while treating duplicate-key conflicts (UNIQUE index or PRIMARY KEY) as a normal outcome. Returns `InsertOutcome.Inserted` (with auto-generated PK populated on the entity) or `InsertOutcome.Duplicate`, without raising an exception and without logging at Error level (Debug at most).
- `Task<Result<int>> InsertManyIgnoreAsync(IEnumerable<T> entities, CancellationToken ct = default)` bulk-inserts entities in batches, returning the count of rows actually inserted (duplicates contribute 0).
- Built with `INSERT IGNORE` and connection-level warning validation: under MySqlConnector's default found-rows semantics (`UseAffectedRows=false`), `INSERT IGNORE` accurately distinguishes inserts (1) from duplicates (0), while connection warnings are intercepted so that non-duplicate errors (such as data truncation or NOT NULL constraint violations) are not silently swallowed.
- **Atomic on failure.** `INSERT IGNORE` still writes a row when it downgrades such an error to a warning, so both methods run in a transaction and roll back everything the call wrote when any warning other than 1062 appears or anything throws: a failure `Result` always means nothing was written. Inside an explicit `TransactionScope` the call rolls back to a savepoint instead, leaving the caller's other work in that transaction intact.
- **Opt-in `mysql.duplicate_key` error code for `InsertAsync` and `InsertManyAsync`**:
- Configurable via `MySqlDatabaseConfig.DistinguishDuplicateKeyError` in connection configuration, `Repository<T>.DistinguishDuplicateKeyError` property, or constructor overload.
- When enabled, MySQL error 1062 (`ER_DUP_ENTRY`) returns the distinct error code `mysql.duplicate_key` and logs at Warning instead of Error.
- Defaults to `false`: keeping unchanged existing behavior (`mysql.insert_failed` / `mysql.bulk_insert_failed` with Error logging).
- **CompositeIndex column drift detection**:
- `SchemaAnalyzer` loads ordered column lists (`SEQ_IN_INDEX`) for existing indexes from `information_schema.STATISTICS`.
- When an index declared via `[CompositeIndex]` exists in the database but its columns or column ordering differ from the model, a Warning is logged naming the table, index name, existing columns, and model columns.
- Safe level (`SchemaSyncLevel.Safe`) never drops or rebuilds drifted indexes, maintaining backwards compatibility.
- **Nullable result support for QueryBuilder aggregates (`MaxAsync`, `MinAsync`, `SumAsync`)**:
- Aggregates now support nullable types (e.g. `MaxAsync(p => (DateTime?)p.CreatedAt)` or `SumAsync(p => (int?)p.Amount)`).
- Unwraps `Nullable.GetUnderlyingType(typeof(TResult))` prior to type conversion so that SQL NULL on an empty set or null column returns `null` instead of throwing `InvalidCastException`.
- `MySqlExpressionVisitor.TranslateSelector` unwraps unary `Convert` expressions to support cast-to-nullable lambda selectors.
- Non-nullable aggregate behavior remains completely unchanged (empty set returns `default(TResult)` such as `DateTime.MinValue` or `0`).

## 2026-09-13

### Behaviour changes (read before upgrading)

- **`Repository<T>.CountAsync()` now applies the `[SoftDelete]` filter.** It used to emit a
bare `SELECT COUNT(*)`, contradicting `GetAllAsync` / `GetPagedAsync` on the same entity --
the paged read already filtered its own count. For a soft-delete entity the returned count
will now be *lower* than before by the number of deleted rows. If you relied on the old
total, use `mysql.Query<T>().IncludeDeleted().CountAsync()`.
- **`CaptureExplainOnSlowQuery` now defaults to `false`.** The flag was declared `true` but
never read, so nothing ran. Now that it is wired, keeping the old default would have turned
`EXPLAIN` capture on for everyone on upgrade. A `config.mysql.json` written by an earlier
version still carries `true` and will therefore capture plans -- set it to `false` if you
do not want that.
- **Configuration fields that were declared and ignored are now read.** `QueryTimeoutMs`,
`MaxBatchInsertSize`, `BackupDirectory`, `CacheEnabledOverride`, `SslCertificatePath`,
`DefaultStringSize`, `N1DetectorThreshold`, `DefaultTtlSeconds` and `PublishEvents` all take
effect now. Every one of them keeps today's behaviour at its default value, but a
non-default value you set previously (and which did nothing) will now change behaviour.

### Fixed

- **Retention purged every entity against the `Default` connection.** The registered-entity
set recorded types with no connection association, so an entity synced against a named
connection was purged from the wrong database — in practice the `DELETE` hit a database
where the table did not exist, the failure was caught and logged, and the retention the
`[RetainDays]` attribute described silently never happened. Registrations now carry the
connection they were made against, and the same entity synced to two connections is two
registrations. Only reachable since the worker began running at all in this same release.
`RetentionWorker.TryRegister(Type, string)` and a `Registrations` view are added; the existing
type-only overload keeps its meaning and registers against the worker's own connection.
- **`ids.Contains(x.Id)` on a `List<T>` or `HashSet<T>` threw instead of emitting `IN`.**
The expression visitor's first `Contains` case matched any single-argument instance call,
so a collection membership test took the string `LIKE` branch and tried to emit the
collection itself as a column. Arrays were unaffected because they bind to the static
two-argument `Enumerable.Contains`, which had its own case. The `LIKE` branch is now
restricted to a string receiver, and both membership shapes share one emitter.
- **`GetRepository<T>` ignored `MaxBatchInsertSize`.** Both overloads (connection id and
`TransactionScope`) passed the slow-query threshold but not the batch size, so
`InsertManyAsync` / `UpsertManyAsync` always chunked at the constructor default of 500.
- **`ProjectedQuery` and `JoinedQuery` dropped the terminal's `CancellationToken`** when
opening the connection -- a cancelled token could still run the query to completion, and
any transient-failure retry around the open ignored cancellation entirely. Both now
forward it.
- **A subquery-filtered query could be cached through `.Select(...)`.** `QueryBuilder.Select`
copied the cache TTL / smart-cache pool into the projection without consulting the
subquery-filter guard that `ShouldCache` applies, so
`WhereExists(...).Select(...).WithCache(...)` cached a cross-table result stamped with only
one table's version and served it stale after the other table changed. The guard now
travels with the projection, so a `.WithCache` applied after `.Select` is refused too.
- **`TypeConverter.ResolveColumn` ignored the configured default string size**, calling
`InferColumn(clrType)` without threading it through. Same root cause as `DefaultStringSize`
below; both are fixed together.

### Added

- **Retention actually runs.** `RetentionWorker` snapshotted its entry list at construction,
and the worker was constructed during library start -- before any documented flow calls
`SyncTableAsync` / `SyncSchemaAsync`. `HasWork` was therefore false and `[RetainDays]` was
dead in normal use. The entry list is now live: an entity registered at any time is picked
up (`RetentionWorker.TryRegister`), and the library starts the loop the first time a
`[RetainDays]` entity is registered. `Start()` remains idempotent, the 5-minute initial
delay and 24-hour interval are unchanged, disposal still cancels cleanly, and the list is
safe to mutate while the loop reads it.
- `MySQL2Library.RunRetentionOnceAsync()` -- an on-demand purge pass over every registered
`[RetainDays]` entity, without reaching for the worker directly.
- **N+1 detection.** `N1DetectorThreshold` is read and `QueryObservability.RecordN1` finally
has a caller: executions of the same normalized SQL template on one connection are counted
in a one-second rolling window and publish `N1QueryDetectedEvent` once per window when the
count crosses the threshold. Bookkeeping is bounded (a fixed number of templates, pruned by
age). `0` -- the default -- disables it with no allocation and no dictionary touch.
`QueryObservability.ConfigureN1Detection(connectionId, threshold)` sets it at runtime.
- **Slow-query `EXPLAIN` capture.** With `CaptureExplainOnSlowQuery` on, a slow query also
runs `EXPLAIN FORMAT=JSON` with the same parameters and attaches the plan to
`SlowQueryEvent.ExplainJson`. It runs on a separate pooled connection (never the caller's
transaction, never the caller's thread), skips statements MySQL cannot explain (DDL, and
multi-statement batches such as `INSERT ...; SELECT LAST_INSERT_ID();`), and swallows every
failure -- the event always publishes, with a null payload when no plan was obtained.
- `QueryBuilder<T>.WithCache()` and `ProjectedQuery<,>.WithCache()` -- parameterless overloads
using the cache configuration's `DefaultTtlSeconds` (60s default).
- `QueryCache.SetConnectionOverride(connectionId, enabled)` backing the per-database
`CacheEnabledOverride`, and an optional config lookup on `BackupManager` backing
`BackupDirectory`. `BackupManager.GetLatestBackupFile` and `CleanupOldBackupsAsync` take an
optional `connectionId` so they read the same directory the backup was written to.

### Changed

- `QueryTimeoutMs` is applied as `CommandTimeout` (rounded up to whole seconds) on the
commands the repository, query builder, projections, joins, retention and the raw-SQL
helpers create. `0` inherits the connection string's `CommandTimeout`; the 30000ms default
equals that 30-second default, so nothing changes until you change it.
- `SslCertificatePath` is written to the connection string as MySqlConnector's `SslCa` when
`EnableSsl` is true, raising the SSL mode to `VerifyCA`. With SSL off it is ignored. The
label and description now say "SSL CA Certificate Path": a single path field can only work
as a CA, since MySqlConnector's client-certificate option `SslCert` additionally requires
`SslKey`, for which this configuration has no field.
- `MaxInClauseValues` is advisory: a generated `IN (...)` list larger than it logs one warning
per query build naming the entity and the count. Nothing is chunked, thrown or rejected, so
no query that works today changes its result.
- `DefaultStringSize` is applied process-wide at initialization from the `Default` database
(or the first enabled one) -- DDL generation is static and not connection-scoped.
- `CacheConfiguration.PublishEvents` gates `CacheHitEvent` / `CacheMissEvent`; the default
`true` is today's behaviour.

### Deprecated

- `MySqlDatabaseConfig.PreparedStatementCacheSize` is `[Obsolete]` and ignored -- statement
caching is the provider's concern, configured on the MySqlConnector connection string
(`IgnorePrepare=false`).
- `CacheConfiguration.MaxMemoryMb` is `[Obsolete]` and ignored -- the in-process store bounds
the cache by entry count, not bytes. Use `MaxEntries`.

### Documentation

- The "not implemented" / "reserved" notes the previous audit pass added for exactly these
items are gone from the README, the overview, the performance page and the schema guide,
replaced by what the code now does.

## 2026-09-13 (documentation audit)

### Documentation

- The README's transaction example still built a `Repository<T>` by hand; it now uses the
`GetRepository<T>(tx)` / `Query<T>(tx)` accessors added in this release.
- Documented `SqlFn` and transaction-scoped queries in the queries guide.
- **Corrected: the raw SQL helpers do not join a transaction.** The queries guide showed
`ExecuteSqlAsync` calls inside an `await using TransactionScope` block as if they were part
of the transaction. They are not — `SqlQueryAsync` / `ExecuteSqlAsync` / `SqlScalarAsync`
take a `connectionId` and open their own pooled connection, so a rollback does not undo
them. The guide now says so and points at `GetRepository<T>(tx)` / `Query<T>(tx)` and
`IMigrationContext` instead.
- **Corrected: the attribute namespace.** The overview and schema guide told you to
`using CL.MySQL2.Attributes;`. No such namespace exists — `[Table]`, `[Column]`,
`[SoftDelete]` and friends live in `CL.MySQL2.Models`.
- **Corrected: configuration fields that do nothing.** `QueryTimeoutMs`,
`MaxBatchInsertSize`, `MaxInClauseValues`, `PreparedStatementCacheSize`,
`N1DetectorThreshold`, `CaptureExplainOnSlowQuery`, `BackupDirectory`,
`CacheEnabledOverride`, `DefaultStringSize`, `Collation`, `SslCertificatePath`,
`MaxMemoryMb`, `DefaultTtlSeconds` and `PublishEvents` were all documented as live knobs.
None of them is read by any code path today. The config tables and the XML comments now
mark each one, and state what actually governs the behaviour (for example insert chunking
is fixed at 500 rows, and generated `IN (...)` lists are uncapped).
- **Corrected: N+1 detection and `EXPLAIN` capture are not implemented.** `N1QueryDetectedEvent`
is never published and `SlowQueryEvent.ExplainJson` is always null; the performance page,
the events table and the README no longer promise either.
- **Corrected: `QueryCache.Enabled` and `QueryCache.TimeQuantizeSeconds` are internal.** The
performance page presented them as part of the public facade.
- **Corrected: soft delete and `CountAsync`.** `Repository.CountAsync()` issues a bare
`SELECT COUNT(*)` and therefore counts soft-deleted rows, unlike every other repository
read. The soft-delete documentation used to imply otherwise.
- **Corrected: the retention SQL and its registration window.** The `RetentionWorker` summary
claimed a server-side `NOW() - INTERVAL N DAY` cutoff; it actually binds a client-side
`DateTime.UtcNow.AddDays(-days)` as a parameter. The schema guide also now explains that the
worker is built at library start from the entities already passed to `SyncTableAsync` /
`SyncSchemaAsync`, and that the first pass runs 5 minutes after start, then daily.
- **Corrected: the upsert SQL.** `UpsertAsync` was documented as emitting
`INSERT ... AS new ON DUPLICATE KEY UPDATE` (MySQL 8.0.20+). It deliberately emits the
`VALUES(col)` form instead, so that it also works on MariaDB.
- **Corrected: `RegisterCachePool(maxIdleFires:)` defaults to 10, not 3** — an unread entry is
dropped after roughly five minutes at a 30-second refresh interval, not ninety seconds.
- **Corrected: the raw-string `.Join` example.** It qualified the left side as `t0`, but the
base table is not aliased on a raw join; `t0` / `t1` exist only inside a typed `Join<,,>`.
- **Corrected: `SqlScalarAsync<long>` returns `Result<long>`, not `Result<long?>`.** The
README and queries examples as written did not compile.
- **Corrected: schema backups.** They are always written to `DataDirectory/backups` as
`{table}_{yyyyMMdd_HHmmss}.sql`, and `RestoreSchemaAsync`'s `backupFile` is a file path, not
a bare name. The restore example used a filename in a format the library never produces.
- Documented the empty-collection `Contains` translation (`1 = 0`), the grouped
`g.Count(predicate)` / `g.Any(predicate)` overloads, and the 365-day window for `DateTime`
cache-key quantization.

### Fixed (configuration validation)

- `MySqlDatabaseConfig.Validate` checked only host, port, database and username. An
inverted pool range, a negative timeout or a zero batch size passed validation and then
failed later as a driver error at connection time rather than a configuration error at
startup. It now applies the same bounds `CL.MSSQL` and `CL.PostgreSQL` already did.

### Fixed

- **A migration registered twice ran twice.** `Register` and `RegisterFrom` both appended
unconditionally, so pairing `RegisterMigrationsFrom(assembly)` with an explicit
`RegisterMigration(...)` held two copies, and both passed the apply filter. Registration
now deduplicates by migration id.
- **`HealthChangedEvent` was declared but never raised.** It is now published on a health
state transition.

### Added

- `RetentionWorker.RunOnceAsync()` — the retention pass was only reachable from a
background loop that wakes once a day, so there was no way to trigger a purge on demand.

- `GetRepository<T>(TransactionScope)` and `Query<T>(TransactionScope)`.
`BeginTransactionAsync` returned a scope that neither accessor took, so callers had to
construct `Repository<T>` by hand to do any work inside a transaction.

### Security

- Added `MySqlDialect` with `Quote`, `QuoteMultipart` and `EscapeLike`, and routed all 113
identifier render sites through it, so a delimiter inside an identifier is escaped rather
than closing it. Output is byte-identical for safe identifiers, so generated DDL and the
schema CRC are unchanged.
- `EntityMetadata<T>` now rejects any mapped table or column name containing a backtick, NUL
or newline. Combined with the render-site quoting this makes identifier injection
structurally impossible rather than merely unlikely.
- Parameters for the dictionary overload of `QueryBuilder.UpdateAsync` are named by ordinal
instead of by the caller's key, so a key that is a valid column name but not a valid
parameter name can no longer corrupt the statement.

### Fixed

- **A `[Column]` attribute without an explicit `DataType` generated `TINYINT`.** Because
`DataType` is a non-nullable enum whose default was `TinyInt`, the
`colAttr?.DataType ?? Infer(...)` fallback could never fire when the attribute was
present. `DataType.Unspecified` is now the enum's default and such columns infer from the
CLR property type. An unattributed `Guid` likewise generated `CHAR(1)` instead of
`CHAR(36)`, because the inferred size was dropped along with the inferred type.
- `Contains()` over an empty collection emitted `IN ()`, which is a syntax error. It now
emits `1 = 0`.
- Both `UpdateAsync` overloads now reject database-generated (auto-increment) columns rather
than producing SQL the server refuses.
- Cancellation tokens are forwarded to connection acquisition, so opening a connection can
be cancelled.
- `ConnectionManager` held its configuration map in a non-concurrent `Dictionary` that could
be written by `RegisterConfiguration` while another thread read it.

### Changed

- Entity values are bound with an explicit `MySqlDbType` derived from the column's declared
or inferred type, via the new `TypeConverter.CreateParameter`, instead of `AddWithValue`.
An inferred type that differs from the column's own forces a server-side conversion and
can prevent the column's index from being used.

### Migration notes

- **Breaking:** `DataType` enum values shift by one to make room for `Unspecified = 0`. This
matters only if the numeric value was persisted somewhere; serialising by name is
unaffected.
- Entities with a `[Column]` attribute that omitted `DataType` will generate corrected DDL
and see one `ALTER` on the next schema sync.

## 2026-09-12

### Changed

- Unified the version line with the CodeLogic framework on **4.8.x**. Every official
library and the framework now share one `major.minor`, so a given `4.8.<patch>`
means the same generation across all packages.
- `version.txt` moved from `4.6` to `4.8`. The patch component remains the CI run
number, composed at pack time; `AssemblyVersion` stays pinned at `Major.Minor.0.0`
(now `4.8.0.0`) so every patch in the line loads interchangeably.

## 2026-08-07

### Added

- Forward-only keyset pagination on entity queries through `.After(cursor)` and
`ToCursorPagedListAsync(pageSize)`, returning `CursorPagedResult<T>`.
- Versioned Base64URL continuation tokens, stable primary-key tie-breaking,
compound ASC/DESC ordering, and MySQL-compatible nullable ordering.

### Fixed

- Reject cursor tokens longer than 4,096 encoded characters before Base64 decoding
or JSON deserialization, bounding work performed on untrusted paging input.

### Documentation

- Expanded the package README into a complete capability overview covering entity
mapping, repositories, querying and paging, schema sync, migrations, lifecycle,
transactions, caching, resilience, observability, configuration, and API boundaries.

## 2026-06-20

### Fixed

- Query-builder parameter re-keying could corrupt SQL when a single predicate
emitted 11 or more parameters: the rename used a substring replace, so `@p1`
also rewrote `@p10`/`@p11`, leaving placeholders with no bound value.
Parameters are now renamed longest-name-first in `QueryBuilder.Where` and
`JoinedQuery`, matching the existing subquery path. Covered by a new
integration test (12-parameter predicate).

### Documentation

- **Full README + multi-page docs rewrite to the unified house style.** The
README is now concise — title, NuGet + license badges, one-line tagline, a
short intro, `Install`, `Quick start`, `Features`, `Configuration` (table +
JSON), `Documentation`, `Requirements`, and `License` — and renders correctly
on both GitHub and NuGet (Markdown only, absolute `https://` links, no raw
HTML or relative paths). The full API now lives in the docs site rather than
the README.
- **Docs site pages rewritten** to match the house style across the four-page
structure: [`index`](https://zyntal-com.github.io/CodeLogic.Libs/libs/mysql2/index.html)
(overview, load, repository basics, entry points, config, health, events),
[`queries`](https://zyntal-com.github.io/CodeLogic.Libs/libs/mysql2/queries.html),
[`schema-migrations`](https://zyntal-com.github.io/CodeLogic.Libs/libs/mysql2/schema-migrations.html),
and [`performance`](https://zyntal-com.github.io/CodeLogic.Libs/libs/mysql2/performance.html).
Each sub-page now opens with a tagline and an overview breadcrumb and closes
with a consistent "See also" footer.
- **No API changes.** Documentation only — no behaviour, signatures, config
keys, or version numbers were altered.

## [4.5.3] — 2026-06-20

### Added

- **Three schema sync modes — `SyncMode`.** A new operator-facing knob on each
database (`config.mysql.json`) replaces the lower-level `SchemaSyncLevel` /
`AllowDestructiveSync` flags (which still work for back-compat — `SyncMode` takes
precedence and maps onto them via `EffectiveSyncLevel`).

| Mode | Behaviour |
|---|---|
| `Developer` | Aggressive rolling updates — drops removed columns/indexes/FKs on every boot (maps to `Full`). |
| `Production` *(default)* | Additive only — adds/modifies, **never drops**. A change that needs a drop is deferred and the table is flagged `DriftPending`. |
| `Migration` | Deliberate one-shot destructive reconcile (takes a schema backup first). Idempotent — once every model matches and no drift is pending it does nothing and logs a warning to switch back to `Production`. |

```json
{ "Databases": { "Default": { "SyncMode": "Production" } } }
```

- **CRC sentinel — `__schema_state`.** Each model's desired schema is hashed
(CRC) into a per-table row. Sync skips a table **entirely** — no
`information_schema` diffing, no DDL — when the stored CRC matches the model
*and* the table still exists. New `SyncResult` fields: `Skipped`, `SchemaCrc`,
`DriftPending`; new `SchemaSyncStatus` enum (`Synced` / `DriftPending`).
Exposed via `mysql.SchemaState` (a `SchemaStateStore`).

- **Cross-node schema-sync lock — `SchemaSyncLock`.** A schema/migration pass
serializes across application nodes with MySQL `GET_LOCK`. The winner runs the
DDL; peers wait, then find the schema already reconciled (matching CRCs) and do
nothing.

- **Batch schema sync + runtime mode override.** `mysql.SyncSchemaAsync(params
Type[])` reconciles a whole set of entities as one pass under a single lock,
honouring the configured `SyncMode` and the CRC fast-path — the recommended
startup entry point. `mysql.SetSyncMode(mode, connectionId)` overrides the mode
at runtime (e.g. to flip `Migration` back to `Production` once a pass completes).

- **Imperative migrations.** `IMigration` / `MigrationVersion` / the abstract
`Migration` base for data transforms, seeds, and semantic changes the
declarative sync can't express. `IMigrationContext` provides `ExecuteAsync`,
`QueryAsync<T>`, `ScalarAsync<T>`, and a `SyncTableAsync<T>()` bridge into
declarative sync. The `MigrationRunner` applies pending migrations in
`MigrationVersion` order over the `__migrations` table, each in its own
transaction, under the shared lock, gated by the app version
(`CodeLogicEnvironment.AppVersion`), and warns when an applied migration's
checksum has drifted. Library surface: `RegisterMigration`,
`RegisterMigrationsFrom(assembly)`, `MigrateAsync`, `GetPendingMigrationsAsync`.

```csharp
public sealed class SeedRoles() : Migration("1.4.0", 1, "Seed default roles")
{
     public override async Task UpAsync(IMigrationContext ctx, CancellationToken ct) =>
         await ctx.ExecuteAsync("INSERT INTO roles (name) VALUES ('admin'), ('user')", ct: ct);
}
```

> MySQL implicitly commits on DDL, so a migration that mixes `ALTER` with data
> changes is not atomic — keep `UpAsync` steps idempotent.

- **Rollback.** `mysql.RollbackAsync(MigrationVersion target)` runs `DownAsync`
newest-first for every applied migration above `target`, each in its own
transaction. It pre-flights the range and aborts cleanly **before any change**
if a migration in range has no `DownAsync` override. Declaratively,
`mysql.RestoreSchemaAsync(tableName)` replays a `BackupManager` schema snapshot
(DDL only — rows are lost) and clears the table's `__schema_state` row so the
next sync reconciles from scratch.

### Fixed

- **Upsert now portable to MariaDB.** `UpsertAsync`, `UpsertManyAsync`, and
`UpsertWithIncrementsAsync` emit the portable `... ON DUPLICATE KEY UPDATE col =
VALUES(col)` form, which works on **both** MySQL and MariaDB, instead of the
MySQL-8.0.19+-only `INSERT ... AS new ... ON DUPLICATE KEY UPDATE` row-alias
syntax that MariaDB rejected.

## [4.5.2] — 2026-06-13

### Added

- **Typed JOINs.** `Query<TLeft>().Join<TRight, TKey, TResult>(leftKey, rightKey,
resultSelector, type)` translates a strongly-typed equi-join to real SQL with
table aliases (left `t0`, right `t1`) and a compiled, reflection-free projection
into `TResult` — only the columns the selector references are transferred.

```csharp
var views = await mysql.Query<Order>()
     .Where(o => o.Total > 100)
     .Join<Customer, long, OrderView>(
         o => o.CustomerId,                 // left key
         c => c.Id,                         // right key
         (o, c) => new OrderView { OrderId = o.Id, Customer = c.Name })
     .OrderByDescending((o, c) => o.Total)
     .Take(20)
     .ToListAsync();
```

- **Join types:** `Inner` (default), `Left`, `Right`. `Cross` is rejected for a
   keyed join (keys imply an equi-join).
- **Composite keys:** `o => new { o.A, o.B }` matched positionally with
   `c => new { c.X, c.Y }`.
- **Carried filters:** `.Where(...)` calls made on the left builder *before*
   `.Join` are re-qualified to the left table and preserved.
- **Fluent surface on the join:** `.Where((l, r) => …)`, `.OrderBy` /
   `.OrderByDescending((l, r) => …)`, `.Take` / `.Skip`, and the
   `ToListAsync` / `FirstOrDefaultAsync` / `CountAsync` terminals.
- The single-table query path and the existing raw-string
   `Join(table, condition, type)` overload are unchanged.

- **Subquery filters — `EXISTS` / `IN`.** Four new WHERE-family methods on the
query builder translate to real SQL subqueries:

```csharp
// Correlated EXISTS — correlated + non-correlated conditions in one predicate
mysql.Query<Order>()

---

Release notes truncated to fit NuGet's 35 000 character limit. The complete changelog ships inside this package as CHANGELOG.md.