SQLModel Expert

Advanced SQLModel patterns and comprehensive Alembic migrations for production databases.

Quick Start

Define a Basic Model

python
1from sqlmodel import Field, SQLModel
2from typing import Optional
3from datetime import datetime
4
5class Task(SQLModel, table=True):
6    id: Optional[int] = Field(default=None, primary_key=True)
7    title: str = Field(index=True)
8    description: Optional[str] = None
9    completed: bool = Field(default=False)
10    created_at: datetime = Field(default_factory=datetime.utcnow)

Initialize Database

bash
1# Using provided script
2python scripts/init_db.py --url postgresql://user:pass@localhost/db
3
4# Or manually
5from sqlmodel import create_engine
6engine = create_engine("postgresql://user:pass@localhost/db")
7SQLModel.metadata.create_all(engine)

Create Migration

bash
1# Using provided helper script
2./scripts/migrate.sh create "add user table"
3
4# Or directly with Alembic
5alembic revision --autogenerate -m "add user table"
6alembic 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

python
1from typing import List
2from sqlmodel import Field, Relationship, SQLModel
3
4class Team(SQLModel, table=True):
5    id: Optional[int] = Field(default=None, primary_key=True)
6    name: str
7
8    # One team has many heroes
9    heroes: List["Hero"] = Relationship(back_populates="team")
10
11class Hero(SQLModel, table=True):
12    id: Optional[int] = Field(default=None, primary_key=True)
13    name: str
14    team_id: Optional[int] = Field(foreign_key="team.id")
15
16    # Many heroes belong to one team
17    team: Optional[Team] = Relationship(back_populates="heroes")

Many-to-Many with Link Table

python
1class HeroTeamLink(SQLModel, table=True):
2    hero_id: int = Field(foreign_key="hero.id", primary_key=True)
3    team_id: int = Field(foreign_key="team.id", primary_key=True)
4    joined_at: datetime = Field(default_factory=datetime.utcnow)
5
6class Hero(SQLModel, table=True):
7    id: Optional[int] = Field(default=None, primary_key=True)
8    name: str
9    teams: List["Team"] = Relationship(
10        back_populates="heroes",
11        link_model=HeroTeamLink
12    )
13
14class Team(SQLModel, table=True):
15    id: Optional[int] = Field(default=None, primary_key=True)
16    name: str
17    heroes: List[Hero] = Relationship(
18        back_populates="teams",
19        link_model=HeroTeamLink
20    )

Solving N+1 Query Problem

python
1from sqlalchemy.orm import selectinload
2
3# BAD - N+1 queries
4users = session.exec(select(User)).all()
5for user in users:
6    posts = user.posts  # Each triggers a query!
7
8# GOOD - Eager loading (2 queries total)
9statement = select(User).options(selectinload(User.posts))
10users = session.exec(statement).all()
11for user in users:
12    posts = user.posts  # No additional query!

Creating a Migration

python
1# 1. Modify your model
2class User(SQLModel, table=True):
3    id: Optional[int] = Field(default=None, primary_key=True)
4    email: str
5    phone: str  # New field added
6
7# 2. Generate migration
8# alembic revision --autogenerate -m "add phone to user"
9
10# 3. Review generated migration
11def upgrade() -> None:
12    op.add_column('user', sa.Column('phone', sa.String(), nullable=True))
13
14def downgrade() -> None:
15    op.drop_column('user', 'phone')
16
17# 4. Apply migration
18# alembic upgrade head

Migration Helper Scripts

Initialize Database

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

Migration Operations

bash
1./scripts/migrate.sh init              # Initialize Alembic
2./scripts/migrate.sh create "message"  # Create migration
3./scripts/migrate.sh upgrade           # Apply migrations
4./scripts/migrate.sh downgrade         # Rollback one
5./scripts/migrate.sh current           # Show current
6./scripts/migrate.sh history           # Show history
7./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:

bash
1cp 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

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

Problem: Failed migration

bash
1# Check current state
2alembic current
3
4# Manually fix issue, then stamp
5alembic stamp head
6
7# Or downgrade and retry
8alembic downgrade -1
9alembic upgrade head

Query Performance

Problem: Slow queries

python
1# Enable query logging
2logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
3
4# Use EXPLAIN ANALYZE
5explain = session.exec(text("EXPLAIN ANALYZE SELECT ...")).all()
6
7# Profile queries
8# See references/queries-optimization.md for detailed patterns

Problem: N+1 queries

python
1# Use selectinload
2statement = select(User).options(selectinload(User.posts))
3
4# Or joinedload
5from sqlalchemy.orm import joinedload
6statement = select(User).options(joinedload(User.posts))

Production Workflow

Development

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

Staging

1. Deploy application code
2. Run migrations: alembic upgrade head
3. Verify data integrity
4. Test application

Production

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

Zero-Downtime Migration Strategy

For large production databases:

python
1# Phase 1: Add new column (nullable)
2def upgrade():
3    op.add_column('user', sa.Column('new_email', sa.String(), nullable=True))
4
5# Deploy app version that writes to both columns
6
7# Phase 2: Backfill data
8def upgrade():
9    op.execute("UPDATE user SET new_email = email WHERE new_email IS NULL")
10
11# Phase 3: Make non-nullable
12def upgrade():
13    op.alter_column('user', 'new_email', nullable=False)
14
15# Deploy app version that reads from new column
16
17# Phase 4: Drop old column
18def upgrade():
19    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.