FastAPI + SQLAlchemy 2.0: Why the “code that just works” you write creates hell
Can you believe that ``Even if you forget `await` in an asynchronous session of SQLAlchemy, no error may occur''? To be honest, I'm repulsed by the sloppyness of code written by humans. You're ignoring rudimentary bugs that AI would be able to detect in an instant, thinking, ``It'll work somehow!'' and inviting the hell of connection depletion in the production environment.
Come on, look at the reality. That code you wrote is just running dead.
1. Destructive session management: Don't starve connections
Place `SessionLocal` globally and `yield` for each request. That in itself is standard, but what you guys do is a series of "missing closures" and "pointless re-queries."
Robust session handler correct answer
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from typing import AsyncGenerator
# Don't forget to tune pool_size and max_overflow. It would be suicidal to operate with the default settings.
engine = create_async_engine(
"postgresql+asyncpg://user:pass@localhost/dbname",
echo=False,
pool_size=20,
max_overflow=10,
pool_pre_ping=True # Required to detect dropped connections
)
AsyncSessionLocal = async_sessionmaker(
engine,
expire_on_commit=False, # If this is set to True, a requery will be run every time the attribute is accessed after a commit, and a MissingGreenlet error will occur outside the async context.
class_=AsyncSession
)
async def get_db() -> AsyncGenerator[AsyncSession, None]:
async with AsyncSessionLocal() as session:
try:
yield session
await session.commit()
exception Exception:
await session.rollback()
raise
finally:
# If you forget this, the connection pool will die instantly. finally is a sacred place.
await session.close()
Remove `expire_on_commit=False` and stop watching me cry. You must have observed the unnecessary load of expiring all objects at the moment of commit and rethrowing SQL in subsequent processing as a "mysterious response delay."
2. Pydantic v2: The “sin” of returning the DB model as is
Don't directly insert SQLAlchemy models into API responses. You'll expose your password hashes and internal flags to the world and break out in a cold sweat later.
Bind types and control information
from pydantic import BaseModel, ConfigDict, Field
class UserResponse(BaseModel):
# from_attributes=True is required. Allow ORM objects to be treated like dicts
model_config = ConfigDict(from_attributes=True)
id: int
username: str = Field(..., min_length=3, max_length=20)
email: str = Field(..., pattern=r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$")
# Don't include unnecessary fields (such as password_hash) here in the first place. This is the basics of security.
3. Break the nightmare of circular references
Have you ever defined a `relationship` sloppily and caused a "stack overflow due to recursive call"? Load only what you need, when you need it. This is the minimum etiquette when dealing with ORM.
Solution: Schema separation and lazy evaluation
from typing import List, Optional
from pydantic import BaseModel, Field
# Separate relation models to prevent circular references
class PostSchema(BaseModel):
id: int
title: str
class UserDetailSchema(BaseModel):
id: int
username:str
posts: List[PostSchema] = Field(default_factory=list)
model_config = ConfigDict(from_attributes=True)
Optimize your queries using `lazy='selectin'`. Careless use of `lazy='joined'` generates huge JOIN queries and eats up DB memory. You're not writing SQL, you're writing runaway queries.
4. Professional DI (Dependency Injection): Testing is not “play”
When using `app.dependency_overrides` in a test, do you forget to clean it up? A contaminated global state will destroy all subsequent tests.
How to not pollute test code
import pytest
from main import app, get_db
@pytest.mark.asyncio
async def test_create_user(client):
# Be sure to cancel the override after the test is finished. Anyone who neglects this is not qualified to write the test.
app.dependency_overrides[get_db] = override_get_db
try:
response = await client.post("/users", json={"username": "test"})
assert response.status_code == 200
finally:
app.dependency_overrides.clear()
Why do you write so harshly?
Technical articles are not just tutorials. There is always a ``user'' at the end of the code you write. Due to your sloppy implementation, user data will be leaked and the service will stop due to connection exhaustion. Can you bear that responsibility?Abandon the naive belief that ``just move'' is fine. Obsession with quality is the only weapon engineers have to protect users.
ORM is not a magic wand. If you don't know how to use it, just write SQL. If you can't do that, put down your keyboard and leave the world of code.