Expert guidance for SQLAlchemy 2.0 + Pydantic + PostgreSQL. Use when setting up database layers, defining models, creating migrations, or any database-related work. Automatically activated for DB tasks.

<essential principles SQLAlchemy 2.0 + Pydantic + PostgreSQL Best Practices This skill provides expert guidance for building production ready database layers. Stack SQLAlchemy 2.0 with async support (asyncpg driver) Pydantic v2 for validation and serialization Alembic for migrations PostgreSQL only Core Principles 1. Separation of Concerns 2. Type Safety First Always use SQLAlchemy 2.0 style with Mapped[] type annotations: 3. Async by Default Use async engine and sessions for FastAPI: 4. Pydantic SQLAlchemy Bridge Keep models and schemas separate but mappable: 5. Repository Pattern Abstract database operations for testability and clean code. </essential principles <intake What do you need help with? 1. Setup database layer Initialize SQLAlchemy + Pydantic + Alembic from scratch 2. Define models Create SQLAlchemy models with Pydantic schemas 3. Create migration Generate and manage Alembic migrations 4. Query patterns Async CRUD, joins, eager loading, optimization 5. Full implementation Complete database layer for a feature </intake <routing Response Workflow 1, "setup", "initialize", "start" workflows/setup database.md 2, "model", "define", "create model" workflows/define models.md 3, "migration", "alembic", "schema change" workflows/create migration.md 4, "query", "crud", "repository" workflows/query patterns.md 5, "full", "complete", "feature" Run setup → define models → create migration Auto detection triggers (use this skill when user mentions): database, db, sqlalchemy, postgres, postgresql model, migration, alembic repository, crud, query async session, connection pool </routing <reference index Domain Knowledge Reference Purpose references/best practices.md Production patterns, security, performance references/patterns.md Repository, Unit of Work, common queries references/async patterns.md Async session management, FastAPI integration </reference index <workflows index Workflow Purpose workflows/setup database.md Initialize complete database layer workflows/define models.md Create models + schemas + relationships workflows/create migration.md Alembic migration workflow workflows/query patterns.md CRUD operations and optimization </workflows index <quick reference File Structure Essential Imports Connection String </quick reference <success criteria Database layer is complete when: [ ] Async engine and session factory configured [ ] Base model with common fields (id, created at, updated at) [ ] Models use Mapped[] type annotations [ ] Pydantic schemas with from attributes=True [ ] Alembic configured for async [ ] Repository pattern implemented [ ] FastAPI dependency for session injection [ ] Connection pooling configured for production </success criteria

sqlalchemy-postgres