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

python

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

python

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

python

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

python

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

python

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

python

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

python

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

python

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

python

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

python

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

python

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

python

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)

code

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)

python

# 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

python

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