fastapi-sqlalchemy-standards
FastAPI + SQLAlchemy standards for this project. Use when writing or reviewing FastAPI routers, dependencies, SQLAlchemy models, or database sessions. Triggers on tasks involving get_db, Depends, ORM
SKILL.mdskills/fastapi-sqlalchemy-standards/SKILL.md
GitHubFastAPI + SQLAlchemy Standards
Project-specific rules derived from production bugs and code review findings.
1. SQLAlchemy — Always Use Modern Mapped[] Style
Python 3.12+ / SQLAlchemy 2.x project. All models must use the modern declarative style.
# ❌ BANNED — legacy Column() style
from sqlalchemy import Column, String, Boolean
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
email = Column(String, unique=True)
# ✅ REQUIRED — modern Mapped[] style
from sqlalchemy.orm import Mapped, mapped_column
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
email: Mapped[str] = mapped_column(String, unique=True)
Rule: If you see Column( in any model file, it is wrong. Rewrite it.
Typed fields
# ❌ Wrong — Mapped[object] hides the real type
created_at: Mapped[object] = mapped_column(DateTime(timezone=True), ...)
# ✅ Correct
from datetime import datetime
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), ...)
SQLite test compat — JSON vs JSONB
Always use JSON().with_variant(JSONB, "postgresql") — NOT bare JSONB — so SQLite-based tests still work:
# ❌ Breaks SQLite tests
data: Mapped[dict] = mapped_column(JSONB, nullable=True)
# ✅ Works on both PostgreSQL (prod) and SQLite (tests)
from sqlalchemy import JSON
from sqlalchemy.dialects.postgresql import JSONB
data: Mapped[dict] = mapped_column(JSON().with_variant(JSONB, "postgresql"), nullable=True)
2. get_db — Single Source of Truth
There must be exactly ONE get_db function in the entire codebase.
db/session.py ← SINGLE definition here
routers/dependencies.py ← re-import only: `from db.session import get_db`
# ❌ BANNED — duplicate definition in dependencies.py
def get_db() -> Generator[Session, None, None]:
db = SessionLocal()
try:
yield db
finally:
db.close()
# ✅ CORRECT — re-export from the canonical location
from db.session import get_db # noqa: F401 (re-export)
Why: FastAPI's dependency override system (app.dependency_overrides[get_db]) uses function object identity. Two get_db functions = two different keys = tests only override one and the app uses the other.
3. routers/dependencies.py Patterns
Never use bare except Exception in auth code
# ❌ WRONG — swallows real errors (DB crash → silently becomes 401)
try:
payload = jwt.decode(...)
...
except Exception:
raise credentials_exception
# ✅ CORRECT — only catch JWT-specific failures
from jose import JWTError
try:
payload = jwt.decode(...)
except JWTError:
raise _CREDENTIALS_401
No inline imports inside functions
# ❌ WRONG — slow, hides deps from IDE, re-evaluated each call
def get_current_user(...):
from core.security import ALGORITHM # ← inline
...
# ✅ CORRECT — top of file
from core.security import ALGORITHM
Module-level exception constants
# ❌ WRONG — allocates new HTTPException object on every request
def get_current_user(...):
credentials_exception = HTTPException(status_code=401, ...)
...
# ✅ CORRECT — create once at module level
_CREDENTIALS_401 = HTTPException(
status_code=status.HTTP_401_UNAUTHORIZED,
detail="Could not validate credentials",
headers={"WWW-Authenticate": "Bearer"},
)
Correct HTTP status for inactive users
# ❌ WRONG — 400 Bad Request implies the client sent bad data
raise HTTPException(status_code=400, detail="Inactive user")
# ✅ CORRECT — 403 Forbidden: user exists but access denied
raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="User account is deactivated")
4. Service Layer — Dependency Injection, Not Session Storage
# ❌ SMELL — service holds db session, impossible to unit test without DB
class LLMAnalysisService:
def __init__(self, db: Session):
self.db = db
self.result = self.db.query(...) # ← tightly coupled
# ✅ BETTER — inject pre-fetched data, keep service pure
class LLMAnalysisService:
def analyze(self, image_data: bytes, excel_data: bytes | None) -> dict:
... # No DB access — just logic
The router fetches data from DB, passes it to the service, and writes results back.
5. Import Ordering (isort-compatible)
# ✅ Correct order for every Python file in this project
from __future__ import annotations # ← always first if used
# stdlib
import uuid
from collections.abc import Generator
from datetime import datetime
# third-party
from fastapi import Depends, HTTPException, status
from sqlalchemy.orm import Session
# local — first-party
from core.config import settings
from db.session import get_db
from models.user import User
Run ruff check --select I --fix . to auto-fix import order.
Checklist — Before Submitting Any ORM/API Code
- All model columns use
Mapped[T]+mapped_column()— noColumn() -
Mapped[object]does not exist anywhere — use the real type -
get_dbdefined exactly once (db/session.py), re-imported everywhere else - JSONB columns use
JSON().with_variant(JSONB, "postgresql")for SQLite compat - No bare
except Exceptionin authentication/authorization paths - No inline imports inside function bodies
- HTTP 403 (not 400) for active/inactive user access denial
-
from __future__ import annotationsat top of every file usingX | Ytypes