alembic-fresh-pg-smoke-test

# Alembic Fresh-PG Smoke Test ## When to Use Any project where: - Unit tests use **SQLite** (e.g. `aiosqlite`) for speed/isolation, **but** - Production / target deployment runs **PostgreSQL**, **and** - The same Alembic migration tree is supposed to apply to both. If both halves are true, you have a **silent gap**: PG-only DDL semantics (enum types, `ALTER COLUMN TYPE` with bound defaults, `DROP TYPE` dependency chains, partial indexes, etc.) are *never exercised* by your test suite. ## The Hidden Failure Mode Long-lived dev/staging databases get migrated **incrementally**, one revision at a time, as commits land. A migration that contains a structural bug will appear to work because the previous state already had whatever it expected. The bomb only detonates when **someone runs `alembic upgrade head` against a brand-new empty database** — typically a new contributor bootstrapping their laptop, a CI provisioning a fresh container, or a prod cutover. By then the person who wrote the migration is no longer paged in to fix it. ## Concrete Bugs This Catches These are real classes of bugs that pass every SQLite test but break on a fresh PG: 1. **`ALTER COLUMN TYPE` blocked by an enum-typed `DEFAULT`** ``` DependentObjectsStillExistError: cannot drop type tasksource because other objects depend on it DETAIL: default value for column source of table tasks depends on type tasksource ``` Cause: column has `DEFAULT 'foo'` typed as the old enum. `ALTER COLUMN TYPE VARCHAR` succeeds for the column but the default still references the enum, so `DROP TYPE` fails. Fix: `ALTER COLUMN ... DROP DEFAULT` *first*, then re-`SET DEFAULT` after the enum is back. SQLite has no enum-bound defaults. 2. **`CREATE TYPE` colliding with SQLAlchemy's metadata-level enum DDL listener** ``` DuplicateObjectError: type "activitylevel" already exists ``` Cause: `op.execute("CREATE TYPE foo")` followed by `op.create_table(..., sa.Enum(..., name="foo"))` — even with `create_type=False` on the migration's own Enum, the model's metadata-bound listener fires another `CREATE TYPE` inside `create_table`. Fix: drop the explicit `op.execute("CREATE TYPE...")` and let SQLAlchemy create it once, OR wrap in `DO $$ ... EXCEPTION WHEN duplicate_object ...`. SQLite has no enum types. 3. **`DROP TYPE` blocked by surviving FKs / dependent objects** — same family as #1 but with foreign keys, default expressions, views, etc. 4. **Partial / functional indexes** that PG accepts but SQLite silently ignores or rewrites. ## The Smoke Test The pattern: pytest test that spins up a throwaway postgres container via `docker run -d --rm`, waits for `pg_isready`, runs `alembic upgrade head` once, asserts exit 0, tears down. **No new dependencies**, just `subprocess` + the existing `docker` binary. Skips automatically when docker is unavailable so it does not break local laptops without docker. ```python # backend/tests/test_alembic_fresh_migration.py """Smoke test: alembic upgrade head must succeed against a fresh PostgreSQL.""" from __future__ import annotations import os import shutil import socket import subprocess import time import uuid import pytest def _docker_available() -> bool: if shutil.which("docker") is None: return False try: subprocess.run(["docker", "info"], check=True, capture_output=True, timeout=5) except (subprocess.CalledProcessError, subprocess.TimeoutExpired, FileNotFoundError): return False return True def _free_port() -> int: with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s: s.bind(("127.0.0.1", 0)) return s.getsockname()[1] def _wait_for_pg(container: str, timeout_s: float = 30.0) -> None: deadline = time.monotonic() + timeout_s while time.monotonic() < deadline: result = subprocess.run( ["docker", "exec", container, "pg_isready", "-U", "myuser", "-d", "mydb"], capture_output=True, text=True, ) if result.returncode == 0: return time.sleep(0.5) raise TimeoutError("postgres did not become ready in time") pytestmark = pytest.mark.skipif( not _docker_available(), reason="docker is not available; skipping fresh-migration smoke test", ) REPO_ROOT = os.path.abspath(os.path.join(os.path.dirname(__file__), "..", "..")) ALEMBIC_INI = os.path.join(REPO_ROOT, "backend", "alembic.ini") def _alembic(database_url: str, *args: str) -> subprocess.CompletedProcess[str]: env = os.environ.copy() env["DATABASE_URL"] = database_url return subprocess.run( ["uv", "run", "--project", "backend", "alembic", "-c", ALEMBIC_INI, *args], cwd=REPO_ROOT, env=env, capture_output=True, text=True, timeout=300, ) def test_alembic_upgrade_head_on_fresh_postgres() -> None: container = f"app-migrate-test-{uuid.uuid4().hex[:8]}" port = _free_port() subprocess.run( [ "docker", "run", "-d", "--rm", "--name", container, "-e", "POSTGRES_DB=mydb", "-e", "POSTGRES_USER=myuser", "-e", "POSTGRES_PASSWORD=mypass", "-p", f"127.0.0.1:{port}:5432", "postgres:16-alpine", ], check=True, capture_output=True, ) try: _wait_for_pg(container) url = f"postgresql+asyncpg://myuser:mypass@127.0.0.1:{port}/mydb" result = _alembic(url, "upgrade", "head") assert result.returncode == 0, ( f"upgrade head failed:\nSTDOUT:\n{result.stdout}\nSTDERR:\n{result.stderr}" ) finally: subprocess.run(["docker", "rm", "-f", container], capture_output=True) ``` Runtime: ~2-10 seconds when the postgres image is cached locally. ~30 seconds on a cold CI image pull. ## Why Not Round-Trip (`upgrade head -> downgrade base -> upgrade head`)? Round-trip is stronger but breaks intentionally-lossy migrations (e.g. collapsing a 6-state enum to 4 states cannot be reversed without data loss and is correctly written to `raise NotImplementedError` on downgrade). For those projects, the upgrade-only smoke is the right granularity. Round-trip is appropriate when downgrades are required to be reversible (e.g. deploy-rollback policy). ## Why Not testcontainers / pytest-docker? Both work, but they add a dependency for what is one self-contained test. Plain `subprocess + docker` keeps the test in the same toolchain you already have and removes any version-pin headache. Switch to testcontainers only if you need it for several tests. ## CI Wiring GitHub Actions and most CI services already have `docker` on the runner — the test runs as-is. If your CI uses a postgres *service container* instead, point `DATABASE_URL` at the service and **delete the container-spinup code** (just keep the alembic call). Either shape catches the same bugs. ## Why This Skill Exists I had a Plan view overhaul branch with ~30 alembic revisions. Backend tests were 80%+ green on SQLite. e2e mock tests were 89/89 green. e2e *live* tests ran against a long-lived dev postgres that had been migrated commit-by-commit for weeks. **Two PG-only migration bugs** (DROP DEFAULT trap on the TaskSource enum, CREATE TYPE collision on `activitylevel`) sat undetected the entire time and only surfaced when I tried to run the backend on a fresh laptop postgres. The fix was three lines per bug; the time wasted finding them was hours. A 10-second smoke test would have failed at PR time. The unit-test-vs-deploy-DB engine mismatch is the real root cause; this skill is the cheapest way to close the gap without giving up SQLite's speed for the rest of the suite. --- ## ⚠️ Two gaps this smoke test does NOT close (measured 2026-09-03) The smoke test above proves **DDL** applies to a fresh PG. Two things sit outside it, and both look exactly like coverage. ### 1. Read the gate's `skipped` count, not just `passed` A PG-gated suite **skips silently** when no Postgres is reachable. A full run came back: ``` 6618 passed, 43 skipped ← the whole PG tier, including a schema migration ``` Green. And the migration in that PR had **never run against Postgres at all**. Turn PG on and the same run reads `6662 passed, 1 skipped`. ⇒ For any PR touching schema/migrations, **read the skip count before the pass count**, and open that gate. A disposable container is enough: ```bash docker run -d --name pg-probe -e POSTGRES_USER=app -e POSTGRES_PASSWORD=app \ -e POSTGRES_DB=app -p 15452:5432 pgvector/pgvector:pg16 ``` Pick a port nothing else holds, and confirm the target is **empty** before a suite that starts with `DROP SCHEMA`: ```sql SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public'; ``` If the project runs a **two-role** setup (owner for DDL, least-privilege for runtime), the runtime DSN must point at the least-privilege role or the RLS tests fail *correctly* and look like your bug. Let the migration create that role, then switch the DSN. ### 2. A migration's DATA branch is a no-op on an empty database This is the sharp one. `upgrade head` on a fresh DB runs your backfill / exemption / rename UPDATE **against zero rows**. The round trip proves the statement *parses*. It proves nothing about what it does. ```python # Runs on an empty DB → 0 rows → passes no matter what it says op.execute(sa.text(""" UPDATE workspaces SET max_concurrent_runs = NULL WHERE id IN (SELECT m.workspace_id FROM memberships m JOIN users u ON u.id = m.user_id WHERE u.email = :operator_email) """)) ``` That statement existed to keep the operator's own workspace from being locked out on deploy. A green fresh-PG round trip said nothing about it. **Force the branch open**: stop one revision short, seed the rows a real deployment would meet, then upgrade to head and assert the data. ```python _alembic(["upgrade", "<revision BEFORE yours>"], env_extra=env_extra) asyncio.run(_seed_the_rows_a_real_deploy_would_meet()) _alembic(["upgrade", "head"], env_extra=env_extra) caps = asyncio.run(_read_them_back()) assert caps[ordinary_ws] == 3 # backfilled assert caps[operator_ws] is None # exempted ``` Then **delete the UPDATE and confirm that test — and only that test — goes red**. Ours failed with `assert 3 is None`, which is the proof the assertion was load-bearing. ⇒ Rule of thumb: **DDL is covered by the round trip; DML is not.** Every `op.execute(... UPDATE/INSERT/DELETE ...)` needs its own seeded test, because the environment the smoke test builds is precisely the one where that statement does nothing. ## 이름을 틀려도 SQLite 는 전부 초록이다 — 마이그레이션의 테이블 이름 가장 싸고 가장 안 잡히는 실패는 DDL 문법이 아니라 **대상 이름**이다. ```python op.add_column("workers", sa.Column("protocol_version", sa.Integer(), ...)) # ^^^^^^^^^ 실제 테이블은 executor_workers ``` 유닛 스위트는 **한 건도 안 빨개진다.** `Base.metadata.create_all` 은 ORM 의 `__tablename__` 을 보고 만들고, **마이그레이션 파일은 읽지도 않는다.** 그래서 모델 쪽 컬럼은 정상 동작하고, 틀린 건 오직 진짜 PG 에 DDL 을 칠 때 드러난다. 덫이 깊어지는 조건: **그 "당연해 보이는" 이름의 테이블이 과거에 실제로 있었다가 삭제된 경우.** 실측 사례에서 `workers` 는 0행인 채 `drop_dead_worker_tables` (2026-08-21)로 지워졌고 진짜 SoT 는 `executor_workers` 였다. git 로그·옛 코드· 내 기억 전부가 틀린 이름을 지지한다. ⇒ **컬럼을 추가하기 전에 `grep -n '__tablename__' <models.py>` 를 한 번 쳐라.** 모델이 유일한 진실이고, 그걸 확인하는 데 3초 걸린다. 그리고 fresh-PG 스모크가 이 부류를 **전량** 잡는 유일한 게이트다 — 이 실수는 PR 리뷰에서도 잘 안 보인다 (틀린 이름이 그럴듯하기 때문에). ### Related * `the-branch-behind-a-human-gate-was-never-run` — same shape, different gate: code behind a credential/approval has an execution count of zero. * `counting-only-passes-hides-what-never-ran` — a summary that counts only passes erases the third state.