Skip to content

0010-alembic-migration-based-schema-management.md

Latest commit

 

History

History
83 lines (65 loc) · 3.52 KB

0010-alembic-migration-based-schema-management.md

File metadata and controls

83 lines (65 loc) · 3.52 KB

ADR-0010: Alembic — Migration-Based Schema Management

Date: 2026-04-09

Status

Accepted. Supersedes ADR-0002.

Context

ADR-0002 decided against Alembic in favour of standalone seed scripts in tools/, citing low schema churn and the simplicity of a single committed SQLite file. Issue #2 was opened to revisit this decision when the project matured.

Several factors made the status quo unsustainable:

  • Committing a binary .db file to the repository means every schema change produces an opaque, unreviewable diff. Reviewers cannot tell what data changed or why.
  • The Docker entrypoint copied a pre-seeded file from the image into the container volume — coupling the data to the image build and requiring a full rebuild whenever the dataset changed.
  • The project targets future PostgreSQL support (issue #542), which a SQLite-only committed file cannot accommodate.
  • As a cross-language educational reference, the project should demonstrate production-grade database lifecycle practices, and Alembic is the standard tool for SQLAlchemy projects.

Alternatives Considered

  • Keep the committed .db file + seed scripts: Already rejected in issue #2; the coupling to the binary file blocks PostgreSQL support and produces unreadable diffs.
  • Prisma Client Python: Requires Node.js alongside Python, uses its own schema DSL instead of SQLAlchemy models, and has less mature Python support. Rejected because it would introduce a second source of truth for the schema.

Decision

We will use Alembic as the schema and seed data migration tool.

Three migration files replace the committed database and the standalone seed scripts:

  • 001_create_players_table.py — autogenerated from the SQLAlchemy Player schema; creates the players table.
  • 002_seed_starting11.py — inserts the 11 Starting XI players using deterministic UUID v5 values.
  • 003_seed_substitutes.py — inserts the 15 Substitute players using deterministic UUID v5 values.

alembic/env.py is configured for async SQLAlchemy (asyncio.run via thread executor), reads DATABASE_URL from the environment (SQLite default, PostgreSQL compatible), and sets render_as_batch=True for SQLite ALTER TABLE compatibility. Each migration's downgrade() function deletes only the rows it inserted (by UUID), so migrations are independently reversible.

alembic upgrade head is run by entrypoint.sh before Uvicorn starts (Docker). For local development, it is run once manually: uv run alembic upgrade head.

Consequences

Positive:

  • Schema and seed data changes are versioned, reviewable, and reversible.
  • The committed .db file is removed from the repository; *.db is added to .gitignore.
  • DATABASE_URL environment variable enables the same codebase to target both SQLite (local/test) and PostgreSQL (Docker/production) without code changes — a prerequisite for issue #542.
  • UUID v5 seed values are deterministic across environments, so test fixtures that reference player IDs remain stable.

Negative:

  • Local development requires uv run alembic upgrade head before the first server start (or after a docker compose down -v).
  • Alembic's offline mode (alembic --sql) is not exercised by integration tests; alembic/env.py offline path is excluded from coverage.
  • render_as_batch=True means SQLite schema changes use a copy-transform- replace strategy rather than ALTER TABLE — this is invisible to callers but must be understood when debugging migration failures.