name: sqlmodel description: | SQLModel ORM skill for Python database operations combining SQLAlchemy power with Pydantic validation. Use when: (1) Defining database models with type hints, (2) Setting up async database connections, (3) Creating model relationships (one-to-many, many-to-many, one-to-one), (4) Implementing repository pattern, (5) Setting up Alembic migrations, (6) Writing complex queries with joins and aggregations, (7) Creating database seeders, (8) Validating data with Pydantic validators in models.

SQLModel ORM Skill

SQLModel combines SQLAlchemy (database ORM) with Pydantic (data validation) into a single library with full type hint support.

Quick Reference

Core Concepts

SQLModel = SQLAlchemy + Pydantic

from sqlmodel import SQLModel, Field

# Schema only (no table=True)
class UserBase(SQLModel):
    email: str
    name: str

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

Minimal Setup

from sqlmodel import SQLModel, Field, create_engine, Session

engine = create_engine("sqlite:///database.db")
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    user = User(email="test@example.com", name="Test")
    session.add(user)
    session.commit()

Async Setup (Recommended)

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

engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/db")
async_session = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_session():
    async with async_session() as session:
        yield session

Dependencies

[project]
dependencies = [
    "sqlmodel>=0.0.22",
    "asyncpg>=0.30.0",       # PostgreSQL async
    "greenlet>=3.1.0",       # Async support
    "alembic>=1.14.0",       # Migrations
]

Common Patterns

Model with Validation

from pydantic import field_validator
from sqlmodel import SQLModel, Field

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    email: str = Field(unique=True, index=True)
    age: int = Field(ge=0, le=150)

    @field_validator("email")
    @classmethod
    def validate_email(cls, v: str) -> str:
        if "@" not in v:
            raise ValueError("Invalid email")
        return v.lower()

CRUD Operations

from sqlalchemy import select

# Create
user = User(email="test@example.com", name="Test")
session.add(user)
await session.commit()

# Read
stmt = select(User).where(User.email == "test@example.com")
result = await session.execute(stmt)
user = result.scalar_one_or_none()

# Update
user.name = "Updated"
await session.commit()

# Delete
await session.delete(user)
await session.commit()

Relationships Quick Reference

# One-to-Many
class Team(SQLModel, table=True):
    members: list["User"] = Relationship(back_populates="team")

class User(SQLModel, table=True):
    team_id: int | None = Field(foreign_key="team.id")
    team: Team | None = Relationship(back_populates="members")

# Many-to-Many (requires link table)
class UserRoleLink(SQLModel, table=True):
    user_id: int = Field(foreign_key="user.id", primary_key=True)
    role_id: int = Field(foreign_key="role.id", primary_key=True)

Migration Commands

alembic init alembic                          # Initialize
alembic revision --autogenerate -m "message"  # Create migration
alembic upgrade head                          # Apply migrations
alembic downgrade -1                          # Rollback one step