Async SQLAlchemy 2.0+ database patterns for FastAPI including session management, connection pooling, Alembic migrations, relationship loading strategies, and query optimization. Use when implementing database models, configuring async sessions, setting up migrations, optimizing queries, managing relationships, or when user mentions SQLAlchemy, async database, ORM, Alembic, database performance, or connection pooling.
View on GitHubvanman2024/ai-dev-marketplace
fastapi-backend
plugins/fastapi-backend/skills/async-sqlalchemy-patterns/SKILL.md
February 1, 2026
Select agents to install to:
npx add-skill https://github.com/vanman2024/ai-dev-marketplace/blob/main/plugins/fastapi-backend/skills/async-sqlalchemy-patterns/SKILL.md -a claude-code --skill async-sqlalchemy-patternsInstallation paths:
.claude/skills/async-sqlalchemy-patterns/# Async SQLAlchemy Patterns
**Purpose:** Implement production-ready async SQLAlchemy 2.0+ patterns in FastAPI with proper session management, migrations, and performance optimization.
**Activation Triggers:**
- Database model implementation
- Async session configuration
- Alembic migration setup
- Query performance optimization
- Relationship loading issues
- Connection pool configuration
- Transaction management
- Database schema migrations
**Key Resources:**
- `scripts/setup-alembic.sh` - Initialize Alembic with async support
- `scripts/generate-migration.sh` - Create migrations from model changes
- `templates/base_model.py` - Base model with common patterns
- `templates/session_manager.py` - Async session factory and dependency
- `templates/alembic.ini` - Alembic configuration for async
- `examples/user_model.py` - Complete model with relationships
- `examples/async_context_examples.py` - Session usage patterns
## Core Patterns
### 1. Async Engine and Session Setup
**Database Configuration:**
```python
# app/core/database.py
from sqlalchemy.ext.asyncio import (
AsyncSession,
create_async_engine,
async_sessionmaker
)
from sqlalchemy.orm import declarative_base
from app.core.config import settings
# Create async engine
engine = create_async_engine(
settings.DATABASE_URL,
echo=settings.DEBUG,
pool_pre_ping=True, # Verify connections before using
pool_size=5, # Base number of connections
max_overflow=10, # Additional connections when pool is full
pool_recycle=3600, # Recycle connections after 1 hour
pool_timeout=30, # Wait 30s for available connection
)
# Create async session factory
AsyncSessionLocal = async_sessionmaker(
engine,
class_=AsyncSession,
expire_on_commit=False, # Keep objects usable after commit
autoflush=False, # Manual flush control
autocommit=False, # Explicit commits only
)
Base = declarative_base()
```
**Dependency Injection Pattern:**
```pyth