sqlalchemy-postgres
This skill provides expert guidance for building production-ready database layers using SQLAlchemy 2.0 with async support, Pydantic v2 validation, and PostgreSQL. It covers initialization, model definition with type-safe annotations, Alembic migrations, async CRUD patterns, and repository architecture for clean separation of concerns.
sqlalchemy-postgres guides you through building async database layers with SQLAlchemy 2.0, Pydantic, and PostgreSQL.
AI-generated summary based on this skill's SKILL.md
Install
cfircoo/claude-code-toolkit/sqlalchemy-postgres · repository language: Shell
git clone https://github.com/cfircoo/claude-code-toolkit
cp -r claude-code-toolkit/skills/sqlalchemy-postgres ~/.claude/skills/sqlalchemy-postgresnpx skillfed install cfircoo/claude-code-toolkit/sqlalchemy-postgresFrequently asked questions
AI-generated answers based on this skill's SKILL.md and metadata
How do I set up SQLAlchemy 2.0 with async PostgreSQL?
sqlalchemy-postgres guides you through initializing a production-ready async database layer with SQLAlchemy 2.0 and PostgreSQL. Start by configuring an async engine using `create_async_engine()` with an asyncpg driver URL, set up an async session factory with `AsyncSession`, and establish connection pooling. The skill covers proper initialization patterns, environment configuration, and best practices for async database connectivity in production environments.
What is the sqlalchemy 2.0 postgres setup process?
sqlalchemy-postgres teaches the complete setup workflow: install SQLAlchemy 2.0+ with asyncpg, configure your PostgreSQL connection string, create an async engine with appropriate pool settings, initialize an AsyncSession factory, and integrate with your application framework. The skill emphasizes type-safe configuration, connection pooling optimization, and production-ready patterns for reliable database initialization.
How do I use SQLAlchemy with Pydantic for validation?
sqlalchemy-postgres shows how to define SQLAlchemy ORM models with proper type annotations, then create corresponding Pydantic v2 schemas for validation and serialization. The skill covers deriving Pydantic models from SQLAlchemy models, handling relationships, configuring validation rules, and maintaining schema consistency. This approach provides type safety, runtime validation, and clean separation between database and API layers.
How should I implement async CRUD operations in sqlalchemy-postgres?
sqlalchemy-postgres provides patterns for implementing CRUD operations using async sessions. The skill covers creating, reading, updating, and deleting records with async/await syntax, using repository patterns for clean abstraction, executing queries with proper session management, and handling transactions. It emphasizes dependency injection for FastAPI integration, error handling, and query optimization for production applications.
What is the Alembic migration workflow with sqlalchemy-postgres?
sqlalchemy-postgres guides you through creating and managing database migrations using Alembic. The skill covers initializing Alembic, auto-generating migrations from SQLAlchemy models, writing custom migrations, managing version control, and executing migrations safely in production. It includes best practices for schema evolution, rollback strategies, and maintaining migration history alongside your codebase.
How do I integrate SQLAlchemy with FastAPI using dependency injection?
sqlalchemy-postgres demonstrates integrating SQLAlchemy's async sessions with FastAPI's dependency injection system. The skill covers creating session dependencies, managing session lifecycle within request handlers, using repository patterns for data access, and ensuring proper resource cleanup. This integration enables clean, testable API endpoints with type-safe database access and automatic session management per request.
SKILL.md
rendered from the published skill — quoted content, verbatim
<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
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
(truncated - see the full file via the links below)
Read as markdown · JSON record · Browse the source repository
File tree — 8 files
skills/sqlalchemy-postgres/SKILL.md
skills/sqlalchemy-postgres/references/async-patterns.md
skills/sqlalchemy-postgres/references/best-practices.md
skills/sqlalchemy-postgres/references/patterns.md
skills/sqlalchemy-postgres/workflows/create-migration.md
skills/sqlalchemy-postgres/workflows/define-models.md
skills/sqlalchemy-postgres/workflows/query-patterns.md
skills/sqlalchemy-postgres/workflows/setup-database.md