---
name: fastapi-python-postgres
description: "Use when building, refactoring, or reviewing a FastAPI + Python + PostgreSQL (Async SQLAlchemy & Pydantic v2) project (FastAPI, Python, PostgreSQL, SQLAlchemy, Alembic, Pydantic, Redis). Production guidelines for FastAPI, Pydantic v2 validation, Async SQLAlchemy 2.0 (asyncpg), Alembic migrations, and PostgreSQL/Redis."
license: MIT
metadata:
  source: https://stackitfast.com/rules/fastapi-python-postgres
  version: "2026-09-10"
---

# FastAPI + Python + PostgreSQL (Async SQLAlchemy & Pydantic v2) — Agent Skill

## When to use this skill
- Any task that scaffolds, modifies, refactors, or reviews code in a FastAPI + Python + PostgreSQL (Async SQLAlchemy & Pydantic v2) codebase.
- Whenever the project depends on FastAPI, Python, PostgreSQL, SQLAlchemy, Alembic, Pydantic, Redis.
- Apply these guidelines before proposing architecture, database, or deployment changes.

## Guidelines
# Project Architecture & Guidelines (FastAPI + Async SQLAlchemy + PostgreSQL)

## 1. System Architecture
- **Framework**: FastAPI (ASGI with Uvicorn / Gunicorn).
- **Data Validation**: Pydantic v2 (`BaseModel`, `Field`, `ConfigDict`).
- **Database & ORM**: PostgreSQL via Async SQLAlchemy 2.0 (`asyncpg` driver).
- **Schema Migrations**: Alembic (`alembic revision --autogenerate`, `alembic upgrade head`).

## 2. Async Connection Pool & Session Management (Critical)
- Configure `create_async_engine` with explicit pool settings:
  ```python
  from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

  engine = create_async_engine(
      settings.DATABASE_URL,
      pool_size=10,
      max_overflow=20,
      pool_timeout=30,
      pool_pre_ping=True, # Validates stale connections before use
  )

  async_session = async_sessionmaker(
      engine,
      class_=AsyncSession,
      expire_on_commit=False,
  )
  ```
- Dependency Injection: Always yield database sessions in FastAPI route handlers:
  ```python
  async def get_db() -> AsyncGenerator[AsyncSession, None]:
      async with async_session() as session:
          try:
              yield session
          except Exception:
              await session.rollback()
              raise
  ```

## 3. SQLAlchemy 2.0 Syntax & Relational Queries
- Use modern 2.0 `select()` syntax with `scalars().all()` or `scalar_one_or_none()`. Do NOT use legacy 1.4 `session.query()`.
- To prevent async lazy-loading errors (`MissingGreenlet`), always use `selectinload()` or `joinedload()` for relations.

## 4. Input Validation & Error Handling
- Define separate Pydantic schemas for `Create`, `Update`, and `Response` objects.
- Set `response_model` on all FastAPI router decorators for automatic response serialization.
- Use custom `HTTPException` with standardized JSON error bodies.

## 5. Common Pitfalls to Avoid
- ❌ Mixing sync database drivers with async event loops (always use `postgresql+asyncpg://`).
- ❌ Lazy loading outside greenlet: Always eager load relations in async SQLAlchemy.
- ❌ Blocking operations in `async def` endpoints: Use `run_in_threadpool` or background Celery tasks for heavy CPU workloads.

## 6. Testing Conventions
- Use `pytest` with `pytest-asyncio` (`asyncio_mode = "auto"`) and `httpx.AsyncClient` for testing FastAPI routes end-to-end.
- Override the `get_db` dependency with a transactional test session that rolls back after every test — never let tests commit against the real database.
- Test Pydantic schema validation boundaries explicitly (missing required fields, wrong types, out-of-range values) since these are the first line of defense against bad input.
- Run `mypy .` in CI alongside `pytest`; async SQLAlchemy's `MissingGreenlet` errors are far easier to catch statically than at runtime.

## 7. Git Workflow & PR Conventions
- Conventional Commits (`feat:`, `fix:`, `refactor:`) scoped to the router or domain module, e.g. `fix(auth): refresh expired JWT correctly`.
- Alembic migrations ship in the same PR as the SQLAlchemy model change that generated them.
- Require `pytest`, `ruff check .`, and `alembic upgrade head --sql` (dry-run) to pass before merge.
- Rebase feature branches onto `main`; never merge with unresolved Alembic branch-point conflicts.

## Source
Maintained at https://stackitfast.com/rules/fastapi-python-postgres — also available as AGENTS.md, CLAUDE.md, and Cursor .mdc.