SQLAlchemy recipes
14 tested SQLAlchemy integration recipes with Alembic, SQLite, aiosqlite, pytest-asyncio. Exact pinned versions and a last-passed receipt, last run 2026-10-15.
Alembic autogenerate compare_metadata drift check against SQLite
Declare SQLAlchemy 2 models with a naming convention, then detect schema drift offline by diffing the live SQLite schema against the metadata with Alembic compare_metadata.
Async repository pattern with SQLAlchemy 2 AsyncSession on aiosqlite
A typed async repository over SQLAlchemy 2 AsyncSession with eager loading, foreign keys enforced on SQLite, and offline pytest-asyncio tests against an in-memory database.
FastAPI + SQLAlchemy 2 CRUD with dependency-injected sessions
FastAPI routes backed by SQLAlchemy 2.x typed models, a per-request session dependency, and TestClient tests on SQLite.
FastAPI with async SQLAlchemy 2 on aiosqlite, tested offline with per-test in-memory DBs
A FastAPI app using AsyncSession and async_sessionmaker on aiosqlite, tested with httpx.AsyncClient over ASGITransport, overriding the session dependency with a fresh in-memory database for each test.
Flask 3 app factory + Flask-SQLAlchemy
Flask application factory with Flask-SQLAlchemy 3, a JSON API and pytest fixtures using an in-memory database.
Flask API with Flask-SQLAlchemy and marshmallow-sqlalchemy, validation errors as 422
A Flask JSON API that loads and dumps SQLAlchemy models through SQLAlchemyAutoSchema, returns validation errors as 422, and is tested offline with the Flask test client on in-memory sqlite.
Flask-SQLAlchemy models with Flask-Migrate upgrades on sqlite in tests
Define Flask-SQLAlchemy models, version the schema with Flask-Migrate, and test upgrade, downgrade and model/migration drift offline on a temporary sqlite file.
pandas to_sql and read_sql with a SQLAlchemy 2 engine on sqlite, with dtype handling
Write and read DataFrames through a SQLAlchemy 2 engine, control column types with dtype, and handle datetimes and nullable ints, tested offline on in-memory sqlite.
pytest fixtures + factory_boy with SQLAlchemy 2 sessions
Transactional pytest fixtures that roll back per test, plus factory_boy SQLAlchemy factories with sequences, SubFactory and traits.
SQLAlchemy 2 + Alembic programmatic migrations
Alembic migrations driven from Python (upgrade, downgrade, autogenerate check) against SQLite, with SQLAlchemy 2 models.
SQLAlchemy 2 async ORM + aiosqlite with pytest-asyncio
SQLAlchemy 2.x typed async ORM (Mapped, mapped_column, async_sessionmaker, selectinload) on aiosqlite, with pytest-asyncio fixtures.
SQLAlchemy 2 ORM rows serialized with Pydantic v2 from_attributes
Map SQLAlchemy 2 models, expose nested relationships through Pydantic v2 schemas with from_attributes=True and a computed_field, and test them offline against in-memory SQLite.
SQLAlchemy 2.0 hybrid properties, column_property and before_flush audit events
Map hybrid_property and column_property on SQLAlchemy 2.0 models, stamp audit timestamps with a before_flush listener, and test everything offline on in-memory SQLite.
SQLModel one-to-many and many-to-many relationships tested on in-memory SQLite
Model one-to-many and link-table many-to-many relationships with SQLModel, load them with selectinload, and test offline on in-memory SQLite with StaticPool.
Used together with SQLAlchemy
AlembicSQLiteaiosqlitepytest-asyncioFastAPIhttpxFlaskFlask-SQLAlchemymarshmallow-sqlalchemymarshmallowflask-migratepandaspytestfactory-boyPydanticSQLModel