python-database-patterns_skill

Overview

This skill provides practical SQLAlchemy 2.0 patterns and database best practices for Python projects, covering sync and async engines, ORM models, queries, relationships, and FastAPI integration. It bundles recommended patterns for connection pooling, migrations (Alembic), and transaction handling to help teams build reliable data access layers. The content focuses on concrete examples and quick references that you can apply directly in applications.

How this skill works

It inspects common database tasks and provides idiomatic SQLAlchemy 2.0 code for model declarations, queries, relationships, and session usage both synchronous and asynchronous. It also documents integration points (FastAPI DI), connection pool configuration, and migration strategies with Alembic. The skill groups patterns into snippets, quick-reference operations, and linked deeper references for sessions, pooling, transactions, and migrations.

When to use it

• When setting up new ORM models and relationships with SQLAlchemy 2.0
• When migrating synchronous DB code to async SQLAlchemy with asyncpg
• When integrating DB sessions into FastAPI routes using dependency injection
• When configuring connection pools, health checks, or tuning performance
• When planning Alembic migrations or adopting repository/unit-of-work patterns

Best practices

• Use DeclarativeBase and mapped_column annotations for clear, typed models
• Prefer async_sessionmaker and create_async_engine for async apps; set expire_on_commit=False
• Keep sessions short-lived per request (dependency injection in FastAPI) and avoid long-lived sessions
• Use explicit transactions and unit-of-work boundaries for multi-step changes
• Eager-load relationships with selectinload when fetching collections to avoid N+1 queries

Example use cases

• Building an async REST API with FastAPI and async SQLAlchemy sessions per request
• Writing repository classes that encapsulate queries and unit-of-work transaction scope
• Tuning pool_size and max_overflow on create_async_engine for predictable concurrency
• Creating Alembic migration templates and scripted workflows for schema evolution
• Implementing common query filters, aggregates, joins, and pagination using 2.0 style select