sqlalchemy_skill

Overview

This skill is a practical, hands-on guide for using SQLAlchemy 2.0+ to build and operate customer support systems. It covers ORM model design for tickets, users, comments, tags, plus session management, query optimization, async FastAPI integration, and PostgreSQL best practices. The focus is real-world patterns that improve reliability, performance, and maintainability of support applications.

How this skill works

The skill inspects common support-domain data models and provides idiomatic SQLAlchemy declarative mappings with type hints, relationship patterns, and association tables. It describes synchronous and asynchronous engine/session configuration, context-managed session usage, and FastAPI dependency patterns. It also demonstrates query optimization (joinedload/selectinload), advanced filtering, pagination, and aggregation queries for analytics.

When to use it

• Designing data models for tickets, users, comments, attachments, and tags in a support app.
• Integrating SQLAlchemy with FastAPI using async sessions and asyncpg.
• Preventing N+1 query problems and tuning eager loading strategies for performance.
• Implementing robust session lifecycle, connection pooling, and error handling.
• Building complex search, filtering, pagination, and analytics queries against PostgreSQL.

Best practices

• Use DeclarativeBase with mapped_column and type hints for clear, maintainable models.
• Choose joinedload for single-row relations and selectinload for large collections to avoid N+1 queries.
• Keep expire_on_commit=False to avoid unnecessary reloading in web request lifecycles when appropriate.
• Use connection pool settings (pool_pre_ping, pool_size, max_overflow) tuned for your traffic patterns.
• Use soft-delete (deleted_at) filters consistently and include them in query builders.

Example use cases

• Fetch tickets with creator, assignee, comments, attachments, and tags in a single optimized query for a support UI.
• Implement a FastAPI dependency that yields an AsyncSession and correctly commits or rolls back on exceptions.
• Build an advanced search endpoint that supports full-text-like filters, status/priority lists, tag filters, and paginated results.
• Create dashboards with aggregation queries returning counts, trends, and SLA-related metrics grouped by date or status.
• Model many-to-many tags with an association table that records metadata like created_at.