SQL migrations

Elarion's persistence story is EF-model-first, and an EF application keeps EF Core migrations — that default is not in question. The gap is the EF-free host: an app published NativeAOT on a raw driver still needs schema management, EF's NativeAOT support is experimental, and nothing maintained fills the hole (Evolve is dormant, Flyway is a JVM tool). Elarion.Migrations is that tier's runner (ADR-0057, ADR-0060): minimal, AOT-compatible, and designed explicitly against Flyway's documented production failure modes. The engine is database-neutral; a provider package supplies the SQL and locking:

Each provider package ships the migration engine alongside the tier's access half — generated row mappers over hand-written SQL, SQL mapping (ADR-0058) — so the single provider call picks the database for both and they share one data source: AddElarionPostgreSql on Elarion.Sql.PostgreSql, AddElarionSqlite on Elarion.Sql.Sqlite. AddElarionMigrations itself is provider-neutral. PostgreSQL runs both together in the samples/EdgeTelemetry NativeAOT host.

Pick the row that matches your app:

It deliberately does not ship Elarion framework-table scripts: the EF-based packages remain EF-delivered, and this package never competes with that default.

Quick start

Embed your scripts and register the runner for your database. The API is identical across providers apart from the registration method:

<ItemGroup>
  <EmbeddedResource Include="Migrations/**/*.sql" />
</ItemGroup>

Registration is two steps and identical across providers: pick the database with the provider call, then register migrations with the neutral AddElarionMigrations — the only line that differs is the provider.

// PostgreSQL — the same call also wires the SQL access tier, on one shared NpgsqlDataSource:
builder.Services.AddElarionPostgreSql(builder.Configuration.GetConnectionString("Default")!);

// ...or SQLite (single-node / edge):
builder.Services.AddElarionSqlite(builder.Configuration.GetConnectionString("Default")!);  // "Data Source=app.db"

// Neutral — names no provider:
builder.Services.AddElarionMigrations(
    o => o.AddScripts(typeof(Program).Assembly, "MyApp.Migrations."));

AddElarionMigrations registers IMigrationRunner plus a hosted service that applies pending migrations before the host reports ready and fails startup on error — serving traffic against a half-migrated schema is worse than not starting. Register it before other hosted services that expect the schema (hosted services start in registration order); set ApplyOnStartup = false to invoke the runner yourself. The PostgreSQL provider call also has an NpgsqlDataSource overload for hosts that already manage one.

Scripts are plain SQL in your database's own dialect — the runner never rewrites them, so write PostgreSQL for the PostgreSQL provider and SQLite for the SQLite provider.

Script conventions

Scripts are embedded resources (discovery reads the assembly manifest — AOT-safe, no filesystem):

• Use timestamp versions (V20260713093000__add_devices.sql): they make branch-merge collisions structurally rare instead of policing them at deploy time.
• Multi-segment versions separate segments with single underscores (V1_2__… is version 1.2) — file names cannot contain dots, because folder paths become dot-separated resource-name segments.
• Validation is fail-closed and total: within the configured scope (assembly + optional resource prefix), every .sql resource must be a valid script — duplicate versions, malformed names, and undecodable content each fail naming the offending resource. Nothing is silently skipped.
• Repeatables are for idempotent CREATE OR REPLACE surfaces (views, functions) — never destructive DDL.

Checksums are SHA-256 over normalized content (BOM stripped, CRLF→LF), so line-ending churn — git autocrlf, an editor touching whitespace — can never invalidate an applied script. A genuine mismatch fails validation with the script name, both hashes, and the two legal resolutions: revert the edit, or add a new script.

Execution model

One runner, one dedicated connection, guarded by an exclusive migration lock so concurrent startups serialize, waiters re-read history and no-op. The lock's scope is the provider's:

• PostgreSQL takes a session-level pg_advisory_lock — it serializes the 1–10-node tier across processes, and a crashed runner releases it with its connection (no lock row to clean up).
• SQLite takes a per-file in-process lock — SQLite is single-node by design (one file per node, never shared across nodes), so this process is the correct scope; LockTimeout bounds the wait and busy_timeout backstops the unsupported cross-process case.

Each versioned script runs in its own transaction, and its history row (elarion_schema_history) commits in that same transaction. A failed transactional migration therefore leaves no history row: fix the script and rerun. There is no repair command because there is nothing to repair — and no undo either (a dropped column's data cannot be un-dropped; roll forward). This invariant is identical on both providers (SQLite has full transactional DDL).

Non-transactional scripts

DDL a database forbids inside a transaction opts out via a directive on the leading comment block:

-- elarion: no-transaction
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);

On PostgreSQL this is for statements like CREATE INDEX CONCURRENTLY; the runner executes the script statement by statement (each as its own implicit transaction — batching them would reintroduce the transaction the directive opts out of). On SQLite it is rarely needed — SQLite has full transactional DDL and no CREATE INDEX CONCURRENTLY — and such a script runs in autocommit mode (each statement commits on its own). Only a non-transactional script can leave a mid-applied state on failure: the runner records an explicit failed history row and subsequent runs fail closed, naming the script and the recovery API:

// AddElarionMigrations registers IMigrationRunner — inject/resolve it where you handle recovery:
IMigrationRunner runner = app.Services.GetRequiredService<IMigrationRunner>();

await runner.ResolveFailedAsync("20260713093000", ResolveAction.Retry);       // rerun the fixed script
await runner.ResolveFailedAsync("20260713093000", ResolveAction.MarkApplied); // schema was completed by hand

A deliberate in-code decision at the call site — not a CLI habit. An unknown directive (a typo like no-transactoin) fails validation instead of silently changing transaction semantics.

Failed rows are recorded for versioned scripts only: a repeatable script that fails under no-transaction records nothing — repeatables are idempotent by doctrine and their changed checksum was never recorded, so the next run simply retries them.

Migrating into a schema

By default everything lands in the connection's own default schema (public on a stock PostgreSQL). To keep the application's objects in a schema of their own — without putting a prefix in a single script — name it on the provider registration:

builder.Services.AddElarionPostgreSql(
    builder.Configuration.GetConnectionString("Default")!, schema: "app");

There is no migration option for this, deliberately. The schema is the connection's Search Path, so one setting steers both the SQL access tier and migrations: prefix-free scripts and unqualified application queries resolve through the same path and cannot drift into different schemas. schema: is pure convenience — Search Path=app in the connection string is the same thing, and wins if you set both. The NpgsqlDataSource overload takes no schema argument for the same reason: your data source already carries it.

What migrations add on top is the one thing a connection setting cannot do: the runner creates the schema if it does not exist, under the migration lock so concurrent starters cannot race, and writes the history table schema-qualified — so two schemas in one database keep independent histories and a script that leaves search_path pointing elsewhere cannot misplace history rows.

SQLite has no schema concept — its "schema names" are ATTACHed database files — so use a separate database file per tenant there instead.

Out-of-order scripts

When a merge lands a script versioned below one already applied, the default policy is Warn: the script is applied, logged as a warning, and recorded in true execution order (installed_rank). The history table records what actually happened either way; a strict default only teaches teams a global escape flag. Teams that want strict ordering opt into OutOfOrder = OutOfOrderPolicy.Deny, which fails the run naming the offenders.

Adopting an existing database

BaselineAsync(version) marks an existing schema as already at a version — scripts at or below it are treated as applied and never run. It is explicit only (allowed solely while the history is empty); there is no baselineOnMigrate auto-magic.

ValidateAsync (checksum + pending report, no writes) and GetPendingAsync back health checks and deploy gates; everything ships in the box, no feature tiers:

IMigrationRunner runner = app.Services.GetRequiredService<IMigrationRunner>();

// One-time adoption of an existing database (history must be empty):
await runner.BaselineAsync("20260713093000", "adopt existing schema", ct);

// Health check / deploy gate — read-only:
MigrationValidationResult validation = await runner.ValidateAsync(ct);
if (!validation.IsValid) Report(validation.Errors);          // checksum drift, malformed scripts
IReadOnlyList<MigrationScriptInfo> pending = await runner.GetPendingAsync(ct);

Options

The neutral options apply to every provider:

The PostgreSQL provider adds two registration arguments rather than options: advisoryLockKey (a fixed constant by default) — distinct keys let two apps sharing one database migrate independent schemas concurrently — and schema (see Migrating into a schema). Both belong to the connection, not to the migration set. SQLite adds neither.

What it deliberately is not

No undo, no repair, no placeholders/variable substitution, no dialect setting, no feature tiers. A new database engine is a new provider package implementing the IMigrationDatabase seam — never a Dialect flag on one runner. If you need any of the rejected features, replace the tool (Flyway or Evolve in CI) rather than growing this default — the same doctrine as every Elarion seam (ADR-0025). App-side data access under AOT is a separate concern, owned by ADR-0058.