sqlmodel

Original：🇺🇸 English

Translated

Expert guidance for SQLModel - the Python library combining SQLAlchemy and Pydantic for database models. Use when (1) creating database models that work as both SQLAlchemy ORM and Pydantic schemas, (2) building FastAPI apps with database integration, (3) defining model relationships (one-to-many, many-to-many), (4) performing CRUD operations with type safety, (5) setting up async database sessions, (6) integrating with Alembic migrations, (7) handling model inheritance and mixins, or (8) converting between database models and API schemas.

1installs

Sourcesalmanferozkhan/cloud-and-fast-api

Added on2026-02-15

NPX Install

npx skill4agent add salmanferozkhan/cloud-and-fast-api sqlmodel

SKILL.md Content

View Translation Comparison →

SQLModel Development Guide

SQLModel combines SQLAlchemy and Pydantic into a single library - one model class serves as both ORM model and Pydantic schema.

Quick Start

Installation

bash

pip install sqlmodel

Minimal Example

python

from sqlmodel import Field, SQLModel, Session, create_engine, select

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    age: int | None = None

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

# Create
with Session(engine) as session:
    hero = Hero(name="Spider-Boy", age=18)
    session.add(hero)
    session.commit()
    session.refresh(hero)

# Read
with Session(engine) as session:
    heroes = session.exec(select(Hero)).all()

Core Concepts

Concept	Description
`table=True`	Makes class a database table (without it, it's just Pydantic)
`Field()`	Define column attributes: `primary_key` , `index` , `unique` , `foreign_key`
`Session`	Database session for CRUD operations
`select()`	Type-safe query builder
`Relationship`	Define relationships between models

Model Patterns

Base Model (API Schema Only)

python

class HeroBase(SQLModel):
    name: str
    age: int | None = None

Table Model (Database)

python

class Hero(HeroBase, table=True):
    id: int | None = Field(default=None, primary_key=True)

Request/Response Models

python

class HeroCreate(HeroBase):
    secret_name: str

class HeroPublic(HeroBase):
    id: int

class HeroUpdate(SQLModel):
    name: str | None = None
    age: int | None = None

CRUD Operations

Create

python

def create_hero(session: Session, hero: HeroCreate) -> Hero:
    db_hero = Hero.model_validate(hero)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

Read

python

def get_hero(session: Session, hero_id: int) -> Hero | None:
    return session.get(Hero, hero_id)

def get_heroes(session: Session, skip: int = 0, limit: int = 100) -> list[Hero]:
    return session.exec(select(Hero).offset(skip).limit(limit)).all()

Update

python

def update_hero(session: Session, hero_id: int, hero_update: HeroUpdate) -> Hero | None:
    db_hero = session.get(Hero, hero_id)
    if not db_hero:
        return None
    hero_data = hero_update.model_dump(exclude_unset=True)
    db_hero.sqlmodel_update(hero_data)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

Delete

python

def delete_hero(session: Session, hero_id: int) -> bool:
    hero = session.get(Hero, hero_id)
    if not hero:
        return False
    session.delete(hero)
    session.commit()
    return True

FastAPI Integration

Database Setup

python

from sqlmodel import SQLModel, Session, create_engine

DATABASE_URL = "sqlite:///./database.db"
engine = create_engine(DATABASE_URL, echo=True)

def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

def get_session():
    with Session(engine) as session:
        yield session

Dependency Injection

python

from typing import Annotated
from fastapi import Depends

SessionDep = Annotated[Session, Depends(get_session)]

@app.post("/heroes/", response_model=HeroPublic)
def create_hero(hero: HeroCreate, session: SessionDep):
    db_hero = Hero.model_validate(hero)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

Lifespan Events

python

from contextlib import asynccontextmanager
from fastapi import FastAPI

@asynccontextmanager
async def lifespan(app: FastAPI):
    create_db_and_tables()
    yield

app = FastAPI(lifespan=lifespan)

Reference Files

Load these based on the task at hand:

Topic	File	When to Use
Models	models.md	Field options, validators, computed fields, inheritance, mixins
Relationships	relationships.md	One-to-many, many-to-many, self-referential, lazy loading
Async	async.md	Async sessions, async engine, background tasks
Migrations	migrations.md	Alembic setup, auto-generation, migration patterns

Querying

Basic Queries

python

# All heroes
heroes = session.exec(select(Hero)).all()

# Single result (first or None)
hero = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).first()

# Get by primary key
hero = session.get(Hero, 1)

Filtering

python

from sqlmodel import select, or_, and_

# Single condition
select(Hero).where(Hero.age >= 18)

# Multiple conditions (AND)
select(Hero).where(Hero.age >= 18, Hero.name == "Spider-Boy")

# OR conditions
select(Hero).where(or_(Hero.age < 18, Hero.age > 60))

# LIKE/contains
select(Hero).where(Hero.name.contains("Spider"))

Ordering and Pagination

python

select(Hero).order_by(Hero.name)
select(Hero).order_by(Hero.age.desc())
select(Hero).offset(10).limit(5)

Best Practices

Separate table models from API schemas - Use
```
table=True
```
only for actual DB tables
Use
model_validate()
for conversion - Convert between schemas and table models

Use
sqlmodel_update()
for partial updates - Pass

exclude_unset=True

model_dump()

Always use
Field()
for constraints - Primary keys, indexes, foreign keys, defaults
Use
Annotated
dependencies - Clean, reusable session injection
Use lifespan for table creation - Not deprecated
```
@app.on_event
```
Index frequently queried columns -
```
Field(index=True)
```
Use
echo=True
during development - See generated SQL queries

Common Issues

Issue	Solution
Missing `table=True`	Add `table=True` to models that need DB tables
Circular imports	Use `TYPE_CHECKING` and string annotations for relationships
Session already closed	Ensure session is still open when accessing lazy-loaded relationships
Migration not detecting changes	Use `compare_type=True` in Alembic env.py

SQLModel Development Guide

SQLModel combines SQLAlchemy and Pydantic into a single library - one model class serves as both ORM model and Pydantic schema.

Quick Start

Installation

bash

pip install sqlmodel

Minimal Example

python

from sqlmodel import Field, SQLModel, Session, create_engine, select

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    age: int | None = None

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

# Create
with Session(engine) as session:
    hero = Hero(name="Spider-Boy", age=18)
    session.add(hero)
    session.commit()
    session.refresh(hero)

# Read
with Session(engine) as session:
    heroes = session.exec(select(Hero)).all()

Core Concepts

Concept	Description
`table=True`	Makes class a database table (without it, it's just Pydantic)
`Field()`	Define column attributes: `primary_key` , `index` , `unique` , `foreign_key`
`Session`	Database session for CRUD operations
`select()`	Type-safe query builder
`Relationship`	Define relationships between models

Model Patterns

Base Model (API Schema Only)

python

class HeroBase(SQLModel):
    name: str
    age: int | None = None

Table Model (Database)

python

class Hero(HeroBase, table=True):
    id: int | None = Field(default=None, primary_key=True)

Request/Response Models

python

class HeroCreate(HeroBase):
    secret_name: str

class HeroPublic(HeroBase):
    id: int

class HeroUpdate(SQLModel):
    name: str | None = None
    age: int | None = None

CRUD Operations

Create

python

def create_hero(session: Session, hero: HeroCreate) -> Hero:
    db_hero = Hero.model_validate(hero)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

Read

python

def get_hero(session: Session, hero_id: int) -> Hero | None:
    return session.get(Hero, hero_id)

def get_heroes(session: Session, skip: int = 0, limit: int = 100) -> list[Hero]:
    return session.exec(select(Hero).offset(skip).limit(limit)).all()

Update

python

def update_hero(session: Session, hero_id: int, hero_update: HeroUpdate) -> Hero | None:
    db_hero = session.get(Hero, hero_id)
    if not db_hero:
        return None
    hero_data = hero_update.model_dump(exclude_unset=True)
    db_hero.sqlmodel_update(hero_data)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

Delete

python

def delete_hero(session: Session, hero_id: int) -> bool:
    hero = session.get(Hero, hero_id)
    if not hero:
        return False
    session.delete(hero)
    session.commit()
    return True

FastAPI Integration

Database Setup

python

from sqlmodel import SQLModel, Session, create_engine

DATABASE_URL = "sqlite:///./database.db"
engine = create_engine(DATABASE_URL, echo=True)

def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

def get_session():
    with Session(engine) as session:
        yield session

Dependency Injection

python

from typing import Annotated
from fastapi import Depends

SessionDep = Annotated[Session, Depends(get_session)]

@app.post("/heroes/", response_model=HeroPublic)
def create_hero(hero: HeroCreate, session: SessionDep):
    db_hero = Hero.model_validate(hero)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

Lifespan Events

python

from contextlib import asynccontextmanager
from fastapi import FastAPI

@asynccontextmanager
async def lifespan(app: FastAPI):
    create_db_and_tables()
    yield

app = FastAPI(lifespan=lifespan)

Reference Files

Load these based on the task at hand:

Topic	File	When to Use
Models	models.md	Field options, validators, computed fields, inheritance, mixins
Relationships	relationships.md	One-to-many, many-to-many, self-referential, lazy loading
Async	async.md	Async sessions, async engine, background tasks
Migrations	migrations.md	Alembic setup, auto-generation, migration patterns

Querying

Basic Queries

python

# All heroes
heroes = session.exec(select(Hero)).all()

# Single result (first or None)
hero = session.exec(select(Hero).where(Hero.name == "Spider-Boy")).first()

# Get by primary key
hero = session.get(Hero, 1)

Filtering

python

from sqlmodel import select, or_, and_

# Single condition
select(Hero).where(Hero.age >= 18)

# Multiple conditions (AND)
select(Hero).where(Hero.age >= 18, Hero.name == "Spider-Boy")

# OR conditions
select(Hero).where(or_(Hero.age < 18, Hero.age > 60))

# LIKE/contains
select(Hero).where(Hero.name.contains("Spider"))

Ordering and Pagination

python

select(Hero).order_by(Hero.name)
select(Hero).order_by(Hero.age.desc())
select(Hero).offset(10).limit(5)

Best Practices

Separate table models from API schemas - Use
```
table=True
```
only for actual DB tables
Use
model_validate()
for conversion - Convert between schemas and table models

Use
sqlmodel_update()
for partial updates - Pass

exclude_unset=True

model_dump()

Always use
Field()
for constraints - Primary keys, indexes, foreign keys, defaults
Use
Annotated
dependencies - Clean, reusable session injection
Use lifespan for table creation - Not deprecated
```
@app.on_event
```
Index frequently queried columns -
```
Field(index=True)
```
Use
echo=True
during development - See generated SQL queries

Common Issues

Issue	Solution
Missing `table=True`	Add `table=True` to models that need DB tables
Circular imports	Use `TYPE_CHECKING` and string annotations for relationships
Session already closed	Ensure session is still open when accessing lazy-loaded relationships
Migration not detecting changes	Use `compare_type=True` in Alembic env.py

sqlmodel

NPX Install

Tags

SKILL.md Content

SQLModel Development Guide

Quick Start

Installation

Minimal Example

Core Concepts

Model Patterns

Base Model (API Schema Only)

Table Model (Database)

Request/Response Models

CRUD Operations

Create

Read

Update

Delete

FastAPI Integration

Database Setup

Dependency Injection

Lifespan Events

Reference Files

Querying

Basic Queries

Filtering

Ordering and Pagination

Best Practices

Common Issues

sqlmodel

NPX Install

Tags

SKILL.md Content

SQLModel Development Guide

Quick Start

Installation

Minimal Example

Core Concepts

Model Patterns

Base Model (API Schema Only)

Table Model (Database)

Request/Response Models

CRUD Operations

Create

Read

Update

Delete

FastAPI Integration

Database Setup

Dependency Injection

Lifespan Events

Reference Files

Querying

Basic Queries

Filtering

Ordering and Pagination

Best Practices

Common Issues