Intermediate 3 min · July 14, 2026

SQLModel: Python ORM for FastAPI and SQLAlchemy

Q: What is the difference between SQLModel and SQLAlchemy?

SQLModel is built on top of SQLAlchemy and Pydantic. It reduces boilerplate by combining model definitions for both ORM and validation. SQLAlchemy requires separate Pydantic models for API schemas.

Q: Can I use SQLModel without FastAPI?

Yes, SQLModel works as a standalone ORM. You can use it with any Python framework or script. However, its main advantage is seamless integration with FastAPI.

Q: How do I handle many-to-many relationships in SQLModel?

Define an association table as a separate SQLModel with `table=True`. Use `Relationship` with `link_model` parameter. Example: `Hero.team: List[Team] = Relationship(link_model=HeroTeam)`.

Learn SQLModel, the modern Python ORM that combines SQLAlchemy and Pydantic for FastAPI.

Naren Founder & Principal Engineer

20+ years shipping production Python across data and backend systems. Lessons pulled from things that broke in production.

✓ Production

production tested

July 15, 2026

last updated

2,398

articles · all by Naren

Before you start⏱ 15-20 min read

✓Python 3.7+ installed
✓Basic knowledge of SQL and databases
✓Familiarity with FastAPI or Flask
✓Understanding of Python type hints

● Production Incident 🔎 Debug Guide ⚙ Triage Commands

⚡Quick Answer

SQLModel is a Python ORM that merges SQLAlchemy's database power with Pydantic's data validation, designed for FastAPI. It lets you define models once, get automatic API schemas, and use async/await. Key benefits: less boilerplate, type safety, and seamless FastAPI integration.

✦ Definition~90s read

What is SQLModel?

SQLModel is a Python ORM that combines SQLAlchemy and Pydantic, allowing you to define database models and API schemas in a single class.

★

Think of SQLModel as a smart translator between your Python code and a database.

Plain-English First

Think of SQLModel as a smart translator between your Python code and a database. Normally, you'd write two sets of instructions: one for the database (SQLAlchemy) and one for data validation (Pydantic). SQLModel combines them into one, so you only write once. It's like having a bilingual friend who can talk to both the database and your API without repeating yourself.

When building APIs with FastAPI, you need to interact with a database. Traditionally, you'd use SQLAlchemy for ORM and Pydantic for data validation, leading to duplicated model definitions. SQLModel, created by FastAPI's author, solves this by merging both into a single model. This tutorial will guide you through SQLModel's core features, from basic CRUD to advanced relationships, with production-ready examples. You'll learn how to define models, perform queries, handle migrations, and debug issues in production. By the end, you'll be able to build robust FastAPI applications with minimal boilerplate.

Setting Up SQLModel

First, install SQLModel: pip install sqlmodel. It pulls in SQLAlchemy and Pydantic automatically. Create a database engine and session. Here's a minimal setup:

setup.pyPYTHON

from sqlmodel import SQLModel, create_engine, Session

DATABASE_URL = "sqlite:///database.db"
engine = create_engine(DATABASE_URL, echo=True)

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

🔥Engine Configuration

📊 Production Insight

In production, use a connection pool with pool_size=10 and max_overflow=20 to handle concurrent requests.

🎯 Key Takeaway

SQLModel uses SQLAlchemy's engine and session under the hood. You can configure connection pools and other options via create_engine.

Defining Models

Models are defined by inheriting from SQLModel. Each attribute is a column. Use type hints for validation. Example: a Hero model with id, name, secret_name, and age.

models.pyPYTHON

from typing import Optional
from sqlmodel import SQLModel, Field

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

# Create all tables
SQLModel.metadata.create_all(engine)

💡Table vs Non-Table Models

📊 Production Insight

Always set id as Optional[int] with default None so SQLAlchemy auto-generates it. For UUIDs, use Field(default_factory=uuid.uuid4).

🎯 Key Takeaway

A single class defines both the database table and the API schema. Use Field for column constraints like primary_key, nullable, etc.

CRUD Operations

CRUD (Create, Read, Update, Delete) is straightforward with sessions. Here's how to create and read heroes:

crud.pyPYTHON

from sqlmodel import Session, select

# Create
with Session(engine) as session:
    hero = Hero(name="Deadpond", secret_name="Dive Wilson", age=25)
    session.add(hero)
    session.commit()
    session.refresh(hero)
    print(hero.id)  # 1

# Read
with Session(engine) as session:
    statement = select(Hero).where(Hero.name == "Deadpond")
    result = session.exec(statement)
    hero = result.one()
    print(hero.name)  # Deadpond

Output

Deadpond

⚠ Always Commit

📊 Production Insight

Wrap CRUD operations in try-except to handle IntegrityError and rollback on failure. Use session.rollback() to reset the session.

🎯 Key Takeaway

Use session.exec() for queries (returns a Result object) and session.add() for inserts. Always commit and refresh to get generated IDs.

Relationships: One-to-Many

SQLModel supports relationships via Relationship and foreign keys. Example: Team has many Heroes.

relationships.pyPYTHON

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

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

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

🔥Back Populates

📊 Production Insight

For large datasets, use selectinload to eagerly load relationships and avoid N+1 queries: statement = select(Team).options(selectinload(Team.heroes)).

🎯 Key Takeaway

Define foreign keys with Field(foreign_key="table.column") and use Relationship to navigate between models.

Using SQLModel with FastAPI

SQLModel shines in FastAPI. Define request/response models as non-table models, and database models as table=True. Use dependency injection for sessions.

fastapi_app.pyPYTHON

from fastapi import FastAPI, Depends
from sqlmodel import Session, select

app = FastAPI()

# Non-table model for request
class HeroCreate(SQLModel):
    name: str
    secret_name: str
    age: Optional[int] = None

@app.post("/heroes/")
def create_hero(hero: HeroCreate, session: Session = Depends(get_session)):
    db_hero = Hero.from_orm(hero)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

@app.get("/heroes/")
def read_heroes(session: Session = Depends(get_session)):
    heroes = session.exec(select(Hero)).all()
    return heroes

💡from_orm vs model_validate

📊 Production Insight

Add response_model to endpoints to automatically filter fields. For example, response_model=HeroRead where HeroRead excludes secret fields.

🎯 Key Takeaway

Use non-table models for input/output to avoid exposing internal fields. Use from_orm or model_validate to convert.

Migrations with Alembic

SQLModel works with Alembic for database migrations. Install alembic and configure it to use your SQLModel metadata.

alembic_env.pyPYTHON

from sqlmodel import SQLModel
from models import Hero, Team  # import all models

target_metadata = SQLModel.metadata

⚠ Import All Models

📊 Production Insight

Always review auto-generated migrations before applying. They may miss complex changes like column type alterations.

🎯 Key Takeaway

Set target_metadata = SQLModel.metadata in Alembic. Run alembic revision --autogenerate -m "message" to generate migrations.

Async Support

SQLModel supports async with databases like PostgreSQL. Use AsyncSession and create_async_engine.

async_example.pyPYTHON

from sqlmodel import SQLModel, create_async_engine, select
from sqlmodel.ext.asyncio.session import AsyncSession

DATABASE_URL = "postgresql+asyncpg://user:pass@localhost/db"
engine = create_async_engine(DATABASE_URL, echo=True)

async def get_session():
    async with AsyncSession(engine) as session:
        yield session

async def create_hero(hero: HeroCreate):
    async with AsyncSession(engine) as session:
        db_hero = Hero.from_orm(hero)
        session.add(db_hero)
        await session.commit()
        await session.refresh(db_hero)
        return db_hero

🔥Async Drivers

📊 Production Insight

Async sessions are not thread-safe. Use asyncio.Lock if sharing sessions across tasks.

🎯 Key Takeaway

Use create_async_engine and AsyncSession for async operations. All session methods become awaitable.

● Production incidentPOST-MORTEMseverity: high

The Missing Foreign Key: A Production Outage

Symptom

Users reported 500 errors when creating orders. Logs showed 'IntegrityError: NOT NULL constraint failed'.

Assumption

The developer assumed that adding a new optional field wouldn't break existing code.

Root cause

A new foreign key column was added with nullable=False, but existing rows had no corresponding parent record, violating the constraint.

Fix

Changed the column to nullable=True, then ran a migration to backfill missing references, then set nullable=False.

Key lesson

Always set new foreign keys as nullable initially in production.
Use database migrations to apply schema changes safely.
Test schema changes against a copy of production data.
Add database constraint checks in CI/CD pipelines.
Monitor error rates after deployments to catch regressions early.

Production debug guideSymptom to Action4 entries

Symptom · 01

IntegrityError on insert

→

Fix

Check foreign key constraints and nullable settings. Use session.rollback() and log the failing data.

Symptom · 02

Slow queries

→

Fix

Enable SQLAlchemy echo=True to log queries. Add indexes on frequently filtered columns.

Symptom · 03

Model not found in API docs

→

Fix

Ensure model inherits from SQLModel and is imported in the app. Check that response_model is set correctly.

Symptom · 04

Session not closed

→

Fix

Use dependency injection with yield to ensure session is closed after request.

★ Quick Debug Cheat SheetCommon SQLModel issues and immediate actions.

IntegrityError: NOT NULL constraint failed−

Immediate action

Check if new column is nullable=False and existing rows have NULL.

Commands

SELECT COUNT(*) FROM table WHERE new_column IS NULL;

ALTER TABLE table ALTER COLUMN new_column DROP NOT NULL;

Fix now

Set nullable=True in model, then backfill data.

No response from API after DB change+

FastAPI docs show wrong schema+

Feature	SQLAlchemy	Pydantic	SQLModel
Define models	Separate classes	Separate classes	Single class
Validation	Manual	Automatic	Automatic
FastAPI integration	Requires extra code	Built-in	Seamless
Async support	Via extensions	N/A	Built-in

⚙ Quick Reference

7 commands from this guide

File	Command / Code	Purpose
setup.py	from sqlmodel import SQLModel, create_engine, Session	Setting Up SQLModel
models.py	from typing import Optional	Defining Models
crud.py	from sqlmodel import Session, select	CRUD Operations
relationships.py	from typing import Optional, List	Relationships
fastapi_app.py	from fastapi import FastAPI, Depends	Using SQLModel with FastAPI
alembic_env.py	from sqlmodel import SQLModel	Migrations with Alembic
async_example.py	from sqlmodel import SQLModel, create_async_engine, select	Async Support

Key takeaways

SQLModel merges SQLAlchemy and Pydantic into one model, reducing boilerplate.

Use table=True for database models and non-table models for API schemas.

Always commit sessions and handle exceptions with rollback.

Use Alembic for migrations and eager loading to avoid N+1 queries.

SQLModel works seamlessly with FastAPI, especially with dependency injection for sessions.

INTERVIEW PREP · PRACTICE MODE

Interview Questions on This Topic

Q01SENIOR

How does SQLModel combine SQLAlchemy and Pydantic?

Q02JUNIOR

What is the purpose of `table=True` in SQLModel?

Q03SENIOR

How do you prevent N+1 queries in SQLModel?

Q01 of 03SENIOR

How does SQLModel combine SQLAlchemy and Pydantic?

ANSWER

SQLModel uses Python's type hints to define both SQLAlchemy columns and Pydantic fields. At runtime, it generates the SQLAlchemy table and Pydantic schema from the same class.

FAQ · 3 QUESTIONS

Frequently Asked Questions

What is the difference between SQLModel and SQLAlchemy?

Can I use SQLModel without FastAPI?

How do I handle many-to-many relationships in SQLModel?

Naren Founder & Principal Engineer

20+ years shipping production Python across data and backend systems. Lessons pulled from things that broke in production.

✓ Verified

production tested

July 15, 2026

last updated

2,398

articles · all by Naren

🔥

That's Python Libraries. Mark it forged?

3 min read · try the examples if you haven't