FastAPI + Python + PostgreSQL (Async SQLAlchemy & Pydantic v2)

Curated rule Developer Tool & API · AI / LLM App · SaaS / Web App Updated Sep 2026 Which file does my tool read?

Production guidelines for FastAPI, Pydantic v2 validation, Async SQLAlchemy 2.0 (asyncpg), Alembic migrations, and PostgreSQL/Redis.

FastAPI Python PostgreSQL SQLAlchemy Alembic Pydantic Redis

Formats: 4 files
AGENTS.md: 63 lines
CLAUDE.md: 17 lines
Languages: Python
Updated: Sep 2026
Used by: 4 projects

Install

Writes .claude/skills/fastapi-python-postgres/SKILL.md

$ curl -s --create-dirs -o .claude/skills/fastapi-python-postgres/SKILL.md https://stackitfast.com/rules/fastapi-python-postgres/SKILL.md

Rule files

AGENTS.md· 63 lines · 3.2 KB

1	# Project Architecture & Guidelines (FastAPI + Async SQLAlchemy + PostgreSQL)
2
3	## 1. System Architecture
4	- Framework: FastAPI (ASGI with Uvicorn / Gunicorn).
5	- Data Validation: Pydantic v2 (`BaseModel`, `Field`, `ConfigDict`).
6	- Database & ORM: PostgreSQL via Async SQLAlchemy 2.0 (`asyncpg` driver).
7	- Schema Migrations: Alembic (`alembic revision --autogenerate`, `alembic upgrade head`).
8
9	## 2. Async Connection Pool & Session Management (Critical)
10	- Configure `create_async_engine` with explicit pool settings:
11	```python
12	from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
13
14	engine = create_async_engine(
15	settings.DATABASE_URL,
16	pool_size=10,
17	max_overflow=20,
18	pool_timeout=30,
19	pool_pre_ping=True, # Validates stale connections before use
20	)
21
22	async_session = async_sessionmaker(
23	engine,
24	class_=AsyncSession,
25	expire_on_commit=False,
26	)
27	```
28	- Dependency Injection: Always yield database sessions in FastAPI route handlers:
29	```python
30	async def get_db() -> AsyncGenerator[AsyncSession, None]:
31	async with async_session() as session:
32	try:
33	yield session
34	except Exception:
35	await session.rollback()
36	raise
37	```
38
39	## 3. SQLAlchemy 2.0 Syntax & Relational Queries
40	- Use modern 2.0 `select()` syntax with `scalars().all()` or `scalar_one_or_none()`. Do NOT use legacy 1.4 `session.query()`.
41	- To prevent async lazy-loading errors (`MissingGreenlet`), always use `selectinload()` or `joinedload()` for relations.
42
43	## 4. Input Validation & Error Handling
44	- Define separate Pydantic schemas for `Create`, `Update`, and `Response` objects.
45	- Set `response_model` on all FastAPI router decorators for automatic response serialization.
46	- Use custom `HTTPException` with standardized JSON error bodies.
47
48	## 5. Common Pitfalls to Avoid
49	- ❌ Mixing sync database drivers with async event loops (always use `postgresql+asyncpg://`).
50	- ❌ Lazy loading outside greenlet: Always eager load relations in async SQLAlchemy.
51	- ❌ Blocking operations in `async def` endpoints: Use `run_in_threadpool` or background Celery tasks for heavy CPU workloads.
52
53	## 6. Testing Conventions
54	- Use `pytest` with `pytest-asyncio` (`asyncio_mode = "auto"`) and `httpx.AsyncClient` for testing FastAPI routes end-to-end.
55	- Override the `get_db` dependency with a transactional test session that rolls back after every test — never let tests commit against the real database.
56	- 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.
57	- Run `mypy .` in CI alongside `pytest`; async SQLAlchemy's `MissingGreenlet` errors are far easier to catch statically than at runtime.
58
59	## 7. Git Workflow & PR Conventions
60	- Conventional Commits (`feat:`, `fix:`, `refactor:`) scoped to the router or domain module, e.g. `fix(auth): refresh expired JWT correctly`.
61	- Alembic migrations ship in the same PR as the SQLAlchemy model change that generated them.
62	- Require `pytest`, `ruff check .`, and `alembic upgrade head --sql` (dry-run) to pass before merge.
63	- Rebase feature branches onto `main`; never merge with unresolved Alembic branch-point conflicts.

Works with Cursor · Claude Code · Windsurf · AGY

Architecture notes

Architecture Overview

Production conventions for high-throughput, async-first Python microservices and AI agent backends using FastAPI, SQLAlchemy 2.0, and PostgreSQL.

Verified Real-World Adoption

This architecture is deployed in production by AI and data platforms including Mem0, OpenBB, Baserow, and Open WebUI.

Key Architectural Nuances

Async Connection Pooling: Leverages asyncpg with pool_pre_ping=True for resilient connection lifecycle management.
Strict Separation of DTOs: Pydantic v2 ensures fast serialization and automated OpenAPI documentation.

Frequently asked questions

Does this AGENTS.md work with Cursor, Claude Code, and Windsurf?

Yes — AGENTS.md is the open, cross-tool standard read by Cursor, Claude Code, Windsurf, and 30+ other agents. A dedicated .mdc file is also included for Cursor's native .cursor/rules format.

Why does async SQLAlchemy throw MissingGreenlet errors?

Lazy-loaded relationships trigger a synchronous database call under the hood, which async SQLAlchemy can't execute outside the greenlet context it uses to bridge sync and async code. Eager-loading the relation up front with selectinload() or joinedload() avoids the lazy trigger entirely, so there's no synchronous call left to fail.

Why use asyncpg instead of psycopg2 for FastAPI?

psycopg2 is a synchronous driver — using it inside an async def route would block the entire event loop on every query, defeating the purpose of async FastAPI. asyncpg is built for asyncio from the ground up and is also one of the fastest Postgres drivers available in Python.

Used in production

Explore all stacks

MindsHub (formerly MindsDB)

Desktop & Web AI Agent Workspace · 20+ People

Splitting the client, API and agent into separately versioned git submodules lets each ship on its own cadence (a signed desktop app, a PyPI server package, an agent package), while one Makefile and one docker-compose.yml still bring the whole stack up together.

VoiceStudio

Local AI Voice Cloning & Speech Studio · 2-5 people

Heavy model work stays in a Python FastAPI backend with a pluggable, hardware-aware engine registry. Desktop, web, MCP and remote-worker clients all use the same local API, so voice models run on the user's own GPU without a cloud dependency.

LangChain

LLM Application Framework & Agent Orchestrator · 1M+ MAU

The current langchain package is a thin agent-building layer on top of LangGraph, with the older chain-based API kept separately as langchain-classic, and every model provider ships as its own versioned package so integrations can release independently of the core library.

FastAPI

High-Performance Python Web API Framework · 1M+ MAU

FastAPI keeps its own dependency surface to Starlette and Pydantic and pushes servers, CLI, templating, forms and settings into an optional 'standard' extra, so a bare install stays minimal while typical apps opt into one bundle instead of assembling it dependency by dependency.

Where this stack fits

Stack It First recommends it at:

Backend · MVP · Data & AI

Walk the backend roadmap

Every stack ships as files

More AI agent coding rules and skills

View all

astro-static-content/AGENTS.mdAstro Static Site + Content Collections + TailwindAstro · Tailwind CSS · TypeScript bun-hono-sqlite/AGENTS.mdBun + Hono + SQLite (libSQL & Drizzle ORM)Bun · Hono · SQLite django-postgres-redis/AGENTS.mdDjango + PostgreSQL + Redis + CeleryDjango · Python · PostgreSQL fastapi-jev-decision-service/AGENTS.mdFastAPI + Jev Decision Service (Python)Python · FastAPI · Jev