Fix: SQLModel Not Working — table=True Confusion, Relationship Loading, and Session Errors

The Error

You define a SQLModel class and the table doesn’t exist:

from sqlmodel import SQLModel, Field

class User(SQLModel):
    id: int = Field(primary_key=True)
    name: str

SQLModel.metadata.create_all(engine)
# users table never created — silently ignored

Or you fetch with a relationship and get MissingGreenlet:

async with AsyncSession(engine) as session:
    result = await session.exec(select(User))
    users = result.all()
    for user in users:
        print(user.posts)   # MissingGreenlet error

Or accessing an attribute after closing the session raises:

with Session(engine) as session:
    user = session.exec(select(User).where(User.id == 1)).first()

print(user.name)   # DetachedInstanceError

Or you try to use SQLModel as a pure Pydantic model and validation breaks:

class UserResponse(SQLModel):
    name: str
    email: str

UserResponse.model_validate({"name": "Alice", "email": "[email protected]"})
# Works, but instances behave oddly when serialized

Or Optional[X] doesn’t behave like Pydantic’s Optional:

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    middle_name: Optional[str] = None   # Auto-default to None — sometimes

SQLModel is Tiangolo’s (FastAPI author) ORM — combines SQLAlchemy 2’s database layer with Pydantic 2’s validation. The “one class is both your DB model and API schema” idea is appealing but produces specific failure modes when the dual nature surfaces (table classes vs Pydantic-only classes, async session lifecycles, relationship loading). This guide covers each.

Why This Happens

SQLModel classes are dual-purpose: a class can be a Pydantic model, a SQLAlchemy mapped class, or both. The distinction is table=True in the class declaration:

• class User(SQLModel) — Pydantic-only schema
• class User(SQLModel, table=True) — Both Pydantic AND SQLAlchemy table

table=True registers the class with SQLModel.metadata. Without it, the class doesn’t become a table — create_all() won’t make it. This is the #1 source of “my table wasn’t created” issues.

Relationship loading inherits SQLAlchemy 2’s semantics — lazy by default, requires explicit selectinload/joinedload for async code. Accessing a relationship attribute after the session closes raises DetachedInstanceError.

Fix 1: table=True Required for Database Tables

from sqlmodel import SQLModel, Field
from typing import Optional

# WRONG — no table=True, this won't become a database table
class User(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    email: str

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

Verify which classes are table-mapped:

from sqlmodel import SQLModel
print(SQLModel.metadata.tables.keys())
# dict_keys(['user'])   # Only classes with table=True appear

Common patterns use SQLModel for both schema and table by inheriting:

# Base — shared fields
class UserBase(SQLModel):
    name: str
    email: str

# DB table — adds id and table=True
class User(UserBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

# API request — same fields as base, no id
class UserCreate(UserBase):
    pass

# API response — base + id, but no DB
class UserRead(UserBase):
    id: int

This pattern keeps validation logic (in UserBase) shared while distinguishing creates from reads.

Common Mistake: Copying a tutorial that uses class User(SQLModel): for schemas and assuming the same syntax creates a table. Always check for table=True — without it, you have a Pydantic model but no database table. The SQLModel.metadata.create_all(engine) call silently skips classes without table=True.

Fix 2: Engine and Session Setup

from sqlmodel import SQLModel, create_engine, Session
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine

# Sync
engine = create_engine("postgresql://user:pass@localhost/mydb", echo=True)
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    user = User(name="Alice", email="[email protected]")
    session.add(user)
    session.commit()
    session.refresh(user)
    print(user.id)   # Now has the auto-generated ID

# Async
async_engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/mydb")

async def main():
    async with async_engine.begin() as conn:
        await conn.run_sync(SQLModel.metadata.create_all)

    async with AsyncSession(async_engine) as session:
        user = User(name="Alice", email="[email protected]")
        session.add(user)
        await session.commit()
        await session.refresh(user)

Engine config — common params:

engine = create_engine(
    "postgresql://...",
    echo=False,                  # Log SQL (use True in dev)
    pool_size=10,                 # Connection pool
    max_overflow=20,
    pool_pre_ping=True,           # Test connection before use
    pool_recycle=3600,            # Recycle after 1 hour
)

Pro Tip: For FastAPI integration, define engine as module-level and use Session as a dependency:

from fastapi import Depends, FastAPI
from sqlmodel import Session

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

@app.get("/users/{user_id}")
def get_user(user_id: int, session: Session = Depends(get_session)):
    return session.get(User, user_id)

For FastAPI dependency injection patterns, see FastAPI dependency injection error.

Fix 3: Field Definitions and Types

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

class Status(str, Enum):
    active = "active"
    inactive = "inactive"

class User(SQLModel, table=True):
    # Primary key (auto-incrementing)
    id: Optional[int] = Field(default=None, primary_key=True)

    # Required string with max length
    name: str = Field(max_length=100)

    # Unique constraint
    email: str = Field(unique=True, index=True)

    # Optional with default
    bio: Optional[str] = Field(default=None)

    # Enum
    status: Status = Field(default=Status.active)

    # Auto-generated timestamp
    created_at: datetime = Field(default_factory=datetime.utcnow)

    # Indexed for fast queries
    username: str = Field(index=True, unique=True)

Field() parameters:

Optional[X] vs default=None:

class User(SQLModel, table=True):
    # WRONG — Optional makes the type nullable BUT Pydantic still requires it
    middle_name: Optional[str]

    # CORRECT — Optional + default for fully optional fields
    middle_name: Optional[str] = Field(default=None)

    # Or simpler
    middle_name: Optional[str] = None

Common Mistake: Marking a field Optional[str] and expecting it to be optional in Pydantic validation. Optional only makes the type allow None — without = None or Field(default=None), Pydantic still requires the field at construction. Always pair Optional with a default for true nullable fields.

Fix 4: Relationships

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

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

    # One-to-many: a 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(default=None, foreign_key="team.id")

    # Many-to-one: a hero belongs to one team
    team: Optional[Team] = Relationship(back_populates="heroes")

Insert with relationship:

with Session(engine) as session:
    team = Team(name="Avengers")
    session.add(team)
    session.commit()

    hero1 = Hero(name="Iron Man", team_id=team.id)
    hero2 = Hero(name="Thor", team_id=team.id)
    session.add(hero1)
    session.add(hero2)
    session.commit()

    # Query and access relationship
    session.refresh(team)
    print([h.name for h in team.heroes])   # ['Iron Man', 'Thor']

Eager loading to avoid N+1 queries:

from sqlmodel import select
from sqlalchemy.orm import selectinload

with Session(engine) as session:
    statement = select(Team).options(selectinload(Team.heroes))
    teams = session.exec(statement).all()

    for team in teams:
        # No additional query — heroes are already loaded
        print(team.name, [h.name for h in team.heroes])

For SQLAlchemy relationship loading patterns that apply to SQLModel, see SQLAlchemy not working.

Fix 5: Async Relationship Loading (MissingGreenlet)

from sqlmodel.ext.asyncio.session import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine

# WRONG — accessing relationship outside the await context
async with AsyncSession(async_engine) as session:
    result = await session.exec(select(Team))
    teams = result.all()
    for team in teams:
        print(team.heroes)   # MissingGreenlet — lazy load triggers in sync context

In async sessions, lazy loading is forbidden — every access must explicitly use the async API.

Fix — eager load with selectinload:

from sqlalchemy.orm import selectinload

async with AsyncSession(async_engine) as session:
    statement = select(Team).options(selectinload(Team.heroes))
    result = await session.exec(statement)
    teams = result.all()

    for team in teams:
        print(team.heroes)   # Already loaded — no async call needed

Alternative — expire_on_commit=False plus explicit reload:

async with AsyncSession(async_engine, expire_on_commit=False) as session:
    statement = select(Team).options(selectinload(Team.heroes))
    teams = (await session.exec(statement)).all()

    await session.commit()
    # teams still accessible because expire_on_commit=False

Common Mistake: Forgetting selectinload() and seeing MissingGreenlet errors. The fix is always: identify which relationship you’ll access, add selectinload() for it in the query. Async sessions never lazy-load — be explicit.

Pro Tip: Set expire_on_commit=False on async sessions globally:

async_session_factory = async_sessionmaker(
    async_engine,
    class_=AsyncSession,
    expire_on_commit=False,
)

expire_on_commit=True (the default) marks all loaded objects as “expired” after commit, forcing re-fetch on next attribute access — which then triggers MissingGreenlet in async. expire_on_commit=False lets you access loaded data after commit without re-querying.

Fix 6: Querying with select

from sqlmodel import select

# Simple select
with Session(engine) as session:
    users = session.exec(select(User)).all()

# With filter
users = session.exec(
    select(User).where(User.name.startswith("A"))
).all()

# First row
user = session.exec(
    select(User).where(User.id == 1)
).first()

# One row (raises if 0 or multiple)
user = session.exec(
    select(User).where(User.email == "[email protected]")
).one()

# Get by primary key (shortcut)
user = session.get(User, 1)

# Order, limit, offset
users = session.exec(
    select(User)
    .order_by(User.created_at.desc())
    .limit(10)
    .offset(20)
).all()

# Multiple columns
results = session.exec(
    select(User.name, User.email)
).all()
for name, email in results:
    print(name, email)

Join across relationships:

from sqlmodel import select

# Inner join
statement = (
    select(Hero, Team)
    .join(Team, Hero.team_id == Team.id)
    .where(Team.name == "Avengers")
)
results = session.exec(statement).all()
for hero, team in results:
    print(hero.name, team.name)

Aggregations:

from sqlalchemy import func

count = session.exec(
    select(func.count()).select_from(User)
).one()

avg_age = session.exec(
    select(func.avg(User.age))
).one()

Fix 7: Working with FastAPI

SQLModel was designed for FastAPI — the integration is tight:

from typing import List
from fastapi import FastAPI, Depends, HTTPException
from sqlmodel import Session, select

app = FastAPI()

# Reuse classes for both DB and API
class UserCreate(SQLModel):   # No table=True — API only
    name: str
    email: str

class User(UserCreate, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

class UserRead(UserCreate):
    id: int

@app.post("/users", response_model=UserRead)
def create_user(user: UserCreate, session: Session = Depends(get_session)):
    db_user = User.model_validate(user)
    session.add(db_user)
    session.commit()
    session.refresh(db_user)
    return db_user

@app.get("/users", response_model=List[UserRead])
def list_users(session: Session = Depends(get_session)):
    return session.exec(select(User)).all()

@app.get("/users/{user_id}", response_model=UserRead)
def get_user(user_id: int, session: Session = Depends(get_session)):
    user = session.get(User, user_id)
    if not user:
        raise HTTPException(status_code=404)
    return user

response_model=UserRead ensures FastAPI serializes only the fields in UserRead — even if the SQLModel has extra fields or sensitive data.

Common Mistake: Using User (the DB model) as response_model directly. If the User has fields like password_hash or api_key, those appear in responses. Always use a separate response model that explicitly excludes sensitive fields.

Fix 8: Update and Delete Patterns

# Update
with Session(engine) as session:
    user = session.get(User, 1)
    if user:
        user.name = "Bob"
        session.add(user)
        session.commit()
        session.refresh(user)

# Bulk update without loading
from sqlmodel import update

statement = update(User).where(User.id == 1).values(name="Bob")
session.exec(statement)
session.commit()

# Delete
user = session.get(User, 1)
session.delete(user)
session.commit()

# Bulk delete
from sqlmodel import delete

statement = delete(User).where(User.is_inactive)
session.exec(statement)
session.commit()

Update from Pydantic dict:

@app.patch("/users/{user_id}", response_model=UserRead)
def update_user(
    user_id: int,
    user_data: UserUpdate,   # Has all fields Optional
    session: Session = Depends(get_session),
):
    db_user = session.get(User, user_id)
    if not db_user:
        raise HTTPException(404)

    update_dict = user_data.model_dump(exclude_unset=True)
    for key, value in update_dict.items():
        setattr(db_user, key, value)

    session.add(db_user)
    session.commit()
    session.refresh(db_user)
    return db_user

exclude_unset=True is critical — it returns only fields the client actually sent, so partial updates don’t overwrite with None values.

Still Not Working?

SQLModel vs SQLAlchemy vs Tortoise

• SQLModel — Best for FastAPI projects, dual Pydantic + SQLAlchemy. See this article.
• SQLAlchemy 2 — More mature, more features, broader ecosystem. See SQLAlchemy not working.
• Tortoise ORM — Django-style async ORM, simpler but less flexible.

SQLModel is rising fast in the FastAPI ecosystem; pure SQLAlchemy still wins for non-FastAPI codebases.

Async Pool Management

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

engine = create_async_engine(
    "postgresql+asyncpg://...",
    echo=False,
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
)

async_session_factory = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_session():
    async with async_session_factory() as session:
        yield session

For asyncpg-specific pool patterns, see asyncpg not working.

Migrations with Alembic

SQLModel works with Alembic — point target_metadata at SQLModel.metadata:

# alembic/env.py
from sqlmodel import SQLModel
import myapp.models   # Import to register classes

target_metadata = SQLModel.metadata

For Alembic-specific issues, see Alembic not working.

Testing with In-Memory SQLite

import pytest
from sqlmodel import SQLModel, Session, create_engine
from sqlmodel.pool import StaticPool

@pytest.fixture
def session():
    engine = create_engine(
        "sqlite://",
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        yield session

StaticPool is required for in-memory SQLite across threads — without it, each connection sees an empty database.

For pytest fixture patterns with database state, see pytest fixture not found.

Pydantic Validation in SQLModel

SQLModel inherits Pydantic 2 validators:

from sqlmodel import SQLModel, Field
from pydantic import EmailStr, field_validator

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    email: EmailStr   # Pydantic email validation
    age: int = Field(ge=0, le=150)

    @field_validator("email")
    @classmethod
    def normalize_email(cls, v: str) -> str:
        return v.lower().strip()

Validators run on instantiation, both for API input and direct construction.

For Pydantic-specific validation patterns, see Pydantic validation error.