Quick Start: Your First SQLModel API in 5 Minutes
After six months running SQLModel in production, my honest take is this: it is one of the most practical library choices I have made for FastAPI projects. Not because it is flashy, but because it eliminates the most tedious part of API development — maintaining synchronized Pydantic schemas and SQLAlchemy models side by side.
Install everything you need with a single command:
pip install sqlmodel fastapi uvicornA complete, working user API:
from fastapi import FastAPI, HTTPException
from sqlmodel import Field, Session, SQLModel, create_engine, select
from typing import Optional
# Single model: ORM table + Pydantic validation in one class
class User(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
email: str
engine = create_engine("sqlite:///./app.db")
SQLModel.metadata.create_all(engine)
app = FastAPI()
@app.post("/users/", response_model=User)
def create_user(user: User):
with Session(engine) as session:
session.add(user)
session.commit()
session.refresh(user)
return user
@app.get("/users/", response_model=list[User])
def list_users():
with Session(engine) as session:
return session.exec(select(User)).all()Run it with uvicorn main:app --reload and open /docs. You get a fully typed Swagger UI with zero extra configuration. The table=True flag is what makes this possible. It registers the class with SQLAlchemy’s mapper registry while keeping all Pydantic validation behavior intact.
Deep Dive: How SQLModel Bridges Pydantic and SQLAlchemy
SQLModel is built directly on top of both libraries. When you define a class with table=True, the metaclass registers it as a SQLAlchemy ORM table. Without that flag, you get a pure Pydantic model — same class syntax, different runtime behavior.
This distinction matters because you almost always want separate models for your API boundary versus your database schema. Here is the pattern I now use in every project:
class UserBase(SQLModel):
name: str
email: str
class User(UserBase, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
hashed_password: str # Never exposed in API responses
class UserCreate(UserBase):
password: str # Raw password, accepted only on input
class UserRead(UserBase):
id: int # Guaranteed non-null in responsesYour endpoints then use the right model for the right context:
@app.post("/users/", response_model=UserRead)
def create_user(user_data: UserCreate):
hashed = hash_password(user_data.password)
db_user = User(
name=user_data.name,
email=user_data.email,
hashed_password=hashed
)
with Session(engine) as session:
session.add(db_user)
session.commit()
session.refresh(db_user)
return db_userThree classes, one base definition — no field duplication. UserCreate handles input validation, UserRead shapes the serialized response, and User maps to the actual database table including sensitive fields that never leave the server. I learned this the hard way: a security review flagged that hashed_password was leaking into API responses because I was using the table model directly as the response type.
Advanced Usage: Relationships, Queries, and Migrations
Defining Table Relationships
Relationships use SQLAlchemy’s standard Relationship API, exposed through SQLModel’s own import. Here is a Post model linked to User:
from sqlmodel import Relationship
from typing import List
class Post(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
title: str
content: str
author_id: int = Field(foreign_key="user.id")
author: Optional["User"] = Relationship(back_populates="posts")
class User(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
email: str
posts: List[Post] = Relationship(back_populates="author")Querying with joins looks exactly like raw SQLAlchemy — no new syntax to learn:
@app.get("/users/{user_id}/posts")
def get_user_posts(user_id: int):
with Session(engine) as session:
user = session.get(User, user_id)
if not user:
raise HTTPException(status_code=404, detail="User not found")
return user.postsSchema Migrations with Alembic
SQLModel plugs cleanly into Alembic. The key step is pointing Alembic at SQLModel.metadata:
pip install alembic
alembic init migrationsIn migrations/env.py, import every table model so the metadata is populated, then assign it:
from sqlmodel import SQLModel
from app.models import User, Post # Must import all table models
target_metadata = SQLModel.metadataGenerate and apply migrations as normal:
alembic revision --autogenerate -m "add posts table"
alembic upgrade headAsync Engine Setup
For high-throughput APIs, SQLModel ships with an async session wrapper:
from sqlmodel.ext.asyncio.session import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
# Use asyncpg for PostgreSQL in production
engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/db")
@app.get("/users/")
async def list_users():
async with AsyncSession(engine) as session:
result = await session.exec(select(User))
return result.all()Install aiosqlite for SQLite or asyncpg for PostgreSQL as the async driver.
Practical Tips: What Six Months of Production Use Taught Me
The surface API is small enough to learn in a day. The rough spots only show up once you move past tutorial examples — and a few of them are genuinely non-obvious.
Never Expose the Table Model Directly as a Response Type
Using User everywhere is tempting for its simplicity. Resist it. Table models often carry fields you never want serialized — password hashes, internal audit flags, soft-delete timestamps. Always route responses through a dedicated UserRead class.
Watch for DetachedInstanceError on Lazy-Loaded Relationships
SQLAlchemy’s lazy loading silently defers fetching relationships until you access them. Once the session closes, accessing a relationship raises DetachedInstanceError. The fix is to eager load within the session scope:
from sqlalchemy.orm import selectinload
statement = select(User).options(selectinload(User.posts))
user = session.exec(statement).first()Type Annotations Drive Database Nullability
SQLModel infers column constraints directly from Python type hints. A field typed as str becomes NOT NULL. A field typed as Optional[str] or str | None becomes nullable. Miss this and your Alembic autogenerate will silently drift from the actual schema — usually discovered at the worst possible moment.
Use In-Memory SQLite for Fast, Isolated Tests
import pytest
from sqlmodel import create_engine, Session, SQLModel
from fastapi.testclient import TestClient
from app.main import app, get_session
@pytest.fixture(name="session")
def session_fixture():
engine = create_engine("sqlite:///:memory:")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
yield session
@pytest.fixture(name="client")
def client_fixture(session: Session):
def override_get_session():
return session
app.dependency_overrides[get_session] = override_get_session
return TestClient(app)Each test gets a clean, isolated schema with no teardown needed.
Alembic Autogenerate Has Blind Spots
Autogenerate does not detect everything — custom constraints, server-side defaults, and some index types require hand-written migration scripts. Always review the generated file before running alembic upgrade head against a production database.
Sebastián Ramírez — FastAPI’s creator — also built SQLModel, which explains why the two fit together so naturally. After six months, my team hasn’t needed to reach for raw SQLAlchemy once. For any new FastAPI project, it’s the starting point now — not an experiment.
