Database Versioning

A relation between Alembic and ORM
databse
versioning
alembic
schema
Author

Shataxi Dubey

Published

August 3, 2026

Alembic vs. the Async ORM

Context

This project (fosterclub-talent-api-ai-ats-service) uses: - SQLAlchemy (async) with DeclarativeBase models in app/core/database.py - MariaDB as the database, accessed via mysql+aiomysql:// (see app/core/config.py:177) - alembic listed as a dependency in pyproject.toml, but not yet initialized — there is no alembic.ini or alembic/versions/ directory in the project, so no migration history currently exists.

Note: asyncpg also appears in pyproject.toml but is unused — the project runs on MariaDB/aiomysql, not Postgres. Worth confirming if it’s dead weight and removing.

Alembic vs. SQLAlchemy ORM — not alternatives

  • SQLAlchemy ORM defines what the schema should look like in Python (ResumeParseJob, JDSession, RankingCriteria, etc.) and lets the app read/write rows at runtime. It does not track schema history or apply changes to an existing database safely.
  • Alembic is the migration tool that turns a diff between your models and the live database into versioned, ordered, reviewable migration scripts, and applies/reverts them (alembic upgrade head / alembic downgrade).

Why Alembic is needed even with the ORM

  • Base.metadata.create_all() only creates tables that don’t exist yet — it never alters existing tables. Adding a column to a model does nothing to a table that’s already in the database.
  • Alembic generates the diff (alembic revision --autogenerate), lets you review/edit it, then applies it consistently across dev/staging/prod.

What Alembic gives you

  • Repeatable deploys — same migrations run in every environment, no schema drift.
  • Team coordination — parallel schema changes become separate migration files instead of manual ALTER TABLE reconciliation.
  • Safe evolution of existing data — renames, backfills, adding NOT NULL to populated tables — things create_all() can’t do.
  • Auditable history — git log on alembic/versions/ is the schema changelog.
  • Rollback — alembic downgrade -1 if a migration goes wrong in production.

When Alembic doesn’t apply / can’t help

  1. Non-relational stores — Alembic only manages SQLAlchemy-mapped relational schema. It has no concept of Qdrant collections, Redis cache keys/TTLs, or Celery/TaskIQ job/queue state. Those need their own versioning approach.
  2. Multi-tenant / sharded databases — Alembic doesn’t fan a migration out across multiple databases/tenants on its own; you’d script that orchestration yourself (e.g. loop alembic upgrade head per connection string).
  3. Large-table structural changes on live traffic — Alembic will run whatever SQL you give it, including a lock-heavy ALTER TABLE, without warning you. Safe multi-step patterns (add nullable column → backfill in batches → add constraint → drop old column) have to be hand-written.
  4. Changes autogenerate can’t detect reliably:
    • Renames (seen as drop + add, loses data unless manually fixed with op.alter_column(..., new_column_name=...))
    • Data backfills/transformations (no “old value” to diff against — write custom Python in the migration)
    • Some dialect-specific objects: enum types, certain index/collation nuances, default-value comparisons
  5. Non-DDL runtime config — feature flags, cache TTLs, queue definitions — outside Alembic’s scope by design.

MariaDB/MySQL-specific caveats (this project’s actual DB)

Since this project runs on MariaDB via aiomysql, not Postgres, these matter more than the generic caveats above:

  1. DDL is not transactional. Most DDL statements (ALTER TABLE, CREATE TABLE, etc.) cause an implicit commit in MariaDB/MySQL and can’t be rolled back as part of a transaction. If a multi-statement migration fails partway through, alembic downgrade may not cleanly undo already-applied DDL — you can be left in a partial state requiring manual cleanup. Postgres, by contrast, has transactional DDL and is safer here.
  2. ALTER TABLE locks the whole table on most MariaDB versions/storage engines (unless the specific version/change qualifies for ALGORITHM=INSTANT/INPLACE). Schema changes on large, live tables can block reads/writes for the duration. For anything beyond a small table, use online-schema-change tooling (pt-online-schema-change, gh-ost) invoked from the migration, or split the change into safer steps — Alembic won’t do this automatically.
  3. Weaker autogenerate support on the MySQL/MariaDB dialect compared to Postgres — enum types, some index/collation nuances, and default-value comparisons are more likely to produce a no-op or slightly wrong diff that needs manual correction.
  4. Connection pool pressure — app/core/config.py:179-181 notes that MariaDB gets flooded under concurrent load from pool sizing (DB_POOL_SIZE + DB_MAX_OVERFLOW per worker process). Any migration step that opens its own connection (e.g. autogenerate comparing live schema) should be mindful of this same constraint, though it’s a minor operational note rather than a blocker.