# Sqlmodel

> SQLModel persistence patterns for Python services and applications. Covers model design, async sessions, relationships, Alembic migrations, query optimization, N+1 prevention, and production-safe schema changes. Load when working with SQLModel, Alembic, relationships, async database layers, or SQLAlchemy-backed persistence in Python.

- Skill: `oornnery/sqlmodel` (Agent Skill, multi-file: 7 files)
- Install (CLI): `npx skillmds@latest add oornnery/sqlmodel`
- Raw SKILL.md: https://api.skillmd.com/api/skills/oornnery/sqlmodel/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: oornnery (https://skillmd.com/u/oornnery)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/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

```bash
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

```python
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

```python
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

```python
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

```python
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

```bash
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