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

from 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

from 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

from 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

from 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

from 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

from 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

from 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

def 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

def 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

def 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

from 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

from 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)

project/
├── 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)

# 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

from 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