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:

The same tier's access half — generated row mappers over hand-written SQL — is SQL mapping (ADR-0058); neither package requires the other. Both run 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>

// PostgreSQL
builder.Services.AddElarionPostgreSqlMigrations(
    builder.Configuration.GetConnectionString("Default")!,
    o => o.AddScripts(typeof(Program).Assembly, "MyApp.Migrations."));

// SQLite (single-node / edge)
builder.Services.AddElarionSqliteMigrations(
    builder.Configuration.GetConnectionString("Default")!,   // e.g. "Data Source=app.db"
    o => o.AddScripts(typeof(Program).Assembly, "MyApp.Migrations."));

That 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 registration 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:

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.

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.

Options

The neutral options apply to every provider:

The PostgreSQL provider adds AdvisoryLockKey (a fixed constant by default) — distinct keys let two apps sharing one database migrate independent schemas concurrently. SQLite adds no options.

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.