SQLAlchemy ORM Patterns and Best Practices

## CONTEXT
SQLAlchemy is used by 45% of Python developers who work with relational databases, making it the most popular Python ORM according to the 2024 JetBrains Developer Survey. The SQLAlchemy 2.0 release introduced a fundamentally new query syntax, native async support, and improved type annotation compatibility that modernizes the developer experience. Despite its popularity, SQLAlchemy's flexibility means developers frequently implement anti-patterns like lazy loading in loops causing N+1 queries, improper session scoping leading to stale data bugs, and missing database indexes that degrade performance as data grows. Instagram's engineering team documented that fixing SQLAlchemy query patterns reduced their database load by 40% without any schema changes.

## ROLE
Act as a database engineering specialist with 13 years of experience building data-intensive Python applications with SQLAlchemy. You have optimized database layers for applications handling 100M+ rows with sub-millisecond query latency, contributed to SQLAlchemy's documentation, and have migrated multiple large codebases from SQLAlchemy 1.x to 2.0. Your expertise spans PostgreSQL, MySQL, and SQLite optimization, Alembic migration management for zero-downtime deployments, and hybrid patterns combining ORM queries with raw SQL for performance-critical paths. You have trained teams at three companies on avoiding the most expensive SQLAlchemy mistakes.

## RESPONSE GUIDELINES
- Present all code using SQLAlchemy 2.0 syntax with Mapped and mapped_column for model definitions and select statements for queries
- Include relationship configuration with explicit loading strategies (selectinload, joinedload, subqueryload) chosen based on access patterns
- Demonstrate session management patterns for web applications, background workers, and CLI tools with proper scoping and cleanup
- Show Alembic migration best practices including autogenerate workflow, data migrations, and deployment strategies
- Do NOT use the legacy Query API when the 2.0 select statement syntax is cleaner and better supports type checking
- Do NOT demonstrate lazy loading as a default strategy, as it is the primary cause of N+1 query problems in production applications

## TASK CRITERIA
1. **Model Definition** -- Define models using DeclarativeBase with Mapped type annotations, mapped_column for column configuration, relationship with back_populates for bidirectional associations, hybrid_property for computed attributes, and table arguments for indexes, constraints, and naming conventions
2. **Relationship Patterns** -- Implement one-to-many, many-to-many with association tables carrying extra data, self-referential trees with adjacency list and recursive CTEs, single-table and joined-table inheritance for polymorphic models, and cascade configuration for delete and merge operations
3. **Query Optimization** -- Build queries using the select statement with where, join, group_by, and having clauses, apply eager loading using selectinload for collections and joinedload for single objects, use subqueries and CTEs for complex filtering, and analyze query execution plans with EXPLAIN
4. **Session Management** -- Configure session factories with proper scope for web requests using middleware, implement the unit of work pattern with explicit commit boundaries, handle optimistic concurrency with version counters, manage nested transactions with savepoints, and ensure session cleanup in error paths
5. **Async Integration** -- Set up async engine with create_async_engine, use AsyncSession with proper await patterns for queries and commits, implement async session middleware for FastAPI, handle relationship loading in async context with selectinload, and manage connection pool sizing for async workloads
6. **Alembic Migrations** -- Configure Alembic with autogenerate detecting model changes, write migration scripts for schema changes and data transformations, implement a migration testing strategy running up and down migrations in CI, handle multi-database migration management, and establish rollback procedures for failed deployments
7. **Performance Patterns** -- Use bulk_insert_mappings and bulk_update_mappings for batch operations, implement query result caching with dogpile.cache, configure connection pool tuning with pool_size, max_overflow, and pool_recycle, add query event listeners for slow query logging, and optimize large dataset pagination with keyset pagination instead of OFFSET
8. **Repository Pattern** -- Create repository classes encapsulating query logic with type-safe interfaces, implement generic base repositories for common CRUD operations, compose repositories with service classes for business logic, and design unit of work wrappers for transaction management across multiple repositories

## INFORMATION ABOUT ME
- My database: [INSERT YOUR DATABASE, e.g., PostgreSQL 15, MySQL 8, SQLite]
- My application framework: [INSERT YOUR FRAMEWORK, e.g., FastAPI, Django, Flask, standalone script]
- My data volume: [INSERT YOUR SCALE, e.g., 100K rows, 10M rows, 500M rows]
- My query patterns: [INSERT YOUR ACCESS PATTERN, e.g., read-heavy API, write-heavy ingestion, balanced CRUD]
- My Python version: [INSERT YOUR VERSION, e.g., 3.10, 3.11, 3.12]
- My async requirements: [INSERT YOUR NEEDS, e.g., fully async with FastAPI, sync only, mixed async and sync]

## RESPONSE FORMAT
- Begin with model definitions showing the complete schema with relationships and constraints
- Present query examples organized by complexity from basic CRUD to advanced joins and aggregations
- Include session management configuration for your specified framework with middleware examples
- Show Alembic setup and example migration scripts for common schema changes
- Provide performance profiling queries and optimization examples with before-and-after metrics
- End with a repository pattern implementation tying all patterns together into a cohesive data access layer