prompts/docs/skills/fastapi-async-sqlalchemy-modernization/SKILL.md

name, description, argument-hint, x-personal-mcp

name	description	argument-hint	x-personal-mcp
fastapi-async-sqlalchemy-modernization	Create a step-by-step modernization plan for an existing FastAPI app using SQLAlchemy async patterns, context managers, and AsyncExitStack. Use when: planning migration from legacy DB setup, standardizing async engine/session lifecycles, defining transaction boundaries, and aligning with SQLAlchemy 2.x best practices.	What is your current FastAPI + SQLAlchemy setup (sync/async driver, session pattern, lifespan usage, and deployment model)?	id version tags capabilities depends_on fastapi-async-sqlalchemy-modernization 1.0.0 fastapi sqlalchemy async modernization resource://skills/fastapi-async-sqlalchemy-modernization/document

FastAPI Async SQLAlchemy Modernization Plan

Create an implementation-ready plan that brings an existing FastAPI application in line with modern async SQLAlchemy practices, with explicit resource lifecycles and deterministic cleanup using async context managers and AsyncExitStack.

Primary targets: PostgreSQL with asyncpg and SQLite with aiosqlite.

When to Use

• Existing FastAPI app has ad hoc database setup or mixed sync/async access.
• Session management is inconsistent across routes/services.
• Lifespan startup and shutdown work is spread across globals and side effects.
• Team needs a migration plan first, not immediate large-scale rewrites.

Outcome

Produce a practical modernization plan with:

• Current-state gap assessment.
• Target architecture for engine/session/transaction lifecycle.
• Branch-based migration path (low-risk staged rollout).
• Quality gates and completion checks.
• Risks, rollback strategy, and test plan.

Top-Level Concepts

Use these concepts as the planning backbone:

1. Engine lifecycle and ownership: One AsyncEngine per process for each DB URL, created once and disposed explicitly when the app lifecycle ends. See the engine lifecycle reference.
2. Session factory and scope: Use async_sessionmaker for configuration; create one AsyncSession per request or unit-of-work, never shared across concurrent tasks. See the session management reference.
3. Transaction boundaries: Prefer context-managed begin blocks for write units and explicit read-only sessions for queries. See the transaction boundaries reference.
4. Lifespan composition: Compose startup/shutdown resources with AsyncExitStack so cleanup is deterministic and ordered. See the engine lifecycle reference.
5. Dependency injection: Provide sessions via FastAPI dependencies with async generators/context managers, not globals. See the session management reference.
6. Implicit I/O control in ORM: Avoid accidental lazy loads; use explicit eager-loading/refresh strategies for asyncio safety. See the implicit I/O reference.
7. Observability and resilience: Add pool/connection settings, logging, timeout, and health checks as first-class plan items. See the observability reference.

Concept Reference Map

Concept	Reference
Engine lifecycle and ownership	Engine lifecycle reference
Session factory and scope	Session management reference
Transaction boundaries	Transaction boundaries reference
Lifespan composition	Engine lifecycle reference
Dependency injection	Session management reference
Implicit I/O control in ORM	Implicit I/O reference
Observability and resilience	Observability reference

Decision Points

Use these branching decisions before proposing migration steps.

Decision	Branch A	Branch B
DB driver	Already async driver (e.g. asyncpg, aiosqlite): modernize in place	Sync driver: plan driver migration first
ORM usage	Already ORM 2.x style (select, session.execute)	Legacy Query API: add compatibility stage and refactor incrementally
Session scope	Request-scoped already	Global/shared sessions found: prioritize session-scope fix first
Lifespan	Existing FastAPI lifespan hook	No lifespan hook: introduce lifespan before broader DB changes
Concurrency	Background jobs/tasks use DB	No background DB use
Transaction style	Explicit context-managed transactions	Implicit/autobegin side effects

Procedure

Step 0: Audit Current State

Inventory the app and write a concise gap list.

• Engine creation location(s) and count.
• Driver URL(s) and async compatibility.
• Session creation patterns in routes/services/background tasks.
• Transaction handling style (explicit begin/commit/rollback vs implicit).
• Lifespan startup/shutdown and cleanup behavior.
• ORM loading patterns that may trigger implicit I/O.

Completion check: every DB touchpoint is mapped to its engine, session, and transaction source.

Step 1: Define the Target Runtime Model

Define one canonical model to migrate toward.

• Create AsyncEngine once per process.
• Configure async_sessionmaker once.
• Use per-request AsyncSession dependency.
• Keep one AsyncSession per concurrent task.
• Use context-managed transactions for writes.

Completion check: architecture diagram can explain where engine/session are created, used, and closed.

Step 2: Plan Engine Modernization

Plan engine creation and pool behavior.

• Use create_async_engine() with async dialect URL.
• Standardize pool settings and pre-ping strategy where relevant.
• Decide isolation level strategy at engine level (avoid ad hoc per-operation switching unless justified).
• Define explicit disposal policy for short-lived scopes and tests.

Completion check: engine configuration is centralized and no per-request engine creation remains.

Step 3: Plan Session Lifecycle Modernization

Define session factory and request dependency pattern.

• Build async_sessionmaker(engine, expire_on_commit=False) unless a strict reason says otherwise.
• Provide session via dependency that yields exactly one AsyncSession.
• Explicitly prohibit sharing a single AsyncSession across concurrent tasks.
• Prefer direct dependency passing over async_scoped_session for new designs.

Completion check: all route/service entry points receive a session from one canonical dependency.

Step 4: Plan Transaction Demarcation

Establish consistent write and read behavior.

• Writes: async with session.begin(): ... for atomic units.
• Reads: execute in managed session context with explicit loader options.
• Nested/SAVEPOINT use only where required; call out backend caveats.
• Define rollback behavior for service-layer exceptions.

Completion check: every mutating use case has a declared transaction boundary.

Step 5: Compose Lifespan with AsyncExitStack

Use async context composition as the preferred orchestration pattern.

from contextlib import AsyncExitStack, asynccontextmanager
from fastapi import FastAPI

@asynccontextmanager
async def lifespan(app: FastAPI):
    async with AsyncExitStack() as stack:
        # Compose resources in acquisition order; cleanup is automatic in reverse order.
        engine = create_async_engine(settings.database_url)
        stack.push_async_callback(engine.dispose)

        session_factory = async_sessionmaker(engine, expire_on_commit=False)
        app.state.session_factory = session_factory

        # Add other async resources with stack.enter_async_context(...) as needed.
        yield

Planning rules:

• Register every acquired resource with AsyncExitStack at acquisition time.
• Prefer enter_async_context() for resources that already expose async context managers.
• Prefer push_async_callback() for async cleanup callables.
• Keep resource ownership in lifespan, not in route handlers.

Completion check: startup/shutdown ordering is explicit and deterministic.

Step 6: Prevent Implicit ORM I/O Under Asyncio (Advisory Mode)

Plan for explicit loading behavior, but treat this as progressive guidance rather than a hard gate.

• Recommend eager-loading strategies (for example selectin-style loading) where relationship access is required.
• For lazy/deferred attributes, define explicit awaitable or refresh paths on high-risk and high-traffic paths first.
• Document model-level defaults and known exceptions so teams can migrate incrementally.

Completion check: critical request paths have explicit loading plans; non-critical paths have tracked follow-up items.

Step 7: Testing and Verification Plan

Create modernization quality gates.

• Unit tests for session dependency and transaction behavior.
• Integration tests for commit/rollback semantics.
• Concurrency tests confirming one-session-per-task behavior.
• Lifespan tests verifying cleanup calls and ordering.
• Health/readiness tests including DB connectivity checks.

Completion check: all quality gates pass under the target async configuration.

Step 8: Rollout Strategy

Plan low-risk migration phases.

1. Introduce centralized engine/session factory and lifespan orchestration.
2. Migrate read paths to new session dependency.
3. Migrate write paths to explicit transaction blocks.
4. Remove legacy globals/helpers and dead code.
5. Enable stricter linting/review checks for forbidden patterns.

Completion check: no legacy session/engine creation path remains in production code.

Quality Criteria

A plan is complete only when it includes:

• Clear current vs target architecture.
• Branch decisions with rationale.
• Explicit context-manager patterns for resource ownership.
• AsyncExitStack composition strategy.
• Transaction policy and exception behavior.
• Concrete tests and rollout checkpoints.
• A documented advisory backlog for non-critical implicit I/O improvements.

Anti-Patterns to Flag

• Creating engines inside request handlers.
• Sharing one AsyncSession across concurrent tasks.
• Implicit commit/rollback behavior with unclear ownership.
• Global mutable session state.
• Lifespan cleanup that depends on implicit garbage collection.

Output Contract

Return the plan as:

1. Current-state gap summary.
2. Target architecture summary.
3. Phased migration checklist with branch notes.
4. Risk register and rollback approach.
5. Verification matrix (tests + operational checks).

References

!!! info "Primary sources"

• SQLAlchemy engine and connections
• SQLAlchemy asyncio extension
• Python async context managers and AsyncExitStack