specx-sqlalchemy-migrations

specx SQLAlchemy Migrations

Use this skill whenever a specx project has SQLAlchemy models or persistence adapters. Read references/alembic.md before editing migration files.

Workflow

1. Add alembic>=1.18.5 as a runtime dependency when SQLAlchemy adapters exist, together with SQLAlchemy's asyncio extra and the selected driver.
2. Add alembic.ini, migrations/env.py, migrations/script.py.mako, and migrations/versions/.
3. Use Alembic's async pattern for async SQLAlchemy engines.
4. Put app-wide SQLAlchemy settings/session factory under top-level infrastructure/sqlalchemy/.
5. Keep scope-owned ORM models and repositories under core/<scope>/infrastructure/sqlalchemy/.
6. Add a project-local SQLAlchemy declarative base under src/<package>/foundation/sqlalchemy_model.py; do not use shared packaged metadata for generated services.
7. Put reusable model discovery under top-level infrastructure/sqlalchemy/model_discovery.py. Use that same function from migrations/env.py and its guardrail test before assigning target_metadata; do not duplicate discovery or maintain hard-coded model module names.
8. Set target_metadata to the project-local BaseSQLAlchemyModel.metadata.
9. Generate or hand-review an initial migration for current models.
10. Add make migrate and make makemigrations.
11. Add tests that run alembic upgrade head against an isolated database, check for pending autogenerate changes, and prove every core SQLAlchemy model file is included by the exact discovery function Alembic uses. Use the production database family when dialect behavior matters.

Guardrails

• Do not call Base.metadata.create_all, metadata.create_all, or drop_all from src/.
• Do not run migrations from FastAPI startup by default. Run migrations as an operational command before app startup.
• Do not put app-wide engine/session factory code inside one core scope.
• Do not let delivery controllers import ORM models, repositories, sessions, or migration helpers.
• Do not let Alembic drift checks depend on incomplete metadata. Model discovery must include every core/*/infrastructure/sqlalchemy/models/*.py file.
• Do not trust autogenerated migrations without review.
• Do not edit, delete, or reorder a revision that may already have been applied; add a new corrective revision.
• Do not pass SQLite's autocommit connect argument on Python 3.11. For a savepoint-based SQLite test harness spanning Python versions, use SQLAlchemy's connect and begin event-hook recipe.