CI checks for EF Core migration integrity (SQLite + MySql) #13

Closed
opened 2026-06-27 00:06:30 +02:00 by timothy · 1 comment
Owner

Risk this addresses

EF Core migrations are the classic source of "works locally, breaks on deploy" and of painful restructuring. We already saw non-transactional PRAGMA foreign_keys migrations warn at startup, and the prod cutover (GHCR→fork image) will run migrations against the real DB. A forgotten or model-drifted migration could corrupt prod or fail to start.

Approach

Add CI checks (and/or tests) covering both providers (TvContext for SQLite and the MySql variant):

  • dotnet ef migrations has-pending-model-changes → fail if the model and migrations have drifted (i.e. someone changed an entity but didn't add a migration).
  • Apply all migrations to a fresh empty DB and assert success (catches broken/un-orderable migrations) — for both SQLite and MySql.
  • Document the dual-provider migration discipline (when/how to add a migration for each provider, the non-transactional-operation caveat).

Payoff

Schema changes become safe and reviewable; protects the prod cutover and every future migration. Backlog — before the prod cutover in server-management#481.

## Risk this addresses EF Core migrations are the classic source of "works locally, breaks on deploy" and of painful restructuring. We already saw non-transactional `PRAGMA foreign_keys` migrations warn at startup, and the prod cutover (GHCR→fork image) will run migrations against the real DB. A forgotten or model-drifted migration could corrupt prod or fail to start. ## Approach Add CI checks (and/or tests) covering both providers (`TvContext` for SQLite and the MySql variant): - `dotnet ef migrations has-pending-model-changes` → fail if the model and migrations have drifted (i.e. someone changed an entity but didn't add a migration). - Apply **all** migrations to a fresh empty DB and assert success (catches broken/un-orderable migrations) — for both SQLite and MySql. - Document the dual-provider migration discipline (when/how to add a migration for each provider, the non-transactional-operation caveat). ## Payoff Schema changes become safe and reviewable; protects the prod cutover and every future migration. Backlog — before the prod cutover in server-management#481.
Author
Owner

Done — closing

Merged via PR #30; full main pipeline green incl. the new migrations job.

What was done — a migrations job in docker-build.yml (runs on every push/PR) that, for both providers:

  1. dotnet ef migrations has-pending-model-changes — fails on model drift (entity changed without a matching migration), so a forgotten migration can't merge.
  2. dotnet ef database update against a fresh empty DB — applies all migrations in order, catching broken/un-orderable ones.

Providers

  • SQLite (prod): throwaway file, no service.
  • MySql: Startup uses ServerVersion.AutoDetect (connects at config time), so the job runs a services: mysql:8.4 container. Verified the runner (act_runner v0.6.1, Docker execution, network downloadswarm) reaches it as mysql:3306 — confirmed green on real CI.

Verified (locally against real DBs, then on real CI): SQLite drift clean + 787 migrations → 139 tables; MySql drift clean + 305 migrations → 137 tables.

Files: .gitea/workflows/docker-build.yml (new job), docs/ci-cd.md (→ Migration integrity: dual-provider discipline, the EF CLI pattern, the non-transactional caveat), CLAUDE.md (convention).

Deferred / notes

  • The migrations job is an independent gate (not yet a needs: of the image build) so the new MySql-service dependency can't block image builds until it's proven stable. Now that it's green, it can be promoted to a needs: of build and/or a required branch-protection check — your call (branch-protection is yours to configure).
  • Protects the prod cutover (server-management#481) as intended.

Dual-provider discipline (task 3) documented: a TvContext model change needs a migration in both provider projects — use scripts/add-migration.sh <Name>; the CI job now enforces it.

## Done — closing Merged via PR #30; full `main` pipeline green incl. the new `migrations` job. **What was done** — a `migrations` job in `docker-build.yml` (runs on every push/PR) that, for **both** providers: 1. `dotnet ef migrations has-pending-model-changes` — fails on **model drift** (entity changed without a matching migration), so a forgotten migration can't merge. 2. `dotnet ef database update` against a **fresh empty DB** — applies *all* migrations in order, catching broken/un-orderable ones. **Providers** - **SQLite** (prod): throwaway file, no service. - **MySql**: `Startup` uses `ServerVersion.AutoDetect` (connects at config time), so the job runs a `services: mysql:8.4` container. Verified the runner (`act_runner v0.6.1`, Docker execution, network `downloadswarm`) reaches it as `mysql:3306` — **confirmed green on real CI**. **Verified** (locally against real DBs, then on real CI): SQLite drift clean + 787 migrations → 139 tables; MySql drift clean + 305 migrations → 137 tables. **Files**: `.gitea/workflows/docker-build.yml` (new job), `docs/ci-cd.md` (→ **Migration integrity**: dual-provider discipline, the EF CLI pattern, the non-transactional caveat), `CLAUDE.md` (convention). **Deferred / notes** - The `migrations` job is an **independent gate** (not yet a `needs:` of the image `build`) so the new MySql-service dependency can't block image builds until it's proven stable. **Now that it's green, it can be promoted to a `needs:` of `build` and/or a required branch-protection check** — your call (branch-protection is yours to configure). - Protects the prod cutover (server-management#481) as intended. **Dual-provider discipline** (task 3) documented: a `TvContext` model change needs a migration in *both* provider projects — use `scripts/add-migration.sh <Name>`; the CI job now enforces it.
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: timothy/ersatztv#13