Shared from "Study" on Inkdown
SQLAlchemy is two tools in one:
Most FastAPI apps use the ORM layer. Core runs underneath it silently. Think: Core = engine, ORM = steering wheel.
Engine = the one connection to your DB. Created once, lives for the app's lifetime.
Session = a "unit of work" per request. Like a shopping cart — you stage changes, then commit.
expire_on_commit=False— without this, SQLAlchemy clears object data after commit. In async FastAPI, your response serializer would crash trying to read cleared attributes.
One user → many posts. Use back_populates to create a two-way link.
In async SQLAlchemy, accessing
user.postswithout eager loading raisesMissingGreenlet. Always explicitly load relationships.
These are two different things. Don't confuse them.
| SQLAlchemy Model | Pydantic Schema | |
|---|---|---|
| Lives in | models/ | schemas/ |
| Purpose | Talks to DB | Validates HTTP data |
| Inherits from | Base | BaseModel |
from_attributes = Truelets Pydantic read SQLAlchemy model attributes instead of expecting a plain dict.
flush = typing on screen (not saved yet, browser crash = gone)commit = clicking Save (permanent)| Situation | Use |
|---|---|
| Need auto-generated ID to create a related object | flush() first, then continue |
| End of a complete operation, everything succeeded | commit() |
| Something went wrong | rollback() |
| Need server-set values (created_at, id) after flush/commit | refresh(obj) |
| Gotcha | Fix |
|---|---|
Forgetting await on DB calls | Every DB call is async — always await db.execute(...) |
Accessing user.posts in async without eager loading | Use selectinload or joinedload |
| N+1 query problem (looping + accessing relationships) | Eager load everything upfront in one query |
| Sharing one session across concurrent requests | get_db creates one session per request — don't share |
| Alembic not detecting a new model | Import all models in alembic/env.py |