Advanced SQLModel patterns and comprehensive Alembic migrations for production databases.

Quick Start

Define a Basic Model

from sqlmodel import Field, SQLModel
from typing import Optional
from datetime import datetime

class Task(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str = Field(index=True)
    description: Optional[str] = None
    completed: bool = Field(default=False)
    created_at: datetime = Field(default_factory=datetime.utcnow)

Initialize Database

# Using provided script
python scripts/init_db.py --url postgresql://user:pass@localhost/db

# Or manually
from sqlmodel import create_engine
engine = create_engine("postgresql://user:pass@localhost/db")
SQLModel.metadata.create_all(engine)

Create Migration

# Using provided helper script
./scripts/migrate.sh create "add user table"

# Or directly with Alembic
alembic revision --autogenerate -m "add user table"
alembic upgrade head

Core Topics

1. Advanced Model Patterns

See: references/advanced-models.md

Relationships: One-to-many, many-to-many, self-referential
Inheritance: Single table, joined table, polymorphism
Validation: Pydantic validators, custom constraints
Mixins: Timestamp, soft delete, reusable patterns
Field Types: Enums, JSON, arrays, custom types
Indexes: Single, composite, partial indexes
Constraints: Unique, check, foreign key cascades

2. Comprehensive Migrations

See: references/migrations.md

Alembic Setup: Configuration, env.py for SQLModel
Creating Migrations: Autogenerate vs manual
Schema Changes: Add/drop columns, rename, change types
Data Migrations: Complex data transformations
Production Workflow: Zero-downtime migrations
Rollback Strategies: Safe downgrade patterns
Troubleshooting: Common issues and solutions

3. Query Optimization

See: references/queries-optimization.md

N+1 Problem: Solutions with eager loading
Query Patterns: Joins, aggregations, subqueries
Performance: Indexes, batch operations, profiling
Advanced Queries: Window functions, CTEs
Bulk Operations: Insert, update, delete at scale
Testing: Query counting, explain analyze

Common Patterns

One-to-Many Relationship

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

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

    # One team has many heroes
    heroes: List["Hero"] = Relationship(back_populates="team")

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    team_id: Optional[int] = Field(foreign_key="team.id")

    # Many heroes belong to one team
    team: Optional[Team] = Relationship(back_populates="heroes")

Many-to-Many with Link Table

class HeroTeamLink(SQLModel, table=True):
    hero_id: int = Field(foreign_key="hero.id", primary_key=True)
    team_id: int = Field(foreign_key="team.id", primary_key=True)
    joined_at: datetime = Field(default_factory=datetime.utcnow)

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    teams: List["Team"] = Relationship(
        back_populates="heroes",
        link_model=HeroTeamLink
    )

class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    heroes: List[Hero] = Relationship(
        back_populates="teams",
        link_model=HeroTeamLink
    )

Solving N+1 Query Problem

from sqlalchemy.orm import selectinload

# BAD - N+1 queries
users = session.exec(select(User)).all()
for user in users:
    posts = user.posts  # Each triggers a query!

# GOOD - Eager loading (2 queries total)
statement = select(User).options(selectinload(User.posts))
users = session.exec(statement).all()
for user in users:
    posts = user.posts  # No additional query!

Creating a Migration

# 1. Modify your model
class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str
    phone: str  # New field added

# 2. Generate migration
# alembic revision --autogenerate -m "add phone to user"

# 3. Review generated migration
def upgrade() -> None:
    op.add_column('user', sa.Column('phone', sa.String(), nullable=True))

def downgrade() -> None:
    op.drop_column('user', 'phone')

# 4. Apply migration
# alembic upgrade head

Migration Helper Scripts

Initialize Database

python scripts/init_db.py --url postgresql://user:pass@localhost/db

Migration Operations

./scripts/migrate.sh init              # Initialize Alembic
./scripts/migrate.sh create "message"  # Create migration
./scripts/migrate.sh upgrade           # Apply migrations
./scripts/migrate.sh downgrade         # Rollback one
./scripts/migrate.sh current           # Show current
./scripts/migrate.sh history           # Show history
./scripts/migrate.sh test              # Test up & down

Example Models

Use the example models in assets/example-models.py as templates:

User model with timestamp mixin
Task model with enums and relationships
Team model with many-to-many
Tag system with link tables
Separate read/write/update models

Copy to your project:

cp assets/example-models.py your-project/app/models.py

Best Practices Checklist

Model Design

Use type hints for all fields Separate read/write/update models Use mixins for common fields (timestamps, soft delete) Define indexes on foreign keys and frequently queried columns Use enums for constrained choices Implement proper validation with Pydantic validators

Relationships

Use back_populates for bidirectional relationships Create explicit link tables for many-to-many Consider cascade delete behavior Use eager loading to prevent N+1 queries Index foreign key columns

Migrations

Always review autogenerated migrations One logical change per migration Test both upgrade and downgrade Use descriptive migration names Never edit applied migrations Add data migrations when changing schemas Backup database before production migrations

Query Optimization

Use eager loading (selectinload) for relationships Select only needed columns Use indexes for WHERE/ORDER BY columns Batch operations instead of loops Profile slow queries Use connection pooling

Troubleshooting Guide

Migration Issues

Problem: Alembic doesn't detect model changes

# Solution: Ensure models are imported in env.py
from app.models import User, Task, Team  # Import all models
target_metadata = SQLModel.metadata

Problem: Failed migration

# Check current state
alembic current

# Manually fix issue, then stamp
alembic stamp head

# Or downgrade and retry
alembic downgrade -1
alembic upgrade head

Query Performance

Problem: Slow queries

# Enable query logging
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

# Use EXPLAIN ANALYZE
explain = session.exec(text("EXPLAIN ANALYZE SELECT ...")).all()

# Profile queries
# See references/queries-optimization.md for detailed patterns

Problem: N+1 queries

# Use selectinload
statement = select(User).options(selectinload(User.posts))

# Or joinedload
from sqlalchemy.orm import joinedload
statement = select(User).options(joinedload(User.posts))

Production Workflow

Development

Modify SQLModel models
Generate migration: ./scripts/migrate.sh create "description"
Review generated migration file
Test migration: ./scripts/migrate.sh test
Commit migration file

Staging

Deploy application code
Run migrations: alembic upgrade head
Verify data integrity
Test application

Production

Backup database: pg_dump mydb > backup.sql
Deploy in maintenance window
Run migrations: alembic upgrade head
Monitor logs and metrics
Verify application functionality

Zero-Downtime Migration Strategy

For large production databases:

# Phase 1: Add new column (nullable)
def upgrade():
    op.add_column('user', sa.Column('new_email', sa.String(), nullable=True))

# Deploy app version that writes to both columns

# Phase 2: Backfill data
def upgrade():
    op.execute("UPDATE user SET new_email = email WHERE new_email IS NULL")

# Phase 3: Make non-nullable
def upgrade():
    op.alter_column('user', 'new_email', nullable=False)

# Deploy app version that reads from new column

# Phase 4: Drop old column
def upgrade():
    op.drop_column('user', 'email')

Additional Resources

Advanced Patterns: See references/advanced-models.md for inheritance, polymorphism, composite keys
Migration Guide: See references/migrations.md for Alembic mastery
Query Optimization: See references/queries-optimization.md for performance tuning

This skill provides everything needed for professional SQLModel development and database management.

sqlmodel-expert

安装

Quick Start

Define a Basic Model

Initialize Database

Create Migration

Core Topics

1. Advanced Model Patterns

2. Comprehensive Migrations

3. Query Optimization

Common Patterns

One-to-Many Relationship

Many-to-Many with Link Table

Solving N+1 Query Problem

Creating a Migration

Migration Helper Scripts

Initialize Database

Migration Operations

Example Models

Best Practices Checklist

Model Design

Relationships

Migrations

Query Optimization

Troubleshooting Guide

Migration Issues

Query Performance

Production Workflow

Development

Staging

Production

Zero-Downtime Migration Strategy

Additional Resources