---

name: sqlmodel description: "Comprehensive SQLModel development assistance including model creation, relationship handling, database operations, and integration with FastAPI. Use when Claude needs to work with SQLModel projects for: (1) Creating data models with SQLModel, (2) Setting up database connections and sessions, (3) Implementing relationships between models, (4) Performing CRUD operations, (5) Handling advanced features like UUIDs and Decimals, or any other SQLModel database operations."

SQLModel Development Assistant

Overview

SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It combines the power of Pydantic and SQLAlchemy, providing data validation, serialization, and database interaction in a single, intuitive interface. SQLModel is designed to work seamlessly with FastAPI and provides excellent editor support with autocompletion and in-editor error checking.

Core Capabilities

1. Model Creation

• Define SQL tables using Python classes
• Implement Pydantic-style validation
• Set up automatic ID generation and data refresh
• Configure indexes and constraints

2. Database Operations

• Establish database connections with engines
• Manage sessions for database interactions
• Perform CRUD (Create, Read, Update, Delete) operations
• Handle transactions and connection pooling

3. Relationship Handling

• Define one-to-many, many-to-many relationships
• Implement back_populates for bidirectional connections
• Use Relationship attributes for intuitive data access
• Configure cascade delete behavior

4. Integration with FastAPI

• Use SQLModel models as request/response bodies
• Implement dependency injection for database sessions
• Handle async operations with FastAPI
• Validate data with Pydantic integration

Basic Model Definition

Simple Model

Copyfrom sqlmodel import SQLModel, Field

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

Model with Constraints

Copyfrom sqlmodel import SQLModel, Field
from typing import Optional

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True, min_length=3, max_length=50)
    headquarters: str

    class Config:
        # Pydantic configuration can be added here
        pass

Relationship Patterns

One-to-Many Relationship

Copyfrom sqlmodel import SQLModel, Field, Relationship
from typing import List, Optional

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str

    # Relationship attribute
    heroes: List["Hero"] = Relationship(back_populates="team")

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

    # Foreign key
    team_id: Optional[int] = Field(default=None, foreign_key="team.id")

    # Relationship attribute
    team: Optional[Team] = Relationship(back_populates="heroes")

Many-to-Many Relationship

Copyfrom sqlmodel import SQLModel, Field, Relationship
from typing import List

# Association table for many-to-many
class HeroTeamLink(SQLModel, table=True):
    team_id: int = Field(foreign_key="team.id", primary_key=True)
    hero_id: int = Field(foreign_key="hero.id", primary_key=True)

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

    # Many-to-many relationship
    heroes: List["Hero"] = Relationship(
        back_populates="teams",
        link_model=HeroTeamLink
    )

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

    # Many-to-many relationship
    teams: List[Team] = Relationship(
        back_populates="heroes",
        link_model=HeroTeamLink
    )

Database Connection and Session Management

Engine Creation

Copyfrom sqlmodel import create_engine
from sqlalchemy import engine

# Create database engine
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)  # echo=True for SQL logging

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

Session Management

Copyfrom sqlmodel import Session
from contextlib import contextmanager

# Context manager for sessions
@contextmanager
def get_session():
    with Session(engine) as session:
        yield session

# Usage
def create_hero(hero_data: HeroCreate):
    with get_session() as session:
        hero = Hero.from_orm(hero_data)  # or however you create the object
        session.add(hero)
        session.commit()
        session.refresh(hero)
        return hero

CRUD Operations

Create Operations

Copyfrom sqlmodel import Session, select

def create_hero(hero: Hero):
    with Session(engine) as session:
        session.add(hero)
        session.commit()
        session.refresh(hero)  # Refresh to get the generated ID
        return hero

Read Operations

Copydef get_hero(hero_id: int):
    with Session(engine) as session:
        statement = select(Hero).where(Hero.id == hero_id)
        hero = session.exec(statement).first()
        return hero

def get_heroes(offset: int = 0, limit: int = 10):
    with Session(engine) as session:
        statement = select(Hero).offset(offset).limit(limit)
        heroes = session.exec(statement).all()
        return heroes

Update Operations

Copydef update_hero(hero_id: int, hero_update: HeroUpdate):
    with Session(engine) as session:
        hero = session.get(Hero, hero_id)
        if hero:
            hero_data = hero_update.dict(exclude_unset=True)
            for key, value in hero_data.items():
                setattr(hero, key, value)
            session.add(hero)
            session.commit()
            session.refresh(hero)
            return hero
        return None

Delete Operations

Copydef delete_hero(hero_id: int):
    with Session(engine) as session:
        hero = session.get(Hero, hero_id)
        if hero:
            session.delete(hero)
            session.commit()
            return True
        return False

Advanced Features

UUID Support

Copyfrom sqlmodel import SQLModel, Field
import uuid
from typing import Optional

class Hero(SQLModel, table=True):
    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    name: str
    secret_name: str

Decimal Numbers

Copyfrom sqlmodel import SQLModel, Field
from decimal import Decimal
from typing import Optional

class Product(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    price: Decimal = Field(decimal_places=2, max_digits=10)

Code Structure Best Practices

Single File Structure (Recommended for Simple Projects)

Copyproject/
├── app/
│   ├── __init__.py
│   ├── main.py          # FastAPI app
│   ├── models.py        # All SQLModel definitions
│   ├── database.py      # Engine and session setup
│   └── api/             # API route modules

Multiple Files (For Larger Projects)

Copy# models/__init__.py
from .hero_model import Hero
from .team_model import Team

# models/hero_model.py
from sqlmodel import SQLModel, Field, Relationship
from typing import TYPE_CHECKING

if TYPE_CHECKING:
    from .team_model import Team

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

    team_id: int | None = Field(default=None, foreign_key="team.id")
    team: "Team" | None = Relationship(back_populates="heroes")

Integration with FastAPI

Dependency Injection for Database Sessions

Copyfrom fastapi import Depends, FastAPI
from sqlmodel import Session

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

@app.post("/heroes/")
def create_hero(hero: Hero, session: Session = Depends(get_session)):
    session.add(hero)
    session.commit()
    session.refresh(hero)
    return hero

Async Support (Planned Feature)

SQLModel is planned to include async/await support for async sessions in future versions, enabling better performance for I/O-bound operations.

Resources

This skill includes resources for different aspects of SQLModel development:

scripts/

Python and shell scripts for common SQLModel operations.

Examples:

• create_model.py - Script to generate new SQLModel model definitions
• setup_database.py - Script to initialize database connections and tables
• generate_crud.py - Script to create CRUD operations for models

references/

Detailed documentation and reference materials for SQLModel features.

Examples:

• advanced_relationships.md - Complex relationship patterns and best practices
• migration_patterns.md - Database migration strategies
• async_patterns.md - Async session handling (when available)
• validation_patterns.md - Pydantic validation with SQLModel

assets/

Project templates and boilerplate code for common SQLModel setups.

Examples:

• templates/basic-model/ - Basic SQLModel application template
• templates/relationship-model/ - Models with relationships template
• templates/fastapi-integration/ - FastAPI + SQLModel integration template