oornnery/sqlmodel

SQLModel

Persistence modeling, async sessions, relationships, migrations, query loading with SQLModel and Alembic.

Boundary

Use for persistence modeling, async sessions, relationships, transactions, migrations, query optimization with SQLModel.

• pair python for general Python conventions, tooling, FastAPI usage
• pair arch when repo boundaries, domain separation, layering matter
• pair design when API contract and persistence shape must stay decoupled
• pair quality when query bugs, regressions, migration failures need tighter guards
• pair security when persistence changes touch auth data, tenant isolation, secrets, unsafe raw SQL

Reference Map

• references/advanced-models.md -- advanced model patterns, relationships, inheritance, mixins, field types, indexes, constraints
• references/migrations.md -- Alembic setup, schema/data migrations, rollback patterns, prod workflow, troubleshooting
• references/queries-optimization.md -- query patterns, eager loading, N+1 prevention, bulk ops, profiling, perf testing

Assets and Scripts

• assets/models.py -- table, create, read, update, timestamps, enums, many-to-many patterns
• scripts/init.py -- starter script to init DB from imported SQLModel metadata
• scripts/migrate.sh -- migration helper wrapper around Alembic commands

What Stays Here

Defaults, async patterns, guardrails for day-to-day work.

• here: multiple-model pattern, async engine/session defaults, FastAPI dep wiring, relationship loading defaults, migration workflow, guardrails
• refs: long examples, advanced model variants, detailed migration strategies, query patterns, deeper troubleshooting

Quick Start

uv add sqlmodel sqlalchemy[asyncio] alembic
uv add asyncpg

Start with:

1. explicit table, create, update, public models
2. one async session per request or job boundary
3. reviewed Alembic migrations
4. explicit relationship loading

Multiple Model Pattern

from sqlmodel import Field, SQLModel


class UserBase(SQLModel):
    name: str = Field(max_length=100)
    email: str = Field(max_length=255, unique=True)


class User(UserBase, table=True):
    id: int | None = Field(default=None, primary_key=True)
    hashed_password: str


class UserCreate(UserBase):
    password: str = Field(min_length=8)


class UserPublic(UserBase):
    id: int


class UserUpdate(SQLModel):
    name: str | None = None
    email: str | None = None

Separate models because table shape, write input, public response rarely share responsibilities.

Async Engine and Session

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlmodel.ext.asyncio.session import AsyncSession

engine = create_async_engine(
    "postgresql+asyncpg://...",
    pool_size=20,
    max_overflow=10,
)
async_session = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)

expire_on_commit=False keeps returned objects usable after commit boundaries in async workflows.

FastAPI Dependency

from collections.abc import AsyncGenerator
from typing import Annotated

from fastapi import Depends
from sqlmodel.ext.asyncio.session import AsyncSession


async def get_session() -> AsyncGenerator[AsyncSession, None]:
    async with async_session() as session:
        yield session


SessionDep = Annotated[AsyncSession, Depends(get_session)]

One session per request or job boundary. Keep transaction scope explicit.

Relationship Defaults

from sqlalchemy.orm import selectinload
from sqlmodel import Field, Relationship, SQLModel, select


class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    members: list["User"] = Relationship(back_populates="team")


statement = select(Team).options(selectinload(Team.members))

In async code, prefer explicit loading over accidental lazy loading. Default selectinload for collections unless query shape clearly favors join.

Migration Workflow

alembic init -t async alembic
alembic revision --autogenerate -m "add users table"
alembic upgrade head

Set target_metadata = SQLModel.metadata in alembic/env.py.

Review autogenerated migrations before applying. Do not trust autogenerate blindly for destructive, data-sensitive, or prod-facing changes.

Production Migration Rules

• one logical schema change per migration
• test both upgrade and downgrade when feasible
• add data migrations explicitly when schema changes need backfill or reshape
• avoid editing already-applied migrations
• stage nullable-first changes for large tables or zero-downtime rollouts
• back up prod data before risky migrations

For prod-heavy changes, load references/migrations.md.

Query and Performance Rules

• prevent N+1 with explicit eager loading
• index foreign keys and frequently filtered/ordered columns
• select only what use case needs
• batch ops over row-by-row loops when volume matters
• profile before optimizing
• keep raw SQL narrow, justified, parameterized

For deeper query patterns, load references/queries-optimization.md.

Advanced Modeling Rules

• explicit link tables for many-to-many
• mixins for reusable timestamps, soft delete, audit columns
• keep inheritance and polymorphism rare and deliberate
• keep indexes, unique constraints, cascades explicit
• separate persistence models from API/domain contracts when responsibilities differ

For advanced patterns, load references/advanced-models.md.

Guardrails

• multiple-model pattern -- never expose table models directly in responses
• always expire_on_commit=False for async sessions unless specific reason not to
• selectinload or another explicit eager-loading strategy -- prevent N+1
• validate at boundaries with create/update models
• async with session.begin() for multi-step transactions
• index columns in WHERE, ORDER BY, relationship joins
• never format SQL strings -- use query builders or parameterized SQL
• keep domain logic out of ORM models when project uses service/repository boundaries
• treat migrations as reviewed changes, not generated boilerplate

Review Focus

• persistence and public contract models separated
• async sessions have clear scope and cleanup
• no accidental lazy loading or hidden N+1
• migrations safe for real data, not schema shape
• indexes and constraints match read/write patterns
• raw SQL parameterized and justified