sqlalchemy-postgres

cfircoo's avatarfrom cfircoo

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.

⭐0stars🔀0forks📁View on GitHub🕐Updated Jan 3, 2026⬇️1downloads
[SQL and Databases]

When & Why to Use This Skill

This Claude skill provides expert guidance for building production-ready database layers using SQLAlchemy 2.0, Pydantic v2, and PostgreSQL. It streamlines backend development by enforcing modern best practices such as async-first operations, type-safe model definitions with Mapped annotations, and the repository pattern for clean, maintainable architecture.

Use Cases

  • Initializing a robust, async-capable database infrastructure for FastAPI or other modern Python web frameworks.
  • Defining type-safe SQLAlchemy 2.0 models and seamlessly mapping them to Pydantic v2 schemas for API serialization.
  • Managing complex database schema evolutions and migrations safely using Alembic in an asynchronous environment.
  • Implementing the Repository pattern to abstract data access, improving code testability and separation of concerns.
  • Optimizing database performance through advanced query patterns, eager loading, and efficient connection pooling.
namesqlalchemy-postgres
descriptionExpert 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.
## 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

models/       # SQLAlchemy ORM models (database layer)
schemas/      # Pydantic schemas (API layer)
repositories/ # Data access patterns
services/     # Business logic

2. Type Safety First Always use SQLAlchemy 2.0 style with Mapped[] type annotations:

from sqlalchemy.orm import Mapped, mapped_column

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

3. Async by Default Use async engine and sessions for FastAPI:

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
engine = create_async_engine("postgresql+asyncpg://...")

4. Pydantic-SQLAlchemy Bridge Keep models and schemas separate but mappable:

# Schema reads from ORM
class UserRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)

5. Repository Pattern Abstract database operations for testability and clean code.

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
| 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
## 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
| 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 | ## File Structure ``` src/ ├── db/ │ ├── __init__.py │ ├── base.py # DeclarativeBase │ ├── session.py # Engine + async session factory │ └── dependencies.py # FastAPI dependency ├── models/ │ ├── __init__.py │ └── user.py # SQLAlchemy models ├── schemas/ │ ├── __init__.py │ └── user.py # Pydantic schemas ├── repositories/ │ ├── __init__.py │ ├── base.py # Generic repository │ └── user.py # User repository └── alembic/ ├── alembic.ini ├── env.py └── versions/ ```

Essential Imports

# Models
from sqlalchemy import String, Integer, ForeignKey, DateTime
from sqlalchemy.orm import Mapped, mapped_column, relationship, DeclarativeBase

# Async
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker

# Pydantic
from pydantic import BaseModel, ConfigDict, Field

Connection String

# PostgreSQL async
DATABASE_URL = "postgresql+asyncpg://user:pass@localhost:5432/dbname"
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