SQLModel + SQLAlchemy: Async Sessions, Engine Pooling, and Relations
- Last updated
- Prerequisites:
- SQLAlchemy 2.0 async engine
- Pydantic & SQLModel basics
- fastapi
- sqlmodel
- sqlalchemy
- asyncio
- asyncpg
- database
- connection-pooling
Read at your depth
The practical view
SQLModel is an ORM created by FastAPI's author that merges SQLAlchemy 2.0 and Pydantic: `class User(SQLModel, table=True): id: int | None = Field(default=None, primary_key=True)`. Using `create_async_engine('postgresql+asyncpg://...')` and `AsyncSession`, you execute asynchronous database queries: `result = await session.exec(select(User))`, `users = result.all()`.
The same idea in other frameworks
SQLModel `AsyncSession` with `select()` and `await session.exec()`.TypeORM `Repository` or Prisma Client (`this.prisma.user.findMany()`).Spring Data JPA with Hibernate Reactive / R2DBC.Go `sqlc` or GORM with PostgreSQL driver.Legacy vs modern
Duplicate SQLAlchemy + Pydantic vs Unified SQLModel Async Session
Maintaining separate SQLAlchemy models and Pydantic schemas creates synchronization bugs; SQLModel defines both in one class.
class UserORM(Base): ...; class UserSchema(BaseModel): ... // ❌ 2 separate models to maintainclass User(SQLModel, table=True): ... // ✅ NEW: 1 class is BOTH Table and Pydantic SchemaInterview gotchas
Context
Interviewers test your knowledge of Greenlet / AsyncIO boundaries in SQLAlchemy.
The mechanical answer
In SQLAlchemy, committing a transaction (`await session.commit()`) normally 'expires' all loaded entity attributes. If you access an attribute on that object later (e.g. during Pydantic response serialization), SQLAlchemy will attempt to issue a lazy synchronous SQL query to refresh the attribute. In an async environment, this triggers a fatal **`MissingGreenlet: greenlet_spawn has not been called`** exception. Setting `expire_on_commit=False` keeps the loaded data accessible in memory after commit.
Trap
Always pass `expire_on_commit=False` to `AsyncSession` to prevent `MissingGreenlet` errors during response serialization.
Context
Checks understanding of async relationship loading.
The mechanical answer
Lazy loading cannot occur synchronously on attribute access in async code. You must eagerly fetch relationships using **`select(User).options(selectinload(User.items))`** or `joinedload()`. Attempting to access `user.items` without eager loading in an async session will throw a `MissingGreenlet` error.
Trap
Default relationship loading (`lazy='select'`) will crash in async SQLAlchemy; use `selectinload` for collections.